用户标签库设计 mysql_探讨下Tag标签的数据库设计（千万级数据量）
探讨下Tag标签的数据库设计(千万级数据量)
当前博客系统普遍采用Tag标签功能，在此基础上，请对现有功能模块进行优化与精简。针对海量级数据需求，请问如何构建高效可靠的Tag数据库系统？
tag需求：
(0)假定作品来自一个表(Poesy),每个作品tag不超过5个
(1)根据tag可以找到 所有包括该tag的作品,可以查看tag包含作品的数量。
用户可以通过所添加的标签来检阅自己创作的作品，并了解该标签涵盖的作品数量
(3)用户可以查看所有tag,可以查看所有tag包括的作品
(3)排序问题：按照作品访问量和时间排序
个人想了两套设计方案
方案一：在作品表里增加一个varchar(100)的tag列,各个tag以 , 分割，详见下：
如果'Poesy'存在于对象ID列表中且不为空，则删除对应的'Poesy'表。
(
idintidentity(1,1),
Titlevarchar(100),
Tagvarchar(100)
createConstraint PrimaryKey on table guidId in database dbPosy for column set { 'Id' };
if (object_id('Tags', 'u') is not null) then drop table Tags else create table Tags (
Tags text,
created_at timestamp not null default current_timestamp,
updated_at timestamp not null on update current_timestamp
);
(
Idintidentity(1,1),
TagNameVarchar(30),
TagCountint,--作品数量TagCreatDatedatetime,
TagTips int::"访问量" 聚集函数 转换为 PostgreSQL 标签表；创建 PostgreSQL 标签表上的主键约束；如果 object_id('UserTags', 'u') 不等于 null，则删除 User_tags 表；否则创建 PostgreSQL 用户标签表 User_tags 并将其添加到当前数据库中。
(
Idintidentity(1,1),
TagUserIdint,
TagNameVarchar(30),
TagCountint,--作品数量TagCreatDatedatetime,
通过访问量字段（TagTips）进行关联设置，并在UserTags表中创建主键（pk_UserTags）以唯一标识条目。系统将自动对poesy表中的标题与Tag字段建立关联关系。当执行增删改操作时，默认会维护两张表的数据完整性，并动态更新每个标签（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)选择较慢查询withOrderlistas(selectrow_number()over(orderbyiddesc)asrownumber,id,titlefrompoesywherecharindex(',蒙古历史10000,',','+tag+',')>0)selectRowNumber,Id,TitlefromOrderlistwhereRowNumberbetween1and50选择快速查询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关联表。详见下：
在Tagged Table模式下操作时，在查询结果中发现目标对象ID存在且非空的情况下，则会同时删除相应表名的目标对象表，并创建新的目标对象表
(
idintidentity(1,1),
TagNamevarchar(30),
设置表 TestTagsName 并添加主键约束 Primary Key (Id)，然后插入测试标签（标题）。执行以下查询：选择 '铁木真', 元朝历史, 蒙古历史 10,000, 蒙古历史字段进行合并查询；如果 TestTagsNameID 存在，则删除该表并创建新的 TestTagsName 表。
(
idintidentity(1,1),
TagIdint,
往文章tag表中 增加测试数据 insertTestTagPoesy(TagId,poesyid) select 1,id from poetry select 2,id from poetry select 3,id from poetry where id = 1000 -- 索引：createindexix_TestTagPoesy_poesyidonTestTagPoesY(poesYid) createindexix_TestTagPoeSy_tagidonTestTagPoEY(tagid) createindexix_TestTagName_titleonTestTagName(TagName) -- 查询tag是 蒙古历史1OoO的文章 withOrderlist as ( select row_number() over(order by a.id desc) as rownumber, a.id, a.title from poetry a inner join TestTagPoeSy as bon a. id = b.poEsid inner join TestTagName as con b. tagid = c.id where c_TAGname = '蒙古历史1OoO') select RowNumber, Id, Title from Orderlist where RowNumber between 1 and 5O -- 查询tag是 元朝历史 的文章 withOrderlist as ( select row_number() over(order by a.id desc) as rownumber, a.id, a.title from poetry a inner join TestTagPoeSy as bon a. id = b.poEsid inner join TestTagName as con b. tagid = c.id where c_TAGname = '元朝历史') select RowNumber, Id, Title from Orderlist where RowNumber between 1 and 5O -- 查询I运行缓慢 因为 tag表中包含 蒙古历史I OOO的一条记录
查询2很快，因为tag里包括 元朝历史 的记录非常多
三 系统测试
机器配置：
内存2G
CPU core 6420 双CPU 2.13G
方案1 和方案二的查询结果是一样，速度都很慢。要一分钟以上。
(这里的测试重点是按照tag去找文章)
问题：
(1)这两个方案，打击支持哪一种？或者说这两个都不可取，可以有更好的方案。
(2)关于性能问题，这两个方案，除了提高硬件外还有别的办法提高性能吗？