众所周知，几乎所有大型项目的最终效率瓶颈都来自于数据查询，不论是使用何种数据源。而在 MySQL 中，大部分效率低下的查询，都是因为没有正确的使用索引。可以说，合理有效的使用索引将会对 MySQL 的效率优化起到决定性的作用。

 

 

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

 

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

 

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

 

 

索引也会被用来做其他的事情，比如我们经常使用唯一索引，来做一些特殊的规则限定，比如地图中的坐标唯一。不过我始终认为，这个应该是程序应该做的事情，而数据结构的限制，只应该是最后的保障。索引的使用，也存在一些“潜规则”，如果不弄清楚，很可能提高效率的愿望没有达到，查询反而变慢了。

 

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

(1)查询条件里有不等号

SELECT * FROM `user` WHERE `id` != 2 这条SQL语句中，id 的索引将不会被使用。

(2)查询条件里使用了函数

SELECT * FROM `user` WHERE DAY(`regTime`) = 6 这条SQL语句中，id 的索引将不会被使用。

(3)在JOIN操作中，如果主键和外键的数据类型不同,则两个表的索引将都不会使用。

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

(4)如果查询条件里使用了比较操作符 LIKE 和 REGEXP，则只有在搜索模板的第一个字符不是通配符的情况下才能使用索引，如下面两条SQL语句：

SELECT * FROM `user` WHERE `name` LIKE '123%'

SELECT * FROM `user` WHERE `name` LIKE '%123'

结论：第一条查询语句能使用 name 的索引，而第二条查询却不行。

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

 

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

 

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

 

除了注意以上的几条规则以外，还有一些构造的技巧，也可以帮助你提升查询的效率，如果查询逻辑比较复杂，并且存在or的情况，请尝试使用 IN 代替 OR，例如下面两条查询语句：

SELECT * FROM `user` WHERE `id` = 2 OR `id` = 4 OR `id` = 6 OR `id` = 8 OR `id` = 10

SELECT * FROM `user` WHERE `id` IN (2, 4, 6, 8, 10)

结论：第二条的查询效率明显比第一条要高的多。

 

SELECT * FROM `user` WHERE DATE(`regTime`) = '2010-02-01'

SELECT * FROM `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

这条SQL语句中，计算距离的操作完全可以由程序来做，或者在需要的时候再用程序计算。这里让数据库来运算，是不理想的做法。

SELECT `C` . * , `U`.`name` AS `userName` FROM `t_user_city` AS `C` LEFT JOIN `user` AS `U` ON `U`.`id` = `C`.`userId`

SELECT * FROM `t_user_city`

SELECT `name` FROM `user` WHERE `id` IN (……)

我更建议使用后面两条查询取代第一条查询，实际上通过增加简单的程序处理就可以实现，但是效率上区别确是很大的，特别是当两个表的记录都很多的时候。两个简单的查询有时候比一个复杂的查询更快，这完全取决于查询语句的复杂度。

 

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

 

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

 

比如频繁的 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 秒的查询语句。

 

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

 

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

log-queries-not-using-indexes

 

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

 

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

 

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

 

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

 

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

 

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