1、问题复现
今天发现一个问题，同样的SQL,当in条件内只有一个的时候很慢很慢。当in条件有多个时，反而很快
慢的SQL如下
explain
select * from erp_goods_stock_inout_deal force index(ix_profileid_cid_billtype_billdate)
where profileid = 20568 and cid in (374040) and billdate between '2025-04-04' and '2025-04-04'
order by profileid ,cid,billtype desc,billdate desc;
快的SQL如下：
explain
select * from erp_goods_stock_inout_deal force index(ix_profileid_cid_billtype_billdate)
where profileid = 20568 and cid in (374040,374080) and billdate between '2025-04-04' and '2025-04-04'
order by profileid ,cid,billtype desc,billdate desc;
两个SQL一模一样
仅有的区别是
慢的SQL where 条件处是 cid in (557279)
快的SQL where 条件处是 cid in (557279,557287)
goods_stock_inout_deal 表 记录数为：3402931
慢的SQL执行需要13秒
而快的SQL只需要0.281秒 两者相差47倍
2、问题排查
a、排查第一步 查看执行计划
慢SQL的执行计划如下
快SQL执珩计划如下
通过执行计划比对，两条SQL 扫描行数一样，且慢SQL还消除了排序,从执行计划上看明显慢SQL还更优，那需要继续深入排查
b、排查第二步 查看实际耗时
慢SQL的实际耗时
快SQL的实际耗时
通过实际耗时对比
慢SQL在扫描 goods_stock_inout_deal 表时用了大量时间 13秒
快SQL在扫描 goods_stock_inout_dea 表时只用了260毫秒
快SQL多了一个Using index condition 这个即常说的ICP
那么可以推测是因为ICP的优化节约了大量时间
c、排查第三步 确认推测
我们来看此索引各字段的区分度
select count(*) from goods_stock_inout_deal force index(ix_profileid_cid_billtype_billdate)
where profileid = 20568 and cid in (374040);
慢SQL索引扫描后符合条件的有 589068
select count(*) from goods_stock_inout_deal force index(ix_profileid_cid_billtype_billdate)
where profileid = 20568 and cid in (374040,374080) and billdate between '2025-04-04' and '2025-04-04'
order by profileid ,cid,billtype desc,billdate desc;
慢SQL扫描索引后符合条件的只有262行。
符合推测，此语句就是因为ICP优化过滤了大量的行
3、性能差异原因
我们来复原两条SQL的执行过程
1、MySQL扫描goods_stock_inout_deal 表的索引 ix_profileid_cid_billtype_billdate共1171124 通过profileid与cid 定位查找共有 589068条记录符合条件。再拿到这589068条记录回表。在server层过滤billdate字段，最终返回262行
2、MySQL扫描goods_stock_inout_deal 表的索引 ix_profileid_cid_billtype_billdate共1171124 通过profileid与cid 定位查找共有 589068条，在定位到 589068记录时通过ICP(索引下推)丢弃到不符合billdate字段的行。丢弃后，共有262行记录，再回表拿到其它字段内容，返回客户端
两者最大的区别是慢SQL有589068条记录需要回表，而快SQL只需要262行记录需要回表
4、解决方案
很明显为什么MySQL在倒序排序上就不能使用ICP 这点MySQL的确应该优化
当前解决方案
1、改变索引 建立新索引idx(profileid，cid，illdate)
2、in条件 如果只有一个。就写两个重复的。通过实验可以确定 in（两个一样的值） 也不能走backward index scan
