根据表、列、索引和WHERE子句中的条件的详细信息,MySQL优化器考虑了许多技术来有效地执行SQL查询中涉及的查找。对一个巨大表的查询可以在不读取所有行的情况下执行;涉及多个表的联接可以在不比较每个行组合的情况下执行。「优化器选择执行最有效查询的操作集称为“查询执行计划(query execution plan)”,也称为EXPLAIN计划。」
通过执行计划我们可以知道MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈,总的来说通过 EXPLAIN 我们可以做以下事情:
执行计划可以通过 「EXPLAIN」 、「DESCRIBE」、「DESC」关键字来查询的,但是一般会使用「EXPLAIN」进行查询,「DESCRIBE」主要用于查询表结构。EXPLAIN为SELECT语句中使用的每个表返回一行信息,它按照MySQL在处理语句时读取表的顺序列出输出中的表。
EXPLAIN语法如下:
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
| TREE
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
字段解释:
FORMAT = TRADITIONAL
在MySQL 8.0开始,EXTENDED和PARTITIONS已废弃,只有FORMAT一个选项。EXPLAIN PARTITIONS
生成的。为了向后兼容,仍然可以识别该语法,但现在默认启用分区输出,因此PARTITIONS关键字是多余的,已弃用。使用它会导致一个警告,并且在MySQL 8.0中从EXPLAIN语法中删除了它。TRADITIONAL
,以表格的形式输出。MySQL 8.0之前有JSON
和TRADITIONAL
两个选择,MySQL 8.0开始增加了TREE
输出格式。如果是使用JSON格式输出的话,执行计划中的NULL值将不会展示。「题外话:」 EXPLAIN关键字还可以像DESCRIBE、DESC或者SHOW COLUMNS
一样展示表结构。MySQL 8.0.18开始还可以使用EXPLAIN ANALYZE
语句来分析SQL语句。感兴趣的可以安装MySQL 8.0试试看。
先创建两张表用于测试,两张表结构除了user1多了一个唯一索引以外,其他都相同
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_name` varchar(40) NOT NULL DEFAULT '' COMMENT '全名',
`gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别',
`create_date` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COMMENT='用户表'
CREATE TABLE `user1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_name` varchar(40) NOT NULL DEFAULT '' COMMENT '全名',
`gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别',
`create_date` datetime NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `uk_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COMMENT='系统用户表'
执行下面SQL,查看执行计划都有哪些列
explain select * from user;
每个列代表的含义如下:
列名 | JSON格式的里面 | 含义 |
---|---|---|
id | select_id | SELECT 语句id |
select_type | None | SELECT 类型 |
table | table_name | 表名 |
partitions | partitions | 匹配的分区信息 |
type | access_type | 针对单表的访问方法 |
possible_keys | possible_keys | 可能用到的索引 |
key | key | 实际上使用的索引 |
key_len | key_length | 实际使用到的索引长度 |
ref | ref | 当使用索引列等值查询时, 与索引列进行等值匹配的对象信息 |
rows | rows | 预估的需要读取的记录条数 |
filtered | filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
Extra | None | 额外的信息 |
对select_type 字段解释之前先了解一下下面几个名词:
❝「子查询物化」:子查询的结果通常缓存在内存或临时表中。 「关联/相关子查询」:子查询的执行依赖于外部查询。多数情况下是子查询的 WHERE 子句中引用了外部查询包含的列。 ❞
SELECT查询的系列号,可以为NULL。id相同,执行顺序相同,从上往下执行;id不同,id越大越先被执行。那什么情况下id相同,什么情况下不同呢?
单表就不用过多说了,就一张表,id就一个肯定是相同的。 对于连接查询来说,一个 SELECT关键字后边的 FROM 子句中可以跟随多个 表,所以在连接查询的执行计划中,每个表都会对应一条记录,但是这些记录的 id 值都是相同的,比如:
EXPLAIN SELECT * FROM `user` INNER JOIN user1 ON `user`.id = user1.id
可以看到两条记录对应两张表,id是相同的。
为什么有可能相同也有可能不同的,那是因为查询优化器觉得这条包含子查询的语句可以使用连接查询进行优化, 就会对这条语句进行重写为连接查询,所以想知道查询优化器是否重写了查询语句,就可以通过explain查看。比如:
「未被优化的」:
EXPLAIN SELECT * FROM `user` WHERE id IN (SELECT id FROM user1) OR `user`.gender = 1;
执行计划结果:
「被优化的语句」:
EXPLAIN SELECT * FROM `user` WHERE id IN (SELECT id FROM user1)
执行计划结果:
可以看到被优化前后id是不同的,也就是执行顺序不同了。
EXPLAIN SELECT * FROM `user` union SELECT * FROM user1;
EXPLAIN 结果:
可以看到,user表和user1表之间用union 连接,结果却有三条记录,这是为什么呢?是因为UNION连接查询到的结果要去重,怎么去重呢?mysql是在其内部创建了临时表进行去重,这里可以看到表名是<unionM,N>其中,M、N分别代表两张表执行计划的id,第三行的id为NULL表明这个临时表是为了合并数据去重所创建的。上面我们说过id也会为空,就是在使用union进行查询的时候。
再看下面这条语句:
EXPLAIN SELECT * FROM `user` UNION ALL SELECT * FROM user1;
EXPLAIN 结果:
可以看到使用UNION ALL之后,结果就变成了2条记录,这是因为UNION ALL不需要去重,查到什么结果就返回什么结果,所以也不会出现临时表。
被查询的表名,可能是真实存在的表,也可能是临时表或者派生表之类的。除了真实表可能出现的3种表名如下:
<unionM,N>
:通过union查询产生的结果,M、N分别代表着执行计划id的值<derivedN>
:N的值为派生表的id值。派生表可能是FROM语句中的子查询<subqueryN>
:N为物化子查询的id值EXPLAIN SELECT * FROM `user` WHERE id =(SELECT id FROM user1 WHERE id = 1);
由于 select_type 为 SUBQUERY 的子查询会被物化,所以只需要执行一遍。
EXPLAIN SELECT * FROM (SELECT id , user_name FROM user GROUP BY user_name) derived_user WHERE derived_user.id > 1;
id=2的 select_type 是 DERIVED ,说明该子查询是以物化的方式执行的。id =1的记录代表外层查询,它的 table 列显示的是<derived2>
,表示该查询是将派生表物化之后的表进行查询的。
EXPLAIN SELECT * FROM user1 WHERE user_name IN (SELECT user_name FROM `user`);
执行计划的第三条记录的 id 值为 2,从它的 select_type 值为 NATERIALIED 可 以看出,查询优化器是要把子查询先转换成物化表。第二条记录的 table 列的值是<subquery2>
,说明该表 其实就是 id 为 2 对应的子查询执行之后产生的物化表,然后将 s1 和该物化表进行连接查询
和分区表有关,一般情况下我们的查询语句的执行计划的 partitions 列的值 都是 NULL。
type表示表连接类型或者数据访问类型,就是表之间通过什么方式建立连接的,或者通过什么方式访问到数据的。它是较为重要的一个指标,结果值从最好到最坏依次是:
「system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL」
出现比较多的是 「system>const>eq_ref>ref>range>index>ALL」 一般来说,得保证查询至少达到 range 级别,最好能达到 ref。
EXPLAIN SELECT * FROM `user` WHERE id = 1
EXPLAIN SELECT * FROM `user` INNER JOIN user1 ON `user`.id = user1.id;
从执行计划的结果中可以看出,MySQL 打算将 user1 作为驱动表,user作为被驱 动表。user1 的访问方法是 eq_ref,表明在访问 user1 表的时候可以通过主键的等值匹配来进行访问。
EXPLAIN SELECT * FROM user1 WHERE id IN (SELECT id FROM `user` WHERE user1.user_name = `user`.user_name) OR user1.id = 1;
EXPLAIN SELECT * FROM `user` WHERE user_name IN (SELECT user_name FROM user1) OR user_name='name';
EXPLAIN SELECT * FROM user1 WHERE user_name > '索码理'
EXPLAIN SELECT id , user_name FROM user1 WHERE user_name > '索码理'
possible_keys 是可能使用到的索引列表;key是实际使用到的索引列表。当possible_keys出现多个索引时,查询优化器会计算使用哪一个或几个或者不使用索引的成本更低,最后选择的索引就会在key里面出现。需要注意的一点是,possible keys列中的值并不是越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引。
key_len列表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度。计算方式是这样的:
对于使用固定长度类型的索引列来说,它实际占用的存储空间的最大长度就是该固定值,对于指定字符集的变长类型的索引列来说,比如某个索引列的类型 是 VARCHAR(100),使用的字符集是utf8,那么该列实际占用的最大存储空间就 是 100 x 3 = 300 个字节。如果该索引列可以存储 NULL 值,则 key_len 比不可以存储 NULL 值时多 1 个 字节。对于变长字段来说,都会有 2 个字节的空间来存储该变长列的实际长度。比如下面这个语句:
EXPLAIN SELECT * FROM user1 WHERE user_name = '索码理';
根据执行计划可以看出使用了索引idx_user_name,user_name字段是varchar(40) NOT NULL,使用的字符集是utf8mb4,utf8mb4字符集单字符最多占用4个字节空间。通过这些信息可以得到40*4+2=162
,跟执行计划中的结果一样,如果user_name字段允许为NULL,那么结果就变成了163。
MySQL 在执行计划中输出 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列(联合索引有最左前缀的特性,如果联合索引能全部使用上,则是联合索引字段的索引长度之和,这也可以用来判定联合索引是否部分使用,还是全部使用),而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用 1 个字节还是 2 个字节。
key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
当使用索引列等值匹配的条件去执行查询时,也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_sutbquery、index_subopery 其中之一时,ref 列展示的就是与索引列作等值匹配的字段或者函数等。比如在上面的key_len的例子中,ref的值是const,表示与user_name作等值匹配的对象是一个常数。有时候也会展示等值匹配具体的字段,比如:
EXPLAIN SELECT * FROM `user` INNER JOIN user1 ON `user`.user_name = user1.user_name WHERE `user`.user_name > 'username4';
可以看到第二列ref的值是db_test.user.user_name
(数据库.表名.具体字段),表user1使用了索引idx_user_name,这表示与表user1索引idx_user_name做等值匹配的是表user中的user_name字段。
有时候与索引列进行等值匹配的对象是一个函数,ref列会输出值func
如果查询优化器决定使用全表扫描的方式对某个表执行查询时,rows 列就代表预计需要扫描的行数,如果使用索引来执行查时,rows 列就代表预计扫描的索引记录行数。rows的值是预估值,对于InnoDB 引擎的表来说,这个值可能不会一直都是准确的。
EXPLAIN SELECT * FROM `user` WHERE id > 4
user表中加了13条数据,根据上面那条SQL语句查询条件能在数据库匹配到的结果有9条,rows就是9
filtered表示满足查询条件的行数占预估满足条件的行数rows的百分比。filtered的值从100开始变小的同时过滤掉的行数也在增加。
EXPLAIN SELECT * FROM `user` INNER JOIN user1 ON `user`.user_name = user1.user_name WHERE `user`.user_name > 'username4';
第一行结果rows=13,filtered=33.33%,就是说从预估符合条件的行数rows中二次筛选出33.33%的行数符合条件,也就是过滤掉了13*0.3333≈5
,满足条件的这5行再和user1表进行连接。filtered只是一个预估值,参考值不大。
顾名思义,Extra 列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。如果要使查询尽可能快,请注意使用 Using filesort 和Using temporary的Extra列值。EXPLAIN有33个左右Extra信息,感兴趣的可以到MySQL官网看下。这里就简单介绍几个经常能遇到的。
MySQL执行计划在面试中会经常问到,尤其是type字段会经常被问到。同时,了解执行计划在工作中也能更好的对SQL查询进行优化,所以执行计划是mysql学习过程中的一个必备技能,来充实一下自己的技能包。