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

MySQL 基本調度策略

MySQL 採用基於優先級的調度機制，核心規則如下：

1. 寫請求按到達順序串行處理：先到先得，形成寫隊列。
2. 寫操作優先級高於讀操作：有寫請求時，新到達的讀請求將等待，直至寫隊列清空。

此策略確保數據修改的及時性，但可能導致高併發讀場景下的「讀饑餓」現象。

表級鎖機制

MyISAM/ISAM 鎖特性

• 讀鎖（共享鎖）：多個客戶可同時持有，允許併發讀，阻塞寫。
• 寫鎖（排他鎖）：獨佔表，阻塞所有其他讀/寫請求。
• 鎖由服務器自動管理，也可通過 LOCK TABLES/UNLOCK TABLES 手動控制。

併發插入

MyISAM 支持「併發插入」：若表中無刪除操作或已執行 OPTIMIZE TABLE 整理碎片，新行僅追加至表尾，此時允許讀操作併發進行。

使用條件：

• 不使用 LOW_PRIORITY 修飾符。
• 讀操作使用 LOCK TABLES ... READ LOCAL（LOCAL 關鍵字僅鎖定現有行，不阻塞尾部追加）。

修飾符影響調度行為

DELAYED 適用場景：日志寫入、非即時統計聚合等可容忍延遲的批量寫入。

行級鎖與頁級鎖：InnoDB 與 BDB

選型建議

• 讀多寫少、以查詢為主：MyISAM 仍有優勢（全表掃描快、壓縮表支持）。
• 高併發寫入、事務處理：首選 InnoDB，行級鎖極大減少衝突。
• 避免死鎖：應用層統一按固定順序訪問表/行；保持事務簡短；設置 innodb_lock_wait_timeout。