如何解决mysql深度分页问题

Victoria ·

更新时间:2024-09-20

· 1227 次阅读

1.基本分页：耗时0.019秒

2.深度分页：耗时10.236秒

3.深度ID分页：耗时0.052秒

4.两步走深度分页：耗时0.049秒+0.017秒

5.一步走深度分页：耗时0.05秒

6.集成BeanSearcher框架

7.万能优化技巧：索引

总结

数据：单表数据25万条。

1.基本分页：耗时0.019秒 select * from cf_qb_info limit 0,20 2.深度分页：耗时10.236秒 select * from cf_qb_info limit 200000,20 3.深度ID分页：耗时0.052秒

提示：如果这一步很慢，count(1) 查询总数应该也会很慢-解决方式：请为主键加上unique索引。

-- 主键ID字段：NUMID
select NUMID from cf_qb_info limit 200000,20

4.两步走深度分页：耗时0.049秒+0.017秒

基于第三步的缺陷（只能查出ID信息），我们可以先查出分页数据的ID，在根据ID查询数据。

select NUMID from cf_qb_info LIMIT 200000,20

select * from cf_qb_info where NUMID in (
'330681650000202108180227345510',
'330681650000202108171031534500',
'330681650000202108190251532141',
'330681650000202108200246376830',
'330681650000202108210229398665',
'330681650000202108220236113895',
'330681650000202108230230034133',
'330681650000202108231017279739',
'330681650000202108231043456276',
'330681650000202108231051404340',
'330681650000202108240237397251',
'330681650000202108250221489228',
'330681650000202108250241536726',
'330681650000202108260253039326',
'330681650000202108270216016138',
'330681650000202108280234013754',
'330681650000202108290230029720',
'330681650000202108300255579204',
'330681650000202108310234184991',
'330681650000202109010237315937'
);

两步合成一步SQL耗时：11.9秒；这一步着实出乎了我的意料。

select * from cf_qb_info where NUMID in (
select NUMID from (select NUMID from cf_qb_info LIMIT 200000,20) as t
);

鉴于这个结果：我们可以在程序里分成两步进行分页查询。

5.一步走深度分页：耗时0.05秒

这一步是对第四步的优化，毕竟两条SQL还需要码代码。利用join 两条SQL合成一条。

SELECT
* 
FROM
cf_qb_info a
JOIN ( SELECT NUMID FROM cf_qb_info LIMIT 200000, 20 ) b ON a.NUMID = b.NUMID

6.集成BeanSearcher框架

原理是使用了BeanSearcher的sql拦截器对SQL进行拦截改造。

①改造Bean

②注入Sql拦截器

package com.ciih.qbbs.config;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.annotation.TableId;
import com.ejlchina.searcher.SearchSql;
import com.ejlchina.searcher.SqlInterceptor;
import com.ejlchina.searcher.SqlSnippet;
import com.ejlchina.searcher.param.FetchType;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.List;
import java.util.Map;
/**
 * BeanSearcher的Sql拦截器:优化深度分页
 *
 * @author sunziwen
 */
@Component
public class SqlInterceptorImpl implements SqlInterceptor {
    @Override
    public <T> SearchSql<T> intercept(SearchSql<T> searchSql, Map<String, Object> paraMap, FetchType fetchType) {
        /**
         * 改造思路
         *
         * <>
         *     前：SELECT * FROM table1 t1 LIMIT 200000,20;
         *     后：SELECT * FROM table1 t1 JOIN ( SELECT id FROM table1 LIMIT 200000, 20 ) t99 ON t1.id = t99.id;
         * </>
         */
        Field[] fields = searchSql.getBeanMeta().getBeanClass().getDeclaredFields();
        String primaryColumnName = null;
        for (Field field : fields) {
            //这里使用了mybatis_plus的注解作为主键标识
            TableId tableId = field.getAnnotation(TableId.class);
            if (tableId != null) {
                if (!"".equals(tableId.value())) {
                    primaryColumnName = tableId.value();
                } else {
                    //驼峰转下划线
                    primaryColumnName = StrUtil.toUnderlineCase(field.getName());
                }
            }
        }
        //如果没有主键标识，则不能进行SQL优化。
        if (primaryColumnName == null) {
            return searchSql;
        }
        //正则表达式获取where之后语句
        List<String> limits = ReUtil.findAll("where[\\s\\S]*limit[ ]+[?]{1}[ ]*,[ ]+[?]{1}", searchSql.getListSqlString(), 0);
        //如果不分页，则不进行SQL优化，即语句中没有limit关键字不优化。
        if (limits.size() == 0) {
            return searchSql;
        }
        //表名小片段
        SqlSnippet tableSnippet = searchSql.getBeanMeta().getTableSnippet();
        //合成子查询SQL
        String inSql = "JOIN ( SELECT " + primaryColumnName + " FROM " + tableSnippet.getSql() + " " + limits.get(0) + " ) t99 ON t1." + primaryColumnName + " = t99." + primaryColumnName + ";";
        //合成整条SQL
        String replace = searchSql.getListSqlString().replace(limits.get(0), inSql);
        //替换
        searchSql.setListSqlString(replace);
        return searchSql;
    }
}

7.万能优化技巧：索引