业务关系是一对多要分页，并且多的一侧可以作为查询条件，要怎么设计？

  • Z时代
  • 2024-02-22
  • 分类：IT

比如说一个人有几种性格标签

我自己想了两种方法：

第一种：
设计两张表
user表：

a_idname
1小明
2小李
3小张

tag表：

iduser_idtag
11活泼
21可爱
22活泼
23可爱

分页sql：

select u.id,u.name,t.tag from  (select id,name from `user` limit #{crrent},#{size}) u LEFT JOIN tag t on  u.id=t.user_id;

结果:

1    小明    活泼
1    小明    可爱

然后用stream流在代码里对u.name 进行分组，将t.tag作为子集合返回。如果查询t.tag=活泼作为查询条件，则少了小明的另一个tag=可爱，结果：

1    小明    活泼
2    小李    活泼

达不到预期效果，感觉要是数据量过多，这样做性能也会出问题的吧

第二种：
设计一张表

a_idnametag
1小明活泼，可爱
2小李活泼
3小张可爱

sql:
select * from user where tag like '%活泼%' limit #{crrent},#{size}
能达到预期效果，但是违反了第一范式，数据量大可能性能也会有问题，然后tag去like
查询结果也不太准确

有没有更好的设计思路？

回答：

这样？

  • 在线测试
select *, (select group_concat(tag) from tag where user_id = user.id and tag = '活泼') as tags
from user
where exists(select 1 from tag where user_id = user.id and tag = '活泼' limit 1)
limit 0, 10;

回答：

这样？

结果

idnametag
1小明活泼
1小明可爱
2小李活泼

关于方法二

我觉得是不可能的，因为你查询多端时，需要扫全表。这是不可接受的

SQL 代码（SQLite 测试通过）

WITH
  user(id, name) AS (
    VALUES
      (1, '小明'),
      (2, '小李'),
      (3, '小张')
  ),
  tag(id, user_id, tag) AS (
    VALUES
      (1, 1, '活泼'),
      (2, 1, '可爱'),
      (2, 2, '活泼'),
      (2, 3, '可爱')
  )
SELECT u.id, u.name, t.tag
  FROM (SELECT id, name
          FROM user
         WHERE EXISTS (SELECT 1
                         FROM tag
                        WHERE user_id = user.id
                          AND tag = '活泼')
         LIMIT 0, 10) u
  JOIN tag t ON u.id = t.user_id;

可能的性能改进

  1. 上述 SQL 拆成两条后再执行，性能应该会更好（因为外层不需要每一行都有 u.name，应避免这种冗余数据的传输）
  2. 分页方式改成 WHERE user.id > {上一次分页最后一个user.id} ORDER BY user.id LIMIT {每页数量}，性能应该会更好（页码越大，越能体现）

回答：

如果是mysql的话，第二种一张表的方法，可以使用FIND_IN_SET这个方法，tag里的每一项以逗号隔开，如下

select * from user where FIND_IN_SET('活泼',tag) limit #{crrent},#{size}

回答：

用合适的工具解决合适的事。

个人以为 Elasticsearch 更适合解决“标签”这个使用场景。

  1. 部分更新标签
  2. 分页返回
  3. 查询时可选择匹配一个或多个标签

以上是 业务关系是一对多要分页，并且多的一侧可以作为查询条件，要怎么设计？ 的全部内容， 来源链接： utcz.com/p/944736.html

回到顶部