MySQL中IN子句包含大量值时查询性能下降且未使用预期索引的解决方法

我之前也碰到过类似的IN子句塞满几千个值导致查询暴跌、执行计划乱跳的问题，结合你的场景，给你几个实用的解决方案：

1. 用临时表替代IN子句 #

当IN里的数值量过大时，数据库优化器很容易“判断失误”，换成临时表关联的方式能让执行计划更稳定：

-- 先创建临时表并插入需要筛选的entry_id
CREATE TEMPORARY TABLE temp_entry_ids (entry_id INT PRIMARY KEY);
INSERT INTO temp_entry_ids VALUES (0),(649),(650),...; -- 替换成你的几千个值

-- 修改查询，用JOIN替代IN子句
EXPLAIN SELECT DISTINCT t.entry_id , t.sticky , wd.field_id_104 , t.title 
FROM exp_channel_titles AS t 
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id 
LEFT JOIN exp_members AS m ON m.member_id = t.author_id 
INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id 
INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
INNER JOIN temp_entry_ids te ON t.entry_id = te.entry_id
WHERE t.entry_id !='' 
  AND t.site_id IN ('1') 
  AND t.entry_date < 1610109517 
  AND (t.expiration_date = 0 OR t.expiration_date > 1610109517);

临时表的主键会帮数据库快速完成关联匹配，彻底避开大IN子句带来的优化器判断偏差。

2. 强制优化器使用指定索引 #

如果exp_channel_titles表的entry_id上有索引（比如主键索引），可以直接用FORCE INDEX引导优化器走正确的索引路径，避免它因为IN值过多而选择全表扫描这类糟糕的计划：

EXPLAIN SELECT DISTINCT t.entry_id , t.sticky , wd.field_id_104 , t.title 
FROM exp_channel_titles AS t FORCE INDEX (PRIMARY) -- 这里替换成你实际的索引名
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id 
LEFT JOIN exp_members AS m ON m.member_id = t.author_id 
INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id 
INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
WHERE t.entry_id !='' 
  AND t.site_id IN ('1') 
  AND t.entry_date < 1610109517 
  AND (t.expiration_date = 0 OR t.expiration_date > 1610109517) 
  AND t.entry_id IN ('0','649','650',...); -- 你的大量值

注意要确认指定的索引确实能覆盖查询条件，不然强制索引可能反而适得其反。

3. 拆分查询为多个小批量请求 #

把几千个值拆分成多个包含几百个值的小IN子句，分别查询后用UNION ALL合并结果。比如每次查500个entry_id：

SELECT DISTINCT t.entry_id , t.sticky , wd.field_id_104 , t.title 
FROM exp_channel_titles AS t 
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id 
LEFT JOIN exp_members AS m ON m.member_id = t.author_id 
INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id 
INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
WHERE t.entry_id !='' 
  AND t.site_id IN ('1') 
  AND t.entry_date < 1610109517 
  AND (t.expiration_date = 0 OR t.expiration_date > 1610109517) 
  AND t.entry_id IN ('0','649',...); -- 第一批500个值

UNION ALL

SELECT DISTINCT t.entry_id , t.sticky , wd.field_id_104 , t.title 
FROM exp_channel_titles AS t 
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id 
LEFT JOIN exp_members AS m ON m.member_id = t.author_id 
INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id 
INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
WHERE t.entry_id !='' 
  AND t.site_id IN ('1') 
  AND t.entry_date < 1610109517 
  AND (t.expiration_date = 0 OR t.expiration_date > 1610109517) 
  AND t.entry_id IN ('1001','1002',...); -- 第二批500个值

这种方式能让优化器对每个小IN子句保持最优执行计划，避免单个大IN带来的性能雪崩。

4. 优化关联表的索引 #

除了主表exp_channel_titles的entry_id索引，还要检查关联表的索引是否到位：

• 确保exp_category_posts的entry_id和cat_id有复合索引
• 确保exp_channel_data的entry_id有单独索引

比如给exp_category_posts加复合索引：

CREATE INDEX idx_cat_posts_entry_cat ON exp_category_posts(entry_id, cat_id);

如果关联字段没有索引，哪怕主表走了索引，关联阶段的全表扫描也会拖慢整个查询。

不管用哪种方案，都要记得用EXPLAIN查看执行计划，确认是否走了预期的索引，有没有出现Using filesort或者Using temporary这类性能瓶颈提示。

内容的提问来源于stack exchange，提问作者Oleksandr IY