MyBatis 查询上万条数据优化

MyBatis 查询上万条数据优化从 10 秒到毫秒的实战指南当 MyBatis 查询数据量超过万条时响应速度急剧下降。本文深入分析 fetchSize 原理并给出 6 种优化方案覆盖 SQL 层面、JDBC 层面和架构层面。一、问题现象1.1 典型场景// 查询上万条数据响应缓慢ListMapString,ObjectlistuserMapper.queryLargeData();// 调用耗时5s ~ 30s数据量默认配置耗时优化后耗时1 万条~3s~0.3s10 万条~30s~2s100 万条超时/OOM~15s需流式处理1.2 根因分析MyBatis 默认通过 JDBC 取数据时默认每次只取 10 条fetchSize 10。这意味着查询 1 万条数据 → 客户端与数据库往返 1000 次 查询 10 万条数据 → 客户端与数据库往返 10000 次每次网络往返的延迟累积导致查询耗时呈线性增长。二、fetchSize 原理2.1 JDBC 数据读取机制┌──────────┐ ┌──────────┐ ┌──────────┐ │ 客户端 │ ──query──▶│ 数据库 │ │ 数据库 │ │ (MyBatis)│ ◀──10条──│ │ │ │ │ │ ──next──▶│ │ │ │ │ │ ◀──10条──│ │ │ │ │ │ ...... │ │ │ │ └──────────┘ └──────────┘ └──────────┘参数默认值含义fetchSize10Oracle/ 不限制MySQLJDBC 每次从数据库读取的记录数增大效果—减少往返次数提高吞吐量减小效果—降低单次内存占用适合流式处理2.2 对比数据fetchSize1 万条往返次数10 万条往返次数10默认1000 次10000 次100100 次1000 次100010 次100 次100001 次10 次结论fetchSize 设为 10000 时1 万条数据仅需 1 次往返效率提升 1000 倍。三、方案一设置 fetchSize最直接3.1 XML Mapper 配置在select标签上添加fetchSize属性!-- 查询上万条数据设置 fetchSize 为 10000 --selectidqueryJhbmsfetchSize10000resultTypemapSELECT * FROM your_table WHERE status ACTIVE/select3.2 注解方式Options(fetchSize10000)Select(SELECT * FROM your_table WHERE status ACTIVE)ListMapString,ObjectqueryLargeData();3.3 MyBatis-Plus 全局配置mybatis-plus:configuration:default-fetch-size:10000或者在 Java 配置中BeanpublicSqlSessionFactorysqlSessionFactory(DataSourcedataSource)throwsException{MybatisSqlSessionFactoryBeanfactoryBeannewMybatisSqlSessionFactoryBean();factoryBean.setDataSource(dataSource);org.apache.ibatis.session.Configurationconfigurationneworg.apache.ibatis.session.Configuration();configuration.setDefaultFetchSize(10000);factoryBean.setConfiguration(configuration);returnfactoryBean.getObject();}3.4 注意事项关注点说明内存占用fetchSize 越大单次内存消耗越高避免超过 JVM 堆内存推荐值一般不超过 10000建议 1000~5000 之间数据库差异Oracle 默认 10MySQL 驱动默认不限制全量读取大字段影响包含 BLOB/TEXT 字段时适当降低 fetchSize四、方案二分页查询推荐通用4.1 MyBatis-Plus 分页// 分页查询每页 1000 条publicvoidqueryByPage(){intpageSize1000;intcurrent1;longtotal;do{PageMapString,ObjectpagenewPage(current,pageSize);PageMapString,ObjectresultuserMapper.selectPage(page,null);ListMapString,Objectrecordsresult.getRecords();// 处理当前页数据processBatch(records);totalresult.getTotal();current;}while((current-1)*pageSizetotal);}4.2 手写分页 SQLselectidqueryByPageresultTypemapSELECT * FROM your_table ORDER BY id LIMIT #{offset}, #{pageSize}/selectpublicvoidqueryByPageManual(){intpageSize1000;intoffset0;ListMapString,Objectbatch;do{batchuserMapper.queryByPage(offset,pageSize);processBatch(batch);offsetpageSize;}while(batch.size()pageSize);}4.3 分页 vs fetchSize 对比方案优点缺点fetchSize一次 SQL减少数据库负载内存消耗集中分页查询内存可控适合大数据量多次 SQL总耗时可能更长五、方案三游标/流式查询适合大数据量导出5.1 MyBatis 游标查询Select(SELECT * FROM your_table WHERE status ACTIVE)Options(fetchSizeInteger.MIN_VALUE)// MySQL 流式读取CursorMapString,ObjectqueryLargeDataCursor();// 使用游标逐条处理内存友好try(CursorMapString,ObjectcursoruserMapper.queryLargeDataCursor()){cursor.forEach(row-{// 逐行处理内存仅保留一条数据processRow(row);});}5.2 MyBatis-Plus 流式查询BeanpublicSqlSessionFactorysqlSessionFactory(DataSourcedataSource){// ... 配置configuration.setDefaultFetchSize(1000);// 不影响游标模式returnfactoryBean.getObject();}// 使用 MyBatis-Plus 的流式查询publicvoidstreamQuery(){userMapper.selectList(newLambdaQueryWrapperUser().eq(User::getStatus,ACTIVE),resultContext-{// 每行回调处理UseruserresultContext.getResultObject();processUser(user);});}5.3 MySQL 游标的特殊要求MySQL 使用游标需要设置Options(fetchSizeInteger.MIN_VALUE)// 告诉 MySQL 驱动使用流式或者 JDBC URL 配置jdbc:mysql://localhost:3306/db?useCursorFetchtruedefaultFetchSize1000六、方案四SQL 层面优化6.1 只查询必要字段!-- ❌ 不推荐SELECT * --selectidqueryAllresultTypemapSELECT * FROM your_table/select!-- ✅ 推荐只查需要的字段 --selectidqueryNeededresultTypemapSELECT id, name, status, create_time FROM your_table/select6.2 使用索引覆盖-- 确保查询的字段都在索引中避免回表-- 创建联合索引CREATEINDEXidx_status_createONyour_table(status,create_time)INCLUDE(id,name);-- 查询时只走索引不访问表数据SELECTid,name,status,create_timeFROMyour_tableWHEREstatusACTIVEORDERBYcreate_time;6.3 避免 JOIN 过多表!-- ❌ 多表 JOIN 全量查询 --selectidqueryWithJoinsresultTypemapSELECT a.*, b.*, c.* FROM table_a a LEFT JOIN table_b b ON a.id b.a_id LEFT JOIN table_c c ON a.id c.a_id WHERE a.status ACTIVE/select!-- ✅ 拆分为多次查询 --selectidqueryMainresultTypemapSELECT * FROM table_a WHERE status ACTIVE/select!-- 然后在业务层组装 --七、方案五数据库端优化7.1 合理使用索引-- 查看查询计划EXPLAINSELECT*FROMyour_tableWHEREstatusACTIVEORDERBYcreate_time;-- 关键指标-- type: ALL全表扫描→ ref/range索引扫描-- rows: 扫描行数越小越好-- Extra: Using filesort需要优化排序7.2 分区表对于超大规模数据百万级以上考虑表分区-- 按时间范围分区CREATETABLEyour_table(idBIGINT,nameVARCHAR(100),create_timeDATETIME)PARTITIONBYRANGE(YEAR(create_time))(PARTITIONp2022VALUESLESS THAN(2023),PARTITIONp2023VALUESLESS THAN(2024),PARTITIONp2024VALUESLESS THAN(2025),PARTITIONp_futureVALUESLESS THAN MAXVALUE);7.3 批量处理代替逐条操作-- ❌ 逐条 UPDATEN 次 SQLUPDATEtableSETstatusDONEWHEREid1;UPDATEtableSETstatusDONEWHEREid2;-- ... N 次-- ✅ 批量 UPDATE1 次 SQLUPDATEtableSETstatusDONEWHEREidIN(1,2,3,...,1000);八、方案六异步 缓存策略8.1 异步查询ServicepublicclassReportService{AsyncpublicCompletableFutureListMapString,ObjectgenerateReportAsync(){ListMapString,ObjectdatauserMapper.queryLargeData();returnCompletableFuture.completedFuture(data);}}8.2 缓存中间结果ServicepublicclassCachedQueryService{AutowiredprivateRedisTemplateString,ObjectredisTemplate;// 缓存查询结果设置过期时间publicListMapString,ObjectqueryWithCache(){StringcacheKeylarge:data:active;// 1. 尝试从缓存获取ListMapString,Objectcached(ListMapString,Object)redisTemplate.opsForValue().get(cacheKey);if(cached!null){returncached;}// 2. 缓存未命中查询数据库ListMapString,ObjectdatauserMapper.queryLargeData();// 3. 写入缓存设置 5 分钟过期redisTemplate.opsForValue().set(cacheKey,data,5,TimeUnit.MINUTES);returndata;}}8.3 数据预热ComponentpublicclassDataWarmerimplementsCommandLineRunner{AutowiredprivateCachedQueryServicecachedQueryService;Overridepublicvoidrun(String...args){// 应用启动时预热数据cachedQueryService.queryWithCache();log.info(Large data cache warmed up);}}九、方案对比与推荐方案适用场景性能提升实现难度风险① fetchSize万级数据单次查询★★★★★★☆☆☆☆内存占用增加② 分页查询任意数据量★★★★☆★★☆☆☆多次 SQL 开销③ 游标查询十万级以上导出★★★★☆★★★☆☆连接长时间占用④ SQL 优化所有查询★★★☆☆★★☆☆☆索引维护成本⑤ 数据库优化长期稳定业务★★★☆☆★★★★☆架构改造成本高⑥ 异步缓存读多写少场景★★★★★★★★☆☆缓存一致性推荐最佳实践流程┌──────────────┐ │ 查询数据量大 │ └──────┬───────┘ ▼ ┌──────────────────┐ │ 是否需要实时数据 │ └──────┬──────┬─────┘ │ │ 是(实时) 否(缓存) │ │ ▼ ▼ ┌────────────┐ ┌────────────┐ │ 万级数据 │ │ 缓存预热 │ │ fetchSize │ │ 异步刷新 │ │ 5000 │ └────────────┘ ├────────────┤ │ 十万级数据 │ │ 游标流式 │ ├────────────┤ │ 百万级数据 │ │ 分页索引 │ └────────────┘十、常见问题 FAQQfetchSize 设为 100000 会不会更快A不一定。过大可能导致内存溢出OOM建议根据单行数据大小估算内存占用 ≈ fetchSize × 单行数据大小 例如fetchSize10000, 单行1KB → 约 10MB 内存占用QMySQL 默认 fetchSize 是多少AMySQL JDBC 驱动默认不限制一次读取全部结果到客户端内存。如果不设 fetchSizeMySQL 反而可能更容易 OOM。Q游标查询有什么缺点A游标会长时间占用数据库连接如果处理慢可能导致连接超时。使用后务必关闭 Cursor建议 try-with-resources。Q分页查询深度分页OFFSET 大慢怎么办A改用游标分页基于上次查询的最后一个 ID-- ❌ 传统分页深度分页慢SELECT*FROMtableORDERBYidLIMIT100000,10;-- ✅ 游标分页走索引SELECT*FROMtableWHEREid100000ORDERBYidLIMIT10;QMyBatis-Plus 的分页和 fetchSize 能一起用吗A可以但分页本身已经控制了数据量fetchSize 作用不大。一般分页不设 fetchSize大批量导出才设。十一、总结核心要点说明默认 10 条往返JDBC 默认 fetchSize10万条数据往返 1000 次设 fetchSize万级数据最直接推荐 5000~10000分页查询通用方案内存友好游标流式十万级以上数据导出首选SQL 优化覆盖索引、避免 SELECT *、减少 JOIN缓存策略读多写少场景结合 Redis 缓存中间结果一句话总结万级数据用 fetchSize十万级用分页百万级用游标 索引优化高频查询加缓存。