MySQL 索引優化進階實戰：覆蓋索引、索引下推、條件下推與避坑指南

索引優化核心策略

1. 建立索引：查詢主導型應用的生命線

無索引 = 全表掃描。大表小結果集時，缺少索引會導致災難性效能。

反面教材：性別欄位只有 M/F 兩值，建索引不但無效，還拖慢寫入 → 過度索引

2. 複合索引 = 多個索引（最佳左前綴特性）

INDEX idx_a_b_c (a, b, c)
-- 等效於三個索引：
-- (a, b, c)、(a, b)、(a)

設計原則：最高頻 WHERE 條件欄位放最左，依序遞減。

3. NULL 值與索引的陷阱

• 含 NULL 的行 不進入索引
• 複合索引任一欄為 NULL → 整行不進該複合索引
• 最佳實踐：NOT NULL DEFAULT '' 或 DEFAULT 0

4. 短索引（前綴索引）節省空間

-- CHAR(255) 欄位，前 10 字符區分度已足夠
ALTER TABLE users ADD INDEX idx_name_prefix (name(10));

優勢：減少索引體積 → 更多索引節點在記憶體 → 少 I/O → 查詢更快

5. 排序與索引的互斥關係

MySQL 單次查詢只能使用一個索引。WHERE 用了索引，ORDER BY 就無法再用索引。

-- ❌ 無法同時利用索引
WHERE area = 'beijing' ORDER BY age DESC

-- ✅ 解法：建立複合索引覆蓋排序
INDEX idx_area_age (area, age)

6. LIKE 查詢索引生效邊界

7. 避免在索引欄上運算（SARGable 原則）

-- ❌ 索引失效：函數運算
SELECT * FROM orders WHERE YEAR(create_time) = 2023;
SELECT * FROM inventory WHERE amount / 7 < 24;

-- ✅ 等價改寫：運算移到右側
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
SELECT * FROM inventory WHERE amount < 24 * 7;

8. NOT IN / OR 導致索引失效

-- ❌ 通常全表掃描
SELECT * FROM t WHERE id NOT IN (1,2,3);
SELECT * FROM t WHERE a = 1 OR b = 2;

-- ✅ 改寫方案
-- NOT IN → NOT EXISTS
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

-- OR → UNION（需各側有索引）
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

-- 範圍替代
SELECT * FROM t WHERE id > 3 OR id < 1;  -- id 為主鍵時可用

進階優化技術

覆蓋索引

查詢欄位全部包含在索引中，不需回表，Extra 顯示 Using index。

INDEX idx_uid_status_created (user_id, status, created_at)

SELECT user_id, status, created_at 
FROM orders 
WHERE user_id = 100 AND status = 1;

索引條件下推（ICP，Index Condition Pushdown）

MySQL 5.6+：將部分 WHERE 條件下推到儲存引擎層過濾，減少回表次數。

INDEX idx_zip_name (zip_code, name)

SELECT * FROM users 
WHERE zip_code = '100000' AND name LIKE 'Zhang%';
-- ICP 會在索引層直接過濾 name，不回表

隱藏索引

-- MySQL 8.0+：測試索引移除影響
ALTER TABLE t ALTER INDEX idx_name INVISIBLE;
-- 優化器忽略此索引，但維護成本仍存在

索引優化檢查清單

實戰案例：電商訂單查詢優化

-- 原始慢查詢（1.2秒）
SELECT o.*, u.name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1 
  AND o.created_at > '2023-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- 優化步驟：
-- 1. orders 表建立複合索引
ALTER TABLE orders ADD INDEX idx_status_created_uid (status, created_at, user_id);

-- 2. users 表主鍵已存在

-- 3. 優化後查詢（0.02秒）
-- 利用覆蓋索引 + ICP + LIMIT 推遲 JOIN