SQL 語句性能調優核心原則：WHERE 子句寫法決定索引成敗

系統上線初期數據量小，SQL 寫法優劣難見真章；隨數據增長，劣質 SQL 與優質 SQL 速度可相差百倍。本文深度剖析 WHERE 子句六大常見反模式，揭示導致優化器放棄索引、退化為全表掃描的根本原因，並給出改寫模板。

反模式一：IS NULL / IS NOT NULL 導致索引失效

原理：B+ 樹索引默認不存儲 NULL 值（Oracle 明確不存，MySQL InnoDB 單列索引不存，聯合索引全 NULL 不存）。任何包含 NULL 的列，該行不在索引中。

後果：WHERE col IS NULL 或 IS NOT NULL 必須全表掃描。

改寫策略：

• 業務層面：避免 NULL，用默認值（空字符串、0、'1900-01-01'）替代，並加 NOT NULL 約束。
• 查詢層面：若必須查 NULL，建立函數索引（Oracle: CREATE INDEX idx_col_nn ON t(NVL(col,'NULL'));，PostgreSQL: CREATE INDEX ON t((col IS NULL))），查詢改用函數表達式。

反模式二：列連接/運算導致索引失效

案例：員工表 First_Name、Last_Name 分列存儲，查詢「Bill Clinton」。

-- ❌ 錯誤：連接列，索引失效
SELECT * FROM Employee WHERE First_Name || ' ' || Last_Name = 'Bill Clinton';

-- ✅ 正確：拆分條件，各列獨立使用索引
SELECT * FROM Employee WHERE First_Name = 'Bill' AND Last_Name = 'Clinton';

變量場景：應用層拿到完整姓名變量 @FullName，不可在 SQL 中拆分（函數作用於列會失效），應在應用層拆分後傳兩個參數，或用覆蓋索引包含兩列讓優化器選擇 Index Skip Scan（Oracle 12c+）。

反模式三：LIKE 通配符前置導致全表掃描

-- ❌ 通配符開頭，無法用索引
SELECT * FROM Employee WHERE Last_Name LIKE '%Clinton%';

-- ✅ 通配符結尾，可用索引範圍掃描
SELECT * FROM Employee WHERE Last_Name LIKE 'Clinton%';

原理：B+ 樹索引按字符順序存儲，前綴固定才能定位範圍；前置通配符破壞前綴有序性，只能掃描全索引/全表。

替代方案：全文檢索（CONTAINS/MATCH AGAINST）、倒排索引、Elasticsearch 異構同步。

反模式四：ORDER BY 非索引列/表達式導致 Filesort

-- ❌ 排序列無索引或含表達式
SELECT * FROM Orders ORDER BY YEAR(OrderDate) DESC, Amount * 1.1;

-- ✅ 方案一：建立組合索引匹配排序
CREATE INDEX idx_od_ymd_amt ON Orders (YEAR(OrderDate), Amount);  -- 需函數索引支持

-- ✅ 方案二：應用層排序（小結果集）
SELECT * FROM Orders WHERE ... ORDER BY OrderDate DESC LIMIT 100;  -- 利用 OrderDate 索引

原則：ORDER BY 列應與索引順序一致（或反向），避免臨時表排序（Using filesort）。

反模式五：NOT / <> / != 導致索引放棄

-- ❌ 否定條件，優化器傾向全表掃描
SELECT * FROM Employee WHERE Salary <> 3000;

-- ✅ 改寫為正向範圍 OR，可用索引範圍掃描
SELECT * FROM Employee WHERE Salary < 3000 OR Salary > 3000;

原因：否定條件選擇率極高（接近 100%），優化器判斷全表掃描成本更低。改寫為兩個範圍 OR 可各自利用索引 RANGE SCAN 再 UNION。

反模式六：IN 子查詢 vs EXISTS 子查詢性能鴻溝

-- ❌ IN 子查詢：先執行子查詢物化臨時表，再驅動主查詢
SELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country='USA');

-- ✅ EXISTS 相關子查詢：主查詢驅動，逐行探測子查詢，命中即停止
SELECT * FROM Orders o WHERE EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerID=o.CustomerID AND c.Country='USA');

否定形式同理：NOT EXISTS 遠優於 NOT IN（後者子查詢有 NULL 直接導致全表無結果）。

總結：WHERE 子句寫法黃金法則

1. 列不參與運算：函數、連接、隱式轉換全寫在常量/參數側。
2. 前綴固定：LIKE 'prefix%' 用索引，LIKE '%suffix' 走全文/倒排。
3. 正向範圍：用 BETWEEN、>/<、IN 替代 NOT IN/<>。
4. EXISTS 驅動：相關子查詢優先，IN 子查詢僅用於小靜態集合。
5. 排序匹配索引：ORDER BY 列順序 = 索引列順序（或反向）。
6. 統計信息新鮮：ANALYZE/DBMS_STATS 定期收集，優化器決策才準確。

掌握這六大反模式與改寫模板，覆蓋 80% 以上由 SQL 寫法導致的索引失效問題。剩餘 20% 需結合執行計劃（EXPLAIN/DBMS_XPLAN）、數據分佈、鎖競爭、硬件資源綜合診斷。

1. 上一篇文章： SQL Server 刪除完全重複記錄保留首條：SELECT INTO 臨時表去重法
2. 下一篇文章： MySQL 大數據量導入導出方案：繞過 phpMyAdmin 限制的分離式腳本實現

MySQL 資料庫相關知識

SQL Server 2005 Express 匯入 Access 資料庫兩種方法：OPENROWSET 與升遷精靈

SQL Server 2005 Express 無 Mana

MySQL MEM_ROOT 內存分配機制深度解析：源碼級原理與啟發式算法

MEM_ROOT 核心定位 MEM_ROOT 是 MySQL

Mac MySQL 中文亂碼徹底解決：從配置文件到驗證的完整流程

一、 問題現象與根因 Java SSH 框架已配置 Char

Ubuntu 14.04 環境 MySQL 安裝配置實戰筆記

環境資訊 系統：Ubuntu 14.04 版本：mysql