数据库执行=搜索时速度很快，执行 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 不走索引啊