面试官：如何使用explain来分析SQL执行性能？

xunaa

2024-10-05 07:03:54

编辑说

介绍
在工作中，我们捕获性能问题最常用的方法就是打开慢查询，定位执行效率差的SQL。然后当我们找到一条SQL时，我们还没有完成。我们还需要知道SQL的执行计划。比如全表扫描或者

介绍

在工作中，我们捕获性能问题最常用的方法就是打开慢查询，定位执行效率差的SQL。然后当我们找到一条SQL时，我们还没有完成。我们还需要知道SQL的执行计划。比如全表扫描或者索引扫描，这些都需要通过EXPLAIN来完成。 EXPLAIN 命令是查看优化器如何决定执行查询的主要方式。它可以帮助我们深入了解MySQL基于成本的优化器，也可以获取优化器可能考虑的很多访问策略的详细信息，以及优化器在运行SQL语句时预计会采用哪些策略。

下面我们来详细分析一下。准备的数据如下：

课程表

创建表`course` ( `cid` int(3) NOT NULL, `cname` varchar(20) NOT NULL, `tid` int(3) NOT NULL, PRIMARY KEY (`cid`) USING BTREE) ENGINE=InnoDB DEFAULT字符集=utf8mb4；

教师名单

创建表`teacher` ( `tid` int(3) NOT NULL, `tname` varchar(20) NOT NULL, `tcid` int(3) NOT NULL, PRIMARY KEY (`tid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4；

教师名片表

创建表`teacher_card` ( `tcid` int(3) NOT NULL, `tcdesc` varchar(20) NOT NULL, PRIMARY KEY (`tcid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id

查询课程号为2或教师证号为3的教师信息

从课程c、教师t、教师卡tc 中选择t.*，其中c.tid=t.tid AND t.tcid=tc.tcid AND ( c.cid=2 OR t.tcid=3 )

解释完上面的SQL后，如下

如果id值相同，则从上到下依次执行。

为什么要先从t表读取数据，然后与tc表进行表连接，最后与c表进行表连接？（上面的SQL可以改写为join on的形式，执行效果是一样的）

我们写的SQL会被优化器优化。 MySQL 将以小结果集驱动大结果集的方式连接表。表连接是两个表的笛卡尔积，因此连接的顺序是t(3)-tc(3)-c(4)，即括号中的数字。如果表数t为5，则连接顺序为tc(3)-c(4)-t(5)。注意，括号中的数字不是表的数量，而是结果集的数量。

如果查询2号课程的老师信息

从课程c、教师t、教师卡tc 中选择t.*，其中c.tid=t.tid AND t.tcid=tc.tcid AND c.cid=2

因为可以确定c的结果集最小，只有一条记录，所以连接顺序为c(1)-t(3)-tc(3)

查询教授SQL课程的老师的描述

SELECT tc.tcdesc FROM Teacher_card tc WHERE tc.tcid=(SELECT t.tcid FROM Teacher t WHERE t.tid=(SELECT c.tid FROM course c WHERE c.cname='sql' ) );

该SQL先查询c表，然后查询t表，最后查询tc表。运行解释看看。

id值不同。 id值越大，执行优先级越高。将上面的SQL改成下面的形式

SELECT tc.tcdesc FROM Teacher t, Teacher_card tc WHERE tc.tcid=t.tcid AND t.tid=( SELECT c.tid FROM course c WHERE c.cname='sql' )

id值可以相同也可以不同。 id值越大，优先级越高。如果id值相同，就会按照从上到下的顺序执行。

select_type

SIMPLE：查询不包含子查询和UNION

从老师那里选择*

PRIMARY：查询有任何复杂的子部分，则外部部分被标记为PRIMARY

SUBQUERY：SELECT 列表中的子查询中包含的SELECT（换句话说，不在FROM 子句中）被标记为SUBQUERY

查询教授SQL课程的老师的描述

SELECT tc.tcdesc FROM Teacher_card tc WHERE tc.tcid=(SELECT t.tcid FROM Teacher t WHERE t.tid=(SELECT c.tid FROM course c WHERE c.cname='sql' ) );

DERIVED：DERIVED 值用于表示FROM 子句的子查询中包含的SELECT。 MySQL将递归执行它并将结果放入临时表中。服务器内部将其称为“派生表”，因为临时表是从子查询派生的

SELECT cr.cname FROM ( SELECT * FROM course WHERE tid IN ( 1, 2 ) ) cr

id为1的表的表名是derivative2，表示它是一个派生表。派生表来自id 2的执行过程。

UNION：UNION 中的第二个及后续SELECT 被标记为UNION。第一个SELECT 被标记为好像它是作为外部查询的一部分执行的。这就是为什么下面第一个示例中UNION 中的第一个SELECT 显示为PRIMARY。如果FROM 子句中的子查询包含UNION，则其第一个SELECT 将被标记为DERIVED，这是下面的第二个示例

UNION RESULT：用于从UNION 的匿名临时表检索结果的SELECT 被标记为UNION RESULT

SELECT * FROM course WHERE tid=1 UNION SELECT * FROM course WHERE tid=2

SELECT cr.cname FROM ( SELECT * FROM course WHERE tid=1 UNION SELECT * FROM course WHERE tid=2 ) cr

type

索引类型，优化类型的前提是有索引。 Type类型有很多，这里就不一一介绍了。我就介绍几个常用的，性能会从最好到最差。

常量，systemeq_refrefrangeindexall

其中system和const只是理想情况，实际上可以实现refrange。

system：只有一条数据的系统表，或者派生表中只有一条数据的子查询

SELECT a.tname FROM ( SELECT * FROM Teacher t WHERE t.tid=1 ) a

const：只能查找一条数据的SQL，用于主键或唯一索引。如果只能找到一条数据，但条件列上没有主键或唯一索引，则不是const。

从老师t 中选择*，其中t.tid=1

如果我们去掉tid上的主键（本例只是临时更改），执行下面的语句，结果如下

从老师t 中选择*，其中t.tid=1

此时type为ALL，验证了我们的想法，虽然只有一个结果，但是条件列上没有主键或唯一索引，也不是const

eq_ref：唯一索引。对于每个键查询，返回匹配的唯一行数据（有且只有一个，不能多，不能0）。常见于唯一索引和主键索引。没有必要有索引。这里是我的句子There is no index added to the tcid field in the Teacher 表的一个例子。当然，我的数据太简单了，所以eq_ref可以出现，无需索引。

从教师t、教师卡tc 中选择t.tcid，其中t.tcid=tc.tcid

我们看一下这个表中的数据

教师名单

教师名片表

此时，将针对teacher表的tcid键返回唯一一条数据，因此类型为eq_ref。如果我们在teacher表中添加一条数据（本例只是临时更改），teacher_card表将保持不变，数据如下

教师名单

重新执行上面的SQL

从教师t、教师卡tc 中选择t.tcid，其中t.tcid=tc.tcid

可以看到类型变成了ALL，因为对于教师表来说，键4返回0条数据，并不是每个键值都返回1条数据。

在上面的示例中，为了严谨起见，teacher_card 保持不变。如果向teacher_card表添加数据，然后执行eq_ref的两个示例，实际上没有影响，因为eq_ref可以为teacher key返回唯一的行。数据

ref：非唯一索引，对于每个索引键查询，返回所有匹配行（0，更多）