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

请教大佬,我这 SQL 写的有没有问题?

  •  
  •   cl903254852 · 2019-10-15 15:06:20 +08:00 · 3416 次点击
    这是一个创建于 1626 天前的主题,其中的信息可能已经有所发展或是发生改变。

    article(文章表):

    article_content(文章内容表):

    article_tag(标签表):

    article_type(分类表):


    其中 article(文章表)、article_content(文章内容表) 是一对一关系

    article(文章表)、article_tag(标签表) 是多对多关系,关系表 tag_article 如下:

    article(文章表)、article_type(分类表) 也是多对多关系,关系表 type_article 如下:


    需求

    查询所有文章,包含以下数据:

    • id:文章 id
    • title:文章标题
    • tag_id:当前文章下的所有标签 id
    • tag_name:当前文章下的所有标签名
    • type_id:当前文章的分类 id
    • type_name:当前文章的分类名称

    然后我自己写的 SQL 是这样的:

    SELECT 
    a.id, 
    a.title,
    GROUP_CONCAT( distinct t.tag_id),
    GROUP_CONCAT( distinct type.type_id),
    GROUP_CONCAT( distinct a_tag.tag_name),
    GROUP_CONCAT( distinct a_type.type_name)
    FROM article a  
    JOIN tag_article t ON a.id=t.article_id
    JOIN type_article type ON a.id = type.article_id 
    JOIN article_tag a_tag ON a_tag.id=t.tag_id
    JOIN article_type a_type ON a_type.id=type.type_id
    GROUP BY a.id;
    

    结果:

    结果也能出来,但我心里总有点不踏实。

    各位大佬,我这 SQL 写的有没有问题?或者不严谨的地方?

    ps:鄙人也是刚入门 Mysql,轻喷☹️

    32 条回复    2019-10-16 15:18:32 +08:00
    wangyzj
        1
    wangyzj  
       2019-10-15 15:12:35 +08:00
    表结构不大好
    leewea
        2
    leewea  
       2019-10-15 15:15:22 +08:00
    头像不错
    xwbz2018
        3
    xwbz2018  
       2019-10-15 15:43:43 +08:00
    标签和分类可以冗余到文章表里
    newtype0092
        4
    newtype0092  
       2019-10-15 15:47:53 +08:00
    tag name 和 type name 这种最好放在 redis 里,查出来 id 之后去 redis 里拿出来,现在这样 join 出来很多多余数据。
    你这种不用 left join 没有问题么?
    cl903254852
        5
    cl903254852  
    OP
       2019-10-15 17:04:26 +08:00
    @xwbz2018 我特意问过同事,他们也喜欢这样冗余,用一个字段来存关系。但我总觉得这样不好,我还是觉得把关系抽离成中间表才是正规做法。
    cl903254852
        6
    cl903254852  
    OP
       2019-10-15 17:06:42 +08:00
    @newtype0092 对的!大佬!!! 这样确实会 join 出来很多多余数据(left join 也是),是我使用姿势错了?。 请问怎么解决呢
    cl903254852
        7
    cl903254852  
    OP
       2019-10-15 17:07:02 +08:00
    @leewea 😹
    cl903254852
        8
    cl903254852  
    OP
       2019-10-15 17:07:40 +08:00
    @wangyzj 虚心请教,应该改成什么样才算好
    gIrl1990
        9
    gIrl1990  
       2019-10-15 17:17:26 +08:00
    article(文章表)、article_tag(标签表) 是多对多关系
    article(文章表)、article_type(分类表) 也是多对多关系

    有木有感觉其中一个是多余的? 都是多对多 那“标签”和“分类”有啥子区别?
    xwbz2018
        10
    xwbz2018  
       2019-10-15 17:28:30 +08:00
    @cl903254852 #5 标签和类型会不会修改?标签和类型多不多?我 join 用的不好,你看看没有分类、标签的数据能不能查出来
    linxiaojialin
        11
    linxiaojialin  
       2019-10-15 17:28:37 +08:00
    为啥执着于一条 SQL 查出所有数据呢?可以分成 4 次查出来的。

    另外,如果你是 PHP && Laravel 的话,设置好 Model Relation,可以用 with 解决 N+1 的问题,例如:
    ```
    $articles = Article::query()->with(['content', 'tags', 'types'])->paginate(10);
    ```
    cl903254852
        12
    cl903254852  
    OP
       2019-10-15 17:31:40 +08:00
    @linxiaojialin 我用的 Nodejs。 多次查会影响性能 我尽量一次查出来
    cl903254852
        13
    cl903254852  
    OP
       2019-10-15 17:32:36 +08:00
    @gIrl1990 分类范围比标签大。 请不要关心业务问题
    ebony0319
        14
    ebony0319  
       2019-10-15 17:44:10 +08:00
    我用 Postgresql 给你重构一下:
    ```sql
    SELECT
    a.id,
    a.title,
    string_agg((select distinct a_tag.tag_name from article_tag a_tag inner join
    ag_article t on a.id=t.article_id inner join
    type_article type ON a.id = type.article_id inner join
    article_type a_type ON a_type.id=type.type_id),',')
    FROM article a
    ```
    gIrl1990
        15
    gIrl1990  
       2019-10-15 18:03:28 +08:00
    @cl903254852 https://v2ex.com/t/609544?p=1#r_8031352
    这个“范围” 区别体现在哪?按你的“标签”“分类”设计 A 文章分在标签 b 和 A 文章分在分类 b 有啥子区别?
    gz911122
        16
    gz911122  
       2019-10-15 18:12:35 +08:00
    @cl903254852 冗余下去好一些..
    单独出来没太大必要啊...

    不过这种帖子文字类的用 mongo 比较舒服,postgre 也行 mysql 完全是给自己制造复杂度...
    wongyusing
        17
    wongyusing  
       2019-10-15 18:30:26 +08:00
    你这样的表结构感觉很奇怪啊
    文章表、文章内容表可以合并在一起。
    没必要设置成一对一。
    而文章类型和文章标签为什么都用多对多啊??

    正常来说,文章和文章类型属于外键关联。
    文章和文章标签是多对多。

    而且你的分类哪里不应该用 type,在某些编程语言中属于是关键字。
    应该用 category
    inhzus
        18
    inhzus  
       2019-10-15 18:44:33 +08:00 via Android
    合理的逻辑应该是 类型和文章一对多,文章和标签多对多,
    taogen
        19
    taogen  
       2019-10-15 18:52:52 +08:00 via Android
    主键关联没什么大问题,就是业务关联看起来有点多。关联太多性能会比放在一张表差,但这些减少了数据的冗余、不一致性。

    建议用 left join。join 等于 inner join 取的是交集。
    wangyzj
        20
    wangyzj  
       2019-10-15 18:55:28 +08:00
    文章分类 article_type 单独一个表
    文章标题 article 和文章内容 article_content 一张表,增加 article_type_id 字段外键对应 article_type 的 type_id, 再增加一个 tag text 字段,把所有标签排重放里面做成数组,用 Sequelize 定义 model 的时候加一个 get 方法自定义 JSON.loads 这个字段自动转换成数组取出
    zeraba
        21
    zeraba  
       2019-10-15 18:56:06 +08:00 via Android
    先恢复一对一,再 join,笛卡尔积很可怕,比如文章对应的标签,先按照文章 id group by 再 concat 最后关联就都没有重复了
    wangyzj
        22
    wangyzj  
       2019-10-15 18:59:08 +08:00
    @cl903254852 酱紫俩表就解决问题了,基本上一次 update 操作,复杂的都没有了,text 可以做全文检索,虽然不咋滴
    多对多的话把 acticle_type_id 字段做成 list 结构,标签我觉得就不用做表了
    cl903254852
        23
    cl903254852  
    OP
       2019-10-15 20:11:52 +08:00
    @gIrl1990 可以不讨论这个吗,你就当产品是白痴,他就这么设计的。我提问题只是想知道这个 SQL 有没有更好的写法😹😹😹
    cl903254852
        24
    cl903254852  
    OP
       2019-10-15 20:16:18 +08:00
    @wongyusing 原谅我的无知,我才开始学 mysql。把文章内容分出去,是考虑到如果数据量很大,而前端列表里不需要展示文章内容,只有在详情里才会查文章内容,这样性能应该会更好,type 这个是我没考虑周全 受教了~。
    hosaos
        25
    hosaos  
       2019-10-15 20:31:32 +08:00   ❤️ 1
    分多次查询
    1、先单表查询文章
    2、根据文章 id 查询文章内容、标签、类型

    连表查 等你数据多了就炸了
    akira
        26
    akira  
       2019-10-15 23:23:13 +08:00
    典型的学院派,没啥大问题。
    greed1is9good
        27
    greed1is9good  
       2019-10-16 09:02:56 +08:00 via Android
    @gIrl1990 估计他的标签应该是和关键字差不多意思吧,其实通常的做法是文章表有个关键字(标签)字段,show 文章的时候关键字(标签)做成查询链接,点击后查询出包含比关键字(标签)的内容。
    cl903254852
        28
    cl903254852  
    OP
       2019-10-16 10:21:50 +08:00
    @akira 哈哈 我是看视频学的
    cl903254852
        29
    cl903254852  
    OP
       2019-10-16 10:22:19 +08:00
    cl903254852
        30
    cl903254852  
    OP
       2019-10-16 10:24:24 +08:00
    @hosaos 大佬意思是连表查,如果连的表多,在数据量大的情况下容易爆炸。 但分多次查,也会导致性能下降吧,我的想法是如果能一次查出来最好,用子查询会降低性能。。。 不知道这样想对不对
    wongyusing
        31
    wongyusing  
       2019-10-16 11:13:10 +08:00
    @cl903254852 你用的是 select * from xxxx 吗?
    你指定一下字段就行啦,这个不影响速度的啊。
    hosaos
        32
    hosaos  
       2019-10-16 15:18:32 +08:00
    @cl903254852 数据量大的情况下 多次查询优于联表查
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1174 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 23:06 · PVG 07:06 · LAX 16:06 · JFK 19:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.