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

server/2024/10/19 6:56:18/

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

文章目录

  • 问题说明
  • 原因排查
  • total查询优化
    • 实现步骤
      • 在缓存类中添加一个原子类的备份文件总数属性
      • 实现一个更新缓存值的方法
      • 在项目启动成功之后，调用上面方法记录total值
      • 修改分页查询方法：在分页查询的时候，不要查询总数，总数从缓存中读取
      • 新增文件、删除文件时更新缓存值
    • 测试
  • 深分页问题优化
    • 问题说明
    • 优化实现
    • 测试
    • explain效率比较分析
  • 总结

问题说明

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

在这里插入图片描述
经过查看日志，可以发现查询一次接口，耗时高达 两年半 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，这样查下一页就很快了，缺点是只能上下页，无法随意切换页

http://www.ppmy.cn/server/123367.html

相关文章

每日学习一个数据结构-DAG有向无环图

每日学习一个数据结构-DAG有向无环图

文章目录 有向无环图的特性使用场景 有向无环图&#xff08;Directed Acyclic Graph&#xff0c;简称DAG&#xff09; 是一种特殊的图结构&#xff0c;在数学和计算机科学领域有广泛应用。它由顶点&#xff08;vertices&#xff09;和边&#xff08;edges&#xff09;组成&…
阅读更多...
Java线程基础

Java线程基础

线程 1.什么是程序? 是为了完成特定任务,用某种语言编写的一组指令的集合 进程 1.进程是指运行中的程序&#xff0c;比如我们使用QQ&#xff0c;就启动了一个进程&#xff0c;操作系统就会为该进程分配内存空间。当我们使用迅雷&#xff0c;又启动了一个进程&#xff0c;操…
阅读更多...
Study--Oracle-09--部署Openfiler存储服务器

Study--Oracle-09--部署Openfiler存储服务器

免费的存储服务器软件有FreeNAS 和 Openfiler。 其中Freenas的网站上只有i386及amd64的版本，也就是说Freenas不能支持64位版本的Intel CPU，而Openfiler则提供更全面的版本支持，在其网站上可以看到支持多网卡、多CPU，以及硬件Raid的支持，还有10Gb网卡的支持。 Openfiler能把…
阅读更多...
Java生成Markdown格式内容

Java生成Markdown格式内容

前一篇写的是markdown格式的文本内容转换保存为word文档&#xff0c;是假定已经有一个现成的markdown格式的文本&#xff0c;然后直接转换保存为word文档&#xff0c;不过在开发中&#xff0c;通常情况下&#xff0c;数据是从数据库中获取&#xff0c;拿到的数据映射到java对象…
阅读更多...
cv2.waitkey(30) 按键盘无效

cv2.waitkey(30) 按键盘无效

cv2.imshow("detection", color_image) # 窗口显示&#xff0c;显示名为 Capture_Videok cv2.waitKey(100) & 0xFF # 每帧数据延时 1ms&#xff0c;延时不能为 0&#xff0c;否则读取的结果会是静态帧 if k ord(s): # 键盘按一下s, 保存当前照片和机械臂位姿…
阅读更多...
数组中两个字符串的最小距离

数组中两个字符串的最小距离

1.题目&#xff1a; 2.解析&#xff1a; 这里利用预处理思想&#xff1a;要找多个位置先记录下某个位置&#xff1a; 这里 i 遍历来记录s1,和s2的位置&#xff0c;不断更新来找到最小距离。 代码&#xff1a; public static void main(String[] args) throws IOException {Buff…
阅读更多...
【2023工业3D异常检测文献】基于混合融合的多模态工业异常检测方法Multi-3D-Memory (M3DM)

【2023工业3D异常检测文献】基于混合融合的多模态工业异常检测方法Multi-3D-Memory (M3DM)

Multimodal Industrial Anomaly Detection via Hybrid Fusion 1、Background 随着3D传感器的发展&#xff0c;最近发布了具有2D图像和3D点云数据的MVTec-3D AD数据集&#xff0c;促进了多模态工业异常检测的研究。 无监督异常检测的核心思想是找出正常表示与异常之间的差异。…
阅读更多...
Java中的Junit、类加载时机与机制、反射、注解及枚举

Java中的Junit、类加载时机与机制、反射、注解及枚举

目录 Java中的Junit、类加载时机与机制、反射、注解及枚举 Junit Junit介绍与使用 Junit注意事项 Junit其他注解 类加载时机与机制 类加载时机 类加载器介绍 获取类加载器对象 双亲委派机制和缓存机制 反射 获取类对象 获取类对象的构造方法 使用反射获取的构造方法创建对象 获…
阅读更多...
最新文章

Copyright @ 2022~2023