MySQL进阶之 explain 执行计划

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

• 1、什么是 explain
• 2、explain 能干啥
• 3、explain 怎么使用
• 4、各字段的解释
• • id 字段
  • select_type 字段
  • table 字段
  • type 字段
  • possible_key 字段
  • key 字段
  • key_len 字段
  • ref 字段
  • rows 字段
  • Extra 字段

1、什么是 explain

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

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

2、explain 能干啥

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

3、explain 怎么使用

explain + SQL 语句

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

4、各字段的解释 id 字段

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

其值有三种情况：

• 相同值

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

• 不同值

  • id 值不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
    例如：explain select * from tbl_emp where tbl_emp.deptId in (select id from tbl_dept);
    执行结果：
    
    由于 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);
    查询结果：
    

• 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;
    查询结果：
    

• 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;
    

• 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;

• eq_ref

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

    例如：explain select user.*,role.* from user,role where user.id=role.user_id;（这里假设 user 表中得 user_name 的值不会重复）。
    

• ref

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

    例如：
    alter table role add index index_userid_rolename(user_id,role_name);
    explain select * from role where role.user_id=1;
    

• range

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

    例如：
    explain select * from role where id between 1 and 3;

• inde

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

• ALL

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

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

possible_key 字段

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

例如：
explain select * from role where id > 2 and user_id >2 ;

key 字段

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

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

查询中若使用到了覆盖索引，则该索引只出现在 key 列表中。

例如：

// 首先建立符合索引
alter table role add index index_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;

key_len 字段

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

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

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

例如：

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

ref 字段

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

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

例如：

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

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 index index_deptName_locAdd(deptName,locAdd);

// 添加索引后的查询
explain select * from tbl_emp,tbl_dept where tbl_emp.deptId=tbl_dept.id and tbl_dept.deptName='RD';

Extra 字段