pagehelper联表分页查询性能优化--（sql允许先分页，后联表）优化的前提条件，分页的sql允许先分页，联表并不影响最后的结果集，只是补齐所需字段信息.
⼆，代码部分
1,mapper
<!-- 分页条件查询优化 -->
<select id="findUserCount" resultMap="BaseResultMap">
select limitable.openId as openId,
bile,4,4,'****')          AS mobile,
limitable.subscribeTime as subscribeTime,
limitable.unSubscribeTime as unSubscribeTime,
c.chanelName as chanelName
from
(select countBind.bindChannelId,countBind.openId,
countBind.unSubscribeTime,countBind.userTag
FROM countBind
WHERE
<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
</select>
<!-- 分页条件统计Count  优化-->
<select id="findUserCount_COUNT" resultType="java.lang.Integer">
select count(*)
from
countBind b
where
<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"
<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
/*
* The MIT License (MIT)
*
* Copyright (c) 2014-2017 abel533@gmail
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE  * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN  * THE SOFTWARE.
*/
import flect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.cache.CacheKey;
import org.utor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import com.github.pagehelper.Dialect;
import com.github.pagehelper.PageException;
import com.github.pagehelper.PageException;
import com.github.pagehelper.cache.Cache;
import com.github.pagehelper.cache.CacheFactory;
import com.github.pagehelper.util.MSUtils;
import com.github.pagehelper.util.StringUtil;
/
**
* 对pagehelper联表分页查询进⾏sql优化.(待优化sql的前提条件.可以先分页后联表) ，指定标识符"AS limitable" * 为分页标识的sql. 末尾带有 AS limitable 的⼦查询会拼接上limit进⾏先分页.然后再外联查询
*
* @author mumu/liuzh/abel533/isea533
* @version 5.0.0
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
@Intercepts({
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class }),
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class }), })
public class MumuPageInterceptor implements Interceptor {
//private static final Logger logger = Logger(MumuPageInterceptor.class);
// 缓存count查询的ms
protected Cache<String, MappedStatement> msCountMap = null;
private Dialect dialect;
private String default_dialect_class = "com.github.pagehelper.PageHelper";
private Field additionalParametersField;
private String countSuffix = "_COUNT";
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
Object[] args = Args();
MappedStatement ms = (MappedStatement) args[0];
Object parameter = args[1];
RowBounds rowBounds = (RowBounds) args[2];
ResultHandler resultHandler = (ResultHandler) args[3];
Executor executor = (Executor) Target();
CacheKey cacheKey;
BoundSql boundSql;
// 由于逻辑关系，只会进⼊⼀次
if (args.length == 4) {
// 4 个参数时
boundSql = ms.getBoundSql(parameter);
cacheKey = ateCacheKey(ms, parameter, rowBounds, boundSql);
} else {
// 6 个参数时
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
List resultList;
// 调⽤⽅法判断是否需要进⾏分页，如果不需要，直接返回结果
if (!dialect.skip(ms, parameter, rowBounds)) {
// 反射获取动态参数
String msId = ms.getId();
Configuration configuration = ms.getConfiguration();
Map<String, Object> additionalParameters = (Map<String, Object>) additionalParametersField
.get(boundSql);
// 判断是否需要进⾏ count 查询
if (dialect.beforeCount(ms, parameter, rowBounds)) {
String countMsId = msId + countSuffix;
Long count;
// 先判断是否存在⼿写的 count 查询
MappedStatement countMs = getExistedMappedStatement(configuration, countMsId);
if (countMs != null) {
count = executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
count = executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
} else {
countMs = (countMsId);
// ⾃动创建
if (countMs == null) {
// 根据当前的 ms 创建⼀个返回值为 Long 类型的 ms
countMs = wCountMappedStatement(ms, countMsId);
msCountMap.put(countMsId, countMs);
}
count = executeAutoCount(executor, countMs, parameter, boundSql, rowBounds, resultHandler);    }
// 处理查询总数
// 返回 true 时继续分页查询，false 时直接返回
if (!dialect.afterCount(count, parameter, rowBounds)) {
// 当查询总数为 0 时，直接返回空的结果
return dialect.afterPage(new ArrayList(), parameter, rowBounds);
}
}
// 判断是否需要进⾏分页查询
if (dialect.beforePage(ms, parameter, rowBounds)) {
/
/ ⽣成分页的缓存 key
CacheKey pageKey = cacheKey;
// 处理参数对象
parameter = dialect.processParameterObject(ms, parameter, boundSql, pageKey);
// 调⽤⽅⾔获取分页 sql
String pageSql = PageSql(ms, boundSql, parameter, rowBounds, pageKey);
// String());
/**
* 对sql进⾏改造
*/
String changer = "";
int limitCount = 0;
if (ains(PageSqlResolver.LIMIT_SIGN_EX)) {
changer = PageSqlResolver.LIMIT_SIGN_EX;
limitCount = 2;
} else if (ains(PageSqlResolver.LIMIT_SIGN)) {
changer = PageSqlResolver.LIMIT_SIGN;
limitCount = 1;
}
pageSql = solveLimit(pageSql, changer);
List<ParameterMapping> parameterMappingList = ParameterMappings();
// 参数调度
/
** 参数偏移，需要调整limit 参数 */
if (ains(PageSqlResolver.SQL_SIGN)) {
String sqlInCount = pageSql.substring(pageSql.indexOf(PageSqlResolver.SQL_SIGN));
// 统计limitable之后还有多少个?//
Integer count = 0;
for (int i = 0; i < sqlInCount.length(); i++) {
if (sqlInCount.indexOf("?", i) != -1) {
i = sqlInCount.indexOf("?", i);
count++;
}
}
/
/ 获取临时的list
List<ParameterMapping> tempParameterMappingList = ParameterMappings();
// count>0则 limit后⾯有参数，需要将limit的参数往前提
int size = tempParameterMappingList.size();
if (count > 0) {
// 只需要移动⼀个
if (limitCount == 1) {
// 获取最后⼀个参数
ParameterMapping p1 = ve(size - 1);
// 将参数插⼊到指定位置
tempParameterMappingList.add((size - 1 - count), p1);
}
// 需要移动两个
// 需要移动两个
else if (limitCount == 2) {
// 获取最后两个参数,p1为倒数第⼀个 p2为倒数第⼆个
ParameterMapping p1 = ve(size - 1);
ParameterMapping p2 = ve(size - 2);
// 将参数插⼊到指定位置
tempParameterMappingList.add((size - 2 - count), p1);
tempParameterMappingList.add((size - 2 - count), p2);
}
}
parameterMappingList = tempParameterMappingList;
}
// String());
BoundSql pageBoundSql = new BoundSql(configuration, pageSql, parameterMappingList, parameter);
// 设置动态参数
for (String key : additionalParameters.keySet()) {
pageBoundSql.setAdditionalParameter(key, (key));
}
// 执⾏分页查询
resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, pageKey, pageBoundSql);
} else {
// 不执⾏分页的情况下，也不执⾏内存分页
resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, boundSql);
}
} else {
// rowBounds⽤参数值，不使⽤分页插件处理时，仍然⽀持默认的内存分页
resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
}
return dialect.afterPage(resultList, parameter, rowBounds);
} finally
{
dialect.afterAll();
}
}
/**
* 执⾏⼿动设置的 count 查询，该查询⽀持的参数必须和被分页的⽅法相同
*
* @param executor
* @param countMs
* @param parameter
* @param boundSql
* @param resultHandler
* @return
* @throws IllegalAccessException
* @throws SQLException
*/
private Long executeManualCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql,  ResultHandler resultHandler) throws IllegalAccessException, SQLException {
CacheKey countKey = ateCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
BoundSql countBoundSql = BoundSql(parameter);
Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey,
countBoundSql);
Long count = ((Number) ((List) countResultList).get(0)).longValue();
return count;
}
/**
* 执⾏⾃动⽣成的 count 查询
分页查询插件*
* @param executor
* @param countMs
* @param parameter
* @param boundSql
* @param rowBounds

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。