MySQL 執行效率優化核心要點全解析：SQL 改寫、索引設計、架構規劃

核心原則

SQL 優化的本質：在最短時間內以最少的塊言問實現最大數據吞吐量。優化切入點：劣質 SQL 改寫、子查詢轉連接、索引覆蓋、優化器特性利用。

一、SQL 撰寫與改寫規範

二、複合索引設計鐵律

• 最左前綴原則： 索引定義順序 (F1, F2, F3)，查詢必須包含 F1，可為 F1、F1,F2、F1,F2,F3，否則索引失效。
• 順序一致性： WHERE/ORDER BY 中欄位順序與索引定義順序一致。
• 覆蓋索引： 查詢列全在索引中，實現「只掃索引不回表」。

三、多表關聯查詢標準寫法

SELECT SUM(t1.je)
FROM table1 t1, table2 t2, table3 t3
WHERE (t1.等值條件)
  AND (t1.非等值條件)
  AND (t2 關聯 t1 條件)
  AND (t2.等值條件)
  AND (t2.非等值條件)
  AND (t3 關聯 t2 條件)
  AND (t3.等值條件)
  AND (t3.非等值條件);

原則：先等值後非等值，先本表後關聯表，有利於優化器構建最優連接順序與索引利用。

四、子查詢轉連接/視圖

反例： SELECT name FROM customer WHERE customer_id IN (SELECT customer_id FROM orders WHERE money > 1000)
正例： SELECT c.name FROM customer c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE o.money > 1000

連接可利用索引、避免臨時表、支援更多優化策略。

五、WHERE 子句禁忌

• 嚴禁對列進行運算/函數處理（如 WHERE SUBSTRING(col,1,3)='abc' → WHERE col LIKE 'abc%'）。
• NOT IN/IN 優先考慮 NOT EXISTS/EXISTS 或 LEFT JOIN ... IS NULL。

六、事務與 DML 效率

1. 同一事務內對同表多個 INSERT 集中執行（批次提交）。
2. 業務流程中讀操作前置，寫操作後置，縮短鎖持有時間，降低死鎖概率。

七、物理存儲規劃（以 Oracle 為例，MySQL 同理）

• 表與索引分離： 不同 Tablespace，減少 I/O 爭用。
• Undo/Rollback 分離： 獨立 Tablespace。
• System Tablespace 純淨： 禁止放用戶對象。
• Temp Tablespace 獨立： 每用戶指定預設臨時表空間。
• 碎片管理： 高 DML 表與靜態表分離；定期 OPTIMIZE TABLE 或重建。
• 熱表分離： 經常 JOIN 的表放不同磁碟/表空間；大表分區。
• RAID 與日誌專盤： Redo/Undo 日誌放獨立高速磁碟（RAID 1/10）。

總結

MySQL 效能優化是系統工程，需從 SQL 寫法、索引設計、查詢結構、事務模式、物理架構全鏈路協同。建議建立「慢查詢審核 → EXPLAIN 分析 → 索引調整 → 壓測驗證」閉環機制，持續迭代。