MySQL 查詢優化：調度策略與鎖定機制深度解析

資料庫管理員常需優化單一查詢速度。MySQL 允許調整語句調度優先權，使多客戶端查詢更好協作，避免單一客戶端因鎖定長時間等待。本文解析預設調度策略、優先權調節符、延遲插入、並發插入與儲存引擎鎖定層級對並發影響。

核心概念定義

• 讀取者：執行 SELECT 的客戶端
• 寫入者：執行 DELETE、INSERT、REPLACE、UPDATE 的客戶端

MySQL 預設調度策略

1. 寫入操作優先於讀取操作
2. 同一資料表寫入操作同一時間只能發生一次，按到達順序處理
3. 同一資料表多個讀取操作可同時進行

MyISAM 與 MEMORY 引擎透過資料表鎖實現此策略。客戶端存取資料表前必須取得鎖，操作完成釋放。鎖類型取決於讀寫操作：

• 寫入需獨佔鎖：資料表處於不一致狀態，不允許其他讀寫
• 讀取需共享鎖：允許其他讀取者同時存取，但阻擋寫入者

語句調節符修改調度優先權

LOW_PRIORITY

適用 DELETE、INSERT、LOAD DATA、REPLACE、UPDATE。降低寫入優先權，允許等待期間新到達的讀取者優先執行。理論上可能導致寫入永遠被阻塞（飢餓）。

HIGH_PRIORITY

適用 SELECT、INSERT。允許 SELECT 插隊等待中的寫入操作，也優先於普通 SELECT。

啟動參數 --low-priority-updates 可讓所有支援的語句預設低優先權，單條 INSERT HIGH_PRIORITY 可覆蓋。

DELAYED

適用 INSERT、REPLACE。資料行放入隊列，立即返回狀態讓客戶端繼續。無讀取者時批次寫入，有讀取者則掛起隊列。與 LOW_PRIORITY 關鍵差異：不阻塞客戶端。

代價：無法取得 AUTO_INCREMENT 值、唯一索引重複計數；伺服器異常關機可能丟失隊列資料（kill -TERM 會先寫入）。

適用場景建議

• 長時間 SELECT 且不想阻塞 → INSERT DELAYED
• 需確保寫入最終完成且可接受等待 → LOW_PRIORITY
• 關鍵查詢必須優先 → HIGH_PRIORITY