mysql-explain

[TOC]

EXPLAIN 的使用

以下说明运行表结构。

 CREATE TABLE `iknow_team_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `teamId` int(11) unsigned NOT NULL COMMENT 'teamId',
  `uid` int(11) unsigned NOT NULL COMMENT 'uid',
  `username` varchar(32) NOT NULL DEFAULT '' COMMENT 'username',
  `joinTime` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'joinTime`',
  `uType` smallint(8) unsigned NOT NULL DEFAULT '0',
  `tagList` varchar(512) NOT NULL DEFAULT '',
  `extPack` varchar(2000) DEFAULT '' COMMENT 'json',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ituid_tid` (`uid`,`teamId`),
  KEY `ittid_utype` (`teamId`,`uType`)
) ENGINE=InnoDB AUTO_INCREMENT=452786 DEFAULT CHARSET=gbk COMMENT='team_user'

1. EXPLAIN tbl_name

EXPLAIN tbl_name 是 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 的一个同义词。
举例如下:

explain iknow_team_user;
+----------+----------------------+------+-----+---------+----------------+
| Fie      | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | int(11) unsigned     | NO   | PRI | NULL    | auto_increment |
| teamId   | int(11) unsigned     | NO   | MUL | NULL    |                |
| uid      | int(11) unsigned     | NO   | MUL | NULL    |                |
| username | varchar(32)          | NO   |     |         |                |
| joinTime | int(11) unsigned     | NO   |     | 0       |                |
| uType    | smallint(8) unsigned | NO   |     | 0       |                |
| tagList  | varchar(512)         | NO   |     |         |                |
| extPack  | varchar(2000)        | YES  |     |         |                |
+----------+----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

2. EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options

当在一个SELECT语句前使用关键字EXPLAIN时,MYSQL会解释将如何运行该SELECT语句,它显示了表如何连接、连接的顺序等信息。
表以它们在处理查询过程中将被MySQL读入的顺序被列出,而不表示执行顺序,每行显示的是执行计划的每一个组成部分以及执行的次序,查询里的每张表对应输出结果集中的一行, 这里表的比较广泛,子查询、union结果集。。。也在此范围内。
举例如下:

explain select * from iknow_team_user where id<100;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | iknow_team_user | range | PRIMARY       | PRIMARY | 4       | NULL |   73 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

2.1 EXPLAIN EXTENDED

5.1 之后增加了一个额外的过滤列extended,它告知服务器把执行计划反编译成select语句, 可以通过show warnings看到这些生成的语句,通过查看这些语句可以知道查询优化器怎么转化查询语句。

explain extended select * from iknow_team_user where id<100;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | iknow_team_user | range | PRIMARY       | PRIMARY | 4       | NULL |   73 |   100.00 | Using where | 
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`iknow_team_user`.`id` AS `id`,`test`.`iknow_team_user`.`teamId` AS `teamId`,`test`.`iknow_team_user`.`uid` AS `uid`,`test`.`iknow_team_user`.`username` AS `username`,`test`.`iknow_team_user`.`joinTime` AS `joinTime`,`test`.`iknow_team_user`.`uType` AS `uType`,`test`.`iknow_team_user`.`tagList` AS `tagList`,`test`.`iknow_team_user`.`extPack` AS `extPack` from `test`.`iknow_team_user` where (`test`.`iknow_team_user`.`id` < 100)
1 row in set (0.01 sec)

2.2 EXPLAIN PARTITIONS

如果使用了mysql的分区表,explain partitions 显示查询需要访问的数据分片信息。

3. 重写非select查询

explain只能解释select查询,无法解释存储过程、insert、delete、update等其他类似的语句,可以把非select语句转化为对等的 select 访问请求。

4. EXPLAIN 使用注意点

4.1 并非所有的 explain 后面的 select 语句都不会执行

from 子句中包含的子查询会执行,mysql 会执行这个子查询,并把结果集放入一个临时表中, 故谨慎在线上执行此类 explain 语句。

4.2 explain 只是一个近似

explain 只是一个近似,它大部分时候是一个很好的近似,但是有时候也会远离实际情况,一些显示出来的统计信息都是估算的,不精确。

EXPLAIN 中的列

首先进行如下约定:
简单查询 SIMPLE
复杂查询:简单子查询 SUBQUERY、衍生表<derived2>、union

1. id 列

表以它们在处理查询过程中将被 MySQL 读入的顺序被列出,而不表示执行顺序。查询里的每张表对应输出结果集中的一行,使用 id 来标示出属于哪一行,id 大小顺序并不一定跟表被 mysql 读入的数据一致。
举例如下:

explain select u.username, (select uid from iknow_team_user limit 1) uu from (select uid,username from iknow_team_user where uid=123) u;
+----+-------------+-----------------+--------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table           | type   | possible_keys | key       | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+--------+---------------+-----------+---------+------+--------+-------------+
|  1 | PRIMARY     | derived3        | system | NULL          | NULL      | NULL    | NULL |      1 |             | 
|  3 | DERIVED     | iknow_team_user | ref    | ituid_tid     | ituid_tid | 4       |      |      1 |             | 
|  2 | SUBQUERY    | iknow_team_user | index  | NULL          | ituid_tid | 8       | NULL | 414815 | Using index | 
+----+-------------+-----------------+--------+---------------+-----------+---------+------+--------+-------------+
3 rows in set (0.01 sec)

以下几点需要注意:

如果是子查询 SUBQUERY,id 越大优先级越高,越先被执行,如下:

explain select * from iknow_team_user where iknow_team_user.uid=(select uid from iknow_team_user as tb where tb.uid=(select uid from iknow_team_user as tc where tc.uid=1));
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table           | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | iknow_team_user | ref  | ituid_tid     | ituid_tid | 4       | const |    1 | Using where              |
|  2 | SUBQUERY    | tb              | ref  | ituid_tid     | ituid_tid | 4       |       |    1 | Using where; Using index |
|  3 | SUBQUERY    | tc              | ref  | ituid_tid     | ituid_tid | 4       |       |    1 | Using index              |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+--------------------------+
3 rows in set (0.00 sec)

id 如果相同,可以认为是一组,从上而下执行;所有组中,id 值越大,优先级越高,越先被执行,如下:
union 操作 id 为 null
union 输出结果集会多出一行,union 的结果集总是放在一个临时表中,如<union1,2>,再从这个临时表中获取结果。此行 id 为 null。
如下:

explain select * from iknow_team_user where uid = 1 union select * from  iknow_team_user where uid=1213;
+----+--------------+-----------------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type  | table           | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+--------------+-----------------+------+---------------+-----------+---------+-------+------+-------+
|  1 | PRIMARY      | iknow_team_user | ref  | ituid_tid     | ituid_tid | 4       | const |    1 |       |
|  2 | UNION        | iknow_team_user | ref  | ituid_tid     | ituid_tid | 4       | const |    1 |       |
| NULL | UNION RESULT | union1,2      | ALL  | NULL          | NULL      | NULL    | NULL  | NULL |       |
+----+--------------+-----------------+------+---------------+-----------+---------+-------+------+-------+
3 rows in set (0.00 sec)

2. select type 列

val	explain
SIMPLE	简单 SELECT(不使用 UNION 或子查询)
PRIMARY	查询中包含任何复杂查询,最外层的的SELECT
SUBQUERY	在 select 或 where 中包含的子查询
DERIVED	FROM 子句的子查询;若 union 包含在 from 子句的子查询中,外层的 select被标记为 DERIVED
UNION	在 select 或 where 中包含的子查询
UNION RESULT	UNION结果
DEPENDENT UNION	UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询 DEPENDENT SUBQUERY
DEPENDENT SUBQUERY	子查询中的第一个 SELECT,取决于外面的查询

举例如下:

3. type 列

即访问类型,可以理解为 mysql 在表里找出所需要行的方式以下的访问类型,性能从最差到最好:

3. 1. All

全表扫描,mysql 必须扫描从头到尾扫描整张表,找到所需要的行
例外:查询条件中使用了 limit，extra 列中显示使用了 distinct 或 not exists 等限定词

explain select * from iknow_team_user where tagList!='';
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | iknow_team_user | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where | 
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

3. 2. index

和全表扫描一样也是做遍历,不同的是它是做全索引遍历,按照索引的次序进行遍历
优点:避免排序
如果在 extra 中看到 using index 说明使用了覆盖索引
举例如下:

explain select id from iknow_team_user limit 10;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | iknow_team_user | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

3. 3. range

有限制的索引扫描,比全索引扫描好一些,不用遍历全部索引,常见的:> < in or
举例如下:

explain select * from iknow_team_user where id < 5 limit 10;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+------------------------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra                        |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+------------------------------+
|  1 | SIMPLE      | iknow_team_user | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using temporary |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

3. 4. ref

非唯一性索引扫描,访问非唯一索引,或唯一索引的前缀时返回匹配这个单独值的所有行

explain select * from iknow_team_user where uid=1 limit 10;
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------+
| id | select_type | table           | type | possible_keys | key       | key_len | ref   | rows | Extra           |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------+
|  1 | SIMPLE      | iknow_team_user | ref  | ituid_tid     | ituid_tid | 4       | const |    1 | Using temporary |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------+
1 row in set (0.00 sec)

3. 5. eq_ref

唯一性索引扫描,主键或唯一索引扫描中常见

3. 6. const,system

当 mysql 对查询的某部分进行了优化,并转话为一个常量时使用这种访问类型 system 是 const 类型的特例,当查询的表中只有一行时为 system
举例如下:

explain select * from iknow_team_user where teamId=1 and uid=1 limit 10;
+----+-------------+-----------------+-------+-----------------------+-----------+---------+-------------+------+-------+
| id | select_type | table           | type  | possible_keys         | key       | key_len | ref         | rows | Extra |
+----+-------------+-----------------+-------+-----------------------+-----------+---------+-------------+------+-------+
|  1 | SIMPLE      | iknow_team_user | const | ituid_tid,ittid_utype | ituid_tid | 8       | const,const |    1 |       |
+----+-------------+-----------------+-------+-----------------------+-----------+---------+-------------+------+-------+
1 row in set (0.00 sec)

3. 7. NULL

mysql 能够在查询过程中分解查询语句,甚至在执行环节无需再访问表或者索引举例如下:

explain select min(id) from iknow_team_user limit 10;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

神奇!! !table、type、key 列都为 NULL

4. possible_keys 列与 key 列

possible_keys 列表明该查询可能使用哪些索引,但一个查询仅能使用到一个索引,key 列显示了 mysql 使用了哪一个索引来优化对该表的访问。但若查询使用了覆盖索引,则仅出现在
key列中,举例如下:

5. key_len 列

显示了 mysql 在索引中使用的字节数,key_len是根据表定义算出来的,和具体数据无关。
如上图举例,uid、doc_id各四个字节,故 key_len为8

6. ref 列

表示表的连接匹配条件,即哪些列或者常量被用于查找索引列上的值,举例如下:

7. rows 列

表示 mysql 根据表统计信息及索引使用情况,估算的要找出结果需要读取的记录行数

8. Extra 列

Extra 列有多种取值,介绍如下四种

using index
表明在 select 操作中使用了覆盖索引
using where
表示 mysql 存储引擎收到结果后再进行过滤,举例如下:
using temporary
表示 mysql 需要使用临时表来存储结果集,排序、分组常见,如下:
using filesort
mysql 中无法利用索引完成的排序成为 filesort,如下

explain select teamId from iknow_team_user group by tagList limit 10;
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | iknow_team_user | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

最后编辑于：2017.11.27 05:37:11

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,839评论 6赞 482
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,543评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 153,116评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,371评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,384评论 5赞 374
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,111评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,416评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,053评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,558评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,007评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,117评论 1赞 334
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,756评论 4赞 324
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,324评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,315评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,539评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,578评论 2赞 355
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,877评论 2赞 345