一次坏代码优化引起的对数据库关键字in和‘=’的思考
一.背景是这样
测试同学讲有个界面反应太慢了,什么程度呢,大概是100多条数据得花接近一秒的时间,确实影响用户体验,于是我就来动手改造了。
二.排查过程
1.先看了日志。好家伙,就查104条数据,执行了416条SQL,刚好是4n倍,原因也很明显,因为表中的考试成绩字段和身份证号字段都是通过联表查询获得,然后拼接回去的。
2.开始追代码。因为系统比较小巧,通过代码review很快找到了出问题的代码段。
3.来到问题代码段。写了段侵入性的代码来统计具体执行时间,然后得到了如图的结果:
104条数据消耗632ms的查询时间。随着数据量的增大,执行SQL数量是数据量的4n倍,所造成的时间消耗肯定是不能接受的。问题代码长这样:
至此,问题原因算是找到了。SQL执行次数随着数据量的规模呈线性增长,造成的时间消耗增加。
4.解决思路。问题就出在循环调用了JPA的findOnexxx方法,主要思路就是将findOnexxx方法改成findxxInxx,其他部分再做对应修改即可。就像这样:
5.达到的效果。经过这一轮操作,最后这个方法的执行时间**小于200ms,**心满意足,给个截图。
6.复盘解决过程。首先是根据现象,看到了控制台中突然暴增的sql执行信息;接着复查代码,发现了循环调用findOnexxx方法的问题;解决思路也很自然,既然都是为了获取104个examRecord记录,依旧经验,一次性取104个出来大抵是比循环104次去获取更快的;判断的依据是IN和’='的区别。
三.继续探究
- 关于两条SQL的执行计划分析
一次性取N条出来是用的IN关键字,对应执行了1条SQL;而循环取N条出来用的是WHERE关键字,对应执行了N条SQL,而SQL执行总时间 = 客户端驱动处理时间 + 数据库执行SQL时间 + 通信时间,将
程序运行时的SQL拿出来分析了下,得到如下结果:
可以看到在小数据规模下,’=‘比IN的执行时间稍快,但是执行104次’=‘当然比执行一次IN时间多得多了。那么为什么’='比IN更快些呢?
- 关于IN和’='在数据库中实现上的区别
首先是IN关键字的实现原理,步骤是这样的:只执行一次,把B表的所有id字段查出来并缓存起来,再比较A表记录的id属否与其相等,相等则放入结果集中,这个过程持续到遍历完整个A表。值得注意的是,**这个过程中的查找是不会因为字段是否有索引而改变的。**代码上表示,大致是如下:
List resultSet={};
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
接下来是关于WHERE关键字,PostGreSQL中有三种JOIN算法,分别是:NestedLoop,HASH JOIN ,以及SORT MERGE JOIN。NestedLoop比较出名,也是MySQL的唯一JOIN算法。在被连接的数据子集较小的情况,并且连接的表中有索引,并且索引选择性较好的时候.就会使用这个NestedLoop算法,是的,会走索引。咋们这个业务背景下,数据库就采用的这个算法执行WHERE语句。
关于NestedLoop算法在执行时间上的主要消耗,主要获取驱动表结果集所需要的循环次数。咋们这里因为走了索引,所以获取**Select xxxxx Where = ‘ksxxBh’**中的ksxxBh的时间复杂度是O(1),但是主查询中用于比较的循环不会降低,因此总的时间复杂度是O(n)。
最后,这个问题绕了一圈儿,回到了走没走索引的差别。在WHERE关键字走了字段索引的背景下,IN不会去走索引而‘=’会走索引,因此’='更快些。