tags的数据库设计问题

每个topic有几个tags，每个tag也能对应多个topic，应该如何设计数据库最合理呢？

想到的一种解决办法是三张表：
1.topic (topic_id,tags)多个tags用某个特殊符号分隔
2.tag (tag_id,tag)
3.topic_tag (topic_id,tag_id)

但是觉得效率会很低，大家的解决方案是怎样？

Topic

tag

tags

50 条回复 • 2014-07-04 16:43:55 +08:00

hbc

2012-12-08 23:39:28 +08:00

many2many 啊

best1a

2012-12-08 23:47:12 +08:00

当时弄某个东西就是三张表。。。。
有一点不太理解，都有topic_tag表了，为什么topic中还有tags字段，方便一次取出？

gfreezy

2012-12-08 23:53:19 +08:00

topic_tag(topic_id, tag_id)

select distinct(topic_id) from topic_tag where tag_id = ?

select distinct(tag_id) from topic_tag where topic_id = ?

gfreezy

2012-12-08 23:54:42 +08:00

没看清楚

@best1a 同不理解， left join不就可以了

lqs

2012-12-09 00:06:57 +08:00

@best1a @gfreezy 用冗余字段提高性能，节约若干次disk seek

ElmerZhang

2012-12-09 00:09:09 +08:00

楼主这种方案是比较好的方案，只要索引合理，效率不会有问题。

gfreezy

2012-12-09 00:22:46 +08:00

@lqs 一般情况下只要索引ok，基本都不会有性能问题，除非量真的特别大。直接在topic表里面存tag字符串也有个问题，比如已经有了一个topic，也拿到了他的tags，但是你要给这些tag加链接，你必须至少还要查询一次tag表来拿tag_id（你的tag的URL是根据tag_id拼出来的情况下)。

如果tag可以只存为字符串的话，直接放redis：
* key: "topic:{{topic_id}}" value: tag list
* key: "tag:{{tag_name}}" value: topic_id list

BigZ

2012-12-09 00:37:42 +08:00

简单点，一个字段，tag用逗号分隔

lookhi

2012-12-09 09:01:33 +08:00

@BigZ 我们也是这么干的

lusin

2012-12-09 11:51:01 +08:00

用标点分隔的话
比如 iphone5(苹果，手机)
然后要取出tag为手机的所有数据，应该效率会低吧

lusin

2012-12-09 11:52:57 +08:00

topic1 tag1
topic1 tag2
topic2 tag1
topic2 tag3
这样做表效率是不是高些？

sobigfish

2012-12-09 12:07:47 +08:00

直接写成数组啊，存在表里
topic1 tag1,tag2

Mutoo

2012-12-09 12:15:26 +08:00

多对多对应关系，范式化后就是三个表。
topic (topicid, ...)
tag (tagid, tag, ...)
topic_tag (topicid, tagid)

这样的好处显而易见。

对topic增删改查tag时只要操作topic_tag表
对tag改名，只需要改tag表
删除tag，只需要删除tag表和topic_tag对应项
统计也很方便
...

atom

2012-12-09 21:16:31 +08:00

楼主的方案就是标准范式，保证一致性，缺点是查询慢。
其它用分隔符只存一张表的是反范式方案，优点是查询快，缺点是一致性比较痛苦。

但吃过一致性的亏后，我会倾向于这样的方案：
1.用标准范式保证一致性；
2.增加缓存层保存热数据，里面的数据已做好关联，便于展现层或业务层直接拿来使用。
3.最上层会有个切换开关（读缓存还是读DB），80%的情况下使用cache数据，做到best-effort，强调可用性，但数据可能不准确。

以下是无责任引用
-----------------------------------------
• 事务处理型:对于这种类型的应用程序,你的用户更关注数据的增查改删(CRUD,Creating/Reading/Updating/Deleting)。这种类型官方称之为 “OLTP”。
• 分析型:对于这种类型的应用程序,你的用户更关注数据分析、报表、趋势预测等功能。这一类的数据库的“插入” 和“更新”操作相对来说是比较少的。用户的主要目的是更加快速地查询、分析数据。这种类型官方称之为 “OLAP”。
换句话说,如果你认为插入、更新、删除数据这些操作在你的程序中更为突出的话,那就设计一个规范化的表,否则的话就去创建一个扁平的、不规范化的数据库结构。

isy

2012-12-09 22:07:14 +08:00

汗，这就是基本多对多的问题。像楼主这样设计没错？用逗号分割存成一个字段的做法太不靠谱了。

zhangtao

2012-12-10 00:15:02 +08:00

想法同@Mutoo ，至于特殊符号分隔还是算了吧，非主流做法

ipconfiger

2012-12-10 00:46:49 +08:00

标准范式查询慢？是SQL优化没做好吧。

AntiGameZ

2012-12-10 02:00:51 +08:00

@isy tags字段的冗余，对减少join次数，提升查询速度来说，在大数据量的时候，意义很大。这样做唯一的问题是，如果有奇葩需求希望修改tagName，需要逐个去修改对应topic tags冗余字段的内容。不过完全可以延迟慢慢去做。

napoleonu

2012-12-10 09:08:42 +08:00

LZ方案很好，如果 1.topic (topic_id,tags) 里面的tags是一段包含tags id的json就更好了，让写分散读压力。

88250

2012-12-10 09:28:03 +08:00

目前和楼主的设计一样，没有发现性能问题。

huaxinjiayou

2012-12-10 10:35:06 +08:00

跟楼主一样…多对多…坐等更好的解决方案…

ipconfiger

2012-12-10 14:24:45 +08:00

符号分隔做冗余的方案基本上多此一举，如果要用性能来说事的话，加缓存即可，何必在查询的时候冗余到字段里，这样在添加新TAG的时候又要做不少事情来保持冗余数据的同步。正确的使用缓存的方法是缓存计算的结果非计算的中间步骤。冗余字段的方式就是典型的舍本逐末了。
假设一个场景，读取topic，同时读取topic相关的TAG，那么你应该缓存的是
select * from topic where id=id 和 select * from topic_tag where topic_id=id 的返回结果，甚至是这个页面渲染的结果，到时候直接输出页面就行了，序列化成字符串存储在一个列里的话，如果不缓存select * from topic where id=id 的结果，那么每次查询都有开销，而且这个方式还会增大每次查询的返回数据大小。如果缓存了整个逻辑的结果那么你冗余存的这一次就没什么意义，反而增大了数据库的冗余，还存在不同步的风险。所以什么都好分割啊，什么存json啊都是异端的设计，基本上都是没有经受过大负载访问洗礼的幼稚设计方法。

wuxqing

2012-12-10 14:43:46 +08:00

@ipconfiger
使用缓存，也要考虑同步的风险

napoleonu

2012-12-10 14:55:05 +08:00

@ipconfiger

stackoverflow.com Alexa Traffic Rank: 85 Traffic Rank in IN: 28

我保证stackoverflow用这种设计都不会出问题。

亲，你生下来就是为了做淘宝的吧，哪那么多大网站啊。

napoleonu

2012-12-10 14:57:14 +08:00

@napoleonu 如果一个页面50条记录，我还真不信每个页面访问50次cache的代价比把tag冗余一下的代价要低。

ipconfiger

2012-12-10 15:55:57 +08:00

@wuxqing 用缓存肯定要实现针对资源的notification机制，当版本过期就应该让缓存失效重新加载数据就行了。

@napoleonu 亲，你理解错了吧，如果只是tag数量，这个存字段里是ok的，但是TAG本身存字段里确实不科学。另外你估计还是没有理解缓存结果而不是运算中间过程的意义是啥

napoleonu

2012-12-10 17:02:44 +08:00

@ipconfiger

http://tagging.pui.ch/post/37027746608/tagsystems-performance-tests

多年前看到的一篇文章，使用这种设计的多着呢。

我确实没理解你“缓存结果”的做法，stackoverflow一共400万帖，按照50条每页(每条平均3个tag)，分80000页，之后，每增加一条记录就重新生成80000页的缓存？不同的页面或者排序可能还有不同的分页，请不吝赐教。

可能我对你只有仰望的份，但你说我“幼稚”，我只能回敬你一句“二逼”，请原谅我的小心眼。

napoleonu

2012-12-10 17:05:27 +08:00

@napoleonu 因为我跟你不熟。

bhuztez

2012-12-10 18:37:30 +08:00

搭车求问，PostgreSQL intarray怎么建索引才对啊？我自己试的时候，发现无论建GIN还是GiST索引，EXPLAIN的结果都和没建没区别啊

http://www.postgresql.org/docs/current/static/intarray.html

zhfsxtx

2012-12-10 21:53:38 +08:00

那如果是 mongodb 应该怎么保存呢

ipconfiger

2012-12-12 09:36:12 +08:00

@napoleonu 既然这么不客气那我就来指出你的二逼之处。你所谓的重新生成80000页的缓存的说法极其可笑，很显然你没用过缓存，而且把缓存和生成静态页弄混了。回去多学几年吧，我没有必要在这里教你缓存怎么用。二逼青年

ElmerZhang

2012-12-12 10:14:27 +08:00

楼上那些把tag或者tagid拼成各种字符串写在一个字段里的，八成是根本没考虑由tag查topic这种常用场景。
把tag或tagid拼成一个字符串记一起，怎么查？用like？查一次就全表扫描一次，机器表示压力很大。

yupbank

2012-12-12 10:21:43 +08:00

@napoleonu ...那个topic-> tag还是加个缓存吧，不要放表了，不然一个topic新增一个tag，你多了一张topic表需要维护。

80000页的缓存问题。。哥，缓存真不是这么操作的，一口气命中80000页也太流弊了。。

yupbank

2012-12-12 10:23:38 +08:00

@ElmerZhang 。。。人家有topic_tag表

其实扯那么多范式也没有说到点子吧，楼主是来问他那个冗余巧不巧妙的。

ElmerZhang

2012-12-12 13:38:45 +08:00

@yupbank 我是在说楼上有些不要 topic_tag 表的。我认为楼主的设计是标准答案。

xjay

2012-12-12 14:52:05 +08:00

1.尽量避免join
2.可以把tagids以及tagnames一起存放进topic表内
3.你的设计表结构还是可以不变

huxos

2012-12-12 23:34:37 +08:00

为什么我觉得这样简单点呢 topic 表什么都不变。
tags（topic_id, tag_value);
每一个topic 有一个tag 就向tags 插一条。
还通过tag_value 轻松的得到一个topic 的列表。

napoleonu

2012-12-12 23:34:52 +08:00

@ipconfiger 我只是想告诉你你可以缓存page，缓存list的代价很大。恩，还没毕业，等我工作两年应该就懂了吧。

napoleonu

2012-12-12 23:39:54 +08:00

@yupbank 网站型的应用读多写少，适量增加写的复杂度来改善读的压力很正常。另外一种比较常见的牺牲写来提高读的案例就是基数，总数计数，分类计数，等等各种计数，看起来写的时候要维护这些似乎很麻烦，但是一次写可以为N次读提供方便。

@ipconfiger 二逼，我说你二逼是因为你不礼貌，我说了技术上我对你只有仰望。已BLOCK，二逼。

napoleonu

2012-12-12 23:47:50 +08:00

@ElmerZhang 用户通过某条post的tag查找这一tag的所有信息流程可以这样

通过tag获得tagid（tagid_tag表）

通过tagid获得这一tag的所有post （tagid_postid表）

zhfsxtx

2012-12-13 00:39:47 +08:00

怎么没人帮我讲下 mongodb 该怎么弄呢，额啊

talentsnail

2012-12-14 22:16:48 +08:00

@gfreezy
@lqs
@ElmerZhang
@BigZ
@lookhi
@lusin
@sobigfish
@Mutoo
@atom
@isy
@zhangtao
@AntiGameZ
@napoleonu
@88250
@yupbank
@xjay
@huxos
谢谢大家，大家的讨论对我很有启发，部分感谢已经送出：）

paloalto

2012-12-14 22:26:44 +08:00

我也是这么做的，只不过topic表里只存了一个记录tag数量的字段tag_num
其他的两张表一个放tag_id和tag_name
一个放tag_id和top_id

gfreezy

2012-12-14 22:31:53 +08:00

怎么设计关系真心不大，反证流量高了怎么设计都是要靠缓存来抗的。
与其各种为了性能做的hack，还不如把表结构做的清晰，然后通过缓存来解决性能问题。
@napoleonu 缓存list，不是直接缓存对象，是缓存一个id的list。然后每个model只会被缓存一次，取的时候根据id list，用get_multi,一次性获得一个list的对象。

lookhi

2012-12-14 22:37:55 +08:00

@ElmerZhang 因为还有站内搜索

xjay

2012-12-15 02:21:33 +08:00

@gfreezy 流量高对系统架构要求更高，什么缓存，什么cdn，都不可信，一旦缓存命中不到，或者cdn抽风，你的系统是不是随时准备挂掉了？表结构清晰是好，但是如果数据量过大，单表查询问题不大，链表的话，很耗时的。如果topic的tag量不大可以直接保存到topic表，那就是相当于数据库端的缓存，一旦你前端缓存命中不到的时候，后端缓存还可以用的上。而many2many的结构也需要，主要是用来做备份，一旦tag缓存字段出问题了，那么这个many2many还可以用得上，可以用来恢复数据等。

napoleonu

2012-12-15 09:57:41 +08:00

@gfreezy id list哪里来的？数据库读？

nojt7Zm

2012-12-15 10:26:30 +08:00

标记下

napoleonu

2012-12-15 10:51:19 +08:00

@gfreezy

如果用上覆盖索引（index1(last_reply_time,topicid)，index2(post_time,topicid)，index3(tagid,topicid)）之后再通过get_multi获得列表详细确实比走覆盖索引再join一下topic 表或者再回表获得详细信息效率高很多，mysql数据量大之后join的效果不是那么理想。多谢指导，获益匪浅。

这是建立在从cache拿数据比直接从数据库拿数据效率高，没有测试，实际上也不一定，别的不说，直接从数据库join拿，只需要一次网络IO，而从数据库拿到再去cache获得详细得两次网络IO。当然当数据量很大并且越来越大，随着join性能越来越低的时候走cache的效率会越高，水平切分的分布式系统就更不用说了。

还有一点我想说的是，即使是 stackoverflow.com 这种全球排名百名内的站点，这么多年来也就400万topic的数据，大网站并没有想象的那么多。百万量级甚至千万量级的数据规模下，LZ这种设计可以说一点问题都没有，特别是在内存廉价和SSD出现后，facebook数据库服务器标配内存不都128G还是256G了么。

从楼上的各个回答来看，使用或者赞同这种设计的人不在少数。退一万步讲，冗余下tags对于生成cache也是有好处的，而业务复杂度和空间上损失的代价也不是那么高，当然如果cache用的好看起来必要性似乎也没那么高，但肯定称不上幼稚的设计。

sectic

2014-07-04 16:43:55 +08:00

我们用redis搞了这个问题。 tag -> entity , entity -> tag 有点浪费