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

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

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

    表结构如下: 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
        1
    8355   112 天前   ♥ 1
    我认为应该是 1
        2
    ckaiii   112 天前   ♥ 1
    1
        3
    luoyou1014   112 天前   ♥ 2
    亲,你有数据直接到数据库里面执行 sql 验证下,比我们验证快多了。。。
        4
    kisshere   112 天前
    如果 2 很有用的情况,还发明 MAX 函数搞啥,所以,我站 1
        5
    linbiaye   112 天前
    mysql 肯定是 1 了
        6
    glacer   112 天前   ♥ 4
    在存在(uid, rid)索引的情况下,rid 会按索引排序,两条语句的性能应该是接近的。
    如果没有索引,个人认为是 1,原因是 Max 函数取最大值的复杂度为 O(n),而排序的复杂度通常为 O(nlogn)。因此 max 函数的复杂度会低一些。
        7
    ljpCN   112 天前 via Android
    一个是最大值,一个是排序,我想除非实现的人抽风了,不然明显最大值的复杂度只有 O(n)
        8
    enenaaa   112 天前
    一样的 ,TOP 1 也是 O(n)
        9
    zjp   112 天前 via Android
    PostgreSQL 的 explain 很强大,别浪费。
    另外按照语义写就是 1
        10
    dog82   112 天前
    差不多一样,但是 1 明显更易懂!
        11
    godlovesxcjtest   112 天前 via Android
    排序和求最大值复杂度会一样?一个是 O(N2),一个是 On,所以不是很明显吗
        12
    sanggao   112 天前 via iPhone   ♥ 1
    我的天那,看了楼上几个回复!!!!
        13
    no1xsyzy   112 天前
    SQL 的优化是数据库帮你完成的,不同的实现可能不同,不过我认为至少存在一个数据库软件这两者是等价的,甚至你写 2 都直接给你优化成 1 实现。
    那么,谁来试一下 PostgreSQL 的优化是否包含 ORDER DESC LIMIT 1 == Max ?
    不过如果没有该优化应该选 1 ;同时为了语义也应该选 1。我看不到选 2 的情况。
        14
    CEBBCAT   112 天前 via Android
    @sanggao 老哥快别吊胃口了,说说楼上哪里有问题吧
        15
    littlewing   112 天前
    我的天那,看了楼上几个回复!!!!
        16
    no1xsyzy   112 天前   ♥ 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 效率极高。

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

    所以
    1. 如果联合索引 rid,uid 语句 2 最优
    2. 建议设置主键索引,如果没有主键索引 innodb 内部也会帮你建立一个内部的,毕竟数据是放聚簇上的
        20
    c4f36e5766583218   112 天前
    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 的本意。
        21
    carlclone   112 天前
    感觉是一样的 , uid 确认的情况下 rid 是有序的 , 并不需要再排序
        22
    polymerdg   112 天前
    MYSQL 第一种
        23
    MetoYou   112 天前
    有索引能命中应该是一样的,没索引应该是 1 快
        24
    Aruforce   112 天前
    UID,RID 联合索引应该是有顺序的...
    大概类似于..这个结构
    ```
    BPTree{
    NODE uid; //uid 里面有一个 RID 的系列节点 array []形式?。。而这些节点是在插入时就排好序的...
    }
    ```
    这个两种实现速度上应该没什么区别才对...

    1 的更容易看出意思来...
        25
    javlib   112 天前 via Android
    用 explain 看看呢? pg 的 explain 应该可以给出优化后的执行 plan
        26
    maierhuang   112 天前
    一个字段上有索引的情况下两种执行计划是一致的,如果是无索引(或则楼主这种联合索引 rid 在后的情况下),max ( rid )走全表扫描,order by 这种需要 sort。
        27
    leishi1313   112 天前 via Android
    与其盲猜,不如用 EXPLAIN ANALYZE 在表上跑一下不就知道了嘛
        28
    applehater   112 天前
    怎么禁用缓存?
        29
    Iamnotfish   112 天前
    哈,这个楼主如果用的是 SQL 的话我刚测过。160W 条数据,用 MAX 的话是比 DESC LIMIT 1 要快的,大概快十几 MS,不过不确定是不是因为有缓存了才是这样的结果。
        30
    wd   112 天前 via iPhone
    where 部分过滤之后,后续的动作不就是那 10 条记录的操作了么,这有啥好比的。
        31
    gavinjou818   111 天前 via iPhone
    我感觉都差不多,假如要纠结...是不是 limit1 多了一个操作
        32
    ColoThor   111 天前
    max 不需要排序吧,性能应该更好?
        33
    zclHIT   111 天前
    mysql 是 1,但是你不是有数据库么。。直接在真实数据中执行一下就能看到了啊。。。
        34
    clarkyi   111 天前
    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 是索引,感觉两条数据所花费的时间差不多
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1048 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 38ms · UTC 22:59 · PVG 06:59 · LAX 15:59 · JFK 18:59
    ♥ Do have faith in what you're doing.