MySQL長事務(wù)：潛伏的數(shù)據(jù)庫殺手！如何識(shí)別與消滅它？
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
你的數(shù)據(jù)庫是否突然變慢甚至凍結(jié)？罪魁禍?zhǔn)卓赡苷悄切┍缓鲆暤拈L事務(wù)！本文將揭示MySQL長事務(wù)的致命危害，并提供全套解決方案，讓你的數(shù)據(jù)庫重獲新生！一、什么是長事務(wù)？一個(gè)定時(shí)炸彈想象你在超市結(jié)賬：
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 特性 | 短事務(wù)（<1s） | 長事務(wù)（>5s） | ||||||
| 鎖持有時(shí)間 | 極短 | 很長 | ||||||
| Undo使用 | 少量 | 巨大 | ||||||
| MVCC版本 | 0-1個(gè) | 數(shù)十上百 | ||||||
| 內(nèi)存占用 | 低 | 高 | ||||||
| 影響范圍 | 局部 | 全局 |
400 186 1886
SELECT * FROM information_schema.INNODB_TRX\G
關(guān)鍵字段：
trx_started：事務(wù)開始時(shí)間
trx_query：最后執(zhí)行的SQL
trx_rows_locked：鎖定行數(shù)
-- 開啟監(jiān)控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE '%transaction%';
-- 查看長事務(wù)
SELECT * FROM performance_schema.events_transactions_current
WHERE TIMER_WAIT > 5000000000; -- 5秒
# my.cnf配置
[mysqld]
long_query_time = 5 -- 記錄超過5秒的事務(wù)
log_slow_transactions = ON
slow_query_log = ON
SHOW ENGINE INNODB STATUS\G
---TRANSACTION 123456, ACTIVE 25 sec -- 長事務(wù)！
2 lock struct(s), 1354 lock(s), undo log entries 1200
MySQL thread id 789, OS thread handle 0x7f8b1c0a6700
-- 查找長事務(wù)ID
SELECT trx_mysql_thread_id
FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30;
-- 終止事務(wù)
KILL 789;
-- 問題查詢（全表掃描）
SELECT * FROM orders
WHERE YEAR(create_time) = 2023
AND status = 'completed';
-- 添加索引
CREATE INDEX idx_create_status ON orders(create_time, status);
-- 重寫查詢
SELECT * FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
AND status = 'completed';
-- 原始長事務(wù)
START TRANSACTION;
UPDATE ... -- 1萬行
INSERT ... -- 5千行
COMMIT;
-- 拆分后
START TRANSACTION;
UPDATE ... LIMIT 1000; -- 分批處理
COMMIT;
START TRANSACTION;
UPDATE ... LIMIT 1000;
COMMIT;
-- 會(huì)話級(jí)超時(shí)
SET SESSION max_execution_time = 5000; -- 5秒
-- 全局超時(shí)
SET GLOBAL innodb_rollback_on_timeout = ON;
SET GLOBAL innodb_lock_wait_timeout = 30; -- 鎖等待超時(shí)30秒
# Python偽代碼示例
def execute_transaction():
attempts = 0
while attempts < 3:
try:
with db.transaction():
# 業(yè)務(wù)操作
db.execute("UPDATE ...")
db.execute("INSERT ...")
return True
except LockTimeoutError:
attempts += 1
sleep(1)
return False
-- 定期清理舊版本
SET GLOBAL innodb_purge_threads = 4; -- 增加清理線程
SET GLOBAL innodb_max_purge_lag = 100000; -- 控制清理延遲
// 錯(cuò)誤示例
void processOrder() {
startTransaction(); // 過早開始
// 復(fù)雜計(jì)算...
updateInventory();
commit();
}
// 正確示例
void processOrder() {
// 復(fù)雜計(jì)算...
startTransaction();
updateInventory(); // 僅包裝DB操作
commit();
}
-- 部署Prometheus監(jiān)控
mysql_global_status_innodb_row_lock_time_avg
mysql_global_status_innodb_num_open_transactions
/* 創(chuàng)建長事務(wù)告警 */
CREATE EVENT check_long_transactions
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
IF (SELECT COUNT(*) FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30) > 0
THEN
-- 觸發(fā)告警
CALL send_alert('Long transactions detected!');
END IF;
END;
時(shí)間：大促期間
現(xiàn)象：數(shù)據(jù)庫凍結(jié)，訂單失敗率飆升
緊急響應(yīng)：15分鐘無法恢復(fù)
-- 罪魁禍?zhǔn)?/p>
START TRANSACTION;
SELECT COUNT(*) FROM orders; -- 2億行表，耗時(shí)5分鐘
-- 忘記提交！
KILL 54321; -- 終止長事務(wù)
-- 添加匯總表
CREATE TABLE order_count (
date DATE PRIMARY KEY,
count INT
);
-- 定時(shí)更新
INSERT INTO order_count
SELECT CURRENT_DATE(), COUNT(*)
FROM orders ON DUPLICATE KEY UPDATE count = VALUES(count);
-- 查詢優(yōu)化
SELECT count FROM order_count WHERE date = CURRENT_DATE();
# my.cnf 推薦配置
[mysqld]
# 事務(wù)超時(shí)
innodb_lock_wait_timeout = 30
max_execution_time = 5000
# 長事務(wù)監(jiān)控
long_query_time = 5
slow_query_log = 1
# Undo優(yōu)化
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1G
最后行動(dòng)：立即執(zhí)行以下命令檢查你的數(shù)據(jù)庫
SELECT
trx_id,
TIMEDIFF(NOW(), trx_started) AS duration,
trx_query
FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 5;
核心原則：
?? 事務(wù)越短越好 - 理想事務(wù)應(yīng)在毫秒級(jí)完成
?? 監(jiān)控勝于救火 - 建立實(shí)時(shí)告警系統(tǒng)
??? 預(yù)防重于治療 - 從設(shè)計(jì)階段規(guī)避風(fēng)險(xiǎn)
閱讀原文：原文鏈接
