MySql之in查询优化

1、功能

        信息模块用户阅读的统计量

2、sql响应时间

        5s左右

3、经过查看，是因为单表数据量过大，且用了mysql的in子查询。所以很慢。

4、优化方式

        4.1、连接优化

使用left join on优化，速度确实提升了，但是依旧不太好。以下是in的sql优化代码（部分）

# 优化前
SELECT
    sum( read_number ) AS read_total_number,
    information_id AS id,
    is_read 
FROM
    mgr_relation_information_user 
WHERE information_id IN ( SELECT 
                            information_id 
                          FROM mgr_relation_information_user 
                          WHERE user_id = 332032 ) 
GROUP BY information_id 

# 优化后
SELECT
    sum( a.read_number ) AS read_total_number,
    a.information_id AS id,
    a.is_read 
FROM mgr_relation_information_user a
left join mgr_relation_information_user b on a.information_id = b.information_id
where b.user_id = 332032
GROUP BY a.information_id 

        4.2、分页优化（基于mysql之in条件200条左右性能最佳）

// 查询所有informationId
        List<Long> list = sqlOptimizeMapper.selectCount();
        if (list.size() > 200) {
            for (int start = 0; start < list.size(); start = start + 200) {
                // 200条为一批条件
                List<Long> list1 = list.subList(start, Math.min(start + 200, list.size() - 1));
                if (list1.size() > 0) {
                    // in 查询
                    List<Map<String, Object>> map = sqlOptimizeMapper.select3(list1);
                }
            }
        }

        4.3、多线程优化（基于4.2分页，速度最快）

// 查询所有informationId
        List<Long> list = sqlOptimizeMapper.selectCount();
        if (list.size() > 200) {
            ExecutorService executorService = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());

            for (int start = 0; start < list.size(); start = start + 200) {
                // 200条为一批条件
                List<Long> list1 = list.subList(start, Math.min(start + 200, list.size() - 1));
                if (list1.size() > 0) {
                    // 线程池
                    executorService.execute(() -> {
                        // in 查询
                        List<Map<String, Object>> map = sqlOptimizeMapper.select4(list1);
                    });
                }
            }
            // 关闭资源
            executorService.shutdown();
        }