产品：大哥，你这列表查询有问题啊！

前言

👳‍♂️产品大哥（怒气冲冲跑过来）： “大哥你这查询列表有问题啊，每次点一下查询，返回的数据不一样呢”

👦我：“FKY 之前不是说好的吗，加了排序查询很卡，就取消了”

🧔技术经理：“卡主要是因为分页查询加了排序之后，mybatisPlus 生成的 count 也会有Order by就 很慢，自己实现一个count 就行了”

👦我：“分页插件在执行统计操作的时候，一般都会对Sql 简单的优化，会去掉排序的”

今天就来看看分页插件处理 count 的时候的优化逻辑，是否能去除order by；

同时 简单阐述一下 order by、limit 的运行原理

往期好文：最近发现一些同事的代码问题

mybatisPlus分页插件count 运行原理

分页插件都是基于MyBatis 的拦截器接口Interceptor实现，这个就不用多说了。下面看一下分页插件的处理count的代码，以及优化的逻辑。

详细代码见：com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.class

count sql 从获取到执行的主要流程

1.确认count sql MappedStatement 对象:

先查询Page对象中 是否有countId（countId 为mapper sql id）,有的话就用自定义的count sql,没有的话就自己通过查询语句构建一个count MappedStatement

2.优化count sql：

得到countMs构建成功之后对count SQL进行优化，最后 执行count SQL，将结果 set 到page对象中。

public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {

    IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);
if (page == null || page.getSize() < 0 || !page.searchCount()) {
return true;

    }


    BoundSql countSql;
// -------------------------------- 根据“countId”获取自定义的count MappedStatement
MappedStatement countMs = buildCountMappedStatement(ms, page.countId());
if (countMs != null) {

        countSql = countMs.getBoundSql(parameter);

    } else {
//-------------------------------------------根据查询ms 构建统计SQL的MS

        countMs = buildAutoCountMappedStatement(ms);
//-------------------------------------------优化count SQL
String countSqlStr = autoCountSql(page, boundSql.getSql());

        PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);

        countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);

        PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());

    }

CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);
//----------------------------------------------- 统计SQL

    List<Object> result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql);
long total = 0;
if (CollectionUtils.isNotEmpty(result)) {
// 个别数据库 count 没数据不会返回 0
Object o = result.get(0);
if (o != null) {

            total = Long.parseLong(o.toString());

        }

    }
// ---------------------------------------set count ret

    page.setTotal(total);
return continuePage(page);

}

count SQL 优化逻辑

主要优化的是以下两点：

哪些情况count 优化限制：

详情可阅读一下代码：

/**

 * 获取自动优化的 countSql

 *

 * @param page 参数

 * @param sql  sql

 * @return countSql

 */
protected String autoCountSql(IPage<?> page, String sql) {
if (!page.optimizeCountSql()) {
return lowLevelCountSql(sql);

    }
try {
Select select = (Select) CCJSqlParserUtil.parse(sql);
SelectBody selectBody = select.getSelectBody();
// https://github.com/baomidou/mybatis-plus/issues/3920  分页增加union语法支持
//----------- SQL 中 有 这些集合操作的 INTERSECT,EXCEPT,MINUS,UNION 直接不优化count

if (selectBody instanceof SetOperationList) {
// ----lowLevelCountSql 具体实现: String.format("SELECT COUNT(*) FROM (%s) TOTAL", originalSql)
return lowLevelCountSql(sql);

        }

        ....................省略.....................
if (CollectionUtils.isNotEmpty(orderBy)) {
boolean canClean = true;
if (groupBy != null) {
// 包含groupBy 不去除orderBy

                canClean = false;

            }
if (canClean) {
for (OrderByElement order : orderBy) {
//-------------- order by 里带参数,不去除order by
Expression expression = order.getExpression();
if (!(expression instanceof Column) && expression.toString().contains(StringPool.QUESTION_MARK)) {

                        canClean = false;
break;

                    }

                }

            }
//-------- 清除order by
if (canClean) {

                plainSelect.setOrderByElements(null);

            }

        }
//#95 Github, selectItems contains #{} ${}, which will be translated to ?, and it may be in a function: power(#{myInt},2)
// ----- 查看select 字段中是否动态条件，如果有条件字段，则不会优化 Count SQL
for (SelectItem item : plainSelect.getSelectItems()) {
if (item.toString().contains(StringPool.QUESTION_MARK)) {
return lowLevelCountSql(select.toString());

            }

        }
// ---------------包含 distinct、groupBy不优化
if (distinct != null || null != groupBy) {
return lowLevelCountSql(select.toString());

        }
// ------------包含 join 连表,进行判断是否移除 join 连表
if (optimizeJoin && page.optimizeJoinOfCountSql()) {

            List<Join> joins = plainSelect.getJoins();
if (CollectionUtils.isNotEmpty(joins)) {
boolean canRemoveJoin = true;
String whereS = Optional.ofNullable(plainSelect.getWhere()).map(Expression::toString).orElse(StringPool.EMPTY);
// 不区分大小写

                whereS = whereS.toLowerCase();
for (Join join : joins) {
if (!join.isLeft()) {

                        canRemoveJoin = false;
break;

                    }

                     .........................省略..............

                    } else if (rightItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) rightItem;
/* ---------如果 left join 是子查询，并且子查询里包含 ?(代表有入参) 或者 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
if (subSelect.toString().contains(StringPool.QUESTION_MARK)) {

                            canRemoveJoin = false;
break;

                        }

                        str = subSelect.getAlias().getName() + StringPool.DOT;

                    }
// 不区分大小写

                    str = str.toLowerCase();
if (whereS.contains(str)) {
/*--------------- 如果 where 条件里包含使用 join 的表的字段作条件,就不移除 join */

                        canRemoveJoin = false;
break;

                    }

for (Expression expression : join.getOnExpressions()) {
if (expression.toString().contains(StringPool.QUESTION_MARK)) {
/* 如果 join 里包含 ?(代表有入参) 就不移除 join */

                            canRemoveJoin = false;
break;

                        }

                    }

                }
// ------------------ 移除join
if (canRemoveJoin) {

                    plainSelect.setJoins(null);

                }

            }

        }
// 优化 SQL-------------

        plainSelect.setSelectItems(COUNT_SELECT_ITEM);
return select.toString();

    } catch (JSQLParserException e) {

      ..............

    }
return lowLevelCountSql(sql);

}

order by 运行原理

order by 排序，具体怎么排取决于优化器的选择，如果优化器认为走索引更快，那么就会用索引排序，否则，就会使用filesort (执行计划中extra中提示：using filesort），但是能走索引排序的情况并不多，并且确定性也没有那么强，很多时候，还是走的filesort

索引排序

索引排序，效率是最高的，就算order by 后面的字段是 索引列，也不一定就是通过索引排序。这个过程是否一定用索引，完全取决于优化器的选择。

filesort 排序

如果不能走索引排序， MySQL 会执行filesort操作以读取表中的行并对它们进行排序。

在进行排序时，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，它的大小是由sort_buffer_size控制的。

sort_buffer_size的大小不同，会在不同的地方进行排序操作：

• 
  
• 如果要排序的数据量小于 sort_buffer_size，那么排序就在内存中完成。
  
• 如果排序数据量大于sort_buffer_size，则需要利用磁盘临时文件辅助排序。
  

  
  

  采用多路归并排序的方式将磁盘上的多个有序子文件合并成一个有序的结果集

  
  

  
  
  

filesort 排序 具体实现方式

FileSort是MySQL中用于对数据进行排序的一种机制，主要有以下几种实现方式：

全字段排序

• 
  
• 原理：将查询所需的所有字段，包括用于排序的字段以及其他SELECT列表中的字段，都读取到排序缓冲区中进行排序。这样可以在排序的同时获取到完整的行数据，减少访问原表数据的次数。
  
• 适用场景：当排序字段和查询返回字段较少，并且排序缓冲区能够容纳这些数据时，全字段排序效率较高。
  

行指针排序

• 
  
• 原理：只将排序字段和行指针（指向原表中数据行的指针）读取到排序缓冲区中进行排序。排序完成后，再根据行指针回表读取所需的其他字段数据。
  
• 适用场景：当查询返回的字段较多，而排序缓冲区无法容纳全字段数据时，行指针排序可以减少排序缓冲区的占用，提高排序效率。但由于需要回表操作，可能会增加一定的I/O开销。
  

多趟排序

• 
  
• 原理：如果数据量非常大，即使采用行指针排序，排序缓冲区也无法一次容纳所有数据，MySQL会将数据分成多个较小的部分，分别在排序缓冲区中进行排序，生成多个有序的临时文件。然后再将这些临时文件进行多路归并，最终得到完整的有序结果。
  
• 适用场景：适用于处理超大数据量的排序操作，能够在有限的内存资源下完成排序任务，但会产生较多的磁盘I/O操作，性能相对较低。
  

优先队列排序

• 
  
• 原理：结合优先队列数据结构进行排序。对于带有LIMIT子句的查询，MySQL会创建一个大小为LIMIT值的优先队列。在读取数据时，将数据放入优先队列中，根据排序条件进行比较和调整。当读取完所有数据或达到一定条件后，优先队列中的数据就是满足LIMIT条件的有序结果。
  
• 适用场景：特别适用于需要获取少量排序后数据的情况，如查询排名前几的数据。可以避免对大量数据进行全量排序，提高查询效率。
  

❗所以减少查询字段 ,以及 减少 返回的行数，对于排序SQL 的优化也是非常重要

❗以及order by 后面尽量使用索引字段，以及行数限制

limit 运行原理

limit执行过程
对于 SQL 查询中 LIMIT 的使用，像 LIMIT 10000, 100 这种形式，MySQL 的执行顺序大致如下：

所以，LIMIT 是先检索所有符合条件的数据，然后丢弃掉前面的行，再返回指定的行数。这解释了为什么如果数据集很大，LIMIT 会带来性能上的一些问题，尤其是在有很大的偏移量（比如 LIMIT 10000, 100）时。

总结

本篇文章分析，mybatisPlus 分页插件处理count sql 的逻辑，以及优化过程，同时也简单分析order by 和 limit 执行原理。

希望这篇文章能够让你对SQL优化 有不一样的认知，最后感谢各位老铁一键三连！

ps: 云服务器找我返点；面试宝典私；收徒ING；