【基础篇】深入浅出索引（下）

背景

针对下面这个表：

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'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

如果执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

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

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

在这个过程中，可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引

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

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。（需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2）

最左前缀

为了直观地说明这个概念，我们用（name，age）这个联合索引来分析。

当使用 where name = ‘张三’ 或者 where name like ‘张%’ 的时候，都能够用上索引（name，age）。也即只需要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

**如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。**当需要同事维护两个索引(a,b) 和 (b) 的时候，就需要考虑空间问题。比如上面的例子中，因为 name 字段是比 age 字段大的，所以创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。（否则一个（age,name) 的联合索引和一个 (name) 的单字段索引，在空间上耗费更大。非主键索引的值都是主键，但是key 的大小有区别，选更小的更能节省空间。）

索引下推优化（index condition pushdown)

考虑以下 sql：

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

这个语句会先查找索引树，使用联合索引（name，age），找到第一个记录 ID3。

然后，在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

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

上面两个图里面，每一个虚线箭头表示回表一次。MySQL 5.6 之前，每条记录都需要进行回表，因为他并不会使用索引中的 age 字段进行过滤。所以，他需要 4 次回表。

而 MySQL 5.6 之后，就会先判断索引中的 age 是否等于 10，这样就减少了 2 次回表操作。

课后思考

CREATE TABLE `geek` (
 `a` int(11) NOT NULL,
 `b` int(11) NOT NULL,
 `c` int(11) NOT NULL,
 `d` int(11) NOT NULL,
 PRIMARY KEY (`a`,`b`),
 KEY `c` (`c`),
 KEY `ca` (`c`,`a`),
 KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

-- 使用场景：
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

**问题：**为了这两个查询模式，这两个索引（ca，cb）是否都是必须的？为什么呢？
回答：

1. 首先说结论：ca 不需要，cb 可以需要；
2. 因为有了 c 索引，按这个索引查询匹配之后，会查询主键索引，这里已经有 ab 字段了（a 这里会保证有序：主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。），所以不需要增加 ca 索引；
3. 但是因为 b 没有单独的索引，我们需要先按 c 排序，再按 b 排序。现有的 ab，c，ca 索引不能满足（他们的顺序是 c -> a ->b），所以需要 cb 这个索引。
4. 当然如果，cb 组合的重复率很低，甚至说一个 c 只会对应一个 b，那么这个索引就没必要了。