MYSQL因IN的范围太大导致索引失效问题
背景
最近发现有个用于统计的门店串码激活数量的SQL特别慢，将其摘出来大致如下
SELECT a.sku_id as skuId,a.store_id as storeId,
count(*) as saleQty
FROM all_imei_info a
where
a.activated_time >= 1675530000000
and a.activated_time <= 1675616399999
and a.store_id in ('1','2',....'23401')
group by a.sku_id,a.store_id这张表中的activated_time和store_id均有索引，但是先线上explain时却是走的全表扫描。
当初写这个SQL的开发人员，本意是想按天统计当下所有门店的一个销量情况，但是错就错在，他先在外层将所有区域查出来，再放到统计SQL的IN语句里面，这样就会导致索引失效。
在整个系统中有2w多个门店，而这个定时任务就是要每天把所有的门店都跑一下，所以说sotre_id in 就是把所有的storeId都放进来了。而mysql有个阈值，决定了阈值之下使用索引查询，而超过阈值，网上说当in的条件命中的数量超过30%时，索引失效，走全表扫描。
后面放弃使用in的方式，直接改为连表查询，即可正常使用索引，速度快的飞起。
SELECT a.sku_id as skuId,a.store_id as storeId,
count(*) as saleQty
FROM all_imei_info a
where
a.activated_time >= 1675530000000
and a.activated_time <= 1675616399999
and a.store_id in (select store_id from store_table where is_del = 0)
group by a.sku_id,a.store_idMySQL中IN数据范围不同导致索引使用不同
EXPLAIN：explain 命令获取 select 语句的执行计划，通过 explain我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息
其中explian结果中的type字段很明显提现是否用到索引。
常见的扫描方式：
system：系统表，少量数据，往往不需要进行磁盘 IO
const：常量连接（通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。）
eq_ref：主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描
ref：非主键非唯一索引等值扫描（查找条件列使用了索引而且不为主键和unique。）
range：范围扫描（有范围的索引扫描，相对于index的全表扫描，他有范围限制，因此要优于index）
index：索引树扫描（另一种形式的全表扫描，只不过他的扫描方式是按照索引的顺序）
ALL：全表扫描 (full table scan)
其中：MySQL索引扫描方式由快到慢依次为：
system > const > eq_ref > ref > range > index > ALL下面展示查询sql及结果
当IN只有一个主键时：
结果： type:const，走的主键索引。
当IN多个主键时：
结果：type:range，此时仍然走了索引，但是效率降低了。
当IN范围继续扩大时：
结果：type：all，没有走索引了，而是全表扫描。
结论：IN肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描。
原因是：mysql有个阈值，决定了阈值之下使用索引查询，而超过阈值则退化，优化器选择索引下潜。
MySQL优化器决定使用某个索引执行查询的仅仅是因为：使用该索引时的成本足够低。
相关文章：