【从入门到放弃-MySQL】mysql中要避免使用大事务

## 前言
在日常工作中经常会使用一些比较“大”的数据库查询和操作,这里的“大”主要是指
- 执行时间长:含有较多的逻辑处理、存在较耗时操作等
- 操作数据多:需要查询或更新操作的数量记录较多,会锁定大量数据造成阻塞和锁超时等。
本文会和大家一起探讨下,为什么 在数据库中要避免使用这些大查询。
## 事务
大家都清楚事务具备ACID特性(即原子性、一致性、隔离性、持久性),针对隔离性,在数据库事务隔离标准中,定义了四种隔离级别:读未提交、读提交、可重复读、串行化。MySQL默认的事务隔离级别是可重复读,我们以此来展开分析
### 事务隔离的实现
#### 多版本并发控制(MMVC)
每行记录后面会有两个隐藏列,记录创建版本号及删除版本号。创建本本号记为row trx_id
对于一个事务来说,启动时(申请完事务id后),MySQL会给此事务创建一个活跃事务(即已启动但还未提交的事务)id数组数组中的最小值记为minTid,最大值记为maxTid。
- 如果minTid > row trx_id,则数据是可见的。
- 如果maxTid < row trx_id,则数据是不可见的。
- 如果minTid <= row trx_id <= maxTid,且:
- row trx_id在数组中,则说明启动时,此事务未提交,数据不可见
- row trx_id不在数组中,则说明启动是,此事务已提交,数据可见
![](https://nc2era.com/b382ba8d/B30110DE-9201-4F7E-96D3-4E56D97EB85E.png)
![](https://nc2era.com/b382ba8d/FA9018E8-28AA-4181-8F3C-2103B7F64BDE.png)
如:当前事务id为50,活跃id数组为[35, 43, 44, 45, 46, 50, 51, 52]则
- row trx_id小于35的数据为可见
- row trx_id大于52的数据不可见
- 35 <= row trx_id <= 52且在数组中的数据不可见,不在数组中的数据可见。
对于不可见的数据,则需要依次去数据上一个版本查询,直到查询到可用版本数据为止。
只有在新的RW事务建立的时候 才会新建一个视图 否则继续使用上次创建的视图。
#### 回滚日志(undo log)
上面提到对于不可见数据需要依次查询上一版本来获取到可用数据。
我们知道数据库的数据更新是非常频繁的,不可能将每一版本的数据都存下来,那样数据量会巨大查询也会非常的缓慢。
MySQL通过undo log来获取历史版本的数据。undo log不会记录每个版本的最终数据,它是一个逻辑日志,是反向将之前的操作取消掉。比如对insert的会进行执行delete,delete的执行insert,对于update的数据会执行一个反向update,将之前修改的内容改回去。
例如:
- S1时刻,事务34启动,进行insert i = 5 操作后,commit,数据记录为D1:i = 5,row_id为34;
- S2时刻,事务36启动;
- S3时刻,事务37启动,进行update i + 3 操作后,commit,数据记录为D3:i = 8,row_id为37;
- S4时刻,事务42启动
- S5时刻,事务54启动,进行update i * 2 操作后,commit,数据记录为D5:i = 16,row_id为54
此时,如果事务42需要查询i的数据,因为当前i = 16,row_id为54,数据不可见,因此需要根据undo log查询上一版本的数据。update i / 2,得到row_id为37。可见,获取i = 8
如果事务36需要查询i的数据,需要update i / 2, 查到row_id = 37,不可见,继续回滚 update i - 3,查到row_id = 34,可见,获取到i = 5
只有当回滚日志不再需要时,才会删除。系统会判断,当没有事务再需要这些回滚日志的时候,才会删除。
所以长事务意味着系统里面会存在很多非常老的事务视图,因为这些事务可能会访问数据库中的任何数据,所以在这个事务提交之前,系统不得不保留它之后可能用到的所有回滚记录。这就会占用大量的存储空间。
#### 事务启动
autocommit参数控制事务是否自动提交,MySQL默认set autocommit=1,开启自动提交,即每条select、update都会自动提交。所以我们日常使用的SQL语句其实等价于
```
begin;
select * from table where xxx;
commit;
```
但有些客户端连接框架默认会在连接成功后执行一条set autocommit = 0,这样会导致你只有执行一条select语句其实就开启了事务。这样会意外导致长事务的出现。
因此还是建议set autocommit = 1配合begin来显示的启动事务。
## 锁
大事务还会长时间、大量占用锁资源,阻塞DML、DDL操作、造成锁超时影响系统并发能力,并且很容易引发死锁问题。
## 连接数
大事务会长时间占用数据库连接,并发情况下容易造成连接数满的问题 拖垮整个应用
## 主备延迟
MySQL主备复制只会在事务执行完毕后才会进行,即binlog在事务commit后才会生成(两阶段提交)。
大事务执行多久就会造成多长时间的主备延迟,主备延迟的时间越长带来的风险也就越高
## 缓存
MySQL的buffer pool对查询具有缓存效果,对于很多高频查询可以直接从缓存返回不需要查找磁盘文件。但是当有大量数据需要返回时通常有很多顺序查询,记录在同一磁盘页中就会命中缓存机制 对缓存造成一定影响
MySQL buffer pool的缓存机制是使用的改良LRU算法(主要增加了访问时间控制)
## 内存&CPU
MySQL数据返回默认是边取边发,因此数据较多,传输时间较长也也会引发长事务带来的问题。
还有如果返回大量数据给客户端处理,对客户端的内存及CPU也会带来较大的压力。
## 超时和超出大小限制
容易引起超时的问题和超出max_binlog_cache_size导致执行失败。(还要注意,避免出现为了让主库大事务顺利进行,临时调大主库max_binlog_cache_size,忽略备库导致的服务宕掉等严重后果)
## 回滚
回滚大事务也是非常耗时和占用内存的,需要注意
## 总结
应该尽量避免使用大事务,开发时要注意尽量
- 如果可以,将一个大事务拆分成多个小事务执行
- 将事务中可以提出的select查询放在事务外执行
## 更多文章
见我的博客:[https://nc2era.com](https://nc2era.com)
written by [AloofJr](https://nc2era.com),转载请注明出处