MySQL 查詢優化實戰案例：字元集不一致導致 JOIN 索引失效，從全表掃描到毫秒級

診斷過程

1. 強制直連順序驗證假設

EXPLAIN SELECT COUNT(*) 
FROM a STRAIGHT_JOIN b ON a.S = b.S 
WHERE a.L > ... AND a.L < ...;
-- a: range, key=IX_L, rows=63
-- b: index, key=IX_S, rows=1038165, Extra=Using where; Using index; Using join buffer

雖順序對了，但 b 表仍走 **index 全掃描**，非 ref 精準查找。

2. 檢查 b.S 索引基數

SHOW INDEX FROM b;  -- Cardinality: 1038165 (接近唯一)
SHOW TABLE STATUS LIKE 'b';  -- Rows: 1038165

索引選擇性極好，理應走 ref，為何不走？

3. 覆蓋索引掩蓋真相

EXPLAIN SELECT * 
FROM a STRAIGHT_JOIN b ON BINARY a.S = b.S 
WHERE a.L > ... AND a.L < ...;
-- b: type=ALL, key=NULL, Extra=Range checked for each record (index map: 0x4)

SELECT * 無法用覆蓋索引，**真相大白：b 表完全走不了索引**，之前是覆蓋索引誤導優化器。

優化方案

方案一：SQL 改寫（隱式轉換）

SELECT COUNT(*) 
FROM b JOIN (
    SELECT S FROM a 
    WHERE a.L > '2014-03-30 00:55:00' AND a.L < '2014-03-30 01:00:00'
) ta ON b.S = ta.S;

衍生表 ta.S 繼承 utf8_bin，JOIN 時隱式轉換發生在**小表**上，b 表索引可用。

方案二：顯式 BINARY 轉換（推薦）

SELECT COUNT(*) 
FROM a JOIN b ON BINARY a.S = b.S 
WHERE a.L > '2014-03-30 00:55:00' AND a.L < '2014-03:30 01:00:00';

BINARY a.S 強制轉為二進位比較（等同 utf8_bin），消除字元集差異，b 表索引直接生效。**理論上比方案一更快**。

核心心得

1. JOIN 條件欄位必須字元集、校對規則完全一致**，否則索引失效
2. utf8_bin 大小寫敏感，utf8_general_ci 不敏感，命名規則 _bin vs _ci
3. 覆蓋索引可能掩蓋「索引實際不可用」的真相，SELECT * 驗證更可靠
4. DDL 修改欄位定義成本大，**SQL 層面用 BINARY 或衍生表轉換** 成本最低

💡 一句話心法：JOIN 走不了索引先查字元集，BINARY 一加索引即生效。