Skip to content

大查询操作耗尽内存

一、 全表扫描对 Server 层的影响

  • 查询命令示例： mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

  • 数据获取与发送流程（边读边发）：

    1. 获取一行数据，写入 net_buffer (由 net_buffer_length 定义，默认 16k)。

    2. 重复获取行，直到 net_buffer 写满。

    3. 调用网络接口将 net_buffer 中的数据发送出去。

    4. 若发送成功，清空 net_buffer，继续下一行。

    5. 若网络栈 (socket send buffer) 写满 (返回 EAGAIN 或 WSAEWOULDBLOCK)，则暂停读取数据，等待网络栈可写后再继续发送。

  • 内存占用分析：

    • MySQL 内部为查询结果分配的内存最大为 net_buffer_length。

    • socket send buffer 大小也有限制 (如 /proc/sys/net/core/wmem_default)。

    • 因此，服务端不会保存完整的、巨大的结果集，从而避免内存被打爆。

  • 客户端接收速度的影响：

    • 如果客户端接收慢，会导致 MySQL 服务端因结果发不出去而阻塞，事务执行时间变长。

    • show processlist 中 State 为 "Sending to client" 表示服务端网络栈已满，等待客户端接收。

  • mysql_use_result vs mysql_store_result：

    • mysql_use_result (客户端使用 --quick 参数)：读一行处理一行。若客户端处理慢，易导致服务端 "Sending to client"。

    • mysql_store_result (默认)：将查询结果完整保存到客户端本地内存。适用于结果集不大的情况。若结果集过大，可能耗尽客户端内存。

  • 应对大量 "Sending to client" 状态：

    • 业务层面优化查询，减少返回结果量。

    • 评估返回大量结果的合理性。

    • 临时方案：增大 net_buffer_length 参数。

  • 区分 "Sending to client" 和 "Sending data"：

    • "Sending data"：表示查询正在执行的任意阶段，并非特指正在发送数据。可能是发送元数据、执行计算、等待锁等。

    • "Sending to client"：明确表示线程处于“等待客户端接收结果”的状态。

二、 全表扫描对 InnoDB 引擎的影响

  • Buffer Pool (BP) 的作用：

    • 加速更新： 配合 WAL (Write-Ahead Logging)，避免随机写盘。

    • 加速查询： 缓存热点数据页，提高内存命中率。

  • 内存命中率：

    • 通过 show engine innodb status 查看 Buffer pool hit rate。

    • 稳定服务的线上系统，命中率通常要求在 99% 以上。

  • Buffer Pool 大小：

    • 由 innodb_buffer_pool_size 参数确定，一般建议为物理内存的 60%-80%。

    • 通常小于磁盘数据总量，因此需要淘汰机制。

  • InnoDB 的 LRU (Least Recently Used) 淘汰算法改进：

    • 基本 LRU 算法的问题： 全表扫描冷数据时，会将 Buffer Pool 中的热数据全部淘汰，导致业务查询性能急剧下降。

    • InnoDB 改进的 LRU 算法：

      1. 将 LRU 链表按 5:3 比例分为 young 区域 (靠近头部，约 5/8) 和 old 区域 (靠近尾部，约 3/8)。

      2. 访问 young 区域数据页： 移到链表头部（与基本 LRU 相同）。

      3. 访问新的、不存在于链表的数据页： 淘汰链表末尾数据页，新数据页插入到 old 区域的头部 (LRU_old 指向的位置)。

      4. 访问 old 区域数据页的判断：

        • 若在 LRU 链表中存在时间 超过 1 秒 (由 innodb_old_blocks_time 控制，默认 1000ms)，则移到链表头部 (young 区域)。

        • 若存在时间 短于 1 秒，位置保持不变。

  • 改进后 LRU 对全表扫描的处理：

    • 扫描过程中新插入的数据页放入 old 区域。

    • 由于顺序扫描，单个数据页在短时间内被多次访问，但间隔通常不超过 1 秒，因此保留在 old 区域。

    • 后续扫描不再访问该数据页，它没有机会进入 young 区域，很快被淘汰。

  • 收益： 全表扫描冷数据时，对 young 区域影响小，保证了正常业务查询的 Buffer Pool 命中率。

三、 总结

  • MySQL Server 层通过“边算边发”机制避免因大结果集耗尽内存。

  • InnoDB 引擎通过改进的 LRU 算法，有效控制全表扫描对 Buffer Pool 的冲击，保护热数据。

  • 尽管有这些机制，全表扫描仍消耗大量 IO 资源，应避免在业务高峰期在主库执行。