从 MySQL 大量数据清洗到 TiDB 说起

作者： luzizhuo 原文来源：https://tidb.net/blog/be54d91d

【是否原创】转载

【首发渠道链接】https://www.cnblogs.com/yanzige/p/15129222.html

【正文】

业务场景

公司主要做的业务是类似贝壳的二手房租售，数据库中存了上亿级别的房源数据，之前数据库使用的是 mysql，后面需要将 MySQL 数据库切换成了 TiDB，在切换的过程中，需要将老库的数据经过数据清洗后再存入新库（因为有一些表结构的设计变了），其中我们处理的一个逻辑就是将房间下业主信息从老库清洗到新库：我们需要按照城市维度，查询新库所有的房间，然后拿着新老库的房间对应关系，再到老库中找到所有对应的房间，然后通过房间再找到每个房间对应的业主信息，最后将业主的不同维度信息（一共 5 个维度信息）清洗到新库的不同数据表中。下面我就简单描述一下数据清洗过程中遇到的各种问题以及解决方案，所有问题都会附上真实的案例和说明

从问题入手选定处理方案：

问题：

1. 在清洗过程工，我们无法将某个城市的几百万甚至上千万的房间信息一次性查询出来，再去找所有房间的业主信息，这样内存肯定会撑爆；

2. 数据清洗过程中肯定不能一条一条的新增数据，这样的话几百万（举例 300W）的房间数据，有 5 个维度需要新增数据，那么就会一条一条的新增 300W 房间对应维度的数据，就会操作 300W*5 次，效率低下；

3. 数据清洗后批量插入新表的时候也不能一次性插入 300W（每个表插入 300W，5 个维度分别插入 5 个表，即插入 5 次 300W 的数据）。

处理方案：

先查询出需要清洗的数据总量，然后按照某个量（比如：1000 条）进行分页查询出具体的数据，然后清洗这 1000 条房间对应维度的数据并插入新库中，再清洗下一个 1000 条数据，直到把所有数据清洗完成。

按照上面选定的处理方案，分页进行清洗数据

在上面处理方案出来后，下面就是在程序开发过程中遇到的一些具体问题，分页查询的逻辑其实就是常用的 SQL 的 limit m, n，通过 page 和 pageSize 来进行分页查询，再使用 limit m,n 进行分页查询的时候又遇到下面几个问题：

1. 分页查询查询和处理新增数据，按照多大的量来进行分页查询，是一次性查询 5000 条房间还是 1000 条房间来处理对应数据的清洗，使得查询和处理的效率最高效？

2. 如果一次性查询和处理较少的数据量，比如每次分页查询出 100 条数据来进行清洗，如果某城市有 800W 的数据，分页查询需要查询处理 80000 次，这个处理次数是否过多？

3. 使用常规的 limit m,n 的方式进行分页查询，那么越查询到靠后的页数（ limit m,n 语句的查询时间与起始记录的 m 位置成正比）查询就会变得越慢，如何处理？

解决方案：

注：下面所有的数据都是在公司的机器上面得出的效率数据，大家在使用的时候以实际为准，这里只是提供解决思路。

批量处理

下面先附上一张我们和 DBA 的聊天来引出问题：

公司大量业务都开始使用 TiDB，很多数据都需要从 MySQL 迁移到 TiDB，在迁移过程中，批量新增都会遇到一个问题，就是随着批量新增的数据量变大，耗时巨慢，DBA 说的是 100 条以内就非常快，那么这个条数多少条对于我们业务处理是最合理的啦？下面就是一个论证过程。

下面直接上数据，后面会对数据进行说明：

关注（注意横纵坐标的含义）：

• 批量新增 409 条数据变成 539 条数据，耗时却从 0.535 变到 2.026 秒，多了 130 数据，耗时却多了 1.5 秒左右；

• 批量新增 536 条数据到 689 条，多了 150 条数据，耗时确多了 3 秒多；

• 批量新增 689 条到 793 条，数据多了 104 条数据，耗时确多了 5 秒；

• 批量新增 793 条到 997 条，数据多了 204 条，耗时确多了 17 秒。

根据上图生成的数据耗时性能坐标图，斜率越低说明性能越好。

从图中粗略的可以看出，在 409 到 539 条之间，应该有一个合理的性能保证值，我们姑且认为大概在批量处理 500 条的时候，性能是一个分水岭，即：在批量处理 500 条房间以内对应的数据，性能较好，超过 500 条后性能开始按照指数增长的方式下降。（注：为了表明数据的真实性，不是我自己瞎编乱造的数据，附上公司数据清洗的几张 log 日志图，用于说明情况。）

批量处理 1 条房间对应的数据，耗时 0.121 秒

批量处理 54 条房间对应的数据，耗时 0.262 秒

批量处理 276 条房间对应的数据，耗时 0.481 秒

批量处理 409 条房间对应的数据，耗时 0.535 秒

批量处理 539 条房间对应的数据，耗时 2.026 秒

批量处理 793 条房间对应的数据，耗时 10.451 秒

批量处理 997 条房间对应的数据，耗时 27.124 秒

** 说明：** 从日志可以看出，我们批量新增对应 m 个房间对应的数据，实际上需要处理 6 个维度（5 个业务维度 +1 个清洗记录维度）的数据：

1. 房间对应【业主档案维度】；

2. 房间对应【查看电话记录信息维度】；

3. 房间对应【跟进记录信息维度】；

4. 房间对应【业主基本信息维度】；

5. 房间对应【业主详情信息维度】；

6. 房间对应【导入记录维度】（防止重复导入）。

即：一个房间信息，可能下面没有联系人的信息，所以该房间就没有业主档案，也可能有多个联系人，那么这时就会有业主档案，并且该业主档案就对应多个业主信息（业主详情信息要根据查询看是否存在业主详情信息），并且该房间下的业主，如果经济人跟进维护及时，那么就会有多条查看电话记录信息和跟进记录。

所以在查询 1000 条的房间信息的时候，实际导入数据的效率取决于我们剩下 5 个业务维度的数据量，在此次文档中我们暂且按照 1 个房间信息分别对应 1 条业务维度来说明，实际业务可以根据自身实际导入时间来处理。我们来看下面这两个 1000 条房间对应 998 条数据的导入时间：

从上面也可以看出来，处理 998 条房间时，对应 5 个业务维度（业务维度数据字段较多）都比较耗时，其中批量新增查看电话记录耗时 10 秒 +（这个日志当时没有记录插入多少条记录查看电话记录数据，后期优化一下以便更加清楚的查看插入数据的耗时情况），而批量新增业主档案信息也是在 1300+ 条数据，耗时也是在 10 秒左右。就这个也不难看出，单独批量插入 1000 条左右的数据，性能也比较低。

总结如下，在批量新增数据的时候，插入数据的耗时：

1. 和你的业务数据复杂度有关 ，插入 1000 条 2 个字段肯定比你插入 1000 条同级别类型的 20 个字段数据快很多（大家这时回看上面的所有日志，从 1 条到 998 条，会发现倒数第二行插入 imported 表的数据都比较快，都在是 150 毫秒以内，是因为我们 imported 和业务无关，是用来记录我们哪些房间数据已经被清洗了，下一次清洗的时候防止重复清洗，所以插入的数据字段较少，性能从 1 到 998 变化不大，但不大并不表示没有，观察发现随着数据量增多耗时也在增多，如果单独统计 imported 的批量新增性能变化点可能是在 5000，也可能是在 8000，但是这个对于我们业务没有意义，也不是我们这个清洗的瓶颈点，所有 我们整篇讨论是建立处理对应 N 条房间的维度，而不是某一个业务维度的耗时，因为单个维度耗时对于我们业务是没有统计意义，也无法对我们整体数据清洗性能优化提供太多的帮助 ）。

2. 插入的数据在某个值的时候性能会变低 ，那么我们在批量新增的时候尽量不要超过这个值，按照我们业务测试来看是在 500 条左右。

分页查询

上面讨论的是每一次处理，即批量处理大概在多少条数据比较合理，下面讨论的就是我们在处理分页查询的问题。

在分页查询时，我们使用普通的 limit m,n 每次查询 1000 条房间数据来处理，整个过程如下：

使用常规的 limit m,n 的方式进行分页查询，那么越查询到靠后的页数查询就会变得越慢（ limit m,n 语句的查询时间与起始记录的 m 位置成正比），日志分页查询变慢截图如下：

从上面几张图可以明显看出，当查询到后面分页的数据的时候，耗时明显增加（这个是最早开始清洗的时候，已经没有日志文件了，截图是之前和 DBA 聊天的截图，所以比较模糊，系统只保留最近一个月的日志记录，从截图看查询速度还比较快，是因为刚开始清洗到系统的房间较少，所以查询前几十页都是几百毫秒内）。

那么如何解决这种查询啦？那么我们可以通过主键来限制每次查询的数据集，即后一次查询的查询范围应该排除之前已经查询过的数据，这种思想有点类似于移动游标，每次查询通过主键 rid，查询的范围保证 rid> m , 这个 m 是上一次查询记录的最大值（所以在查询的时候需要主键排序），于是查询就变成了 where rid > m limit 0 , n , 其中 m 就充当了游标点，通过移动游标，查询指定 n 条数据，这时游标的作用就有两个：1、定位查询的数据 2、缩小查询数据集范围。

总数据量是 3430173 条数据，1000 条清洗一个批次，需要清洗 3431 批次，从 0 开始计数要清洗到 3430 批次结束

加入游标后查询的速度加快，日志截图如下（查询的数据最开始是在 6 秒左右，是因为现在清洗过来的房间数据已经好 1000W+ 的数据，所以大家看到查询的数据是从 6 秒开始）

总结： 通过主键 id 值的移动来实现游标的方式控制查询的数据集的大小，将查询耗费时间随着查询分页的后移来而变得越来越短。

换种思路

我们虽然优化了分页查询效率的问题，但是从上图我们不难看出，如果 100W 的数据量进行分页查询还是会经历 1000 次的查询，那么我们如何解决多次查询的问题？我们最早的问题告知我们不能一次性查询百万或者千万条数据，因为这样内存吃不消，但是我们换种思路，也不是说一次只能查询最优分页查询的数据量（解决方案第 1 步中统计出来是 500 条左右），于是有了下面的演进：

通过内存存储减少查询次数，我们给到外部的貌似是 1000 条一页一页的查询，实际我们会一次查询大于 1000 条数据，下面以 2W 举例，就是分页查询的时候我们是一次性查询 2W 条数据，然后遍历每 1000 条处理一次，直到 2W 条数据处理完成，在分页查询下一个 2W 条到内初进行处理，依次类推，直到处理完成。

总结：减少查询次数，不能一次性查询出上百万的数据，那我一次性查询出 1W 或者 2W 的数据，然后利用内存再将这 2W 的数据进行 1000 条按照一批次处理，这样就 将 20 次分页查询变成了 1 次分页查询 + 20 次内存运算处理 。从而大大加快数据清洗的效率。

数据清洗说明

在实际数据清洗过程中，还有很多复杂逻辑，不过都是偏业务层面，没有分享出来的必要性；其次上面的数据，如各种性能的值需要根据各自业务数据的复杂性自行测试，找到各自性能的最优处理值。

思考：

下面贴一张截止到 2021 年 8 月 22 日靠前的几个城市的房间数据：

1. 由于我们业主档案列表分页查询，查询过程因为是以房间为维度，关联项目表、分期表、楼栋表、单元表、业主档案表（后面还加了 7 天联系记录）等，从上图大家可以看到成都、上海的房间数据都超过了 700W+ 的数据，那么在关联多个表查询，固然会存在查询效率慢的原因，我们除了在 SQL 层面本身进行优化，是否可以利用到上面的一些思想？比如用户查询 1、2、3、4…等分页（假如每页 20 展示条）数据，我们是否只需要真实的查询出第一页数据（快速响应），然后利用假分页，如查询出 200 条数据放入缓存，并设置过期时间，假如用户翻页到 9 页（一共缓存 10 页数据）的时候（预估用户行为，提前到第 9 页就进行下一次查询，防止用户翻到第 10 页在进行查询花费大量时间），提前把下一个 200 条数据查询出来。对于客户来说，他的分页是正常分页，而我们在底层代码通过假分页和真分页呈现数据，并且因为我每次只缓存了 200 条数据，多个用户操作，不会影响我缓存的内存，其次因为我缓存数据较少，且都设置过期时间，数据实时性也能得以保障。另外如果有统计功能，其实第一页也是可以通过分析用户的查询行为进行定期缓存。这样就在满足业务复杂业务需求的同时，保证客户的使用体验。

2. 数据清洗的量进行动态配置，下面先来两张图片说明情况：

这个是某次清洗房间关联表（新库房间 id 和老库房间 id 的对应关系）84W+ 的数据，而其中存在业主档案的数据量却只有 28974，大概就是每 1000 个房间有 34 条对应业主档案可能需要清洗。

而第二张图是另外一个城市，房间关联表只有 27171 条数据，对应的可能存在业主档案的数据有 22635 条，就是大约 1000 条房间数据有 833 条对应业主档案可能需要清洗。

因为我们默认是每 1000 条房间清洗一次，那么上面第一种情况每一批次 1000 条数据只有 34 条左右的业主档案被清洗到新库中，离性能最优点 500 差的有点远，故性能有点浪费。第二种情况每一批次 1000 条数据又有 833 条左右的业主档案需要被清洗到新库中，离性能最优点 500 也差的有点远，这时我们这个每批次清洗 1000 条对应房间的数据就有点设置不太合理，于是我们可以通过这几个值动态生成需要清洗每批次的数据量，来保证每批次清洗数据在 500 条左右，​使得每次清洗都可以在最优的效率下执行。