联合索引的最左前缀原则属于面试高频题，想必大部分同学都知道一些，但是，那些不符合最左前缀的部分，会怎么样呢（索引下推）
索引下推不算高频题，知道的同学应该不是很多（不过并不代表有啥难度哈，挺简单的），学起来装波杯
老规矩，背诵版在文末。点击 大厂面试火箭计划 可以直达我收录整理的各大厂面试真题
引子
看下面这张用户表，包含主键 id、身份证号 id_card、姓名 name、年龄 age和性别 sex，并且在 id_card 上建立了辅助索引（普通索引/非聚集索引）
如果现在有一个 高频请求，要根据市民的身份证号查询他的姓名：
select name from user where id_card = xxx;
众所周知，这会导致回表查询，通过 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），下列语句可以直接通过联合索引得到结果：
SELECT ... FROM TABLE WHERE a=xxx ORDER BY bSELECT ... FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c
但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次排序操作，因为索引 (a，c) 并未排序：
SELECT ... FROM TABLE WHERE a=xxx ORDER BY c
说了这么多，好像和最左前缀啥关系也没有啊
考虑下，对于下面这条语句，能否用到联合索引（a, b）？
select * from table where a = XXX and b= XXX;
这个当然没问题。
那对于 a 列的单独查询，能否用到联合索引（a, b）？
select * from table where a = XXX;
当然也可以，我们不是说了，a 列是已经排好序的
但是对于 b 列的单独查询则不能使用联合索引（a, b）！
select * from table where b = XXX;
因为 把叶子节点中的 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 查询 id：
select id from user where name = xxx; - 根据 age 查询 id：
select id from user where age= xxx; - 根据 name 和 age 查询 id：
select id from user where name = xxx and age = xxx;
这个时候，我们有两种索引建立的选择：
- 联合索引 (age, name) + 单字段索引 (name)
- 联合索引 (name, age) + 单字段索引 (age)
怎么选？
这种场景下，我们要考虑的原则就是 空间。
显然，name 字段是要比 age 字段大的，所以，第二种选择占用的空间要小于第一种选择，推荐大伙儿使用第二种选择：联合索引 (name, age) + 单字段索引 (age)
索引下推
最左前缀可以用于在索引中定位记录，那么，那些不符合最左前缀的部分，会怎么样呢？
以用户表的联合索引（name, age）为例，假设现在有一个需求，找出所有姓 “张” 并且 20 岁的男性：
select * from tuser where name like '张%' and age = 20 and sex = male
《高性能 MySQL》 书中提到： 对于联合索引，如果查询中有某个列的范围查询，则其右边所有列都无法使用索引进行快速定位
所以对于这条语句来说，其实并不能完全踩中 (name, age) 这个联合索引，他只能踩到 name。
具体来说，这个语句在搜索（name，age）的联合索引树的时候，并不会去看 age 的值，只是按顺序把 “name 第一个字是张” 的记录一条条取出来，然后开始回表，到主键索引上找出数据行，再一个一个判断其他条件是否满足。从下图可以看出来，需要回表 3 次。
这是 MySQL 5.6 之前的做法，简单总结，当进行索引查询时，首先根据索引来查找记录，然后再根据 where 条件来过滤记录
而 MySQL 5.6 开始，数据库在取出索引的同时，会根据 where 条件直接过滤掉不满足条件的记录，减少回表次数。这就是 索引下推 (Index Condition 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 = xxx order by c = xxx这种请求的话，还需要额外对 c 列进行一次排序才行。
另外，如果有对 a,b,c 的联合条件查询的话，并且 a 是模糊匹配或者说是范围查询的话，其实并不能完全踩中联合索引（a,b,c），a 列右边的所有列都无法使用索引进行快速定位了。所以这个时候就需要进行回表判断。也就是说数据库会首先根据索引来查找记录，然后再根据 where 条件来过滤记录。
不过在 MySQL 5.6 中支持了索引下推 ICP，数据库在取出索引的同时，会根据 where 条件直接过滤掉不满足条件的记录，减少回表次数
大厂面试火箭计划
点击 大厂面试火箭计划 直达
目前网络上提供的面试题答案大部分都是短短的几行字，并且没有逻辑，面试官一听就知道是背的，我觉得这无法满足大部分同学的诉求。
如何让面试官知道我不是背的？如何知其然而知其所以然？
So，我希望的是以面试题为导向，建立完整的知识体系，让八股文变得有价值，而不是东一锤西一棒
后续准备以牛客上的面经帖为导向，对每个面试题提供 偏口语化的背诵版 + 偏专业化的详解版，已经会的同学呢可以直接看背诵版，还不太了解的同学呢可以结合详解版一起看
所有的面试题都已经按照知识体系的顺序进行过排序，并且我会加入一些补充题用于完善
Original: https://www.cnblogs.com/cswiki/p/15701065.html
Author: 飞天小牛肉
Title: 最左前缀有手就会，那索引下推呢？
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/715361/
转载文章受原作者版权保护。转载请注明原作者出处！