在使用过程中,pagehelper插件在复杂的sql查询count时,会自动生成select count(0) from (需要分页的sql) tmp_count,有时也会把排序字段放进去,导致count查询时间很长。
如,生成的分页sql:
SELECT t.*,
( SELECT x.NAME FROM tb_jd_token x WHERE ( x.ID = t.MALL_FLAG ) ) AS mall_name,
( SELECT `NAME` FROM tmk_mall_category tmc WHERE t.category = tmc.ID ) as category_name,
CASE
WHEN (SELECT COUNT(1)
FROM tmk_good_type a
WHERE t.gdty_id = a.id
AND t.status = 1
AND a.maca_id IN (SELECT x.id FROM tmk_mall_category x WHERE x.status = 1)
AND t.good_count_descn != '无货'
AND t.show_flag in (0, 1, 2)
AND t.preferential_price IS NOT NULL) > 0 THEN
'已显示'
ELSE
'未显示'
END show_count_mean,
CONCAT(
IF(t.update_flag = 1, '类目对应关系不存在、', ''),
IF(t.update_flag = 2, '该类目无权限维护、', ''),
IF(t.show_flag = 3, '已暂停、', ''),
IF((t.PREFERENTIAL_PRICE is null or t.PREFERENTIAL_PRICE = 0), '采购价为空或为0、', ''),
IF(t.PREFERENTIAL_PRICE > t.SALE_PRICE, '采购价大于官网价、', ''),
IF(t.good_count_descn = '无货', '无货、', ''),
IF(t.status = 0, '已下架、', '')
) not_show_type_mean
from tmk_goods t
ORDER BY t.ldate desc
LIMIT ?
自动生成的count查询语句:
SELECT
count( 0 )
FROM
(
SELECT t.*,
( SELECT x.NAME FROM tb_jd_token x WHERE ( x.ID = t.MALL_FLAG ) ) AS mall_name,
( SELECT `NAME` FROM tmk_mall_category tmc WHERE t.category = tmc.ID ) as category_name,
CASE
WHEN (SELECT COUNT(1)
FROM tmk_good_type a
WHERE t.gdty_id = a.id
AND t.status = 1
AND a.maca_id IN (SELECT x.id FROM tmk_mall_category x WHERE x.status = 1)
AND t.good_count_descn != '无货'
AND t.show_flag in (0, 1, 2)
AND t.preferential_price IS NOT NULL) > 0 THEN
'已显示'
ELSE
'未显示'
END show_count_mean,
CONCAT(
IF(t.update_flag = 1, '类目对应关系不存在、', ''),
IF(t.update_flag = 2, '该类目无权限维护、', ''),
IF(t.show_flag = 3, '已暂停、', ''),
IF((t.PREFERENTIAL_PRICE is null or t.PREFERENTIAL_PRICE = 0), '采购价为空或为0、', ''),
IF(t.PREFERENTIAL_PRICE > t.SALE_PRICE, '采购价大于官网价、', ''),
IF(t.good_count_descn = '无货', '无货、', ''),
IF(t.status = 0, '已下架、', '')
) not_show_type_mean
from tmk_goods t
ORDER BY
t.ldate DESC
) tmp_count
原本应该自动生成的count查询语句:
select count(0) from tmk_goods t
解决方法:
复杂sql分页时,手写count查询语句
手写的count的sql的statementId = 需要分页的sql的statementId + “_COUNT”
pagehelper以“_COUNT”结尾作为count的sql的标志
private Long count(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds,
ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
String countMsId = ms.getId() + this.countSuffix;
MappedStatement countMs = ExecutorUtil.getExistedMappedStatement(ms.getConfiguration(), countMsId);
Long count;
Long count;
if (countMs != null) {//存在手写的 count 查询
count = ExecutorUtil.executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
} else { //不存在,自动生成 count 查询
countMs = (MappedStatement) this.msCountMap.get(countMsId);
if (countMs == null) {
countMs = MSUtils.newCountMappedStatement(ms, countMsId);
this.msCountMap.put(countMsId, countMs);
}
count = ExecutorUtil.executeAutoCount(this.dialect, executor, countMs, parameter, boundSql, rowBounds,
resultHandler);
}
return count;
}
pagehelper首先判断有没有手写的count语句,没有的话,就自动生成
实例:
TmkGoodsServiceImpl分页代码:
@Override
public PageInfo<TmkGoods> findPage(Map<String, Object> params) {
//设置分页
if (params.get("pageNumber") == null) {
//默认的第一页
params.put("pageNumber", 1);
}
if (params.get("pageSize") == null) {
//默认50条记录
params.put("pageSize", 50);
}
//分页插件,第几页,每页显示数量
PageHelper.startPage(Integer.valueOf(params.get("pageNumber").toString()),
Integer.valueOf(params.get("pageSize").toString()));
Query query = new Query(params);
List<TmkGoods> tmkGoods = tmkGoodsMapper.find(query);
return new PageInfo<TmkGoods>(tmkGoods);
}
TmkGoodsMapper.xml的分页sql为“find”,增加查询语句“find_COUNT”:
<select id="find_COUNT" resultType="Integer">
select count(1) from tmk_goods t
<where>
<include refid="condition" />
</where>
</select>
TmkGoodsMapper.java可以不用增加方法:
int find_COUNT(Object object);
count查询已经变更:
select count(1) from tmk_goods t
附上大致的流程图: