PageHelper是项目中常用的分页插件,它在完成分页的过程中会执行两条SQL:
- limit分页sql
- count查总数sql(在不关闭插件count的前提下)
对于百万级以上的数据表,只要添加好索引,limit语句的速度很快,但是count语句的速度可能会非常慢,导致出现慢sql。
下面结合项目中实际遇到的一次慢sql优化,对PageHelper插件的count效率优化做一个总结。
问题描述
项目中存在如下sql:
select count(0)
from table1 as a
left join table2 as b on a.aid = b.aid
left join table 3 as c on a.aid = c.aid and a.vin = c.vin
where 1 = 1
-- ifxxx1不是null
and a.xxx1 = xxx1
-- ifxxx2不是null
and b.xxx2 = xxx2
-- ifxxx3不是null
and c.xxx3 = xxx3;
这个sql的特点是:
- 用来计算总数,是pagehelper插件自动执行的
- 使用了left join,即以左表的数据为主
- where条件涉及了abc三张表,where条件将影响count的结果
腾讯云监控到的慢sql是:
select count(0)
from table1 as a
left join table2 as b on a.aid = b.aid
left join table 3 as c on a.aid = c.aid and a.vin = c.vin
where 1 = 1
即xxx1,xxx2和xxx3的查询条件不传入时会产生慢sql,通过explain分析,该慢sql走了全表扫描,扫描数据120w+,耗时2.5s左右。
下面的问题就是如何对这条sql进行优化。
PageHelper的count优化方法
方法1:关闭自动count
PageHelper.startPage(start, limit, false);
优点是简单粗暴,直接消灭自动执行的count语句,缺点是返给前端的参数里就没有真实的total了,前端只能做个假分页。
方法2:使用自定义的count语句代替自动执行的
在mapper.xml的这条查询语句下面再写一个查询:
<!-- 原sql -->
<select id="selectByAid" resultMap="BaseResultMap">
select * from table1
<where>
<if test="aid != null">
aid = #{aid,jdbcType=VARCHAR}
</if>
</where>
</select>
<!-- 自定义的count语句,注意名字后面拼接_COUNT -->
<select id="selectByAid_COUNT" resultMap="BaseResultMap">
select count(0) from table1
<where>
<if test="aid != null">
aid = #{aid,jdbcType=VARCHAR}
</if>
</where>
</select>
这样pagehelper就会自动使用自定义的count语句完成计数查询。
这个方法并不适用于我们上面的sql优化,因为上面的慢sql有多个输入的where过滤条件涉及到了abc三张表,where条件会影响实际的count结果。
假如上面的慢sqlwhere条件只涉及表a,那么我就可以使用自定义count语句去掉两次left join了:
select count(0)
from table1 as a
where 1 = 1
经实际检验,去掉两次left join能把执行时间从2500ms降低到80ms左右。
方法3:使用Mybatis的二级缓存
myabtis的二级缓存:
只要是mapper.xml的同一个方法,只要入参相同就会被缓存起来,下次查询时不再触发查询数据库。默认缓存时间一小时。
因为我上面的慢sql使用的频率不高(大概一天只有几次),而且要求每次都把最新的数据查出来,因此从业务的角度考虑不太适用于二级缓存。没有对这种方式进行实践,可以参考这篇blog:https://xiaozhang.blog.csdn.net/article/details/127036887
我的优化方案
综合考虑了以上三种方法,我决定选择方法2,但是需要结合实际的业务进行改造。
- 方法2不能直接适用的主要原因是因为我的慢sql里面有输入的where过滤条件,其中xxx2和xxx3分别涉及了table2和table3,因此必须要left join这两个表。
- 但是实际产生的慢sql,是没有任何where条件输入的情况,这也符合业务方的实际操作习惯:不输入查询条件直接点查询。
因此,我在业务代码中对输入的where过滤条件进行判断,如果没有输入xxx2或xxx3,那就使用关闭pagehelper自动的count,该用自定义的count语句:
<!-- 自定义的count语句,注意名字后面拼接_COUNT -->
<select id="selectByAid_COUNT" resultMap="BaseResultMap">
select count(0) from table1
<where>
<if test="xxx1 != null">
xxx1 = #{xxx1,jdbcType=VARCHAR}
</if>
</where>
</select>
此时去掉了两次left join,虽然还是会走全表扫描,但执行时间从2500ms降低到了80ms。
而当输入了xxx2或xxx3条件时,就不关闭pagehelper的自动count,但是此时因为输入了具体的过滤条件,整个扫描的行数减少了很多,也就不会出现执行时间过长的问题了。