如何解決mysql深度分頁(yè)問(wèn)題

更新時(shí)間：2023年01月09日 09:19:37 作者：蒼穹之躍

這篇文章主要介紹了如何解決mysql深度分頁(yè)問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

1.基本分頁(yè)：耗時(shí)0.019秒
2.深度分頁(yè)：耗時(shí)10.236秒
3.深度ID分頁(yè)：耗時(shí)0.052秒
4.兩步走深度分頁(yè)：耗時(shí)0.049秒+0.017秒
5.一步走深度分頁(yè)：耗時(shí)0.05秒
6.集成BeanSearcher框架
7.萬(wàn)能優(yōu)化技巧：索引

總結(jié)

mysql深度分頁(yè)問(wèn)題

數(shù)據(jù)：?jiǎn)伪頂?shù)據(jù)25萬(wàn)條。

1.基本分頁(yè)：耗時(shí)0.019秒

select * from cf_qb_info limit 0,20

2.深度分頁(yè)：耗時(shí)10.236秒

select * from cf_qb_info limit 200000,20

3.深度ID分頁(yè)：耗時(shí)0.052秒

提示：如果這一步很慢，count(1) 查詢總數(shù)應(yīng)該也會(huì)很慢-解決方式：請(qǐng)為主鍵加上unique索引。

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

4.兩步走深度分頁(yè)：耗時(shí)0.049秒+0.017秒

基于第三步的缺陷（只能查出ID信息），我們可以先查出分頁(yè)數(shù)據(jù)的ID，在根據(jù)ID查詢數(shù)據(jù)。

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耗時(shí)：11.9秒；這一步著實(shí)出乎了我的意料。

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

鑒于這個(gè)結(jié)果：我們可以在程序里分成兩步進(jìn)行分頁(yè)查詢。

5.一步走深度分頁(yè)：耗時(shí)0.05秒

這一步是對(duì)第四步的優(yōu)化，畢竟兩條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攔截器對(duì)SQL進(jìn)行攔截改造。

①改造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攔截器:優(yōu)化深度分頁(yè)
 *
 * @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的注解作為主鍵標(biāo)識(shí)
            TableId tableId = field.getAnnotation(TableId.class);
            if (tableId != null) {
                if (!"".equals(tableId.value())) {
                    primaryColumnName = tableId.value();
                } else {
                    //駝峰轉(zhuǎn)下劃線
                    primaryColumnName = StrUtil.toUnderlineCase(field.getName());
                }
            }
        }
        //如果沒(méi)有主鍵標(biāo)識(shí)，則不能進(jìn)行SQL優(yōu)化。
        if (primaryColumnName == null) {
            return searchSql;
        }
 
        //正則表達(dá)式獲取where之后語(yǔ)句
        List<String> limits = ReUtil.findAll("where[\\s\\S]*limit[ ]+[?]{1}[ ]*,[ ]+[?]{1}", searchSql.getListSqlString(), 0);
 
        //如果不分頁(yè)，則不進(jìn)行SQL優(yōu)化，即語(yǔ)句中沒(méi)有l(wèi)imit關(guān)鍵字不優(yōu)化。
        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;
    }
}