mysql监控和优化长事务

一、先明确：什么是“长事务”？

长事务没有绝对定义，通常认为运行时间超过 5 秒的事务就是长事务（高并发场景下，超过 1 秒就可判定）。长事务的危害核心是：

• 持续持有锁，引发大量锁等待/死锁；
• 占用 MVCC 快照资源，导致 undo 日志膨胀；
• 降低数据库并发吞吐量，甚至拖慢整个实例。

二、监控长事务：精准定位问题事务

1. 实时查询长事务（最直接的 SQL 方法）

通过 INFORMATION_SCHEMA.INNODB_TRX 系统表，可实时查看所有活跃事务的运行时长、执行的 SQL、隔离级别等关键信息：

-- 查看运行超5秒的长事务（核心监控SQL）
SELECT
  trx_id AS 事务ID,
  trx_mysql_thread_id AS 线程ID,
  trx_started AS 启动时间,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS 运行时长_秒,
  trx_isolation_level AS 隔离级别,
  trx_state AS 事务状态,
  trx_query AS 正在执行的SQL -- 显示事务当前执行的SQL（关键）
FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 5 -- 筛选超5秒的事务
ORDER BY 运行时长_秒 DESC;

2. 开启长事务日志（持续监控）

MySQL 可通过配置开启长事务日志，自动记录超过阈值的事务，方便事后分析：

-- 临时开启（重启失效）
SET GLOBAL slow_query_log = ON; -- 开启慢查询日志
SET GLOBAL long_query_time = 1; -- 记录执行超1秒的SQL
SET GLOBAL log_slow_admin_statements = ON; -- 记录管理类慢操作（如ALTER）
SET GLOBAL log_slow_slave_statements = ON; -- 从库也记录慢事务
-- 日志文件路径（可自定义）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/long_trx.log';

通过 mysqldumpslow 分析日志，筛选长事务相关的 SQL：

# 按执行时间排序，查看长事务SQL
mysqldumpslow -s t -g 'START TRANSACTION|COMMIT|ROLLBACK' /var/lib/mysql/long_trx.log

3. 可视化监控工具（新手友好）

• Percona Monitoring and Management (PMM)：开源免费，提供“Long Running Transactions”面板，直观展示长事务的数量、运行时长、关联 SQL；
• MySQL Workbench：在“Performance → Dashboard → InnoDB Transactions”中，可查看“Longest Running Transaction”指标；
• Prometheus + Grafana：自定义监控面板，设置长事务告警（如“运行时长>5秒的事务数>10”时触发告警）。

4. 关键监控指标（量化长事务影响）

查询指标的 SQL：

SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME IN (
  'Innodb_trx_running',
  'Innodb_undo_log_bytes',
  'Threads_running'
);

三、分析长事务：找到耗时根源

监控到长事务后，需分析其耗时的原因，常见根源包括：

1. 事务内包含大量非核心操作：如查询无关数据、复杂统计、外部接口调用；
2. SQL 执行效率低：事务内的 SELECT/UPDATE 未走索引，导致单条 SQL 执行超时；
3. 事务未及时提交：代码中忘记写 COMMIT，或事务逻辑分支遗漏提交；
4. 锁等待导致事务阻塞：事务因等待锁而“被动”变长（看似长事务，实则是锁等待）；
5. 大事务操作：批量插入/更新大量数据，单事务处理数万行数据。

分析方法：

1. 查看事务执行的完整 SQL 链路：
   通过 performance_schema 查看线程执行的历史 SQL（需先开启）：

   -- 开启performance_schema（临时生效）
   SET GLOBAL performance_schema = ON;
   -- 查看线程ID=123的历史SQL（线程ID从INNODB_TRX中获取）
   SELECT
     EVENT_ID,
     SQL_TEXT,
     TIMER_WAIT/1000000000 AS 执行时间_秒
   FROM performance_schema.events_statements_history
   WHERE THREAD_ID = 123
   ORDER BY EVENT_ID;
   

2. 区分“主动长事务”和“被动长事务”：
   • 主动长事务：SQL 本身执行慢（如全表扫描），需优化 SQL；
   • 被动长事务：事务等待锁导致耗时久，需优化锁冲突（参考之前的锁等待优化方法）。

四、优化长事务：缩短事务生命周期

1. 核心优化原则：“事务只做必要的事”

长事务的本质是“事务内包含了太多非必要操作”，优化核心是把非核心操作移出事务，仅保留必须的修改操作。

反例（典型的长事务）：

START TRANSACTION;
-- 1. 非核心查询（无必要在事务内）
SELECT * FROM user WHERE id = 1;
SELECT COUNT(*) FROM order WHERE user_id = 1;
-- 2. 外部接口调用（耗时且无锁，完全可移出）
-- 伪代码：call_payment_api(1); -- 调用支付接口，耗时3秒
-- 3. 核心修改
UPDATE account SET balance = 2000 WHERE id = 1;
-- 4. 非核心统计
SELECT SUM(amount) FROM trade WHERE user_id = 1;
COMMIT; -- 事务总耗时超5秒

优化后（短事务）：

-- 步骤1：所有非核心操作移出事务（无锁，不占用事务资源）
SELECT * FROM user WHERE id = 1;
SELECT COUNT(*) FROM order WHERE user_id = 1;
-- call_payment_api(1); -- 外部接口调用移出
SELECT SUM(amount) FROM trade WHERE user_id = 1;

-- 步骤2：仅保留核心修改，快速提交
START TRANSACTION;
UPDATE account SET balance = 2000 WHERE id = 1;
COMMIT; -- 事务耗时仅毫秒级

2. 具体优化方法（按优先级排序）

（1）拆分大事务为小事务（最高优先级）

批量操作（如批量插入/更新）是长事务的重灾区，需拆分为小批量事务，每次仅处理少量数据：

-- 反例：批量更新1万行，事务耗时超10秒
START TRANSACTION;
UPDATE order SET status = 2 WHERE create_time < '2026-01-01';
COMMIT;

-- 优化：按主键分批更新，每次100行，事务耗时<1秒
-- 第1批
START TRANSACTION;
UPDATE order SET status = 2 WHERE id BETWEEN 1 AND 100;
COMMIT;
SELECT SLEEP(0.01); -- 短暂延迟，避免集中锁冲突

-- 第2批
START TRANSACTION;
UPDATE order SET status = 2 WHERE id BETWEEN 101 AND 200;
COMMIT;
-- 以此类推...

（2）优化事务内的慢 SQL

事务内的 SQL 执行效率直接决定事务时长，需确保所有 SQL 走索引：

-- 反例：事务内SELECT无索引，耗时3秒
START TRANSACTION;
SELECT * FROM order WHERE user_id = 123; -- user_id无索引，全表扫描
UPDATE account SET balance = 2000 WHERE id = 1;
COMMIT;

-- 优化：给user_id加索引，SELECT耗时<10ms
ALTER TABLE order ADD INDEX idx_user_id (user_id);

START TRANSACTION;
SELECT * FROM order WHERE user_id = 123; -- 走索引，快速执行
UPDATE account SET balance = 2000 WHERE id = 1;
COMMIT;

（3）避免事务内的外部依赖

事务内调用外部接口（如支付、短信）、执行 Shell 脚本等，会大幅延长事务时长，必须移出：

# 伪代码示例（Java/Python通用逻辑）
# 反例：事务内调用外部接口
conn.begin() # 开启事务
update_account_balance(1, 2000) # 修改数据库
call_payment_api(1) # 调用外部支付接口，耗时3秒
conn.commit() # 事务总耗时>3秒

# 优化：先调用接口，再执行短事务
call_payment_api(1) # 外部操作移出事务
conn.begin() # 开启事务
update_account_balance(1, 2000) # 仅数据库修改
conn.commit() # 事务耗时<100ms

（4）及时提交/回滚事务

代码中需确保事务逻辑无遗漏，异常时立即回滚，避免事务“挂起”：

-- 反例：忘记COMMIT，事务一直运行
START TRANSACTION;
UPDATE account SET balance = 2000 WHERE id = 1;
-- 无COMMIT，事务持续持有锁

-- 优化：明确提交，异常回滚
START TRANSACTION;
BEGIN TRY
  UPDATE account SET balance = 2000 WHERE id = 1;
  COMMIT; -- 正常提交
END TRY
BEGIN CATCH
  ROLLBACK; -- 异常回滚
END CATCH

（5）降低事务隔离级别（按需）

对非核心业务，将隔离级别从 REPEATABLE READ 改为 READ COMMITTED，减少 MVCC 快照开销，缩短事务执行时间：

-- 仅当前会话生效（针对非核心事务）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

五、应急处理：杀掉失控的长事务

若长事务已导致数据库性能暴跌，可直接杀掉对应的线程，强制终止事务：

-- 1. 先查长事务的线程ID（trx_mysql_thread_id）
SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;

-- 2. 杀掉线程（替换为实际线程ID）
KILL 123;

注意：强制杀线程可能导致事务回滚，需评估业务影响（优先杀非核心业务的长事务）。

六、优化效果验证

优化后通过以下指标验证效果：

1. 长事务数量：运行时长>5秒的事务数减少 80% 以上；
2. 活跃事务数：Innodb_trx_running 回落至正常范围；
3. 锁等待次数：Innodb_row_lock_waits 下降 60% 以上；
4. TPS/QPS：数据库吞吐量回升至基准值。

验证 SQL：

-- 对比优化前后的长事务数
SELECT COUNT(*) AS 长事务数 FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 5;