pagehelper插件，踩坑笔记

在使用过程中，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 

 

附上大致的流程图：