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

Question

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

我自己想了两种方法：

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

a_id	name
1	小明
2	小李
3	小张

tag表：

id	user_id	tag
1	1	活泼
2	1	可爱
2	2	活泼
2	3	可爱

分页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_id	name	tag
1	小明	活泼，可爱
2	小李	活泼
3	小张	可爱

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

有没有更好的设计思路？

Answer 1

这样？

• 在线测试

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;

Answer 2

这样？

结果

id	name	tag
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 {每页数量}，性能应该会更好（页码越大，越能体现）

Answer 3

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

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

Answer 4

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

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

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