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

下面两条 SQL 语句哪个效率更高?

  •  
  •   imherer · 2019-06-24 11:08:49 +08:00 · 8880 次点击
    这是一个创建于 1981 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构如下: uid,rid,name,home,event,data,time

    其中 uid 和 rid 联合唯一索引

    假如现在表里有接近 100W 行数据,其中每个 uid 大概有 10 行记录

    现要查询出单个 uid 下的最大 rid,下面哪个 SQL 语句效率更高呢?

    1.SELECT MAX(rid) AS rid FROM table WHERE uid = $1

    2.SELECT rid FROM table WHERE uid = $1 ORDER BY rid DESC LIMIT 1

    数据库为:PostgreSQL

    另外像这样的表结构没有主键性能上会有什么问题吗?(因为主键是用不到的)

    34 条回复    2019-06-25 10:44:37 +08:00
    8355
        1
    8355  
       2019-06-24 11:16:02 +08:00   ❤️ 1
    我认为应该是 1
    ckaiii
        2
    ckaiii  
       2019-06-24 11:23:21 +08:00   ❤️ 1
    1
    luoyou1014
        3
    luoyou1014  
       2019-06-24 11:25:42 +08:00   ❤️ 2
    亲,你有数据直接到数据库里面执行 sql 验证下,比我们验证快多了。。。
    kisshere
        4
    kisshere  
       2019-06-24 11:33:13 +08:00
    如果 2 很有用的情况,还发明 MAX 函数搞啥,所以,我站 1
    linbiaye
        5
    linbiaye  
       2019-06-24 11:47:55 +08:00
    mysql 肯定是 1 了
    glacer
        6
    glacer  
       2019-06-24 11:50:53 +08:00   ❤️ 4
    在存在(uid, rid)索引的情况下,rid 会按索引排序,两条语句的性能应该是接近的。
    如果没有索引,个人认为是 1,原因是 Max 函数取最大值的复杂度为 O(n),而排序的复杂度通常为 O(nlogn)。因此 max 函数的复杂度会低一些。
    ljpCN
        7
    ljpCN  
       2019-06-24 12:24:42 +08:00 via Android
    一个是最大值,一个是排序,我想除非实现的人抽风了,不然明显最大值的复杂度只有 O(n)
    enenaaa
        8
    enenaaa  
       2019-06-24 12:25:35 +08:00
    一样的 ,TOP 1 也是 O(n)
    zjp
        9
    zjp  
       2019-06-24 12:25:56 +08:00 via Android
    PostgreSQL 的 explain 很强大,别浪费。
    另外按照语义写就是 1
    dog82
        10
    dog82  
       2019-06-24 12:48:27 +08:00
    差不多一样,但是 1 明显更易懂!
    godlovesxcjtest
        11
    godlovesxcjtest  
       2019-06-24 12:58:33 +08:00 via Android
    排序和求最大值复杂度会一样?一个是 O(N2),一个是 On,所以不是很明显吗
    sanggao
        12
    sanggao  
       2019-06-24 13:08:52 +08:00 via iPhone   ❤️ 1
    我的天那,看了楼上几个回复!!!!
    no1xsyzy
        13
    no1xsyzy  
       2019-06-24 13:19:01 +08:00
    SQL 的优化是数据库帮你完成的,不同的实现可能不同,不过我认为至少存在一个数据库软件这两者是等价的,甚至你写 2 都直接给你优化成 1 实现。
    那么,谁来试一下 PostgreSQL 的优化是否包含 ORDER DESC LIMIT 1 == Max ?
    不过如果没有该优化应该选 1 ;同时为了语义也应该选 1。我看不到选 2 的情况。
    CEBBCAT
        14
    CEBBCAT  
       2019-06-24 13:32:13 +08:00 via Android
    @sanggao 老哥快别吊胃口了,说说楼上哪里有问题吧
    littlewing
        15
    littlewing  
       2019-06-24 13:35:01 +08:00
    我的天那,看了楼上几个回复!!!!
    no1xsyzy
        16
    no1xsyzy  
       2019-06-24 13:39:05 +08:00   ❤️ 1
    就当我口胡吧,似乎两者功能不完全等价。
    https://stackoverflow.com/questions/34246403/performance-of-max-vs-order-by-desc-limit-1
    ORDER BY DESC 会导致 NULL 在最前,所以有 NULL 的时候 2. 选出来的那个一定是 NULL。

    而该提问应该是没有索引导致的问题,而根据是否能选出至少一行(有 WHERE 所以不一定能找出来):
    找得出来的情况 ORDER BY DESC + LIMIT 1 效率比 MAX 高;
    找不出来的情况 MAX 效率极高。

    如果有索引我认为两者基本一致,因为不会需要进行一次排序。
    no1xsyzy
        17
    no1xsyzy  
       2019-06-24 13:45:18 +08:00
    @no1xsyzy 好像建立了索引还是,刚没细读,搜不出来的情况 ORDER BY DESC + LIMIT 1 时间很长,用了 JOIN 就 1200ms 以上,写成子查询才保持始终最短时间。
    综上,A/B 测试吧。
    imherer
        18
    imherer  
    OP
       2019-06-24 13:49:07 +08:00
    @glacer 确实是,我往表里插了 100W 数据,有(uid, rid)索引的情况下确实是一样的,差不多都是 38ms 左右
    WriteCloser
        19
    WriteCloser  
       2019-06-24 13:54:57 +08:00
    看联合索引顺序
    如果是 rid 是左边那么用得着索引,如果在 MySQL innodb 里面索引默认就有顺序,所以不需要重复的排序这个过程
    我不清楚 max 里面的排序过程,但是不管怎样,语句二都不用排序了这么都比 1 性能更优吧

    所以
    1. 如果联合索引 rid,uid 语句 2 最优
    2. 建议设置主键索引,如果没有主键索引 innodb 内部也会帮你建立一个内部的,毕竟数据是放聚簇上的
    c4f36e5766583218
        20
    c4f36e5766583218  
       2019-06-24 14:11:03 +08:00
    1. 取某一列的最大值,最小值
    min/max vs order by: https://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit
    2. 取某一列值,移除重复项
    DISTINCT vs GROUP BY: https://blog.csdn.net/ljl890705/article/details/70602442
    我觉得吧,看你需求,如果只是简单的取最大 /小值或者去重,还是用原生函数较好。毕竟不是 order-by,group-by 的本意。
    carlclone
        21
    carlclone  
       2019-06-24 14:32:02 +08:00
    感觉是一样的 , uid 确认的情况下 rid 是有序的 , 并不需要再排序
    polymerdg
        22
    polymerdg  
       2019-06-24 16:29:32 +08:00
    MYSQL 第一种
    MetoYou
        23
    MetoYou  
       2019-06-24 16:35:46 +08:00
    有索引能命中应该是一样的,没索引应该是 1 快
    Aruforce
        24
    Aruforce  
       2019-06-24 16:46:55 +08:00
    UID,RID 联合索引应该是有顺序的...
    大概类似于..这个结构
    ```
    BPTree{
    NODE uid; //uid 里面有一个 RID 的系列节点 array []形式?。。而这些节点是在插入时就排好序的...
    }
    ```
    这个两种实现速度上应该没什么区别才对...

    1 的更容易看出意思来...
    javlib
        25
    javlib  
       2019-06-24 19:36:46 +08:00 via Android
    用 explain 看看呢? pg 的 explain 应该可以给出优化后的执行 plan
    maierhuang
        26
    maierhuang  
       2019-06-24 23:30:08 +08:00
    一个字段上有索引的情况下两种执行计划是一致的,如果是无索引(或则楼主这种联合索引 rid 在后的情况下),max ( rid )走全表扫描,order by 这种需要 sort。
    leishi1313
        27
    leishi1313  
       2019-06-25 00:29:19 +08:00 via Android
    与其盲猜,不如用 EXPLAIN ANALYZE 在表上跑一下不就知道了嘛
    applehater
        28
    applehater  
       2019-06-25 00:55:39 +08:00
    怎么禁用缓存?
    Iamnotfish
        29
    Iamnotfish  
       2019-06-25 04:30:46 +08:00
    哈,这个楼主如果用的是 SQL 的话我刚测过。160W 条数据,用 MAX 的话是比 DESC LIMIT 1 要快的,大概快十几 MS,不过不确定是不是因为有缓存了才是这样的结果。
    wd
        30
    wd  
       2019-06-25 06:33:27 +08:00 via iPhone
    where 部分过滤之后,后续的动作不就是那 10 条记录的操作了么,这有啥好比的。
    gavinjou818
        31
    gavinjou818  
       2019-06-25 10:07:12 +08:00 via iPhone
    我感觉都差不多,假如要纠结...是不是 limit1 多了一个操作
    ColoThor
        32
    ColoThor  
       2019-06-25 10:29:10 +08:00
    max 不需要排序吧,性能应该更好?
    zclHIT
        33
    zclHIT  
       2019-06-25 10:31:51 +08:00
    mysql 是 1,但是你不是有数据库么。。直接在真实数据中执行一下就能看到了啊。。。
    clarkyi
        34
    clarkyi  
       2019-06-25 10:44:37 +08:00
    SQL1 执行 explain 得到的结果
    QUERY PLAN
    Aggregate (cost=2582.44..2582.45 rows=1 width=4)
    -> Index Scan using idx_table_uid on table_name (cost=0.57..2580.40 rows=815 width=4)
    Index Cond: ((uid)::text = '$id'::text)


    SQL2 执行得到的结果
    Limit (cost=2584.48..2584.48 rows=1 width=4)
    -> Sort (cost=2584.48..2586.51 rows=815 width=4)
    Sort Key: id
    -> Index Scan using idx_table_uid on table_name (cost=0.57..2580.40 rows=815 width=4)
    Index Cond: ((uid)::text = '$id'::text)

    数据总量在 1.4 亿左右,uid 是索引,感觉两条数据所花费的时间差不多
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3368 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 12:05 · PVG 20:05 · LAX 04:05 · JFK 07:05
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.