MySQL 查詢優化系列：深入解析查詢優化器（二）

透過索引優化聯結查詢效能

為了進一步提升查詢效率，我們在其中一個聯結欄位上建立索引，並重新執行 EXPLAIN 指令觀察執行計畫的變化：

MySQL> ALTER TABLE t2 ADD INDEX (i2);
MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
Extra: Using where; Using index

從輸出結果可以明顯看出效能有了顯著提升。雖然 t1 仍需進行全表掃描（type 仍為 ALL），但優化器處理 t2 的方式發生了根本改變：

• 存取類型由 ALL 變為 ref：表示優化器可利用來自 t1 的參考值，透過索引查找快速定位 t2 中符合條件的資料列。
• ref 欄位顯示參考值：sampdb.t1.i1，確認了聯結條件的參考來源。
• 預估掃描行數大幅下降：從 1000 降至 10。這意味著優化器預估每筆 t1 資料只需檢查 t2 中的 10 筆資料（實際上僅 1 筆匹配，後文將說明如何改善此估計值）。總體預估組合數從 1,000,000 降為 10,000，效能提升顯著。

是否有必要為 t1 建立索引？

對於此特定聯結操作，掃描一張表是不可避免的，因此為 t1 建立索引並無實質效益。但為了驗證效果，我們仍嘗試為 t1.i1 加上索引並再次執行 EXPLAIN：

MySQL> ALTER TABLE t1 ADD INDEX (i1);
MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: i1
key: i1
key_len: 5
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
Extra: Using where; Using index

與先前輸出相比，t1 的執行計畫發生變化：

• type 從 ALL 變為 index：表示採用索引全掃描而非資料表全掃描。
• Extra 顯示 Using index：優化器直接從索引檔案讀取所需值，無需存取資料檔案。這在 MyISAM 表中尤為明顯，InnoDB 和 BDB 表同樣受惠於此優化。

使用 ANALYZE TABLE 改善統計資訊

執行 ANALYZE TABLE 可讓伺服器產生鍵值的靜態分佈統計，進一步優化預估值：

MySQL> ANALYZE TABLE t1, t2;
MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: i1
key: i1
key_len: 5
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 1
Extra: Using where; Using index

此時優化器精確預估 t2 中每筆 t1 值僅匹配 1 筆資料，總預估組合數進一步降為 1,000，達到最佳狀態。

介入優化器行為：STRAIGHT_JOIN 與索引提示

雖然優化器通常能做出最佳決策，但特定情況下仍需人工介入：

強制聯結順序：STRAIGHT_JOIN

使用 STRAIGHT_JOIN 可強制優化器依照指定順序聯結資料表。關鍵在於將「最具限制性（輸出行數最少）的表」放在最前面，越早縮小候選資料集，查詢效能越好。但需注意：

• 必須同時測試加上與不加 STRAIGHT_JOIN 的查詢效能，有時優化器原本的選擇反而更優。
• 若無法確定哪張表最具限制性，可嘗試將資料量最大的表置於首位。

索引使用提示：FORCE INDEX / USE INDEX / IGNORE INDEX

在聯結的資料表後方加上這些修飾符，可指導 MySQL 如何選擇索引，適用於優化器選錯索引的情境。

高效清空資料表：TRUNCATE TABLE 與替代方案

對於 MyISAM 表，TRUNCATE TABLE tbl_name; 透過重建表結構實現秒級清空，避免逐行刪除的開銷。但需注意副作用：

• 無法精確返回刪除行數：若需精確計數，請使用 DELETE FROM tbl_name;（無 WHERE 子句）。
• AUTO_INCREMENT 計數器歸零：若需保留當前序號，請使用 DELETE FROM tbl_name WHERE 1;。雖速度較慢（需逐行評估 WHERE 條件），但能保留自增序列值。