MySQL 效能優化實戰：SQL 寫法、索引策略與常見效能陷阱避坑指南

問題背景

開發初期資料量小，SQL 寫法優劣不明顯；上線後資料增長，劣質 SQL 與優質 SQL 差距可達百倍。優化核心在於讓優化器用上索引，避免全表掃描。

WHERE 子句寫法禁忌與修正

1. IS NULL / IS NOT NULL 無法使用索引

-- 無法用索引
SELECT * FROM users WHERE email IS NULL;

-- 改法：預設值 + 索引
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL DEFAULT '';
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = '';

2. 欄位運算/函數導致索引失效

-- 索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE CONCAT(first_name, last_name) = 'JohnDoe';

-- 改法：範圍查詢 / 拆分欄位
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

3. 隱式類型轉換

-- varchar 欄位對比數字，索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 改法：統一型別
SELECT * FROM users WHERE phone = '13800138000';

4. LIKE 以萬用字元開頭

-- 全表掃描
SELECT * FROM products WHERE name LIKE '%phone%';

-- 改法：全文索引或前綴匹配
ALTER TABLE products ADD FULLTEXT(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

5. OR 條件跨欄位導致全表掃描

-- 除非每個 OR 條件欄位都有索引，否則全掃
SELECT * FROM orders WHERE user_id = 1 OR order_no = 'A123';

-- 改法：UNION ALL 分開查詢再合併
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE order_no = 'A123';

索引設計黃金法則

1. 最左前綴原則：複合索引 (a,b,c) 支援 a、a,b、a,b,c 查詢；
2. 高選擇性優先：基數大（唯一值多）的欄位放前面；
3. 覆蓋索引：SELECT a,b FROM t WHERE c=1 配合索引 (c,a,b) 避免回表；
4. 避免過度索引：每張表 5 個以內，寫入效能與索引數成反比。

EXPLAIN 實戰分析

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

重點欄位：

• type：ALL(全掃) → index → range → ref → eq_ref → const → system（由差到好）；
• key：實際使用的索引；
• rows：預估掃描行數；
• Extra：Using where、Using index(覆蓋)、Using temporary、Using filesort（需優化）。

大表優化進階技巧

• 分頁優化：LIMIT 100000, 20 → 子查詢 WHERE id > (SELECT id FROM t ORDER BY id LIMIT 100000, 1) LIMIT 20；
• 計數優化：COUNT(*) 無條件用 MyISAM 直接讀統計資訊；InnoDB 需掃描，改用快取或觸發器維護計數表；
• 批次操作：大量 INSERT/UPDATE 分批 1000 行/批，避免長鎖表與 Binlog 膨脹；
• 唯讀副本：報表查詢導向 Slave，主庫專職寫入。