MyISAM 表鎖優化全攻略：縮短鎖時間、提升並發、讀寫分離

MyISAM 鎖特性回顧

• 表級鎖：粒度最大、開銷最小、無死鎖
• 讀鎖共享、寫鎖排他、讀寫互斥
• 預設寫優先：長查詢易導致寫入飢餓，大量寫入易阻塞查詢

策略一：縮短鎖持有時間 (核心)

1. 拆解複雜查詢：大 SQL 拆分為多個小步驟，減少單次鎖定時長
2. 建立高效索引：讓查詢快速定位，減少全表掃描鎖表時間
3. 精簡表結構：只存必要欄位，控制字段長度，減少 I/O
4. 定期優化表：OPTIMIZE TABLE 整理碎片，提升掃描效率

策略二：啟用並發插入

參數 concurrent_insert：

配合定期 OPTIMIZE TABLE 清理碎片，兼顧空間回收與並發效能。

策略三：調整讀寫優先級

• 讀密集系統：low_priority_updates=1 或啟動參數 --low-priority-updates，降低寫優先級
• 寫密集系統：保持預設 (寫優先)
• 會話級控制：SET LOW_PRIORITY_UPDATES=1 或單句 INSERT LOW_PRIORITY ...
• 平衡機制：max_write_lock_count=1，每處理一個寫鎖暫停讓讀鎖插隊

策略四：快取加速讀取

• Key Cache：key_buffer_size 適當調大，快取索引塊
• Query Cache：query_cache_size 快取重複查詢結果集 (適用靜態資料)
• 第三方快取：Memcached/Redis 緩存熱點資料，繞過資料庫讀取

策略五：讀寫分離架構

• 主庫負責寫入 (INSERT/UPDATE/DELETE)
• 從庫負責讀取 (SELECT)，透過複寫同步
• 從庫無寫入鎖爭用，查詢極快且不阻塞主庫
• 進階：分區表 (PARTITION BY RANGE/HASH) 將大表拆分，減少單表掃描範圍

鎖爭用監控指標

SHOW STATUS LIKE 'Table_locks%';
-- Table_locks_immediate / Table_locks_waited > 5000 表鎖爭用嚴重，建議遷移 InnoDB

總結建議

1. 新專案**優先選 InnoDB** (行級鎖、支援交易、崩潰安全)
2. 既有 MyISAM 系統：調大 concurrent_insert=2、配置 low_priority_updates、上快取、規劃讀寫分離
3. 監控鎖等待比例，超過閾值啟動遷移評估