MySQL 查詢優化 20 大實戰技巧：索引、SQL 改寫、分頁、大數據應對

優化評估指標

• 執行時間
• 檢查行數
• 返回行數

建立索引原則

1. 合理索引加速讀取，不合理索引拖慢寫入。
2. 索引越多，UPDATE/INSERT/DELETE 越慢。
3. MyISAM（B-Tree 索引）較 InnoDB 適合大量索引場景，但不支援交易。
4. SQL/架構優化無效時，考慮引入 Memcached 等分散式快取。
5. 習慣用 EXPLAIN 分析 SQL 效能。

20 大優化技巧

1. COUNT 優化

-- 當 WHERE 篩選後行數較少時，用總數減去反向篩選更快
SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE id <= 5;

2. 避免不相容資料型別

float vs int、char vs varchar、binary vs varbinary 不相容會阻礙優化器。

3. 索引欄位勿做運算

-- 錯誤：導致全表掃描
WHERE F1/2 = 100
-- 正確：
WHERE F1 = 100*2

4. 避開 !=、<>、IS NULL、IS NOT NULL、IN、NOT IN

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

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)

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. 消除大表順序存取

-- 原查詢強迫順序掃描
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
-- 改用 UNION 走索引路徑
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008

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

WHERE name LIKE '%L%'   -- 全表掃描
WHERE name LIKE 'L%'    -- 可用索引

13. UPDATE 最佳實踐

• 勿修改主鍵。
• VARCHAR 修改時保持同長度。
• 最小化含觸發器表的 UPDATE。
• 避免 UPDATE 將複寫到其他庫的欄位。
• 避免 UPDATE 索引密集欄位。
• 避免 UPDATE WHERE 條件欄位。

14. UNION ALL 替代 UNION

UNION ALL 不執行 DISTINCT 排序，大幅降低資源消耗。已知無重複或不在意重複時首選。

15. 欄位型別優化

• 避免 NULL（需特殊處理、索引邏輯複雜），預設用 NOT NULL 或特殊值（0、-1）。
• 越小型別越好（磁碟 I/O、記憶體、CPU 周期），但留擴充空間避免重構。
• 優先定長型別。

16. 大偏移量 LIMIT 優化

-- 低效
SELECT film_id, description FROM film ORDER BY title LIMIT 50, 5;
-- 高效：覆蓋索引偏移後再 JOIN 回表
SELECT f.film_id, f.description
FROM film f
INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50, 5) tmp 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 索引優化模式

20. 持續監控與迭代

優化無終點，配合 EXPLAIN、慢查詢日誌、performance_schema 持續觀測調整。