网站专题制作 公司,湖南电商网站建设,做UI设计的网站,做淘宝客网站要备案吗#x1f3af; 导读#xff1a;本文针对大数据量下的分页查询性能问题进行了深入探讨与优化#xff0c;最初查询耗时长达12秒#xff0c;通过避免全表计数及利用缓存保存总数的方式显著提升了浅分页查询速度。面对深分页时依然存在的延迟#xff0c;采用先查询倒数第N条记录… 导读本文针对大数据量下的分页查询性能问题进行了深入探讨与优化最初查询耗时长达12秒通过避免全表计数及利用缓存保存总数的方式显著提升了浅分页查询速度。面对深分页时依然存在的延迟采用先查询倒数第N条记录ID再依据此ID获取后继记录的策略进一步降低了查询时间。此方案适用于优化大量数据背景下的分页展示性能问题。 ️ 项目仓库数据增量备份系统 项目介绍【文件增量备份系统】系统功能介绍与开源说明 文章目录 问题说明原因排查total查询优化实现步骤在缓存类中添加一个原子类的备份文件总数属性实现一个更新缓存值的方法在项目启动成功之后调用上面方法记录total值修改分页查询方法在分页查询的时候不要查询总数总数从缓存中读取新增文件、删除文件时更新缓存值 测试 深分页问题优化问题说明优化实现测试explain效率比较分析 总结 问题说明
当数据量达到百万级时查询性能已经非常慢了 经过查看日志可以发现查询一次接口耗时高达 两年半 5 秒而且查的还是第一页等查完数据黄花菜都凉了受不了一点为了用户的体验必须改进
原因排查
原始代码如下对id进行降序排序是因为id是递增的id越大代表文件备份时间越新。对id进行排序是为了把最新备份的文件记录放在最前面
Override
public PageResponseBackupFile pageBackupFileV1(BackupFileRequest request, boolean isOrder) {long start System.currentTimeMillis();QueryWrapperBackupFile 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);IPageBackupFile 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_fileParameters:Columns: totalRow: 3458533Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession521d455c]
时间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 10Parameters: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.DefaultSqlSession327ac23]
时间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 QueryWrapperBackupFile().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 PageResponseBackupFile pageBackupFileV2(BackupFileRequest request, boolean isOrder) {long start System.currentTimeMillis();QueryWrapperBackupFile 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);PageBackupFile page new Page(request.getCurrent(), request.getSize());// 关闭总记录数统计page.setSearchCount(false);IPageBackupFile pageResult baseMapper.selectPage(page, queryWrapper);ListBackupFile 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, ListString ignoreFileList, ListString 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.DefaultSqlSession3fc32129]
分页查询时间7712ms优化实现
首先根据偏移量查询id
select idselectIDByOffset resultTypejava.lang.Longselect idfrom backup_fileorder by id desc limit #{offset}, 1
/select再根据查询到的 id 来取后面 size 条数据
// 将所有sql包裹在一个事务中执行避免创建两次SqlSession。设置为只读事务因为这里没有更新操作
Transactional(readOnly true)
Override
public PageResponseBackupFile 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());QueryWrapperBackupFile 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());ListBackupFile 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.DefaultSqlSession4cbb45b9]
JDBC Connection [HikariProxyConnection1178808009 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection2c08fcbd] will be managed by SpringPreparing: select id from backup_file order by id desc limit ?, 1Parameters: 3459100(Long)Columns: idRow: 1760179379180195842Total: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession4cbb45b9]
Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession4cbb45b9] from current transactionPreparing: 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 10Parameters: 1760179379180195842(Long)
.....
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession4cbb45b9]
分页查询时间3492ms
Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession4cbb45b9]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession4cbb45b9]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession4cbb45b9]explain效率比较分析
通过单元测试发现时间主要花费在根据偏移量查询id后面根据偏移 id 来查询数据就很快了。 Parameters:Columns: idRow: 1760179379180195842Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession46b4d4e7]
id1760179379180195842
查id时间3169msCreating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession3387d45e] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection1063860793 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection1ef7e4c7] will not be managed by SpringPreparing: 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 10Parameters: 1760179379180195842(Long)
......
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession3387d45e]
查数据时间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这样查下一页就很快了缺点是只能上下页无法随意切换页
