这是一个创建于 227 天前的主题，其中的信息可能已经有所发展或是发生改变。
表里存的传感器回报信号数据，因为数据量不大，没用时序数据库因为感觉关系型跑的也挺好的。
表里只有三列，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 分钟。
这是啥原因？数据量两亿行左右。
42 条回复 • 2024-05-14 05:35:33 +08:00
 |
1
codehz 227 天前 via iPhone
建议先对比下单独查三次然后 union 的
|
 |
2
lmshl 227 天前
先把 Explain 贴上来再说
|
 |
3
latifrons 227 天前
最左匹配原则，在遇到范围查询的时候，就会停止匹配，所以你的 datetime 排序没用到索引。如果你 sid unique 数量少，甚至会做全表扫描
|
 |
4
fov6363 227 天前
联合索引是 sid_datetime 还是 date_time_sid ？
|
 |
5
FlyingBackscratc OP |
 |
6
LiaoMatt 227 天前
可能是 sid 作为索引基数太小了, 数据不够分散导致? 可以看下 optimize trace 分析
|
|
7
FlyingBackscratc OP |
 |
8
cannotagreemore 227 天前
MySQL 里面 IN 会被解释成多个 OR ，这个 sid 的区分度不够转成全表扫描了吧。一般可以查回来应用层做合并吧
|
 |
9
siweipancc 227 天前 via iPhone
数据量少变成全表跟内存排序当然慢了
|
|
10
LiaoMatt 227 天前
@FlyingBackscratc 应该就是 sid_date_time 联合索引 sid 基数太少导致, 你使用 >=是无法利用组合索引的, sid 的基数太少, 需要扫描的页过多, 而且你是取所有数据, 还需要回表, 数据库引擎觉得全表扫描的成本比通过 sid + 索引下推 + 回表的成本低, 所以选择全表扫描
|
 |
11
ydpro 227 天前
挺多原因的，如果 sid 列的基数低，不论是走 sid 索引还是联合索引效果都不太好。索引的选择性就差。同时你建立的联合索引 siddatetime 根据最左前缀规则，最左列的是 sid 同样会因为基数低的原因导致需要筛选的数据过多。
|
 |
12
abbychau 227 天前
>= 沒法走聯合索引
|
 |
13
lolizeppelin 227 天前
传感器数据还不上时序!换 pg!
|
 |
14
kiracyan 227 天前
一般数据量大的 SQL 是不太建议用 in 的，都是单独查出来再 union
|
|
15
abbychau 227 天前
@lolizeppelin 他沒上時序，但已經在用 PG 了
|
|
16
lolizeppelin 227 天前
那还不 explain 看
|
 |
17
mayli 227 天前 via Android
我觉得是排序导致的，把 order by 去了可能时间上会差不多。最直接办法还是看看 explain, 盲猜 in 过滤一堆数据，但是因为你最后是 datetime 排序，所以这个排序做了一个临时表去排。
理论上数据库应该是能利用索性查这个的，但是你用的数据库可能就傻傻的都查出来再排了。 |
 |
18
FYFX 227 天前  1
我怎么感觉你这个 union 和 in 的写法其实是不等价的，或者说在有 order by datetime 的情况下，数据库应该没法把你 in(0,1,2)+limit 的逻辑优化成 union 多个带有 limit 的查询
|
 |
19
8355 227 天前
跳行太多了 增加 sid order by
|
 |
20
opengps 227 天前
in 不走索引啊
|
 |
21
CEBBCAT 227 天前 1
我认为和那句 「 ORDER BY datetime ASC LIMIT 10 」有关系。原来是直接从索引表顺序读就可以，现在要从三个 SID 的簇里面全局按照 datetime 增序取前 1000 。
以上假定为 MySQL InnoDB 普通索引。说起来楼主数据库、引擎、EXPLAIN 可以贴一下的 |
 |
22
huangcjmail 227 天前
@FlyingBackscratc #7 这是啥数据库，第一次见 fetch first 这种语法。大家默认都当作 MySQL 去分析了.
|
 |
23
me1onsoda 227 天前 1
数据库是啥都没说，一通分析。。。
|
|
24
huangcjmail 227 天前 1
@opengps #20 武断了，很多情况都能走到的
|
|
25
opengps 227 天前
@huangcjmail 参数稍微多一点就不走了，直接当做不走谨慎使用为佳
|
 |
26
rambo92 227 天前
MySQL 的话，看看 sid 的区分度大不大，不大的话，建个 datetime + sid 的联合索引或者单独的 datetime 索引再试试看？
|
|
27
LiaoMatt 227 天前
@huangcjmail 底层只要是 B+树就可以这么分析, 思想是趋同的
|
 |
28
RedisMasterNode 227 天前
@opengps 没有这种说法，优化器会按照统计数据分析成本决定用什么索引怎么查。
|
 |
29
iosyyy 227 天前 1
@FlyingBackscratc #7 分析执行计划大概率是内存的问题
分析这两个图 第二种因为排序占用内存过大导致整体排序时间被拉长 而第一种看着像没有进行排序这一步 因为做了 limit 1 可能数据库做了优化 建议把你用的数据发出来具体问题具体分析 |
|
30
iosyyy 227 天前
@FlyingBackscratc #7 另外排序为啥用 datetime 直接用插入顺序 rid 不行吗..
|
|
32
huangcjmail 226 天前
@LiaoMatt #27 理论上是这样，但是很难说各个 db 的执行器策略、优化策略是什么。比如 MySQL 有时候会因为 cost 估算错误导致不走索引。换个没有这种功能的 db 可能就走索引了。
|
 |
33
shockingFly 226 天前 1
in 通常是走索引的，当 in 后面的数据在数据表中超过 30%（上面的例子的匹配数据大约 6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此 in 走不走索引和后面的数据有关系
|
 |
34
lxdlam 226 天前 1
看 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 |
 |
35
zlowly 226 天前
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 ，这个排序联合索引是没能起作用的。 |
|
36
LiaoMatt 226 天前
@shockingFly 确实
|
 |
37
yjhatfdu2 226 天前
看来 oracle 的优化器和性能都是有点挫了，pg 下毫无问题
|
 |
38
coderzhangsan 226 天前
如果数据库是 mysql ，sid 整形，联合索引，可能的情况 in 查询的覆盖基数太大了，考虑到是联合索引，可能扫描行数太多，即便走索引依然很慢，具体看下 explain 结果，必要时单独 sid 设置索引。
|
 |
39
ily433664 226 天前
"ORDER BY datetime ASC"
猜测是这个排序的原因 = 时，可以直接走索引确定数据 in 时，需要确定所有匹配到 in 的数据，再进行排序 |
 |
40
trzzzz 226 天前
看两个 explain 后的都是走 sid_datetime 的索引，最可能的原因是 in(20,21,22)中数据 datetime 很散。如果是 20 的 datetime 严格小于或等于 21 那就很快（但显然不太现实），数据库在内存中又自己把(20,21,22)按照 datetime 排了一遍，看 in 的那个 explain 中的 TempSpc 大概是用来排序了
|
 |
41
tslling 226 天前 via iPhone
我觉得 18 楼和 21 楼是正解。sid=0,1,2 的数据分别有多少条呀
|
 |
42
billccn 226 天前 2
看第二张图，优化器给你生成了一个临时的缓存在磁盘上的 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 反而被当作成本过高而弃用了。 |
Select Language