MariaDB WHERE IN参数数量变化引发查询执行计划异常问题

问题根因分析 #

你遇到的两类异常本质都是MariaDB优化器成本估算错误导致的执行计划选择偏差，核心原因如下：

1. IN列表索引选择异常
   你的执行计划里rows字段统一返回1，但表实际有上千万条记录，说明InnoDB的表统计信息严重失真。优化器误判全表扫描仅需遍历1行数据，成本低于通过主键索引执行3次等值查找，因此在IN参数为3个时选择了全表扫描；当IN参数为2个时，成本估算刚好低于全表扫描阈值，因此选择了range索引扫描。
   部分版本的MariaDB还存在eq_range_index_dive_limit参数阈值触发的估算逻辑切换：当IN列表参数数量超过该阈值时，优化器会放弃精准的索引 dive 成本计算，改用失真的统计信息做估算，进一步放大了执行计划选错的概率。
2. select主键列反而更慢
   InnoDB的主键是聚簇索引，当你查询的列只有主键时，优化器会判定可以走覆盖索引，但是由于统计信息失真，优化器错误选择了index类型的全索引扫描（遍历整个主键索引的所有页，而非走range定位目标值）。你使用的是机械硬盘，遍历上千万条数据的主键索引需要大量随机IO，因此耗时达到数分钟，而select *时优化器反而不会考虑全索引扫描的选项，走了正确的range查询，因此速度更快。

优化方案 #

• 优先执行ANALYZE TABLE tls201_appln;更新表统计信息，如果统计信息依然不准，可以临时调高采样页数再执行分析：

  SET SESSION innodb_stats_sample_pages = 1000;
  ANALYZE TABLE tls201_appln;
  

• 调整优化器参数，扩大精准成本估算的IN列表长度阈值：

  -- 临时生效，如需永久生效请写入my.cnf
  SET GLOBAL eq_range_index_dive_limit = 1000;
  

• 业务查询中可以强制指定索引，避免优化器选错：

  -- 查询全字段强制走主键
  SELECT * FROM tls201_appln FORCE INDEX(PRIMARY) WHERE appln_id IN (1465778,1517002,1);
  -- 查询主键列强制走range类型的主键索引
  SELECT appln_id FROM tls201_appln FORCE INDEX FOR RANGE(PRIMARY) WHERE appln_id IN (1465778,1517002,1);
  

• 升级到MariaDB 10.5及以上稳定版本，新版本修复了大量IN列表成本估算的已知bug。

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