赞
踩
优化 SQL 的第一步应该是读懂 SQL 的执行计划。本篇文章,我们一起来学习下 MySQL EXPLAIN
执行计划相关知识。
执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后,具体的执行方式。
执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN
的结果,可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
MySQL 为我们提供了 EXPLAIN
命令,来获取执行计划的相关信息。
需要注意的是,EXPLAIN
语句并不会真的去执行相关的语句,而是通过查询优化器对语句进行分析,找出最优的查询方案,并显示对应的信息。
EXPLAIN
执行计划支持 SELECT
、DELETE
、INSERT
、REPLACE
以及 UPDATE
语句。我们一般多用于分析 SELECT
查询语句,使用起来非常简单,语法如下:
EXPLAIN + SELECT 查询语句;
我们简单来看下一条查询语句的执行计划:
- mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
- +----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
- | 1 | PRIMARY | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | Using where |
- | 2 | SUBQUERY | dept_emp | NULL | index | PRIMARY,dept_no | PRIMARY | 16 | NULL | 331143 | 100.00 | Using index |
- +----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
可以看到,执行计划结果中共有 12 列,各列代表的含义总结如下表:
列名 | 含义 |
---|---|
id | SELECT查询的序列标识符 |
select_type | SELECT关键字对应的查询类型 |
table | 用到的表名 |
partitions | 匹配的分区,对于未分区的表,值为 NULL |
type | 表的访问方法 |
possible_keys | 可能用到的索引 |
key | 实际用到的索引 |
key_len | 所选索引的长度 |
ref | 当使用索引等值查询时,与索引作比较的列或常量 |
rows | 预计要读取的行数 |
filtered | 按表条件过滤后,留存的记录数的百分比 |
Extra | 附加信息 |
为了分析 EXPLAIN
语句的执行结果,我们需要搞懂执行计划中的重要字段。
SELECT 标识符,是查询中 SELECT 的序号,用来标识整个查询中 SELELCT 语句的顺序。
id 如果相同,从上往下依次执行。id 不同,id 值越大,执行优先级越高,如果行引用其他行的并集结果,则该值可以为 NULL。
查询的类型,主要用于区分普通查询、联合查询、子查询等复杂的查询,常见的值有:
查询用到的表名,每行都有对应的表名,表名除了正常的表之外,也可能是以下列出的值:
<unionM,N>
: 本行引用了 id 为 M 和 N 的行的 UNION 结果;<derivedN>
: 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。 -<subqueryN>
: 本行引用了 id 为 N 的表所产生的的物化子查询结果。查询执行的类型,描述了查询是如何执行的。所有值的顺序从最优到最差排序为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常见的几种类型具体含义如下:
possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ,则表示没有可能用到的索引;这种情况下,需要检查 WHERE 语句中所使用的的列,看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。
key 列表示 MySQL 实际使用到的索引。如果为 NULL,则表示未用到索引。
key_len 列表示 MySQL 实际使用的索引的最大长度;当使用到联合索引时,有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ,则 key_len 列也显示 NULL 。
rows 列表示根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好。
这列包含了 MySQL 解析查询的额外信息,通过这些信息,可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下:
这里提醒下,当 Extra 列包含 Using filesort 或 Using temporary 时,MySQL 的性能可能会存在问题,需要尽可能避免。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。