loong - 数据库 - MySQL - EXPLAIN
MySQL EXPLAIN命令是查询性能优化不可缺少的一部分,常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描。
环境准备
#创建user_info 表
CREATE TABLE `user_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL DEFAULT '',
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_index` (`name`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
#插入数据
INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);
#创建order_info表
CREATE TABLE `order_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` BIGINT(20) DEFAULT NULL,
`product_name` VARCHAR(50) NOT NULL DEFAULT '',
`productor` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
#插入数据
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
EXPLAIN 输出字段
mysql> explain select * from user_info;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
字段含义
列名 | 描述 |
---|---|
id | 执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置 |
select_type | 显示本行是简单或复杂select。如果查询有任何复杂的子查询,则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUlT) |
table | 访问引用哪个表(引用某个查询,如“derived3”) |
type | 数据访问/读取操作类型(ALL、index、range、ref、eq_ref、const/system、NULL) |
possible_keys | 此次查询中可能选用的索引 |
key | 此次查询中确切使用到的索引. |
key_len | 显示mysql在索引里使用的字节数 |
ref | 哪个字段或常数与 key 一起被使用 |
rows | 显示此查询一共扫描了多少行. 这个是一个估计值. |
Extra | 额外信息,如using index、filesort等 |
partitions | 分区 |
filtered | rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的 id 值比当前表 id 值小的表) |
id
id是用来顺序标识整个查询中SELELCT 语句的,在嵌套查询中id越大的语句越先执行。该值可能为NULL,如果这一行用来说明的是其他行的联合结果。
select_type
select_type 表示了查询的类型, 它的常用取值有:
- SIMPLE:表示此查询不包含 UNION 查询或子查询
- PRIMARY:表示此查询是最外层的查询
- UNION: 表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION: UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT: UNION 的结果
- SUBQUERY:子查询中的第一个 SELECT
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
最常见的查询类别应该是 SIMPLE 了, 比如当我们的查询没有子查询, 也没有 UNION 查询时, 那么通常就是 SIMPLE 类型, 例如:
mysql> explain select * from user_info where id = '2';
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
如果我们使用了 UNION 查询, 那么 EXPLAIN 输出 的结果类似如下:
mysql> explain select * from user_info where id = 1 union select * from user_info where id = 2;
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 2 | UNION | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+------+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)
table
表示查询涉及的表或衍生表
- 关联优化器会为查询选择关联顺序,左侧深度优先
- 当from中有子查询的时候,表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列
- 当有union result的时候,表名是union 1,2等的形式,1,2表示参与union的query id
注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的
type
type显示的是访问类型,它提供了判断查询是否高效的重要依据依据,通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.
-
**system:**表中只有一条数据. 这个类型是特殊的 const 类型.
-
const: 对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.
mysql> explain select * from user_info where id = '2'; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
-
eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
-
ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀规则索引的查询.下面这个例子中, 就使用到了 ref 类型的查询
-
**range:**表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中;当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。
mysql> explain select * from user_info where id between 1 and 8; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 8 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
在where后面使用范围,如果该列不是主键(普通索引、唯一索引)则索引不起作用,如果是组合索引那么索引会被利用单独在user_id上加上普通索引
给order_info增加组合索引u_pname_p(user_id,p_name,p)则索引能利用上
-
**index:**表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描。
而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到(这种一般指的是innodb引擎), 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
mysql> explain select name from user_info; +----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | index | NULL | name_index | 152 | NULL | 10 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
上面的例子中, 我们查询的 name 字段恰好是一个索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据. 因此这样的情况下, type 的值是 index, 并且 Extra 的值是 Using index.
-
**ALL:**表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.
mysql> explain select age from user_info where age = 20; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
- ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
- index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
- 后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys
possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.
key
key列显当前查询时所真正使用到的索引.。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len
表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
key_len 的计算规则如下:
-
字符串
- char(n): n 字节长度
- varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
-
数值类型
- TINYINT: 1字节
- SMALLINT: 2字节
- MEDIUMINT: 3字节
- INT: 4字节
- BIGINT: 8字节
-
时间类型
- DATE: 3字节
- TIMESTAMP: 4字节
- DATETIME: 8字节
-
字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.
key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好 。
例如:
上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引:
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
不过此查询语句 WHERE user_id < 3 AND product_name = ‘p1’ AND productor = ‘WHH’ 中, 因为先进行 user_id 的范围查询, 而根据 最左前缀匹配 原则, 当遇到范围查询时, 就停止索引的匹配。
因此实际上我们使用到的索引的字段只有 user_id, 因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT ‘0’, 则 key_length 应该是8。
上面因为 最左前缀匹配 原则, 我们的查询仅仅使用到了联合索引的 user_id 字段, 因此效率不算高.
再例如:
这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = ‘p1’ 中, 仅仅使用到了联合索引中的前两个字段, 因此:
keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161
ref
哪个字段或常数与 key 一起被使用
rows
rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数. 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.。注意这是一个预估值。
Extra
EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:
-
Using filesort
当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
我们的索引是
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort. 如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了.
如果对于利用组合索引进行排序,那么多个排序的顺序应该是一致的,否则还是会using filesort
正确示例:
-
Using index
“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
-
Using temporary
查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
-
Distinct
MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
-
Not exists
MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
-
Using where
WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
-
Using sort_union(…), Using union(…), Using intersect(…)
这些函数说明如何为index_merge联接类型合并索引扫描
-
range checked for each record (index map: #)
MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
-
Using where
WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
-
Using sort_union(…), Using union(…), Using intersect(…)
这些函数说明如何为index_merge联接类型合并索引扫描
-
range checked for each record (index map: #)
MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。