深入了解mysql长事务

深入了解 MySQL 长事务的攻略

在 MySQL 中，长事务是指执行时间超过一定阈值（一般是几秒钟）的事务，长事务会导致锁定资源，降低系统的吞吐量，甚至还会导致性能问题、死锁等。

深入了解 MySQL 长事务，有助于我们排查生产环境中出现的性能问题、死锁等问题，下面是详细的攻略：

了解长事务在 MySQL 中的表现

MySQL 在 InnoDB 存储引擎中，使用 MVCC（多版本并发控制）机制，保证读写数据的一致性。可以通过以下 SQL 查询，查看当前运行的长事务列表，来了解长事务在 MySQL 中的表现：

SELECT * FROM information_schema.INNODB_TRX;

该查询返回的结果集，包含了当前运行的所有事务，显示以下列：

• trx_id：事务 ID。
• trx_state：事务状态，包含 RUNNING（运行中）、LOCK WAIT（等待锁）、ROLLING BACK（回滚中）和 COMMITTING（提交中）。
• trx_started：事务的启动时间。
• trx_request_lock_id：事务正在等待的锁的 ID。
• trx_wait_started：事务等待锁的开始时间。
• trx_mysql_thread_id：客户端线程 ID。
• trx_query：当前长事务的 sql 语句。

找出长事务的元凶

在上一步中，我们了解到如何查看当前运行的长事务列表。但是，如何判断长事务是数据库性能瓶颈的元凶呢？

可以考虑使用以下两种手段，找出长事务的元凶：

1. 使用 show processlist 查看当前所有连接的状态

SHOW FULL PROCESSLIST;

该命令可以获取到当前所有连接及其状态，包括连接建立时间、当前的 SQL 语句等。通过该命令，可以发现长时间占用数据库资源的 SQL 语句，可以进行优化。

2. 分析长事务的日志

对于长时间运行的事务，可以通过 MySQL 的日志文件（如 slow query log）进行分析。在 MySQL 的配置文件中，可以通过设置以下参数启用慢查询日志：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

启用该日志后，MySQL 会将执行时间超过一定阈值（如 2 秒）的 SQL 语句记录下来。我们可以通过分析慢查询日志，找出长时间运行的 SQL 语句，进行针对性的优化。

优化中长事务

找出长事务的元凶之后，接下来可以进行优化了。下面是一些常见的优化手段：

1. 优化查询语句

可以使用 explain 命令来分析 SQL 语句的执行计划，查看是否存在表扫描、索引失效等问题。有了 explain 的结果，就可以根据具体情况，对查询语句进行优化。

2. 分解长事务

对于长时间运行的事务，可以考虑将其拆分成多个较短的事务。可以通过对业务逻辑的分析，找出长事务中可以拆分成多个短事务的部分，减小锁冲突的概率，降低系统的压力。

3. 减少数据操作量

可以通过批量操作、缓存等手段，减少数据的操作量。例如，批量更新数据可以使用一条 update 语句，而不是循环执行多次 update 语句。

示例说明

假设我们有如下一张表：

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `age` int(11) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

然后，我们执行如下 SQL 语句：

START TRANSACTION;
INSERT INTO users (name, age) VALUES ('张三', 25);
INSERT INTO users (name, age) VALUES ('李四', 30);
INSERT INTO users (name, age) VALUES ('王五', 35);
INSERT INTO users (name, age) VALUES ('赵六', 40);
COMMIT;

以上 SQL 语句执行时间较短，不会被视为长事务。

接着，我们执行如下 SQL 语句：

START TRANSACTION;
INSERT INTO users (name, age) VALUES ('张三', 25);
INSERT INTO users (name, age) VALUES ('李四', 30);
INSERT INTO users (name, age) VALUES ('王五', 35);
INSERT INTO users (name, age) VALUES ('赵六', 40);
-- 模拟长时间操作
SELECT SLEEP(10);
COMMIT;

以上 SQL 语句中，模拟了长时间操作。可以通过以下命令查看当前运行的长事务：

SELECT * FROM information_schema.INNODB_TRX;

同时，也可以通过慢查询日志找到长时间运行的 SQL 语句：

# Time: 2019-01-01T00:00:00.000000Z
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 10.000000  Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1546300800;
START TRANSACTION;
INSERT INTO users (name, age) VALUES ('张三', 25);
INSERT INTO users (name, age) VALUES ('李四', 30);
INSERT INTO users (name, age) VALUES ('王五', 35);
INSERT INTO users (name, age) VALUES ('赵六', 40);
SELECT SLEEP(10);
COMMIT;

最后，我们可以通过上述的优化手段，对长事务进行优化。