05 | 深入浅出索引（下）

覆盖索引

一个查询操作，如果索引 k 已经包含我们要获取的所有字段，可以少一次回表操作，我们称这种情况为覆盖索引

最左前缀

MySQL数据模型是B+树，索引项在B+树里的存储是按照索引定义里面字段出现的顺序来排序的，只要满足索引的最左前缀(可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符)，就可以利用索引来加速检索

建立联合索引的时候，如何安排字段顺序

• 根据最左前缀规则，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
• 空间最小原则：如果查询中既有同时筛选 a, b 的情况，又有分别筛选 a，b 的情况，且 a 的字段长度 > b 的字段长度，应该建立 (a, b) 和 b 这两个索引，而不是 (b, a) 和 a 这两个索引

索引下推

对于联合索引 (a, b, c)，查询时筛选 a=1， c!=1，按最左前缀原则仅能使用到索引 a，每次获取到一行a=1的数据就需要回表，再做c!=1的判断。MySQL5.6引入来索引下推，在回表前会先做 c!=1的判断，不满足直接跳过，从而减少来回表次数，加快来检索

问题讨论

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
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

评论区讨论

关于InnoDB对索引的组织

InnoDB会把主键字段放到索引定义字段后面，当然同时也会去重。

所以当主键是(a,b)的时候： 定义为c的索引，实际上是（c,a,b) 定义为(c,a)的索引，实际上是(c,a,b) 定义为(c,b）的索引，实际上是（c,b,a)