MySQL • SQL 效能解密

本節方法適用於所有版本的 MySQL。

取得執行計畫#

在 SQL 敘述前加上 explain：

EXPLAIN SELECT 1;

計畫以表格形式呈現（此處省略了幾個較不重要的欄位）：

~+-------+------+---------------+------+~+------+------------~
~| table | type | possible_keys | key  |~| rows | Extra
~+-------+------+---------------+------+~+------+------------~
~| NULL  | NULL | NULL          | NULL |~| NULL | No tables...
~+-------+------+---------------+------+~+------+------------~

操作#

索引與資料表存取#

MySQL 的 explain plan 容易給人虛假的安全感，因為它對「索引有被使用」講了很多。這在技術上沒錯，但不代表索引被有效率地使用——即使 TYPE 欄位出現 INDEX 關鍵字，也不表示索引建得正確。

eq_ref：只做 B-tree 走訪。當主鍵或唯一性約束保證搜尋條件最多匹配一筆時使用。
ref、range：做 B-tree 走訪並走過葉節點找出所有匹配條目（類似 INDEX RANGE SCAN）。
index：依索引順序讀取整個索引（所有列），類似 INDEX FULL SCAN。
ALL：以磁碟儲存順序讀取整張表（所有列與欄位）。除了高 IO，還必須檢視所有列，同樣對 CPU 造成可觀負載。
Using Index（出現在 Extra 欄位）：代表沒有存取資料表，因為索引已具備所需的全部資料。可以把它想成「using index ONLY」。
PRIMARY（出現在 key 或 possible_keys 欄位）：主鍵自動建立的索引名稱。

排序與分組#

using filesort（Extra 欄位）：代表有明確的排序操作——不論排序發生在主記憶體還是磁碟上。它需要大量記憶體具體化中間結果（非管線化）。

Top-N 查詢#

MySQL 的執行計畫不會明確標示 top-N 查詢。若你使用 limit 語法，而 Extra 欄位沒有出現 using filesort，就代表它是以管線化方式執行的。

區分存取述詞與篩選述詞#

MySQL 以三種方式評估 where 子句：

存取述詞（透過 key_len 欄位判斷）：葉節點走訪的起訖條件。
索引篩選述詞（Using index condition，MySQL 5.6 起）：只在葉節點走訪期間套用，不縮小掃描範圍。
資料表層級篩選述詞（Extra 欄位的 Using where）：欄位不屬於索引，必須先從表中載入該列才能求值。

範例一：整個 where 子句都是存取述詞#

CREATE TABLE demo (
   id1 NUMERIC
 , id2 NUMERIC
 , id3 NUMERIC
 , val NUMERIC);

INSERT INTO demo VALUES (1,1,1,1);
INSERT INTO demo VALUES (2,2,2,2);

CREATE INDEX demo_idx ON demo (id1, id2, id3);

EXPLAIN
 SELECT * FROM demo
  WHERE id1=1 AND id2=1;

+------+----------+---------+------+-------+
| type | key      | key_len | rows | Extra |
+------+----------+---------+------+-------+
| ref  | demo_idx | 12      |    1 |       |
+------+----------+---------+------+-------+

Extra 欄位既沒有 Using where 也沒有 Using index condition，而索引確實被使用（type=ref、key=demo_idx），因此可以推斷整個 where 子句都是存取述詞。

用 key_len 可以驗證這一點：它顯示查詢用掉索引定義的前 12 個位元組。要把它對應到欄位名稱，你「只需」知道每個欄位佔多少儲存空間（見 MySQL 文件的「Data Type Storage Requirements」）。在沒有 NOT NULL 約束時，MySQL 每個欄位還要多一個位元組——本例中每個 NUMERIC 欄位需要 6 個位元組。12 這個鍵長因此證實前兩個索引欄位被用作存取述詞。

範例二：改用 ID3 篩選#

EXPLAIN
 SELECT * FROM demo
  WHERE id1=1 AND id3=1;

MySQL 5.6 及之後的版本使用索引篩選述詞：

+------+----------+---------+------+-----------------------+
| type | key      | key_len | rows | Extra                 |
+------+----------+---------+------+-----------------------+
| ref  | demo_idx | 6       |    1 | Using index condition |
+------+----------+---------+------+-----------------------+

鍵長為 6，代表只有一個欄位被用作存取述詞。

較早的版本則對同一段查詢使用資料表層級篩選述詞——以 Extra 欄位的 Using where 標示：

+------+----------+---------+------+-------------+
| type | key      | key_len | rows | Extra       |
+------+----------+---------+------+-------------+
| ref  | demo_idx | 6       |    1 | Using where |
+------+----------+---------+------+-------------+