那索引下推呢 最左前缀有手就会

联结索引的最左前缀准则属于面试高频题，想必大局部同窗都知道一些，但是，那些不合乎最左前缀的局部，会怎样样呢(索引下推)

索引下推不算高频题，知道的同窗应该不是很多(不过并不代表有啥难度哈，挺便捷的)，学起来装波杯

老规矩，背诵版在文末。点击阅读原文可以中转我收录整顿的各大厂面试真题

引子

看上方这张用户表，蕴含主键 id、身份证号 id_card、姓名 name、年龄 age和性别 sex，并且在 id_card上建设了辅佐索引(普通索引/非汇集索引)

假设如今有一个高频恳求，要依据市民的身份证号查问他的姓名：

妇孺皆知，这会造成回表查问，经过 id_card 这棵辅佐索引树只能找到主键 id，而后须要再回到主键索引(汇集索引)树上依据主键 id 查找相应的name。

所以，这个时刻，咱们可以建设一个 (id_card, name)的联结索引来启动提升，关于这条语句来说，也就是笼罩索引，在这个高频恳求上用到笼罩索引，不再须要回表查整行记载，大幅缩小了语句的口头期间。

不过，索引字段的保养总是有代价的，假设为每一种查问都设计一个联结索引，索引是不是太多了?反过去说，独自为一个不频繁的恳求创立一个联结索引是不是有点糜费了。因此在建设冗余索引来允许笼罩索引时就须要咱们去做出一些掂量思索了。

详细来说，咱们应该怎样做呢?

最左前缀准则

B+ 树这种索引结构，可以应用联结索引的 “最左前缀” 来定位记载。

何为联结索引的最左前缀准则?

从实质过去说，联结索引也是一棵 B+ 树，不同的是联结索引的键值的数量不是 1，而是大于等于2。咱们来看下两个整型列组成的联结索引，假定两个键值的称号区分为 a、b。

从图中可以看到多个键值的 B+ 树状况，键值都是排序的。经过叶子节点可以逻辑上顺序读取一切数据，就上方图中所示，即为(1，1)、(1、2)、(2、1)、(2、4)、(3、1)、(3、2)，数据是依照 (a, b) 的顺序启动寄存。

?? 这里 “**键值都是排好序” 的这种说法或许会让大伙很纳闷，**似乎只要 a 列是排序的，b 列并没有排序啊。

留意!这里的排序，意思是确定了第一个键，关于第一个键相反的记载来说，查问的结果是对第二个键启动了排序。

这也是**经常使用联结索引的第二个好处，即曾经对第二个键值启动了排序解决，可以防止多一次性排序操作。**举个例子：有些运行程序都须要查问某个用户的购物状况，并依照期间启动排序，取出最近n 次的购置记载，这时经常使用联结索引就可以防止多一次性排序操作，由于索引自身在叶子节点曾经排序了。

更进一步说，假定有联结索引(a，b，c)，下列语句可以间接经过联结索引获取结果：

但是关于上方的语句，联结索引不能间接获取结果，其还须要口头一次性排序操作，由于索引 (a，c) 并未排序：

说了这么多，如同和最左前缀啥相关也没有啊

思索下，关于上方这条语句，能否用到联结索引(a, b)?

这个当然没疑问。

那关于 a 列的独自查问，能否用到联结索引(a, b)?

当然也可以，咱们不是说了，a 列是曾经排好序的。

但是关于 b 列的独自查问则不能经常使用联结索引(a, b)!

由于把叶子节点中的 b 值独自拎进去看它不是有序的：1、2、1、4、1、2，因此关于 b 的查问是经常使用不到 (a，b) 这个联结索引的。

雷同的情理，关于(a, b, c)联结索引来说，查问 (a, b) 可以用到这个联结索引，但是查问 (b, c) 就没方法经常使用这个联结索引，由于 b 和c 列的有序性都是附丽于 a 列的存在的。

This，就是联结索引的最左前缀准则，只需查问的是联结索引的最左 N 个字段，就可以应用该联结索引来减速查问。

基于上方对最左前缀索引的说明以及用户表的例子，咱们来探讨一个疑问：在建设联结索引的时刻，如何布置索引内的字段顺序?

有两点准则。

首先，第一准则，假设经过调整顺序，可以少保养一个索引，那么这个字段顺序往往就是须要优先思索驳回的

很好了解，当曾经有了 (a,b) 这个联结索引后，普通就不须要独自在 a 上建设索引了。

那么，再思索一个疑问：假设既有联结查问 (a,b)，又有基于 a、b 各自的查问呢?

显然，假设查问条件外面只要 b 的语句，是不可经常使用 (a,b) 这个联结索引的，这时刻你不得不保养另外一个 b 列的索引，也就是说你须要同时保养(a,b)、(b) 这两个索引。

举个用户表例子，有这样三个高频查问需求：

这个时刻，咱们有两种索引建设的选用：

怎样选?

这种场景下，咱们要思索的准则就是空间。

显然，name 字段是要比 age 字段大的，所以，第二种选用占用的空间要小于第一种选用，介绍大伙儿经常使用第二种选用：联结索引 (name, age) +单字段索引 (age)

索引下推

最左前缀可以用于在索引中定位记载，那么，那些不合乎最左前缀的局部，会怎样样呢?

以用户表的联结索引(name, age)为例，假定如今有一个需求，找出一切姓 “张” 并且 20 岁的男子：

《高性能 MySQL》 书中提到：关于联结索引，假设查问中有某个列的范围查问，则其左边一切列都不可经常使用索引启动极速定位。

所以关于这条语句来说，其实并不能齐全踩中 (name, age) 这个联结索引，他只能踩到 name。

详细来说，这个语句在搜查(name，age)的联结索引树的时刻，并不会去看 age 的值，只是按顺序把 “name 第一个字是张”的记载一条条取进去，而后开局回表，到主键索引上找出数据行，再一个一个判别其余条件能否满足。从下图可以看进去，须要回表 3 次。

这是 MySQL 5.6 之前的做法，便捷总结，当启动索引查问时，首先依据索引来查找记载，而后再依据 where 条件来过滤记载。

而 MySQL 5.6 开局，数据库在取出索引的同时，会依据 where 条件间接过滤掉不满足条件的记载，缩小回表次数。这就是 索引下推 (IndexCondition Pushdown,ICP) ，一种依据索引启动查问的提升形式。

从图中可以看进去，InnoDB 在 (name,age) 索引外部就判别了 age 能否等于 20，关于不等于 20 的记载，间接判别并跳过，所以只须要对ID1 这条记载启动回表判别就可以了。

面试官：讲一下联结索引的最左前缀准则，为什么得最左婚配，不依照这个来为什么失效?

小牛肉：最左前缀准则就是只需查问的是联结索引的最左 N 个字段，就可以应用该联结索引来减速查问。

不依照最左婚配来为什么失效，其要素就在于联结索引的 B+ 树中的键值是排好序的。不过，这里指的排好序，其实是相对的，举个例子，有 (a, b, c)联结索引，a 首先是排序好的，而 b 列是在 a 列排序的基础上做的排序，雷同的 c 是在 a,b 有序的基础上做的排序。所以说，假设有 where a =xxx order by b = xxx 这种恳求的话，是可以间接在这颗联结索引树上查进去的，不用对 b 列启动额外的排序;而假设是 where a = xxxorder by c = xxx 这种恳求的话，还须要额外对 c 列启动一次性排序才行。

另外，假设有对 a,b,c 的联结条件查问的话，并且 a 是含糊婚配或许说是范围查问的话，其实并不能齐全踩中联结索引(a,b,c)，a列左边的一切列都不可经常使用索引启动极速定位了。所以这个时刻就须要启动回表判别。也就是说数据库会首先依据索引来查找记载，而后再依据 where条件来过滤记载。

不过在 MySQL 5.6 中允许了索引下推 ICP，数据库在取出索引的同时，会依据 where 条件间接过滤掉不满足条件的记载，缩小回表次数。