mysql的20条优化总结

前言

现如今，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。所以，我整理了MySQL优化的几点建议，希望这些优化技巧对您有用，总结不到的，欢迎大家补充。

SQL执行慢的原因

1. 网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等硬件问题

2. 没有索引或者索引失效

3. 数据表里的数据记录过多

4. 服务器调优及各个参数设置也可能会影响

5. 开发者编写的SQL效率

6. 其他

1、EXPLAIN分析你的SELECT查询

很多情况下，使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的，这可以帮你分析你的查询语句，从而或许能尽快的找到优化方法以及潜在的性能问题。具体EXPLAIN的使用以及各个参数的含义，请查阅相关文档即可。

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）的标志。

8、尽可能的使用 NOT NULL

不要以为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 &＃39;%key_word%&＃39; 或者 LIKE &＃39;%key_word&＃39; 这样搜索的，这两种搜索方式，都会导致索引失效从而进行全表扫描搜索。如果解决上面的这种模糊查询呢，答案就是使用“使用全文索引”，具体的用法有兴趣的可以自己查资料一波。

12、避免在WHERE查询时对字段进行表达式操作

例如查询语句SELECT id FROM table WHERE num * 2 = 50;，这样的查询，对字段num做了一个乘2的算数操作，就会导致索引失效。

14、减少不必要的排序

排序操作会消耗较多的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了。

20、欢迎补充

结束语

这主要是从查询角度去考虑优化，还有一些分表、分区技术以及读写分离等；以上优化之处，如果说的不到位的地方，请大家谅解，MySQL优化的地方可以有很多处，欢迎提出其他优化建议，谢谢。

以上就是mysql的20条优化总结的详细内容，更多请关注 第一PHP社区 其它相关文章！