06-26

語言: CN / TW / HK

Mysql 中查看錶結構,分析查詢語句的命令,有相關工作經驗的都能答上來:desc 或 explain,而且這兩個命令的效果是相同的。不過,今天的內容中,因為要知道表的儲存引擎是啥,所以我們使用的是 show create table 命令。這裡我準備了兩張表,分別是 myisam_user 和 user。通過命令檢視下,我們可以看到 ENGINE 分別是 MyISAM 和 InnoDB。

2. 分析查詢語句

查看下錶中的資料,當前兩張表中都只有一條資料資料,執行 explain select user 後,可看到返回結果。

這裡的欄位有 10 幾個,每個欄位都有相應的功能,表格列出了相關的描述。接下來就是圍繞著這些欄位來展開實驗,讓我們充分理解分析查詢語句過程中如何進行優化。比如說我們要知道 possible_keys 顯示可能應用在這張表中的索引,key 是實際使用的索引,如果為 NULL,則沒有使用索引。key_len 是使用的索引的長度,在不損失精確性的情況下,長度越短越好。rows 是 MySQL 認為它需要執行的行數。執行行數如果遠大於返回行數,就要引起注意,需要優化了。

本次實驗主要涉及 select_type、type 和 extra,其它欄位不在實驗範圍,如感興趣也可留言討論。

欄位 描述
select_type 查詢中每個select子句的型別。
可選值:SIMPLE,PRIMARY,UNION,DEPENDENT UNION,UNION RESULT,SUBQUERY,DEPENDENT SUBQUERY,DERIVED
table 查詢的資料表。
type 顯示連線使用了何種型別。
從最好到最差:system,const,eq_ref,ref,ref_or_null,fulltext,index_merge,unique_subquery,index_subquery,range,index,ALL。
possible_keys 顯示可能應用在這張表中的索引,一個或多個。
查詢涉及到的欄位上若存在索引,則該索引將被列出,但不一定被查詢實際使用。如果為空,則需要考慮相應的查詢語句是否合理。
key 實際使用的索引,如果為 NULL,則沒有使用索引。
如果沒有選擇索引,鍵是 NULL。要想強制 MySQL 使用或忽視 possible_keys 列中的索引,在查詢中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
key_len 使用的索引的長度。
在不損失精確性的情況下,長度越短越好。
ref 顯示哪個列或常數與 key 一起從表中進行選擇。
rows MySQL 認為它需要執行的行數。
優化查詢的關鍵資訊就在這,執行行數如果遠大於返回行數,就要引起注意,需要優化了。
extra 包含MySQL 解決查詢的詳細資訊。
這又是一個非常關鍵的列,資訊量有點大,一一舉例有點多,可以參考官文。 EXPLAIN Extra Information

2.1 Select type

關於 select type 欄位,前面的表格中已經列出,具體的意義感興趣的可以自行搜尋下,這裡不多解釋,因為專案中都會盡量避免複雜寫法。這裡有幾條查詢語句展示了每個 type 在什麼情況下會出現。

首先簡單查詢出現的就是 simple 了。

第二條語句展示了多個內容在一起,derived 是衍生表的意思,在 table 這一列出現 derived2 表示的是當前這個 explain 結果中的第二行產生的,所以第二行的 type 就是 derived 了。拉下來第 3 行是 union 了 user 表,它與第 2 行產生 union result 寫在了第 4 行中,表名就是 union2,3 了。

第三條語句展示的是子查詢 subquery 的情況。

第四條語句展示的是 dependent subquery 和 dependent union 了。

需要注意的是,in 查詢與 “=” 號查詢是有區別的。

explain select * from user where id = 1;
explain select id from (select * from myisam_user union select * from user) a;
explain select * from user where id = (select id from myisam_user where id = 1);
explain select * from user where id in (select id from user where id = 1 union all (select id from myisam_user where id = 1));
explain select * from user where id in (select id from myisam_user where id = 1);

Type

關於 type 欄位,是需要重點討論的欄位。表格列出了所有欄位並且是按效能從好到差排好序的。接下來就一個一個的實驗並解釋。

首先是 system 型別,它是 const 連結特有型別,表示只有一行滿足條件(只有一行),一般來說表示 MyISAM 和 Memory 儲存引擎中才會出現,但是在 InnoDB 中也可以寫出相關語句,不過是在產生衍生表後出現,我的理解是衍生表已經是 Memory 了。下面是針對 MyISAM 和 InnoDB 中實驗結果。system 型別在專案遇到不多。

explain select * from myisam_user;
explain select * from user;
explain select * from user where id = 1;
explain select * from (select * from user where id = 1 limit 1) a;

const 型別,使用用唯一索引或者主鍵查詢,使返回記錄最多隻有一行記錄時型別通常是 const,如前面查詢加上 id = 1 ,需要注意的是資料必須為 not null。

eq_ref 型別出現在要連線多個表的查詢計劃中,常常是用來比較兩個表之間帶索引的列。eq_ref 通常使用的是 primary keyunique index 。這是最好的連線型別。為了防止 const 或者 system 型別干擾,在兩張表中分別擴充到 2 條以上資料。

select * from user,myisam_user;
explain select * from user,myisam_user where user.id = myisam_user.id;

ref 型別跟 eq_ref 型別的區別在於採用的索引不是主鍵或者唯一索引。 一個好的查詢最次也得到ref級別,再低就準備優化吧

為了驗證此型別需要在 name 欄位上增加索引。

explain select * from user where name = 'aa';

ref_or_null 經常用於子查詢,它和 ref 類似,只是在查詢的時候會搜尋 null 值的記錄。這裡我修改了表結構,並將資料增加到了 7 條,防止索引失效。

show create table user;
select * from user;
explain select * from user where name = 'aa' or name is null;

fulltext 是全文索引,要注意的是全文索引的優先順序很高,若全文索引和普通索引同時存在時,MySQL 不管代價,優先選擇使用全文索引。為了驗證此功能,將 name 欄位的索引型別修改為 fulltext。

show create table user;
select * from user where id > 3;
explain select * from user where match(name) against('boss') and id > 3;

index_merge 型別表示查詢使用了兩個以上的索引,最後取交集或者並集,常見 and/or 的條件使用了不同的索引。注意,index_merge看上去是使用了索引,但當表的數量非常大時,其實查詢速度還是非常的慢。這裡把索引改回普通索引。

show create table user;
explain select * from user where name = 'aa' or id = 2;

unique_subquery 用於帶 in 的子查詢,子查詢返回不重複值唯一值,unique_subquey 通常使用的是 primary keyunique index 。官方文件中表示 unique_subquery 只是 eq_ref 的一個特例,對於下圖中這種 in 的語句查詢會出現以提高查詢效率。由於 MySQL 會對select 進行優化,基本無法出現這個場景,只能用 update 這種語句了。

explain update user set age = 19 where id in (select * from (select id from user where name like 'jo%') a);

index_subquery 和 unique_subquery 類似,只是針對的是非唯一索引。

range 是範圍查詢,其實就是帶有限制條件的索引掃描。

常見的範圍查詢比如 between and ,>,<,like,in 都有可能出現 range。

explain select * from user where id > 10;

index 跟全表掃描類似,只是掃表是按照索引順序進行。

explain select name from user;

ALL 就是全表掃描,沒啥好說的。

Extra

Extra 是查詢分析的附加額外資訊,這個太多了,有興趣可以自己看官方文件,只列舉一些常見的。

Using index是覆蓋索引。簡單來說就是查詢的資料在索引只已經存在,直接拿出結果就可以了

explain select name from user where name like 'a%' and name = 'aa';

Using index condition是索引下推,簡單來說就是加上了條件篩選,減少了回表的操作。這裡可以看到 age >= 17 的資料有 3 條,但是名字以 a 開頭的只有一條,所以我們在查詢時,儲存引擎會主動幫我們過濾掉不必要的資料。

select * from user where age >= 17;
select * from user where name like 'a%';
explain select * from user where age >= 17 and name like 'a%';

distinct、Using index for group-bydistinct 操作是一旦MYSQL找到了與行相聯合匹配的行,就不再搜尋了。一直沒找到相關的 case,但是 distinct 和 group by 都會觸發展示 Using index for group-by。

explain select count(distinct name) from user;

Using filesort使用外部索引檔案排序,但是不能從這裡看出是記憶體還是磁碟排序,我們只能知道更消耗效能。

Using temporary使用了臨時表排序。檔案排序、臨時表排序理論上是我們一定要優化的語句了,當然有特殊需求除外。

explain select distinct name,age from user group by name,age;

參考