索引与优化原理(下)

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

继续上一篇，本节重点介绍联合索引。

Explain

之前我们已经接触了一部分索引优化相关的概念，但更多的是我一个人在讲，大家被动地接收。如何验证文章所说的正确性，以及一条SQL是否真的用到了索引呢？

这个时候就该Explain上场了，使用方式有两种：

• 借助Navicat等软件

• 直接使用Explain命令

大家把它当做一个命令即可，是MySQL提供的专门用来分析SQL执行计划的，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。使用场景一般是：

• 当你发现某条SQL很慢时，可以借助Explain分析一下是否走索引
• 当你修改某条SQL后，借助Explain查看是否达到预期

Explain有很多指标，重点看type、key、Extra：

列名	解释
id	查询编号
select_type	查询类型：显示本行是简单还是复杂查询
table	涉及到的表
partitions	匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。
type	本次查询的表连接类型
possible_keys	可能选择的索引
key	实际选择的索引
key_len	被选择的索引长度：一般用于判断联合索引有多少列被选择了
ref	与索引比较的列
rows	预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
filtered	按条件筛选的行的百分比
Extra	附加信息

type

type 的值	解释
system	查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const	基于主键或唯一索引查询，最多返回一条结果
eq_ref	表连接时基于主键或非 NULL 的唯一索引完成扫描
ref	基于普通索引的等值查询，或者表间等值连接
fulltext	全文检索
ref_or_null	表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge	利用多个索引
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用普通索引
range	利用索引进行范围查询
index	全索引扫描
ALL	全表扫描

possible_keys、key、key_len

possible_keys是MySQL预估会使用的索引（基本不用看），key才是最终实际选择的索引，可以结合key_len来推测联合索引的“使用率”。比如联合索引index(a,b,c)，假设每个索引长度为1，而key_len却是2，那么可以推测联合索引只用了前两个。

Extra

Extra是最难的，含义也最多，和索引覆盖、排序等都有关系。

Extra 常见的值	解释	例子
Using filesort	将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序	explain select * from t1 order by create_time;
Using temporary	需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时	explain select * from t1 group by create_time;
Using index	使用覆盖索引	explain select a from t1 where a=111;
Using where	使用 where 语句来处理结果	explain select * from t1 where create_time=‘2019-06-18 14:38:24’;
Impossible WHERE	对 where 子句判断的结果总是 false 而不能选择任何数据	explain select * from t1 where 1<0;
Using join buffer (Block Nested Loop)	关联查询中，被驱动表的关联字段没索引	explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
Using index condition	先条件过滤索引，再查数据	explain select * from t1 where a >900 and a like “%9”;
Select tables optimized away	使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是	explain select max(a) from t1;

联合索引

联合索引其实要比单列索引难很多，很多SQL优化原则都是针对万恶的联合索引搞出来的，比如大名鼎鼎的“最左匹配原则”。

虽然联合索引容易引发各种问题，看起来不那么容易驯服，但它在提高索引利用率、加快分组、排序等方面却有着很大的作用，所以实际开发时，联合索引一般来说应该优于单列索引。

我不知道大家是否好奇联合索引到底长啥样，反正我当初学习SQL优化时，就有很多的疑问：

• 联合索引到底长啥样？
• 查询时是怎么检索的呢？
• 为什么对于联合索引index(a, b, c)，查询条件WHERE b=1, c=2无法利用索引呢？

我们先回答第一、第二个问题，第三个问题我们分为三小节，专门讨论所谓的“最左匹配原则”。

联合索引到底长什么样呢？

• 还是一棵树，不会因为是联合索引，就变成多棵树
• 索引节点会存储多列，比如原先单列索引的节点会存储[name, name, name...]，而多列索引的节点内会存储[[name, age], [name, age],?[name, age]...]

为了帮大家更好地理解联合索引，我们一起探讨一个问题：

当我们向建有联合索引的表中插入一条数据时，发生了什么？

假设现在数据库的记录是这样的(最底下是有序链表，即叶子节点的数据)：

除了主键索引，其他都是辅助索引，联合索引也不例外。所以，上图叶子节点的数据应该是id，但为了更直观，我画成name和age。

现在要往数据库插入一条数据(bravo, 18, "杭州", "18255555556")，插入时需要维护索引，所以需要沿着索引找到数据所在的位置并在叶子节点的表数据中插入：

插入数据的过程其实伴随着索引的维护，插入后整棵索引树就变成了这样：

通过分析上面数据的插入过程，你会发现，在插入时其实会进行索引比较，先比较name再比较age。换句话说，这样沿着索引比较后插入的数据，本身就是有序的，符合联合索引的顺序。

比如班级排座位，老师按照两个条件综合排序，先按身高排，身高一样的，成绩好的坐后面。那么放学走出教室时，顺序自然就是先按身高排，矮的先出来，高的后出来，身高相同的成绩差的先出来。

所以，对于下面这条SQL：

SELECT 学生 FROM '三年二班' ORDER BY 身高, 成绩

你认为MySQL还有排序的必要吗？直接把三年二班的学生从头数到尾拎出来即可。

这就是所谓的“索引能加快排序速度”：

因为本身插入或更新时，都是按照某种规则维护的。当你ORDER BY的规则与维护索引的规则一致时（都是根据身高、成绩），此时索引就能加快排序，因为这个时候根本不用排序。

所以记住，最快的排序就是不用排序，也就是利用索引排序。把对顺序的维护分散到每一次增删改的过程中，而不是查询时再匆忙从零开始排序。

最左匹配原则：WHERE条件查询

接着，让我们思考一下“为什么对于联合索引index(name, age)，条件WHERE age=25无法利用索引”。

上面分析过，数据总是要经过索引比较后才插入，所以数据天然就是按索引顺序排列的。当我们拿着WHERE子句的条件（age=25）顺着索引树比较时，是得不到正确的结果的。因为当初这棵树排序的第一个条件是name，而不是age。

由于整棵树的排序规则是“先根据name排列，再根据age排列”，你硬要直接拿age查询，最终结果可能会南辕北辙。还是以学生排座位举例，人家明明是先按照身高排序，再按照成绩排序，那么注定成绩不会成“递增”或“递减”的趋势（请观察右边分数列），而面对没有相关趋势的数据，我们只能遍历查找：

• 180cm 98分
• 182cm 96分
• 182cm 97分
• 183cm 99分
• 184cm 93分
• 184cm 95分

分数的相关趋势只有在“身高一致”这个前提下才会显现，是局部特征（绿色块、蓝色块的分数确实呈现某种趋势）：

• 180cm 98分
• 182cm 96分
• 182cm 97分
• 183cm 99分
• 184cm 93分
• 184cm 95分

当然，实际上MySQL在解析SQL时并不会真的像上面一样进行逻辑分析，而是直接进行语法匹配和成本预估，发现WHERE条件不符合联合索引的“最左匹配原则”时，直接放弃走索引，选择全表扫描。

那么，怎么才能利用index(name, age)这个联合索引呢？

把查询条件变成 WHERE name=bravo, age=18即可！此时它会先按照name比较，再按照age比较，刚好和联合索引的顺序一致，也就是和排序顺位一致：

也就是说，此时WHERE子句的条件顺序刚好能满足最左匹配原则。

假设联合索引是index(a, b, c)，来看几个最左匹配原则的案例：

• WHERE a, b, c ??
• WHERE a, b???（只能匹配a,b）
• WHERE a, c ??（只能匹配a）
• WHERE b, c（?）

上面WHERE a, c的查询过程是怎么样的呢？

也就是说，WHERE后条件如果能走索引，就能站在更高的地方判断条件是否符合，否则只能作为判断条件深入节点内部的数据逐个比对。

如果能走全部索引，就可以少几次比较，甚至可能少几次IO：

另外，除了不能缺少联合索引中的查询字段外，还要注意尽量不要使用范围查询。

比如，联合索引是index(a, b, c)：

• WHERE a=1, b>2, c=3 ??（只能匹配a, b）

注意一下即可，毕竟真要做范围查询也不是我们能决定的，无非索引利用率低一些罢了。

最左匹配原则：ORDER BY

上面说过了，对于index(name, age)的索引树，最底层的数据本身就是先按name，再按age排序的。当ORDER BY的条件刚好是ORDER BY name, age时，直接查询即可，无需排序，因为数据在插入时就按索引顺序排好了。

现在我们反过来讨论，如果没有利用索引排序，或者无法利用索引排序时，会发生什么呢？

filesort！

所谓filesort是EXPLAIN命令中extra一列的某个指标，当extra出现filesort这个指标时，说明我们的SQL没有走索引排序，而是利用内存或磁盘自己重新排序。

那么，什么情况下会导致ORDER BY无法利用索引排序呢？

以联合索引index(name, age)为例，以下情况无法利用索引排序：

• ORDER BY age, name（字段顺序不一致）
• ORDER BY name DESC, age ASC（字段排序方式不同步，DESC和ASC混着来）

第一种情况：

因为索引里的数据是先按name、后按age排序的，而你想要先按age再按name排序，对不起，你自己对结果集重新排吧，我不管了。

特别要注意，对于联合索引index(a,b,c)，WHERE条件无论是a,b,c、a,c,b还是b,c,a...都没关系，比如女生找男朋友要身高180以上、长得帅的，如果你“反过来”，长得帅、身高180以上也是可以的。但是排序不同，顺序必须和联合索引一致，否则语义是不同的。比如，先按身高再按分数和先按分数再按身高是两种完全不同的排序策略，结果自然也不同。

大家不妨做个实验，对于('a', 17),('a', 16), ('b', 18), ('c', 18)，('d', 15)先按name正序再按age正序的结果是：

• ('a', 16)
• ('a', 17)
• ('b', 18)
• ('c', 18)
• ('d', 15)

而先按age正序再按name正序的结果是：

• ('d', 15)
• ('a', 16)
• ('a', 17)
• ('b', 18)
• ('c', 18)

所以对于index(name, age)，ORDER BY age, name排序顺序与索引不一致，是无法利用索引排序的。

第二种情况：

维护索引时默认都是name ASC, age ASC排序，如果你需要name DESC，age ASC，对不起，你自己对结果集重新排吧，我不管了。

理由同上。

但以下情况仍可以利用索引排序：

• ORDER BY?name DESC, age DESC（字段顺序和索引顺序一致即可，全部DESC或ASC都没关系）

为什么ORDER BY name DESC, age DESC也能利用索引排序呢？

假设右边数据是联合索引自动排序的，而现在查询的排序规则是ORDER BY name DESC, age DESC。我们先尝试一下，如果右边的数据真的按ORDER BY name DESC, age DESC排序，会是什么样呢？

其实就是左边数据倒过来！

所以 ORDER BY name DESC, age DESC看起来好像无法利用索引，但其实索引数据反着来刚好符合期望的排序。

总之，大家应该理解为索引排序是“很消极”的：

反正索引当初就这么排的，如果你刚好想要这种排序，就直接拿数据即可。但你如果想要的排序不是我现有的这种，对不起，请你自己重新排序（内存排序、文件排序），但我明确告诉你，重新排序会让整个查询变慢。

说了这么多，我们来验证一下。

创建表：

给name、address加了联合索引。

共1000w数据：

先介绍两个简单的指标，具体的Explain命令后面介绍：

Extra列中，如果出现了Using filesort，说明没有走索引排序，也就是说本次查询自己额外做了排序。

ORDER BY? name（正常）：

ORDER BY? name, address（正常）：

ORDER BY ?`name` DESC, address DESC（正常）：

Using index指的是索引覆盖，是个好消息，意味着本次查询不会回表。

索引失效案例：

当前叶子节点链表上的数据是按索引排的（先name后address），而SQL希望的排序方式是先address后name。既然不能有效利用索引的排序，只能把结果集取出来重新排序。

总之，如果想利用索引排序，那么ORDER BY的顺序必须符合最左前缀原则，顺序完全一致，且DESC和ASC不能混用。

OK，上面讲的都是ORDER BY本身的一些规则，接下来我们把战场扩大些，把WHERE也引进来。

当WHERE和ORDER BY搭配，就会产生一种“例外情况”：对于index(a,b,c)，SELECT * FROM table WHERE a=1 ORDER BY b,c也是可以利用索引排序的。

单独观察ORDER BY后面的字段，由于不符合“最左匹配原则”，理应不能走联合索引才对。

加入WHERE后怎么就可以了呢？

道理其实很简单，比如原本学生的排序是身高、体重、分数：

• 180cm 65kg 98分
• 182cm 65kg 96分
• 182cm 65kg 97分
• 183cm 62kg 99分
• 184cm 63kg 93分
• 184cm 64kg 95分

虽然ORDER BY b, c看起来不完整，但只要我定死身高为182（WHERE a=182），那实际筛选出来的学生其实就是先按体重、再按分数排序的，也就是ORDER BY b, c。

当没有通过a过滤时，数据整体没有相关性，但被a过滤后，局部数据就呈现相关性。

但这个“例外”本身还有个例外，就是：WHERE的条件不能是范围查询。

比如：

SELECT * FROM table WHERE a>1 ORDER BY b,c;

这条语句是无法利用联合索引index(a,b,c)排序的，理由同上面讲得一样，a>1筛选出来的结果集并不能保证b,c是预期的顺序。

上面说过，WHERE height=182 ORDER BY weight, score是可以走索引排序的，因为局部范围内能保证走索引排序，而WHERE height>=182 ORDER BY weight, score，显然就不满足索引排序。

• 180cm 65kg 98分
• 182cm 65kg 96分
• 182cm 65kg 97分
• 183cm 62kg 99分
• 184cm 63kg 93分
• 184cm 64kg 95分

最左匹配原则：GROUP BY

这个没什么好说的，GROUP BY其实可以看成两步：先排序，后归并。

一般对于GROUP BY的优化，就是尽可能让它也走索引排序。当它和联合索引顺序一致时，GROUP BY会跳过排序，直接归并，从而达到优化的目的。

后面我们专门安排一个章节，汇总一下优化规则，上面的内容理解即可。

联合索引的使用场景

GROUP BY没什么好讲的，可以理解为排序的基础上在进行归并，所以学习联合索引时，主要关注WHERE和ORDER BY即可。

场景一：多条件查询，提高利用率

如果发现很多SQL的WHERE条件经常是多个相同的字段，比如SELECT xxx WHERE a,b,c、SELECT xxx WHERE a,b、SELECT xxx WHERE a,c等，此时可以考虑创建联合索引index(a,b,c)。

这里有几条规则需要注意：

• WHERE条件的书写顺序并不影响是否走联合索引。比如WHERE a,b,c和WHERE b,c,a都可以走index(a,b,c)
• 但WHERE b,c是不行的，因为缺少a，而WHERE a,c只能利用部分索引
• 创建索引时，最好把区分度高的排在前面（注意，我说的是创建索引的顺序，而不是WHERE条件的顺序）

第三条需要大家再仔细品味一下，比如要给id_card和name建立联合索引，如果你建的是index(name, id_card)，就会浪费无畏的IO在name查找上：先找到全国同名同姓的，再根据id_card匹配。而如果创建index(id_card, name)，那么id_card是唯一的，区分度极高，基本就是一击必中。

场景二：避免回表

上一篇已经介绍过了，如果走辅助索引并回表，就会无端多一次对主键索引的扫描。比如对于只有主键索引和name索引的表执行以下SQL：

SELECT id, name, age FROM table name='xxxx'，

由于name索引只包含了id和name，而SELECT的列却是id、name、age，此时MySQL底层不得不回表，拿着id再跑一遍主键索引，把age给捞出来。

可行的解决办法是：添加联合索引index(name, age)，这样辅助索引上就同时包含id、name、age，可以直接返回。

当然啦，并不是只有联合索引才能使用索引覆盖，只要辅助索引上的字段满足SELECT的列即可，所以即使是单列索引index(name)，也是可以避免回表的，比如SELECT id, name FROM table WHERE name='xxx'。

但大家要认识到，实际开发索引覆盖可遇不可求，基本还是回表的情况多一些。比如，即使你定了联合索引index(name, age)，但实际上却是要查询所有列怎么办？此时还是要乖乖回表（好歹走了辅助索引了，知足吧）。

场景三：索引排序

当你建了联合索引index(a,b,c)，那么每次增删改都会按这个顺序维护。如果查询是需要的顺序刚好是a,b,c，就可以直接返回数据，无需排序，美其名约“利用索引排序”。

有个比较特别的地方是，WHERE和ORDER BY可以“联手”玩转联合索引，比如对于index(a,b,c)，并不是只有WHERE a,b,c或者ORDER BY a,b,c才能利用联合索引，WHERE a=1 ORDER BY b,c也是可以的~

讲完了这三个场景，再稍微注意一下联合索引失效的问题即可（只说两个最常用的）：

• 最左匹配原则
• 注意范围查询，比如WHERE a>1 and b=2 and c=3，那么就只能用到a啦

场景四：COUNT统计

虽说一般大数据量不推荐直接使用COUNT函数，但绝大部分公司都是小公司，一张表不会很大，所以初期COUNT也无妨，但最好尽量走索引。比如要求统计天猫平台下的店主粉丝数量：

SELECT COUNT(*) FROM t_user_follow WHERE uid=123 and platform=6 and follow_status=1;

此时最好建立联合索引index(uid, platform, follow_status)，速度会稍微快一些。

其实场景四不过是联合索引的一个特例，这里单独提出来，就是为了给大家提个醒：哦？COUNT也能用上联合索引啊！

MySQL的架构图

看到那个查询优化器了吗，比如index(a,b,c)联合索引，即使我们写成 WHERE a=1, c=3, b=2也能走全索引，就是因为它帮我们优化了顺序。

好了，虽然上面的内容不一定全部正确，但个人认为利大于弊，起码能让你快速、清晰地理解SQL优化的底层原理，相信大家对于市面上任意的SQL优化课程都能快速吸收。

如果要用一句话概括SQL优化，应该是下面这句：

所谓SQL优化，其实就是让查询优化器根据程序员的意愿选择匹配的执行计划，最终减少查询中产生的IO。

这几篇下来，大家应该对索引是什么、如何进行SQL优化有了整体的认知。

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

进群，大家一起学习，一起进步，一起对抗互联网寒冬