MySQL大数据查询揭秘：全表查询的影响与应对
本文为《MySQL归纳学习》专栏的第五篇文章，同时也是关于 MySQL查询知识点的第五篇文章。
往期回顾：
海量数据查询中的全表查询问题一直困扰着许多开发者。本文详细介绍了全表查询在MySQL的Server层和InnoDB引擎中的不利影响，揭示了其带来的挑战。同时，我们探索了MySQL采用的边读边发逻辑，以及InnoDB引擎内部的淘汰策略和对LRU算法的改进，从而解释了为何大查询不会导致内存暴涨。
首先来看一下这张思维导图，对本文内容有个直观的认识。
何谓全表扫描？
我们学习 MySQL，都是基于 InnoDB 学习的，在学习索引章节，我们了解到聚簇索引（主键索引）的叶节点data域保存了完整的数据记录，所以全表扫描实际上是直接扫描表的主键索引。
全表扫描对 server 层的影响
假设，我们现在要对一个 200G 的 InnoDB 表 db1. t，执行一个全表扫描。当然，你要把扫描结果保存在客户端，会使用类似这样的命令：
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
实际上，服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
- 获取一行，写到 net_buffer（每个线程单独分配，并非共享的） 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
- 重复获取行，直到 net_buffer 写满，调用网络接口一次性发送到 socket send buffer。
- 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer，直到写满后重复步骤2。
- 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
注意，net_buffer_length 最小值为 1k，最大值为 1G；socket send buffer 的大小暂时未找到明确的说法。如果 net_buffer_length 小于socket send buffer 的大小，那么在一次性发送过程中，会出现放不下的情况，由于 MySQL的网络通信协议是基于TCP/IP的，那么就需要分块发送，保证数据的可靠传输。
上述过程发生在 server 层，该过程对应的流程图如下所示：
从这个流程中，你可以看到：
- 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；
- socket send buffer 也不可能达到 200G（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。socket receive buffer是客户端那边的，socket send buffer是mysql所在服务器的tcp层。
也就是说，MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。
如果客户端不去读 socket receive buffer 中的内容（即降低甚至停止数据接收速率，也可以提高 socket send buffer的发送速率），然后在服务端 show processlist 看到的结果。
那么什么情况下 State 的值会显示为“Sending to client”？
最核心的一点就是本次查询的数据没有全部放入到 net_buffer，而 net_buffer 想要继续存放数据，则需要等net_buffer里的内容清空后再继续放入后续的结果，想要清空 net_buffer 里的数据，则需要将数据都发送到 socket send buffer 中。
如果你看到 State 的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。
客户端获取服务器数据的两种方式：
- 如果客户端使用–quick 参数，会使用 mysql_use_result 方法。这个方法是读一行处理一行。你可以想象一下，假设有一个业务的逻辑比较复杂，每读一行数据以后要处理的逻辑如果很慢，就会导致客户端要过很久才会去取下一行数据，可能就会出现如上图所示的这种情况。
- 对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存。 如果查询结果过大，则还是建议采用 mysql_use_result 方法。
在实际应用中，我们可能会选择使用一些 ORM框架，比如说 MyBatis Plus。
MyBatis Plus默认使用的是第二种方式，即mysql_store_result，将查询结果保存到本地内存。这是因为 MyBatis Plus基于 MyBatis 框架，并且 MyBatis 默认也是将查询结果一次性读取到内存中，然后进行处理。否则可能因为一次性查询大量数据导致内存溢出 (OOM)。
如果 MySQL 里看到很多个线程都处于“Sending to client”这个状态，就意味着你要让业务开发同学优化查询结果，并评估这么多的返回结果是否合理。
而如果要快速减少处于这个状态的线程的话，将 net_buffer_length 参数设置为一个更大的值是一个可选方案，调整大net_buffer_length 更多的查询结果缓存到net_buffer中。假如net_buffer包含所有的查询数据，此时net_buffer中的数据慢慢发送至socket send buffer即可，客户端即使接收socket send buffer慢或者读取socket receive速度慢；都不影响服务端，因为服务端的结果已经全部存储至net_buffer了，语句执行结束了。
不过上述方案也不安全，因为 net_buffer 是每个线程单独分配，较大的net_buffer大小可以提高处理大量数据的性能，但也会占用更多的内存资源。如果查询请求过多，可能会引发内存压力，导致查询性能下降。
与“Sending to client”长相很类似的一个状态是“Sending data”
实际上，一个查询语句的状态变化是这样的（注意：这里，我略去了其他无关的状态）：
- MySQL 查询语句进入执行阶段后，首先把状态设置成“Sending data”；
- 然后，发送执行结果的列相关的信息（meta data) 给客户端；
- 再继续执行语句的流程；
- 执行完成后，把状态设置成空字符串。
“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。比如，你可以构造一个锁等待的场景，就能看到 Sending data 状态。
可以看到，session B 明显是在等锁，状态显示为 Sending data。
也就是说，仅当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"；而如果显示成“Sending data”，它的意思只是“正在执行”。
查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。
全表扫描对 InnoDB 的影响
内存的数据页是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。
而 Buffer Pool 对查询的加速效果，依赖于一个重要的指标，即：内存命中率。
你可以在 show engine innodb status 结果中，查看一个系统当前的 BP 命中率。一般情况下，一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率 Buffer pool hit rate 要在 99% 以上。
如果所有查询需要的数据页都能够直接从内存得到，那是最好的，对应的命中率就是 100%。但，这在实际生产上是很难做到的。
InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。
InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。
下图是一个 LRU 算法的基本模型。
InnoDB 管理 Buffer Pool 的 LRU 算法，是用链表来实现的。
- 在上图 的state 1 里，链表头部是 P1，表示 P1 是最近刚刚被访问过的数据页；
- 假设内存里只能放下这么多数据页；这时候有一个读请求访问 P3，因此变成state 2，P3 被移到最前面；
- state 3 表示，这次访问的数据页是不存在于链表中的，所以需要在 Buffer Pool 中新申请一个数据页 Px，加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾 Pm 这个数据页的内存，存入 Px 的内容，然后放到链表头部。
- 从效果上看，就是最久没有被访问的数据页 Pm，被淘汰了。
如果做全表扫描一个 200G 的历史数据表，平时不怎么访问，按照上述的算法就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。
对于一个正在做业务服务的库，这可不妙。你会看到，Buffer Pool 的内存命中率急剧下降，磁盘压力增加，SQL 语句响应变慢。
所以，InnoDB 不能直接使用这个 LRU 算法。实际上，InnoDB 对 LRU 算法做了改进。
在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。
改进后的 LRU 算法执行流程变成了下面这样。
- 上图中state 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成state 2。
- 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
- 处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
- 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
- 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。
这个策略，就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描 200G 的历史数据表为例，我们看看改进后的 LRU 算法的操作逻辑：
- 扫描过程中，需要新插入的数据页，都被放到 old 区域 ;
- 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域；
- 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。
可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。
小结
由于 MySQL 采用的是边读边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。如果客户端读结果不及时，会堵住 MySQL 服务端的查询过程，但是不会把内存打爆。
而对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。
全表扫描还是比较耗费 IO 资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。
扩展
大数据表查询分页
批量从 MySQL 中查询大量数据，每次通过限制起始+limit 数量的来分批次查询，后来有同事推荐使用MySQL JDBC中的 fetchSize()方法，不做分页通过一次大查询然后客户端流式读取来批量查询数据。
推荐做法：分批次取，然后每一批拿到最大的 id（主键），下次查询 where id>N。
如果客户端由于压力过大，迟迟不能接收数据，会对服务端造成什么严重的影响。
这个问题的核心是，造成了“长事务”。
- 如果前面的语句有更新，意味着它们在占用着行锁，会导致别的语句更新被锁住；
- 当然读的事务也有问题，就是会导致 undo log 不能被回收，导致回滚段空间膨胀。
undo空间增大，导致服务阻塞
用 mysqldump 对业务db做逻辑备份保存在客户端，客户端是虚拟机，磁盘很快满了，导致server端出现sending to client状态，更糟糕的是业务db更新频繁，导致undo表空间变大，db服务堵塞，服务端磁盘空间不足。
用如下实例来说明：
执行流程如下：
session B 执行完 100 万次 update 语句后，id=1 这一行处于什么状态呢？你可以从图 16 中找到答案。
session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。
带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。