【文件增量备份系统】MySQL百万量级数据量分页查询性能优化

🎯 导读：本文针对大数据量下的分页查询性能问题进行了深入探讨与优化，最初查询耗时长达12秒，通过避免全表计数及利用缓存保存总数的方式显著提升了浅分页查询速度。面对深分页时依然存在的延迟，采用先查询倒数第N条记录ID，再依据此ID获取后继记录的策略，进一步降低了查询时间。此方案适用于优化大量数据背景下的分页展示性能问题。
🏠️ 项目仓库：数据增量备份系统
📙 项目介绍：【文件增量备份系统】系统功能介绍与开源说明

问题说明

当数据量达到百万级时，查询性能已经非常慢了

经过查看日志，可以发现查询一次接口，耗时高达 两年半 5 秒，而且查的还是第一页，等查完数据，黄花菜都凉了，受不了一点，为了用户的体验，必须改进

原因排查

原始代码如下，对id进行降序排序是因为id是递增的，id越大，代表文件备份时间越新。对id进行排序是为了把最新备份的文件记录放在最前面

@Override
public PageResponse<BackupFile> pageBackupFileV1(BackupFileRequest request, boolean isOrder) {long start = System.currentTimeMillis();QueryWrapper<BackupFile> queryWrapper = new QueryWrapper<>();if (request.getBackupSourceId() != null) {queryWrapper.eq("backup_source_id", request.getBackupSourceId());}if (request.getBackupTargetId() != null) {queryWrapper.eq("backup_target_id", request.getBackupTargetId());}if (!StringUtils.isEmpty(request.getSourceFilePath())) {queryWrapper.like("source_file_path", request.getSourceFilePath());}if (!StringUtils.isEmpty(request.getTargetFilePath())) {queryWrapper.like("target_file_path", request.getTargetFilePath());}queryWrapper.orderByDesc("id");IPage<BackupFile> page = baseMapper.selectPage(new Page(request.getCurrent(), request.getSize()), queryWrapper);System.out.println("分页查询时间：" + (System.currentTimeMillis() - start) + "ms");return PageUtil.convert(page);
}

通过查看日志，发现在一次分页查询中，主要做两件事情：

• 查询数据总条数

SELECT COUNT(*) AS total FROM backup_file

• 进行真正的分页查询

SELECT id,backup_source_id,backup_target_id,source_file_path,target_file_path,backup_num,file_type,last_backup_time,file_name,file_suffix,file_length,file_length_after_compress,father_id,is_compress,is_contain_file,create_time,update_time FROM backup_file ORDER BY id DESC LIMIT 10

那上面慢的是哪个sql呢，还是说两个都慢，分别对两个sql进行单元测试

【查询数据总条数】

==>  Preparing: SELECT COUNT( * ) AS total FROM backup_file
==> Parameters:
<==    Columns: total
<==        Row: 3458533
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@521d455c]
时间：4093ms

【进行真正的分页查询】

==>  Preparing: SELECT id,backup_source_id,backup_target_id,source_file_path,target_file_path,backup_num,file_type,last_backup_time,file_name,file_suffix,file_length,file_length_after_compress,father_id,is_compress,is_contain_file,create_time,update_time FROM backup_file ORDER BY id DESC LIMIT 10
==> Parameters:
<==    Columns: id, backup_source_id, backup_target_id, source_file_path, target_file_path, backup_num, file_type, last_backup_time, file_name, file_suffix, file_length, file_length_after_compress, father_id, is_compress, is_contain_file, create_time, update_time
......
<==      Total: 10
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@327ac23]
时间：25ms

好家伙，原来慢的是查询总数。那为什么这么慢呢？
原因是 COUNT() 需要遍历整个表中的每一行来计算总行数（涉及大量的磁盘I/O操作，尤其是如果数据分布在多个磁盘块上时），因为行数多，所以慢

total查询优化

既然查询total那么久的话，怎么加速total查询呢，最方便的一个方法就是使用缓存。查询一次total就把它放到缓存中，当新增或修改数据时，再更新缓存

实现步骤

在缓存类中添加一个原子类的备份文件总数属性

import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicLong;/*** 缓存类** @Author dam* @create 2024/2/19 19:57*/
public class Cache {....../*** 所备份文件的总数量*/public static AtomicLong FILE_TOTAL_NUM = new AtomicLong();
}

实现一个更新缓存值的方法

/*** 更新缓存中的total值*/
@Override
public void updateTotalCache() {Long total = baseMapper.selectCount(new QueryWrapper<BackupFile>().select("id"));FILE_TOTAL_NUM.set(total);
}

在项目启动成功之后，调用上面方法记录total值

import lombok.extern.slf4j.Slf4j;
import org.dam.service.BackupFileService;
import org.dam.service.BackupTaskService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;/*** @Author dam* @create 2024/1/25 19:29*/
@Component
@Slf4j
public class BackupTaskInit implements CommandLineRunner {@Autowiredprivate BackupTaskService backupTaskService;@Autowiredprivate BackupFileService backupFileService;@Overridepublic void run(String... args) throws Exception {log.info("项目启动成功，执行初始化，将没有完成的备份任务设置为失败状态");backupTaskService.updateNotFinishedTask();log.info("项目启动成功，更新备份文件总数缓存");backupFileService.updateTotalCache();}
}

修改分页查询方法：在分页查询的时候，不要查询总数，总数从缓存中读取

@Override
public PageResponse<BackupFile> pageBackupFileV2(BackupFileRequest request, boolean isOrder) {long start = System.currentTimeMillis();QueryWrapper<BackupFile> queryWrapper = new QueryWrapper<>();if (request.getBackupSourceId() != null) {queryWrapper.eq("backup_source_id", request.getBackupSourceId());}if (request.getBackupTargetId() != null) {queryWrapper.eq("backup_target_id", request.getBackupTargetId());}if (!StringUtils.isEmpty(request.getSourceFilePath())) {queryWrapper.like("source_file_path", request.getSourceFilePath());}if (!StringUtils.isEmpty(request.getTargetFilePath())) {queryWrapper.like("target_file_path", request.getTargetFilePath());}queryWrapper.orderByDesc("id");Page<BackupFile> page = new Page<>(request.getCurrent(), request.getSize());// 关闭总记录数统计page.setSearchCount(false);IPage<BackupFile> pageResult = baseMapper.selectPage(page, queryWrapper);List<BackupFile> backupFileList = pageResult.getRecords();PageResponse pageResponse = new PageResponse();pageResponse.setRecords(backupFileList);pageResponse.setCurrent(request.getCurrent());pageResponse.setSize(request.getSize());pageResponse.setTotal(Cache.FILE_TOTAL_NUM.get());System.out.println("分页查询时间：" + (System.currentTimeMillis() - start) + "ms");return pageResponse;
}

新增文件、删除文件时更新缓存值

由于该系统仅为个人使用，对缓存的时效性要求没有那么高，因此我只在备份结束的时候更新缓存值即可

/*** 根据备份任务来进行备份** @param task                备份任务* @param ignoreFileList      忽略文件名列表* @param ignoreDirectoryList 忽略目录名列表*/
private void backUpByTask(Task task, List<String> ignoreFileList, List<String> ignoreDirectoryList) throws IOException {......// 更新备份文件总数缓存backupFileService.updateTotalCache();
}

测试

查询第一页数据仅需要17ms，性能得到了飞一般的提升

你以为到这里就优化完了吗？不不不，随着分页的深度逐步加深，查询的速度会越来越慢，请继续阅读下面的深分页问题

深分页问题优化

问题说明

当查看最后一页数据时（数据量有3,459,110条），发现耗时竟然接近 8 秒，性能还是太差了。原因：我们默认的分页是使用offset来实现的，假设有10000条数据，当我们查询最后一页时，即使我们只需要10条数据，数据库也需要先检索出前面的99990条记录并丢弃它们，才能得到我们需要的结果，所以这个过程很慢

==>  Preparing: SELECT id,backup_source_id,backup_target_id,source_file_path,target_file_path,backup_num,file_type,last_backup_time,file_name,file_suffix,file_length,file_length_after_compress,father_id,is_compress,is_contain_file,create_time,update_time FROM backup_file ORDER BY id DESC LIMIT ? OFFSET ?
==> Parameters: 10(Long), 3459100(Long)
......
<==      Total: 10
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3fc32129]
分页查询时间：7712ms

优化实现

首先根据偏移量查询id

<select id="selectIDByOffset" resultType="java.lang.Long">select idfrom backup_fileorder by id desc limit #{offset}, 1
</select>

再根据查询到的 id 来取后面 size 条数据

// 将所有sql包裹在一个事务中执行，避免创建两次SqlSession。设置为只读事务，因为这里没有更新操作
@Transactional(readOnly = true)
@Override
public PageResponse<BackupFile> pageBackupFileV3(BackupFileRequest request, boolean isOrder) {long start = System.currentTimeMillis();request.setOffset((request.getCurrent() - 1) * request.getSize());Long idByOffset = baseMapper.selectIDByOffset((request.getCurrent() - 1) * request.getSize());QueryWrapper<BackupFile> queryWrapper = new QueryWrapper<>();if (request.getBackupSourceId() != null) {queryWrapper.eq("backup_source_id", request.getBackupSourceId());}if (request.getBackupTargetId() != null) {queryWrapper.eq("backup_target_id", request.getBackupTargetId());}if (!StringUtils.isEmpty(request.getSourceFilePath())) {queryWrapper.like("source_file_path", request.getSourceFilePath());}if (!StringUtils.isEmpty(request.getTargetFilePath())) {queryWrapper.like("target_file_path", request.getTargetFilePath());}queryWrapper.orderByDesc("id");queryWrapper.le("id", idByOffset);queryWrapper.last("LIMIT " + request.getSize());List<BackupFile> backupFileList = baseMapper.selectList(queryWrapper);PageResponse pageResponse = new PageResponse();pageResponse.setRecords(backupFileList);pageResponse.setCurrent(request.getCurrent());pageResponse.setSize(request.getSize());pageResponse.setTotal(Cache.FILE_TOTAL_NUM.get());System.out.println("分页查询时间：" + (System.currentTimeMillis() - start) + "ms");return pageResponse;
}

有读者可能有疑问。为什么要分两次查询，不直接用一个子查询sql来实现呢？（例如下面的代码）我测试了，发现浅分页的时候，查询的结果没有问题，深分页之后，查出来的数据和直接分页查询的数据对不上，不知道是不是我用了分表，对子查询产生了影响（有知道的大佬求求在评论区教教我，非常感谢）

select f1.id,f1.backup_source_id,f1.backup_target_id,f1.source_file_path,f1.target_file_path,f1.backup_num,f1.file_type,f1.last_backup_time,f1.file_name,f1.file_suffix,f1.file_length,f1.file_length_after_compress,f1.father_id,f1.is_compress,f1.is_contain_file,f1.create_time,f1.update_time
from backup_file f1
where (select idfrom backup_fileorder by id desc limit #{request.offset} , 1) >= id
order by f1.id desclimit #{request.size}

测试

经过测试，发现最后一页的查询时间为 3.4 s，又把时间减少了一半

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9]
JDBC Connection [HikariProxyConnection@1178808009 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@2c08fcbd] will be managed by Spring
==>  Preparing: select id from backup_file order by id desc limit ?, 1
==> Parameters: 3459100(Long)
<==    Columns: id
<==        Row: 1760179379180195842
<==      Total: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9]
Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9] from current transaction
==>  Preparing: SELECT id,backup_source_id,backup_target_id,source_file_path,target_file_path,backup_num,file_type,last_backup_time,file_name,file_suffix,file_length,file_length_after_compress,father_id,is_compress,is_contain_file,create_time,update_time FROM backup_file WHERE (id <= ?) ORDER BY id DESC LIMIT 10
==> Parameters: 1760179379180195842(Long)
.....
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9]
分页查询时间：3492ms
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4cbb45b9]

explain效率比较分析

通过单元测试，发现时间主要花费在根据偏移量查询id，后面根据偏移 id 来查询数据就很快了。

==> Parameters:
<==    Columns: id
<==        Row: 1760179379180195842
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46b4d4e7]
id：1760179379180195842
查id时间：3169msCreating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3387d45e] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1063860793 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@1ef7e4c7] will not be managed by Spring
==>  Preparing: select id, backup_source_id, backup_target_id, source_file_path, target_file_path, backup_num, file_type, last_backup_time, file_name, file_suffix, file_length, file_length_after_compress, father_id, is_compress, is_contain_file, create_time, update_time from backup_file where ? >= id order by id desc limit 10
==> Parameters: 1760179379180195842(Long)
......
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3387d45e]
查数据时间：27ms

【直接分页查询】

explain SELECT id,backup_source_id,backup_target_id,source_file_path,target_file_path,backup_num,file_type,last_backup_time,file_name,file_suffix,file_length,file_length_after_compress,father_id,is_compress,is_contain_file,create_time,update_time FROM backup_file_5 ORDER BY id DESC LIMIT 1000,10

• 查询类型（type）为"index"，这意味着MySQL正在执行全索引扫描。这通常意味着查询只访问索引树上的数据，而不需要回表获取其他列的信息。
• possible_keys 列显示为空，表示没有指定任何可能使用的键。然而，key 列显示 PRIMARY，说明实际上使用了主键作为索引。
• key_len 列值为8，表明在主键上使用了完整的索引长度。对于一个整数类型的主键来说，这通常是正确的。
• ref 列显示为 NULL，这是因为在这个查询中没有涉及与其他表的关联操作。
• rows 列显示预计需要读取的行数为10,010。这表明查询将遍历大约10,010个索引项来找到满足条件的数据。
• Extra 列显示 “Backward index scan”，表示MySQL正在进行反向索引扫描。这通常发生在查询从高到低排序时，或者当查询优化器认为这样做更有效率时。

【根据偏移量查询id】

explain select id from backup_file_5 order by id desc limit 1000,1

从分析来看，很多指标和【直接分页查询】是一样的，区别是extra值为"Backward index scan; Using index" 表明正在进行反向索引扫描，并且只使用索引，无需回表查询原始数据

【根据偏移 id 来查询数据】

explain select id, backup_source_id, backup_target_id, source_file_path, target_file_path, backup_num, file_type, last_backup_time, file_name, file_suffix, file_length, file_length_after_compress, father_id, is_compress, is_contain_file, create_time, update_time from backup_file_5 where 7373278992159211536 >= id order by id desc limit 10

• 查询类型（type）为"range"，这意味着MySQL正在执行范围扫描。比全表扫描或全索引扫描要好
• possible_keys 列显示为空，表示没有指定任何可能使用的键
• key 列显示 PRIMARY，说明实际上使用了主键作为索引
• key_len 列值为8，表明在主键上使用了完整的索引长度。对于一个整数类型的主键来说，这通常是正确的
• ref 列显示为 NULL，这是因为在这个查询中没有涉及与其他表的关联操作
• rows 列显示预计需要读取的行数为124。这表明查询将遍历大约124个索引项来找到满足条件的数据
• Extra 列显示 “Using where; Backward index scan”，表示MySQL正在进行反向索引扫描，并应用了WHERE子句中的条件

【总结】

• 根据偏移量查询id：相对于直接分页查询，只使用 id 来查询，数据量更小，且无需回表操作查询其他字段，消耗的时间和资源少
• 根据偏移 id 来查询数据：只需要范围扫描，效率更高

总结

• 查询效率有了比较大的提升
  • 查询第一页，查询时间从5秒下降到ms级别，性能有巨大提升
  • 查询最后一页数据，直接分页查询耗时12.5 秒，改进查询下降到3.4 s，性能提升 3.6 倍
• 随着数据量的进一步提升，达到千万级，现在的实现方案在查询深分页时性能肯定会非常差，还需要进一步的优化。
• 其他常用的效率优化逻辑
  • 冷热数据分离：将不常使用的数据迁移到其他数据库中
  • 使用游标分页：记录上一页的最后一条数据id，这样查下一页就很快了，缺点是只能上下页，无法随意切换页