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

news/2024/9/29 6:12:56 标签: 性能优化, MySQL, 深分页优化, SpringBoot, Java

🎯 导读：本文针对大数据量下的分页查询性能问题进行了深入探讨与优化，最初查询耗时长达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 {

    @Autowired
    private BackupTaskService backupTaskService;

    @Autowired
    private BackupFileService backupFileService;

    @Override
    public 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 id
    from backup_file
    order 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 id
       from backup_file
       order by id desc limit #{request.offset} , 1) >= id
order by f1.id desc
    limit #{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时间：3169ms

Creating 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.niftyadmin.cn/n/5682541.html

相关文章

【python】函数介绍

【python】函数介绍

学习目标 快速体验函数的使用了解函数的作用 函数 函数&#xff1a;是组织好的&#xff0c;可重复使用的&#xff0c;用来实现特定功能的代码段。 name "xiaoming" length len(name) print(length)输出结果是&#xff1a; 8 为什么随时都可以使用 len() 统计长…
阅读更多...
uni-app在线预览pdf

uni-app在线预览pdf

这里推荐下载pdf.js 插件 PDF.js - Browse Files at SourceForge.net 特此注意 如果报 Promise.withResolvers is not a function 请去查看版本兼容问题 降低pdf.js版本提高node版本 下载完成后 在 static 文件夹下新建 pdf 文件夹&#xff0c;将解压文件放进 pdf 文件…
阅读更多...
【4.7】图搜索算法-DFS和BFS解根到叶子节点数字之和

【4.7】图搜索算法-DFS和BFS解根到叶子节点数字之和

一、题目 给定一个二叉树&#xff0c;它的每个结点都存放一个 0-9 的数字&#xff0c; 每条从根到叶子节点的路径都代表一个数字 。 例如&#xff0c;从根到叶子节点路径 1->2->3 代表数字 123。计算从根到叶子节点生成的所有数字之和。 说明 : 叶子节点是指没有子节点…
阅读更多...
PHP爬虫：获取商品销量详情API的利器

PHP爬虫：获取商品销量详情API的利器

在电子商务时代&#xff0c;商品的销量数据对于商家来说至关重要。它不仅能够帮助商家了解市场动态&#xff0c;还能够指导库存管理和营销策略。PHP作为一种流行的服务器端脚本语言&#xff0c;结合其强大的HTTP请求处理能力&#xff0c;可以有效地用于编写爬虫程序&#xff0c…
阅读更多...
对话总结：Scale AI的创始人兼CEO Alex Wang

对话总结：Scale AI的创始人兼CEO Alex Wang

AI的三大支柱 计算：主要由大公司如NVIDIA推动。算法：顶尖实验室如OpenAI主导。数据：Scale致力于推动数据进展。前沿数据的重要性 与人类智能相比较，前沿数据是AI发展的关键。互联网数据是机器与人类合作的结果。语言模型的发展 第一阶段：原始的Transformer论文和GPT的小规…
阅读更多...
动态顺序表的增删改查（数据结构）

动态顺序表的增删改查（数据结构）

目录 一、顺序表 二、静态顺序表 三、动态顺序表 3.1、动态顺序表的实现 3.2、动态顺序表的实现 3.3.1、结构体创建 3.3.2、初始化 3.3.3、销毁数据 3.3.4、增容空间 3.3.5、尾插数据 3.3.6、头插数据 3.3.7、删除尾数据 3.3.8、打印数据 3.3.9、删除头数据 3.3…
阅读更多...
Python中的机器学习：从入门到实战

Python中的机器学习：从入门到实战

机器学习是人工智能领域的一个重要分支&#xff0c;它通过构建模型来使计算机从数据中学习并做出预测或决策。Python凭借其丰富的库和强大的生态系统&#xff0c;成为了机器学习的首选语言。本文将从基础到实战&#xff0c;详细介绍如何使用Python进行机器学习&#xff0c;涵盖…
阅读更多...
【深度学习】注意力机制与自注意力机制详解

【深度学习】注意力机制与自注意力机制详解

深度学习中的注意力机制/自注意力机制详解 1. 注意力机制的通俗理解2. 注意力和自注意力机制的区别3. 自注意力机制原理与计算流程3.1 引入自注意力机制的目的与思想3.2 从向量角度理解 [R1]3.3 从Self-Attention核心公式理解 [R3] 4. 多头自注意力机制&#xff08;Multi-head …
阅读更多...
最新文章

Copyright @ 2022~2023