《MySQL学习》 索引 下 覆盖索引，MRR，联合索引

一. 覆盖索引

有一张表T1，它的建表语句如下

mysql> create table T1 (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T1 values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

如果我们执行 select * from T1 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索引树上查找了三次记录，在ID索引上匹配了两次记录，在ID索引上的操作，就是回表操作。

可能你会很奇怪，为什么不在K索引树上一次把所有满足条件的k找出去，再去ID索引树上找值呢？

因为 k 索引是按照k的顺序排序的，对于ID索引树来说，它是无序的。 但MySQL也有相应的优化

MRR

 set optimizer_switch='mrr=on,mrr_cost_based=off';

执行如上SQL命令后，将开启 MRR

MRR，全称「Multi-Range Read Optimization」。

简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

更多关于MRR的知识可以点击 MySQL 的 MRR 到底是什么？ - 知乎 (zhihu.com) 查看

先借用两张图

未开启MRR前

开启MRR后

可以看到，开启MRR后，会使用到 一块rowid buffer的内存，将主键索引ID在内存中排好序，然后再有顺序的去聚簇索引中查询数据。相比开启MRR前，将 无序的磁盘随机读 变成了有序的磁盘顺序读，从而提高了磁盘效率

虽然开启了MRR后，能提高磁盘查询效率，但始终还是得回表。有没有什么方法不需要回表呢？

有，索引下推。

如果我们将上述SQL语句语句改成

select ID from T1 where k between 0 and 5

可以看到出现了 Using index 也就是覆盖索引

由于我们需要的仅仅是ID字段，而id在二级索引上也是是作为叶子节点的数据存储的，因此是不需要再次回到一级索引查找数据。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

有一个地方需要注意，虽然我们只获取到两条数据，但K只是一个普通索引，因此还需要再读取一条记录判断是否满足查询条件，因为下一条等于6已经不满足了，所有读取了三行记录，返回了两条记录

二. 联合索引

如何建立合适的联合索引

原则一 ： 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

我们都知道联合索引是需要遵循最左前缀原则的，如果表T上需要建立name和age字段的联合索引 index_name_age。而我们的业务中也需要通过age字段去查找数据，难道再创建一个age索引吗？

我们可以调整联合索引的顺序 改成 index_age_name，当使用age字段查询数据时，也是满足最左前置原则的，索引也是一种特殊的数据结构，也需要占用磁盘空间的，能少则少

但是如果 age 和name 两个字段都需要建立索引怎么办 ？ 此时我们可以选择将小的字段单独建立索引，大的字段放在联合索引开头 比如改成 index_name_age 和 age索引

索引下推

索引下推的定义是 索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

例如图一是没有用到索引下推，回表4次

图二用到索引下推 回表两次