一种基于主键索引和缓存机制的大数据表同步方法

2019独角兽企业重金招聘Python工程师标准>>>

在全量数据同步中，常常因为带宽的限制，需要对数据进行分页查询，在mysql中，分页的offset并不是跳过offset行，而是取offset+N行，然后返回前放弃前offset行，返回N行，当offset特别大的时候，效率就非常的低下。

select id,title from basic_program where deleted = 0 limit 0,5000;

/* 受影响行数: 0  已找到记录: 5,000  警告: 0  持续时间 1 查询: 0.015 sec. (+ 0.047 sec. network) */

select id,title from basic_program where deleted = 0 limit 5000000,5000;

/* 受影响行数: 0  已找到记录: 5,000  警告: 0  持续时间 1 查询: 12.106 sec. (+ 0.016 sec. network) */

 

可以看到，在offset增大时，查询效率严重受影响，在大数据表同步时，要获取到所有数据，需要花费几个小时或者更长时间，这在特定业务中是无法满足需求的。特别是在一些异常情况下，中断后，就需要重新来一遍，耗费时间和资源。

 

针对以上方法，我们可以简单做一个优化，

 

第一步：查询所有id，该步骤基本无性能损耗，主要在网络传输上，且id一般为数值类型，数据量要小很多，

select id from basic_program where deleted = 0;

/* 受影响行数: 0  已找到记录: 5,657,738  警告: 0  持续时间 1 查询: 0.000 sec. (+ 5.819 sec. network) */

第二步：获取部分ID列表，每次按照5000获取，然后进行in方法查询，

select id,title from basic_program where id in (45586 .... 83737247)

/* 大SQL查询 (44.0 KiB)，分割于 2,000 个字符 */

/* 受影响行数: 0  已找到记录: 5,000  警告: 0  持续时间 1 查询: 0.031 sec. (+ 0.031 sec. network) */

 

可以看到，查询效率大大提高，而且不会受分页限制。

 

以上方法，在大数据表同步时，只能在单任务进行，如果能进行分布式处理，多线程处理，同步效率就会更高。

所以，我们在第一步结束，可以将分页数据保存在缓存中，如redis中，提前做好分页，然后获取每页的ID列表，进行第二步查询。

这样，每个查询，相互不影响，可以并发处理，效率会大大的提高。

 

基本处理流程图如下：

 

 

分页数据缓存存储结构如下：

 

 

整个处理过程如下：

1）  同步网元调用数据源接口或者直接查询数据库，获取该大数据表的整个id列表。

2）  对该id列表进行分组，每1000条数据一组，顺序的进行编号，存储，该结构为list。

3）  每个list的key，作为数据，统一存储到缓存set中。

4）  同步网元随机的pop出分页key。

5）  利用该key获取list中id列表。

6）  使用id列表，调用数据源接口或者直接用id为索引，批量获取数据。

 

后续改进：

 

该方法基于缓存set pop的方式，一旦pop出来后，就直接删除该key，如果处理过程出问题，则该部分数据同步就丢失。MQ可以保证，在处理完成后，再进行ack回复，确保数据的可靠性，所以缓存部分可以使用mq替换。

 

基于mq的方案，在后续中也有限制，因为某一些id是单独存储，而调用接口，则是需要批量，获取1000之后，在调用一次接口，已提高效率。此种情况下，利用mq反而受限。

 

因此，在具体业务场景中，根据具体应用分析确定。

转载于:https://my.oschina.net/u/2441175/blog/896825