MySQL SQL 語句優化總結（一）：連接類型選擇、索引設計、分頁優化、避免全表掃描的 15 條實戰原則

MySQL SQL 優化 15 條實戰原則（上）

1. 連接類型選擇

• 優先 INNER JOIN：等值連接，返回行少，性能最好。
• 次選 LEFT/RIGHT JOIN：外連接，驅動表結果集小的放左邊（LEFT JOIN）或右邊（RIGHT JOIN），條件下推到驅動表。
• 避免子查詢：EXISTS/SUBQUERY 往往比外連接慢，用 INNER JOIN 改寫。
  例：SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id=B.id) → SELECT * FROM A INNER JOIN B USING(id)。

2. ON vs WHERE 順序

LEFT JOIN ... ON 條件 WHERE 條件：先執行 ON 過濾右表，再 WHERE 過濾連接結果。ON 不影響左表行數，WHERE 影響最終結果。

3. 小表驅動大表

編寫 JOIN 時，結果集小的表放驅動位置（LEFT JOIN 左、RIGHT JOIN 右），並盡量拆分多表連接為多條簡單查詢，減少鎖競爭。

4. 索引設計原則

• 複合索引最左前綴：WHERE 用到的列放索引最左側。
• 關聯列類型一致：JOIN 列類型、長度、字符集必須一致，否則索引失效。
• LIKE 只有後綴 % 才用索引：name LIKE 'abc%' 用索引，LIKE '%abc' 全表掃描。
• 複合索引 = 多個索引：INDEX(a,b,c) 等價於 (a,b,c)、(a,b)、(a) 三個索引（最佳左前綴）。
• NULL 列不入索引：含 NULL 的列不會被索引（唯一鍵除外），設計時設 NOT NULL DEFAULT ''。
• 短索引：長字符串列只索引前綴（如 INDEX(name(10))），節省空間提速。
• 排序列若 WHERE 已用索引則無法再用索引排序：需建複合索引覆蓋 WHERE+ORDER BY。

5. 千萬級分頁優化

• LIMIT 10000000,10 極慢（需掃描前 1000 萬行）。
• 優化：WHERE id >= (SELECT id FROM t LIMIT 10000000,1) LIMIT 10（需 id 索）。
• 更優：WHERE id BETWEEN 10000000 AND 10000010（連續 ID 範圍）。

6. 避免 SELECT *

只查需要的列，減少磁盤 I/O、網絡傳輸、內存佔用。

7. 避免 ORDER BY RAND()

全表掃描+排序極慢。替代：應用層隨機 ID、或 WHERE id >= (SELECT FLOOR(RAND()*MAX(id)) FROM t) LIMIT 1。

8. 單行查詢用 LIMIT 1

SELECT * FROM t WHERE name LIKE '%xxx' LIMIT 1 命中即停止掃描。

9. 減少排序

排序消耗 CPU，緩存命中率高時可接受，否則優化索引或應用層排序。

10. 少用 OR

MySQL 優化器處理 OR 差，改用 UNION ALL（無去重）或 UNION（去重）。

11. UNION ALL 代替 UNION

無去重需求時 UNION ALL 避免排序去重開銷。

12. 避免隱式類型轉換

WHERE 列與參數類型不一致導致列轉換 → 索引失效。轉換應在參數端：WHERE col = CAST(? AS INT)。

13. 列上不做運算

WHERE YEAR(dt) < 2023 → 全表掃描。改為 WHERE dt < '2023-01-01'。

14. 避免 NOT IN / !=

不走索引。NOT IN → NOT EXISTS 或 LEFT JOIN ... WHERE IS NULL。id != 3 → id > 3 OR id < 3。

15. 批量插入 / 存儲過程

INSERT INTO t(a,b) VALUES (1,2),(3,4),(5,6) 減少交互。大批量用 LOAD DATA INFILE 或存儲過程。