前言

在日常工作中经常会使用一些比较“大”的数据库查询和操作，这里的“大”主要是指

• 执行时间长：含有较多的逻辑处理、存在较耗时操作等
• 操作数据多：需要查询或更新操作的数量记录较多，会锁定大量数据造成阻塞和锁超时等。

本文会和大家一起探讨下，为什么 在数据库中要避免使用这些大查询。

事务

大家都清楚事务具备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语句其实等价于

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

written by AloofJr，转载请注明出处