记一次复杂分页查询的优化历程：从临时表到普通表的架构演进

1. 问题背景

在项目开发中，我们需要实现一个复杂的分页查询功能，涉及大量 IP 地址数据的处理和多表关联。在我接手这个项目的时候,代码是这样的

要知道代码里面的 ipsList 数据可能几万条甚至更多,这样拼接的sql,必然是要内存溢出的,一味地扩大jvm参数不是解决问题的根本

2.优化历程

2.1.临时表处理

为了解决内存溢出的问题,我尝试使用临时表,分批次处理ipsList数据

虽然解决了栈溢出的问题,但是数据量太大,频繁的io,单次查询的时间也大概在9秒左右,batchSize的值也不是越大越好,但是不管尝试多少,单次查询的时间最快也需要八秒多,如果我是用户,我觉得这是不能忍受的,但是对于程序员来说,能跑就行,想要快,那是另外的价钱,不过谁让我善良体贴又温柔呢,于是分析了一下,耗费时间的这一步无非是overhauledPlanMapper.insertBatchWithParams(params);

这个io操作,如果能异步并发处理的话,是不是就能解决查询慢的问题了,毕竟这也不涉及到共享变量的修改

(写到这里,手有些凉,于是插进了口袋,糟糕,早上买的两个鸡蛋忘记吃了,这一天天的上班把我脑子都上坏了)

2.2异步并发编程

到这里我觉得已经很完美的解决了这个问题了,等我部署上去运行的时候

我很无解,仔细研究了代码,我不明白为什么会出现临时表不存在的问题,看了日志我发现在insert语句还没有完成之前表就已经被drop了,一开始我以为是线程安全问题,于是我开始尝试加锁,使用synchronized(TEMP_TABLE_LOCK)全局锁,使用事务,确保所有操作在同一个事务中进行,但是不管我使用哪种方法,依然会存在临时表不存在的问题,这让我很百思不得其解.

在翻阅了很多资料之后我终于发现了问题所在,问题就出在临时表上,让我们看看临时表的特点

生命周期:

• 临时表(TEMPORARY TABLE):

• 仅在当前会话(Session)可见

• 会话结束时自动删除

• 不同会话间互不可见

可见性:

• 临时表: 只对创建它的会话可见

看到这我终于明白了为什么会出现这个问题了,我得表是在主线程创建的,由于 临时表仅在当前会话可见,不同会话间不能共享,所以子线程在并发插入的时候无法访问主线程创建的表,我悟了,但是我又触底反弹了,舔狗的剧本里舔狗才是主角,额…不好意思,走错片场了.

到这问题就简单了,我只需要把临时表修改成普通表就行了,只需要删除TEMPORARY关键字就行

到这里大功告成,已经完美解决了临时表不存在的问题,部署运行,尝试了多个batchSize的值,最终发现当batchSize=1000左右的时候,查询效率最高,单次查询时间在1.8秒左右

但是如果batchSize设置成固定的值的话,我觉得可能会出现个问题,如果ipsList的数量太大,就是批次太多,可能会有上百个批次,也就意味着可能会出现同时并发上百个线程,而你的cpu又不能同时负担这么多线程的话,就会出现线程阻塞,服务就会卡死,于是再优化一波

这是最终的版本,写注释呢并不是给我看,我是怕后面接手的人看不懂,毕竟每次改别人的代码我都是边骂边改的,口吐芬芳,鸟语花香,如芒刺背,如坐针毡,如鲠在喉…

虽然说这只是一个简单的查询,但是这中间优化的过程还是挺有意思的,思想和逻辑可以运用到其他项目中的各个业务中,对我的启发还是挺大的,所以记录一下,此篇文章为中午休息时间所写,以此共勉…