查询in数量优化与多线程优化

Geho

已于 2024-01-12 09:51:58 修改

阅读量610

点赞数 14

于 2024-01-11 16:05:11 首次发布

本文链接：https://blog.youkuaiyun.com/HHCS231/article/details/135532208

版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

文章讨论了在SQL查询中遇到大量in值时可能导致卡死的问题，提出通过将值分组并使用多线程或调整线程池配置来提高查询效率的方法，同时提供了Java代码示例以实现这一优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题

一个查询语句，某一个字段的 in 值非常多，在执行SQL的时候，直接卡死

select * from user where deleted = 0 and code in (1,2,3,4,.... more than 9999)

解决方案

将code中的值分组，分组大小取决于机器的性能，最好不要超过500

分组后分别执行SQL语句进行查询

如果机器性能较好，可以使用多线程来进行查询，提高查询效率

select * from user where deleted = 0 and code in (1,2,3,4,....,200);
select * from user where deleted = 0 and code in (201,,....,400);
select * from user where deleted = 0 and code in (401,,....,600);
....

具体实现

将数据集拆分成200个一组

public class ListUtils {
    public static <T> List<List<T>> subList(List<T> list, Integer shardingSize) {
        List<List<T>> result = new ArrayList<>();
        if (list == null || list.size() == 0 || shardingSize == 0) {
            return result;
        }
        int index = 0;
        int start = 0;
        int end = 0;
        while (index < list.size()) {
            start = index;
            //如果越界，那就取最大值
            end = Math.min(index + shardingSize, list.size());
            List<T> subList = list.subList(start, end);
            result.add(new ArrayList<>(subList));
            index += shardingSize;
        }
        return result;
    }
}

//大小设置为200一个，根据设备性能调整
List<List<String>> subCodeList = ListUtils.subList(codeList, 200);

定义一个线程池，使用多线程查询时使用

@Configuration
public class ThreadPoolConfig {
    @Bean(name="poolTaskExecutor")
    public ThreadPoolTaskExecutor tskExecutor() {
        ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
        //设置线程池参数信息
        //核心线程数
        taskExecutor.setCorePoolSize(10);
        //最大线程数量        
        taskExecutor.setMaxPoolSize(15);
        taskExecutor.setQueueCapacity(Integer.MAX_VALUE);
        taskExecutor.setKeepAliveSeconds(60);
        taskExecutor.setThreadNamePrefix("--tskExecutor--");
        taskExecutor.setWaitForTasksToCompleteOnShutdown(true);
        taskExecutor.setAwaitTerminationSeconds(60);
        //修改拒绝策略为使用当前线程执行
        taskExecutor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        //初始化线程池
        taskExecutor.initialize();
        return taskExecutor;
    }
}

使用多线程优化查询数据库

List<UserDO> userDOList = new ArrayList();
try {
     //全流式处理转换成CompletableFuture[]+组装成一个无返回值CompletableFuture，join等待执行完毕。返回结果whenComplete获取
     CompletableFuture[] cfs = cardCodeSubList.stream().map(subList -> CompletableFuture.supplyAsync(() -> queryUser(subList), threadPoolTaskExecutor)
            .whenComplete((v, e) -> userDOList.addAll(v))).toArray(CompletableFuture[]::new);
     //等待总任务完成，但是封装后无返回值，必须自己whenComplete()获取
     CompletableFuture.allOf(cfs).join();
} catch (Exception e) {
     throw new UserFriendlyException("多线程根据code查询用户 异常", e);
}

public List<UserDO> queryUser(List<String> codeList) {
	List<UserDO> userList = userMapper.selectByCodeList(codeList);
	return userList;
}

优化调整

通过调整拆分集合的大小和线程池的线程数量，比较单线程查询和多线程查询所需要消耗的时间，得到最优效率。