解决mysql深度分页问题

客官°小女子只卖身不卖艺 2024-04-08 12:32 96阅读 0赞

**目录**

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%A8![icon-default.png_t_M7J4][]https://bs.zhxu.cn/guide/latest/advance.html\#sql-%E6%8B%A6%E6%88%AA%E5%99%A8][https_bs.zhxu.cn_guide_latest_advance.html_sql-_E6_8B_A6_E6_88_AA_E5_99_A8_icon-default.png_t_M7J4_https_bs.zhxu.cn_guide_latest_advance.html_sql-_E6_8B_A6_E6_88_AA_E5_99_A8]

①改造Bean

![af584f2e54ae4951a6c316a3937de48e.png][]②注入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.万能优化技巧：索引。   ![66d583279cd041b292bf36d0bbdc7dcb.png][] ####

![ff9b0fb7c61749da9f6aa3d5b4de43bb.png][]

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

![9921a0672e9542b9a1a86e8de9773fba.png][]

[icon-default.png_t_M7J4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/19b8df8989f64109b1d2c8a40aa3460f.png
[https_bs.zhxu.cn_guide_latest_advance.html_sql-_E6_8B_A6_E6_88_AA_E5_99_A8_icon-default.png_t_M7J4_https_bs.zhxu.cn_guide_latest_advance.html_sql-_E6_8B_A6_E6_88_AA_E5_99_A8]: https://bs.zhxu.cn/guide/latest/advance.html#sql-%E6%8B%A6%E6%88%AA%E5%99%A8
[af584f2e54ae4951a6c316a3937de48e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/2b793bae26ef4f76a0f372708eab95dc.png
[66d583279cd041b292bf36d0bbdc7dcb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/56dc0d901d0b4801bf74c7b7bd2042ee.png
[ff9b0fb7c61749da9f6aa3d5b4de43bb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/7df12d7d1c744e61b4f9560f71b72ed6.png
[9921a0672e9542b9a1a86e8de9773fba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/fc27f23e368c49a485165118f1300734.png