学习数据库查询优化技术,第一步需要看明白查询执行计划,根据查询执行计划理解查询优化器的执行过程,体会优化技术的运用情况。所以,读懂查询执行计划是掌握查询优化技术的必要条件。
以下从MySQL查询执行计划的格式和关键字,介绍MySQL的查询执行计划,并结合实例,帮助读者理解查询执行计划。
示例 演示如何阅读MySQL的查询执行计划。先创建5张表,命令如下(各表的数据量为:t1表10000行数据,t2表100行数据,t3表100行数据,t4表7行数据,t5表10行数据):
CREATE TABLE t1(id1 INT,a1 INT,b1INT,PRIMARY KEY(id1));
CREATE TABLE t2(id2 INT,a2 INT,b2 INT);
CREATE TABLE t3(id3 INT UNIQUE,a3 INT,b3INT);
CREATE TABLE t4(id4 INT,a4 INT,b4 INT);
CREATE TABLE t5(id5 INT UNIQUE,a5 INT,b5INT);
使用下面的存储过程插入表中的数据:
DELIMITER //
CREATE PROCEDURE proc01()
begin
declare var int;
set var=0;
while var<1000 do
insert into t1 values(var,var,var);
set var=var+1;
end while;
end;
//
DELIMITER ;
注:具体的测试环境搭建过程,我在另一个博客中有所描述,可以参考。
MySQL的查询执行计划解释
MySQL 5.6.10 版本使用Explain类表示查询执行计划。通过EXPLAIN这个SQL语句触发,显示查询执行计划。
1. EXPLAIN功能
语法格式:
EXPLAN [explain_type] explainable_stmt
可选项包括:
EXTENDED | PARTITIONS | FORMAT=format_name
Format_name:
TRADITIONAL |JSON
说明:
EXPLAIN命令:显示SQL语句的查询执行计划。
EXPLAIN EXTENDED命令:显示SQL语句的详细的查询执行计划;之后可以通过 show warnings命令查看详细的信息。
EXPLAIN PARTITIONS命令:显示SQL语句的带有分区表信息的查询执行计划。
EXPLAIN命令:输出格式有以下两种:
TRADITIONAL:传统类型;按行隔离,每行标识一个子操作。
JSON:JSON格式。
Explainable_stmt:可被EXPLAIN执行的SQL语句,包括的类型有:select、insert、update、delete。
2. 查询执行计划
理解MySQL查询执行计划,需要理解执行顺序和结点解析两个部分。
1) 执行顺序
执行5表连接的查询语句,演示MySQL的查询执行计划样式,语句如下:
EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true),(t3 FULL JOIN t4 ON true),t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;
执行SQL,结果如下所示:
EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true),(t3 FULL JOIN t4 ON true),t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 192 | Using where |
| 1 | SIMPLE | FULL | ref | id3 | id3 | 5 | test.t2.id2 | 1 | NULL |
| 1 | SIMPLE | t5 | ref | id5 | id5 | 5 | test.t2.id2 | 1 | NULL |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id2 | 1 | NULL |
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 400 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
5 rows in set (0.00 sec)
> EXPLAIN SELECT * FROM (t1 LEFT JOIN t2 ON true),(t3 FULL JOIN t4 ON true),t5 WHERE id1=id2 AND id2=id3 AND id3=id4 AND id4=id5;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
| 1 | SIMPLE | FULL | ref | id3 | id3 | 5 | test.t4.id4 | 1 | NULL |
| 1 | SIMPLE | t5 | ref | id5 | id5 | 5 | test.t4.id4 | 1 | NULL |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t4.id4 | 1 | NULL |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------------------------------------------+
5 rows in set (0.00 sec)
下面对该执行结果进行简要分析:
从第1行到第9行,表示了完成的查询计划;
第1行到第3行,表明查询计划的结构;id表示对象被操作的顺序;ID值大,先被执行;如果相同,执行顺序从上到下;
从第4行起,每一行为一个结点,表示本结点被操作对象的可用信息,如索引等;
表的连接次序为:t4,t5,t3,t1,t2.这和初始给定的连接次序不同,经过优化,外连接被消除;
T4表的元组数最少,安装mysql多表连接算法,表经过排序后,顺序为t4,t5,t2,t3,t1.
因为t5,t3,t1上有索引可以利用,所以t4上的一条元组确定后,则额可以利用索引之间定位t5,t3,t1表上的元组,所以第5,6,7行的key列又索引可用;ref列表明了这3个表都是引用了t4表的id4列。
t2表的数据相对较多,且又没有索引,最后被连接,连接使用了Extra列表明的嵌套循环连接算法,并且使用了连接缓存。
2) 结点解析
MySQL查询计划的输出列的含义如下:
id:每个被独立执行的操作的标识,表示对象被操作的顺序;ID值大,先被执行;如果相同,执行顺序从上到下;
select_type:查询每个select子句中的类型;具体值如下表所示;
table:名字,被操作的对象名称,通常是表明,但又其他格式。
partitions:匹配的分区信息(对于非分区表值为NULL)
type:连接操作的类型;具体值见后面的表格;
possible_keys:备选的索引(列出可能被使用到的索引)
key:经过优化器选定的索引;常用analyzetable命令,可以使优化器正确地选择索引。
key_len:被优化器选定的索引键的长度,单位是字节。
ref:表示本行被操作的对象的参照对象(被参照的对象可能是一个常量用const表示,也可能是其他表的key指向的对象)。
rows:查询执行所扫描的元组个数(对于InnoDB,此值是估计值)。
filtered:安装条件表上数据被元组过滤的元组个数的百分比,rows*filetered/100可以求出过滤后的元组数即实际的元组数。
extra:MySQL查询优化器执行查询过程中对查询计划的重要补充信息。
MySQL查询执行计划select_type列子句类型表
Select_type | 说明 |
SIMPLE | 简单的select语句(不包括UNION操作或子查询操作) |
PRIMARY | 查询中最外层的select(如两表做UNION或存在有子查询,外层的表被称作primary,内层被作为UNION) |
UNION | UNION操作中,查询中处于内层的select(内存的select语句与外层的select语句没有依赖关系) |
DEPENDENT UNION | UNION操作中,查询中处于内层的select(内存的select语句与外层的select语句有依赖关系) |
UNION RESULT | UNION操作的结果,ID通常为NULL |
SUBQUERY | 子查询中的首个select(如果有多个子查询存在) |
DEPENDENT SUBQUERY | 子查询中的首个select,但依赖于外层的表(如果有多个子查询存在) |
DERIVED | 被驱动的select子查询(子查询位于from子句) |
MATERIALIZED | 被物化的子查询 |
UNCACHEABLE SUBQUERY | 对于外层的主表,子查询不可被物化,每次都需要计算(耗时操作) |
UNCACHEABLE UNION | UNION操作中,内层的不可物化的子查询(类似于UNCACHEABLE SUBQUERY) |
连接操作数据访问方式表
代码表示方式 | 说明 |
JT_SYSSTEM | 常量表情况一,表上只有一条元组匹配 |
JT_COUNST | 常量表情况二,where条件筛选后表上至多有一条元组匹配,如:where table.pk=2(pk列是主键列,值为2的要么有一条,要么没有) |
JT_EQ_REF | 参与连接运算的表,是内表(在嗲吗实现的算法中,两表连接时作为循环中的内循环遍历对象,这样的表为内表)。基于索引(连接字段上存在唯一索引或主机索引,且操作符必须是“=”谓词,索引的值不能为NULL)做扫描,使得对外表的一条元组,内表只有唯一一条元组与之对应 |
JT_REF | 可用于单表索引或连接。参与连接运算的表,是内表。基于索引(连接字段上的索引是非唯一索引,操作符必须是“=”谓词,连接字段值不可为NULL)做扫描,是的对外表的一条元组,内表可有若干条元组与之对应 |
JT_REF_OR_NULL | 类似ref,只是搜索条件敖阔:连接字段的值可为NULL的情况,如:where col=… or col IS NULL |
JT_RANGE | 范围扫描。基于索引做范围扫描,为诸如 BETWEEN,IN,>=,like类操作提供支持 |
JT_INDEX_SCAN | 索引做扫描。是基于索引在索引的叶子结点上找满足条件的数据(不需要访问数据文件) |
JT_ALL | 全表扫描或范围扫描;不使用索引,顺序扫描,直接读取表上的数据(访问数据文件) |
JT_UNIQUE_SUBQUERY | 在子查询中,基于唯一索引进行扫描。类似于eq_ref |
JT_INDEX_SUBQUERY | 在子查询中,基于除唯一索引之外的索引进行扫描 |
JT_INDEX_MERGE | 多重范围扫描,两表连接的每个表的连接字段上均有索引存在且索引有序,结果合并在一起。适用于做集合的并、交操作 |
JT_FT | FT,FullText,全文检索 |
Extra信息解释表
Extra值 | 说明 |
Child of ‘table’ pushed join@l | MySQL cluster使用的参数。表示在连接操作中某个表被下推到NDB引擎的某个结点上执行 |
Const row not found | 在连接中,查询一个没有元组的空表 |
Deleting all rows | 对于删除操作,某些存储引擎支持快速删除数据 |
Distinct | 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作 |
FirstMatch(tb1_name) | 半连接算法中对表采取了受此匹配的连接策略 |
Full scan on NULL key | 子查询中,优化器不能使用基于索引的访问方式时(因有空值存在)所采取的一种数据访问策略 |
Impossible HAVING | Having的子句值总是FALSE(不能获取任何元组) |
Impossible WHERE | Where子句的值总是FALSE(不能获取任何元组) |
Materialize,Scan | MySQL 5.6.7版本之前,表示使用单一被物化的临时表;之后的版本,物化是通过select_type列表达的 |