千万级数据清洗案例总结 [mysql with mycat -> Elasticsearch]

背景

---

最近工作较忙，没有时间打理博客，失眠之余，写一写前阵子遇到的数据清洗案例。1000多万数据的清洗工作，从Mysql清洗到Es里面，起初的规划是用limit查询分片数据，然后逐一批次插入到es内部。然而，想法和实际操作完全两码事，细节性知识重要性尤其凸显

我们的目的

性能瓶颈

---

可能出现的性能瓶颈 从上图来看，我们粗略的可以这样划分我们的程序可能出现的瓶颈。 于是乎有以下几点考虑

• 为了使得清洗速度快，我们必须并发执行查询和插入操作
• 网络io以及硬盘IO是本例中的性能瓶颈，由于ES本身的刷盘策略，我们可以把IO任务简单化为网络IO。
• 存储中间件承受并发的能力，timeout以后丢失数据怎么办？
• sql质量问题，查询是否会出现慢查询的情况
  

执行模型

---

初步执行模型

细节阐述

---

由上图我们可以看到，红色部分，如何进行线程数据分片和查询插入函数是关键的。首先先说数据分片的思想

• 自增ID的重要性

---

如果我们的表内有唯一主键自增ID并且是主键，那么恭喜你。这基本上可以认为你的操作可以简化很大一部分，如上图中第一个红色区域，我们则可以利用自增ID进行分片，每一个线程负责一部分，或者用线程池轮询任务，直到做完为止，如果你采用多线程自建来做这件事情，那么需要注意的是ID运算初始化，或在线程运算期间保证分片计数变量的3大特性，用来保证执行正确的结果。但是，如果你采用线程池，则不需要如此繁琐，批量取的任务即可。在线程池外进行计数器的计算工作即可

• mycat的limit大坑

---

如果你的应用中使用了分库分表来处理相关的数据，在数据清洗时，请分开并行，速度快还安全，何乐而不为呢？不要用任何中间件，也在于博主孤陋寡闻了，mycat对于limit的拆分简直会让你惊掉大牙

select field from mycat.tableName limit 1000000,1000
转化为：
select field from database1.tableName limit 0,1000000
select field from database2.tableName limit 0,1000000
select field from databaseN.tableName limit 0,1000000

可以看到mycat对于limit语句的拆分过程，这样的sql，跑到生产的db上是致命的。谁试谁知道。mycat将会汇总这些结果给我们最终的结果。这将耗费很大的计算量和io操作。直接拖垮数据库。正确的操作方式应该是单个库，逐一操作方式。

• limit本身即是坑,请使用大于小于

---

当limit开始记录下标比较大的时候，请使用条件进行数据分片, 条件判定的效率是要优于limit很多的。 需要注意的是，条件判定尽量用主键，至少用索引字段。

select field form database1.tableName limit 1000000,1000
select field from database1.tableName where id > x and id < x+[size]

todo explain 原理

• 重试机制 [timeout捕获收集和单线程修补]

---

当我们进行大数据量清洗的时候，这将是大并发场景，对出入io的两个中间件，造成相应的io瓶颈。

伴随正常业务进行，这个量级，我们必须对timeout异常进行捕获，并且重试。当然值得注意的是我们也不能一直重试，在设定的重试上限内，如果最终仍然失败，我们则需要收集这些失败的请求。

这些失败的请求最终收集完成之后，减少了量级，我们即可在多线程程序执行完毕之后，进行单线程修复。最后即可check我们的数据是否正确导入。当然对于大数据量的情况下，可以转换思路，将这些数据io到其他的数据源上。

总之，一切都是思想的产物。

• todo Talk is cheap, show me code