[TOC]
EXPLAIN 的使用
以下說明運行表結(jié)構(gòu)。
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
當(dāng)在一個SELECT語句前使用關(guān)鍵字EXPLAIN時,MYSQL會解釋將如何運行該SELECT語句,它顯示了表如何連接戈二、連接的順序等信息捌锭。
表以它們在處理查詢過程中將被MySQL讀入的順序被列出,而不表示執(zhí)行順序,每行顯示的是執(zhí)行計劃的每一個組成部分以及執(zhí)行的次序,查詢里的每張表對應(yīng)輸出結(jié)果集中的一行, 這里表的比較廣泛,子查詢炉爆、union結(jié)果集丛忆。否纬。慈格。也在此范圍內(nèi)怠晴。
舉例如下:
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,它告知服務(wù)器把執(zhí)行計劃反編譯成select語句, 可以通過show warnings看到這些生成的語句,通過查看這些語句可以知道查詢優(yōu)化器怎么轉(zhuǎn)化查詢語句遥金。
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的分區(qū)表,explain partitions 顯示查詢需要訪問的數(shù)據(jù)分片信息。
3. 重寫非select查詢
explain只能解釋select查詢,無法解釋存儲過程蒜田、insert稿械、delete、update等其他類似的語句,可以把非select語句轉(zhuǎn)化為對等的 select 訪問請求物邑。
4. EXPLAIN 使用注意點
4.1 并非所有的 explain 后面的 select 語句都不會執(zhí)行
from 子句中包含的子查詢會執(zhí)行,mysql 會執(zhí)行這個子查詢,并把結(jié)果集放入一個臨時表中, 故謹(jǐn)慎在線上執(zhí)行此類 explain 語句溜哮。
4.2 explain 只是一個近似
explain 只是一個近似,它大部分時候是一個很好的近似,但是有時候也會遠離實際情況,一些顯示出來的統(tǒng)計信息都是估算的,不精確。
EXPLAIN 中的列
首先進行如下約定:
簡單查詢 SIMPLE
復(fù)雜查詢:簡單子查詢 SUBQUERY色解、衍生表<derived2>茂嗓、union
1. id 列
表以它們在處理查詢過程中將被 MySQL 讀入的順序被列出,而不表示執(zhí)行順序。查詢里的 每張表對應(yīng)輸出結(jié)果集中的一行,使用 id 來標(biāo)示出屬于哪一行,id 大小順序并不一定跟表 被 mysql 讀入的數(shù)據(jù)一致科阎。
舉例如下:
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 越大優(yōu)先級越高,越先被執(zhí)行,如下:
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 如果相同,可以認為是一組,從上而下執(zhí)行;所有組中,id 值越大,優(yōu)先級越高,越先 被執(zhí)行,如下:
- union 操作 id 為 null
union 輸出結(jié)果集會多出一行,union 的結(jié)果集總是放在一個臨時表中,如<union1,2>,再從這個臨時表中獲取結(jié)果述吸。此行 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 | 查詢中包含任何復(fù)雜查詢,最外層的的SELECT |
| SUBQUERY | 在 select 或 where 中包含的子查詢 |
| DERIVED | FROM 子句的子查詢;若 union 包含在 from 子句的子查詢中,外層的 select被標(biāo)記為 DERIVED |
| UNION | 在 select 或 where 中包含的子查詢 |
| UNION RESULT | UNION結(jié)果 |
| 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
和全表掃描一樣也是做遍歷,不同的是它是做全索引遍歷,按照索引的次序進行遍歷
優(yōu)點:避免排序
如果在 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
當(dāng) mysql 對查詢的某部分進行了優(yōu)化,并轉(zhuǎn)話為一個常量時使用這種訪問類型 system 是 const 類型的特例,當(dāng)查詢的表中只有一行時為 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 能夠在查詢過程中分解查詢語句,甚至在執(zhí)行環(huán)節(jié)無需再訪問表或者索引 舉例如下:
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 使用了哪一個索引來優(yōu)化對該表的訪問错英。但若查詢使用了覆蓋索引,則僅出現(xiàn)在
key列中,舉例如下:
5. key_len 列
顯示了 mysql 在索引中使用的字節(jié)數(shù),key_len是根據(jù)表定義算出來的,和具體數(shù)據(jù)無關(guān)入撒。
如上圖舉例,uid、doc_id各四個字節(jié),故 key_len為8
6. ref 列
表示表的連接匹配條件,即哪些列或者常量被用于查找索引列上的值,舉例如下:
7. rows 列
表示 mysql 根據(jù)表統(tǒng)計信息及索引使用情況,估算的要找出結(jié)果需要讀取的記錄行數(shù)
8. Extra 列
Extra 列有多種取值,介紹如下四種
- using index
表明在 select 操作中使用了覆蓋索引 - using where
表示 mysql 存儲引擎收到結(jié)果后再進行過濾,舉例如下: - using temporary
表示 mysql 需要使用臨時表來存儲結(jié)果集,排序椭岩、分組常見,如下: - 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)
