大数据量场景下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拼接sqlselect * 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数据有序这样能达到最大效果。联合索引时查询也同理每个索引字段都取得最大值和最小值再构造sqlselect * 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}