导出优化

前提：数据量会很大，不能一批查询所有数据并导出，必须分批。
问题 : 该如何高效准确地分批？

开始测试，假设每批导出X个数据。

方案一（也是目前使用的方案）

1 查询最小id maxId
select id from test_x where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' ORDER BY id LIMIT 1;

2 查询最大id minId

select id from test_x where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' ORDER BY id DESC LIMIT 1;

3 循环查询
for ( id1 minId to maxId by X )
select id from test_x where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' and id BETWEEN id1 AND id1+X;
end

缺陷：
1 查询minId 和 maxId 耗时
2 每批不能保证X个数据，且第4步会重复很多次，效率不稳定。
例如
id稀疏， minId= 10， maxId = 1000W，符合where条件的数据只有1W条，循环（1000W-10）/ X 次。
id不稀疏， minId= 10, maxId = 2W, 符合where条件的数据有1W条，循环（2W-10）/ X 次。

方案二

1 分页查询limit X，并记录本次最大id- id1
select * from test_x where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' ORDER BY id LIMIT X;

2 如果上一步数据量>X, 继续分页查询limit X
select * from test_x where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' and id > id1 ORDER BY id LIMIT X;

3 重复步骤2，直到本次数据量小于X 结束

比较两种方案的耗时：
X = 20000
where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030'
符合条件的数量=33430

方案一
step1
step2
step3
0.001s
1.087s
？？
select id from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' ORDER BY id LIMIT 1;
35871
select id from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' ORDER BY id DESC LIMIT 1;
357017
select id from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' and id BETWEEN 35871 and 55871;
.... 后面要循环10多次。

方案二
子方案
step1
step2
step3
2.1
0.910s
3.090s
无
2.2
0.803s
1.660s
无
2.1
select * from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' order by id limit 20000;
maxId = 238264

select * from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' and id>238264 order by id limit 20000;
数据量 < 2W, 结束

2.2
select * from test_a
inner join (select id from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' order by id limit 20000) as tmp USING (id);

select * from test_a
inner join (select id from test_a where area_cd in ('上海') and zdezwerks_dep_v in ('L047') and erdat between '20171001' and '20171030' order by id limit 20000,20000) as tmp USING (id);
结束

结论：
用方案2.2

推荐阅读更多精彩内容