一次关于基于标签的查询与维护的数据结构设计探索

需求背景

对Author打标签（Tag），两者完全多对多映射：即一个author对应多个tag，一个tag也能被多个author所使用。

此数据结构设计需考虑一下操作的效率（实际耗时短）与便捷（使用hibernate ORM编码逻辑简单）。

给author增加tag
给author移除tag
查询所有的author关联的tag集合
查询某条author的所有tag集合
根据一条或多条tag查询同时带有这些标签的author集合

雏形

引入一个中间表Author_Tag，使用外键关联实现关系型数据库经典的ManyToMany关系
在author中保存用连接符（如逗号或空格）连接的tag名字长字符串
在author中保存用连接符连接的tag主键长字符串

基本行为对比

方案	增tag	删tag	查所有tag	author查tags	tag查authors
经典多对多	增加中间表	删除中间表	OK	中间表与tag表	查中间表与author表
保存tag名	直接修改author字段	直接修改author字段	OK	最快，只是普通的字段	很慢，全表like字段，并且索引基本无效果：中文，a+b的索引不等于b+a
保存tag.id	直接修改author字段	直接修改author字段	OK	将id.split后逐一查询tag表	较慢，内容都是数字，但仍然会由于顺序不同造成索引效果不佳

对比可见：

中间表多对多的方案在ORM的帮助下，编码最为方便，但需要查询多张表
保存tag名方案的最大优势在于又author查询tag是单表查询，但是浪费空间，并且反向查询效率极差
保存tag.id方案相对中庸，反向查询效率较高

实际开发小坑

最开始选择的是方案1，增删改查都十分方便，但是当要求多个tag并列筛选author时，写了个特别坑爹的sql：

SELECT a.*
FROM Author a 
JOIN Author_Tag aut ON(a.id=aut.author_id)
WHERE aut.tag_id=1
OR aut.tag_id=2
OR aut.tag_id=3
GROUP BY a.id
HAVING COUNT(DISTINCT aut.tag_id)=3

字面理解的话，这个SQL十分符合人类（菜鸟）的思维:

tag.id=1,2,3不是in，不是or，但也不是and，因为笛卡尔积中不可能有一条数据同时满足tag.id=1 and tag.id=2 and tag.id=3

于是将满足任一条件的结果查询出来，在数个数是否等于3，就可以判断是否同时满足。

但是，千万忍住这样的诱惑，这样的聚合查询会大大拖慢SQL查询速度。

stackoverflow中推荐这种SQL查询语句：

SELECT aut1.author_id
FROM Author_Tag aut1 
JOIN Author_Tag aut2 ON(aut1.author_id=aut2.author_id and aut2.tag_id=2)
JOIN Author_Tag aut3 ON(aut1.author_id=aut3.author_id and aut3.tag_id=3)
WHERE aut1.tag_id=1

这种想法也不难理解，自连接三次后将笛卡尔积三个条件全部满足的部分筛选出来，留下来的author_id就是三个tag都满足的author数据

至于这个查询为什么比上面的聚合查询选个数快20倍，具体的原因我也不是十分清楚，模糊得感觉到这是一个空间换时间的策略。

有待后续深入学习数据库再来填这个坑。

确定方案

由于考虑到由tag查询author应该很少使用（产品上比较隐蔽，也没有突出此功能），最终选择了方案2来追求author查tags的方便与快捷。

用户标签数据结构设计