大数据量场景下sql查询中in查询项过多时很慢的其他解决方式

最近遇到查询一张大数据量表时，需要对一个字段做in查询，in中的元素数量可能达到几千个，即使对这个字段加上索引，速度也慢到无法接受

示例表结构如下：

表中有几十万的数据，且example_id和data_id字段加了联合索引，只做一个简单的select查询：

select * from TEST_TABLE01 where example_id=:exampleId and data_id in(:dataIds)

其中in存在1000个元素，查询速度很慢，因为in的个数太多，导致索引失效，会全表扫描。

下面有两种优化方案：

优化方案1：

不使用in语法，将sql语句简化成下面这种，索引就生效了

select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId

但是这样一次只能查询一条data_id匹配的数据，这就意味着程序要和数据库交互1000次，但是我测试的速度要快于上面的in方式。

进一步优化，减少数据库交互方式，使用union all拼接sql：

select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId0 union all select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId1 union all select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId2 union all select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId3 ... ... union all select * from TEST_TABLE01 where example_id=:exampleId and data_id=:dataId999

程序中对dataId的参数进行组装，这样只和数据库交互一次，索引也不会失效，这种方式解决了in查询慢的问题。

优化方案2（推荐）：

参考很多大厂系统查询的做法，查询条件中常常携带上一个创建时间的默认参数，后台通过创建时间大于或小于传入值来筛选掉大部分不需要的数据，这个创建时间在后台表中建有索引，这样查询速度非常快。

这里我们也可以参考这种思路，将in中的元素list进行升序排序，取第一个（firstV）和最后一个(lastV)，优化写法：

select * from TEST_TABLE01 where example_id=:exampleId and data_id>=:firstV and data_id<=:lastV and data_id in(:dataIds)

使用最大值和最小值来限定data_id字段，这个时候索引也是生效的，data_id in(:dataIds)这个查询条件可以根据实际场景看是否可以去掉（比如data_id是主键，并且dataIds是连续且有序的集合）。

注意：如果firstV和lastV不是取自有序的结果集，可能会导致查询结果数据量仍然很多，无法达到预期速度。

因此来源结果集dataIds最好能有序，可以使用order by多个索引字段，使dataIds数据有序，这样能达到最大效果。

联合索引时查询也同理，每个索引字段都取得最大值和最小值，再构造sql：

select * from TEST_TABLE01 where example_id=:exampleId and col1>=:col1Min and col1<=:col1Max and col2>=:col2Min and col2<=:col2Max and (col1,col2) in((:col1_n,:col2_n),...)

对于delete也可以使用类似的方式优化：

delete from TEST_TABLE01 WHERE example_id=#{exampleId} and data_id>=#{startDataId} and data_id<=#{endDataId}