MySQL 性能優化實戰：常用命令、鎖超時、死鎖、長事務排查與優化策略

MySQL 性能優化實戰：從常用命令到疑難雜癥排查

記錄生產環境高頻優化操作：通用日誌控制、事務隔離、鎖超時、死鎖、長事務排查的具體命令與解決思路。

一、常用診斷命令

# 通用日誌（慎開，極耗 IO）
SHOW GLOBAL VARIABLES LIKE '%general%';
SET GLOBAL general_log = ON;   -- 開啟
SET GLOBAL general_log = OFF;  -- 關閉

# 事務狀態
SET autocommit = 0;  -- 開啟手動提交模式

# InnoDB 核心狀態
SHOW ENGINE INNODB STATUS\G
-- 重點關注：
-- TRANSACTIONS 區：活躍事務 ID、鎖等待、Undo Log 記錄數
-- FILE I/O / BUFFER POOL / LOG 區

二、典型問題分析與解決

1. Lock wait timeout exceeded

現象：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

原因：事務持有行鎖時間過長，超過 innodb_lock_wait_timeout（默認 50s）。

優化策略：

1. 調整語句順序：將易發生鎖衝突的表操作放在事務末尾，縮短鎖持有時間。
2. 拆分事務：大事務拆為多個小事務，中間提交釋放鎖。
3. 合併語句：多條 UPDATE/INSERT 合併為一條（多值 INSERT、JOIN UPDATE），減少網絡往返與鎖獲取次數。
4. 索引優化：確保 WHERE 條件走索引，避免全表掃描升級為表鎖/大量行鎖。

2. Deadlock

排查：SHOW ENGINE INNODB STATUS\G 查看 LATEST DETECTED DEADLOCK 區塊，分析雙方持有鎖與等待鎖。

預防：

• 不同事務以固定順序訪問表/行（如按主鍵升序）。
• 避免長事務，減少鎖持有時間。
• 適當降低隔離級別（RC + 鎖讀一致性讀）或使用 SELECT ... LOCK IN SHARE MODE / FOR UPDATE 顯式加鎖。

3. 長事務未提交

危害：阻塞 Purge 線程，History List Length 暴增，Undo Log 膨脹，導致讀性能下降、磁盤空間耗盡。

定位：

SHOW ENGINE INNODB STATUS\G
-- 找到 Trx_id、Trx_started、Trx_mysql_thread_id
-- 關聯 PROCESSLIST 找到具體 SQL 與應用

處理：

• 應用層修復：補漏 COMMIT/ROLLBACK、加超時控制。
• 緊急 Kill：KILL （謹慎，可能導致大量回滾）。

三、優化總結清單

四、參考資源

• InnoDB Status 詳解：https://imysql.cn/2008_05_22_walk_through_show_innodb_status
• Percona Toolkit：pt-deadlock-logger、pt-query-digest 自動化分析。

總結

MySQL 性能優化核心是縮短事務、縮小鎖範圍、消除全掃。建立「慢查詢日誌 -> EXPLAIN -> 索引/改寫 -> 驗證」與「InnoDB Status 監控 -> 鎖/事務異常告警 -> 應用層修復」雙閉環，將故障解決從「救火」轉為「防火」。