排查了一则MySQL中in查询传入270个入参则 索引失效 的问题，本文记录分析与解决的过程。

背景：

上线了一个统计类的逻辑，发现有慢SQL。

相关表结构：

1
2
3
4
5
6

CREATE TABLE `t` (
  `id`  bigint(20)   NOT NULL AUTO_INCREMENT COMMENT 'id',
  `repeat_hash` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_repeat_hash` (`repeat_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

对应查询语句：

1
2
3

select *
from t
where repeat_hash in (一堆入参)

线上入参传入了100-300个，查询耗时达到了 5000-50000ms，大概就是5s到50s级别。单个入参为固定的hash值，示例：“19445304e93d3a6134544458004d9170”。

分析：

explain一波：

找到问题症结了！

in元素过多 or in元素在索引树上范围较大，导致扫了全表。

将入参数量改到一百来个，减少入参数量：

此时type变为了range。

根据我的经验值，一般 “19445304e93d3a6134544458004d9170” 这种入参批量in查询时，传入三五百个甚至一千个都不是问题。

所以我们需要挖一下背后的原理。

原理解析

mysql官网关于如何用索引的文档：

https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

翻译：

• 小表上的索引不重要；
• 大表上，但是查询需要遍历到大部分的行，这种情况顺序扫描全表的开销反而比使用索引的开销小（顺序读减少了磁盘寻道）；

因此，当前慢查询的原因，我个人判断是传入的repeathash范围较大，mysql 优化器认为此时不如直接扫全表来的快。

这里存疑：由于我们的编码值是一个固定位数的hash值，如果300个左右的编码比较分散，理论上来说100个编码同样也是分散的。但是根据后面实测，100个编码确实能走到索引上。

解决思路

方案一

每批in的入参数量改成100 。

优点是改动较小，缺点是如果100个元素的范围较大，依然会比较慢。

方案二

一批数据处理过程，每次处理一条数据时，根据对应的repeatHash查一次数据库。

优点是每次查询都能保证是一个type==ref的单条命中索引的查询，性能预期较稳定 ，缺点则是jvm与mysql的io rt需要多次。

经过后面的实测，选用方案一。

实测

实际跑下来的数据表现是：

• 每次批量100左右的in，可以走到索引，耗时几十ms级别；
• 单条for循环耗时稳定，300条左右耗时10s左右，100条左右耗时4s；

对比下来选用了方案一。 对应性能表现：

开启数据任务后，暂未出现慢查询。

Ref

• https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html