33 | 我查这么多数据，会不会把数据库内存打爆？

---

MySQL45讲

实践篇

33 | 我查这么多数据，会不会把数据库内存打爆？

全表扫描对 server 层的影响

假设对一个 200G 的 InnoDB 表 db1. t 执行一个全表扫描。要把扫描结果保存在客户端，会使用类似下面的命令：

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

InnoDB 的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表 t 的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。

服务端并不需要保存一个完整的结果集。 取数据和发数据的流程如下：

1. 获取一行，写到 net_buffer （每个线程一个）中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer） 写满了，进入等待。直到网络栈重新可写，再继续发送。
   

从这个流程中，可以看到：

• 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；
• socket send buffer 也不可能达到 200G（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

MySQL 是“边读边发的”。

如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，使得这个事务的执行时间变长。

让客户端不去读 socket receive buffer 中的内容，然后在服务端 show processlist 看到的结果。

State 的值一直处于“Sending to client”，表示服务器端的网络栈写满了。

因此，对于正常的线上业务来说：

• 如果一个查询的返回结果不多，建议使用 mysql_store_result 接口（把查询结果保存到本地内存）。
• 如果查询的返回结果很多，则可能导致客户端占用大量的内存，这种情况下就需要改用 mysql_use_result 接口（不缓存，读一个处理一个）。

mysql_sotre_result 把结果保存在客户端本地内存，直接把所有结果读过来，可能导致客户端占用大量的内存；
mysql_use_result 则是客户端一行一行的从 Server 读取数据，如果每行数据都有业务处理逻辑的话Server 就要等待，会造成长事务。

如果 MySQL 里看到很多个线程都处于 “Sending to client” 这个状态，就需要优化查询结果，并评估这么多的返回结果是否合理。而如果要快速减少处于这个状态的线程，将 net_buffer_length 参数设置为一个更大的值是一个可选方案。

一个查询语句的状态变化如下（略去了与 Sending data 其他无关的状态）：

• MySQL 查询语句进入执行阶段后，首先把状态设置成“Sending data”；
• 然后，发送执行结果的列相关的信息（meta data) 给客户端；
• 再继续执行语句的流程；
• 执行完成后，把状态设置成空字符串。

“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_buffer_pool_size 小于磁盘的数据量是很常见的。如果一个 Buffer Pool 满了，而又要从磁盘读入一个数据页，那就需要淘汰一个旧数据页。

InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。

示例

假设按照 LRU 算法，扫描一个 200G 的表，而这个表是一个历史数据表，平时没有业务访问它。

按照 LRU 算法扫描，就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说，Buffer Pool 里面主要放的是这个历史数据表的数据。

对于一个正在做业务服务的库，会导致 Buffer Pool 的内存命中率急剧下降，磁盘压力增加，SQL 语句响应变慢。

所以，InnoDB 对 LRU 算法做了改进。

在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成 young 区域和 old 区域。 图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。

改进后的 LRU 算法执行流程如下：

1. 图中状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
2. 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
3. 处于 old 区域的数据页，每次被访问的时候都要判断。 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。 1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。

这个策略，就是为了处理类似全表扫描的操作量身定制的。还是以扫描 200G 的历史数据表为例，改进后的 LRU 算法的操作逻辑如下：

1. 扫描过程中，需要新插入的数据页，都被放到 old 区域 ;
2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过 1 秒，因此还是会被保留在 old 区域；
3. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是 young 区域），很快就会被淘汰出去。

这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。