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