优化的前提条件,分页的sql允许先分页,联表并不影响最后的结果集,只是补齐所需字段信息.
一,pagehelper改造思路
1,5.0.0以上版本支持手动count查询语句.详见 https://github.com/pagehelper/Mybatis-PageHelper/
2,自定义拦截器,对有别名为"AS limitable" 的子查询sql进行sql拼接,将 本应拼接到末尾的limit ?,? 拼接到子查询sql末尾.
3,重新定位入参的位置.将limit的参数从末尾调整到limit子查询语句的地方.
4,用法,同pagehelper正常用法(版本要求5.0.4以上), 将pagehelper拦截器配置为自定义拦截器.
二,代码部分
1,mapper
<!-- 分页条件查询 优化 -->
<select id="findUserCount" resultMap="BaseResultMap">
select limitable.openId as openId,
insert(limitable.mobile,4,4,'****') AS mobile,
limitable.subscribeTime as subscribeTime,
limitable.unSubscribeTime as unSubscribeTime,
c.chanelName as chanelName
from
(select countBind.bindChannelId,countBind.openId,
countBind.mobile,countBind.subscribeTime,
countBind.unSubscribeTime,countBind.userTag
FROM countBind
WHERE
countBind.countId = #{countId,jdbcType=INTEGER}
<if
test="userCountReq.unSubendTime != null and userCountReq.unSubendTime !=''">
and countBind.unSubscribeTime <= #{userCountReq.unSubendTime}
</if>
<if
test="userCountReq.bindChannelIds != null and userCountReq.bindChannelIds.size()>0">
and countBind.bindChannelId in
<foreach collection="userCountReq.bindChannelIds" index="index"
item="idItem" open="(" separator="," close=")">
#{idItem}
</foreach>
</if>
<if test="userCountReq.tags != null and userCountReq.tags.size()>0">
and countBind.userTag in
<foreach collection="userCountReq.tags" index="index" item="tagItem"
open="(" separator="," close=")">
#{tagItem}
</foreach>
</if>
) AS limitable
left join
channel c
on
limitable.bindChannelId=c.channelId
where
c.countId = #{countId,jdbcType=INTEGER}
</select>
<!-- 分页条件统计Count 优化-->
<select id="findUserCount_COUNT" resultType="java.lang.Integer">
select count(*)
from
countBind b
where
b.countId = #{countId,jdbcType=INTEGER}
<if
test="userCountReq.unSubendTime != null and userCountReq.unSubendTime !=''">
and b.unSubscribeTime <= #{userCountReq.unSubendTime}
</if>
<if
test="userCountReq.bindChannelIds != null and userCountReq.bindChannelIds.size()>0">
and b.bindChannelId in
<foreach collection="userCountReq.bindChannelIds" index="index"
item="idItem" open="(" separator="," close=")">
#{idItem}
</foreach>
</if>
<if test="userCountReq.tags != null and userCountReq.tags.size()>0">
and b.userTag in
<foreach collection="userCountReq.tags" index="index" item="tagItem"
open="(" separator="," close=")">
#{tagItem}
</foreach>
</if>
</select>
2 pagehelper拦截器