SQL夯實基礎（六）：MqSql Explain

標籤：tor   應該   inf   pos   where   col   編碼   span   customer   

　　關係型資料庫中，互連網相關行業使用最多的無疑是mysql，雖然我們C# Developer很多用的都是sql server ，但是學習一些mysql方面的知識也是必要的，他山之石麼。

　　先上一個explain的執行個體，以下我會通過我自己的理解，逐個解釋表中每列的含義。(僅供樣本使用，實際項目不建議如此寫sql)。

id

　　這個欄位是用來確定查詢語句執行的優先順序的。

這個值會有三種情況：

id值相同：這種情況意味著查詢語句按照explain結果中的id自上而下執行

id值不相同：這種情況下，id值會自遞增，id值越大，explain結果中的相應sql語句被執行的優先順序越高，越先被執行。這通常會在子查詢中出現

id值存在相同的和不同的值：這種情況下，id值越大，優先順序越高，越先被執行，那麼，對於id值相同的結果，mysql會按照explain結果中的id自上而下執行。

select_type

表示查詢的類型，先看錶

 

PRIMARY：查詢中若包含若干子查詢或者巢狀查詢，那麼最外層的查詢將被標記為PRIMARY.

SUBQUERY：在select或where語句中包含子查詢

DERIVED：在from列表中包含的子查詢將被標記為DERIVED(衍生)，MySQL會遞迴執行這些子查詢，將結果放在暫存資料表中。

table

對應行正在訪問哪一個表，表名或者別名，有可能是一下幾種

1 實際的表名  

2 表的別名

比如 select * from customer as c

3 derived  子查詢

<derivedx>, x是個數字,我的理解是第幾步執行的結果

4 null 直接結算的結果，不走表

關聯最佳化器會為查詢選擇關聯順序，左側深度優先

當from中有子查詢的時候，表名是derivedN的形式，N指向子查詢，也就是explain結果中的下一列

當有union result的時候，表名是union 1,2等的形式，1,2表示參與union的query id

注意：MySQL對待這些表和普通表一樣，但是這些“暫存資料表”是沒有任何索引的。

 

type：

type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般來說，得保證查詢至少達到range層級，最好能達到ref。

 

ref            使用非唯一索引掃描或唯一索引首碼掃描（有時候需要索引很長的字元列，這會讓索引變得大且慢。通常可以索引開始的部分字元，這樣可以大大節約索引空間，從而提高索引效率），返回單條記錄，常出現在關聯查詢中

eq_ref         類似ref，區別在於使用的是唯一索引，使用主鍵的關聯查詢

possible_keys

　　指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用

　　該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按產生的表次序使用。

　　如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢效能。如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢

 

key

顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好 。

表示查詢最佳化工具使用了索引的位元組數. 這個欄位可以評估複合式索引是否完全被使用, 或只有最左部分欄位被使用到.

key_len 的計算規則如下:

字串

char(n): n 位元組長度

varchar(n): 如果是 utf8 編碼, 則是 3 n + 2位元組; 如果是 utf8mb4 編碼, 則是 4 n + 2 位元組.

數實值型別:

TINYINT: 1位元組

SMALLINT: 2位元組

MEDIUMINT: 3位元組

INT: 4位元組

BIGINT: 8位元組

時間類型

DATE: 3位元組

TIMESTAMP: 4位元組

DATETIME: 8位元組

欄位屬性: NULL 屬性 佔用一個位元組. 如果一個欄位是 NOT NULL 的, 則沒有此屬性.

我們來舉兩個簡單的栗子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = ‘p1‘ AND productor = ‘WHH‘ \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: order_info   partitions: NULL         type: rangepossible_keys: user_product_detail_index          key: user_product_detail_index      key_len: 9          ref: NULL         rows: 5     filtered: 11.11        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)

上面的例子是從表 order_info 中查詢指定的內容, 而我們從此表的建表語句中可以知道, 表 order_info 有一個聯合索引:

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

不過此查詢語句 WHERE user_id < 3 AND product_name = ‘p1‘ AND productor = ‘WHH‘ 中, 因為先進行 user_id 的範圍查詢, 而根據 最左首碼匹配 原則, 當遇到範圍查詢時, 就停止索引的匹配, 因此實際上我們使用到的索引的欄位只有 user_id, 因此在 EXPLAIN 中, 顯示的 key_len 為 9. 因為 user_id 欄位是 BIGINT, 佔用 8 位元組, 而 NULL 屬性佔用一個位元組, 因此總共是 9 個位元組. 若我們將user_id 欄位改為 BIGINT(20) NOT NULL DEFAULT ‘0‘, 則 key_length 應該是8.

上面因為 最左首碼匹配 原則, 我們的查詢僅僅使用到了聯合索引的 user_id 欄位, 因此效率不算高.

 

接下來我們來看一下下一個例子:

mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = ‘p1‘ \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: order_info   partitions: NULL         type: refpossible_keys: user_product_detail_index          key: user_product_detail_index      key_len: 161          ref: const,const         rows: 2     filtered: 100.00        Extra: Using index1 row in set, 1 warning (0.00 sec)

       這次的查詢中, 我們沒有使用到範圍查詢, key_len 的值為 161. 為什麼呢? 因為我們的查詢條件 WHERE user_id = 1 AND product_name = ‘p1‘ 中, 僅僅使用到了聯合索引中的前兩個欄位, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161

rows

rows 也是一個重要的欄位. MySQL 查詢最佳化工具根據統計資訊, 估算 SQL 要尋找到結果集需要掃描讀取的資料行數.

這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好.

Extra

EXplain 中的很多額外的資訊會在 Extra 欄位顯示, 常見的有以下幾種內容:

 

Using join buffer：改值強調了在擷取串連條件時沒有使用索引，並且需要串連緩衝區來儲存中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

總結：

• EXPLAIN不會告訴你關於觸發器、預存程序的資訊或使用者自訂函數對查詢的影響情況

• EXPLAIN不考慮各種Cache

• EXPLAIN不能顯示MySQL在執行查詢時所作的最佳化工作

• 部分統計資訊是估算的，並非精確值

• EXPALIN只能解釋SELECT操作，其他動作要重寫為SELECT後查看執行計畫。

 

SQL夯實基礎（六）：MqSql Explain