使用Explain分析select語句

explain可以分析某條select語句會查詢多少條記錄、以怎樣的方式查詢，以及復雜select的執行順序，借此可以了解到select語句的性能和查詢是如何執行的

如: select子句和from子句，先執行from子句

ps: 我們的服務器上mysql版本是5.1.73，mysql 5.6 explain能對update、insert等進行解釋

第一步：先插入大量數據，因為explain的結果和數據庫實際的數據有關系

delimiter $$                   
drop procedure if exists addoplist;
create procedure addoplist(in mpoint int, in mproductid int, in mnum int)
begin
declare id int;
declare maid int;
declare msid int;
declare mpid int;
set id=0;
while id<mnum do
select aid, sid, rid into maid, msid, mpid from tbl_roles where aid>=((select max(aid) from tbl_roles) - (select min(aid) from tbl_roles))*rand() + (select min(aid) from tbl_roles) limit 1;
insert table_oplist(optype, aid, sid, pid, optime, rid, point, freeze, productid, device) values(1, maid, msid, mpid, UNIX_TIMESTAMP(), 0, mpoint, 0, mproductid, concat("qwerwqrqwrwdxcvzxvdfge", round(rand()*1000)));
set id=id+1;
end while;
end$$
delimiter ;
call addoplist(30, 1010, 300000);

第二步：expalin解釋了哪些有用信息 例：explain select count(*) from table_oplist where device="qwerwqrqwrwdxcvzxvdfge52";

+----+-------------+------------+------+---------------+---------------+---------+-------+------+--------------------------+
| id | select_type | table      | type | possible_keys | key           | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+---------------+---------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | tbl_oplist | ref  | oplist_device | oplist_device | 131     | const |  307 | Using where; Using index |

+----+-------------+------------+------+---------------+---------------+---------+-------+------+--------------------------+

1> rows列是mysql估計為了找到所需的行而要讀取的行數; 這個估算可能不精確，也反映不出limit的作用; 結果有多行時，所有rows列的值相乘來粗略估算整個查詢要讀的行數；

rows和數據庫的實際總行數有關；

2> type列我認為是最重要的一列

ALL     ：按行全表掃描；當查詢中使用了limit時，并不是全表，而是找到limit的幾個就不再掃描了；或者在Extra列顯示“Using distinct/not exists”(暫時沒見過);

index   ：按索引次序全表掃描，而不是按行；當Extra列顯示"Using index",說明使用的是覆蓋索引，只掃描索引的數據，而不是按索引全表掃描；

range  ： 相對于index來說，它是一個范圍的索引掃描，不一定但通常出現在select語句中帶有between或者where子句里帶有>等比較符; 出現在in ()子句或or列表的情況（目前不太清楚）

ref       ： 索引訪問，索引跟參考值相比較，返回所有匹配行；它可能找到多個符合條件的行；查找+掃描；只有當使用非唯一索引或唯一索引的非唯一性前綴時才發生；ref_or_null也屬于這類（在初次查找的結果里進行第二次查找以找出NULL條目）

eq_ref  ：最多返回一條符合條件的記錄，在使用主鍵或者唯一性索引查找時可看到；

const, system ：mysql能對查詢的某部分進行優化并轉換成一個常量時，就會使用這個類型；例如：將主鍵放在where子句來選這行的主鍵時，就會被轉換成常量

NULL   ：mysql在優化階段分解查詢語句，在執行階段甚至用不著再訪問表或索引；

ref圖

mysql> create index oplist_device on table_oplist(device);

mysql> explain select * from table_oplist where  device="";
+----+-------------+------------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tbl_oplist | ref  | oplist_device | oplist_device | 131     | const |    4 | Using where |

+----+-------------+------------+------+---------------+---------------+---------+-------+------+-------------+

沒有索引時：

mysql> explain select distinct aid from table_oplist where  device="";
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | tbl_oplist | ALL  | NULL          | NULL | NULL    | NULL | 5180 | Using where; Using temporary |

+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------+

const圖

mysql> explain select * from table_account where account="test02222";
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_account | const | PRIMARY       | PRIMARY | 66      | const |    1 |       |

+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+

3> select_type

simple     : 簡單select,不包括子查詢和union

primary   : 查詢中若包含任何復雜的子部分,最外層的select被標記為primary

derived    : 對應的table列是<derivedN>

當explain輸出 select_type為derived時，表示一個嵌套范圍的開始，如果后面的id較小，代表嵌套已結束；

subquery  : select 子句

union       : union中的第二個或后面的select語句

union result: union的結果

4> table

表示對應行正在訪問的表；當from子句中有子查詢或有union時，table列會變得復雜；

當from子句中有子查詢時，table列是<derivedN>,N是explain輸出中后面一行的id

5> key

這一列顯示的是優化采用的哪一個索引可以最小化查詢成本；不一定出現在 possible_keys中

6> ref

這一列顯示了在key列記錄的索引中查找值所用的列或常量，值為null時僅表示啥都沒使用；