【MySQL核心面试题】MySQL 核心 - Explain 执行计划详解！

欢迎关注公众号 【11来了】（文章末尾即可扫码关注） ，持续 中间件源码、系统设计、面试进阶相关内容

在我后台回复 「资料」 可领取 编程高频电子书！
在我后台回复「面试」可领取 30w+ 字的硬核面试笔记！

感谢你的关注！

该文章内容已经收录在《面试进阶之路》，包含底层原理解析，带你冲破面试迷雾

了解 Explain 执行计划吗？

为什么要知道 SQL 的执行计划？

首先我们为什么要去学习 MySQL 中的执行计划呢？

我们知道，在大多数场景下对于 MySQL 的优化都是通过建立索引来完成的，但是在实际业务场景中，可能一个 SQL 非常复杂，其中执行起来我们可能并不能去预测它的执行方式，也不知道它到底走没有走索引

那么就可能出现我们去针对这个 SQL 建立了联合索引，但是性能还是非常差的情况，因此针对这种情况，我们需要去查看 SQL 的执行计划，了解 SQL 是如何运行的，之后再针对它进行索引、性能优化！

详解执行计划 type 列

通过 explain 语句可以帮助我们查看查询语句的具体执行计划，那么在执行计划中的 type 列表示 MySQL 是如何查找对应的数据了，我们先来说一下执行计划中 type 列常见的值

这里主要说一下常见的几种：const、ref、range、index，性能从左到右逐渐变差

• 首先，const 的话表示性能是常量级的，非常快

就比如对于 SQL 语句：select * from table where id = 1

SQL 语句可以通过 聚簇索引 或者 二级唯一索引 + 聚簇索引 的方式，在常量级别的时间内找到我们想要的数据

这里需要注意的是，如果使用的是二级唯一索引的方式，必须保证建立 unique key 唯一索引，来保证二级索引列中的值都是唯一的，比如对于 SQL：select * from table where name = x ，那么就需要保证 name 列的值是唯一的，且 name 列是二级索引

• ref 级别的查询

如果在 SQL 中只使用到了普通的二级索引，如：select * from table where name = x ，name 为普通二级索引，不是唯一索引，那么此时 SQL 的查询级别就是 ref，速度也比较快

如果对聚簇索引或者唯一索引判断是否为 null 值的话，也是 ref 级别的查询，如：select * from table where name is NULL ，如果 name 是聚簇索引（主键索引）或者唯一索引的话，此时查询级别为 ref

• range 级别的查询

使用索引进行范围查询的 SQL，此时就是 range 级别的查询

如：select * from table where age >= 18 and age <= 30 ，如果 age 为普通索引，通过 age 进行范围查询，则查询为 range 级别的

• index 级别的查询

看到 index 级别，可能觉得很快，其实不是这样的，index 级别的 SQL 查询性能仅仅比全表扫描要略好一些而已

index 的意思就是，如果有一个 SQL，发现你建立了一个联合索引，这个索引恰好是覆盖索引，因此直接遍历这个联合索引的叶子节点，将要查询的值全部取出来之后，就不需要再去聚簇索引中取值了，这种情况下查询的级别就是 index，性能仅仅比全表扫描要好一些而已

比如说 select account, name, age from table where name = x ，建立的联合索引为（account, name, age），那么发现 where 条件中直接根据 name 判断，不符合最左前缀原则，但是符合覆盖索引，因此 MySQL 判断二级索引大小还是比较小的，因此直接扫描二级索引的全部叶子节点，直接将对应的值给取出来即可

explain 各列含义

explain 查出来的各列含义如下：

• id：在一个大的查询语句中，每个 select 关键字都对应一个唯一的 id

• select_type：select 关键字对应的那个查询的类型

  • simple：简单查询。表示查询不包含子查询和union

  • primary：复杂查询中最外层的 select

  • subquery：包含在 select 中的子查询（不在 from 子句中）

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

    set session optimizer_switch='derived_merge=off';  #关闭 mysql5.7 新特性对衍生表的合并优化
    explain select (select 1 from employees where id = 5) from (select * from account where id = 3) der;
    set session optimizer_switch='derived_merge=on'; #还原默认配置
    

    

  • union：在 union 中的第二个和随后的 select

    explain select 1 union all select 1;
    

• partitions：匹配的分区信息

• type：表示访问类型，即 MySQL 决定如何查找表中的行。从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

  一般来说得保证查询达到 range 级别，最好达到 ref

  • Null：表示 MySQL 在优化阶段分解查询语句，执行时不需要再访问表或索引。例如 explain select min(id) from account; 在索引列中取最小值，单独查询索引即可，执行时不需要再访问表

  • system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 explain select * from test; 在 test 表中只有一条数据，如果 test 表使用 MyISAM 存储引擎，则 type 为 system；如果 test 表使用 InnoDB 存储引擎，则 type 为 ALL

  • const：const 表示代价时常数级别，当根据主键索引、唯一索引、二级索引与常数进行等值匹配时，对单表访问就是 const，只匹配到一行数据，很快.

    explain select * from account where id = 1

  • eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

    explain select * from t1 left join t2 on t1.id=t2.id

  • ref：相比于 eq_ref，不使用唯一索引，使用普通索引或者唯一索引的部分前缀，索引值和某个值相比较，可能找到多个符合条件的行

    name 是普通索引（非唯一索引），explain select * from account where name = 'abc'

  • range：范围扫描通常出现在 in()、between、>、<等操作

  • index：扫描全索引就能拿到结果，一般是扫描某个二级索引，会直接对二级索引的叶子节点遍历和扫描。这种查询一般为使用了覆盖索引，二级索引比较小，所以通常比 ALL 快一些

  • ALL：全表扫描，扫描聚簇索引的所有叶子节点，通常情况下这需要增加索引来进行优化

• possible_keys：可能用到的索引

  查询时可能出现 possible_keys 有列，但是 key 显示 Null 的情况，这是因为表中数据不多，MySQL 认为索引帮助不大，选择了全表扫描

  如果该列是 Null，说明没有相关索引，可以通过添加索引来提高查询性能

• key：实际上使用的索引

  如果为 Null 表示没有使用索引，可以使用 force index、ignore index 来强制使用索引

• key_len：实际使用到的索引长度

  key_len 计算规则如下：

  • 字符串，char(n)、varchar(n) 在 5.0.3 之后，n 代表字符数，而不是字节数，如果是 utf-8，一个数字或字母占 1 个字节，1 个汉字占 3 个字节
    • char(n)：如果存汉字，长度为 3n 字节
    • varchar(n)：
      • 如果存汉字（utf8），长度为 3n+2 字节，加的 2 字节用来存储字符串长度（varchar 是变长字符串）
      • 如果存汉字（utf8-mb4），长度为 4n+2 字节
  • 数值类型
    • tinyint：1 字节
    • smallint：2 字节
    • int：4 字节
    • bigint：8 字节
  • 时间类型：
    • date：3 字节
    • timestamp：4 字节
    • datetime：8 字节
  • 如果字段允许为 Null，则还需要 1 字节记录是否为 Null

  计算示例：

  • 设置索引：idx_balance(balance)，SQL 语句为 explain select name from account where balance = '111' ;

    该 SQL key_len = 5，4 个字节用于存储 balance（int，4B），1 个字节记录是否为 Null

  • 设置索引：idx_name(name)，name 字段编码为 uft8-mb4，长度为varchar(10)，explain select name from account where name = 'abc';

    该 SQL key_len = 43，name 索引长度为 10，使用 utf8-mb4 存储汉字的话，1 个汉字占 4 个字节，长度为 10 所占用字节为 4 * 10 = 40，还需要 2 个字节存储 varchar 的长度，name 字段可以为空，因此还需要 1 个字节记录是否为 Null，因此 name 索引的长度为 40 + 2 + 1 = 43

    如果是 utf-8 编码，1 个汉字是占 3 个字节的。

• ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息，常见的 ref 值有：const（常量），字段名（例如：film.id）

• rows：预估的需要读取的记录条数，并不是结果集中的实际行数

• Extra：—些额外的信息，常见的重要值如下：

  • Using index：使用覆盖索引，通过索引可以直接获取查询结果，无需回表查询

  • Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

  • Using index condition：查询的列不完全被索引覆盖，where 条件中是一个前导列的范围

    • 示例：索引（name，balance） explain select *from account where name > 'a';

  • Using temporary：mysql 需要创建一张临时表来处理查询。出现这种情况需要使用索引进行优化

    • 示例：name 字段没有索引，此时创建一张临时表来 distinct，explain select distinct name from account

    临时表可能在内存上，也可能在磁盘上，这取决于 tmp_table_size 和 max_heap_table_size 值的最小值，内存临时表的最大容量为这两个参数中的最小值，可以通过命令查看：show global variables like 'tmp_table_size' 和 show global variables like 'max_heap_table_size'

  • Using filesort：使用外部排序而不是索引排序，数据较少时在内存中排序，数据较大时在磁盘中排序，一般情况下也是需要考虑使用索引进行优化

    • 示例：name 字段没有索引，explain select name from account order by name

    一般在没有索引的列上进行 order by 就会触发 Using filesort，可以在 order by 的列上添加索引

    如果 order by 是多字段排序，也要遵循最左前缀原则，并且都是升序或者降序

    如果不可避免地要出现 Using filesort，可以适当增大排序缓冲区大小 sort_buffer_size（默认 256k）：show variables like 'sort_buffer_size'

    当排序的数据大小 < sort_buffer_size 时，为内存排序；否则为磁盘排序

  • Select tables optimized away：使用聚合函数来访问存在索引的某个字段

    • 示例：explain select min(id) from account;

通过实操理解 explain 执行计划

• 案例一：开胃小菜

SQL 语句：

explain select * from test1;

执行计划如下：

首先，id = 1，id 是每一个 SQL 语句的唯一标识

select_type 值为 SIMPLE 表示这个 SQL 是一个简单的查询，不包含子查询以及 union 等操作

table 表明对哪个表进行的操作

type = index 表明对二级索引的叶子节点进行扫描得到了结果，因为这个 test1 表里只有两个字段，id 和 name，我在 name 列上建立了索引，因此对 name 索引的叶子节点扫描一遍就可以得到 id 和 name 值

rows = 3 表明扫描了 3 行数据

filtered = 100 表明没有通过 where 过滤数据，因此筛选出来的数据在表里数据的占比为 100%

• 案例二：多表查询

SQL 语句：

explain select * from test1 join test2;

执行计划：

可以发现有两条执行计划，也就是说明会访问两个表，两条执行计划的 id 都是 1，说明是同一个 SQL 语句

首先第一条执行计划是对 test2 表进行全表扫描（type = ALL），rows = 1 表明扫描出来了 1 条数据，在表中占比为 100%

第二条执行计划是对 test1 表进行全表扫描，rows = 3 表明扫描出来 3 条数据，占比 100%，其中 Extra 列与第一条执行计划有所不同

可以看到 Extra 列值为 Using join buffer(Block Nested Loop)，

这是因为使用了 join 对两个表进行连表查询，这样其实查出来的是笛卡尔积，对两个表中的所有数据进行关联，在 MySQL 中一般会以数据量比较小的表作为驱动表，因此以 test2 表为驱动表，去 test1 表中找到所有数据进行匹配，小表作为驱动表可以减少比较的行数，在 test1 表中对数据进行匹配时使用到了 Using join buffer，也就是通过一块内存区域 join buffer 来对数据进行连接操作，而 Nested Loop 表明进行嵌套循环连接，也就是笛卡尔积（test2 表的每一行数据都和 test1 表的每行数据做连接）

• 案例三：union 并集查询

SQL 语句：

explain select * from test1 union select * from test2;

执行计划：

前两条执行计划就是对 test1 和 test2 这两张表进行全表扫描操作

第 3 条执行计划是对两张表中的数据进行合并去重操作，table = <union 1,2> 指的是这个临时表的表名，extra = Using temporary 也表明了使用了临时表

union 是对两张表的结果进行合并去重

union all 的话不会对数据进行去重操作