在实际的工作中,遇到如下的业务场景的计算处理优化问题:
- 几百行的大SQL关联查询所有数据
- 数据查完后对每条数据某些字段进行计算
- 最后将所有结果集数据进行存储
以上原先的数据处理方式在遇到各关联表数据量大的时候,把MySQL直接打崩了,即使有时候配置高程序能够运行也不稳定,并且耗时过长,各表几十万数据关联SQL查询能够达到1小时以上,实在令人震惊。以下记录一下个人的优化思路。
本文原创发布于Jianger’s Blog
针对这个场景,在不改动MySQL配置的情况下有三个方面可以优化,不过方向思路其实是一个:分批并行/并发处理,减少数据间影响
- SQL查询
- 代码中的计算处理
- 数据入库
SQL查询优化
语句拆分
- 将几百行的大SQL拆分成多个小SQL,分别执行处理
- 如果数据量比较大的话,每个小SQL再根据数据量分批查询。
SQL优化
- 根据语句酌情创建where\order\group by等语句上的字段索引
- 只查询必要的字段,避免使用select *
- 其他索引优化方法…
代码计算优化
使用线程池并发处理数据集,提高计算处理速度,而不是单线程遍历。
数据入库优化
当数据量比较多的时候,测试发现程序耗时有一半花在数据入库上了,那么有什么方法能够提高插入速度呢。
原先的情况是所有字段都在一张结果表上,即使并发查询处理出结果,在数据插入时由于锁的问题,仍旧在排队处理。
水平分区(分表)
通常按照日期或者主键水平分区(分表),这个对数据插入的速度相对垂直分表提升不多,当单表数据量大时进行数据插入耗时长,水平分区分表后单表数据量就显著降低下来,每次数据入库的耗时就比较均衡
垂直分表
由于原先结果表是个大框表,字段数40+,同时插入40多个字段数据很耗时,测试过即使在不垂直分表的情况下,每次插入10个字段,分4次插入,比一次性插入40个字段快得多。当垂直分表后配合上面的SQL语句拆分,就能同时并发处理不同数据并且同时入库不同字段了,因为这些字段分布在不同表上,锁表概率极大的降低。
最后根据项目现状和影响范围运用了多个优化方法,由于垂直分表影响较大,需要更改原先接口,所以没有使用这个方法,但其他优化方法都加上了。优化后完整计算处理流程如下:
- 多进程并行处理不同批次(即不同分区)的数据
- 每个进程内开启多个线程并发执行不同的SQL(处理不同字段),在需要代码计算处理的部分使用线程池并发计算处理
- 每个进程内对数据入库采用异步方式执行
最后几十万数据集的计算执行时间由1小时以上优化到2分钟