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条左右性能最佳)文章来源:https://www.toymoban.com/news/detail-579550.html
// 查询所有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分页,速度最快)文章来源地址https://www.toymoban.com/news/detail-579550.html
// 查询所有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();
}
到了这里,关于MySql之in查询优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!