探讨下Tag标签的数据库设计(千万级数据量)

现在博客都有tag标签的功能，如何设计千万级数据量Tag数据库呢？现在把功能限制一下

tag需求：

(0)假定作品来自一个表(Poesy),每个作品tag不超过5个

(1)根据tag可以找到 所有包括该tag的作品,可以查看tag包含作品的数量。

(2)用户可以根据自己添加的tag来查看自己的作品,查看tag包括的作品的数量

(3)用户可以查看所有tag,可以查看所有tag包括的作品

(3)排序问题：按照作品访问量和时间排序

个人想了两套设计方案

方案一：在作品表里增加一个varchar(100)的tag列,各个tag以 , 分割，详见下：

--建表(作品表)：ifobject_id('Poesy','u')isnotnulldroptablePoesygocreatetablePoesy

(

idintidentity(1,1),

Titlevarchar(100),

Tagvarchar(100)

)goaltertablePoesyaddconstraintpk_Posyprimarykey(Id)goifobject_id('Tags','u')isnotnulldroptableTagsgocreatetableTags

(

Idintidentity(1,1),

TagNameVarchar(30),

TagCountint,--作品数量TagCreatDatedatetime,

TagTipsint--访问量)goaltertableTagsaddconstraintpk_Tagsprimarykey(Id)go--面向用户的tagifobject_id('UserTags','u')isnotnulldroptableUserTagsgocreatetableUserTags

(

Idintidentity(1,1),

TagUserIdint,

TagNameVarchar(30),

TagCountint,--作品数量TagCreatDatedatetime,

TagTipsint--访问量)goaltertableUserTagsaddconstraintpk_UserTagsprimarykey(Id)go--用户在增加、修改、删除作品的时候，都要对 tags和UserTags表进行操作，更新两个表里的tag包括的作品数量(如果没有则增加tag)--造数据到作品表declare@Numberint,@NumberTopint,@TagIdintSet@TagId=1Set@number=1Set@numberTop=10000000while@number<=@numberTopbeginif@TagId=100Set@TagId=1INSERTPoesy(Title,Tag)select'title'+cast(@numberasvarchar),'历史'++cast(@TagIdasvarchar)+',铁木真,元朝历史,蒙古历史'+cast(@numberasvarchar)Set@number=@number+1Set@TagId=@TagId+1end--建立索引createindexIx_poesyTagonPoesy(Tag)--查询 很慢要一分钟以上(1)withOrderlistas(selectrow_number()over(orderbyiddesc)asrownumber,id,titlefrompoesywherecharindex(',蒙古历史10000,',','+tag+',')>0)selectRowNumber,Id,TitlefromOrderlistwhereRowNumberbetween1and50--查询很快(2)withOrderlistas(selectrow_number()over(orderbyiddesc)asrownumber,id,titlefrompoesywherecharindex(',元朝历史,',','+tag+',')>0)selectRowNumber,Id,TitlefromOrderlistwhereRowNumberbetween1and50

--建立索引

create index Ix_poesyTag on Poesy(Tag)

查询1很慢，因为 tag里 包含 蒙古历史10000的记录只有一条。

查询2很快，因为tag里包括 元朝历史 的记录非常多

建立了索引，也没有什么变化。

方案二：增加一个文章TAG关联表。详见下：

--tag表ifobject_id('TestTagName','u')isnotnulldroptableTestTagNamegocreatetableTestTagName

(

idintidentity(1,1),

TagNamevarchar(30),

)goaltertableTestTagNameaddconstraintpk_TestTagNameprimarykey(Id)go--插入taginsertTestTagName(Title)select'铁木真'unionallselect'元朝历史'unionallselect'蒙古历史10000'unionallselect'蒙古历史'--文章tag表ifobject_id('TestTagPoesy','u')isnotnulldroptableTestTagPoesygocreatetableTestTagPoesy

(

idintidentity(1,1),

TagIdint,

poesyidint)goaltertableTestTagPoesyaddconstraintpk_TestTagPoesyprimarykey(Id)go--增加测试数据 到 文章tag表insertTestTagPoesy(TagId,poesyid)select1,idfrompoesyinsertTestTagPoesy(TagId,poesyid)select2,idfrompoesyinsertTestTagPoesy(TagId,poesyid)select3,idfrompoesywhereid=10000--索引：createindexix_TestTagPoesy_poesyidonTestTagPoesy(poesyid)createindexix_TestTagPoesy_tagidonTestTagPoesy(tagid)createindexix_TestTagName_titleonTestTagName(TagName)--查询tag是 蒙古历史10000的文章withOrderlistas(selectrow_number()over(orderbya.iddesc)asrownumber,a.id,a.titlefrompoesy ainnerjoinTestTagPoesyasbona.id=b.poesyidinnerjoinTestTagNameasconb.tagid=c.idwherec.TagName='蒙古历史10000')selectRowNumber,Id,TitlefromOrderlistwhereRowNumberbetween1and50--查询tag是 蒙古历史10000的文章withOrderlistas(selectrow_number()over(orderbya.iddesc)asrownumber,a.id,a.titlefrompoesy ainnerjoinTestTagPoesyasbona.id=b.poesyidinnerjoinTestTagNameasconb.tagid=c.idwherec.TagName='元朝历史')selectRowNumber,Id,TitlefromOrderlistwhereRowNumberbetween1and50查询1很慢，因为 tag里 包含 蒙古历史10000的记录只有一条。

查询2很快，因为tag里包括 元朝历史 的记录非常多

三 系统测试

机器配置：

内存2G

CPU core 6420 双CPU 2.13G

方案1 和方案二的查询结果是一样，速度都很慢。要一分钟以上。

(这里的测试重点是按照tag去找文章)

问题：

(1)这两个方案，打击支持哪一种？或者说这两个都不可取，可以有更好的方案。

(2)关于性能问题，这两个方案，除了提高硬件外还有别的办法提高性能吗？