hello，大家好，我是灰小猿！

复杂业务下大数据量查询性能优化可谓是让众多程序猿朋友最为头疼的事情，最近我也是在做一些相关的研究，总结了一些优化心得分享给大家，文章中内容均是依据个人业务和实践得出的结论，如有不足或可以补充的地方，欢迎小伙伴们一起交流！

建议小伙伴们如果要参考该文章进行优化时，最好按照我下面列举的顺序依次检查自己的代码并优化，这也是优化难度和对代码的改动量依次由易到难的过程，可能你优化到某一步骤的时候就已经达到了你想要的效果！

1、大数据量查询在mapper里面手写sql查询，不要使用MybatisPlus的自动映射，

MyBatis-Plus 是在 MyBatis 的基础上进行扩展的，它提供了一些便利的功能，比如自动生成 SQL、代码生成器、内置的分页查询等。这些功能虽然提高了开发效率，但也可能导致性能开销增加。以下是一些使用 MyBatis-Plus 查询大数据时速度慢于 MyBatis 的原因：

1. 反射机制：MyBatis-Plus 使用反射来将查询结果映射到 Java 对象中。反射的性能通常低于直接访问属性，因为它涉及到动态解析类的信息，这在处理大量数据时可能会显著影响性能。
2. 类型处理器：MyBatis-Plus 内置的类型处理器在进行数据映射时可能会有额外的转换开销，尤其是在处理复杂类型（如自定义对象、集合等）时，转换过程可能较为耗时。
3. 字段映射：如果数据库字段与 Java 对象属性不完全匹配，MyBatis-Plus 需要额外的映射处理，这会增加时间开销。特别是在使用 @TableField 等注解时，复杂的映射逻辑可能会影响性能。
4. 大量数据：当查询的数据量很大时，映射过程的开销会随着数据量的增加而线性增长，导致整体性能下降。

所以在查询数据量不大的情况下，使用mybatisPlus自带的查询方法不会有什么问题，但是当查询数据量较大（建议数据量上千之后）时，不推荐再使用mybatisPlus的自动映射了，这个时候推荐使用mybatis原生的方式在mapper中手写sql实现，可以避免上述中出现的问题。

2、（重要）非必要字段坚决不查询

这一条是最重要但是也最容易忽略的地方，比如很多sql在查询时往往只需要ID和名称，但是却将整张表的全部属性都查询了出来，这无疑是增加了属性映射的时间，从而影响了整体的查询效率，

即使只是多查询了一个字段，也是有可能让你的查询性能受到很大的影响的，这一点还是要格外注意！

错误写法

List<UserModel> queryBatchUserByIdsWithMyBatisPlus(List<String> ids) {
    //根据ID查询user的全部字段
    List<UserModel> userModels = userMapper.selectListByIds(ids);
    return userModels;
}

只查询关键数据写法：

List<UserModel> queryBatchUserByIdsWithMyBatisPlus(List<String> ids) {
    //根据ID只查询id和名称字段
    List<UserModel> userModels = userMapper.selectColumnListByIds(ids,UserModel::getId,UserModel::getName);
    return userModels;
}

3、大量数据查询或复杂sql考虑采用JdbcTemplate编写原生sql查询

首先说一下使用org.springframework.jdbc.core.JdbcTemplate进行查询的缺点：

1. 写起来比较复杂，一般情况一个场景下的查询就要一个单独的Mapper查询类
2. 查询场景较多时会出现Mapper中代码冗长的情况，不利于维护

但是抛开这些缺点不说，使用JdbcTemplate进行查询的查询效率无疑最接近于直接进行mysql查询的，所以对于这种查询方式，我的建议是在上面使用mybatis进行查询效率仍然不理想的情况下，才会建议使用这种查询方式。

具体使用方式如下：

1、实现对应对象的JDBC查询类，建议一个对象有一个自己的JDBCMapper类，如果该对象有多种不同的查询场景，那么就在这个JDBCMapper下新建不同的子类。

import org.example.entity.UserModel;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;

public class UserJdbcMapper implements RowMapper<UserModel> {
    @Override
    public UserModel mapRow(ResultSet resultSet, int rowNum) throws SQLException {
        UserModel userModel = new UserModel();
        //下面的数字1234主要是标识sql查询结果中该属性对应的是第几列
        userModel.setId(resultSet.getInt(1));
        userModel.setAge(resultSet.getInt(2));
        userModel.setTel(resultSet.getString(3));
        userModel.setName(resultSet.getString(4));
        return userModel;
    }

    public String getSql(List<String> ids) {
        String sql = "SELECT * FROM user WHERE id IN (%S)";
        //将ID集合拼接成sql中的格式
        String idsStr = ids.stream().collect(Collectors.joining("','", "'", "'"));
        return String.format(sql, idsStr);
    }

}

2、查询方法调用JDBC查询

@RequiredArgsConstructor
@Repository
public class UserDAO {

    private final UserMapper userMapper;
    private final JdbcTemplate jdbcTemplate;

    List<UserModel> queryBatchUserByIds(List<String> ids) {
        //调用JDBC原生查询
        UserJdbcMapper userJdbcMapper = new UserJdbcMapper();
        List<UserModel> userModels = jdbcTemplate.query(userJdbcMapper.getSql(ids), userJdbcMapper);
        return userModels;
    }
}

4、检查索引是否生效

对于在代码中执行缓慢的sql，建议将其拿到mysql的可视化工具中执行一下，观察其耗时情况，以确定耗时的原因是sql本身还是使用的持久化框架的问题，如果是持久层框架的问题，建议尝试上面的第1/2种方式，如果是sql本身就存在耗时，那么就应该使用Explain对sql的执行效率进行监控分析，检查索引是否生效或联表方式是否合理。

下面我们逐个分析：

（1）检查是否使用索引或索引是否生效

引起索引不生效的可能原因有：

1、查询条件不匹配：

• 使用了不等于（!=）、IS NULL 或 IS NOT NULL 等条件，通常会导致索引失效。
• 对于范围查询，索引可能只在某些条件下生效，比如使用了 BETWEEN 或者 >、< 的时候。

2、采用IN查询且IN中的数据范围较大

如果查询条件中存在IN查询，一般情况下IN中的条件范围在大于3000的时候查询性能会明显下降并且可能会造成索引失效，这种一般建议采用多线程分批查询，最后再对数据进行聚合。

3、数据类型不一致：

查询条件中的数据类型与索引字段的数据类型不匹配，可能导致索引无法被利用

4、函数使用：

在查询中对索引字段使用了函数（如 LOWER(), YEAR() 等），会导致索引失效，因为数据库无法直接利用索引。

5、排序和分组：

在使用 ORDER BY 或 GROUP BY 的时候，如果字段没有被索引，或者排序的字段与索引不匹配，可能会导致全表扫描。

6、复合索引的使用：

对复合索引的查询条件不完整，可能会导致索引不生效。例如，复合索引的第一列必须出现在查询条件中，才能有效利用。

7、表的更新和碎片：

当表数据频繁更新、插入、删除时，索引可能会变得不够优化，导致性能下降。定期重建索引有助于改善这一点。

（2）检查联表方式

检查联表查询方式是否合理，是否是小表驱动大表，尝试不同的联表查询方式并对比最优的查询sql

5、多个的单表查询不一定比连接多个表查询快，要比较单表和连表查询性能后再选择

在进行一些多表关联查询的业务场景时，很多人往往喜欢偏向于全部使用单表查询+业务代码处理的方式，或者完全使用联表查询获取查询结果，但是仔细去分析某一个存在性能问题的业务场景，单表查询不一定优于联表，联表也不一定就优于单表，具体还是需要分别去采用这两种不同的方式进行查询后比较性能，择优选择！

甚至在有些情况下需要进行单表和联表结合使用，才能将对应的查询需求的性能发挥到极致，这些都是需要不断的对某一段问题代码打磨后才能得到的。

6、考虑采用多线程同步执行耗时任务

（1）业务逻辑并行执行

这里需要对整个接口的执行性能进行监控，找出存在耗时的逻辑，然后根据你的实际业务需求，分析可否将这些耗时逻辑采用多线程同步执行，等待这些子线程的数据处理完毕之后，再进行下一步的操作。

但是要注意“水桶原理”，多线程执行的任务的最终总耗时是取决于执行最慢的那一个子线程，所以在采用多线程执行时要考虑合理分配哪些任务由哪些线程执行，并不是开的子线程越多越好。

（2）大数据分批并行查询

另外在查询数据量过大或条件过多时，同样可以使用多线程分批查询来进行优化，之后再对数据进行聚合，如使用SelectListIn的方式进行数据查询，此时In的条件集合过大，可能上千上万个，此时如果直接将所有的条件使用一条In查询，那么查询性能不但非常慢，而且大In还可能引起索引失效，

因此这种情况下就建议拆分In的条件集合，分成多批次利用线程池查询，一般建议拆分的阈值为3000（也可以视情况而定）。举例如下：

List<UserModel> queryBatchUserByIdsWithSplit(List<String> ids) {
    //每3000条拆分查询
    List<List<String>> idsList = CollectionUtil.split(ids, 3000);
    List<FutureTask<List<UserModel>>> futureTasks = new ArrayList<>();
    for (List<String> splitIds : idsList) {
        FutureTask<List<UserModel>> futureTask = new FutureTask<>(() -> userMapper.selectListByIds(splitIds));
        executor.execute(futureTask);
        futureTasks.add(futureTask);
    }
    //聚合每一个线程的执行结果
    List<UserModel> userModels = new ArrayList<>();
    for (FutureTask<List<UserModel>> futureTask : futureTasks) {
        try {
            List<UserModel> tempUserModels = futureTask.get();
            userModels.addAll(tempUserModels);
        } catch (InterruptedException  | ExecutionException e) {
            throw new RuntimeException(e);
        } 
    }
    return userModels;
}

7、审阅代码，合并相似或重复查询

这一点主要是对相关的功能需求和代码逻辑进行二次梳理，检查代码中前后是否存在相似查询，或者说一个查询在方法外执行一遍，在方法内又执行一遍的情况，

审阅代码时主要可以从以下几方面入手：

1. 修改合并重复查询的代码为批量查询，减少数据库请求，并将结果传参方式传递
2. 审查需求，检查查询逻辑是否可以优化，减少非必要的条件查询
3. 结合需求检查是否可以在数据表中增加冗余字段，以达到减少查询条件的目的
4. 检查返回数据结果是否过于复杂，尝试拆分接口功能
5. 非必要的显示数据考虑是否可以做成懒加载，后面需要时再查询

推荐代码性能日志打印工具：StopWatch

最后推荐一个性能监控日志工具类StopWatch，相对于以log的形式计算时间再输出性能的方式，StopWatch使用起来更加简洁友好，不足是官方自带的输出的性能耗时是毫秒级别的，阅读体验感较差，我这里提供了一个格式化工具方法，直接传入你的StopWatch对象，即可规范输出以秒为单位的性能监控信息。

/**
* 打印日志输出时间以秒为单位
*/
private String prettyPrintBySecond(StopWatch stopWatch) {
    StopWatch.TaskInfo[] taskInfos = stopWatch.getTaskInfo();
    StringBuilder sb = new StringBuilder();
    sb.append('\n');
    sb.append("StopWatch '").append(stopWatch.getId()).append("': running time = ").append(stopWatch.getTotalTimeSeconds()).append(" s");
    sb.append('\n');
    if (taskInfos.length < 1) {
        sb.append("No task info kept");
    } else {
        sb.append("---------------------------------------------\n");
        sb.append("s         %     Task name\n");
        sb.append("---------------------------------------------\n");
        NumberFormat nf = NumberFormat.getNumberInstance();
        nf.setMinimumFractionDigits(3);
        nf.setGroupingUsed(false);
        NumberFormat pf = NumberFormat.getPercentInstance();
        pf.setMinimumIntegerDigits(2);
        pf.setMinimumFractionDigits(2);
        pf.setGroupingUsed(false);
        for (StopWatch.TaskInfo task : taskInfos) {
            sb.append(nf.format(task.getTimeSeconds())).append("  ");
            sb.append(pf.format((double) task.getTimeNanos() / stopWatch.getTotalTimeNanos())).append("  ");
            sb.append(task.getTaskName()).append("\n");
        }
    }
    return sb.toString();
}

以上就是我在以mybatisPlus作为持久层框架开发时，排查并解决接口性能问题的一些思考和解决，如果有不足或可以优化的地方，欢迎大家一起交流。

我是灰小猿，我们下期见！