解决MySQL要避免大事务以及大事务的问题

2022-11-12 09:12:28
内容摘要
这篇文章主要为大家详细介绍了解决MySQL要避免大事务以及大事务的问题，具有一定的参考价值，可以用来参考一下。 感兴趣的小伙伴，下面一起跟随512笔记的小玲来看看吧！ 什么是大
文章正文

这篇文章主要为大家详细介绍了解决MySQL要避免大事务以及大事务的问题，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随数据库教程的小编来看看吧！

什么是大事务

运行时间比较长，长时间未提交的事务就可以称为大事务

大事务产生的原因

  • 操作的数据比较多
  • 大量的锁竞争
  • 事务中有其他非DB的耗时操作
  • 。。。

大事务造成的影响

  • 并发情况下，数据库连接池容易被撑爆
  • 锁定太多的数据，造成大量的阻塞和锁超时
  • 执行时间长，容易造成主从延迟
  • 回滚所需要的时间比较长
  • undo log膨胀
  • 。。。

如何查询大事务

**注**：本文的sql的操作都是基于mysql5.7版本

以查询执行时间超过10秒的事务为例：

代码如下：

1
2
<code>
select \* from information\_schema.innodb\_trx where TIME\_TO\_SEC(timediff(now(),trx\_started))>10</code>

MySQL为什么要避免大事务以及大事务解决的方法

如何避免大事务

通用解法

  • 在一个事务里面， 避免一次处理太多数据
  • 在一个事务里面，尽量避免不必要的查询
  • 在一个事务里面， 避免耗时太多的操作，造成事务超时。一些非DB的操作，比如rpc调用，消息队列的操作尽量放到事务之外操作

基于mysql5.7的解法

  • 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。**如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放**
  • 通过SETMAX_EXECUTION_TIME命令， 来控制每个语句查询的最长时间，避免单个语句意外查询太长时间
  • 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill
  • 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题
  • 设置innodb_undo_tablespaces值，将undo log分离到独立的表空间。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便

附录查询事务相关语句

**注**：sql语句都是基于mysql5.7版本

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
<code>
# 查询所有正在运行的事务及运行时间
 
select t.\*,to\_seconds(now())-to\_seconds(t.trx\_started) idle\_time from INFORMATION\_SCHEMA.INNODB\_TRX t
 
 
 
# 查询事务详细信息及执行的SQL
 
select now(),(UNIX\_TIMESTAMP(now()) - UNIX\_TIMESTAMP(a.trx\_started)) diff\_sec,b.id,b.user,b.host,b.db,d.SQL\_TEXT from information\_schema.innodb\_trx a inner join information\_schema.PROCESSLIST b
 
on a.TRX\_MYSQL\_THREAD\_ID=b.id and b.command = 'Sleep'
 
inner join performance\_schema.threads c ON b.id = c.PROCESSLIST\_ID
 
inner join performance\_schema.events\_statements\_current d ON d.THREAD\_ID = c.THREAD\_ID;
 
 
 
# 查询事务执行过的所有历史SQL记录
 
SELECT
 
 ps.id 'PROCESS ID',
 
 ps.USER,
 
 ps.HOST,
 
 esh.EVENT\_ID,
 
 trx.trx\_started,
 
 esh.event\_name 'EVENT NAME',
 
 esh.sql\_text 'SQL',
 
 ps.time
 
FROM
 
 PERFORMANCE\_SCHEMA.events\_statements\_history esh
 
 JOIN PERFORMANCE\_SCHEMA.threads th ON esh.thread\_id = th.thread\_id
 
 JOIN information\_schema.PROCESSLIST ps ON ps.id = th.processlist\_id
 
 LEFT JOIN information\_schema.innodb\_trx trx ON trx.trx\_mysql\_thread\_id = ps.id
 
WHERE
 
 trx.trx\_id IS NOT NULL
 
 AND ps.USER != 'SYSTEM\_USER'
 
ORDER BY
 
 esh.EVENT\_ID;
 
  
 
 # 简单查询事务锁
 
 select \* from sys.innodb\_lock\_waits
 
  
 
 # 查询事务锁详细信息
 
 SELECT
 
 tmp.\*,
 
 c.SQL\_Text blocking\_sql\_text,
 
 p.HOST blocking\_host
 
FROM
 
 (
 
 SELECT
 
 r.trx\_state wating\_trx\_state,
 
 r.trx\_id waiting\_trx\_id,
 
 r.trx\_mysql\_thread\_Id waiting\_thread,
 
 r.trx\_query waiting\_query,
 
 b.trx\_state blocking\_trx\_state,
 
 b.trx\_id blocking\_trx\_id,
 
 b.trx\_mysql\_thread\_id blocking\_thread,
 
 b.trx\_query blocking\_query
 
 FROM
 
 information\_schema.innodb\_lock\_waits w
 
 INNER JOIN information\_schema.innodb\_trx b ON b.trx\_id = w.blocking\_trx\_id
 
 INNER JOIN information\_schema.innodb\_trx r ON r.trx\_id = w.requesting\_trx\_id
 
 ) tmp,
 
 information\_schema.PROCESSLIST p,
 
 PERFORMANCE\_SCHEMA.events\_statements\_current c,
 
 PERFORMANCE\_SCHEMA.threads t
 
WHERE
 
 tmp.blocking\_thread = p.id
 
 AND t.thread\_id = c.THREAD\_ID
 
 AND t.PROCESSLIST\_ID = p.id </code>

MySQL为什么要避免大事务以及大事务解决的方法

以上就是MySQL避免大事务以及大事务解决的方法的详细内容，更多关于MySQL 大事务的资料请关注512笔记其它相关文章！

注：关于解决MySQL要避免大事务以及大事务的问题的内容就先介绍到这里，更多相关文章的可以留意

代码注释
上一篇MySQL中json字段的操作的解决办法下一篇配置hive元数据到MySQL中的全过程记录的解决办法

作者：喵哥笔记

IDC笔记

学的不仅是技术，更是梦想！