MyBatis-Plus 大表分页 count () 性能瓶颈深度解析

在使用MyBatis-Plus进行大表分页查询时你是否通过日志发现分页插件总会先执行一条count()语句且这条count()在千万级数据下耗时极长严重拖慢整体响应本文将从源码层面剖析MyBatis-Plus分页count()的执行机制结合生产实战分析性能根因并提供一套可落地的优化方案。一、背景铺垫MyBatis-Plus分页的基本流程MyBatis-Plus通过MybatisPlusInterceptor分页插件实现物理分页核心流程分为两步count查询先拦截原SQL自动生成并执行select count(0)语句获取总记录数limit分页根据总记录数和分页参数在原SQL后添加limit offset, size执行分页查询。这一机制在小表下无感知但在大表千万级下count()往往成为性能瓶颈。二、底层原理MyBatis-Plus分页count()的生成逻辑基于v3.5.5我们从源码层面看count语句是如何生成的2.1 核心拦截器MybatisPlusInterceptor分页插件的核心是MybatisPlusInterceptor它会在SQL执行前拦截调用CountExecutor生成count语句// 源码片段com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor Override public Object intercept(Invocation invocation) throws Throwable { // ... 省略前置逻辑 // 执行count查询 if (count) { countExecutor.execute(executor, mappedStatement, parameter, rowBounds, resultHandler, boundSql); } // 执行分页查询 // ... 省略后续逻辑 }2.2 Count语句生成策略CountSqlParserCountSqlParser负责解析原SQL并生成count语句默认策略如下优化场景如果原SQL是单表查询且无group by、having、union等会直接优化为select count(0) from 表 where 条件默认场景否则会生成select count(0) from (原SQL) tmp子查询。问题根源默认场景下的子查询count()在大表下会导致全表扫描或临时表开销性能极差。三、生产实战问题复现与根因分析3.1 场景模拟千万级订单表我们有一张order_info表数据量1200万结构如下CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no varchar(64) NOT NULL COMMENT 订单号, user_id bigint NOT NULL COMMENT 用户ID, amount decimal(10,2) NOT NULL COMMENT 订单金额, status tinyint NOT NULL COMMENT 订单状态, create_time datetime NOT NULL COMMENT 创建时间, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;3.2 基础代码实现// 实体类 Data TableName(order_info) public class OrderInfo { TableId(type IdType.AUTO) private Long id; private String orderNo; private Long userId; private BigDecimal amount; private Integer status; private LocalDateTime createTime; } // Mapper public interface OrderInfoMapper extends BaseMapperOrderInfo { } // Service Service RequiredArgsConstructor Slf4j public class OrderInfoService { private final OrderInfoMapper orderInfoMapper; public PageOrderInfo pageOrders(int pageNum, int pageSize, Long userId) { PageOrderInfo page new Page(pageNum, pageSize); LambdaQueryWrapperOrderInfo wrapper Wrappers.lambdaQuery(); wrapper.eq(OrderInfo::getUserId, userId) .orderByDesc(OrderInfo::getCreateTime); PageOrderInfo result orderInfoMapper.selectPage(page, wrapper); log.info(分页查询完成总记录数{}, result.getTotal()); return result; } } // 分页插件配置 Configuration public class MybatisPlusConfig { Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } }3.3 问题复现与根因分析执行pageOrders(1, 10, 1001L)查看日志DEBUG c.b.m.e.p.p.PaginationInnerInterceptor - 执行count查询SELECT COUNT(0) FROM order_info WHERE (user_id ?) DEBUG c.b.m.e.p.p.PaginationInnerInterceptor - 执行分页查询SELECT id,order_no,user_id,amount,status,create_time FROM order_info WHERE (user_id ?) ORDER BY create_time DESC LIMIT ?看似count语句被优化了单表无group by但如果条件变为status 1无索引或者原SQL有joincount性能会骤降。更典型的慢场景原SQL有join比如关联用户表查询Select(SELECT o.*, u.username FROM order_info o LEFT JOIN user_info u ON o.user_id u.id WHERE o.user_id #{userId}) PageOrderInfoVO pageOrderVO(PageOrderInfoVO page, Param(userId) Long userId);此时MyBatis-Plus生成的count语句会是SELECT COUNT(0) FROM (SELECT o.*, u.username FROM order_info o LEFT JOIN user_info u ON o.user_id u.id WHERE o.user_id ?) tmp子查询临时表在千万级数据下count()可能耗时数秒。四、生产踩坑与避坑5大优化方案4.1 方案一自定义count语句最有效核心思路避免子查询直接写count语句利用索引。在Mapper中自定义count方法MyBatis-Plus会自动识别// Mapper接口 public interface OrderInfoMapper extends BaseMapperOrderInfo { PageOrderInfoVO selectOrderVOPage(PageOrderInfoVO page, Param(userId) Long userId); Long selectOrderVOCount(Param(userId) Long userId); }!-- OrderInfoMapper.xml -- mapper namespacecom.example.mapper.OrderInfoMapper select idselectOrderVOPage resultTypecom.example.vo.OrderInfoVO countselectOrderVOCount SELECT o.id, o.order_no, o.user_id, u.username, o.amount, o.status, o.create_time FROM order_info o LEFT JOIN user_info u ON o.user_id u.id WHERE o.user_id #{userId} ORDER BY o.create_time DESC /select select idselectOrderVOCount resultTypejava.lang.Long SELECT COUNT(0) FROM order_info o WHERE o.user_id #{userId} /select /mapper4.2 方案二利用覆盖索引优化count()核心思路让count()查询走覆盖索引避免回表。如果经常按status和create_time分页查询-- 添加覆盖索引 ALTER TABLE order_info ADD INDEX idx_status_create_time (status, create_time);此时count查询会走覆盖索引无需回表。4.3 方案三不需要精确count时使用缓存或估算核心思路对于列表页用户不关心总记录数的精确值可接受近似值或缓存。Service RequiredArgsConstructor Slf4j public class OrderInfoService { private final OrderInfoMapper orderInfoMapper; private final RedisTemplateString, Object redisTemplate; private static final String ORDER_COUNT_KEY order:count:userId:; public PageOrderInfo pageOrders(int pageNum, int pageSize, Long userId) { PageOrderInfo page new Page(pageNum, pageSize); String key ORDER_COUNT_KEY userId; Long total (Long) redisTemplate.opsForValue().get(key); if (total null) { LambdaQueryWrapperOrderInfo wrapper Wrappers.lambdaQuery(); wrapper.eq(OrderInfo::getUserId, userId); total orderInfoMapper.selectCount(wrapper); redisTemplate.opsForValue().set(key, total, 1, TimeUnit.HOURS); } page.setSearchCount(false); LambdaQueryWrapperOrderInfo wrapper Wrappers.lambdaQuery(); wrapper.eq(OrderInfo::getUserId, userId) .orderByDesc(OrderInfo::getCreateTime); PageOrderInfo result orderInfoMapper.selectPage(page, wrapper); result.setTotal(total); return result; } }4.4 方案四超大数据量使用搜索引擎Elasticsearch核心思路将数据同步到ES利用ES的count和分页能力。实现步骤将order_info表数据同步到ES使用Canal、Flink CDC或Logstash分页查询时直接调用ES的searchAPI使用from/size或search_after分页同时获取hits.total.value作为总记录数。优势ES的count性能极高即使亿级数据也能毫秒级返回。4.5 方案五分库分表后的count处理如果做了分库分表如ShardingSpherecount()需要跨库统计此时方案1使用ShardingSphere的COUNT聚合函数它会自动在各分库执行count并汇总方案2将总记录数缓存到Redis定期通过离线任务统计各分库的count并汇总更新。五、性能优化进阶从架构层面解决5.1 读写分离count查询走从库将count查询和分页查询路由到从库减轻主库压力spring: shardingsphere: datasource: names: master,slave master: type: com.zaxxer.hikari.HikariDataSource jdbc-url: jdbc:mysql://master-host:3306/db username: root password: xxx slave: type: com.zaxxer.hikari.HikariDataSource jdbc-url: jdbc:mysql://slave-host:3306/db username: root password: xxx rules: readwrite-splitting: style="margin-top:12px">