MySQL MyISAM 讀寫互斥鎖優化：犧牲實時性換取穩定性

問題背景

MyISAM 讀寫串行：寫操作獨佔鎖，讀操作共享鎖。大量併發讀寫時，寫鎖阻塞所有讀，導致查詢堆積、連接耗盡、服務雪崩。典型場景：日志表、統計表、高寫入低一致性要求業務。

MyISAM 鎖機制核心參數

1. concurrent_insert：控制併發插入行為

推薦：concurrent_insert = 2，定期 OPTIMIZE TABLE 整理碎片。

2. max_write_lock_count：寫鎖閾值讓位讀請求

[mysqld]
max_write_lock_count = 1

效果：每處理 1 個寫鎖請求後，暫停寫鎖隊列，優先處理待讀請求。防止寫入風暴餓死讀查詢。

3. low-priority-updates：降級寫操作優先級

[mysqld]
low-priority-updates = 1

效果：所有 UPDATE/DELETE/INSERT 優先級低於 SELECT。讀請求不再被寫請求搶佔。

二選一：max_write_lock_count=1 更靈活（寫請求仍能執行，只是間歇讓位）；low-priority-updates=1 更徹底（寫請求完全後置）。

綜合優化配置模板

[mysqld]
# 1. 開啟併發插入（核心）
concurrent_insert = 2

# 2. 寫鎖讓位策略（二選一）
max_write_lock_count = 1
# 或
low-priority-updates = 1

# 3. 縮短鎖持有時間
# 查詢優化：加索引、避免全表掃描
# 寫入優化：批次提交、減少事務範圍

# 4. 定期碎片整理（配合 concurrent_insert=2）
# 可建立 cron 任務
# 0 3 * * * mysql -e "OPTIMIZE TABLE log_db.access_log, stats_db.daily_counter;"

效果驗證指標

-- 監控鎖等待
SHOW STATUS LIKE 'Table_locks%';
-- Table_locks_immediate: 立即獲得鎖
-- Table_locks_waited: 等待過鎖的次數（目標：趨近 0）

-- 監控線程狀態
SHOW PROCESSLIST;
-- 關注 State: "Waiting for table level lock" 的佔比

遷移建議：MyISAM → InnoDB

若業務允許，徹底解決鎖爭用的最佳路徑是遷移至 InnoDB：

• 行級鎖：寫不阻塞讀，MVCC 實現非阻塞一致性讀
• 事務支持：ACID 保障數據一致性
• 崩潰恢復：Redo/Undo log 自動恢復
• 遷移命令：ALTER TABLE t ENGINE=InnoDB;（大表用 pt-online-schema-change）

適用場景判斷樹

IF 業務要求強一致性/事務:
    MUST 遷移 InnoDB
ELSE IF 純追加寫入/日志/統計/可容忍最終一致性:
    MyISAM + concurrent_insert=2 + max_write_lock_count=1 可延續使用
ELSE:
    評估遷移成本 vs 鎖爭用風險，傾向 InnoDB

MyISAM 讀寫互斥是架構級限制，參數調優只能緩解症狀。concurrent_insert=2 配合 max_write_lock_count=1 是存量 MyISAM 高併發場景的最佳止血方案，長期規劃應走向 InnoDB。