关键字
覆盖索引、最左前缀索引、索引下推
0.引
我们简历一个表：
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'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
5-索引结构.png
现在，我们执行如下语句：
select * from T where k between 3 and 5
其执行流程如下：
- 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
- 再到 ID 索引树查到 ID=300 对应的 R3；
- 在 k 索引树取下一个值 k=5，取得 ID=500；
- 再回到 ID 索引树查到 ID=500 对应的 R4；
- 在 k 索引树取下一个值 k=6，不满足条件，循环结束。
在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
由于我们查询的数据只在主键索引上有，所以我们不得不回表。但是你也可以看出，回表是一个比较繁琐的操作，我们有没有方法，根据需求优化索引呢？
1.覆盖索引
依然是上面的例子，如果我们执行的语句是：
select ID from T where k between 3 and 5
由于在 k索引树 的叶子节点中包含主键 ID 的数据，所以这条语句不需要回表。
也就是说，在这个查询中，索引 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
很明显，身份证号码是市民的唯一标识，我们可以建立一个身份证号的索引，加快查找。
此时，如果有一个高频请求，要根据身份证号查询市民姓名，你有什么好方法吗？
答案是，建立一个 （身份证号、姓名）的联合索引。它可以在这个高频请求上用到覆盖索引，避免回表，从而减少语句执行时间。
2.最左前缀原则
B+树 这种索引结构，可以利用索引的“最左前缀”来定位记录
为了说明这个概念，我们用（name，age）这个联合索引来分析：
5-联合索引.jpg
你可以看到，索引项是按照索引定义中出现的字段顺序来排序的，即最左前缀（这有点像原地排序算法的典型应用？）
当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。
如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
面对这种情况，我们如何决定使用 （name，age） 还是使用 （age，name）呢？换句话说，如何安排索引内的字段顺序？
评估标准是：索引的复用能力，如果通过调整顺序，可以少维护一个索引，我们就优先考虑它
这句话难以理解，我试着用自己的理解解释：
- （name，age）的联合索引，因为最左前缀原则，实际上包含了一个隐藏的 name索引。
- 反过来，（age，name），实际上包含了 age索引。
- 此时，如果 name 是一个非常频繁的搜索字段，我们就可以考虑优先使用 （name，age）索引，这样我们就不再需要建立 name 索引了。
- 如果两个字段都频繁使用，你可以维护 （name，age） 和 age 索引。
- 当然，上一条的情况你也可以选择，比如单独建立一个 name索引 比单独建立 age索引 耗费更多空间（name字段更大）
3.索引下推
依然使用上面的例子：在市民表中，我们建立了（name，age）索引，同时又有以下请求：
查找姓张的所有10岁男孩：
mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
- 首先，MySQL可以使用（name，age）索引 和 最左前缀原则快速找到姓张的所有人。
-
然后，我们需要判断其它条件是否满足：
在 5.6 版本之前，MySQL 会一个个回表，找到数据行，然后对比字段值：
5-无索引下推.jpg
在 5.6 版本之后，MySQL 引入了索引下推优化，它会先在索引中做判断，过滤掉不符合条件的记录，然后再回表：
5-有索引下推.jpg
- 可以看到，索引下推优化节省了两次查询操作。
小结
- 这一节，我们讨论了数据库索引的使用技巧，包括覆盖索引、前缀索引、索引下推。
- 你可以看到，使用这些技巧，我们可以减少对数据库的访问次数。
- 这是设计数据库时非常重要的原则。
思考题
实际上主键索引也是可以使用多个字段的。DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：
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;
公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。
但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？
同事告诉他，是因为他们的业务里面有这样的两种语句：
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;
我给你的问题是，这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？
解答
表记录
–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d
主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。
索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键
–c--|–a--|–主键部分b-- （注意，这里不是 ab，而是只有 b）
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3
这个跟索引 c 的数据是一模一样的。
索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键
–c--|–b--|–主键部分a-- （同上）
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2
所以，结论是 ca 可以去掉，cb 需要保留。
以上就是索引的内容，希望对你有启发。
注：本文章的主要内容来自我对极客时间app的《MySQL实战45讲》专栏的总结，我使用了大量的原文、代码和截图，如果想要了解具体内容，可以前往极客时间
网友评论