MySQL 查詢優化系統性指南：索引、SQL 改寫、加載策略、硬件四大維度

查詢優化是數據庫應用系統成敗的關鍵。MySQL 雖速度優異，但面對複雜業務仍需系統性調優。本文從索引設計、SQL 改寫技巧、數據加載策略、硬件資源規劃四大維度，給出可落地的優化方法論與避坑原則。

一、索引設計：優化的基石與核心槓桿

索引為什麼快？

• B+ 樹結構：邏輯有序、範圍查詢只需掃描葉子節點鏈表。
• 定位終點：知道最後一條匹配行位置，後續無需掃描。
• 覆蓋索引：葉子節點直接包含查詢列，避免回表。

存儲引擎索引差異

InnoDB 主鍵越短，二級索引越小，緩存命中率越高 → 業務主鍵優先用自增整數/短業務碼。

索引創建黃金法則

1. 只在搜索、排序、分組、關聯條件列上建索引，僅出現在 SELECT 列表的列不建。
2. 優先唯一索引：區分度越高（基數越大），選擇性越好。
3. 盡量短的列：INT 優於 BIGINT，CHAR(10) 優於 VARCHAR(100)，前綴索引 col(10) 替代全長。
4. 聯合索引遵循最左前綴：INDEX (a,b,c) 支援 a、a,b、a,b,c，不支援 b、c、b,c。
5. 拒絕過度索引：每個索引增加寫入開銷（InnoDB 修改二級索引）、佔用空間、優化器評估成本。單表建議 ≤ 5 個索引。
6. HEAP/MEMORY 表只支援 = 比較，範圍查詢無法用索引。
7. 慢查詢日志 + pt-query-digest 定期審視，但注意：log_queries_not_using_indexes=ON 會記錄大量全表掃描，需人工篩選真慢查詢。
8. 列定義 NOT NULL：省 1 位標記位，優化器可跳過 IS NULL 判斷。
9. 低基數枚舉用 ENUM：內部存數值，比較按數值進行，速度遠快於字符串。
10. 定期 OPTIMIZE TABLE 整理碎片：VARCHAR 更新導致行分裂、碎片化，影響掃描效率。
11. 大字段（BLOB/TEXT）拆離或壓縮：單獨存文件/對象存儲，或應用層壓縮後存 VARBINARY。
12. 虛擬列/函數索引（MySQL 5.7+）：ALTER TABLE t ADD INDEX idx_crc32 ((CRC32(long_col)))，對長文本按哈希值索引。

二、SQL 改寫：助優化器做最優決策

1. 同類型比較：WHERE int_col = '123' 隱式轉換導致索引失效 → WHERE int_col = 123 或 WHERE str_col = '123'。
2. 索引列保持「乾淨」：WHERE YEAR(date_col) = 2023 → WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01'。
3. 慎用前導模糊匹配：LIKE '%abc%' 無法用索引 → 全文索引/Elasticsearch/應用層倒排。
4. 多試幾種寫法：IN vs EXISTS vs JOIN，EXPLAIN 對比 rows/type/Extra。
5. 拒絕隱式類型轉換：數字別寫成字符串，日期別寫成數字。
6. OR 改 UNION ALL：WHERE a=1 OR b=2 往往全表掃描 → SELECT * FROM t WHERE a=1 UNION ALL SELECT * FROM t WHERE b=2 AND a!=1（各走各索引）。
7. 分頁大偏移用游標：LIMIT 1000000, 20 → WHERE id > last_id ORDER BY id LIMIT 20。

三、數據加載：批量勝過單條，順序勝過隨機

1. LOAD DATA INFILE 碾壓 INSERT：單事務、禁用二級索引維護、順序寫入，速度提升 10-20 倍。
2. 必須用 INSERT 時：
   • 事務表：START TRANSACTION; INSERT ...; INSERT ...; COMMIT; 批量提交。
   • 非事務表：LOCK TABLES t WRITE; INSERT ...; INSERT ...; UNLOCK TABLES; 減少鎖開銷。
3. 大量數據先導後建索引：CREATE TABLE ...; LOAD DATA ...; CREATE INDEX ...; 避免導入過程中反覆維護 B+ 樹。
4. 按主鍵順序導入：InnoDB 聚簇索引順序寫入無分裂，亂序導入產生大量頁分裂、碎片。

四、硬件資源：物理基礎決定上限

1. 內存越大越好：innodb_buffer_pool_size 設物理內存 60-80%，熱數據全內存，I/O 降為 0。
2. 高速存儲：NVMe SSD / PCIe 4.0，隨機 IOPS 10萬+，innodb_io_capacity 對應調大。
3. 多物理磁碟隔離：數據、Redo Log、Binlog、Temp 分盤，並行 I/O 無干擾。
4. 多核 CPU：MySQL 5.7+ 多線程刷頁、並行查詢（innodb_parallel_read_threads），充分利用核心。
5. 網絡帶寬/延遲：主從同步、集群心跳、客戶端連接池，千兆起步、萬兆標配。

五、實戰優化閉環

1. 建立基線：核心業務 SQL 的 EXPLAIN、執行時間、掃描行數、返回行數。
2. 識別 Top N：慢查詢日志 + pt-query-digest 按「總耗時 = 平均耗時 × 執行次數」排序。
3. 單條優化：索引調整 → SQL 改寫 → 參數微調 → 硬件擴容，逐層驗證。
4. 回歸測試：準備環境跑全量業務 SQL 集合，對比基線，防副作用。
5. 持續監控：SHOW GLOBAL STATUS、Performance Schema、Sys Schema 可視化告警。

總結

查詢優化無銀彈，有體系：**索引是槓桿、SQL 是手術刀、加載是搬運工、硬件是地基**。按「零成本索引/改寫 → 低成本參數/加載 → 高成本硬件/架構」順序推進，每步有基線、有驗證、有回滾。將優化從「拍腦袋」變為「有數據、有邏輯、有閉環」的工程實踐，才是 DBA 與開發者的核心競爭力。