問題 SQL
SELECT COUNT(*)
FROM a JOIN b ON a.S = b.S
WHERE a.L > '2014-03-30 00:55:00' AND a.L < '2014-03-30 01:00:00';
表結構差異（關鍵）
| 表 | 欄位 S 定義 |
|---|
| a | S VARCHAR(64) CHARSET utf8 COLLATE utf8_bin |
| b | S VARCHAR(64) DEFAULT NULL（預設 utf8_general_ci） |
錯誤執行計劃
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| 1 | SIMPLE | b | index | IX_S | IX_S | 195 | NULL | 1,038,165| Using index |
| 1 | SIMPLE | a | ref | IX_L,IX_S | IX_S | 195 | test.b.S | 1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
- 優化器選
b 為驅動表（全索引掃描 103 萬行）
- 預期應以
a 為驅動（時間範圍僅 63 行）
STRAIGHT_JOIN 驗證假設
EXPLAIN SELECT COUNT(*) FROM a STRAIGHT_JOIN b ON a.S = b.S WHERE ...;
-- a 表 type=range (IX_L, rows=63) ✓
-- b 表 type=index (IX_S, rows=1,038,165) ✗ 仍全掃描！
真相大白：字符集不匹配導致索引失效
EXPLAIN SELECT * FROM a STRAIGHT_JOIN b ON BINARY a.S = b.S WHERE ...;
-- b 表 type=ALL, Extra: Range checked for each record (index map: 0x4)
-- 確認：無 BINARY 時走覆蓋索引掃描（誤以為用上索引），實際無法用索引查找
優化方案對比
| 方案 | SQL 寫法 | 原理 | 優缺點 |
|---|
| 1. 子查詢隱式轉換 | SELECT COUNT(*) FROM b JOIN (SELECT S FROM a WHERE ...) ta ON b.S = ta.S | 衍生表欄位繼承 utf8_bin，匹配 b.S | 直觀，隱式轉換 |
| 2. 顯式 BINARY（推薦） | SELECT COUNT(*) FROM a JOIN b ON BINARY a.S = b.S WHERE ... | 將 a.S 強制為二進制比較，匹配 b.S 的 utf8_general_ci | 語義明確，理論更快 |
| 3. DDL 修正（根治） | ALTER TABLE a MODIFY S VARCHAR(64) CHARSET utf8 COLLATE utf8_general_ci; | 統一校對規則 | 徹底解決，但需鎖表 |
核心教訓
- JOIN 欄位字符集/校對規則必須完全一致，否則索引失效
- 覆蓋索引掃描 ≠ 索引查找：
type=index + Using index 可能只是全索引掃描
BINARY col = ... 可臨時解決校對衝突，但最好 DDL 統一- 驗證技巧：
SELECT * 破壞覆蓋索引，觀察真實 type 與 key
- 上一篇文章： MySQL 異常查詢案例：LIMIT 值影響索引選擇的優化器 Bug 分析
- 下一篇文章： MySQL 常見優化點歸納：索引、分頁、JOIN、資料類型四大維度
Copyright ©
程式碼之家 All Rights Reserved
