执行计划是SQL语句调优的一个重要依据,MySQL的执行计划查看相对Oracle而言简便了很多,功能也相对简单。MySQL的EXPLAIN命令用于查看SQL语句的查询执行计划(QEP)。从这条命令的输出结果中就能够了解MySQL优化器是如何执行SQL语句的。这条命令虽然并没有提供任何调整建议,但它能够提供重要的信息用来帮助做出调优决策。
MySQL的EXPLAIN语法可以运行在SELECT语句或者特定表上。如果作用在表上,那么此命令等同于DESC表命令。MySQL 5.6.3之前只能对SELECT生成执行计划,5.6.3及之后的版本对SELECT、DELETE、INSERT、REPLACE和UPDATE都可以生成执行计划。MySQL优化器是基于开销来工作的,它并不提供任何的QEP的位置。这意味着QEP是在每条SQL语句执行的时候动态地计算出来的。在MySQL存储过程中的SQL语句也是在每次执行时计算QEP的。存储过程缓存仅仅解析查询树。
MySQL生成执行计划的语法如下所示:
{EXPLAIN l DESCRIBE I DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type:{
EXTENDED
|PARTITIONS
|FORMAT=format_name
}
format_name:{
TRADITIONAL
|JSON
}
explainable_stmt:{
SELECT statement
|DELETE statement
|INSERT statement
|REPLACE statement
|UPDATE statement
}
所以,EXPLAIN和DESC都可以生成执行计划。
下面给出一个查看MySQL语句执行计划的示例:
mysql>CREATE TABLE t_4(
->id int,
->name varchar(10),
->age int,
->INDEX MutiIdx(id,name,age)
->);
Query OK, 0 rows affected(0.04 sec)
mysql>show CREATE TABLE t_4/G;
***************************1.row***************************
Table: t_4
Create Table: CREATETABLE 't_4' (
'id' int(11) DEFAULT NULL,
'name' varchar(10) DEFAULT NULL,
'age' int(11) DEFAULT NULL,
KEY 'Mutildx'('id','name','age')
)ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set(0.00 sec)
ERROR:
No query specified
mysql>INSERT INTO t_4 values(1,'AAA',10),(2,'bbb',20),(3,'ccc'30),(4,'ddd'40),(5,'eee',50);
Query OK, 5 rows affected(0.02 sec)
Records: 5 Duplicates:0 Warning:0
mysql>SELECT*FROM t_4;
5 rows in set(0.05 sec)
mysql>EXPLAIN SELECT NAME,AGE FROM t_4 WHERE ID<3;
1 row in set(0.18 sec)
mysql>EXPLAIN SELECT NAME,AGE FROM t_4 WHERE ID<3\G
**************************1.row**************************
id:1
select_type:SIMPLE
table:t_4
type:index
possible_keys:Mutildx
key:Mutildx
key_len:23
ref:NULL
rows:5
Extra:Using where;Usmg index
1 row in set(0.17 sec)
mysql>explain select state,
-> sum(duration)as total_r,
-> round(100*sum(duration)/
-> (select sum(duration)
-> from information_schema.profiling
-> where query_id=1),
-> 2)as pct_r,
-> count(*)as calls,
-> sum(duration)/count(*)as "r/call"
-> from information_schema.profiling
-> where query_id=1
-> group by state
-> order by total_r desc;
2 rows in set(0.22 sec)
下面介绍每种指标的含义:
1.id
id包含一组数字,表示查询中执行SELECT子句或操作表的顺序;执行顺序从大到小执行;当id值一样的时候,执行顺序由上往下。
2.select_type
select_type表示查询中每个SELECT子句的类型,最常见的值包括SIMPLE、PRIMARY、DERIVED和UNION。其他可能的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION以及UNCACHEABLE QUERY,这些类型的含义如下:
1)SIMPLE:查询中不包含子查询、表连接或者UNION等其他复杂语法的简单查询,这是一个常见的类型。
2)PRIMARY:查询中若包含任何复杂的子查询,则最外层查询被标记为PRIMARY。这个类型通常可以在DERIVED和UNION类型混合使用时见到。
3)SUBQUERY:在SELECT或WHERE列表中包含了子查询,该子查询被标记为SUBQUERY。
4)DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),或者说当一个表不是一个物理表时,那么就被称为DERIVED,例如:
5)UNION:若第二个SELECT出现在UNION之后,则被标记为UNION,即UNION中的第二个或后面的查询语句会被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED。
6)UNION RESULT:从UNION表获取结果的SELECT被标记为UNION RESULT。这是一系列定义在UNION语句中的表的返回结果。当select_type为这个值时,经常可以看到table的值是<unionN,M>,这说明匹配的id行是这个集合的一部分。下面的SQL产生了一个UNION和UNIONRESULT的select-type。
7)DEPENDENT SUBQUERY:这个select-type值是为使用子查询而定义的。下面的SQL语句提供了这个值。
3.type
type表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型有以下几种,从上到下,性能由最差到最好。
1)ALL:全表扫描(Full Table Scan),MySQL将进行全表扫描。
2)index:索引全扫描(Index Full Scan),MySQL将遍历整个索引来查询匹配的行,index与ALL区别为index类型只遍历索引树。
3)range:索引范围扫描(Index Range Scan),对索引的扫描开始于某一点,返回匹配值域的行,常见于BETWEEN、<、>、>=、<=的查询。需要注意的是,若WHERE条件中使用了IN,则该列也是显示range。
4)ref:返回匹配某个单独值的所有行,常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找。此外,ref还经常出现在join操作中。
5)eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描,即在多表连接中,使用主键或唯一索引作为连接条件。
6)const:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。例如,将主键列或唯一索引列置于WHERE列表中,此时,MySQL就能将该查询转换为一个常量const。单表中最多只有1个匹配行,所以查询非常迅速,则这个匹配行中的其他列的值就可以被优化器在当前查询中当作常量来处理。
7)system:表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,那么type列通常都是ALL或者index。
8)NULL:MySQL在优化过程中分解语句,执行时不用访问表或索引就能直接得到结果。
类型type还有其他值,如ref_or_null(与ref类似,区别在于条件中包含对NULL的查询)、index_merge(索引合并优化)、unique_subquery(in的后面是一个查询主键字段的子查询)、index_subquery(与unique_subquery类似,区别在于in的后面是一个查询非唯一索引字段的子查询)、fulltext(全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,MySQL不会考虑代价,优先选择使用全文索引)等。
4.possible_keys
possible_keys表示查询时可能使用到的索引,指出MySQL能使用哪个索引在表中找到行,若查询涉及的字段上存在索引,则该索引将被列出,但不一定被查询使用。一个会列出大量可能的索引(例如多于3个)的QEP意味着备选索引数量太多了,同时也可能提示存在一个无效的单列索引。
5.key
key显示MySQL在查询中实际使用的索引,若没有使用索引,则显示为NULL。若查询中使用了覆盖索引,则该索引仅出现在key列表中。一般来说SQL查询中的每个表都仅使用一个索引。“SHOW CREATE TABLE <table>”命令是最简单的查看表和索引列细节的方式。和key列相关的列还包括possible_keys、rows以及key_len
6.key_len
key_len表示使用到索引字段的长度,可通过该列计算查询中使用的索引的长度。此列的值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
常见的计算规律为:
1)1个utf8字符集的字符占用3个字节;1个gbk字符集的字符占用2个字节。
2)对于变长的类型(VARCHAR),key_len还要加字节;若字段允许为空,则key_len需要加1。
3)INT类型的长度为4。
4)对于DATATIME类型的字段,在MySQL 5.6.4以前是8个字节(不能存储小数位),之后的长度为5个字节再加上小数位字节数。DATATIME最大小数位是6。若小数位长度为1或2,则总字节数为6(5+1);若小数位为3或4,则总字节数为7(5+2);若小数位为5或6,则总字节数为8(5+3)。
由此可见,是否可以为空、可变长度的列以及key_len列的值只与用在连接和WHERE条件中的索引的列有关。索引中的其他列会在ORDER BY或者GROUP BY语句中被用到。
下面给出一个示例:
CREATE TABLE 'wp_osts'(
'ID' bigint(20) unsigned NOT NULLAUTO_INCREMENT,
'post_date' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
'post_status' varchar(20) NOT NULL DEFAULT 'publish',
'post_type' varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY ('ID'),
KEY 'type_status_date'('post type','post_status','post_date','ID')
)DEFAULT CHARSET=utf8;
这个表的索引包括post_type、post_status、post_date以及ID列。下面是一个演示索引列用法的SQL查询:
mysql>CREATE TABLE 'wp_posts'(
-> 'ID' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> 'post_date' datetime NOT NULL DEFAULT '0000-00-00 00:00:00'.
-> 'post_status' varchar(20) NOT NULL DEFAULT 'publish',
-> 'post_type' varchar(20) NOT NULL DEFAULT 'post',
-> PRIMARY KEY ('ID'),
-> KEY 'type_status_date'('post_type','post_status','post_date','ID')
-> )DEFAULTCHARSET=utf8;
Query OK,0 rows affected(0.52 sec)
mysql>EXPLAIN SELECT ID, post_status FROM wp_posts WHERE post_type='post' AND post_date>'2010-06-01';
1 rowin set(0.03 sec)
这个查询的QEP返回的key_len是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62,post_type长度为20;1个utf8字符集的字符占用3个字节;对于变长的类型,key_len还要加2字节;若字段允许为空,则key_len需要加1)。尽管查询在WHERE语句中使用了post_type和post_date列,但只有post_type部分被用到了。其他索引没有被使用的原因是MySQL只能使用定义索引的最左边部分。为了更好地利用这个索引,可以修改这个查询来调整索引的列。请看下面的示例:
在SELECT查询的添加一个post_status列的限制条件后,QEP显示key_len的值为132,这意味着post_type、post_status、post_date三列((62+62+8)=[(20×3)+2]+[(20×3)+2]+8)都被用到了。此外,这个索引的主码列ID的定义是使用MyISAM存储索引的遗留痕迹。当使用InnoDB存储引擎时,在非主码索引中包含主码列是多余的,这可以从key_len的用法看出来。相关的QEP列还包括带有Using index值的Extra列。
7.ref
ref表示上述表的连接匹配条件,即那些列或常量被用于查找索引列上的值。
8.rows
rows表示MySQL根据表统计信息及索引选用情况,估算找到所需的记录所需要读取的行数。
9.Extra
Extra包含不适合在其他列中显示但十分重要的额外信息:
1)Using where:表示:MySQL服务器在存储引擎收到记录后进行“后过滤”(Post_filter),如果查询未能使用索引,那么Using where的作用只是说明MySQL将用where子句来过滤结果集。如果用到了索引,那么行的限制条件是通过获取必要的数据之后处理读缓冲区来实现的。
2)Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询。这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的列上使用了DISTINCT,或者使用了不同的ORDERBY和GROUPBY列。
3)Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”。这是ORDER BY语句的结果。这可能是一个CPU密集型的过程。可以通过选择合适的索引来改进性能,用索引来为查询结果排序。
4)Using index:这个值重点强调了只需要使用索引就可以满足查询表的要求,不需要直接访问表数据,说明:MySQL正在使用覆盖索引。
5)Using join buffer:这个值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进性能。
6)Impossible where:这个值强调了where语句会导致没有符合条件的行。
7)Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行。
8)Distinct:这个值意味着MySQL在找到第一个匹配的行之后就会停止搜索其他行。
9)Index merges:当MySQL决定要在一个给定的表上使用超过一个索引的时候,Indexmerges就会出现,用来详细说明使用的索引以及合并的类型。
10.table
table列是EXPLAIN命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表的标识符,如派生表、子查询或集合。下面是QEP中table列的一些示例:
1)table: item。
2)table: <derivedN>。
3)table: <unionN,M>。
表中N和M的值参考了另一个符合id列值的table行。相关的QEP列还有select_type。
11.partitions
partitions列代表给定表所使用的分区。这一列只会在EXPLAIN PARTITIONS语句中出现。
12.filtered
filtered列给出了一个百分比的值,这个百分比值和rows列的值一起使用,可以估计出那些将要和QEP中的前一个表进行连接的行的数目。前一个表就是指id列的值比当前表的id小的表。这一列只有在EXPLAIN EXTENDED语句中才会出现。使用EXPLAIN EXTENDED和SHOW WARNINGS语句,能够看到SQL在真正被执行之前优化器做了哪些SQL改写。
MySQL 5.6后可以加参数explain format=json xxx输出json格式的执行计划信息,如下所示:
mysql>EXPLAIN format='json' select* from employees where email='lhr@qq com'\G;
***************************1.row***************************
EXPLAIN:{
"query_block":{
"select_id":1,
"cost_info":{
"query_cost":"1.00"
},
"table":{
"table_name":"employees",
"access_type":"const",
"possible_keys":[
"uk_email"
],
"key":"uk_email",
"used key_parts":[
"email"
],
"key_length":"47",
"ref":[
"const"
],
"rows examined_per_scan":1,
"rows_produced_per_join":1,
"filtered":"100.00",
"cost_iufo":{
"read_cost":"0.00",
"eval_cost":"0.20",
"prefix_cost":"0.00",
"data_read_per_join":"112"
},
"used_columns":[
"id",
"firstname",
"lastname",
"email",
"phone:
]
}
}
}
1 row in set, 1 warning(0.00 sec)
ERROR:
No query specified
mysql>