解决mysql深度分页问题

目录

1.基本分页：耗时0.019秒

2.深度分页：耗时10.236秒

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

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

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

6.集成BeanSearcher框架

7.万能优化技巧：索引。​编辑

8. 如果对数据插入的要求很高，比如每秒插入十多条数据，还是每条逐个插入，时间久了必然数据量非常大。这里还有一个方法可以试一试，那就是牺牲事务而获取性能，对不重要的数据可以这么做。将InnoDB引擎改为MyISAM引擎。

数据：单表数据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进行拦截改造。https://bs.zhxu.cn/guide/latest/advance.html#sql-%E6%8B%A6%E6%88%AA%E5%99%A8https://bs.zhxu.cn/guide/latest/advance.html#sql-%E6%8B%A6%E6%88%AA%E5%99%A8

 ①改造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  SearchSql intercept(SearchSql searchSql, Map 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 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.万能优化技巧：索引。

8. 如果对数据插入的要求很高，比如每秒插入十多条数据，还是每条逐个插入，时间久了必然数据量非常大。这里还有一个方法可以试一试，那就是牺牲事务而获取性能，对不重要的数据可以这么做。将InnoDB引擎改为MyISAM引擎。