PageHelper插件count效率优化

PageHelper是项目中常用的分页插件，它在完成分页的过程中会执行两条SQL：

1. limit分页sql
2. 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的特点是：

1. 用来计算总数，是pagehelper插件自动执行的
2. 使用了left join，即以左表的数据为主
3. 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，但是需要结合实际的业务进行改造。

1. 方法2不能直接适用的主要原因是因为我的慢sql里面有输入的where过滤条件，其中xxx2和xxx3分别涉及了table2和table3，因此必须要left join这两个表。
2. 但是实际产生的慢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，但是此时因为输入了具体的过滤条件，整个扫描的行数减少了很多，也就不会出现执行时间过长的问题了。