MySQL 查询优化技巧

 

瓶颈

众所周知，几乎所有大型项目的最终效率瓶颈，都来自于如何更高效的进行数据查询，不论是使用何种数据源。

而在 MySQL 中，大部分效率低下的查询，都是因为没有正确的使用索引。

可以说，合理有效的使用索引将会对 MySQL 的效率优化起到决定性的作用。

原则

索引是为了优化查询效率而存在的，正确的设置索引，将会使查询效率有质的飞跃。

索引并不是设置的越多越好，设置过多的索引，将会大大的影响非 SELECT 查询的执行效率。

将作为查询和排序条件次数最多的字段作为索引，是我们最常使用的设置原则。

技巧

索引也会被用来做其他的事情，比如我们经常使用唯一索引，来做一些特殊的规则限定，比如地图中的坐标唯一。

不过我始终认为，这个应该是程序应该做的事情，而数据结构的限制，只应该是最后的保障。

索引的使用，也存在一些“潜规则”，如果不弄清楚，很可能提高效率的愿望没有达到，查询反而变慢了。

以下的几种情况，对应字段的索引是无法生效的：

查询条件里有不等号

SELECT * FROM `t_user` WHERE `id` != 2 中，id 的索引将不会被使用。

查询条件里使用了函数

SELECT * FROM `t_user` WHERE DAY(`regTime`) = 6 中，regTime 的索引将不会被使用。

在JOIN操作中，主键和外键的数据类型不同

SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `t_user` AS `U` ON `U`.`id` = `C`.`userId` 中，如果 t_user 表的 id 字段和t_user_city 表的 userId 字段数据类型不同，那么这两个字段的索引将不会被使用。

查询条件里使用比较操作符 LIKE 和 REGEXP，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。

①SELECT * FROM `t_user` WHERE `name` LIKE ‘123%’ 和 ②SELECT * FROM `t_user` WHERE `name` LIKE ‘3’ 中，① 查询能使用 name 的索引，而 ② 查询却不行。

使用索引查询出的记录数量超过全表记录的30%，MySQL 会遍历全表。

SELECT * FROM `t_user` WHERE `isNovice` = 1 中，t_user 表所有的记录都会被遍历，因为 isNovice 只有 0 和 1 这两个值，记录数各占 50%，设置 isNovice 为索引没有任何意义。

索引的优化是双向的，不仅仅是数据库结构如何设计、索引如何定义，在程序中如何构造查询语句也是至关重要的。

除了注意以上的几条规则以外，还有一些构造的技巧，也可以帮助你提升查询的效率。

尝试使用 IN 代替 OR

①SELECT * FROM `t_user` WHERE `id` = 2 OR `id` = 4 OR `id` = 6 OR `id` = 8 OR `id` = 10 和 ②SELECT * FROM `t_user` WHERE `id` IN (2, 4, 6, 8, 10) 中，②的效率明显比 ① 要高的多。

避免在查询条件中使用函数

①SELECT * FROM `t_user` WHERE DATE(`regTime`) = ‘2010-02-01’ 和 ②SELECT * FROM `t_user` WHERE `regTime` > ‘2010-02-01’ AND `regTime` < ‘2010-02-02’中，② 的效率明显比 ① 要高的多。

使用简单的程序处理来代替数据库处理

SELECT `userId`, SQRT(POW(`unitX`, 2) + POW(`unitY`, 2)) AS `distance` FROM `t_map_unit` WHERE `userId` = 2 中，计算距离的操作完全可以由程序来做，或者在需要的时候再用程序计算。为了图一时方便，让数据库来运算，是得不偿失的。

尽量避免使用联合查询

①SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `t_user` AS `U` ON `U`.`id` = `C`.`userId` 和 ②SELECT * FROM `t_user_city` 、③SELECT `name` FROM `t_user` WHERE `id` IN (……) ，我更建议使用后者，实际上通过增加简单的程序处理就可以实现，但是效率上区别确是很大的，特别是当 2 个表的记录都很多的时候。两个查询有时候比一个更快，这完全取决于查询语句的复杂度。

我们要深刻的明白一个道理，只让数据库提供和保存数据就好，尽量不要让它做其他的事情。

它很脆弱，它很容易累，它对你很重要。哪怕多写数十行程序只能减少一次查询，也值得一试。

控制

优化的过程是持续的，能及时的发现问题、解决问题，是控制项目风险的精要所在。

我们可以通过很多方法来发现数据库查询的种种问题。

比如频繁的 SHOW FULL PROCESSLIST，然后记录那些频繁出现或者出现 copy tmp table 的语句，并解决它们。

但是这毫无疑问是个笨方法，我们可以使用更便捷的方式记录这些有问题的语句。

修改 MySQL 配置，记录查询速度较慢的查询

在 my.cnf 中增加如下配置选项：

long_query_time = 1
log-slow-queries = /data/mysql/slow.log

这样我们就能通过查看 /data/mysql/slow.log 这个文件，找到所有查询时间超过 1 秒的查询语句。

修改 MySQL 配置，记录没有使用索引的查询

在 my.cnf 中增加如下配置选项： 

log-slow-queries = /data/mysql/slow.log
log-queries-not-using-indexes

这样 /data/mysql/slow.log 将会记录所有没有使用索引的查询语句。

两个选项可以一起使用，这样我们基本上就可以抓住大多数的“问题查询”了。

再针对这些查询进行分析和研究，修改查询或修改索引设置，最终让它们不再在这个日志文件中出现。

如果最终这个日志文件中除了必要的遍历查询以外再无其它内容，你的项目至少在数据库上已经没有效率问题了。

另外，对于频繁进行数据修改操作的表，索引可能会损坏。

索引损坏后，将不会起任何作用，周期性的检查和优化是非常必要的。

希望本文能对你有所帮助，欢迎来信交流优化经验。