解决千万数据的查询慢以及插入速度慢的问题

业务背景

在开发物料系统主要是计算物料需求量，你可以理解为：一年 360天每天要生产苹果手机、华为手机 10000部，现在要计算组成这个手机的螺丝和螺钉在每一天需要多少个这个数量是指数级增长

螺钉 = 360 * 苹果手机数量10000 * 螺钉数量

螺钉 = 360 * 华为手机数量10000 * 螺钉数量

以此类推其他的零部件，组成这个产品所需要物料可能是几千个也可能是几百个，所以这个数据量会非常大，以下的优化思路都是我个人的想法，仅供参考

问题1: 查询慢 & 插入慢

在把物料清单和生产计划导入之后点击执行计划，就开始计算每个物料的日需求量并插入到数据库，未优化前几百万的数据插入耗时在半个小时甚至一个多小时都没有执行完，这个过程中有查询也有计算

解决思路

在不考虑硬件加配置的情况下，先考虑代码和数据库的优化。在用户角度来看查询对比插入后者优先级没那么高。但是这个顺序也很重要，我在先优化插入同时也考虑到查询，所以在数据库层面优化插入后，我的查询那边几乎就没什么大的改动了。

代码逻辑优化
数据库层面优化

具体解决方案

针对执行慢的接口，使用IntelliJ Profile或者日志打印排查出执行慢的那一块代码，发现计算的过程中最慢的反而是插入，以及删除，在每次计算之前都需要把之前计算的数据删除，然后插入新的数据这个方法经过排查有2个不正常的点：

当数据量到七百万左右，删除完之后才能插入，因为这个表有索引删除起来也会很浪费时间
表设计有问题，这个表大部分数据重复，某个物料的日需求可能几万行行数据大部分列数据都相同，不同的是日期和所在日期的需求量不同，在分页查询的时候，在 select count时就很慢了，物料信息和需求量结果，前者大概几万行，但是计算结果有几百万行

第一步：拆表

把日期和需求量单独建一个，如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create table m_relation_date_quantity
(
    id                    bigint auto_increment comment '主键id'
        primary key,
    relation_id           bigint           not null comment '关联 ID',
    date                  datetime         not null comment '日期',
    number                decimal(24, 6)   not null comment '数量',
    create_time           datetime         not null comment '建立時間',
    create_user_name      varchar(90)      not null comment '建立者外文姓名',
    last_update_time      datetime         not null comment '最後修改時間',
    last_update_user_name varchar(90)      not null comment '最後修改者英文名稱',
    soft_delete           bigint default 0 not null comment '邏輯刪除;其它 = 邏輯刪除, 0 = 未刪除'
) comment '中间表,这个只记录日期对应的数量';

create index m_relation_date_quantity_idx
    on m_relation_date_quantity (soft_delete, relation_id, date, number);

为什么把soft_delete也放到索引并在最左边，因为使用 mybatis-plus 软删除，会自动在 SQL 最左边加上软删除字段，所以放在最左边符合索引最左边匹配原则 再把关键查询的条件也要放在前面；date, number这两个不会特地通过条件去查，这里主要是命中索引时不需要回表，再查一遍。通过explain查看 SQL 语句分析返回结果中的key、type、rows、extra列

key：查看实际命中的索引，是否命中理想索引

type：查看索引类型

越往下性能越好：

type 值	含义	优劣
ALL	全表扫描	❌ 最差，说明索引失效
index	全索引扫描（不回表）	❌ 次差
range	范围扫描（BETWEEN、>、<）	⚠️ 一般
ref	普通等值匹配（使用索引）	✅ 很好
eq_ref	唯一匹配（主键或唯一索引）	✅ 极好
const / system	只返回一行结果	✅✅ 最优

💡一般我们希望： 单表查询至少达到 ref 或 eq_ref 级别。

rows：查看索引所匹配的行数，MySQL 优化器估计的扫描行数，越小越好。表示查询的结果数量，越少越精确

extra：查看索引所匹配的行数

常见几种情况：

Extra	含义	影响
Using where	需要额外过滤（正常）	✅ 正常现象
Using index	覆盖索引（无需回表）	✅ 性能很好
Using temporary	创建临时表	❌ 慢（常见于 GROUP BY、DISTINCT）
Using filesort	排序需要额外步骤	❌ 慢（常见于 ORDER BY）
Using index condition	部分索引条件（ICP）	⚠️ 一般，能接受
NULL	没额外操作	✅ 非常好

通过 explain 就能知道我们的SQL哪里有问题以此来重新设计表，也可以在代码中避免

第二步：去除插入前的删除

在计算之前删除数据，这里数据量大的情况下删除数据会很慢，同时也考虑同时删除和插入这也会导致锁行，因为你的索引要更新实际上并不会多快；并且加事务情况下如果出现错误，此时回滚就会很慢所以不能加事务，在计算需求量的方法中不能删除旧数据也不能更新数据，通过一个虚拟 ID(实时变化的 ID) 关联需求数据，这样计算的同时也可以看旧数据，在计算完成时把 虚拟 ID 替换一下，就可以查看最新的数据，但是有问题在于新增失败的问题，会存在无效数据，可以存在缓存中后续通过定时任务删除

为了防止数据一直增长写了一个定时任务定时去删除旧数据，因为要删除几百万到几千万都有可能，不能一次性删除否则会锁表，所以封装分批删除代码如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/**
 * 每次默认删除多少
 */
int DEFAULT_DELETE_LIMIT = 20000;

/**
 * 慎用，用之前需要看一下源码
 */
default int delete(Class<T> clazz, String expression) {
    String tableName;
    if (!AnnotationUtil.hasAnnotation(clazz, TableName.class) || StrUtil.isBlank((tableName = AnnotationUtil.getAnnotationValue(clazz, TableName.class)))) {
        throw new RuntimeException("实体类不存在表名");
    }
    int totalCount = 0;
    int count;
    do {
        count = deleteByTableName(tableName, DEFAULT_DELETE_LIMIT, expression);
        totalCount += count;
    } while (count == DEFAULT_DELETE_LIMIT);
    return totalCount;
}


/**
 * 动态删除,慎用不可以直接给前端使用
 *
 * @param tableName  表名
 * @param limit      删除数量
 * @param expression 表达式,慎用,默认删除必须要有条件
 * @return 删除数量
 */
int deleteByTableName(@Param("tableName") String tableName, @Param("limit") int limit, @Param("expression") String expression);

对应的 XML：注意#{}和${}的区别

1
2
3
4

<delete id="deleteByTableName">
    DELETE FROM ${tableName} where ${expression} LIMIT #{limit}
</delete>

第三步：优化(插入 & 连接池配置)

使用 mybatis-plus 批量插入数据实际底层是循环一条一条的插入，虽然是一次性发送到数据库一起执行。但还是建议使用 values插入，借此封装如下方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/**
 * SHOW VARIABLES LIKE 'max_allowed_packet'; 决定单条 SQL 大小最大 64MB
 */
int DEFAULT_INSERT_BATCH_SIZE = 10000;

/**
 * 批量保存而不是循环插入values(),(),这里不加事务保存，如需事务自己添加事务
 */
default boolean saveBatch(Collection<E> dataList, SqlSessionFactory sqlSessionFactory, String flag, boolean isPrintCostTime) {
    AtomicInteger total = new AtomicInteger(dataList.size());
    return CollUtil.split(dataList, DEFAULT_INSERT_BATCH_SIZE).stream().allMatch(splitDataList -> {
        long start = System.currentTimeMillis();
        MybatisBatch.Method<E> mapperMethod = new MybatisBatch.Method<>(this.getBaseMapper().getClass().getInterfaces()[0]);
        // 执行批量插入注意不是循环插入
        List<BatchResult> results = MybatisBatchUtils.execute(sqlSessionFactory, splitDataList, mapperMethod.insert());
        if (isPrintCostTime) {
            total.set(total.get() - splitDataList.size());
            CostTimePrintUtils.costTimePrint(flag, start, String.format("批量保存 %s 条 还剩 %s 条 总共 %s 条", splitDataList.size(), total.get(), dataList.size()));
        }
        //清空
        splitDataList.clear();
        return Objects.nonNull(results) && !results.isEmpty();
    });
}

可以使用SHOW VARIABLES LIKE 'max_allowed_packet',查看单条 SQL 大小，8版本默认64MB。

连接池优化

通过以下命令分析返回的信息合理配置项目中的连接池

1
2
3
4
SHOW STATUS LIKE 'Threads%'; #查询连接数
SHOW PROCESSLIST; #查询正在连接的客户端
SHOW VARIABLES LIKE '%max_connections%'; #查询最大连接数
show variables like 'innodb_buffer_pool_size'; #查询缓存池大小

第四步：查询优化

在大量数据情况下尽量避免复杂 SQL，复杂 SQL 尽量拆分成多次查询，查询时只返回需要的列，因为大部分项目的表都存在很多通用字段，我所在的项目中通用字段就有7 - 8个
避免大表驱动小表
创建索引优化查询速度，不要创建很多只针对查询很频繁的创建，多个频繁查询字段使用联合索引，这样命中后无需回表查询
多使用explain 检查 SQL ，因为有时 SQL 很难一步到位需要慢慢的调试

第五步：代码优化

使用多线程，本次项目为了计算需求数据，需要提前从很多表中拉数据，拉数据的过程中是没有关联性的可以同时异步去拉
避免循环中套大批量数据循环，我在优化的过程中发现外层循环中有一个内层循环，这个内层循环List 高达几万，也就是外层循环的次数乘以内层循环的次数，这样会很慢，所以尽量避免这种循环嵌套
避免一次性查询所有数据返回防止OOM，尽量使用分页查询
导出和导入改为异步，避免阻塞，导入时数据量过大，会导致页面就一直卡在转圈圈通常超时很影响用户体验，改为异步，增加一个状态来表示正在解析导入的数据，导出可以做成导出任务，给状态和下载按钮

第六步：事务

大数据量情况事务尽量避免使用，因为他的回滚或者一次性提交都会很慢，但是为了数据一致性的需要做出补偿，比如上文中的数据插入失败就通过虚拟 ID 保证删除失败依旧能看到旧数据

End

当然优化还是要看具体的项目和场景来判断。以上就是我总结的一些优化点，有一些通用的可以参考