您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

撰寫高效能 SQL 完整指南：從 IS NULL 陷阱到 LIKE 萬用字元的 10 大索引殺手與破解之道

編輯：MySQL 資料庫相關知識

前言：優化器不聰明，寫法決定命運

多數資料庫優化器基於成本模型，依靠統計資訊與索引結構決策。WHERE 子句一旦寫錯，優化器被迫全表掃描。對 TB 級資料，差異是秒 vs 小時。

十大索引殺手深度解析

1. IS NULL / IS NOT NULL

B+ 樹索引預設不存 NULL（或存但難定位）。

-- ❌
WHERE col IS NULL
-- ✅ 方案 A：預設值 0/-1/'' 替代 NULL
WHERE col = 0
-- ✅ 方案 B：MySQL 8.0+ 函數索引
CREATE INDEX idx_col_null ON t ((col IS NULL));
WHERE col IS NULL  -- 此時可用索引

2. 欄位聯接/運算/函數

-- ❌ 破壞索引順序
WHERE first_name || ' ' || last_name = 'Bill Clinton'
WHERE YEAR(dt) = 2024
WHERE UPPER(email) = 'A@B.C'
WHERE price * 1.05 > 100

-- ✅ 常數側運算 / 範圍查詢
WHERE first_name = 'Bill' AND last_name = 'Clinton'
WHERE dt >= '2024-01-01' AND dt < '2025-01-01'
WHERE email = 'a@b.c'  -- ci 校對不區分大小寫
WHERE price > 100 / 1.05

3. LIKE 前導萬用字元

-- ❌
WHERE name LIKE '%keyword%'
WHERE name LIKE '%keyword'

-- ✅ 前綴匹配
WHERE name LIKE 'keyword%'

-- 中文/全文需求 → FULLTEXT (ngram) 或 Elasticsearch

4. OR 連接不同索引欄位

-- ❌ 優化器通常放棄索引
WHERE a = 1 OR b = 2

-- ✅ UNION ALL 改寫
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a != 1;
-- 或建立複合索引 (a,b) 覆蓋

5. 隱含型別轉換

-- ❌ VARCHAR 比數字
WHERE varchar_col = 123
WHERE char_col = 1

-- ✅ 字串字面值
WHERE varchar_col = '123'
WHERE char_col = '1'

6. NOT IN 子查詢（含 NULL 陷阱）

-- ❌ 若子查詢結果含 NULL，整體永遠為 FALSE/UNKNOWN
WHERE id NOT IN (SELECT id FROM other)

-- ✅ NOT EXISTS 或 LEFT JOIN
WHERE NOT EXISTS (SELECT 1 FROM other WHERE other.id = t.id)
-- 或
SELECT t.* FROM t LEFT JOIN other ON t.id = other.id WHERE other.id IS NULL;

7. 複合索引最左前綴違反

-- 索引 INDEX (a, b, c)
-- ❌
WHERE b = 1
WHERE c = 1
WHERE b = 1 AND c = 2

-- ✅
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

8. 範圍查詢後續欄位無法用索引

-- 索引 INDEX (a, b, c)
-- ❌ b 為範圍，c 無法用索引
WHERE a = 1 AND b > 10 AND c = 3

-- ✅ 調整索引順序或拆分查詢
-- 索引 (a, c, b) 則 c 可用

9. 隱含字元集校對轉換

-- ❌ 連線 utf8mb4，欄位 latin1 → 隱含轉換
WHERE latin1_col = '中文'

-- ✅ 統一字元集或明確轉換
WHERE latin1_col = CONVERT('中文' USING latin1)
-- 根治：ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4

10. SELECT * 導致回表/覆蓋索引失效

-- ❌ 需回表取所有欄位
SELECT * FROM t WHERE indexed_col = 1

-- ✅ 僅查索引欄位（覆蓋索引）
SELECT indexed_col, other_indexed_col FROM t WHERE indexed_col = 1
-- 或評估是否真正需要所有欄位