MySQL EXPLAIN 实践汇总
来源：互联网 发布：网络在线打电话 编辑：程序博客网 时间：2024/06/04 19:17
MySQLEXPLAIN可以显示估计查询语句执行计划，从中可以分析查询的执行情况是不是最优，这有助于我们对不使用索引的查询进行优化。EXPLAIN对于每个查询语句返回一行信息，它列出了一个有序的表格，MySQL处理语句的时候会读取他们。MySQL解决所有的连接都使用嵌套连接方法。这意味着MySQL读取第一张一行，然后匹配第二张表的所有行，第三张表或更多表都如此。当所有的表在处理时，MySQL会输出已经查询出来的列，并且回溯到表继续查找直到所有的行被找到，从该表读取下一行，直到程序继续处理下一张表。
当使用关键词 EXTENDED 时，EXPLAIN 会处理通过 SHOW WARNINGS 看到的一些而外信息。EXPLAIN EXTENDED 也会显示这些滤出的列。
语法：
EXPLAIN <select statement>;
输出表格字段如下：
mysql> explain select * from mysql.user where user='root';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Column
JSONName
Meaning
id
select_id
查询标识。id越大优先执行；id相同自上而下执行；
select_type
None
查询的类型
table
table_name
查询的表
partitions
partitions
Thematching partitions
type
access_type
连接类型
possible_keys
possible_keys
可能选择的索引
key
key
实际使用的索引
key_len
key_length
使用的索引长度
ref
ref
哪一列或常数在查询中与索引键列一起使用
rows
rows
估计查询的行数
filtered
filtered
被条件过滤掉的行数百分比
Extra
None
解决查询的一些额外信息
以下主要举例说明3个字段：select_type 、type、Extra
【select_type】
alue
JSONName
Meaning
SIMPLE
None
简单查询 (不使用UNION或子查询)
PRIMARY
None
外层查询，主查询
UNION
None
UNION中第二个语句或后面的语句
DEPENDENTUNION
dependent (true)
UNION中第二个语句或后面的语句,独立于外部查询
UNIONRESULT
union_result
UNION的结果
SUBQUERY
None
子查询中第一个SELECT
DEPENDENTSUBQUERY
dependent (true)
子查询中第一个SELECT,独立于外部查询
DERIVED
None
子查询在 FROM子句中
MATERIALIZED
materialized_from_subquery
物化子查询（不清楚是什么样的查询语句？）
UNCACHEABLESUBQUERY
cacheable (false)
结果集不能被缓存的子查询，必须重新评估外层查询的每一行
UNCACHEABLEUNION
cacheable (false)
UNION中第二个语句或后面的语句属于不可缓存的子查询
创建测试表：
create table tabname (id int auto_increment not null primary key,name varchar(10) null,indate datetime null,tid int null,key(tid),key(indate))engine=innodb;create table tabname2 (id int auto_increment not null primary key,name varchar(10) null,indate datetime null,tid int null,key(tid),key(indate))engine=myisam;insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);#SIMPLE
mysql> explain select * from tabname;+----+-------------+---------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |+----+-------------+---------+------+---------------+------+---------+------+------+-------+
#PRIMARY / DERIVED
mysql> explain select * from (select * from tabname) as a;+----+-------------+------------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+------+---------------+------+---------+------+------+-------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | || 2 | DERIVED | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |+----+-------------+------------+------+---------------+------+---------+------+------+-------+
#PRIMARY / UNION / UNION RESULT
mysql> explain select * from tabname union select * from tabname;mysql> explain select * from tabname union all select * from tabname;+----+--------------+------------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------+------------+------+---------------+------+---------+------+------+-------+| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | || 2 | UNION | tabname | ALL | NULL | NULL | NULL | NULL | 3 | || NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |+----+--------------+------------+------+---------------+------+---------+------+------+-------+
#PRIMARY / SUBQUERY
mysql> explain select * from tabname where id=(select max(id) from tabname);+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+| 1 | PRIMARY | tabname | const | PRIMARY | PRIMARY | 4 | const | 1 | || 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+---------+-------+---------------+---------+---------+-------+------+------------------------------+
#PRIMARY / DEPENDENT SUBQUERY
mysql> explain select * from tabname a where exists(select 1 from tabname b where a.id=b.id);mysql> explain select *,(select name from tabname b where a.id=b.id) from tabname a;mysql> explain select * from tabname where id not in(select id from tabname);+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index |+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
#PRIMARY / DEPENDENT UNION / DEPENDENT SUBQUERY / UNION RESULT
mysql> explain select * from tabname where id in (select id from tabname union select id from tabname);+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | tabname | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index || 3 | DEPENDENT UNION | tabname | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using index || NULL| UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |+----+--------------------+------------+--------+---------------+---------+---------+------+------+-------------+
【type】
type
Meaning
system
表仅一行数据 (=system table).这是const连接类型的特例。
const
表最多只有一个匹配行，在查询开始时被读取。因为只有一个值，优化器将该列值视为常量。当在primarykey或者unique索引作为常量比较时被使用。
eq_ref(engine=myisam)
来自前面表的结果集中读取一行，这是除system和const外最好的连接类型。当在使用PRIMARYKEY或者UNIQUENOT NULL的索引时会被使用。
ref
对于前面表的结果集匹配查询的所有行，当连接使用索引key时，或者索引不是PRIMARYKEY和UNIQUE，则使用该类型。如果使用索引匹配少量行时，是不错的连接类型。
ref_or_null
连接类型类似ref，只是搜索的行中包含NULL值MySQL做了额外的查找。
fulltext
使用全文索引时出现。
index_merge
使用了索引合并优化。(未成功)
unique_subquery
该类型将ref替换成以下子查询的格式：
valueIN (SELECTprimary_key FROMsingle_table WHERE some_expr)
index_subquery
与 unique_subquery类似，但是将主键改为非唯一索引：
valueIN (SELECTkey_columnFROMsingle_table WHERE some_expr)
range
使用索引检索给定范围内的行。
index
该连接类型与ALL相同，除了扫描索引树。如果查询的字段都在索引列中，则使用index类型，否则为ALL类型。
ALL
对于前面表的结果集中，进行了全表扫描。最差的一种类型，应考虑查询优化了！
查询类型性能由优到差：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
#system
mysql> explain select id from(select id from tabname where id=1) as a;+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | || 2 | DERIVED | tabname | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
#const
mysql> explain select * from tabname as a,tabname as b where a.id=b.id and a.id=1;mysql> explain select * from tabname where id=1;+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | tabname | const | PRIMARY | PRIMARY | 4 | const | 1 | |+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
#eq_ref(engine=myisam)
mysql> explain select * from tabname2 as a,tabname2 as b where a.id=b.id;+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 3 | || 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | |+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
#ref
mysql> explain select * from tabname as a,tabname as b where a.tid=b.tid and a.tid=2;mysql> explain select * from tabname where tid=2;+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+| 1 | SIMPLE | tabname | ref | tid | tid | 5 | const | 1 | Using where |+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
#ref_or_null
mysql> explain select id,tid from tabname where tid=2 or tid is null;+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+| 1 | SIMPLE | tabname | ref_or_null | tid | tid | 5 | const | 2 | Using where; Using index |+----+-------------+---------+-------------+---------------+------+---------+-------+------+--------------------------+
#fulltext
mysql> alter table tabname2 add fulltext(name);mysql> explain select * from tabname2 where match(name) against('love');+----+-------------+----------+----------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+----------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tabname2 | fulltext | name | name | 0 | | 1 | Using where |+----+-------------+----------+----------+---------------+------+---------+------+------+-------------+#index_merge(未成功)
mysql> explain select * from tabname where tid>1 or indate<now();mysql> explain select * from tabname where (tid>1 or indate>now()) AND name<'kk';
#unique_subquery
mysql> explain select * from tabname where tid in(select id from tabname);mysql> explain select * from tabname where id in(select id from tabname);+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | tabname | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index |+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
#index_subquery
mysql> explain select * from tabname where tid in(select tid from tabname);mysql> explain select * from tabname where id in(select tid from tabname);+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+| 1 | PRIMARY | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using where || 2 | DEPENDENT SUBQUERY | tabname | index_subquery | tid | tid | 5 | func | 1 | Using index; Using where |+----+--------------------+---------+----------------+---------------+------+---------+------+------+--------------------------+
#range
mysql> explain select * from tabname where tid between 1 and 2;mysql> explain select * from tabname where id>1;+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | tabname | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
#index
mysql> explain select id,tid from tabname;mysql> explain select tid from tabname;+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | Using index |+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
#ALL
mysql> explain select * from tabname where tid<>2;mysql> explain select * from tabname;+----+-------------+---------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | |+----+-------------+---------+------+---------------+------+---------+------+------+-------+
【Extra】
该列输出关MySQL如何解决查询的额外信息。（下面列出部分常见的）
Extra
Meaning
usingwhere
使用过滤条件
usingindex
从索引树中查找所有列
usingtemporary
使用临时表存储结果集，在使用groupby和orderby发生
selecttables optimized away
没有groupby情况下使用min(),max(),或者count(*)
usingfilesort
有排序
notexists
在leftjoin中匹配一行之后将不再继续查询查询
distinct
查找到第一个匹配的行之后，MySQL则会停止对当前行的搜索
impossiblewhere
where子句总数失败的查询
impossiblehaving
having子句总数失败的查询
usingjoin buffer
使用连接缓存
Usingindex for group-by
与Usingindex类似，在使用group-by时可从索引中找到字段
#using where
mysql> explain select * from tabname where id>2;mysql> explain select * from tabname where tid=2;+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+| 1 | SIMPLE | tabname | ref | tid | tid | 5 | const | 1 | Using where |+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
#using index
mysql> explain select tid from tabname;+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | Using index |+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
#using temporary
mysql> explain select distinct name from tabname;+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary |+----+-------------+---------+------+---------------+------+---------+------+------+-----------------+
#select tables optimized away
mysql> explain select max(tid) from tabname;+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
#using filesort
mysql> explain select id,name from tabname group by id,name;mysql> explain select * from tabname order by name;+----+-------------+---------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+----------------+| 1 | SIMPLE | tabname | ALL | NULL | NULL | NULL | NULL | 3 | Using filesort |+----+-------------+---------+------+---------------+------+---------+------+------+----------------+
#not exists
mysql> explain select * from tabname a left join tabname b on a.id=b.id where b.id is null;+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | || 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | Using where; Not exists |+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------------------+
#distinct
mysql> explain select distinct a.id from tabname a left join tabname b on a.id=b.id;+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+| 1 | SIMPLE | a | index | NULL | tid | 5 | NULL | 3 | Using index; Using temporary || 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.id | 1 | Using index; Distinct |+----+-------------+-------+--------+---------------+---------+---------+-----------+------+------------------------------+
#impossible where
mysql> explain select * from tabname where 1=2;+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
#impossible having
mysql> explain select id,count(*) from tabname group by id having 1=2;mysql> explain select count(*) from tabname having 1=2;+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible HAVING |+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
#usingjoin buffer
#Using index for group-by
=====================================================================================
=====================================================================================
现在使用 EXTENDED 情况：
语法：
EXPLAIN EXTENDED <select statement>;
不使用 extended 和使用extended 的分析情况：
mysql> explain select tid from tabname;+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | Using index |+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain extended select tid from tabname;+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | tabname | index | NULL | tid | 5 | NULL | 3 | 100.00 | Using index |+----+-------------+---------+-------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
可以看到，使用 extended 时，输出的最下面多了 1 条警告。 此时可以用 show warnings 来查看：
mysql> show warnings \G;*************************** 1. row *************************** Level: Note Code: 1003Message: select `test`.`tabname`.`tid` AS `tid` from `test`.`tabname`1 row in set (0.00 sec)ERROR:No query specified
show warnings 显示了优化器中是怎么规范表和字段名的，在通过重写和优化规则之后的 select 语句是什么样子。
更多参考：
EXPLAIN Output Format
EXPLAIN EXTENDED Output Format
本文地址：Hello.KK （SQL Server） http://blog.csdn.net/kk185800961/article/details/49179619
0 0
- MySQL EXPLAIN 实践汇总
- [MySql]explain用法及实践
- MySQL Explain 结果解读与实践
- MySQL Explain 结果解读与实践
- mysql 索引及explain的一些实践
- MySQL 性能优化——「Explain 分析实践」
- MYSQL explain
- MYSQL Explain
- mysql explain
- mysql explain
- mysql explain
- MySQL Explain
- Mysql EXPLAIN
- mysql explain
- mysql explain
- mysql explain
- mysql explain
- MYSQL EXPLAIN
- SQL截取字符串
- 类超市管理系统后台第一次bug修改
- 自己用jQuery写一个瀑布流
- 搭建Maven开发环境
- sql 多行转多列,多行转一列合并数据,列转行
- MySQL EXPLAIN 实践汇总
- 开个人博客啦！！！
- 使用AD9361实现FM调频接收机接受广播
- jquery tmpl 详解
- gns3 1.4.0b3 gns3vm使用笔记
- 如何让我们的“家”更漂亮
- 微信连WIFI
- 什么样的loading动画，我会等！
- 共同期待 经典Q版坦克大战揭开神秘面纱
