MySQL 效能調優核心：SQL 寫法決定索引成敗

系統上線初期資料量小，SQL 寫法好壞差異不明顯；隨資料增長至百萬級，劣質 SQL 與優質 SQL 執行時間可相差 百倍以上。MySQL 優化器依賴索引加速查詢，但不當的 WHERE 子句寫法會導致優化器放棄索引、轉為全表掃描。本文針對六大常見反模式詳細解析與改寫示範。

1. IS NULL / IS NOT NULL 導致索引失效

原則：NULL 不存入 B+ 樹索引，任何包含 NULL 的複合索引鍵值該列皆不入索引。
後果：WHERE col IS NULL 或 IS NOT NULL 直接全表掃描。
對策：

• 設計階段設定 NOT NULL DEFAULT '' 或預設值。
• 查詢改用 WHERE col = '' 或 WHERE col <> '' (需配合索引)。

2. 欄位連接運算 (Concatenation) 破壞索引

-- 錯誤：連接後比較，last_name 索引失效
SELECT * FROM employee WHERE first_name || ' ' || last_name = 'Bill Clinton';

-- 正確：分別比較，可用 last_name 索引
SELECT * FROM employee WHERE first_name = 'Bill' AND last_name = 'Clinton';

-- 變數拆分技巧 (函數作用於變數端，非索引欄)
SELECT * FROM employee 
WHERE first_name = SUBSTR(@fullname, 1, INSTR(@fullname, ' ')-1)
  AND last_name = SUBSTR(@fullname, INSTR(@fullname, ' ')+1);

鐵律：索引欄不可參與運算、函數、隱含轉型，否則索引失效。

3. LIKE 通配符前置導致全表掃描

-- 失效：% 開頭無法利用索引
SELECT * FROM employee WHERE last_name LIKE '%clinton%';

-- 生效：固定前綴可用索引範圍掃描
SELECT * FROM employee WHERE last_name LIKE 'clin%';

若業務必須模糊前綴，考慮 全文索引 (FULLTEXT)、反向索引 或 Elasticsearch 等搜尋引擎。

4. ORDER BY 非索引欄或含運算式

ORDER BY 若非索引順序或包含運算 (col1+col2, UPPER(col))，MySQL 需額外 filesort 排序，大量資料極慢。
解法：

• 建立覆蓋索引 INDEX (sort_col, select_cols...)。
• 避免 ORDER BY rand()，改用應用層隨機抽樣。

5. NOT / <> / != 否定條件索引失效

-- 失效：無法使用 salary 索引
SELECT * FROM employee WHERE salary <> 3000;

-- 生效：改寫為範圍查詢，可用索引範圍掃描
SELECT * FROM employee WHERE salary < 3000 OR salary > 3000;

同理 NOT IN, NOT EXISTS 亦會阻礙索引，能用肯定條件改寫盡量改寫。

6. IN 子查詢效率遠低於 EXISTS

-- 低效：先執行子查詢建臨時表，再掃描主查詢
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip=1);

-- 高效：相關子查詢，主查詢驅動，命中即停止
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id=o.customer_id AND c.vip=1);

原理：EXISTS 只檢查是否存在，不回傳資料；IN 需物化子查詢結果集。否定場景同理：NOT EXISTS 優於 NOT IN (後者需處理 NULL 語義陷阱)。

實戰檢查清單

1. EXPLAIN SELECT ... 觀察 type (目標: ref/range/eq_ref)，key (實際用到的索引)，rows (預估掃描行數)。
2. 慢查詢日誌 (long_query_time=1) 定期分析 Top 10。
3. 覆蓋索引 (INDEX (a,b,c) 含 SELECT a,b,c) 避免回表。
4. 大表分頁優化：WHERE id > last_id LIMIT 20 替代 OFFSET 100000 LIMIT 20。