MySQL 优化：explain 执行计划详解

• 一、准备测试数据
• 二、什么是explain 执行计划
• • 2.1 执行计划的定义
  • 2.2 执行计划有什么用处
  • 2.3 使用方法
  • 2.4 执行计划的结果集
• 三、参数详解
• • 3.1 id
  • 3.2 select_type
  • • 3.2.1 simple
    • 3.2.2 primary、subquery
    • 3.2.3 derived、union、union result
  • 3.3 table
  • 3.4 partitions
  • 3.5 type
  • • 3.5.1 ALL
    • 3.5.2 index
    • 3.5.3 range
    • 3.5.4 ref
    • 3.5.5 eq_ref
    • 3.5.6 const
    • 3.5.7 system
  • 3.6 possible_keys
  • 3.7 key
  • 3.8 key_len
  • • 3.8.1 定义
    • 3.8.2 计算方式
  • 3.9 ref
  • 3.10 rows
  • 3.11 filtered
  • 3.12 Extra
  • • 3.12.1 using filesort
    • 3.12.2 Using temporary
• 四、总结

一、准备测试数据

MySQL 版本：5.7.35

建立课程表、教师表、教师证表如下：

create table course(cid int(3),cname varchar(20),tid int(3));create table teacher(tid int(3),tname varchar(20),tcid int(3));create table teacherCard(tcid int(3),tcdesc varchar(200));

分别添加数据如下：

insert into course values(1,'java',1);insert into course values(2,'html',1);insert into course values(3,'sql',2);insert into course values(4,'web',3);insert into teacher values(1,'tz',1);insert into teacher values(2,'tw',2);insert into teacher values(3,'tl',3);insert into teacherCard values(1,'tzdesc') ;insert into teacherCard values(2,'twdesc') ;insert into teacherCard values(3,'tldesc') ;

二、什么是explain 执行计划

2.1 执行计划的定义

什么是执行计划？简而言之，就是 SQL 在数据库中执行时的表现情况，通常用于 SQL 性能分析、优化和加锁分析等场景，执行过程会在 MySQL 查询过程中由解析器，预处理器和查询优化器共同生成。

在 MySQL 中使用 explain 关键字来查看。

2.2 执行计划有什么用处

它可以用来分析 SQL 语句和表结构的性能瓶颈：

• 关联查询的执行顺序
• 查询操作的操作类型
• 哪些索引可以被命中
• 哪些索引实际被命中
• 每张表有多少记录参与查询

2.3 使用方法

在 select 语句前加上 explain

2.4 执行计划的结果集

explain 的结果集：

1. id：执行编号
2. select_type：查询类型
3. table：表
4. partitions：命中的分区
5. type：类型
6. possible_keys：预测用到的索引
7. key：实际使用的索引
8. key_len：实际使用索引的长度
9. ref：表之间的引用
10. rows：通过索引查询到的数据量
11. filtered：实际命中数据量的占比
12. Extra：额外的信息

三、参数详解

3.1 id

在这里，id 实际上就代表着 sql 语句的执行顺序。

1. id 值相同时，从上往下，顺序执行
2. id 值不同时，id值越大越优先查询

示例分析：查找教授SQL 课程的老师的描述

EXPLAIN SELECT teacherCard.tcdesc FROM teacherCard, teacher WHERE teacherCard.tcid = teacher.tcid AND teacher.tid = (SELECT course.tid FROM course WHERE course.cname = "sql");

从结果上看，course 表对应的 sql 语句最先执行，其后是 teacher 表，最后是 teacherCard 表。

也就是说，在执行嵌套子查询时，会先执行内层的子查询语句，再执行外层的语句。

那么为什么外层语句的执行顺序是先 teacher 再 teacherCard 表呢？

事实上，这个 select 的返回结果集是笛卡尔积。

出于对性能的考虑，MySQL 会将数据量小的表或子结果作为笛卡尔积的左域，也就是会优先查询数据量小的数据表。

具体的由MySQL 查询优化器进行选择。

3.2 select_type

select_type，显示本行是简单或复杂查询。

3.2.1 simple

simple，最简单的查询，在查询中不包含子查询或者 union 交并差集等操作。

示例：查询course 表的所有数据

EXPLAIN SELECT * from course;

3.2.2 primary、subquery

primary，当查询语句中包含任何复杂的子部分(union 或子查询)，最外层查询则被标记为 primary。

subquery，当查询语句中包含任何复杂的子部分(union 或子查询)，非最外层查询则被标记为 subquery。

示例分析：查找教授SQL 课程的老师的描述

EXPLAIN SELECT teacherCard.tcdesc FROM teacherCard, teacher WHERE teacherCard.tcid = teacher.tcid AND teacher.tid = (SELECT course.tid FROM course WHERE course.cname = "sql");

SELECT course.tid FROM course WHERE course.cname = “sql” 为子查询语句，因此被标记为subquery ，而最外层的select 语句则是被标记为primary 。

3.2.3 derived、union、union result

derived，衍生查询，使用到了临时表。

derived 分为两种情形：

1. 在 from 子查询中，只有一张表
2. 在 from 子查询中，如果有 t1 union t2 ，则 t1 的 select_type 为derived ，t2 为 union

示例分析：

EXPLAIN select cr.cname from (select * from course limit 2) cr;

可以看到最外层查询的table 列是 ，这表示涉及到了衍生表，对应的数据集为执行编码id 为2 的查询结果集。

示例分析：

EXPLAIN select cr.cname from (select * from course limit 2) cr union select cr.cname from (select * from course limit 3) cr

可以看到执行计划的最后一行的select_type 是 union result ，对应的 table 列的结果是 ，表示对执行编码 id 为 1 和 3 的查询结果进行了 union 操作。

3.3 table

table，查询的表名，并不一定是真实存在的表，也可能为临时表。

3.4 partitions

partitions，查询时匹配到的分区信息，对于非分区表值为 NULL，当查询的是分区表时，partitions 显示分区表命中的分区情况。

3.5 type

type，联接类型，显示了连接使用了哪种类别、有无使用索引，在 SQL 优化中是一个非常重要的指标。

性能从好到坏依次是：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

要对 type 进行优化的前提：有索引。

以下我们只介绍几种最常见的类型：

3.5.1 ALL

ALL，全表扫描，通常意味着 MySQL 必须从头到尾扫描整张表，去查找匹配的行的行，性能极差。

但是，如果在查询里使用了 LIMIT n，虽然 type 依然是 ALL，但是 MySQL 只需要扫描到符合条件的前 n 行数据，就会停止继续扫描。

3.5.2 index

index，按索引次序全表扫描，避免了排序的开销。

示例：

create INDEX name_index on course(cname);EXPLAIN select cname from course ;

3.5.3 range

range，范围扫描，一个有限制的索引扫描。

范围扫描分为以下两种情况：

1. 范围条件查询：在 WHERE 子句里带有 BETWEEN、>、=、<= 的查询。
2. 多个等值条件查询：使用 IN() 和 OR ，以及使用 like 进行前缀匹配模糊查询。

示例：

EXPLAIN select cname from course where cname like 's%';

3.5.4 ref

ref，索引访问，返回所有匹配索引值的数据行，每个索引可能有 0 个或多个匹配的数据行。

只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。

示例：

create INDEX tid_index on course(tid);EXPLAIN select * from course where tid = 1;

3.5.5 eq_ref

eq_ref，唯一性索引，对于每个索引键的查询，只能返回唯一一行匹配的数据，不能多也不能少。

常见于唯一索引、主键索引。

示例：

alter table teachercard add CONSTRAINT pk_tcid PRIMARY key(tcid);alter table teacher add CONSTRAINT uk_tcid unique index(tcid);EXPLAIN select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid;

3.5.6 const

const，最多只会有一条记录匹配。只见于唯一索引和主键索引进行等值条件查询。

示例：

create UNIQUE INDEX id_index on course(cid);EXPLAIN select * from course where cid = 1;

3.5.7 system

官方文档原文是：

The table has only one row (= system table). This is a special case of the const join type.

该表只有一行（=系统表）。这是 const 关联类型的特例。

示例：从系统库 mysq l的系统表 proxies_priv 里查询数据，这里的数据在Mysql 服务启动时候已经加载在内存中，不需要进行磁盘IO 。

EXPLAIN SELECT * FROM `mysql`.`proxies_priv`

3.6 possible_keys

possible_keys，可能用到的索引，只是一种预测，不一定准。

3.7 key

key，实际使用到的索引。

3.8 key_len

3.8.1 定义

key_len，实际使用到的索引的长度，可以用来判断复合索引中使用到的具体索引。

在不损失精确性的情况下，原则上长度越短越好。

key_len 只计算 where 条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到 key_len 中。

3.8.2 计算方式

key_len 索引长度的计算方式：

• 对于所有的索引字段，如果没有设置为 not null，则加 1 个字节。
• int 占 4 个字节，date 占 3 个字节，char(n) 占 n 个字符，varchar(n) 占 n 个字符 +2 个字节。
• 对于不同的字符集，一个字符所占用的字节数也不一样：
  1. latin1 编码一个字符占用一个字节
  2. gbk 编码一个字符占用两个字节
  3. utf8 编码一个字符占用三个字节
  4. utf8mb4 编码一个字符占用四个字节

示例分析：

EXPLAIN select * from course where cid = 1;

可以看到，key_len 为5 ，由 int 的 4 个字节 + null 的 1 个字节构成。

3.9 ref

ref，指明当前表所参照的字段或常量。

示例分析：

EXPLAIN select * from course where cid = 1;

可以看到ref 这一列的值是const，因为cid 索引的条件值是一个常量。

EXPLAIN select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid;

可以看到，tc 表的ref 值是testmysql.t.tcid，表示tc.tcid 的参照字段是testmysql 数据库的 t 表的 tcid 列。

3.10 rows

rows，实际通过索引查询到的数据行数。

3.11 filtered

filtered，命中率，表里符合条件的记录数所占百分比。

3.12 Extra

Extra，额外的补充信息，对SQL 优化有重要作用。

常见类型：

3.12.1 using filesort

using filesort，需要一次额外的排序，常见于order by 或 group by 没有命中索引时。需要进行优化。

示例分析：

EXPLAIN select * from course where cname = 'sql' order by tid;

对于单列索引，where 条件的字段与order by 的字段不一致时，会产生Using filesort。

对于复合索引，如果不遵循最左前缀原则，也会产生Using filesort 。

drop index id_index on course;drop index name_index on course;drop index tid_index on course;create INDEX id_name__tid_index on course(cid,cname,tid);EXPLAIN select * from course where cname = 'sql' order by tid;

3.12.2 Using temporary

Using temporary，用临时表保存中间结果，常用于order by 或 group by 操作中。需要进行优化。

产生条件：

• 如果group by 的列没有索引，产生临时表
• 如果group by 的列有索引，order by 的列没索引，产生临时表
• 如果group by 的列和order by 的列不一样，即使都有索引也会产生临时表

示例：

explain select tid from teacher group by tid ;

四、总结

参考视频：SQL优化（MySQL版）

注意：视频内容不一定匹配当前数据库版本。

暂时先到这里吧，后续需要补充的话，再在文末进行添加。

我是陈冰安，Java 工程师，时不时也会整一整Linux 。
欢迎关注我的公众号【暗星涌动】，愿与你一同进步。