MySQL执行计划是通过关键字explain来实现的,可以查看相应SQL(MySQL 5.5以前只支持select,5.6开始支持insert/update/delete等)执行过程中的一些信息,比如:索引,扫描的数据行数等等。根据执行计划信息,我们可以对SQL进行优化,通过修改或者增加索引的方式,可以大幅度提高SQL执行的效率。有的时候,通过索引优化过的SQL语句相比于优化前的SQL语句,在效率上可能有成千上万倍的差距。
查看MySQL某条SQL语句的执行计划的方式很简单,只要在原来的SQL语句前面加上explain关键字即可,假设我们有如下表:
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB
表中有40w条记录根据id自增,我们需要查看id=1000的SQL语句的执行计划,可以通过以下方式进行:
explain select * from t where id=1000;
通过上述语句,可以得到如下的执行计划:
+----+------------------+---------+------+------------------+------------+-----------+-------+------+----------+
| id | select_type | table | type | possible_keys| key | key_len| ref | rows | Extra |
+----+----------------+---------+-------+-----------------+-------------+---------+--------+------+-----------+
| 1 | SIMPLE | sbtest1 |const| PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+---------+-------+------------------+-------------+---------+---------+--------+--------+
1 row in set (0.00 sec)
通过上述执行计划,可以获取该SQL语句通过主键索引进行查询并返回结果。
通过explain关键字+ SQL语句的方式可以获取到相应的执行计划信息,那执行计划中的各个字段的值又代表了什么意思?接下来就来介绍下执行计划中不同的值各自代表的含义。
select_type表示查询中每个select子句的类型,select_type包含8种不同的类型,各种类型和代表的含义见下表:
select_type |
含义 |
例子 |
SIMPLE |
查询中不包含子查询和union等形式 |
select * from t where id =1; |
PRIMARY |
查询包含子查询的最外层查询 |
select * from t where id in (select * from t1);外层查询 |
SUBQUERY |
where列表中包含了子查询 |
select * from t where id in (select * from t1);内层查询 |
DEPENDENT SUBQUERY |
子查询中的一个查询取决于外面查询 |
select * from t where id in(select id from t where id=1) |
DERIVED |
from中包含了子查询 |
select * from (select * from t)a; |
UNION |
在union后出现子查询 |
select * from t where id =1 union select * from t |
DEPENDENT UNION |
union后面的子查询取决于外面的查询 |
select * from t where id in (select id from t1 union select id from t2) |
UNION RESULT |
从union中获取结果的子查询 |
select * from t where id =1 union select * from t |
type表示MySQL查找所需行的方式,可以分为ALL,INDEX,RANGE,REF,EQ_REF,CONST或SYSTEM,NULL等7种不同的方式,如下表:
type |
含义 |
例子 |
ALL |
全表扫描 |
select * from t; |
index |
只扫描索引 |
select id from t; |
range |
索引范围扫描 |
select * from t where id >10; |
ref |
非唯一性索引扫描 |
select * from t where k=1; |
eq_ref |
唯一性索引扫描 |
select * from t,t1 where t.id=t1.id |
const,system |
查询优化成常量 |
select * from t where id=1; |
NULL |
优化产生 |
select * from t where id = (select max(id) from t); |
possible_keys表示SQL语句在执行过程中可能用到的索引,possible_keys可能会有多个,但不一定会被使用到。
相对于possible_keys,key字段显示的是SQL语句在执行过程中使用到的索引,如果没有使用任何索引,在该列显示为NULL。
查询过程中使用的索引的长度(单位为字节),该长度是静态的,根据表的定义获取,而非真正执行过程中查询获得,对于联合主键的长度一般为两个键长度之和。
说明连接匹配的条件,如果匹配条件中有常量如where id=1,则ref为const,如果还有其他的连接方式,比如where t.id = t1.id则,相关表的id也会被放入ref。
rows表示MySQL根据执行计划信息估算出来的需要读取记录的行数,这个值不是精确的,只是一个估算值。
包含部分额外的信息,说明该SQL查询使用了哪些条件,如Using where, Using index等等。
MySQL执行计划能够很好的反应SQL语句在执行过程中的大致情况,给数据库管理员在SQL优化上一个很好的参考,但还有一些局限,如下:
1. 执行计划不能用在trigger,procedure和udf上
2. 执行计划不考虑执行过程中的cache情况,如果在命中cache的情况下,执行计划显示的就不准确了。
3. MySQL 5.6以前版本,执行计划不能用于select外的操作,如:delete/insert/update等。
本文来自网易实践者社区,经作者蒋鸿翔授权发布。