索引相關六大誤區
| # | 誤區 | 正確做法 | 原理 |
|---|
| 1 | 無索引/未命中 | EXPLAIN 關注 type=ALL, key=NULL | 全表掃描 |
| 2 | 索引列加函數 | WHERE gmt_created BETWEEN ... 替代 TO_CHAR(...) | 函數導致索引失效 |
| 3 | 前導模糊查詢 | 改用搜尋引擎或後綴匹配 | B+ 樹無法支援 LIKE '%xxx%' |
| 4 | 複合索引缺前導列 | 索引 (a,b)，查詢必須含 a | 索引鍵拼接順序決定 B+ 樹結構 |
| 5 | 查詢列超出索引 | 建立覆蓋索引包含 SELECT 列 | 避免回表，純索引掃描 |
| 6 | COUNT(id) 慢於 COUNT(*) | 統一用 COUNT(*)，優化器選最小索引 | COUNT(id) 需檢查 NULL |
Stop 機制善用
-- 判斷是否存在
SELECT 1 FROM offer WHERE member_id=234 LIMIT 1; -- 找到即停止
-- 優於
SELECT COUNT(*) FROM offer WHERE member_id=234; -- 全掃描
高效分頁策略
延遲關聯法
-- 原始：大偏移量排序慢
SELECT * FROM t WHERE ... ORDER BY id LIMIT 10000, 20;
-- 優化：子查詢只取 ID，再 JOIN 回表
SELECT t.* FROM (
SELECT id FROM t WHERE ... ORDER BY id LIMIT 10000, 20
) tmp JOIN t USING (id);
ORDER BY 索引失效常見模式
| 模式 | 索引 | 原因 |
|---|
| 混合排序 | (a,b,c) | ORDER BY a ASC, b DESC |
| 缺前導列 | (a,b,c) | WHERE g=const ORDER BY b,c |
| 缺中間列 | (a,b,c) | WHERE a=const ORDER BY c |
| 排序列不在索引 | (a,b,c) | WHERE a=const ORDER BY a,d |
隨機查詢優化演進
-- 1. 錯誤：全表掃描 + filesort
SELECT * FROM t WHERE kind=1 ORDER BY RAND() LIMIT 1;
-- 2. 改進：偏移量法（大偏移仍慢）
SELECT * FROM t WHERE kind=1 LIMIT 1 OFFSET (RAND() * (SELECT COUNT(*) FROM t WHERE kind=1));
-- 3. 最優：主鍵範圍查找（需連續自增整數主鍵）
SELECT * FROM t WHERE kind=1 AND id > (RAND() * (SELECT MAX(id) FROM t WHERE kind=1)) LIMIT 1;
高效 JOIN 原則
- 小表驅動大表
- 子查詢改寫為 JOIN（MySQL 子查詢優化弱）
- 關聯欄位必須有索引且類型一致
資料類型隱式轉換陷阱
-- 表：account CHAR(11)
EXPLAIN SELECT * FROM user WHERE account = 123; -- type=ALL，隱式 TO_NUMBER()
EXPLAIN SELECT * FROM user WHERE account = '123'; -- type=const，命中唯一索引
主鍵設計鐵律
- ✅ 自增 BIGINT：插入快、頁無碎片、二級索引小（存主鍵值而非行指針）
- ❌ UUID/業務鍵/組合鍵：寫入隨機、二級索引膨脹
