| # | 場景 | 錯誤寫法 | 正確寫法 | 原理 |
|---|---|---|---|---|
| 1 | 不等號（!=、<>） | WHERE id != 2 | 改用 WHERE id < 2 OR id > 2（仍難用索引），或應用層過濾 | 範圍過大，優化器選全表掃描 |
| 2 | 索引欄用函數 | WHERE DAY(regTime) = 6 | WHERE regTime >= '2023-01-06' AND regTime < '2023-01-07' | 函數破壞索引順序 |
| 3 | JOIN 欄位型別不一致 | ON u.id = c.userId (id=int, userId=varchar) | 統一型別、長度、編碼、UNSIGNED | 隱式轉換導致索引失效 |
| 4 | LIKE 以 % 開頭 | WHERE name LIKE '%abc' | WHERE name LIKE 'abc%' 或全文索引 | B+ 樹無法從中間匹配 |
| 5 | 低選擇性欄位建索引 | WHERE isNovice = 1 (只有 0/1，各 50%) | 不建索引，或組合索引 (isNovice, create_time) | 回表成本 > 全表掃描 |
| 6 | OR 條件未全覆蓋索引 | WHERE id=2 OR id=4 OR id=6 | WHERE id IN (2,4,6) | IN 可走 range/ref，OR 易退化 |
| 7 | SELECT * 觸發回表 | SELECT * FROM t WHERE idx_col = 1 | SELECT id, col1 FROM t WHERE idx_col = 1（覆蓋索引） | 回表隨機 I/O 慢 |
| 8 | 隱式型別轉換 | WHERE varchar_col = 123 | WHERE varchar_col = '123' | 字串轉數值，索引失效 |
-- 慢
SELECT * FROM t_user WHERE id = 2 OR id = 4 OR id = 6;
-- 快
SELECT * FROM t_user WHERE id IN (2, 4, 6);
-- 慢
SELECT * FROM t_user WHERE DATE(regTime) = '2010-02-01';
-- 快
SELECT * FROM t_user WHERE regTime >= '2010-02-01' AND regTime < '2010-02-02';
-- 慢：DB 做距離運算
SELECT userId, SQRT(POW(unitX,2)+POW(unitY,2)) AS distance FROM t_map_unit WHERE userId=2;
-- 快：只取座標，應用層算距離
SELECT userId, unitX, unitY FROM t_map_unit WHERE userId=2;
-- 慢：大表 JOIN
SELECT c.*, u.name FROM t_user_city c LEFT JOIN t_user u ON u.id = c.userId;
-- 快：兩步查詢 + 應用層組裝
SELECT * FROM t_user_city;
SELECT name FROM t_user WHERE id IN (...);
原理：避免臨時表、減少鎖競爭、利用應用層記憶體與 CPU 平行處理。
CONCAT()，同樣導致索引失效，拆成多欄等值最佳。(where_col, order_col) 複合索引。EXPLAIN 確認 type != ALL。SHOW INDEX 確認索引存在且基數高。FLUSH STATUS; SELECT ...; SHOW SESSION STATUS LIKE 'Handler%'; 觀察 Handler_read_key vs Handler_read_rnd_next。pt-query-digest 定期審視。索引失效多源於「破壞索引順序」（函數、運算、隱式轉換）或「選擇性不足」（低基數、大範圍）。掌握上述 8 大場景與 4 大重寫技巧，配合 EXPLAIN 驗證，能解決 90% 以上的慢查詢問題。記住：好的 SQL 是寫出來的，更是測出來的。
