【MySQL】十五、深入MySQL索引的秘密（三）

本文主要介绍MySQL数据库的必须要掌握的索引的相关知识

在SQL进行排序的时候，如何才能使用索引

假设你有一个select * from table where xxx=xxx order by xxx这样一个SQL语句，似乎应该是基于where语句通过索引快速筛选出来一波数据，接着放到内存里，或者放在一个临时磁盘文件里，然后通过排序算法按照某个字段走一个排序，最后把排序好的数据返回。

但是这么搞通常速度有点慢，尤其是万一你要排序的数据量比较大的话，还不能用内存来排序，如果基于磁盘文件来排序，在MySQL里有一个术语，叫filesoft，这速度就比较慢了。

通常而言，尽量别这么搞，尤其是类似于select * from table order by xx1,xx2,xx3 limit 100这样的SQL语句，按照多个字段进行排序然后返回前100条数据，类似的语句其实常常见于分页SQL语句里，可能需要对表里的数据进行一定的排序，然后走一个limit拿出来制定部分的数据。

这种情况下，假设我们建立了一个INDEX(xx1,xx2,xx3)这样的一个联合索引，这个时候默认情况下在索引树里本身就是一次按照xx1,xx2,xx3三个字段的值去排序的，那么此时你在运行select * from table order by xx1,xx2,xx3 limit 100这样的SQL语句，显然是不用再临时磁盘文件里排序了，因为他要求的就是按照xx1,xx2,xx3三个字段来进行排序，在联合索引的索引树里都排序好了，直接按照索引树里的顺序，取前面100条数据就可以了。

但是你不能在SQL的order by语句既有升序又有降序，这样是不能用索引的。

当SQL里进行分组的时候，如何才能使用索引

假设你有一个类似select count(*) from table group by xxx的SQL语句，似乎看起来必须把你所有的数据放到一个临时磁盘文件里还有加上部分内存，去搞一个分组，按照指定字段的值分成一组一组的，接着对每一组都执行一个聚合函数，这个性能是非常差的，因为毕竟涉及大量的磁盘交互。

因为我们的索引树里默认都是按照指定的一些字段都排序好的，其实字段值相同的数据都是在一起的，假设要是走索引去执行分组后再聚合，那性能一定是比临时磁盘文件去执行好多了。

所以通常而言，对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开来，这样的话，其实就可以完美的运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数就可以了。

其实group by 和order by用上索引的原理和条件都差不多的，本质都是在group by 和order by之后的字段顺序和联合索引中最左侧开始的字段顺序一致，然后就可以充分利用索引树里完成排序的特性，快速的根据排序好的数据执行后续操作了。

这样就不需要针对杂乱无章的数据利用临时磁盘文件加上部分内存数据结构进行耗时耗力的现场排序和分组，那速度是极慢的。

所以平时我们设计表里的索引的时候，必须充分考虑到后续你SQL语句要怎么写，大概会根据哪些字段来进行where语句里的筛选和过滤，大概会根据哪些字段来进行排序和分组。然后再考虑好之后，就可以为表设计两三个常用的索引，覆盖常见的where筛选、order by 排序和group by 分组的需要，保证常见的SQL语句都可以用上索引，这样当系统真正跑起来，起码是不会有太大的查询性能问题了。

对于更新语句而言，其实最核心的就是三大问题，一个是你索引别太多，索引太多了，更新的时候维护很多索引树肯定是不行的，一个是可能会涉及到一些锁等待和死锁的问题，一个就是可能会涉及到MySQL连接池、写redo log文件之类的问题。

回表查询对性能的损害以及覆盖索引是什么？

一般我们自己建的索引不管是单列索引还是联合索引，其实一个索引就对应着一颗独立的索引B+树，索引B+树的节点仅仅包含了索引里的几个字段的值以及主键值。

即使我们根据索引树按照条件找到了需要的数据，那也仅仅是索引里的几个字段的值和主键值，万一你搞了一个select * 还需要很多其他的字段，那还得走一个回表操作，根据主键跑到主键的聚簇索引里找，聚簇索引的叶子节点是数据页，找到数据页里才能把一行数据的所有字段值提取出来。

假设你是类似select * from table order by xx1,xx2,xx3的语句，可能你就是得从联合索引的索引树里按照顺序取出来所有数据，接着对每一条数据都走一个主键的聚簇索引的查找，其实性能也是不高的。

有的时候MySQL的执行引擎甚至可能会认为，你要是类似select * from table order by xx1,xx2,xx3的语句，相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索引了，直接全表扫描了，这样还就扫描一个索引而已。

但是你如果要是select * from table order by xx1,xx2,xx3 limit 10这样的语句，那执行引擎就知道了，你先扫描联合索引的索引树拿到10条数据，接着对10条数据在聚簇索引里查找10次就可以了，那么还是会走联合索引的。

另外，覆盖索引其实不是一种索引，他就是一种基于索引查询的方式罢了，他的意思就是针对类似select xx1,xx2,xx3 from table order by xx1,xx2,xx3这样的语句，在这种情况下，你仅仅需要联合索引里的几个字段的值，那么其实就只要扫描联合索引的索引树就可以了，不需要回表去聚簇索引里找其他字段了，所以这个时候，需要的字段值直接在索引树里就能提取出来，不需要回表到聚簇索引，这种查询方式就是覆盖索引。

所以在写SQL语句的时候，一方面是你要注意一下也许你会用到联合索引，但是是否可能会导致大量的回表到聚簇索引，如果需要回表到聚簇索引的次数太多了，可能就直接给你做成全表扫描不走联合索引了。

一方面是尽可能还是在SQL里指定你仅仅需要的几个字段，不要搞一个select * 把所有字段都拿出来，甚至最好是直接走覆盖索引的方式，不要去回表到聚簇索引，即使真的要回表到聚簇索引，那也要尽可能用limit、where之类的语句限定一下回表到聚簇索引的次数，这样性能也会好一些。

设计索引的时候，一般要考虑哪些因素

• 设计的索引最好是让各个where、order by和group by后面跟的字段都是联合索引的最左侧开始的部分字段，这样他们都能用上索引。
• 尽量使用哪些技术比较大的字段，就是指比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
• 尽量是对那些字段类型比较小的列来设计索引，比如说tinyint之类的，因为他的字段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时在搜索的时候性能也会比较好一点。
• 设计索引别太多，建议两三个联合索引就应该覆盖掉你这个表的全部查询了。