MySQL 查詢優化 20 大實戰技巧：從索引設計到 SQL 改寫的完整避坑指南

MySQL 查詢優化 20 大實戰技巧

優化評估指標

• 執行時間
• 檢查行數 (Rows_examined)
• 返回行數 (Rows_sent)

索引建立原則

1. 合理索引加速讀取，不合理拖慢寫入
2. 索引越多，UPDATE/INSERT/DELETE 越慢
3. MyISAM (B-Tree) 比 InnoDB 更適合純讀場景，但不支援事務
4. SQL/結構優化到極致仍有瓶頸 → 引入 Redis/Memcached 分散式快取
5. 養成 EXPLAIN 分析習慣

20 大優化技巧

1. COUNT 優化

大偏移量 COUNT 可用「總行數 - 小偏移量 COUNT」反向查詢。無 WHERE 時 COUNT(*) 直接讀取表統計資訊最快。

2. 避免不相容資料類型

float/int、char/varchar、binary/varbinary 混用會阻礙優化器優化。

3. 索引欄位上避免運算

-- 差：WHERE F1/2=100 (全表掃描)
-- 優：WHERE F1=100*2 (走索引)

4. 慎用 !=、<>、IS NULL、IN、NOT IN

導致無法使用索引。IN 可用 EXISTS 替代。

5. 優先使用數字型欄位

字串比較需逐字元，數字一次比較，效能差異顯著。

6. 善用 EXISTS/NOT EXISTS

-- 差：WHERE (SELECT COUNT(*) FROM T2 WHERE T2.C2=T1.C2) > 0
-- 優：WHERE EXISTS (SELECT * FROM T2 WHERE T2.C2=T1.C2)

存在性檢查用 EXISTS 而非 COUNT(*)。

7. 能用 BETWEEN 別用 IN

8. 能用 DISTINCT 別用 GROUP BY

9. 避免 SELECT INTO

會導致表鎖定阻塞其他連線。

10. 必要時強制指定索引

SELECT * FROM T1 (INDEX=IX_ProcessID) WHERE nextprocess=1 AND processid IN (8,32,45)

11. 消除大表順序存取

-- 差：WHERE (A=1 AND B>100) OR C=1008 (全表掃描)
-- 優：WHERE A=1 AND B>100 UNION SELECT * WHERE C=1008 (走索引)

12. 避免索引字串欄位非前綴搜尋

LIKE '%L%' / SUBSTRING(name,2,1)='L' → 全表掃描
LIKE 'L%' → 走索引

13. UPDATE/DELETE 最佳實踐

• 勿修改主鍵
• VARCHAR 修改保持長度相近
• 減少含觸發器表的 UPDATE
• 避免更新將被複制的欄位
• 避免更新高索引欄位
• 避免 WHERE 條件欄位被更新

14. 能用 UNION ALL 別用 UNION

UNION ALL 不排序去重，大幅節省資源。跨庫查詢尤為明顯。

15. 欄位型別優化

• 避免 NULL (需特殊處理、佔額外位元組) → 用 NOT NULL + 預設值 0/-1
• 越小越好 (少磁碟 IO、少記憶體) → 但預留擴充空間
• 優先定長型 (CHAR vs VARCHAR 視場景)

16. 大偏移量 LIMIT 優化

-- 差：LIMIT 50000, 5
-- 優：覆蓋索引偏移 + JOIN 回表
SELECT f.film_id, f.description
FROM sakila.film f
INNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50000, 5) t USING (film_id);

17. 批量 INSERT 合併單條

INSERT INTO person(name,age) VALUES ('xboy',14), ('xgirl',15), ('nia',19);

18. 索引放在 WHERE/ORDER BY 欄位，而非 SELECT 欄位

19. ORDER BY 優化

• ORDER BY col LIMIT N → 單欄索引 (col)
• WHERE x=1 ORDER BY col LIMIT N → 聯合索引 (x, col)
• WHERE x IN (...) ORDER BY col LIMIT N → 聯合索引效果有限，待優化
• WHERE uid=1 ORDER BY x,y LIMIT N → 最佳索引 (uid, x, y) 而非 (x, y, uid)

20. 持續監控與迭代

優化非一次性工作，建立慢查詢審核機制、定期 ANALYZE TABLE 更新統計資訊、關注新版本優化器改進。