Mysql实例 数据库优化--数据库表设计

一.前言

现如今，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显，所以要重视数据库的性能优化。

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的，它是随着用户量的增加，基础架构才逐渐完善。

SQL执行慢的原因：

• 网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等硬件问题
• 数据库整体结构已经扛不住压力
• 没有索引或者索引失效
• 数据表里的数据记录过多
• 服务器调优及各个参数设置也可能会影响
• 开发者编写的SQL效率
• 其它

根据上述问题，将数据库的优化分为几个阶段进行调整，力求让数据库发挥好的性能和稳定运行。

二.数据库表设计

项目立项后，开发部门根据产品部门需求开发项目。开发工程师在开发项目初期会对表结构设计。对于数据库来说，表结构设计很重要，如果设计不当，会直接影响到用户访问网站速度，用户体验不好！

这种情况具体影响因素有很多，例如慢查询（低效的查询语句）、没有适当建立索引、数据库堵塞（锁）等。当然，有测试部门的团队，会做产品测试，找Bug。

由于开发工程师重视点不同，初期不会考虑太多数据库设计是否合理，而是尽快完成功能实现和交付。等项目上线有一定访问量后，隐藏的问题就会暴露，这时再去修改就不是这么容易的事了！

sql语句优化 1.EXPLAIN分析SELECT查询 很多情况下，使用EXPLAIN关键字可以知道MySQL是如何处理SQL语句的，这可以帮助分析查询语句，从而或许能尽快的找到优化方法以及潜在的性能问题。

2.SELECT查询必须指明字段名 SELECT * 的查询会加很多不必要的消耗（例如CPU、I/O等），同时，也有可能增加了使用覆盖索引。所以SELECT查询时，要求直接在后面指明需要查询的对应字段名。

3.查询一条数据的时候，使用 LIMIT 1 减少多余的查询，因为指定limit 1后，查询到一条数据就不再继续查询了，使得EXPLAIN中type列达到const类型，查询语句更优。

4.为搜索的WHERE字段建立索引 一般，每个表都会设置一个主键，而索引并不一定就是给主键。如果在表中，有某个字段总要会经常用来做WHERE查询搜索，而且是读大于写的。

5.千万不要使用 ORDER BY RAND() 如果想随机取数据，不要用用随机数取，因为这种查询，对数据库的性能毫无益处（消耗CPU）。更好的方案之一是先找到数据所在的条数N，然后再用LIMIT N, 1这样查询。

6.保证每张表都有一个主键ID 每设计新建一张表的时候，都应该为其设计一个ID字段，并让其成为主键，而且最好是INT型（也有使用UUID的），同时设置这个ID字段为自增（AUTO_INCREMENT）的标志。

7.尽可能的使用 NOT NULL NULL也需要额外的空间，NULL字段在进行查询比较的时候，是比较麻烦的。如果不是必须使用NULL，就建议使用NOT NULL。

8.选择合适的存储引擎 在MySQL中有MyISAM和InnoDB两种存储引擎，两者各有利弊，需要了解两者的差异然后来做出最合适的选择，例如InnoDB支持事务而MyISAM不支持，MyISAM查询比InnoDB快等等；若不太清楚选择什么的话，那就用InnoDB。

9.把IP地址存为UNSIGNED INT 在遇到需要存储IP地址的时候，大多数想法都会是存储VARCHAR(15)字符串类型的，而不会想到要用INT整型来存储；如果用整型来存储，只需要4个字节，并且你可以有定长的字段，而且会带来查询上的优势。

10.尽量不要在WHERE查询时对字段进行null值判断 当对一个字段进行null的判断时候，会比较慢的，这是因为这个判断会导致引擎放弃使用所有已有的索引而进行全表扫描搜索。

11.尽量不要使用%前缀的LIKE模糊查询 模糊查询，在日常开发中会经常遇到，很多都是直接 LIKE ‘%key_word%’ 或者 LIKE ‘%key_word’ 这样搜索的，这两种搜索方式，都会导致索引失效从而进行全表扫描搜索。建议使用“全文索引”。

12.避免在WHERE查询时对字段进行表达式操作 例如查询语句SELECT id FROM table WHERE num * 2 = 50;，这样的查询，对字段num做了一个乘2的算数操作，就会导致索引失效。

13.减少不必要的排序 排序操作会消耗较多的CPU资源，所以减少不必要的排序可以在缓存命中率高等I/O足够的情况下，会降低SQL的响应时间。

14.建议用JOIN代替子查询 JOIN的性能相比子查询有更大优势。

15.避免发生隐式类型转换 类型转换主要是指在WHERE子句中出现字段的类型和传入的参数类型不一致的时候发生的类型转换；这是因为如果传入的数据类型和字段类型不一致，MySQL可能会对数据进行类型转换操作，也可能不进行处理而直接交由存储引擎去处理，这样一来，就可能会出现索引无法使用的情况而造成执行计划问题。

16.避免多表查询字段类型不一致 在遇到需要多表联合查询的时候，设计表结构的时候，尽量保持表与表的关联字段一致，并且都要设置索引。同时，多表连接查询时，尽量把结果集小的表作为驱动表。

17.建议开启查询缓存 大多数的MySQL服务器都开启了查询缓存，这是提高性能最有效的方法之一，因为查询缓存由MySQL数据库引擎自动处理，当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表，而直接访问缓存结果了。

18.使用UNION代替临时表 UNION查询可以把两条或更多的SELECT查询结果合并到一个查询中，从而不再需要创建临时表来完成。需要注意的是，使用UNION的所有SELECT语句中的字段数目要相同。

19.慎用IN查询 IN以及NOT IN查询都要慎重，因为可能会导致全表扫描，而对于连续的数值，能用BETWEEN就不要用IN了。