数据库执行=搜索时速度很快，执行 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 分钟。

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

[codehz]

建议先对比下单独查三次然后 union 的

[lmshl]

先把 Explain 贴上来再说

[latifrons]

最左匹配原则，在遇到范围查询的时候，就会停止匹配，所以你的 datetime 排序没用到索引。如果你 sid unique 数量少，甚至会做全表扫描

[fov6363]

联合索引是 sid_datetime 还是 date_time_sid ？

[FlyingBackscratc]

@fov6363 联合索引是 siddatetime ，基本顺序这种不会搞错

@latifrons 感觉有点像这个原因，不过解释里 IN 是被解释成多个 OR 的，感觉也不太对

@lmshl 下帖

[LiaoMatt]

可能是 sid 作为索引基数太小了, 数据不够分散导致? 可以看下 optimize trace 分析

[FlyingBackscratc]

https://imgur.com/4CHvixU.png

union 执行时间 0.3 秒

https://imgur.com/8pc6iaF.png

IN 的执行时间是 24 秒

[cannotagreemore]

MySQL 里面 IN 会被解释成多个 OR ，这个 sid 的区分度不够转成全表扫描了吧。一般可以查回来应用层做合并吧

[siweipancc]

数据量少变成全表跟内存排序当然慢了

[LiaoMatt]

@FlyingBackscratc 应该就是 sid_date_time 联合索引 sid 基数太少导致, 你使用 >=是无法利用组合索引的, sid 的基数太少, 需要扫描的页过多, 而且你是取所有数据, 还需要回表, 数据库引擎觉得全表扫描的成本比通过 sid + 索引下推 + 回表的成本低, 所以选择全表扫描

[ydpro]

挺多原因的，如果 sid 列的基数低，不论是走 sid 索引还是联合索引效果都不太好。索引的选择性就差。同时你建立的联合索引 siddatetime 根据最左前缀规则，最左列的是 sid 同样会因为基数低的原因导致需要筛选的数据过多。

[abbychau]

>= 沒法走聯合索引

[lolizeppelin]

传感器数据还不上时序!换 pg!

[kiracyan]

一般数据量大的 SQL 是不太建议用 in 的，都是单独查出来再 union

[abbychau]

@lolizeppelin 他沒上時序，但已經在用 PG 了

[lolizeppelin]

那还不 explain 看

[mayli]

我觉得是排序导致的，把 order by 去了可能时间上会差不多。最直接办法还是看看 explain, 盲猜 in 过滤一堆数据，但是因为你最后是 datetime 排序，所以这个排序做了一个临时表去排。
理论上数据库应该是能利用索性查这个的，但是你用的数据库可能就傻傻的都查出来再排了。

[FYFX]

我怎么感觉你这个 union 和 in 的写法其实是不等价的，或者说在有 order by datetime 的情况下，数据库应该没法把你 in(0,1,2)+limit 的逻辑优化成 union 多个带有 limit 的查询

[8355]

跳行太多了 增加 sid order by

[opengps]

in 不走索引啊

[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 大概是用来排序了

[tslling]

我觉得 18 楼和 21 楼是正解。sid=0,1,2 的数据分别有多少条呀

[billccn]

看第二张图，优化器给你生成了一个临时的缓存在磁盘上的 View ，并行读了 8 百万行原表，把所有符合条件的行都复制过去了，最后从 View 里面选出了前 10 个返回。而第一张图之是分两次查原表，各读满 10 个以后截断，然后 Union 。

原因 1 是你这两个查询的语义本来就不一样，你的第一张图可以输出 20 个结果，且每个 sid 里面的时间排序相互独立。第二张是一共输出 10 个结果，而且是从三个 SID 结果的合集中再按照时间排序。

原因 2 是 Oracle 的 fetch first 功能是后来加的 hack ，没有很好的融合进构架里面去，它不影响默认的全局优化策略(ALL_ROWS ，这导致优化器会先忽略你只要前面几行的要求，它会生成一个找到所有符合要求的结果最快的 plan ，然后在最外层加一个附加条件来输出前面几个结果。你需要在 select 后面加/*+ FIRST_ROWS(10) */这个 hint （ 10 是需要的行数），让它进入前几行行输出速度优先模式。

原因 3 是根据我的经验，Oracle 犯这个傻也跟这个数据库的配置有关，优化器可能觉得 IO 成本很低，所以用每一个 sid 单独查（就是你第一张图想表达的模式）的那个 plan 反而被当作成本过高而弃用了。