MySQL 優化利器 EXPLAIN 深度實戰 (上)：核心欄位逐個擊破

環境準備

MySQL 8.0.33
CREATE TABLE people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  zipcode INT,
  firstname VARCHAR(50),
  lastname VARCHAR(50),
  addr VARCHAR(100),
  city VARCHAR(50),
  state CHAR(2),
  KEY idx_zip_fn_ln (zipcode, firstname, lastname)
) ENGINE=InnoDB;

INSERT INTO people (zipcode, firstname, lastname, addr, city, state)
SELECT 
  FLOOR(10000 + RAND()*90000),
  ELT(FLOOR(1+RAND()*10), 'John','Jane','Bob','Alice','Tom','Mary','Mike','Lisa','David','Sarah'),
  ELT(FLOOR(1+RAND()*20), 'Smith','Johnson','Williams','Brown','Jones','Garcia','Miller','Davis','Wilson','Anderson','Taylor','Thomas','Moore','Jackson','Martin','Lee','Thompson','White','Harris'),
  CONCAT('Addr ', FLOOR(1+RAND()*10000)),
  ELT(FLOOR(1+RAND()*5), 'NY','LA','Chicago','Houston','Phoenix'),
  ELT(FLOOR(1+RAND()*3), 'NY','CA','IL')
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2
CROSS JOIN (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 4) t3;  -- ~256 rows

欄位深度解讀 (結合實例)

1. id — 查詢序號

EXPLAIN SELECT * FROM (SELECT id FROM people) a;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+----------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra    |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+----------+
|  1 | PRIMARY     |  | system | NULL              | NULL    | NULL    | NULL |    1 | NULL     |
|  2 | DERIVED     | people     | index  | NULL              | idx_zip_fn_ln | 158 | NULL |  256 | Using index |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+----------+

• id=2 (DERIVED) 先執行，掃描覆蓋索引產生衍生表；id=1 (PRIMARY) 讀取衍生表 (system: 單行)。

2. select_type — 查詢分類

3. table — 訪問對象

• 實表名 / 別名。
• <derivedN>：id=N 的衍生表結果 (無索引，慎用大結果集)。
• <unionM,N>：UNION 合併的臨時表。

4. type — 訪問類型 (性能核心指標)

由優到劣：

1. system：表僅 1 行 (system 表)。
2. const：主鍵/唯一索引 = 常量，**最多 1 行**，優化器提前讀取當常量處理。
   EXPLAIN SELECT * FROM people WHERE id=1;
3. eq_ref：Join 時用主鍵/唯一索引匹配，**每行最多 1 匹配**。
   EXPLAIN SELECT * FROM people p JOIN people_car pc ON p.id=pc.people_id WHERE pc.people_id IS UNIQUE;
4. ref：非唯一索引或聯合索引最左前綴匹配，**可能多行**。
   EXPLAIN SELECT * FROM people WHERE zipcode=10000; (idx_zip_fn_ln 最左列)
5. fulltext：全文索引。
6. ref_or_null：ref + 額外 NULL 檢查 (優化 IS NULL 子查詢)。
7. index_merge：合並多個索引 (Using intersect/union/sort_union)，**通常不如聯合索引**。
8. unique_subquery / index_subquery：IN 子查詢優化為索引查找函數。
9. range：索引範圍掃描 (>, >=, <, <=, BETWEEN, IN, LIKE 'prefix%')。**注意**：IN (1,2,3) 視為多個等值範圍，**可用聯合索引後綴列**；id > 10 範圍查詢**不可用聯合索引後綴列**。
10. index：全索引掃描 (覆蓋索引或 ORDER BY 索引)。
11. ALL：全表掃描，**必須優化**。

5. possible_keys / key / key_len

• possible_keys：優化器候選索引集合。
• key：實際選用索引。NULL = 無索引可用/全掃。**可用 FORCE INDEX(idx) / IGNORE INDEX(idx) 干預**。
• key_len：使用的索引字節長度。**推斷聯合索引用了幾列**：
  Int=4, BigInt=8, Char(N)=3N+2(utf8mb4), DateTime=5, Nullable+1。
  例：idx_zip_fn_ln (zipcode INT, firstname VARCHAR(50), lastname VARCHAR(50)) → 完整長度 4 + 152 + 152 = 308。若 key_len=4 僅用 zipcode；key_len=156 用了 zipcode+firstname。

6. ref — 索引比較值來源

• const：常量。
• 列名：Join 關聯列。
• func：函數/表達式結果 (如關聯子查詢)。

7. rows / filtered — 預估與過濾

• rows：優化器預估掃描行數 (基於統計信息，**可能嚴重偏差**)，ANALYZE TABLE 更新統計。
• filtered (5.7+)：表條件過濾後剩餘百分比。**rows * filtered% ≈ 實際參與 Join 的行數**，用於評估驅動表大小。

8. Extra — 關鍵額外信息 (見上文詳細表)

• Using index：覆蓋索引，**極佳**。
• Using where：Server 層過濾，**嘗試下推 (ICP) 或建更好索引**。
• Using temporary：臨時表，**優化 GROUP BY/ORDER BY/DISTINCT**。
• Using filesort：額外排序，**建索引匹配 ORDER BY**。
• Using index condition：ICP 索引條件下推 (5.6+)，**好現象**。
• Using join buffer (Block Nested Loop)：Join 無索引，**給關聯列加索引**。

實戰優化口訣

1. 看 type：≥ ref 才安心，ALL/index_merge 必改。
2. 看 key：有無命中預期索引？key_len 幾列？
3. 看 Extra：Using temporary/filesort → 首優；Using index → 滿意。
4. 看 rows × filtered：驅動表是否過大？需否 STRAIGHT_JOIN/Hint。
5. 看 select_type：DEPENDENT SUBQUERY → 改寫 JOIN/CTE。