MySQL之SQL优化篇（五）：索引查询优化

MySQL之SQL优化篇（五）：索引查询优化

  索引类似于一本书的目录，在数据到达一定量时，创建合理的索引并使用索引能有效提高查询的效率。

上一节：MySQL之SQL优化篇（四）：schema及数据类型优化

前言

  索引，类似于书本的目录，用于快速查找数据。在数据量很少的时候，有没有索引对于查询来说，影响并不明显，当数据量急剧膨胀，索引的作用（加快查询效率）就凸显出来了。不过，当数据量实在太大时，维护索引的代价也会随之增加，此时可以考虑分区，关于分区请参考本系列文章有关分区的介绍。在MySQL中，索引的实现位于存储引擎层，不同的存储引擎采用的索引结构是不尽相同的，这里我们并不想探讨不同存储引擎的区别，一般来说，如果没有特别说明，我们所说的索引即是InnoDB（默认存储引擎）的索引。
  了解了索引是什么，接下来就是如何创建、使用和维护索引。参照网上让SQL走索引的经验法则，有时候确实能走索引，可用完了发现心里还是没底。这只是使用层面，对于创建和维护，心里就更没底了。就我个人而言，在没接触这些知识之前，心里通常会有如下疑问（也许你也有）：
  （1）索引的结构是怎样的？
  （2）where条件的顺序为什么会影响索引的使用？
  （3）如何分析并创建合适的索引？
  本文将针对索引使用过程中的痛点，让你遇到索引时不再害怕和迷惘！！！

InnoDB索引结构

B+树

  B+树除了叶子节点存储数据外，其余节点只是存储数据索引，用于找到数据所在范围。由于数据存储在叶子节点，每次查找都要查找到叶子节点才算结束，性能稳定。如果是范围查找，只需要找到索引上下限，然后遍历叶子节点形成的链表即可。另外，可以发现，叶子节点是按照键（定义的索引）的某种顺序排列的，利用这点可以优化order by操作。

  根据索引结构，我们可以得出在索引使用时需要注意的一些点：

注意点	说明
使用组合索引，顺序很重要	从图中可以很直观地看出，该组合索引类似(name, age)这种形式，在查找的时候如果写成where age = 6 and name = ‘ace’，则会先查年龄。但是，先按照年龄就用不到索引了，不信可以自己去图中按年龄找一下，无奈只能按链表顺序查找。
为什么like '%ace’不能走索引	B+树是按照字符串的某种规则进行排序的，默认是最左前缀，如果使用后缀匹配的话，肯定不起作用，此时会扫描链表。
where条件中，范围查询后面的索引不生效	比如查找 where name like ‘ab%’ and age = 8, 由于已经通过第一列索引name找到了范围，此时已经到链表上去了，后面的age就用不到索引了

聚簇索引

  首先，聚簇（cù）索引并不是某种索引类型，它也是基于B+树的，只不过在它的叶子节点中，存储了键所对应行的所有数据。众所周知，一张表应该有唯一的主键，因此，一张表就只能有唯一的聚簇索引（没必要把数据放在很多地方），下面示例很好地展示了聚簇索引。
  数据库表：user

id(主键)	name	age
1	acd	18
2	abd	20
3	afe	19
4	ace	17
5	abc	22
6	aed	15

  聚簇索引：

  可以看到，数据存放在主键所对应的聚簇索引上，在当前表的其他索引中，叶子节点存放的是主键值，而不是数据存放的地址，因此，如果使用其他索引来查询，会先去其他索引找到叶子节点对应的主键ID，然后再去聚簇索引取数据。
  例如，在user表上name列建立索引，结构如下：

覆盖索引

  如果一个索引包含（或者说覆盖）所有需要查询的字段的值，那么该索引就可以称作“覆盖索引”。显然，是不是覆盖索引取决于其他索引是否提供了我们需要的所有列值。在聚簇索引里保存了主键对应的所有列数据，上图中根据user表的name字段创建的索引，虽然没有所有列的数据，但是它也保存了一份name数据，如果在查询中只需要name列(select name from user where name like ‘ab%’)，那么当前索引就能提供所有数据，此时不需要根据ID去聚簇索引中再找一遍。这就是为什么建议不要写成select *的原因所在。

小结

  下图简单表示了一次查询中使用索引的情况，其中：组合索引（条件1，条件2，条件3）

创建合理的索引

前缀索引

  有test_prefix表结构如下：

  创建前缀索引很简单，只需要下面的一句SQL：

alter table test_prefix add index(content(7));

  创建此索引的关键并不在于语法，而是取的前缀长度，到底怎么才是符合content的最佳前缀长度呢？首先，需要理解一下“选择性”的概念。索引的选择性指的是，不重复的索引值和数据表的记录总数之比，0-1之间。id主键列的选择性为1，因为不重复的值数量和表数据总数量一致。因此，在创建前缀索引时，应该先计算不同长度选择性，然后选择最合适的长度。下面是一个选择最佳前缀长度的例子，在长度为4的时候选择性为1，此时最佳:

# 计算最短长度
select min(length(content)) from spring_cloud.test_prefix;
# 计算选择性
SELECT count(distinct left(content, 1))/count(*) as str1,
count(distinct left(content, 2))/count(*) as str2,
count(distinct left(content, 3))/count(*) as str3,
count(distinct left(content, 4))/count(*) as str4,
count(distinct left(content, 5))/count(*) as str5,
count(distinct left(content, 6))/count(*) as str6,
count(distinct left(content, 7))/count(*) as str7,
count(distinct left(content, 8))/count(*) as str8
FROM spring_cloud.test_prefix;

  结果：

多列（组合）索引

  并没有哪条法则能够确定组合索引怎么建才是最佳的，对于此类索引的建立，应该结合当前表的SQL中，where后面最多使用的列，对这些列建立索引。有时候并不是将选择性最高的列放最前面就是最好的，而是需要实际测试一下，因为我们面对的不是一两条查询那么简单，而是在所有查询中找到一个最佳平衡点。

利用索引排序

  通过前面的介绍已经知道，索引已经按照某种规则进行排序了，字符串通过字符串校验规则、数字通过大小等。如果需要根据索引列进行排序，那么只需要按顺序扫描索引即可。对于字符串来说，如果需要倒序排列，在存储时可以考虑将字符串反转。在使用组合索引排序时，也需要按照组合索引顺序从左到右，如果order by里用不到开头的列，那么可以在where条件中指定值，在order by里接着后面的列写即可。

# 在user表上有(name, age)组合索引
select name, age from user where name ='abd' order by age
# 反例，下面这句就不走索引
select name, age from user order by age

维护索引

  建完索引之后，需要对索引进行维护。可用的方法如下：

# 修复表命令
repair table

# 修改存储引擎为当前存储引擎
alter table tb_xxx engine=innodb

总结

  至此，关于MySQL InnoDB引擎的索引结构读者应该已经有了一个大概的认识，对于索引的结构是怎样的？where条件的顺序为什么会影响索引的使用？如何分析并创建合适的索引？等问题应该也有了自己的理解。笔者才疏学浅，理解得比较浅薄，但对于一般的SQL优化任务，应该是能对付了。

下一节：MySQL之SQL优化篇（六）：其他查询优化