深入浅出索引（下）

1. 索引树和回表查询的次数

首先给定一个数据库表：

mysql>
    create table T(
    ID int primary key,
    k int not null default 0,
    s varchar(16) not null default '',
    index k(k)
)engine=InnoDB;

insert into T values(100,1,'aa'),(200,2,'bb'),(500,5,'ee'),(600,6,'ff');

我们就可以得到数据表T对应的索引结构：

那么我们如果执行下面这条SQL语句，需要进行多少次索引树搜索，会扫描多少行？

mysql>
    select * from T where k between 3 and 5

下面我们来分析这条SQL语句的执行流程：

1. 在k索引树上找到k=3的记录，取得ID=300
2. 再到ID索引树查到ID=300对应的R3
3. 在k索引树取下一个值5，取得ID=500
4. 再回到ID索引树查到ID=500对应的R4
5. 在k索引树取下一个值6，不满足条件，退出循环。

从上面的过程中可以观察到，读了k索引树3次（步骤1、3、5），回表2次（步骤2、4）。
但是对于MySQL的Server层来说，它只从引擎中拿到两条记录，因此MySQL默认的扫描行数是2。

2. 覆盖索引

如果执行下面的语句：

mysql>
    select ID from T where k between 3 and 5

因为语句只是查询ID，而ID已经在k索引树上了，所以可以直接查到结果，不需要回表。

在这个查询里面，索引k已经覆盖了我们的查询的请求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以覆盖索引是常用的一个优化手段。

又如下面这个例子，建立(name,age)的联合索引。当利用名字取查询对应的年龄时，就可以走这个索引。比如查询“张三”的年龄，根据索引找到名字为张三的叶子节点，节点内部就有对应的年龄，这样可以减少一次回表操作。

接下来思考一个问题：在一个市民信息表中，是否有必要将身份证号和名字建立联合索引？

这个问题需要根据实际情况来考虑。

1. 如果有一个高频请求要根据身份证查询姓名，那这个身份证号和名字的联合索引就有意义。因为能够避免回表查询
2. 但是，索引的维护也总是有代价的

3. 最左前缀原则

最左前缀匹配匹配原则就是最左边的优先，优先走最左边的索引。

最左前缀原则的原理

MySQL建立多列索引（联合索引）有最左前缀匹配的原则，即最左优先：
如果有一个2列的索引（a,b），则实际对(a),(a,b)建立了索引
如果有一个3列的索引(a,b,c)，则实际对(a),(a,b),(a,b,c)建立了索引

比较常见的例子有以下几个，方便理解：

事先建立索引(a,b,c)
select * from table where a = '1'; --符合前缀匹配，会走索引
select * from table where a = '1' and b = '2'; --符合前缀匹配，会走索引
select * from table where a = '1' and b = '2' and c = '3'; --符合前缀匹配，会走索引
select * from table where a = '1' and b = '2' and c = '3'; --优化器会调整顺序，还是符合前缀匹配，会走索引

select * from table where c = '1'; --不符合前缀匹配原则，不会走索引
select * from table where b = '1' and c = '2'; -- 不符合前缀匹配原则，不会走索引

但是有些情况下，最左匹配原则会出现失效的情况，例如下面这个语句，会出现，a与b会走索引，但是c不会走索引：

select * from table where a = 2 and b > 1000 and c = 'yy'

对于上面这种类似的语句,mysql会一直向右匹配，直到碰到范围查询（>,<,between,like）就停止匹配。
具体的原因是因为：

如下表所示：索引(a,b,c)是先基于a排序，再基于b排序，最后基于c排序

因此索引(a,b,c)是一个，以a字段绝对有序而b与c相对有序的B+树，存储引擎可以通过二分查找定位到a=2的
数据，b在a=2的情况下是有序的（所以b的有序是相对的），依然可以通过二分查找来实现。但这些b字段有可
能有很多个不同的值，所以c字段是无序的，因此就不走索引了，直接进行扫描。

a	b	c
1	1	4
2	1	3
2	2	4
3	1	1

4. 索引下推

前面说到会有些情况无法符合最左前缀匹配原则，那么怎么处理呢？

以下面的语句为例，检索出“名字第一个字是张，且年龄是10岁的所有孩子”

事先建立索引(name,age)

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

在MySQL 5.6之前，对于age是没法走索引的， 只能通过ID进行回表。那就如下图需要四次回表。

但是在MySQL 5.6之后，引入了索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

有了索引下推，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录就直接跳过，可以减少回表次数。如下图所示：

来源：自己整理的MySQL实战45讲笔记