V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
mugglezzz
V2EX  ›  问与答

请教大家一个 SQL 的问题

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

    需求是这样的,就是有 A 、B 两个表,两个表都有 ID 、FullName 、ShortName 三个字段,以及各有一些其他的字段,现在想要这两个表连接查询,条件是 优先 ID 相等,其次 FullName 相等,最后 ShortName 相等的记录,目前是这么做的:

    select A.*,B.* from A left join B 
    on (A.ID=B.ID and B.ID is not null) 
    or (A.FullName=B.FullName and B.FullName is not null)
    or (A.ShortName=B.ShortName and B.ShortName is not null)
    

    其中 A 表数据比较少,B 表数据有 20 多 w,这样查出来需要 30 几秒。。。个人觉得问题应该是出在这个 or 这里,但是不知道有什么办法,请问各位有什么优化的方法吗?

    liprais
        1
    liprais  
       89 天前 via iPhone
    你要的不就是这三个条件的并集么
    gam2046
        2
    gam2046  
       89 天前
    首先确保三个字段都有索引,然后尝试 right join,即数据少的表 join 数据多的表,如果还是很慢,贴出来 explain 和机器配置
    BiChengfei
        3
    BiChengfei  
       89 天前
    信息太少,暂不知道怎么优化,
    -- 第一种 sql 似乎可以简写下,不知道会不会命中索引
    ```
    select A.*, B.* from A
    inner join B on A.ID = B.ID or A.FullName = B.FullName or A.ShortName = B.ShortName
    ```

    -- 第二种用 union 拼接,不知道这样去重可以吗,也不知道会不会命中索引
    ```
    select distinct * from (
    select
    A.*,
    B.*
    from A
    inner join B on A.ID = B.ID
    union
    select
    A.*,
    B.*
    from A
    inner join B on A.FullName = B.FullName
    union
    select
    A.*,
    B.*
    from A
    inner join B on A.ShortName = B.ShortName) temp
    ```
    BiChengfei
        4
    BiChengfei  
       89 天前
    左连接,左边表不能为 null,右边表可能为 null,你 sql 中把右边表为 null 的情况排除,那么就是内连接,所以改为 inner join
    因为 mysql 会对你的 sql 进行解析优化,再加上有 is not null,所以以前的语句能不能命中索引不知道,不知道有人能有理有据的科普下吗
    BiChengfei
        5
    BiChengfei  
       89 天前
    union 如果可以,这个语句还可以优化,考虑到 id 、fullname 、shortname,这三种匹配数据量的问题,在子语句中进行排除重复数据
    zhangyaxiao072
        6
    zhangyaxiao072  
       89 天前
    @BiChengfei 我也想到了第二种,不需要 distinct 了吧,union 会去重
    BiChengfei
        7
    BiChengfei  
       89 天前
    @zhangyaxiao072 不知道,很少用这个,楼主试一下就知道了,小问题
    mugglezzz
        8
    mugglezzz  
    OP
       89 天前
    @BiChengfei #5
    @zhangyaxiao072 #6
    谢谢,我试一下看看
    Egfly
        9
    Egfly  
       89 天前
    要不考虑一下分开查?然后代码里根据业务合并结果集。没必要给数据库太多压力
    coderJie
        10
    coderJie  
       89 天前
    一般来说都是用第二种 union 的方式来代替 or 的方式
    @Egfly 用代码来实现 union 效果的话会增加内存开销,一般交给数据库来操作就可以了,只要数据库不要太拉闸...
    coderJie
        11
    coderJie  
       89 天前
    @Egfly 数据量小的可以这么做,数据量大的话还是交给数据库吧
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2163 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 15:21 · PVG 23:21 · LAX 07:21 · JFK 10:21
    ♥ Do have faith in what you're doing.