EXPLAIN命令
EXPLAIN命令是查看优化器如何决定执行查询的主要方法,但最终不一定就是按照这个来。
EXPLAIN可以作用于 SELECT、DELETE、INSERT、UPDATE和REPLACE语句。
可以使用FORMAT=JSON来输出详细的执行计划成本
EXPLAIN FORMAT=JSON SELECT id FROM zc_order;
大致是以下的格式
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "399.75"
},
"table": {
"table_name": "zc_order",
"access_type": "index",
"key": "IDX_ORDER_NO",
"used_key_parts": [
"ORDER_NO"
],
"key_length": "98",
"rows_examined_per_scan": 3755,
"rows_produced_per_join": 3755,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "24.25",
"eval_cost": "375.50",
"prefix_cost": "399.75",
"data_read_per_join": "14M"
},
"used_columns": [
"ID"
]
}
}
}
执行计划包含的信息
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
下面具体看看每一项的含义
id
包含一组数字,表示查询中执行select子句或操作表的顺序
id相同,表示同一组,执行顺序由上至下
id不同则值越大优先级越高,越先执行
如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
临时表的id是NULL
select_type
查询中每个select子句的类型(简单OR复杂)
select_type对于非SELECT语句,显示的是其类型,例如UPDATE显示UPDATE
-
SIMPLE:简单的SELECT,查询中不包含子查询或者UNION
mysql> EXPLAIN SELECT * FROM zc_order; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | zc_order | NULL | ALL | NULL | NULL | NULL | NULL | 3781 | 100 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
-
PRIMARY:最外层的SELECT,查询中若包含任何复杂的子部分,最外层查询则被标记
-
SUBQUERY:在SELECT或WHERE列表中包含了子查询,该子查询被标记
mysql> EXPLAIN SELECT * from zc_order_goods where order_no = (select MAX(order_no) from zc_order); +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | PRIMARY | zc_order_goods | NULL | ALL | NULL | NULL | NULL | NULL | 4696 | 10 | Using where | | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
-
UNION:标记出现在UNION后的SELECT
-
UNION RESULT:UNION的结果
mysql> EXPLAIN SELECT * from zc_order o where o.order_no = 'UNO200418000000008' UNION SELECT * from zc_order o2 where o2.order_no = 'UNO200418000000009'; +------+--------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+ | 1 | PRIMARY | o | NULL | const | IDX_ORDER_NO | IDX_ORDER_NO | 130 | const | 1 | 100 | NULL | | 2 | UNION | o2 | NULL | const | IDX_ORDER_NO | IDX_ORDER_NO | 130 | const | 1 | 100 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+
-
DERIVED:用来表示包含在FROM子句中的子查询,mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表",因为该临时表是从子查询中派生出来的。(派生表的性能不好)
-- MySQL 5.7开始优化器引入derived_merge, -- 当子查询中存在 UNION、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作时会禁止该优化 mysql> EXPLAIN SELECT * FROM ( SELECT * FROM zc_order LIMIT 1) temp; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100 | NULL | | 2 | DERIVED | zc_order | NULL | ALL | NULL | NULL | NULL | NULL | 3781 | 100 | NULL | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
-
DEPENDENT DERIVED:表示派生表依赖于另一个表
-
SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE,DEPENDENT意味着SELECT在子查询中并且依赖于外层查询。UNCACHEABLE意味着无法缓存其结果的子查询,必须为外部查询的每一行重新计算其结果。
mysql> EXPLAIN SELECT * from zc_order where order_no in (SELECT order_no from zc_order o where o.order_no = 'UNO200418000000008' UNION SELECT order_no from zc_order o2 where o2.order_no = 'UNO200418000000009'); +------+--------------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+ | 1 | PRIMARY | zc_order | NULL | ALL | NULL | NULL | NULL | NULL | 3781 | 100 | Using where | | 2 | DEPENDENT SUBQUERY | o | NULL | const | IDX_ORDER_NO | IDX_ORDER_NO | 130 | const | 1 | 100 | Using index | | 3 | DEPENDENT UNION | o2 | NULL | const | IDX_ORDER_NO | IDX_ORDER_NO | 130 | const | 1 | 100 | Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +------+--------------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+
-
MATERIALIZED 物化子查询,用于实现对子查询的优化,对应FORMAT=JSON输出的
materialized_from_subquery
详见:MySQL官方文档-subquery-materialization
table
表示该行所访问的表
<derivedN>
表示当前查询依赖于id=N的查询
<unionM,N>
表示id=M、N的查询参与了UNION
<subqueryN>
id=N的物化子查询结果
partitions
如果查询是基于分区表的话,会显示查询将访问的分区
type
表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:
现有索引
ALTER TABLE zc_order ADD PRIMARY KEY (ID);
ALTER TABLE zc_order ADD UNIQUE INDEX IDX_ORDER_NO (ORDER_NO);
ALTER TABLE zc_order ADD INDEX IDX_USER_NO_ORDER_STATUS (USER_NO, ORDER_STATUS);
all: Full Table Scan, MySQL将遍历全表以找到匹配的行
mysql> EXPLAIN SELECT * FROM zc_order;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | zc_order | NULL | ALL | NULL | NULL | NULL | NULL | 3781 | 100 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
index: Full Index Scan,index与ALL区别为index类型只遍历索引树
mysql> EXPLAIN SELECT id, order_no FROM zc_order;
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | zc_order | NULL | index | NULL | IDX_ORDER_NO | 130 | NULL | 3781 | 100 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
range: 索引范围扫描,显而易见的索引范围扫描是带有between、!=、<>、in、not in、or
的查询(or两边必须都为索引)
mysql> EXPLAIN SELECT * FROM zc_order WHERE order_no = 'UNO200418000000008' or order_no = 'UNO200418000000009';
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | zc_order | NULL | range | IDX_ORDER_NO | IDX_ORDER_NO | 130 | NULL | 2 | 100 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
ref: 使用非唯一索引扫描或者唯一索引的前缀扫描(组合索引),返回匹配某个单独值的记录行
-- 非唯一索引,走ref
-- 唯一索引,如果是单列索引走的是const, 如果是组合索引的前缀匹配走ref
mysql> EXPLAIN SELECT * FROM zc_order WHERE USER_NO = 'US0000000001';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | zc_order | NULL | ref | IDX_USER_NO_ORDER_STATUS | IDX_USER_NO_ORDER_STATUS | 258 | const | 4 | 100 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
eq_ref: 类似ref,区别就在使用的索引是主键索引或者唯一索引,对于每个索引键值,表中只有一条记录匹配
-- 单表没测试出来,通常是const
-- 连表查询
mysql> EXPLAIN SELECT * FROM zc_order o, zc_order_goods g WHERE o.ORDER_NO = g.ORDER_NO;
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
| 1 | SIMPLE | g | NULL | ALL | NULL | NULL | NULL | NULL | 4696 | 100 | NULL |
| 1 | SIMPLE | o | NULL | eq_ref | IDX_ORDER_NO | IDX_ORDER_NO | 130 | test-db.g.ORDER_NO | 1 | 100 | NULL |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
const: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。例如使用主键或唯一索引进行查询
mysql> EXPLAIN SELECT * FROM zc_order WHERE order_no = 'UNO200418000000008';
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | zc_order | NULL | const | IDX_ORDER_NO | IDX_ORDER_NO | 130 | const | 1 | 100 | NULL |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
system: system是const类型的特例,当查询的表只有一行的情况下,使用system
-- 测不出来,即使表中只存在一条记录,还是const
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成
mysql> EXPLAIN SELECT MIN(order_no) FROM zc_order;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
从上到下,性能越来越好
possible_keys
指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
有可能出现possible_keys
不为null,但是key
为null的情况,一般是查询条件上有符合的索引,但是mysql优化器认为走全表扫描效率更高
mysql> EXPLAIN SELECT * FROM zc_order where user_no > 'US0000000013';
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | zc_order | NULL | ALL | IDX_USER_NO_ORDER_STATUS | NULL | NULL | NULL | 3781 | 90.27 | Using where |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
另一总情况刚好相反,possible_keys为null,但是key不为null,通常是使用了覆盖索引导致的
-- 这里比较有趣的一点是,覆盖的索引为id,任意索引中都存在主键 mysql根据某种规则选择了其中一个而不是直接拿主键索引
-- 如果查询的是user_no,那么必定是使用IDX_USER_NO_ORDER_STATUS索引
mysql> EXPLAIN SELECT id FROM zc_order;
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | zc_order | NULL | index | NULL | IDX_ORDER_NO | 130 | NULL | 3781 | 100 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
key
实际用到的索引
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
- 所有的索引字段,如果没有设置not null,则需要加一个字节用于记录是否为NULL
- 定长字段,int占四个字节、date占三个字节、char(n)占n个字符,tinyint占一个字节
- 对于变长字段varchar(n),则有n个字符+两个字节。
- 不同的字符集,一个字符占用的字节数不同。latin1编码的一个字符占用一个字节,gbk编码的一个字符占用两个字节,utf8编码的一个字符占用三个字节,utf8mb4编码的一个字符占用四个字节
-- `ORDER_NO` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT ''
-- 32 * 3 + 2 = 98
mysql> EXPLAIN SELECT * FROM zc_order where order_no = 'UNO200515000000060';
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | zc_order | NULL | const | IDX_ORDER_NO | IDX_ORDER_NO | 98 | const | 1 | 100 | NULL |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
-- `USER_NO` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT ''
-- `ORDER_STATUS` char(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
-- 64 * 3 + 2 + 2 * 3 = 200
mysql> EXPLAIN SELECT * FROM zc_order where user_no = 'US0000000013' and order_status = '01';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | zc_order | NULL | ref | IDX_USER_NO_ORDER_STATUS | IDX_USER_NO_ORDER_STATUS | 200 | const,const | 1 | 100 | NULL |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
ref
表示索引的哪些列被使用了,可能是一个常量
常见的有:const、字段名、NULL
-- const
mysql> EXPLAIN SELECT * FROM zc_order WHERE id = 20;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
| 1 | SIMPLE | zc_order | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100 | Directly search via Primary Index |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
-- 字段名
mysql> EXPLAIN SELECT * FROM zc_order o, zc_order_goods g WHERE o.ORDER_NO = g.ORDER_NO;
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
| 1 | SIMPLE | g | NULL | ALL | NULL | NULL | NULL | NULL | 4696 | 100 | NULL |
| 1 | SIMPLE | o | NULL | eq_ref | IDX_ORDER_NO | IDX_ORDER_NO | 130 | test-db.g.ORDER_NO | 1 | 100 | NULL |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
rows
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
filtered
这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的百分比(估计)
这个字段几乎没什么用,通常都是显示100%,只对索引类型为index
和all
时才生效(where条件中出现的列上建有索引,但是执行计划不走索引的range或ref扫描,而走全表扫描或覆盖索引扫描)
mysql> EXPLAIN SELECT * FROM zc_order where user_no > 'US0000000013';
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | zc_order | NULL | ALL | IDX_USER_NO_ORDER_STATUS | NULL | NULL | NULL | 3755 | 49.99 | Using where |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
1 row in set
Extra
包含不适合在其他列中显示但十分重要的额外信息
-
Using where
表示mysql服务器将在存储引擎检索后再进行过滤(service层过滤)。许多where条件里涉及索引中的列,当它读取索引时,能够直接在索引中进行过滤,因此不是所有带where的查询都会显示"Using where"
-
Using index
该值表示相应的select操作中使用了覆盖索引(Covering Index)
-
Using index confition
表示使用到了索引下推
-
Using temporary
表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询
-
Using filesort
MySQL必须做一个额外的步骤来找出如何按排序的顺序检索行
-
Using join buffer
该值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能
-
Impossible where
这个值强调了where语句会导致没有符合条件的行
-
Select tables optimized away
这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行。
EXPLAIN SELECT MIN(id) FROM zc_order;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
-
Index merges
当MySQL 决定要在一个给定的表上使用超过一个索引的时候,就会出现以下格式中的一个,详细说明使用的索引以及合并的类型
Using sort_union(…)
Using union(…)
Using intersect(…)