• 前言
• 初步了解索引
• 要调优 SQL，怎么能不认识 explain
• 重点!SQL 优化

因为笔者现在工作中用的存储引擎大多是 InnoDB，所以本文基于 InnoDB，数据库版本MySQL 5.7为前提写的。我们平常说的 SQL 优化，基本上就是对索引的优化。这里既然重点是 SQL 优化，所以我们得先了解索引，然后了解下我们分析 SQL 的工具 explain，最后才能到优化。这也是本文的大纲顺序。

了解 SQL 优化之前，有几个概念需要先知道：

• MySQL 索引的数据结构

B+Tree，是 M 阶搜索树。现在以主键索引为例，非叶子节点会冗余我们的主键排序并构成树结构(非叶子节点不会存储数据);叶子节点会存储数据，并且叶子节点会形成一个双向链表，值得注意的是首尾节点也有指针互相指向。(具体可以看：https://segmentfault.com/a/1190000008545713?utm_source=sf-related)

• 聚簇索引

叶子节点存储索引对应的 record信息。

• 非聚簇索引

叶子节点只存储主键数据，所以要查询索引以外的数据需要回表。

• 回表

走非聚簇索引得到主键数据后，根据主键再走一次聚簇索引那里查询列需要的数据。

• 优化器

优化器是MySQL 众多组件中的一个，它会对我们的 SQL 进行分析，看预计使用哪些索引，SQL 的执行顺序如何，实际会使用哪些索引(没有真的执行 SQL，执行 SQL 是存储引擎去进行读写的)，使用索引的情况等等。

需要知道使用 InnoDB 的表肯定有一个聚簇索引(有且仅有一个)，使用的数据结构是 B+Tree。

*.frm：数据表结构相关信息存储的文件

*.idb：索引和数据存储的文件

注意：*.idb 这个文件本身就是 B+Tree 的文件，叶子节点包含完整的数据记录。

下面以主键索引为例(我的user表就只有三个字段)

• 主键(不会重复)

如果我们没有主键，MySQL会使用我们表从第一列开始选择一列所有元素都不相等的列构建B+Tree，假设我们不存在符合这个要求的列，MySQL会自己为我们创建一个符合这个条件的隐藏列构建索引。像这种开销没必要花费，我们自己建表时，直接处理可以。

• 自增

维护B+Tree时，更容易，性能更好。

• 整型

查询范围时，整型比较大小更简单;整型占用空间更小，节约空间，事实上公司一般都会要求明确字段大小，过大字段，DBA一般都会要求开发解释为什么要这么大，当然从存储数据量角度来看，索引也是越小越好。

二级索引是非聚集的，主要是为了节约空间。二级索引是先找到主键，通过主键回表找到真正的数据行。

假如现在我有个用户表有4个字段：username、telephone、age、sex。

我们可以建两种类型的联合索引：联合主键，普通的联合索引。

现在我用 username、sex 构建成联合主键，维护索引如下：

这个和上面的差不多，只是 data 存的是主键，需要回表查找。

以上图为例子，先根据名字转成的ascii码进行排序，如果 ascii 码一样，那么再根据性别的 ascii 码大小比较排序。只有 username 的索引生效了，sex 的索引才有可能生效。要证明也很容易：如果没有匹配 username，直接匹配 sex，单看 sex 的话，我们索引的排序是无序的，就没法使用二分法了，所以不走索引。

讲了索引的数据结构，以及生效的情况，那么接下来就要看看如何 SQL 优化了。但是在此之前，我们要先了解下 explain 和 trace。

使用 explain 可以模拟优化器执行 SQL，分析 SQL，看看能否优化。

explain 标识的 SQL 不会真的执行，只是返回执行计划。如果 from 中包含子查询，仍会执行该子查询，子查询的结果将会放在临时表中。

explain 分析的 SQL 中，每查询一个表就会有一行记录。

更多内容请参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

了解每一列的意义，掌握最常用那几列。

3.1.1 id

id 列的编号是 select 的序列号，查几个表就有几个 id，并且 id 值越大执行优先级越高。如果 id 值相同，就从上往下执行，最后执行 id 为 null 的。

3.1.2 select_type

查询类型。

• primary

简单查询。查询不包含子查询和union。

• subquery

复杂查询中最外层的 select。

• derived

包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)。如下：

? union

其实就是使用了 union 关键字后面的查询，如下：

3.1.3 table

表示这一列使用的是哪一张表。

当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。如下图：

当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

3.1.4 partitions

使用的哪个分区，需要结合表分区才可以看到。因为我的例子都是没有分区的，所以是 null。

3.1.5 type

关联类型或者访问类型。一般要保证查询达到 range 级别，最好达到 ref。

从最优到最差：system > const > eq_ref > ref > range > index > ALL。

• system, const

const 是 MySQL 能对查询的某部分转成一个常量，如下:

而 system 是 conts 的一个特例，当表里只有一条记录时，匹配时为 system。

• eq_ref

使用了主键字段或者唯一索引字段进行关联，最多只会返回一条符合条件的记录时，等级为 eq_ref。

• ref

相较于 eq_ref，它使用的是普通索引或者唯一索引的部分前缀，可能会找到多条符合条件的记录。

• range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

• index

这种一般是通过扫描某个二级索引的所有叶子节点(其实就是应该做全表扫描，但是这里利用了B+Tree的叶子节点是链表的特性遍历)。这种方式，虽然比较慢，但是用覆盖索引优化，性能上还是要比全表扫描(ALL)要好的，因为它占用空间小，一次IO可以读更多数据。

• ALL

这个级别没啥好说的，就是我们常说的全表扫描。

3.1.6 possible_keys

显示可能会使用的索引。

3.1.7 key

实际会使用的索引。

3.1.8 key_len

通过这个值，可以推算出使用到索引的哪些列(一般针对联合索引使用多些)，举个例子：

film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是4字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id列来执行索引查找。

key_len计算规则如下：

• 字符串：char(n) 和 varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

– char(n)：如果存汉字长度就是 3n 字节

– varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

• 数值类型

– tinyint：1字节

– smallint：2字节

– int：4字节

– bigint：8字节

• 时间类型

– date：3字节

– timestamp：4字节

– datetime：8字节

• 如果字段允许为 NULL，需要1字节记录是否为 NULL。索引最大长度是768字节，当字符串过长时，MySQL会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

3.1.9 ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)，字段名(例：film.id)。

3.1.10 rows

这一列是MySQL估计要读取并检测的行数，注意这个不是结果集里的行数。

3.1.11 filtered

通过过滤条件之后对比总数的百分比。

3.1.12 Extra

这一列展示的是额外信息。常见的重要值如下：

• Using index

使用覆盖索引。覆盖索引其实就是查询列是索引字段，这样就能避免回表，提高性能。因此，我们覆盖索引针对的是辅助索引。

• Using where

使用 where 语句处理结果，并且查询列未被索引覆盖。如下：

• Using index condition

查询的列没被索引完全覆盖， where 条件中是一个前导列的范围。

• Using temporary

创建临时表来处理查询

(1)actor.name没有索引，此时创建了张临时表来distinct。

(2)film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表。

• Using filesort

使用外部排序而不是索引排序，数据量较小时使用内存，否则会使用磁盘。

(1)actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录。

(2)film.name建立了idx_name索引，此时查询时extra是using index。

Using filesort 原理详解：

– 单路排序

一次性取出满足条件的所有字段，然后在 sort buffer 中排序。用 trace 工具可以看到 sort_mode 信息里显示 或者 < sort_key, packed_additional_fields>

– 双路排序(回表排序)

先根据条件获取相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中排序，最后回表获取完整记录。用 trace 工具可以看到 sort_mode 信息里显示 。

– MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

1. 如果字段的总长度小于 max_length_for_sort_data，那么使用单路排序。
2. 如果字段的总长度大于 max_length_for_sort_data，那么使用双路排序。

• Select tables optimized away

使用某些聚合函数(比如 max、min)来访问存在索引的某个字段时为 Select tables optimized away。

极端点说，SQL 优化就是对索引的优化。因此，我们要看下各种情况下，如何优化索引。

在我看来，SQL优化分以下几种情况：

1.可以走索引

• 应该走索引，但是没走
• 走索引了，但是没到最优(explain 分析，type 一般我们要求至少到达 range 这个级别)
• order by 和 group by 优化

2.没法走索引(客观现实上的)或者 type 是 index，而且数据量大

• 了解适用索引的情况，请不要只有面试时会说，工作就不知道了(数据量不大，直接查没事;大的话，考虑引进其他技术解决，如 :Redis, MongoDB, elasticsearch等)

3.小表驱动大表

4.count 查询优化

5.如何建索引

• 该在哪个字段建索引
• 哪些字段要使用联合索引
• 表字段的设计(数据类型，大小)

Note : 单个索引生不生效，怎么处理还是比较简单的，所以下面只针对联合索引做分析。

下面先建表和造数据：

其中员工表插入了10W+数据。

• 联合索引第一个字段不能过滤大部分数据，导致回表效率低，走全表扫描的 cost 更小。

当然我们也可以选择强制走索引，如下：

不过，走索引一定性能就更好吗?我们试验下。

别看我这差距不大，我这只是表列不多，字段不大，数据量也不算太多，所以差距不大，如果表更大的话，差距就会比较明显了。实际工作中，我们很难确定走索引的 cost 就一定小于全表扫描的。因此，我们一般不强制走索引。

优化方案：

我想让 MySQL自己去走索引，而不是我强制走索引。怎么办呢?其实上面已经提到了，这里是因为第一个字段过滤不多，导致回表效率低。既然如此，我们让它不回表不就好了吗?使用覆盖索引优化，就是我们查询列的字段都是使用的这个索引树上建了索引的字段，这样就不需要回表了。如下：

扩展：

1. 我们使用 in 和 or 时，有时走索引，有时不走，其实是因为 MySQL 判断走索引的cost不如全表扫描的。
2. 我们这里用了 like 'keyword%'，这里涉及到一个概念叫索引下推。其实就是，MySQL 5.6 之前，对于以下的SQL，如果是走索引的话，它会先根据 name 过滤得到主键，进行回表拿到数据后，再去对比 age 和 position。MySQL 5.6 对此进行了优化——索引下推，根据 name 过滤后，不先回表，而是直接去对比 age 和 position，最后得到的主键才回表查数据。注意：1、索引下推只用于二级索引;2、不是 like 'keyword%' 就一定使用索引下推。

• 分页不走索引

分页查询，系统十分常见的查询，建议大家学习完后，赶紧看下自己负责的分页功能是否走索引了，或者是否走了索引但是还能优化。以下，看例子来说一些优化手段。

这 SQL 其实是去了10010条记录出来，然后再舍弃前面的一万条。因此数据量大的话，其实效率是十分低的。

一些优化方案：

1.和产品同事商量，给一些一定有的查询条件或者隐藏的查询条件，给这些条件使用上索引。

这个方案是最简单并且直接的。

2.像我这里记录的id是连续且自增的情况下：

属于取巧，通过主键索引使用 where 直接筛选掉前面10000条记录。

缺点：

(1) 如果 id 不是连续且自增，那么这种方式就不行。

(2)不是使用主键排序，这种情况也不行。

3.非主键排序，不用ID连续自增也能生效。

• 首先想到覆盖索引优化，看看能否这样干

扩展：

• 不能使用覆盖索引，用了非主键排序，全表扫描的原因：MySQL 5.6~5.7 版本的优化器认为走二级索引再回表的效率不如全表扫描，这时是不会走索引的(但是也有例外，select * from employees order by name desc limit 10 就会走索引，因为只需要拿10条记录，这数量足够小，具体可以看这个博客，写得很好：https://www.cnblogs.com/25lH/p/11010095.html)。

解决方案如下：

（1）

这里其实就是利用了二级索引，拿到了10010条数据，并且按照 name 排好序，由于这里的子查询只要 id，所以不需要回表，然后再通过 join 就能利用主键索引快速拿到记录。

（2）当然除了这种方式，我们也可以强制走索引，因为我们知道这里二级索引只有一个，并且 name 是前导列，所以我这个案例走索引性能肯定比全表扫描好。因此，我们也可以选择强制走索引。

• 不符合最左原则

我们索引之所以可以帮我们快速找到目标数据，是因为它的数据结构的特点。其中有序这一特征十分重要，如果不满足，那么肯定是不会走索引的(具体原因要回到平衡二叉查找树，再到二分法。因为不是这里的重点，所以不展开讲)。

• 在索引列上做了以下操作：

– 对索引列是用了函数

– 对索引列做了类型转换

• 根据查询条件过滤的数据不多，导致优化器认为走索引不如全表扫描。

其实第一个案例已经涉及到了，但是这里针对的是不等于， not in, not exists, <, >, is null, is not null 等等，这些能匹配到多条记录的写法。

排序和分组的优化其实是十分像的，本质是先排序后分组，遵循索引创建顺序的最左匹配原则。因此，这里以排序为例。

??https://www.cnblogs.com/25-lH/p/11010095.html??这个博客有讲到无查询条件的排序的案例，我这里就直接上图了，如下：

接下来写的都是有查询条件的情况。

MySQL 支持两种排序方式 filesort 和 index， Using index 是扫描索引完成的排序，而 Using filesort 是利用内存甚至磁盘完成排序的。因此，index 效率高，filesort 效率低。

当我们做多表关联查询时，常常会听到小表驱动大表。这里要了解什么是小表，什么是大表，为什么是小表驱动大表，MySQL 用了什么算法。

下面以两张表关联为例，介绍概念

什么是小表，什么是大表?不是表数据量较多那张表就是大表!!!而是经过我们的条件筛选后，匹配数据相对较小的那张表就是小表，另外一张就是大表。

所谓的小表驱动大表就是：先查小表，然后通过关联字段去匹配大表数据。

MySQL 的表关联常见有两种算法：

• Nested-Loop Join 算法(NLJ)
• Block Nested-Loop Join 算法(BNL)

4.3.1 NLJ，嵌套循环连接算法

这个算法就是一次一行地从驱动表中读取，通过关联字段在被驱动表中取出满足条件的行，然后取出两张表的结果合集。

从执行计划可以看出：

• uuc_user_role 是驱动表，并且扫描了9条记录(表里只有9条记录)，然后通过 user_id 去关联了 uuc_user(被驱动表)。

注意：优化器一般会优先选择小表驱动大表，我们 SQL 写的表的先后顺序有可能会被优化。

上面 SQL 的大致流程如下：

1. 先从 uuc_user_role 中读取一行记录(如果有查询条件，会根据查询条件过滤结果中取一条)
2. 获取关联字段，通过关联字段到 uuc_user 找到匹配记录
3. 对第二步得到的记录，根据查询条件得到的记录跟第一步得到的记录进行合并，返回客户端
4. 重复上面三步

查询结果如下(由于数据太多，手工拼接图了)

NLJ这个过程会读取 ur 所有数据(9行记录)，每次读一行并拿到 user_id 的值，然后得到对应的 uuc_user 里的记录(这就是又扫了一次索引得到一行数据)。也就是说，整个过程扫描了18行记录。注意：如果被驱动表的关联字段没有索引，使用NLJ算法性能较低，MySQL会选择使用 BNL 算法。

扩展：如果我这里使用的是 left join，这时，左边的是驱动表，右边的是被驱动表;right join 则刚好相反。

4.3.2 BNL，基于块的嵌套循环连接算法

把驱动表的数据读入 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来和 join_buffer 中的数据做匹配。

上面扩展已经出现了 BNL 算法的例子了，我就直接使用了。

这条 SQL 的流程大致如下：

1. 把 uuc_user 所有记录放入 join_buffer
2. 查 uuc_user_role 的记录和 join_buffer 中的数据匹配
3. 返回满足条件的数据

整个过程扫描了 uuc_user 表225条记录和 uuc_user_role 表9条记录，总扫描行数为234行。内存比较最大次数 = 225 * 9 = 2025(次)，想想 for 循环的代码就知道了。

两个问题：

• 如果内存不够大，即 join_buffer 放不下 uuc_user 的数据怎么办?
• 为什么被驱动表的关联字段没有索引会选择 BNL 算法呢?

答案：

• 内存不够，那就分段放。打个比方我内存只能放下200条记录，我这里225，那么我一次放200，分两次放完就好了。join_buffer 默认值是256k。
• 如果关联字段没有索引，使用 NLJ 算法的话，那么我们的比较都需要走磁盘扫描(等于是查询没有用到索引)。这时，都没用到索引的话，我内存比较的性能要比磁盘的好。因此，使用 BNL。但是有索引的话，我们可以通过索引大大提升查询性能(其实就是减少IO)，所以会使用 NLJ。

4.3.3 多表关联的优化

互联网公司其实一般不允许做多表关联，如果做了关联，最多不超过3张表。多表关联时，关联字段一定要有索引，并且数据类型保持一致。为什么这么要求?直接原因，阿里规范(老大都这样规范，小弟跟着做，没毛病)。根本原因?看《高性能MySQL》，这本书推荐阅读。

• 关联字段加索引，让 MySQL 做 join 时尽量选择 NLJ 算法。
• 小表驱动大表，如果自己能知道哪张表肯定是小表，我们可以使用 straight_join，省去优化器的判断时间。

4.3.4 in 和 exsits

原则还是小表驱动大表

假设 A 表是左表，B 表是子查询的表。当 A 表是大表， B 表是小表时，使用 in。

当 A 表是小表， B 表是大表时，使用 exsits。

网上挺多资料说，要count(id)或者count(1)，不要count(*)，到底是不是这样呢?我们今天就来实践一下。

具体耗时如下(其实，随着电脑的状态不同，会有出入，但是多次测试会发现，这截图的排序结果是多数)。

因此，我们可以看出 count(*) 少用，性能较差是谣言，可以放心使用。这是因为 MySQL 5.6+ 会对 count(*) 进行优化，所以执行效率还是很高的。

hire_time 慢的原因是因为没有索引。

老生常谈的东西了，面试也经常问，这里就做个总结。

对于如何建索引这个问题，我个人觉得应该从以下几个角度思考：

• 什么场景要建索引
• 应该挑选哪些字段建索引，字段的大小，字段的类型
• 索引的数量

4.5.1 什么场景要建索引

• 高频查询，且数据较多，能够通过索引筛选较多数据
• 表关联
• 统计，排序，分组聚合

4.5.2 应该挑选哪些字段建索引，字段的大小，字段的类型

• 高频查询，更新低频，并且可以过滤较多数据的字段
• 用于表关联的关联字段
• 用于排序，分组，统计等等的字段
• 作为建索引的字段尽量小，可以降低树的高度，具体规则看下面的阿里规范

4.5.3 索引的数量

索引的数量要尽量的少。

1. 因为索引是会占空间的;
2. 记录更新数据库记录时，是有维护索引的成本的，数量越多，维护成本越高;
3. 一张表索引过多，当一个条件发现多个索引都生效时，优化器一般会挑选性能最好的那个索引来用，数量多，优化器的挑选的成本也会上升。

1.代码先行，索引后上

只有对系统有了一定全局观，才知道哪些地方需要用索引，大多 SQL 是怎样的，我应该如何建索引。这样，我们就能有效减少不必要的索引，做到联合索引尽量覆盖条件。

2.尽量不要在过滤数据不多的字段建立索引，如：性别。

3.where 与 order by 冲突时，优先处理 where。

蔡柱梁，51CTO社区编辑，从事Java后端开发8年，做过传统项目广电BOSS系统，后投身互联网电商，负责过订单，TMS，中间件等。