Mysql where in 的范围非常多时如何优化?

问题描述:
数据库Mysql8,某个单表几百万的数据量,产品层面上做分页查询,业务上存在select count(*) from records where sale_id in (list) and create_time between start and end
select * from records where sale_id in (list) and create_time between start and end order by create_time desc limit 0, 10的需求,list的数量可能会非常多,可能会几千个,导致查询非常慢,接口超时。

之所以list非常多是因为,选项是个多级树形结构,产品层面可以跨级多选父级,代码在做查询的时候会把选中的sale_id统一转化为叶子节点这一级,叶子总量有1W+, 目前如果只勾选1个1级节点,list有几千个,查询需要6s。

考虑过冗余记录 parent_id序列,但是这个层级对应关系会时长发生变动,如果冗余记录parent_id序列,对应关系变动时会引发大批量的历史数据的更新。

请问目前这种问题应当如何进行优化?

《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
讨论数量: 10
GDDD

file

2周前 评论
Complicated 2周前

where in 最好不要超过100 条数据 多了最好用join

2周前 评论
穿过你的黑发的我的手 (楼主) 2周前
lijizheng (作者) 1周前
lijizheng (作者) 1周前

laravel orm 自动会把 id 罗列出来 这种数据量大了就不合适了

2周前 评论

临时表:

创建一个临时表并将所有的值插入到这个表中。
使用JOIN或EXISTS子句来代替IN,以便与临时表进行比较。

CREATE TEMPORARY TABLE temp_values (id INT PRIMARY KEY);
INSERT INTO temp_values VALUES (...);

SELECT * FROM target_table
WHERE EXISTS (SELECT 1 FROM temp_values WHERE temp_values.id = target_table.id);
1周前 评论
周小云 (作者) 1周前

试了两种方法有效

1.加sale_id和create_time的复合索引
2.把sale_id改成varchar
1周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!