V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
FlyingBackscratc
V2EX  ›  程序员

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

  •  
  •   FlyingBackscratc · 194 天前 · 3997 次点击
    这是一个创建于 194 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    表里只有三列,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
    codehz
        1
    codehz  
       194 天前 via iPhone
    建议先对比下单独查三次然后 union 的
    lmshl
        2
    lmshl  
       194 天前
    先把 Explain 贴上来再说
    latifrons
        3
    latifrons  
       194 天前
    最左匹配原则,在遇到范围查询的时候,就会停止匹配,所以你的 datetime 排序没用到索引。如果你 sid unique 数量少,甚至会做全表扫描
    fov6363
        4
    fov6363  
       194 天前
    联合索引是 sid_datetime 还是 date_time_sid ?
    FlyingBackscratc
        5
    FlyingBackscratc  
    OP
       194 天前
    @fov6363 联合索引是 siddatetime ,基本顺序这种不会搞错

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

    @lmshl 下帖
    LiaoMatt
        6
    LiaoMatt  
       194 天前
    可能是 sid 作为索引基数太小了, 数据不够分散导致? 可以看下 optimize trace 分析
    FlyingBackscratc
        7
    FlyingBackscratc  
    OP
       194 天前
    https://imgur.com/4CHvixU.png

    union 执行时间 0.3 秒

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

    IN 的执行时间是 24 秒
    cannotagreemore
        8
    cannotagreemore  
       194 天前
    MySQL 里面 IN 会被解释成多个 OR ,这个 sid 的区分度不够转成全表扫描了吧。一般可以查回来应用层做合并吧
    siweipancc
        9
    siweipancc  
       194 天前 via iPhone
    数据量少变成全表跟内存排序当然慢了
    LiaoMatt
        10
    LiaoMatt  
       194 天前
    @FlyingBackscratc 应该就是 sid_date_time 联合索引 sid 基数太少导致, 你使用 >=是无法利用组合索引的, sid 的基数太少, 需要扫描的页过多, 而且你是取所有数据, 还需要回表, 数据库引擎觉得全表扫描的成本比通过 sid + 索引下推 + 回表的成本低, 所以选择全表扫描
    ydpro
        11
    ydpro  
       194 天前
    挺多原因的,如果 sid 列的基数低,不论是走 sid 索引还是联合索引效果都不太好。索引的选择性就差。同时你建立的联合索引 siddatetime 根据最左前缀规则,最左列的是 sid 同样会因为基数低的原因导致需要筛选的数据过多。
    abbychau
        12
    abbychau  
       194 天前
    >= 沒法走聯合索引
    lolizeppelin
        13
    lolizeppelin  
       194 天前
    传感器数据还不上时序!换 pg!
    kiracyan
        14
    kiracyan  
       194 天前
    一般数据量大的 SQL 是不太建议用 in 的,都是单独查出来再 union
    abbychau
        15
    abbychau  
       194 天前
    @lolizeppelin 他沒上時序,但已經在用 PG 了
    lolizeppelin
        16
    lolizeppelin  
       194 天前
    那还不 explain 看
    mayli
        17
    mayli  
       194 天前 via Android
    我觉得是排序导致的,把 order by 去了可能时间上会差不多。最直接办法还是看看 explain, 盲猜 in 过滤一堆数据,但是因为你最后是 datetime 排序,所以这个排序做了一个临时表去排。
    理论上数据库应该是能利用索性查这个的,但是你用的数据库可能就傻傻的都查出来再排了。
    FYFX
        18
    FYFX  
       194 天前   ❤️ 1
    我怎么感觉你这个 union 和 in 的写法其实是不等价的,或者说在有 order by datetime 的情况下,数据库应该没法把你 in(0,1,2)+limit 的逻辑优化成 union 多个带有 limit 的查询
    8355
        19
    8355  
       194 天前
    跳行太多了 增加 sid order by
    opengps
        20
    opengps  
       194 天前
    in 不走索引啊
    CEBBCAT
        21
    CEBBCAT  
       194 天前   ❤️ 1
    我认为和那句 「 ORDER BY datetime ASC LIMIT 10 」有关系。原来是直接从索引表顺序读就可以,现在要从三个 SID 的簇里面全局按照 datetime 增序取前 1000 。

    以上假定为 MySQL InnoDB 普通索引。说起来楼主数据库、引擎、EXPLAIN 可以贴一下的
    huangcjmail
        22
    huangcjmail  
       194 天前
    @FlyingBackscratc #7 这是啥数据库,第一次见 fetch first 这种语法。大家默认都当作 MySQL 去分析了.
    me1onsoda
        23
    me1onsoda  
       194 天前   ❤️ 1
    数据库是啥都没说,一通分析。。。
    huangcjmail
        24
    huangcjmail  
       194 天前   ❤️ 1
    @opengps #20 武断了,很多情况都能走到的
    opengps
        25
    opengps  
       194 天前
    @huangcjmail 参数稍微多一点就不走了,直接当做不走谨慎使用为佳
    rambo92
        26
    rambo92  
       194 天前
    MySQL 的话,看看 sid 的区分度大不大,不大的话,建个 datetime + sid 的联合索引或者单独的 datetime 索引再试试看?
    LiaoMatt
        27
    LiaoMatt  
       194 天前
    @huangcjmail 底层只要是 B+树就可以这么分析, 思想是趋同的
    RedisMasterNode
        28
    RedisMasterNode  
       194 天前
    @opengps 没有这种说法,优化器会按照统计数据分析成本决定用什么索引怎么查。
    iosyyy
        29
    iosyyy  
       194 天前   ❤️ 1
    @FlyingBackscratc #7 分析执行计划大概率是内存的问题
    分析这两个图
    第二种因为排序占用内存过大导致整体排序时间被拉长
    而第一种看着像没有进行排序这一步 因为做了 limit 1 可能数据库做了优化
    建议把你用的数据发出来具体问题具体分析
    iosyyy
        30
    iosyyy  
       194 天前
    @FlyingBackscratc #7 另外排序为啥用 datetime 直接用插入顺序 rid 不行吗..
    iosyyy
        31
    iosyyy  
       194 天前
    @iosyyy #29 用的数据库 不是数据打错了 另外最好把表发出来单开一个帖子问
    huangcjmail
        32
    huangcjmail  
       194 天前
    @LiaoMatt #27 理论上是这样,但是很难说各个 db 的执行器策略、优化策略是什么。比如 MySQL 有时候会因为 cost 估算错误导致不走索引。换个没有这种功能的 db 可能就走索引了。
    shockingFly
        33
    shockingFly  
       194 天前   ❤️ 1
    in 通常是走索引的,当 in 后面的数据在数据表中超过 30%(上面的例子的匹配数据大约 6000/16000 = 37.5%)的匹配时,会走全表扫描,即不走索引,因此 in 走不走索引和后面的数据有关系
    lxdlam
        34
    lxdlam  
       194 天前   ❤️ 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
    zlowly
        35
    zlowly  
       194 天前
    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
        36
    LiaoMatt  
       194 天前
    @shockingFly 确实
    yjhatfdu2
        37
    yjhatfdu2  
       194 天前
    看来 oracle 的优化器和性能都是有点挫了,pg 下毫无问题
    coderzhangsan
        38
    coderzhangsan  
       194 天前
    如果数据库是 mysql ,sid 整形,联合索引,可能的情况 in 查询的覆盖基数太大了,考虑到是联合索引,可能扫描行数太多,即便走索引依然很慢,具体看下 explain 结果,必要时单独 sid 设置索引。
    ily433664
        39
    ily433664  
       194 天前
    "ORDER BY datetime ASC"
    猜测是这个排序的原因
    = 时,可以直接走索引确定数据
    in 时,需要确定所有匹配到 in 的数据,再进行排序
    trzzzz
        40
    trzzzz  
       194 天前
    看两个 explain 后的都是走 sid_datetime 的索引,最可能的原因是 in(20,21,22)中数据 datetime 很散。如果是 20 的 datetime 严格小于或等于 21 那就很快(但显然不太现实),数据库在内存中又自己把(20,21,22)按照 datetime 排了一遍,看 in 的那个 explain 中的 TempSpc 大概是用来排序了
    tslling
        41
    tslling  
       194 天前 via iPhone
    我觉得 18 楼和 21 楼是正解。sid=0,1,2 的数据分别有多少条呀
    billccn
        42
    billccn  
       194 天前   ❤️ 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 反而被当作成本过高而弃用了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1008 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 23:10 · PVG 07:10 · LAX 15:10 · JFK 18:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.