MySQL 查詢與更新性能優化全攻略

MySQL 查詢與更新性能優化全攻略

MySQL 的查詢與更新速度直接決定了應用程式的響應能力。本文系統梳理影響效能的關鍵因素，並提供從權限檢查、索引設計、查詢優化到具體操作（SELECT、INSERT、UPDATE、DELETE）的調優實戰指南。

一、權限系統對效能的影響

MySQL 的權限檢查會帶來額外開銷。權限系統越複雜（GRANT 語句越多），每次查詢的權限驗證成本越高。若應用不需要精細權限控制，可避免使用 GRANT，或簡化權限結構，以減少每次查詢的權限檢查開銷。

二、使用 BENCHMARK 測量函數效能

內建函數 BENCHMARK(loop_count, expression) 可快速評估表達式執行效率。例如在 Pentium II 400MHz 上執行 1,000,000 次 1+1 僅耗時 0.32 秒。所有內建函數均經高度優化，但仍可能存在例外，遇到疑似函數瓶頸時可用此工具驗證。

三、估算查詢效能：磁盤尋道模型

對於小表，索引通常被緩衝，僅需 1 次磁盤尋道即可定位行。大表使用 B+ 樹索引時，尋道次數估算公式：

log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

以 50 萬行、索引長度 3 字節（MEDIUMINT）、索引塊 1024 字節、資料指標 4 字節為例，約需 4 次尋道。索引大小約 5.2 MB，若索引緩衝區命中率高（典型 2/3 滿），實際可能僅需 1-2 次系統調用即可讀取資料。

寫入操作需額外尋道：約 4 次尋道定位新索引位置，加 2 次尋道更新索引並寫入行。隨資料增長，只要作業系統或 MySQL 緩衝池能容納熱點資料，效能衰減緩慢；一旦超出緩衝容量，受磁盤尋道限制，效能將呈 N log N 級下降。解決之道是隨資料增長同步擴大索引緩衝區（參見 key_buffer_size 調整）。

四、SELECT 查詢加速核心策略

1. 加索引：慢查詢優化首選檢查是否可加索引。所有表關聯欄位應建立索引。用 EXPLAIN 分析實際使用的索引。
2. 執行 myisamchk --analyze：載入資料後執行，更新索引統計資訊，助優化器做出更好決策。
3. 排序索引與資料：myisamchk --sort-index --sort-records=1 可按索引順序排列資料，加速按索引順序讀取。注意大表排序耗時極長。

五、WHERE 子句優化機制

MySQL 對 WHERE 條件執行多層優化，同樣適用於 DELETE 和 UPDATE：

• 移除多餘括號：((a AND b) AND c OR (((a AND b) AND (c AND d)))) → (a AND b AND c) OR (a AND b AND c AND d)
• 常數摺疊：(a<b AND b=c) AND a=5 → b>5 AND b=c AND a=5
• 移除恆真/恆假條件：(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) → B=5 OR B=6
• 索引用到的常數表達式僅計算一次
• 單表無 WHERE 的 COUNT(*) 直接從表元資料讀取
• 早期偵測不可能的常數條件，直接回傳空結果
• 無 GROUP BY 時 HAVING 併入 WHERE
• 對每個子聯結構造更簡單的 WHERE，加速計算並盡早過濾行

常數表優化

下列情況視為常數表（優先讀取、僅 1 行）：

• 空表或 1 行表
• 對 PRIMARY KEY 或 UNIQUE 索引所有部分使用常數條件且定義為 NOT NULL
• 透過 PRIMARY KEY 關聯的多表聯結

索引選擇與掃描策略

• 優先嘗試所有聯結組合找出最佳順序；若 ORDER BY 和 GROUP BY 列來自同一表，該表優先
• 若 ORDER BY/GROUP BY 包含非第一張表的列，或兩者不一致，建立臨時表
• SQL_SMALL_RESULT 使用記憶體臨時表
• DISTINCT 轉為 GROUP BY，配合 ORDER BY 常需臨時表
• 各表索引被查詢，若無覆蓋 <30% 行的索引，改用全表掃描
• 若查詢僅涉及索引列且為數值型，直接從索引樹讀取（覆蓋索引）
• 輸出前過濾不符合 HAVING 的行

極速查詢範例

SELECT COUNT(*) FROM tbl_name;
SELECT MIN(key_part1), MAX(key_part1) FROM tbl_name;
SELECT MAX(key_part2) FROM tbl_name WHERE key_part_1=constant;
SELECT ... FROM tbl_name ORDER BY key_part1, key_part2 LIMIT 10;
SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC LIMIT 10;

六、LEFT JOIN 優化邏輯

1. 表 B 設為依賴表 A
2. 表 A 依賴 LEFT JOIN 條件中除 B 外的所有表
3. 所有 LEFT JOIN 條件移入 WHERE 子句
4. 執行標準聯結優化，但依賴表必在被依賴表之後讀取；循環依賴報錯
5. 執行標準 WHERE 優化
6. 若 A 有行匹配 WHERE 但 B 無匹配 LEFT JOIN 條件，生成全 NULL 行
7. 若 WHERE 中有 column_name IS NULL 且該列定義為 NOT NULL，找到首匹配行後停止該鍵組合的進一步搜尋

七、LIMIT 子句優化行為

• 無 HAVING 時 LIMIT # 可能改用索引而非全表掃描
• LIMIT # ORDER BY：找到前 # 行即停止排序
• LIMIT # DISTINCT：找到 # 個唯一行即停止
• 某些 GROUP BY 可透過索引順序讀取完成，LIMIT # 避免不必要分組計算
• 發送完前 # 行給客戶端即放棄查詢
• LIMIT 0 快速回傳空集合，適合檢查欄位類型
• 臨時表大小由 LIMIT # 決定

八、INSERT 效能優化

單筆插入耗時組成：連接(3) + 發送查詢(2) + 解析(2) + 插入記錄(1×大小) + 插入索引(1×索引數) + 關閉(1)。表大小以 N log N 減慢索引插入。

加速插入實戰技巧

1. 批次多值 INSERT：單語句插入多行，比多條 INSERT 快數倍
2. INSERT DELAYED：多客戶端並發寫入時提高吞吐
3. MyISAM 並發插入：無刪除空洞時，SELECT 期間可並發 INSERT
4. LOAD DATA INFILE：導入文字檔比多條 INSERT 快 20 倍
5. 大量載入索引優化流程：
   1. 建表（不建索引）
   2. FLUSH TABLES
   3. myisamchk --keys-used=0 -rq 禁用索引
   4. LOAD DATA INFILE 載入資料（不更新索引）
   5. 需壓縮則執行 myisampack
   6. myisamchk -r -q 重建索引（記憶體中構建平衡樹，避免大量尋道）
   7. FLUSH TABLES
6. 鎖表批次寫入：LOCK TABLES a WRITE → 多條 INSERT → UNLOCK TABLES，索引緩衝區僅刷盤一次。單條大 INSERT 無需鎖表。鎖表降低整體耗時約 40%，但增加個別線程等待時間。建議每 1000 行 UNLOCK TABLES 釋放並發。
7. 擴大 key_buffer_size 提升 LOAD DATA INFILE 與 INSERT 速度

九、UPDATE 效能優化

UPDATE 被優化為帶寫入開銷的 SELECT。寫入速度取決於更新資料量與被更新索引數。延遲更新、批次鎖表執行比逐行單獨更新更快。動態記錄格式下頻繁更新長記錄可能導致記錄分裂，需定期 OPTIMIZE TABLE 整理。

十、DELETE 效能優化

刪除耗時與索引數成正比。增大索引緩衝區可加速刪除。TRUNCATE TABLE 或 DELETE FROM table（無 WHERE）清空全表比大量逐行刪除快得多。