【从入门到放弃-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不在数组中,则说明启动是,此事务已提交,数据可见

如:当前事务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语句其实等价于

1
2
3
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查询放在事务外执行