InnoDB索引模型（下）

在下面的表中，执行select * from T where k between 3 and 5,需要执行几次树的搜索操作，会扫描多少行？

这条SQL查询语句的执行流程为

1. 在A索引树上找到A=3的记录，取得ID=300

2. 再到ID索引树查到ID=300对应的A3

3. 再回到A索引数取下一个值5，取得ID=600

4. 再回到ID索引树查到ID=600对应的A5

5. 在K索引树取下一个值A=6,不满足条件，循环结束

在这个过程中，回到主键索引树搜索的过程，为回表。这个例子中查询结果所需要的数据只在主键索引上有，所以不得不回表，那么，有没有可能经过索引优化，避免回表的过程呢？

覆盖索引

1. 如果执行的语句为select id from T where k between 3 and 5 这时只需要查询Id的值，而ID的值已经在A索引树上了，因此直接提供查询结果，不需要回表，在这个查询里面，索引 A已经“覆盖”了我们的查询需求，我们称为覆盖索引。

2. 由于覆盖索引减少了树的搜索次数，性能显著提升，所以使用覆盖索引是一个常用的性能优化手段。

栗子：

学生表定义：

1. 学号是学生的唯一标识，如果有根据学号查询学生的信息，只要在学号这个字段上建立索引就够了，在建立一个（学号，姓名）的联合索引，是不是有点浪费空间？

2. B+树这种索引结构，可以利用索引的最左前缀，来定位记录。

下面用(name,age)这个联合索引来分析

1. 可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

2. 当查到所有名字是张三的人时，可以快速定位到ID4，饭后向后遍历得到所有需要的结果。

3. 当你查到所有名字第一个字为张的人，SQL语句为‘张%’,也能用上这个索引

4. 只要满足最左前缀，就可以利用索引加速检索，这个前缀索引可以使联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

在建立联合索引的时候，如何安排索引内的字符顺序？

1. 当有(a,b)两个联合索引后，一般就不需要单独在a上建立索引了，因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

索引下推（MySQL 5.6 引入）

1. 我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

   select * from tuser where name like '张 %' and age=10 and ismale=1

   有索引下推：

   
   

   

   无索引下推：

   

在图 3 和 4 这两个图里面，每一个虚线箭头表示回表一次。

    图 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取回表。因此，需要回表 4 次。

    图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表2次