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

MySQL 調度優先級與鎖定層級完整指南

前篇聚焦單一查詢加速，本文探討多客戶端並發下的調度策略、鎖定機制與儲存引擎差異。為便於說明，執行 SELECT 的客戶端稱為「讀取者」，執行 DELETE/INSERT/REPLACE/UPDATE 的稱為「寫入者」。

MySQL 預設調度策略

• 寫入優先於讀取：寫入操作優先級高於讀取。
• 寫入串行化：同一張表同一時間只能有一個寫入，按到達順序處理。
• 讀取並發：多個讀取操作可同時進行。

MyISAM 和 MEMORY 引擎透過表鎖實現此策略。客戶端存取表前必須獲得鎖，操作完成後釋放。通常由伺服器鎖管理器自動處理，也可用 LOCK TABLES/UNLOCK TABLES 顯式控制。

鎖的類型

• 寫入鎖 (排他鎖)：寫入期間表處於不一致狀態，禁止其他讀寫。
• 讀取鎖 (共享鎖)：防止寫入，但允許其他讀取者並發讀取。

調度修飾符

LOW_PRIORITY 寫入

降低寫入優先級，允許等待期間到達的讀取者插隊。若持續有讀取請求，寫入可能長期被阻塞 (飢餓風險)。啟動參數 --low-priority-updates 可令所有支援的語句預設低優先級。

HIGH_PRIORITY 讀取

允許 SELECT 插入等待中的寫入操作之前，也優先於普通 SELECT。

DELAYED 插入

服務器將資料行放入佇列，立即返回狀態給客戶端，客戶端可繼續執行。無讀取者時批量寫入佇列資料；有新讀取請求則掛起寫入。關鍵差異： LOW_PRIORITY 阻塞客戶端；DELAYED 讓客戶端立即繼續，資料在記憶體緩衝。

風險： 伺服器崩潰或 kill -9 導致佇列資料遺失；無法獲取 AUTO_INCREMENT 值與唯一索引衝突資訊。

並發插入 (Concurrent Inserts)

MyISAM 表無「空洞」(無刪除/更新產生的空洞) 時，允許讀取與末尾插入並發。注意事項：

• 勿使用 LOW_PRIORITY，會阻塞並發插入。
• 顯式鎖表用 LOCK TABLES ... READ LOCAL，LOCAL 允許末尾插入。
• LOAD DATA 加 CONCURRENT 修飾符。
• 有空洞時無法並發插入，可用 OPTIMIZE TABLE 整理碎片。

鎖層級與並發性對比

原則： 鎖粒度越細，並發性越好。但細粒度鎖易產生死鎖，表鎖天然免疫死鎖。InnoDB/BDB 在事務過程中動態獲取鎖，可能發生死鎖，需服務器終止某事務解決。