MySQL大SQL和大数据量处理优化实践

在实际的工作中,遇到如下的业务场景的计算处理优化问题:

  • 几百行的大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分钟

本文已结束 ❤ 感谢阅读
觉得文章不错,赞赏站长一包辣条( •̆ ᵕ •̆ )◞ ❤
0%