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

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

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

tag需求：
（0）假定作品来自一个表（Poesy）,每个作品tag不超过5个
（1）根据tag可以找到 所有包括该tag的作品,可以查看tag包含作品的数量。
 (2)用户可以根据自己添加的tag来查看自己的作品,查看tag包括的作品的数量
 (3)用户可以查看所有tag,可以查看所有tag包括的作品
 (3)排序问题：按照作品访问量和时间排序


个人想了两套设计方案

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

-- 建表（作品表）：
if   object_id ( ' Poesy ' , ' u ' )  is   not   null
drop   table  Poesy
go

create   table  Poesy
(
    id  int   identity ( 1 , 1 ),
    Title  varchar ( 100 ),
    Tag  varchar ( 100 )
)
go
alter   table   Poesy  add   constraint  pk_Posy  primary   key (Id)
go

if   object_id ( ' Tags ' , ' u ' )  is   not   null
drop   table  Tags
go

create   table  Tags
(
    Id  int   identity ( 1 , 1 ),
    TagName  Varchar ( 30 ),
    TagCount  int ,   -- 作品数量
    TagCreatDate  datetime ,
    TagTips  int   -- 访问量
)
go
alter   table   Tags  add   constraint  pk_Tags  primary   key (Id)
go

-- 面向用户的tag
if   object_id ( ' UserTags ' , ' u ' )  is   not   null
drop   table  UserTags
go

create   table  UserTags
(
    Id  int   identity ( 1 , 1 ),
    TagUserId  int ,
    TagName  Varchar ( 30 ),
    TagCount  int ,  -- 作品数量
    TagCreatDate  datetime ,
    TagTips  int   -- 访问量
)
go
alter   table   UserTags  add   constraint  pk_UserTags  primary   key (Id)
go

-- 用户在增加、修改、删除作品的时候，都要对 tags和UserTags表进行操作，更新两个表里的tag包括的作品数量（如果没有则增加tag)

-- 造数据到作品表
declare   @Number   int , @NumberTop   int , @TagId   int
Set   @TagId   =   1
Set   @number   =   1
Set   @numberTop   =   10000000

while   @number <= @numberTop
begin
     if   @TagId   =   100
         Set   @TagId   =   1
     INSERT  Poesy(Title,Tag)
         select   ' title ' + cast ( @number   as   varchar ),  ' 历史 ' ++ cast ( @TagId   as   varchar ) + ' ,铁木真,元朝历史,蒙古历史 ' + cast ( @number   as   varchar )
     Set   @number   =   @number   + 1
     Set   @TagId   =   @TagId   + 1
end

-- 建立索引
create   index  Ix_poesyTag  on  Poesy(Tag)

-- 查询 很慢要一分钟以上（1）
with  Orderlist  as  (
     select  row_number()  over ( order   by  id  desc )  as  rownumber,id,title
     from  poesy
     where   charindex ( ' ,蒙古历史10000, ' , ' , ' + tag + ' , ' ) > 0
)  select  RowNumber,Id,Title
from  Orderlist
where  RowNumber  between   1   and   50

-- 查询很快（2）
with  Orderlist  as  (
     select  row_number()  over ( order   by  id  desc )  as  rownumber,id,title
     from  poesy
     where   charindex ( ' ,元朝历史, ' , ' , ' + tag + ' , ' ) > 0
)  select  RowNumber,Id,Title
from  Orderlist
where  RowNumber  between   1   and   50

复制代码

--建立索引
create index Ix_poesyTag on Poesy(Tag)

查询1很慢，因为 tag里 包含 蒙古历史10000的记录只有一条。
查询2很快，因为tag里包括 元朝历史 的记录非常多

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

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

-- tag表
if   object_id ( ' TestTagName ' , ' u ' )  is   not   null
drop   table  TestTagName
go

create   table  TestTagName
(
    id  int   identity ( 1 , 1 ),
    TagName  varchar ( 30 ),
)
go
alter   table   TestTagName  add   constraint  pk_TestTagName  primary   key (Id)
go
-- 插入tag
insert  TestTagName(Title)
select   ' 铁木真 '   union   all
select   ' 元朝历史 '   union   all
select   ' 蒙古历史10000 '   union   all
select   ' 蒙古历史 '

-- 文章tag表
if   object_id ( ' TestTagPoesy ' , ' u ' )  is   not   null
drop   table  TestTagPoesy
go

create   table  TestTagPoesy
(
    id  int   identity ( 1 , 1 ),
    TagId  int ,
    poesyid  int
)
go
alter   table   TestTagPoesy  add   constraint  pk_TestTagPoesy  primary   key (Id)
go

-- 增加测试数据 到 文章tag表

insert  TestTagPoesy(TagId,poesyid)
select   1 ,id
from  poesy

insert  TestTagPoesy(TagId,poesyid)
select   2 ,id
from  poesy

insert  TestTagPoesy(TagId,poesyid)
select   3 ,id
from  poesy
where  id =   10000

-- 索引：
create   index  ix_TestTagPoesy_poesyid  on  TestTagPoesy(poesyid)
create   index  ix_TestTagPoesy_tagid  on  TestTagPoesy(tagid)
create   index  ix_TestTagName_title  on  TestTagName(TagName)

-- 查询tag是 蒙古历史10000的文章
with  Orderlist  as  (
     select  row_number()  over ( order   by  a.id  desc )  as  rownumber,a.id,a.title
     from  poesy a   inner   join  TestTagPoesy  as  b  on  a.id  =  b.poesyid
     inner   join  TestTagName  as  c  on  b.tagid  =  c.id
     where  c.TagName  = ' 蒙古历史10000 '
)  select  RowNumber,Id,Title
from  Orderlist
where  RowNumber  between   1   and   50

-- 查询tag是 蒙古历史10000的文章
with  Orderlist  as  (
     select  row_number()  over ( order   by  a.id  desc )  as  rownumber,a.id,a.title
     from  poesy a   inner   join  TestTagPoesy  as  b  on  a.id  =  b.poesyid
     inner   join  TestTagName  as  c  on  b.tagid  =  c.id
     where  c.TagName  = ' 元朝历史 '
)  select  RowNumber,Id,Title
from  Orderlist
where  RowNumber  between   1   and   50

查询1很慢，因为 tag里 包含 蒙古历史10000的记录只有一条。
查询2很快，因为tag里包括 元朝历史 的记录非常多 

复制代码

三 系统测试

机器配置：
内存2G
CPU core 6420 双CPU 2.13G


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

（这里的测试重点是按照tag去找文章）

问题：

（1）这两个方案，打击支持哪一种？或者说这两个都不可取，可以有更好的方案。
（2）关于性能问题，这两个方案，除了提高硬件外还有别的办法提高性能吗？