MySQL进阶 explain 执行计划详解

2022年9月28日09:13:36

MySQL进阶之 explain 执行计划的应用

1、什么是 explain

使用 explain 关键字可以模拟 MySQL 优化器执行 SQL 语句，从而知道 MySQL 是如何处理我们的 SQL 语句的。

可以分析 SQL 查询语句或者表结构的性能瓶颈。

2、explain 能干啥

  • 显示表的读取顺序
  • 显示数据读取操作的类型
  • 显示哪些索引可以使用
  • 显示哪些索引被实际使用
  • 显示表之间的引用
  • 每张表有多少行被优化器查询

3、explain 怎么使用

explain + SQL 语句

例如：explain select * from tbl_emp; SQL 语句的显示结果为：

MySQL进阶 explain 执行计划详解

4、各字段的解释

id 字段

作用：通过 id 字段来判断表的读取顺序

其值有三种情况：

  • 相同值

    • 所有的 id 相同时，从上到下顺序执行
      例如：explain select * from tbl_emp,tbl_dept where tbl_emp.deptId=tbl_dept.id;
      执行结果：
      MySQL进阶 explain 执行计划详解
      最后 MySQL 优化器的 SQL 执行结果是，从上到下依次读取表 tbl_dept、tbl_emp。

  • 不同值

    • id 值不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
      例如：explain select * from tbl_emp where tbl_emp.deptId in (select id from tbl_dept);
      执行结果：
      MySQL进阶 explain 执行计划详解
      由于 id 值大的优先执行，所以最后 MySQL 优化器的 SQL 执行结果是，先读取表 tbl_dept，然后读取表 tbl_emp。

  • 既有相同值又有不同值

    • 先根据不同值，id 值越大的优先被执行，然后 id 值相同的，从上到下顺序执行

select_type 字段

即数据读取操作的操作类型、查询类型，主要是用来区别普通查询、联合查询、子查询等复杂查询。

其值有六种情况：

  • SIMPLE

    • 简单查询即查询语句中不含有子查询或者 UNION

  • PRIMARY

    • 查询中包含复杂的子部分，最外层查询则被标记为 PRIMARY

  • SUBQUERY

    • 在 SELECT 或 WHERE 列表中包含了子查询
      例如：explain select * from tbl_emp where tbl_emp.deptId in (select id from tbl_dept);
      查询结果：
      MySQL进阶 explain 执行计划详解

  • DERIVED

    • 在FROM 列表中包含的子查询被标记为 DERIVED（衍生），同时 MySQL 会递归执行这些子查询，把结果放到临时表里。
      例如：
      explain select tbl_emp.*,d1.deptName from (select tbl_dept.* from tbl_dept) d1,tbl_emp where d1.id=tbl_emp.deptId;
      查询结果：
      MySQL进阶 explain 执行计划详解

  • UNION

    • 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；
      若 UNION 包含在 FROM 语句的子查询中，外层 SELECT 将被标记为：DERIVED
      例如：
      explain select * from tbl_emp e left join tbl_dept d on e.deptId=d.id
      union
      select * from tbl_emp e right join tbl_dept d on e.deptId=d.id;
      MySQL进阶 explain 执行计划详解

  • UNION RESULT

    • 从 UNION 表获取结果的 SELECT

table 字段

显示这一行的数据是关于哪张表的。

type 字段

显示查询使用了何种类型，是判断是否需要优化 SQL 的一个重要指标。（如果有上百万条记录，查询类型为 ALL ，则需要进行建立索引）

访问类型值从最好到最坏排列：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > range > index > ALL

在工作中最常见的为以下几种类型：
system > const > eq_ref > ref > range > index > ALL

一般来说至少保证查询达到range 级别，最好能达到ref 级别。

  • system

    • 表只有一行记录（等同于系统表），是 const 类型的特例，平常不会出现，可以忽略不计。

  • const

    • 表示通过索引一次就找到了，const 用来比较 PRIMARY KEY 或者 UNIQUE 索引。因为只匹配一行数据，所以很快。也就是说需要将主键或者唯一索引键置于 WHERE 列表中作为查询条件。
      例如：将主键 id 作为子查询的查询条件：
      explain select * from (select * from tbl_emp where id=1) t1;MySQL进阶 explain 执行计划详解

  • eq_ref

    • 唯一索引的扫描，对于每一个索引键，都只有一条记录与之匹配。常见于主键索引和唯一索引。

      例如：explain select user.*,role.* from user,role where user.id=role.user_id;（这里假设 user 表中得 user_name 的值不会重复）。
      MySQL进阶 explain 执行计划详解

  • ref

    • 非唯一性索引扫描，返回匹配某个单独值的所有行。
      本质上也是一种索引访问，它返回匹配某个单值的所有行，也就以为这它会找到多个符合条件的行，所以它属于查找和扫描的混合体。

      例如：
      alter table role add index index_userid_rolename(user_id,role_name);
      explain select * from role where role.user_id=1;
      MySQL进阶 explain 执行计划详解

  • range

    • 只检索指定范围的行
      一般就是在 where 语句中出现了 between、< 、>、in 等的查询
      这种范围索引扫描比全表扫描要好，因为它只开始于索引的某一点，而结束于另一点，不用扫描全部索引。

      例如：
      explain select * from role where id between 1 and 3;MySQL进阶 explain 执行计划详解

  • inde

    • 全索引扫描。index 和 ALL 的区别为 index 类型只遍历索引树，也就是说虽然 all 和 index 都是读全表，但是 index 是从索引中读取，而 all 是从硬盘中读取。通常 index 比 ALL 快，因为 索引文件通常比数据文件小。
      例如：
      explain select id from role;
      MySQL进阶 explain 执行计划详解

  • ALL

    • 遍历全表找到匹配的行（也就意味着查询语句中没有使用到索引列）
      例如：
      explain select * from role;
      MySQL进阶 explain 执行计划详解

有百万级别或者千万级别数据当出现 ALL 类型的查询语句时，就需要对查询语句进行优化。一般来说至少得保证查询达到 range 级别，最好能达到 ref 级别。

possible_key 字段

显示该条查询语句可能会用到的索引列。（但实际查询不一定会用到）

例如：
explain select * from role where id > 2 and user_id >2 ;
MySQL进阶 explain 执行计划详解

key 字段

显示该条查询语句实际上用到的索引列。

如果 key 的值为 NULL 就说明要么没有建索引要么建了索引但是没有用也就是所谓的索引失效。

查询中若使用到了覆盖索引（即在查询语句中，SELECT 所查询的列是符合索引的部分列或全部列，与顺序无关，同时不可使用 select *），则该索引只出现在 key 列表中。

例如：

// 首先建立符合索引
alter table role add indexindex_userid_rolename(user_id,role_name);

explain select role_name from role;

explain select role_name from role;

explain select user_id,role_name from role;

explain select role_name,user_id from role;

MySQL进阶 explain 执行计划详解

key_len 字段

表示索引中使用的字节数，可通过该列计算出查询中所使用的索引的长度，在不损失精度的情况下，长度越短越好。

key_len 显示的值为索引的最大长度，并非实际使用长度。

如果是单列索引， key_len 就是整个索引的长度；如果是符合索引，由于查询不一定会用到所有的列，所以用多少算多少。

例如：
MySQL进阶 explain 执行计划详解

同时 key_len 只计算 where 查询条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len 中。

ref 字段

显示索引的哪一列被使用了，有可能是一个常数（const）。

可以根据该字段，来分析表之间的引用。

例如：

explain  select* from user left join role on user.id=role.user_id;

MySQL进阶 explain 执行计划详解

rows 字段

根据表统计信息及索引选用情况，大致估算该条查询语句所要读取的数据行数。

例如：

// 没加索引前的查询
explain select* from tbl_emp,tbl_dept where tbl_emp.deptId=tbl_dept.id and tbl_dept.deptName='RD';// 添加符合索引
alter table tbl_dept add indexindex_deptName_locAdd(deptName,locAdd);// 添加索引后的查询
explain select* from tbl_emp,tbl_dept where tbl_emp.deptId=tbl_dept.id and tbl_dept.deptName='RD';

MySQL进阶 explain 执行计划详解

Extra 字段

用来显示除了前面字段的其他额外的重要信息。（比如分组 Group By）

其值有以下情况：

  • Using filesort（重点）

    • 优化器执行查询语句时，产生了文件类排序。（出现该信息，说明查询语句需要优化）

  • Using temporary（重点）

    • 优化器执行查询语句时，使用临时表保存中间结果，MySQL在对查询结果排序时使用到了临时表。（出现该信息，说明查询语句及其需要优化）
      例如：查询语句中通过符合索引（或部分列）分组
    // 通过符合索引的部分列排序
explain select user_id from role where user_id in(1,2,3) group by dept_id\G// 通过符合索引的部分列排序
explain select user_id from role where user_id in(1,2,3) group by role_name,dept_id\G
explain select user_id from role where user_id in(1,2,3) group by user_id,dept_id\G

explain select user_id from role where user_id in(1,2,3) group by user_id,role_name\G// 通过符合索引的全部列排序
explain select user_id from role where user_id in(1,2) group by user_id,role_name,dept_id\G

    MySQL进阶 explain 执行计划详解
    由此可见，当查询语句通过符合索引的列分组时，出现 Using filesort、Using temporary 信息，需要按照符合索引的前两列或者全部列进行分组即可完成其优化。

  • Using index（重点）

    • 表明该执行该查询语句时使用到了覆盖索引（即在查询语句中，SELECT 所查询的列是符合索引的部分列或全部列，与顺序无关，同时不可使用 select *）。避免了访问表的数据，效率比较高。
      如果同时出现 Using where ，表明索引被用来进行索引键值的查找，即索引列用在了 where 后面作为查询条件。
      例如： 
      explain select user_id from role where user_id=1;

      MySQL进阶 explain 执行计划详解
      如果没有出现 Using where，表明索引用来读取数据而非执行条件查询动作。也就是说在 select 中出现 符合索引的列，在 where 后没有使用到 符合索引的列。
      例如：explain select user_id,role_name from role;
      MySQL进阶 explain 执行计划详解

  • Using where

    • 表明使用了 where 过滤

  • Using join buffer

    • 使用了连接缓存，即在查询语句中使用到了 join （注意左连接和右连接不会出现该信息）。
      例如：
    explain select* from user join role on user.id=role.user_id;

    MySQL进阶 explain 执行计划详解

  • impossible where

    • where 子句的值总是 false，不能用来获取任何元组。
      例如：
    explain select* from user where name='张三' and name='王五';

    MySQL进阶 explain 执行计划详解

  • select tables optimized away

    • 在没有 Group By 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  • distinct

    • 优化 distinct 操作，在找到第一个匹配的元组后立即停止查找同样值的操作。

Extra的 前三个值是重点，必须掌握，后三个值了解即可。

  • 作者：Herz001
  • 原文链接：https://blog.csdn.net/tf835991342/article/details/120615389
    更新时间：2022年9月28日09:13:36 ，共 5201 字。