前言
在日常工作中经常会使用一些比较“大”的数据库查询和操作，这里的“大”主要是指
- 执行时间长：含有较多的逻辑处理、存在较耗时操作等
- 操作数据多：需要查询或更新操作的数量记录较多，会锁定大量数据造成阻塞和锁超时等。
本文会和大家一起探讨下，为什么 在数据库中要避免使用这些大查询。
事务
大家都清楚事务具备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，转载请注明出处
