数据库执行=搜索时速度很快，执行 IN 搜索时速度很慢是什么原因？

[FlyingBackscratc]

表里存的传感器回报信号数据，因为数据量不大，没用时序数据库因为感觉关系型跑的也挺好的。

表里只有三列，rid(自增主键)，传感器编号 sid ，上报时间 datetime 。在 sid 和 datetime 上有联合索引。

之前做基准测试的时候只做了单点的，就是

SELECT ... FROM records WHERE sid=0 ORDER BY datetime ASC LIMIT 10

类似这种感觉的，运行速度是很快的，平均延迟低于 100 毫秒，解释分析也确实是走索引了

但是最近改成多栏目搜索后

SELECT ... FROM records WHERE sid IN (0, 1, 2) ORDER BY datetime ASC LIMIT 10

解释分析也是走索引的，但是执行时间会超过 1 分钟。

这是啥原因？数据量两亿行左右。

[CEBBCAT]

我认为和那句 「 ORDER BY datetime ASC LIMIT 10 」有关系。原来是直接从索引表顺序读就可以，现在要从三个 SID 的簇里面全局按照 datetime 增序取前 1000 。

以上假定为 MySQL InnoDB 普通索引。说起来楼主数据库、引擎、EXPLAIN 可以贴一下的

[huangcjmail]

@FlyingBackscratc #7 这是啥数据库，第一次见 fetch first 这种语法。大家默认都当作 MySQL 去分析了.

[me1onsoda]

数据库是啥都没说，一通分析。。。

[huangcjmail]

@opengps #20 武断了，很多情况都能走到的

[opengps]

@huangcjmail 参数稍微多一点就不走了，直接当做不走谨慎使用为佳

[rambo92]

MySQL 的话，看看 sid 的区分度大不大，不大的话，建个 datetime + sid 的联合索引或者单独的 datetime 索引再试试看？

[LiaoMatt]

@huangcjmail 底层只要是 B+树就可以这么分析, 思想是趋同的

[RedisMasterNode]

@opengps 没有这种说法，优化器会按照统计数据分析成本决定用什么索引怎么查。

[iosyyy]

@FlyingBackscratc #7 分析执行计划大概率是内存的问题
分析这两个图
第二种因为排序占用内存过大导致整体排序时间被拉长
而第一种看着像没有进行排序这一步 因为做了 limit 1 可能数据库做了优化
建议把你用的数据发出来具体问题具体分析

[iosyyy]

@FlyingBackscratc #7 另外排序为啥用 datetime 直接用插入顺序 rid 不行吗..

[iosyyy]

@iosyyy #29 用的数据库 不是数据打错了 另外最好把表发出来单开一个帖子问

[huangcjmail]

@LiaoMatt #27 理论上是这样，但是很难说各个 db 的执行器策略、优化策略是什么。比如 MySQL 有时候会因为 cost 估算错误导致不走索引。换个没有这种功能的 db 可能就走索引了。

[shockingFly]

in 通常是走索引的，当 in 后面的数据在数据表中超过 30%（上面的例子的匹配数据大约 6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此 in 走不走索引和后面的数据有关系

[lxdlam]

看 op 应该是 Oracle

猜测 union 查询命中了联合索引，所以不需要对 `datetime` 排序，只需要决定起始位置就可以直接 fetch 数据；而 IN list 先对命中三个条件的数据进行 merge ，然后 sort 后再筛选，无论是合并、排序操作还是需要扫描的行数（ 8989K > 2123K+180K ）都远比前一个查询计划大。

一种可行的优化可以尝试 over partition 拆成三个子表排序再重新 where 一下，不确定优化效果，需要再 explain 一下看看，参考 https://use-the-index-luke.com/sql/partial-results/window-functions

[zlowly]

sid IN (0, 1, 2) ORDER BY datetime 很显然不能完全利用 sid 和 datetime 的联合索引。
和前面有人提到的，sid IN (0, 1, 2) ORDER BY datetime 和 sid=n ORDER BY datetime 后再 union,逻辑上是不等价的。
它最多会用 index skip scan 找出所有 sid IN (0, 1, 2)，然后直接内存排序 datetime ，这个排序联合索引是没能起作用的。

[LiaoMatt]

@shockingFly 确实

[yjhatfdu2]

看来 oracle 的优化器和性能都是有点挫了，pg 下毫无问题

[coderzhangsan]

如果数据库是 mysql ，sid 整形，联合索引，可能的情况 in 查询的覆盖基数太大了，考虑到是联合索引，可能扫描行数太多，即便走索引依然很慢，具体看下 explain 结果，必要时单独 sid 设置索引。

[ily433664]

"ORDER BY datetime ASC"
猜测是这个排序的原因
= 时，可以直接走索引确定数据
in 时，需要确定所有匹配到 in 的数据，再进行排序

[trzzzz]

看两个 explain 后的都是走 sid_datetime 的索引，最可能的原因是 in(20,21,22)中数据 datetime 很散。如果是 20 的 datetime 严格小于或等于 21 那就很快（但显然不太现实），数据库在内存中又自己把(20,21,22)按照 datetime 排了一遍，看 in 的那个 explain 中的 TempSpc 大概是用来排序了