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

mysql 查询历史用户的疑问?

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

    表 t

    uid status
    1 1
    2 2
    1 2
    1 3
    2 3

    查询只存在 status=1 的用户,这两种写法有什么区别吗

    SELECT * FROM t where `status` = 1 and uid not IN (
    
    SELECT uid FROM  t as t1 WHERE `status` in (2,3) and t.uid = t1.uid
    )
    
    SELECT * FROM t where `status` = 1 and uid not IN (
    
    SELECT uid FROM  t as t1 WHERE `status` in (2,3) 
    )
    

    场景是查历史用户,即 status != 2,3 的,以上是简化的模型,两个字段都有索引

    上面的多了一个子查询和外表的条件,在表数据量大的情况下性能谁更好,leader 说第一种查询效率会更高,因为它只在满足外表的条件下进行了过滤。或者有没有其他性能更好的写法?

    8 条回复    2023-04-24 14:20:15 +08:00
    kkwa56188
        1
    kkwa56188  
       340 天前
    不是做 Mysql 的, 感觉是 1 比较快.
    其次 用 in (value1, value2) 通常是很慢的, 索引不一定管用.

    我会换成:
    SELECT * FROM t where t.status = 1
    and not exists ( SELECT uid FROM t as t1 WHERE status = 2 and t1.uid = t.uid )
    and not exists ( SELECT uid FROM t as t2 WHERE t2.status = 3 and t2.uid = t.uid )
    joetse
        2
    joetse  
       340 天前 via iPhone
    为什么不用 bitmap?
    akira
        3
    akira  
       340 天前   ❤️ 1
    status 区分度这么低,再来个 not in ,性能不好上来吧。试试这么考虑呢,虽然是有点取巧了

    select uid, min(status) `mins`, max(status) `maxs`
    from t
    group by uid
    having `mins` = 1 and `maxs` = 1
    512357301
        4
    512357301  
       340 天前 via Android
    用 group by 呢?
    select
    uid,
    sum(if(status = 1,1,0)) one_cnt,
    sum(if(status = 2 or status = 3,1,0)) other_cnt
    from t
    group by uid
    having one_cnt > 0 and other_cnt = 0

    没在机器上测试,反正我下意识的写法是这么写,楼主可以测试下。

    PS:对楼主和楼上各位的子查询写法感觉很别扭,我一直用不惯子查询嵌套,更习惯 left join ,不过 mysql 出身的貌似都这么写,存在即是合理,我不争辩。
    xuanbg
        5
    xuanbg  
       340 天前
    我会写成这样:
    with have23 as (SELECT uid FROM t as t1 WHERE `status` in (2,3))
    select * from t left join have23 h on h.uid = t.uid where h.uid is null;
    Grocker
        6
    Grocker  
    OP
       340 天前
    @akira 实际场景是有 4 种状态,ABCD ,存在 AB 且没有 CD 状态的用户
    Grocker
        7
    Grocker  
    OP
       340 天前
    @xuanbg 忘了说了是 mysql5.7
    akira
        8
    akira  
       340 天前
    @Grocker 那就分别对 4 个状态做统计,思路是一样的
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5395 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 07:06 · PVG 15:06 · LAX 00:06 · JFK 03:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.