MySQL 查詢優化：調度策略與鎖機制協同調優實戰指南

核心目標：單查詢快 + 多客戶端協作不互斥

MySQL 允許通過 **調度優先級** 和 **鎖機制** 兩大維度，優化多客戶端並發場景下的查詢性能與公平性。

默認調度策略 (MyISAM/MEMORY 表鎖實現)

1. 寫入優先於讀取：UPDATE/INSERT/DELETE > SELECT
2. 寫入串行化：同表寫入按到達順序排隊
3. 讀取並發：多個 SELECT 可同時執行

鎖管理器自動獲取/釋放流程

• 客戶端訪問表 → 自動獲取對應鎖 (讀鎖/寫鎖)
• 操作完成 → 自動釋放鎖
• 可手動控制：LOCK TABLES / UNLOCK TABLES

鎖類型與兼容性矩陣

鎖釋放優先級：寫鎖隊列優先於讀鎖隊列

**後果**：大量更新操作會導致 SELECT 長期等待 (寫入飢餓讀取)。

調度修飾符：改變默認優先級

實戰應用場景

-- 1. 非關鍵統計更新，不阻塞核心查詢
UPDATE LOW_PRIORITY stats SET pv=pv+1 WHERE page='home';

-- 2. 關鍵用戶查詢，搶佔優先級
SELECT HIGH_PRIORITY * FROM users WHERE id=100;

-- 3. 大批量日志寫入，不阻塞業務
INSERT DELAYED INTO access_log (url, ua, ip) VALUES (...);

-- 4. 會話級設置所有更新低優先級
SET LOW_PRIORITY_UPDATES=1;
-- 全局設置
SET GLOBAL low_priority_updates=1;
-- 配置文件永久
[mysqld]
low-priority-updates=1

InnoDB 行鎖 + MVCC：現代並發標配

• **表鎖僅在 LOCK TABLES 時使用**，正常操作用行鎖
• **MVCC 快照讀**：SELECT 不加鎖，讀取歷史版本，**完全不阻塞寫入**
• **行鎖類型**：記錄鎖、間隙鎖、臨鍵鎖 (REPEATABLE READ 默認)
• **死鎖自動檢測**：等待圖檢測 → 回滾代價最小事務

InnoDB 鎖優化核心原則

1. 索引命中：WHERE 條件用到索引 → 行鎖；全表掃描 → 表鎖 (或大量行鎖退化)
2. 縮小範圍：精確條件、避免 LIKE '%xxx%'、IS NULL 導致全掃
3. 縮短事務：事務內少查詢、早提交、避免用戶交互
4. 合理隔離級別：READ COMMITTED 減少間隙鎖，配合 binlog_format=ROW

並發插入特性 (MyISAM 3.23.7+)

• 條件：表中間無空洞 (無 DELETE/UPDATE 碎片)
• SELECT 期間允許 INSERT 追加到文件末尾
• OPTIMIZE TABLE 清理空洞恢復特性

大量 INSERT/SELECT 衝突應對

1. 寫入臨時表，定期合併到主表
2. 使用 INSERT DELAYED (MyISAM/MEMORY)
3. 遷移 InnoDB (行鎖 + MVCC 徹底解決)

SELECT/DELETE 衝突應對

• DELETE ... LIMIT 1000 分批刪除，減少持鎖時間
• SELECT SQL_BUFFER_RESULT 結果先存臨時表，釋放表鎖

監控指標體系

指標	來源	告警閾值	含義
Table_locks_waited / (immediate+waited)	SHOW STATUS	> 1%	表鎖等待率
Innodb_row_lock_waits	SHOW STATUS	持續增長	行鎖等待次數
Innodb_row_lock_time_avg	SHOW STATUS	> 10ms	平均行鎖等待時間
Innodb_deadlocks	SHOW STATUS	> 0	死鎖次數
Slow_queries	SHOW STATUS	業務基線	慢查詢累計

優化總結口訣

「MyISAM 表鎖：LOW_PRIORITY 寫、HIGH_PRIORITY 讀、DELAYED 非即時、並發插入靠無空洞」
「InnoDB 行鎖：索引命中、範圍最小、事務最短、RC 隔離級別」
「調度修飾符：語句級精準控制、會話級批量設置、全局級策略固化」
「監控驅動：Table_locks_waited 率、Innodb_row_lock_waits、死鎖歷史」
「架構選型：95% 場景 InnoDB 行鎖+MVCC 徹底解決讀寫衝突」

調度與鎖的協同調優，本質是 **在一致性、並發性、延遲性三角中找平衡**。MyISAM 靠優先級妥協；InnoDB 靠 MVCC 突破。認清引擎特性，匹配業務場景，監控指標說話，才是優化正道。