SQL 查詢優化 15 條實戰法則：從全表掃描到索引覆蓋

核心原則：讓優化器用上索引

優化本質是減少掃描行數。掃描參數（SARG）能被優化器識別為索引查找條件，非 SARG 條件導致全表掃描。

15 條優化鐵律

1. 具體列代替 SELECT *

-- ❌ 悪習
SELECT * FROM t WHERE id=1;
-- ✅ 最佳
SELECT id, name, score FROM t WHERE id=1;

減少網絡傳輸、內存佔用、解析開銷，利用覆蓋索引。

2. WHERE 條件優先建索引

-- WHERE、JOIN ON、GROUP BY、ORDER BY 使用的列優先建索引
CREATE INDEX idx_user_status ON orders(user_id, status);

3. 避免 IN / NOT IN 導致全表掃描

-- ❌ 子查詢 IN
SELECT * FROM t1 WHERE uid IN (SELECT uid FROM t2 WHERE status=1);
-- ✅ EXISTS 關聯
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.uid=t1.uid AND t2.status=1);
-- ✅ 連續值用 BETWEEN
SELECT * FROM t WHERE id BETWEEN 100 AND 200;

4. 避免 OR 導致索引失效

-- ❌ OR 通常導致全表掃描
SELECT * FROM t WHERE id=1 OR id=2;
-- ✅ UNION ALL 走索引
SELECT * FROM t WHERE id=1
UNION ALL
SELECT * FROM t WHERE id=2;

注意：同列 OR（如 id=1 OR id=2）優化器可能自動優化為 IN 走索引，但跨列 OR 必須用 UNION。

5. 避免前導模糊查詢

-- ❌ 索引失效
SELECT * FROM t WHERE name LIKE '%abc%';
-- ✅ 後置模糊走索引
SELECT * FROM t WHERE name LIKE 'abc%';

全文搜索場景用 Fulltext 索引或 Elasticsearch。

6. 避免 NULL 判斷導致索引失效

-- ❌
SELECT * FROM t WHERE score IS NULL;
-- ✅ 設計階段給默認值 0
SELECT * FROM t WHERE score = 0;

7. 避免 WHERE 左側運行函數/表達式

-- ❌ 索引失效
SELECT * FROM t WHERE YEAR(create_time) = 2023;
SELECT * FROM t WHERE score/10 > 90;
-- ✅ 移至右側
SELECT * FROM t WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
SELECT * FROM t WHERE score > 900;

8. 避免 WHERE 1=1 條件

-- ❌ 動態拼接殘留
SELECT * FROM t WHERE 1=1 AND name='abc';
-- ✅ 代碼層組裝：無 WHERE 加 WHERE，有 WHERE 加 AND

9. 覆蓋索引：查詢列全在索引中

-- 索引 idx_a_b(a,b)
SELECT a, b FROM t WHERE a=1; -- 只掃描索引，不回表

EXPLAIN 顯示 Using index 表示命中覆蓋索引。

10. 最左前綴原則

-- 聯合索引 idx_a_b_c(a,b,c)
WHERE a=1              -- ✅ 用到 a
WHERE a=1 AND b=2      -- ✅ 用到 a,b
WHERE a=1 AND c=3      -- ⚠️ 只用到 a（b 缺失）
WHERE b=2              -- ❌ 無法用到索引

11. 範圍查詢後列索引失效

-- idx_a_b_c(a,b,c)
WHERE a=1 AND b>2 AND c=3  -- 只用到 a,b；c 失效（b 是範圍）

12. 條件下推 (ICP) 利用

5.6+ 支持 Index Condition Pushdown，將 WHERE 條件下推到存儲引擎層過濾，減少回表。EXPLAIN 顯示 Using index condition。

13. 連接查詢驅動表優先

-- 小表驅動大表
SELECT * FROM small_table s JOIN large_table l ON s.id=l.s_id;
-- 確保被驅動表關聯列有索引

14. LIMIT 分頁優化

-- ❌ 深分頁掃描大量行
SELECT * FROM t ORDER BY id LIMIT 1000000, 10;
-- ✅ 子查詢定位主鍵再回表
SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10;
-- ✅ 或業務層禁止深分頁，用游標/ID 續傳

15. 定期 ANALYZE TABLE 更新統計信息

ANALYZE TABLE t;

優化器依賴統計信息選擇執行計劃，數據變更超過 10% 後建議執行。

EXPLAIN 實戰讀懂執行計劃

總結

SQL 優化無銀彈，核心是理解索引結構（B+Tree）、掌握最左前綴、識別 SARG 條件、讀懂 EXPLAIN。15 條法則覆蓋 90% 常見慢查詢場景，配合覆蓋索引、ICP、統計信息維護，解決絕多數性能問題。