常见面试题-MySQL的Explain执行计划

了解 Explain 执行计划吗？

答：

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)，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
  • Using filesort：使用外部排序而不是索引排序，数据较少时在内存中排序，数据较大时在磁盘中排序，一般情况下也是需要考虑使用索引进行优化
    • 示例：name 字段没有索引，explain select name from account order by name
  • Select tables optimized away：使用聚合函数来访问存在索引的某个字段
    • 示例：explain select min(id) from account;