MySQL中IN operator替代方案及慢查询优化：降低query cost求助

生产环境中使用包含IN运算符的查询时性能极低，查询语句如下：

SELECT col1 FROM table_name WHERE col2 IN (251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,
270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,1,2,3,4,5,6,7,8,9,10,11,12,13,14) AND col3 > '1' AND col4 = 1 GROUP BY col1;

已尝试FIND_IN_SET但性能无改善，已创建与WHERE子句列顺序一致的复合索引，当前查询成本为151630.44，过滤率100%，表数据量百万级，期望将查询成本降至1或2，同时保持过滤率100%。

优化方案 #

• 调整复合索引顺序，使用覆盖索引
  把等值匹配列col4放在索引最前列，再依次放col2、col3，最后包含col1，创建索引：CREATE INDEX idx_col4_col2_col3_col1 ON table_name(col4, col2, col3, col1);。
  数据库会先通过col4=1快速过滤数据，再匹配col2的IN条件，接着筛选col3>'1'，最后直接从索引中获取col1完成GROUP BY，无需回表，大幅降低查询成本。

• 拆分IN列表为连续范围
  观察IN中的数值，251-298、1-14都是连续范围，将IN条件改写为范围判断：

  SELECT col1 FROM table_name 
  WHERE col4 = 1 
  AND (col2 BETWEEN 251 AND 298 OR col2 BETWEEN 1 AND 14) 
  AND col3 > '1' 
  GROUP BY col1;
  

  数据库对连续范围的索引扫描效率远高于大量离散值的IN，能减少索引跳转次数，提升性能。

• 修正数据类型匹配问题
  检查col3的数据类型：如果是数值类型，把col3 > '1'改为col3 > 1，类型不匹配会导致索引失效，触发全表扫描。

• 验证索引执行计划
  执行EXPLAIN命令查看索引使用情况：

  EXPLAIN SELECT col1 FROM table_name WHERE col2 IN (...) AND col3 > '1' AND col4 = 1 GROUP BY col1;
  

  确认key列显示目标复合索引，Extra列出现Using index（覆盖索引），这是最优执行状态。

• 用JOIN替代IN（高频查询场景）
  若该查询高频执行，可将IN中的值存入临时表，通过JOIN查询：

  CREATE TEMPORARY TABLE temp_col2 (val INT PRIMARY KEY);
  INSERT INTO temp_col2 VALUES (251),(252),...,(14);
  
  SELECT t.col1 FROM table_name t
  JOIN temp_col2 tc ON t.col2 = tc.val
  WHERE t.col4 = 1 AND t.col3 > '1'
  GROUP BY t.col1;
  

  小表JOIN的方式比大IN列表更高效，数据库可利用临时表的主键索引快速匹配数据。

内容的提问来源于stack exchange，提问作者Sakshi abrol