MySQL之索引系列（二）

有了索引一定会增加查询速度么，如何利用好索引呢

现在有以下表结构及数据，执行 select * from T between 3 and 5,需要执行几次树的搜索，会扫描多少行?
图1 表的结构：

图2 表中数据：

图3 InnoDB的索引组织结构：

由图1及图3可知，ID为主键索引，k为非主键索引，K的叶子节点值为主键ID。
以下是语句的执行过程：

1. 在k索引树查找k=3的记录，得到ID=300；
2. 根据ID=300查找对应的R3；
3. 在k索引树查找下一个k=5的记录，得到ID=500；
4. 根据ID=500找到R4；
5. 在k索引树找下一个k=6，不满足，退出。

从非主键索引回到主键索引树搜索的过程，称之为回表。以上过程中，读了k索引树的三条记录（步骤1、3、5），回表了两次（步骤2、4）。 在引擎内部通过索引k上其实是读了三个记录：k=3、4、5,但是对于MySQL的server层来说，他在找引擎拿到了两条记录(具体查找过程中server与引擎的执行过程见一条SQL查询语句的执行过程 )，因此MySQL认为扫描行数为2。具体如何查看扫描行数后面的会介绍。
了解了回表的过程，下面来看如何优化索引来避免回表

覆盖索引

上文执行的SQL语句如果是 select ID from T where k between 3 and 5，只是请求ID值，那么这个值已经存在于k索引树上，已经可以直接提供结果，不需要回表。也就是说，新的SQL中索引k已经 "覆盖了" 语句的查询需求，以上称之为覆盖索引。
索引覆盖可以减少树的搜索次数，以此来提升查询性能，所以使用覆盖索引是常见的性能优化手段。

基于覆盖索引，来看一个新的例子：
有一个用户表，定义如下：

CREATE TABLE `tuser` (
`id` INT ( 11 ) NOT NULL,
`id_card` VARCHAR ( 32 ) DEFAULT NULL,
`name` VARCHAR ( 32 ) DEFAULT NULL,
`age` INT ( 11 ) DEFAULT NULL,
`ismale` TINYINT ( 1 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `id_card` ( `id_card` ),
KEY `name_age` ( `name`, `age` ) 
) ENGINE = INNODB
复制代码

已知通过身份证号是唯一标识，为了满足根据身份证号查到一个用户的所有信息，只需要对身份证号建立索引就够了。在建立一个(id_Card,name)的联合索引，究竟有没有必要？

如果现在有这样一个高频请求：根据身份证号找到用户的姓名，那么这个联合索引的意义就体现出来了，这个高频的请求能够用到覆盖索引，因为在(id_Card,name)这个联合索引树上直接能根据身份证号查到姓名，不用在拿到主键ID后回表查找整行记录，减少语句的执行时间。 覆盖索引的目的就是尽量能够一次查到需要数据，避免有回表过程。

最左前缀原则

按照刚刚的需求，为了根据身份证号查到姓名，需要创建(id_card,name)联合索引，如果现在新的需求需要根据身份证号查到家庭住址，总不能再添加一个(id_card,addr)联合索引，为了每一种查询都设计一个索引，显然是不现实的。 这时候应该怎么做呢？
结论：B+树这种索引结构，可以利用索引的"最左前缀"来定位记录。

以联合索引("name",age)为例
图4 (name,age)索引示意图：

由上图可知索引项是按照索引定义的字段顺序排序的。

如果要查询 "name = '张四'" 的所有结果时，会快速定位到D4,然后向后遍历到D5结束，返回结果。
如果想查找所有姓张的结果，则条件为"where name like '张%'" ,此时还是可以用上这个联合索引，定位到D3，然后向后遍历，直到D5结束，返回结果。

由上面两个查询可知，只要满足最左边的N个字段或者M个字符，就可以用这个索引来查询。

以上结论又引发一个新的问题：考虑到最左前缀原则，建立联合索引时，内部的字段顺序该如何安排？

1. 如果已经存在(a,b)联合索引，则不需要对a字段建立索引。如果能够调整联合索引的顺序，来减少一个索引，name这个顺序应该被优先考虑，以此来提高索引的复用能力。 回到本节开头的问题：没有必要为(id_card,addr)建一条索引。可以复用已经存在高频使用的(id_card,name)联合索引，在根据id_card查找地址时，也能够根据最左前缀原则匹配到(id_card,name)索引，以此来增加查找速度。

2. 对于id_card和name字段，可以建立三个索引搜索树： 对id_card和name分别建立索引、对(id_card，name)建立联合索引。根据上一条原则，可以通过调整联合索引的字段顺序来减少一个索引，即保留"(id_card,name)和name索引"或者"保留(name,id_card)和id_card索引"。这时需要考虑空间的占用情况了，显然id_card要比name字段大，那就尽量复用id_card，建议使用(id_card,name)联合索引和name单字节索引。

索引下推

还是以上图为例，分析以下SQL执行过程

select * from tuser where name like '张%' and age = 23;    
复制代码

• 在MySQL5.6之前，通过最左前缀原则定位到D3，然后从D3开始一个一个回表，到主键索引树上找到对应行对比age字段的值，直到D5结束，不能匹配到'张%'后退出查询，以上共回表3次。虽然比遍历全表快，但是还是做了多次无用的回表操作。

• 在MySQL5.6及以后，引入了索引下推优化(index condition pushdown),在对联合索引遍历的过程中，根据索引中包含的字段对查询语句的其他条件进行判断，以此过滤不满足条件的记录，减少回表次数。对于上述例子来说，就是innoDB在联合索引搜索树根据最左前缀定位到D3，接着判断age字段不等于23，继续向后遍历，D4中name符合"张%"，但age不等于23，继续直到D5，name与age都符合，这时才会回表。以上过程只需要回表1次。

总结

三种优化方式：

• 覆盖索引
• 最左前缀原则
• 索引下推
  以上三种方式都有同一个原则，也是数据库设计的重要原则之一：尽量少地访问资源达到同样的效果

查询语句中where里面顺序和联合索引顺序不一致，优化器会自动做优化。

---

本文为极客时间《MySQL实战45讲》 的学习笔记，其中含有部分原文，如有侵权行为请联系我立刻删除
再次感谢丁奇大佬
第一节：一条SQL查询语句的执行过程