怎样从InnoDB表中导出大量冷数据

前言

导出是业务中常见的一个功能，算不上复杂。一般来说，我们从数据库中查询出数据，将其写入文件中，再给一个用户下载的入口即可。但是，如果要导出的数据量很大，比如上百万，这个时候，怎么样处理导出就是一个值得仔细思考的问题了。

我本人曾经在实际业务中遇到过一个这样的场景：导出上百万的数据，都是冷数据，数据库磁盘I/O飙升，buffer pool缓存使用率下降，最终导致正常业务收到影响。经过一番折腾，才找到了一个比较好的解决方案。今天就来和大家分享一下我的经验。

本文讲的对于MySQL5.6-MySQL8.2都适用，因为直到目前为止（MySQL8.2），InnoDB的buffer pool的实现原理并没有发生颠覆性改变。

什么是冷数据

此处说的冷数据，是指那些不常用的历史数据。如，记录/流水类数据，随着时间的推移，数据量会越来越大。但是，这类历史数据，一般来说，被访问的频率会越来越低，甚至有些数据，可能一年都不会被访问一次。这类数据，就是冷数据。

一般来说，当数据量增大时，我们都会进行冷热分离。此类记录/流水类数据，我们一般会按时间进行分表，也许是按年分表，也许是按月分表。把大量的冷数据，放到历史表中。这样，可以提高InnoDB的Buffer Pool的使用效率、减少热数据的查询时间和备份时间、并在一定程度上提高更新操作的性能。

异步导出的常用方法

一般来说，导出不适合进行同步导出，因为其性能通常不会太高。下面列出一些具体原因：

• 导出一般不会是导出直接从单张表中查询出的数据，往往需要聚合多张表，甚至是查询多张表+查询一些内部接口。
• 导出的数据量可能比较大。比如，导出几十万，甚至上百万数据，然后还要再从主表查询出数据之后，再调用其它接口查询数据，再进行聚合，再写入到表格中，这个过程，一定是耗时的。
• 有时可能需要处理很多文件，比如本人就处理，将多个文件打包成压缩包，供用户下载。
• 导出有时可能需要进行一些特殊的文件处理，比如生成pdf文件。

所以，一般来说，导出操作更适合进行异步处理。下面结合本人经验，给出异步导出的步骤：

1. 同步接口：用户发起导出请求，服务端接收到请求，将导出任务写入数据库中，投递消息到消息队列中，然后返回给用户。
2. 后台任务消费消息：后台任务消费消息，根据消息中的参数，进行导出操作，最终生成文件。中间可能需要更新导出记录表的导出进度字段。
3. 后台任务上传文件到文件服务器（现在一般是云存储，比如阿里的OSS，腾讯的COS），更新导出记录表的文件地址、状态、导出进度等字段。
4. 通知用户导出任务完成。
5. 用户下载文件：给用户生成签名的下载链接，用户通过该链接下载文件。

此处需要注意的是，步骤4不是必须的。一般来说，会有一个导出记录页面来展示导出记录，导出完成时不进行主动通知，用户也可以在导出记录页面中查看导出记录的状态，然后再进行下载。也可以采用简单的轮询方式来查询导出的进度。

从InnoDB的buffer pool说起

Buffer Pool简述

Buffer Pool是InnoDB用来存储表和索引数据的一块内存区域。通常来说，MySQL数据库服务器的大部分内存都会分配给Buffer Pool。

Buffer Pool被分为一个一个的数据页（page），每个数据页中有多条记录。为了高效管理这些页面，InnoDB使用链表将这些页面连接起来，并使用LRU算法来淘汰不常用的页面。

BufferPool的LRU链表

Buffer Pool使用非标准的LRU算法来管理页面链表。链表被分成两部分：new（young）和old，如下图所示：

上图来自MySQL官方文档。 默认情况下，old区域占的比例是3/8。

当需要淘汰页面时，InnoDB会淘汰一个最近最少使用的页面，然后将一个新页面添加到LRU链表的中部。

我们需要记住以下几点：

• 默认情况下，当访问一个old区域的页面时，会将其移动到new区域的头部。
• 在old区域的页面，不一定会被访问到。如果是 预读功能将其读取到Buffer Pool中，则有可能根本不会访问到。
• 为了防止访问次数极少的old页面被移动到new区域，导致new区域的页面被频繁淘汰，就要想办法让 冷页面不会迅速变热。InnoDB中的设置参数 innodb_old_blocks_time，就是用来控制old区域的页面在多长时间内不会被移动到new区域的头部的，其默认值是1000，单位是毫秒。

怎样做才能降低对正常业务的影响

在了解了InnoDB的buffer pool的LRU链表之后，我们就可以开始思考怎样做才能降低对正常业务的影响了。 我们的目标是：

• 尽可能的不读取不必要的数据到buffer pool中，减少不必要的重复读取。
• 尽可能的不让冷数据变热。 这两点的终极目的，是减少不必要的磁盘I/O。

那么，结合我们这个导出大量冷数据的业务场景，怎样做到以上两点呢？

首先，高效的使用索引，减少不必要的回表。 能用到覆盖索引最好，用不到的，也要保证where和order子句中的字段都在索引中。

其次，控制翻页深度，最好是只翻一页。 一页一页的读下去，会导致大量的重复读取。我们知道，limit n,100这个操作，可不是从n开始读取，而是从0开始读取的！所以，如果我们要导出的数据量很大，还要用传统方案，0-100，100-200，200-300这样翻页，那么，就会导致大量的重复读取。所以，我们要控制翻页深度，最好是只翻一页。即下一次的起始条件用上一次的结束条件（有些文章中也称其为游标），这样就可以保证不会重复读取数据。

最后，为以防万一，添加定期休眠机制或暂停机制。 可以通过定期休眠机制或暂停机制，来控制导出的速度，防止导出过快，防止磁盘I/O长期持续过高。

怎样杜绝对正常业务的影响

上面介绍的方案，是尽可能的减少对正常业务的影响。那么，如果一定要杜绝对正常业务的影响，又要导出大量冷数据，怎么办呢？

最好的方案,是正常业务和导出业务，不用同一个数据库。 可以将历史数据迁移到归档库中，也可以同步到其它类型的数据库中，从而彻底的将导出业务和正常业务隔离开来。当然，这个方案的成本是比较高的。

总结

本文介绍了如何从InnoDB表中导出大量冷数据，希望能对大家有所帮助。

再留一个思考：是否可以在产品设计层面，就杜绝导出大量数据呢？

参考资料

• MySQL是怎样运行的：从根上理解MySQL。
• MySQL5.7官方文档。
• MySQL8.0官方文档。

本文由 mdnice 多平台发布