MySQL 大量 Locked 進程排查與優化：從鎖等待根因到配置調優實戰

故障現象

• 查詢無響應，Navicat 顯示大量進程狀態為 Locked
• 單條長時間運行查詢處於 Sending data，阻塞其他所有連線
• Kill 掉該進程後，資料庫瞬間恢復正常

核心原因分析

MyISAM 表級鎖機制

MyISAM 只支援表級鎖：

• SELECT → 共享讀鎖（可並發讀，阻塞寫）
• INSERT/UPDATE/DELETE → 獨佔寫鎖（阻塞所有讀寫）
• 長時間 SELECT 持有讀鎖 → 後續寫入排隊 → 後續讀取被寫鎖阻塞 → 連鎖阻塞

InnoDB 行級鎖 + 間隙鎖

InnoDB 支援行級鎖，但以下情況仍會退化：

• 無索引 / 索引失效 → 全表掃描 → 鎖定所有記錄 + 間隙鎖
• 大事務長時間持有鎖
• 死鎖未及時檢測

排查步驟

-- 1. 查看當前進程
SHOW FULL PROCESSLIST;

-- 2. 查看鎖等待 (MySQL 5.7+)
SELECT * FROM sys.schema_table_lock_waits;
SELECT * FROM sys.innodb_lock_waits;

-- 3. 查看 InnoDB 引擎狀態
SHOW ENGINE INNODB STATUS\G
-- 關注 TRANSACTIONS 區塊：LOCK WAIT、等待時間、持有鎖的 SQL

-- 4. 慢查詢日誌分析
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
-- 使用 pt-query-digest 分析

優化策略：由淺入深

第一層：SQL 與索引優化（最高 ROI）

第二層：架構與應用層優化

• 讀寫分離：主庫寫、從庫讀，降低主庫鎖競爭
• 快取層：Redis 快取熱點數據，減少 DB 讀取壓力
• 異步處理：非核心寫入入隊列，削峰填谷
• 拆分熱點表：計數器、日誌、統計表獨立存放

第三層：MySQL 參數調優（輔助）

[mysqld]
# 連線層
max_connections = 2000
thread_cache_size = 100
back_log = 500
wait_timeout = 7200
interactive_timeout = 7200

# InnoDB 核心
innodb_buffer_pool_size = 60%_physical_ram
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON

# 查詢快取 (5.7 建議關閉，8.0 已移除)
query_cache_type = 0
query_cache_size = 0

# 表快取
table_open_cache = 4000
table_definition_cache = 2000

# 臨時表
tmp_table_size = 256M
max_heap_table_size = 256M

# 排序/連接緩衝 (per thread, 慎調)
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 4M
read_rnd_buffer_size = 4M

MyISAM → InnoDB 遷移建議

若仍在使用 MyISAM 遇到鎖爭用，**強烈建議遷移至 InnoDB**：

ALTER TABLE table_name ENGINE=InnoDB;

• 行級鎖大幅降低爭用
• 支援事務、崩潰恢復、外鍵
• 自適應雜湊索引、Change Buffer 寫入優化

遷移注意：COUNT(*) 無 WHERE 時 InnoDB 需掃描，建議維護計數表或使用 EXPLAIN 估算行數。

監控告警指標