高访问量的评论系统数据库存储过程架构-mysql教程-PHP中文网

首页

数据库

mysql教程

高访问量的评论系统数据库存储过程架构

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:56 PM

存储数据库架构系统访问量评论过程

网站的评论信息是最耗资源的地方，做好评论数据的优化对大型站点来说至关重要无 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[CommentsTables]([ID] [int] IDENTITY(1,1) NOT NULL,[Key] [nvarchar](50) NOT NULL,[TableName] [nvarch

网站的评论信息是最耗资源的地方，做好评论数据的优化对大型站点来说至关重要

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CommentsTables](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Key] [nvarchar](50) NOT NULL,
	[TableName] [nvarchar](80) NOT NULL,
	[StartID] [int] NOT NULL,
	[EndID] [int] NOT NULL,
 CONSTRAINT [PK_SysTables] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO






--根据SourceID和key获得表名
create function [dbo].[funGetTableName]
(
@SourceID int,
@Key nvarchar(50)
)
RETURNS nvarchar(80)
as 
begin
	declare @tableName nvarchar(80);
	declare @tableArea int;
	declare @mod int;
	
	declare @Size int;
	set @Size = 1000;
	
	set @mod = @SourceID % @Size;
	if @mod > 0 
		set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;		
	else	
		set @tableArea = Cast((@SourceID-1) / @Size as int);
			
			
	set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
	
	return @tableName
end
GO







--评论写入调用存储过程

CREATE proc [dbo].[procAddComment]
(
@ParentID int,
@SourceID int,
@NickName nvarchar(20),
@Content nvarchar(300),
@IP nvarchar(30),
@City nvarchar(30),
@BeFiltered bit,
@Disable bit,
@Key nvarchar(50),
@InsertedID int Output
)
as
begin
	declare @tableName nvarchar(80);
	declare @tableArea int;
	declare @mod int;
	
	declare @Size int;
	set @Size = 1000;
	
	set @mod = @SourceID % @Size;
	if @mod > 0 
		set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;		
	else	
		set @tableArea = Cast((@SourceID-1) / @Size as int);
		


	
	set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
	
	if not Exists(select 'x' from [CommentsTables] where [Key]=@Key and [TableName]=@tableName)	
	begin
	
		declare @StartID int;
		declare @EndID int;
		
		set @EndID = @tableArea * @Size;
		set @StartID = @EndID - (@Size-1);
	
	
		--创建表
		declare @CreateSQL nvarchar(MAX);
		set @CreateSQL = 
		'Create table [dbo].['+@tableName+'](
		[ID] [int] IDENTITY(1,1) NOT NULL,
		[ParentID] [int] NOT NULL,
		[SourceID] [int] NOT NULL,
		[NickName] [nvarchar](20) NOT NULL,
		[Content] [nvarchar](300) NOT NULL,
		[Datetime] [datetime] NOT NULL,
		[IP] [nvarchar](30) NOT NULL,
		[City] [nvarchar](30) NOT NULL,
		[BeFiltered] [bit] NOT NULL,
		[Disable] [bit] NOT NULL,
		[Lou] [int] NOT NULL,
		[Ding] [int] NOT NULL,
		[Cai] [int] NOT NULL,
		 CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]'
		
		
		EXEC(@CreateSQL);
		
		
		--创建索引 ID DESC
		EXEC('		
		CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+'] 
		(
			[ID] DESC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
		
		--创建索引 Ding DESC
		EXEC('
		CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+'] 
		(
			[Ding] DESC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
		
		--创建索引 SourceID DESC
		EXEC('
		CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+'] 
		(
			[SourceID] DESC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');

		--创建索引 Lou DESC
		EXEC('
		CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Lou_DESC] ON [dbo].['+@tableName+'] 
		(
			[Lou] DESC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]')
		
		
		--创建默认值
		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_ParentID]  DEFAULT ((0)) FOR [ParentID]');
		
		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Datetime]  DEFAULT (getdate()) FOR [Datetime]');
		
		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_BeFiltered]  DEFAULT ((0)) FOR [BeFiltered]');	

		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Disable]  DEFAULT ((0)) FOR [Disable]');
	
		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Lou]  DEFAULT ((1)) FOR [Lou]');
			
		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Ding]  DEFAULT ((0)) FOR [Ding]');
		
		EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD  CONSTRAINT [DF_'+@tableName+'_Cai]  DEFAULT ((0)) FOR [Cai]');
		
		Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID);
	end 

	
	declare @TemLou int;
	declare @SQL nvarchar(MAX);
	set @SQL = N'select @TemLou = Count(ID) from dbo.['+@tableName+N'] where SourceID=@SourceID';
	
	exec sp_executesql @SQL,
	N'@SourceID int,@TemLou int output',
	@SourceID,
	@TemLou output;
	
	if @TemLou = 0
		set @TemLou = 1;
	else
		set @TemLou = @TemLou + 1;
	
	
	
	declare @Lou int;
	set @Lou = @TemLou;
	
	declare @InsertSQL nvarchar(MAX);
	set @InsertSQL = N'Insert Into dbo.['+@tableName+N'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable],[Lou])
	values (@ParentID,@SourceID,@NickName,@Content,@IP,@City,@BeFiltered,@Disable,@Lou);select @InsertedID = SCOPE_IDENTITY();';
	
	exec sp_executesql @InsertSQL,
	N'@ParentID int,@SourceID int,@NickName nvarchar(20),@Content nvarchar(300),@IP nvarchar(30),@City nvarchar(30),@BeFiltered bit,@Disable bit,@Lou int,@InsertedID int output',
	@ParentID,
	@SourceID,
	@NickName,
	@Content,
	@IP,
	@City,
	@BeFiltered,
	@Disable,
	@Lou,
	@InsertedID output;
end 








GO















--获得最新评论存储过程

CREATE proc [dbo].[procGetNewComments]
(
@SourceID int,
@Key nvarchar(50),
@PageIndex int,
@PageSize int,
@Fields nvarchar(100),
@PageCount int output
)
as
begin
	declare @tableName nvarchar(80);
	set @tableName = dbo.funGetTableName(@SourceID,@Key);
	declare @Rc int;	
	
	declare @SQL nvarchar(MAX);
	set @SQL = N'select @Rc = COUNT(ID) from dbo.['+@tableName+N'] where SourceID = @SourceID';	
	
	exec sp_executesql @SQL,
	N'@SourceID int,@Rc int output',
	@SourceID,
	@Rc output;
	
	if @Rc % @PageSize > 0 
		set @PageCount = Cast(@Rc / @PageSize as int) + 1;
	else
		set @PageCount = Cast(@Rc / @PageSize as int);
		

	
	if @PageIndex = 1 
		begin
			set @SQL = N'select top '+Cast(@PageSize as nvarchar(30))+' '+@Fields + N' from dbo.['+@tableName+N'] where SourceID=@SourceID order by Lou desc';					
			exec sp_executesql @SQL,
			N'@SourceID int',
			@SourceID;
		end 		
	else
		begin
			declare @StartLou int;
			declare @EndLou int;
			
			--1 20  1 - 20,21- 40,41-60
			set @EndLou =  @Rc - (@PageIndex-1) * @PageSize;
			
			if @EndLou > @Rc 
				set @EndLou  = @Rc;			
			
			set @StartLou = @EndLou - @PageSize + 1;
			
			if @StartLou < 1 
				set @StartLou = 1;
			
			
			set @SQL = N'select '+@Fields + N' from dbo.['+@tableName+N'] where Lou>=@StartLou and Lou<=@EndLou and SourceID = @SourceID order by Lou desc';
			
			
			exec sp_executesql @SQL,
			N'@SourceID int,@StartLou int,@EndLou int',
			@SourceID,@StartLou,@EndLou;
		end
	
end




GO











--踩

Create proc [dbo].[procCai]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
	declare @tableName nvarchar(80);
	set @tableName = dbo.funGetTableName(@SourceID,@key);
	
	declare @SQL nvarchar(MAX);
	set @SQL = N'update dbo.['+@tableName+N'] set Cai=Cai+1 where ID=@ID;select @Times=Cai from dbo.['+@tableName+N'] where ID=@ID';
	
	
	exec sp_executesql @SQL,
	N'@ID int,@Times int output',
	@ID,
	@Times output;
end


GO


--顶
create proc [dbo].[procDing]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
	declare @tableName nvarchar(80);
	set @tableName = dbo.funGetTableName(@SourceID,@key);
	
	declare @SQL nvarchar(MAX);
	set @SQL = N'update dbo.['+@tableName+N'] set Ding=Ding+1 where ID=@ID;select @Times=Ding from dbo.['+@tableName+N'] where ID=@ID';
	
	
	exec sp_executesql @SQL,
	N'@ID int,@Times int output',
	@ID,
	@Times output;
end

GO

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

MySQL的位置：数据库和编程Apr 13, 2025 am 12:18 AM

MySQL在数据库和编程中的地位非常重要，它是一个开源的关系型数据库管理系统，广泛应用于各种应用场景。1）MySQL提供高效的数据存储、组织和检索功能，支持Web、移动和企业级系统。2）它使用客户端-服务器架构，支持多种存储引擎和索引优化。3）基本用法包括创建表和插入数据，高级用法涉及多表JOIN和复杂查询。4）常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5）性能优化方法包括合理使用索引、优化查询和使用缓存，最佳实践包括使用事务和PreparedStatemen

MySQL：从小型企业到大型企业Apr 13, 2025 am 12:17 AM

MySQL适合小型和大型企业。1)小型企业可使用MySQL进行基本数据管理，如存储客户信息。2)大型企业可利用MySQL处理海量数据和复杂业务逻辑，优化查询性能和事务处理。

幻影是什么读取的，InnoDB如何阻止它们（下一个键锁定）？Apr 13, 2025 am 12:16 AM

InnoDB通过Next-KeyLocking机制有效防止幻读。1）Next-KeyLocking结合行锁和间隙锁，锁定记录及其间隙，防止新记录插入。2）在实际应用中，通过优化查询和调整隔离级别，可以减少锁竞争，提高并发性能。

mysql：不是编程语言，而是...Apr 13, 2025 am 12:03 AM

MySQL不是一门编程语言，但其查询语言SQL具备编程语言的特性：1.SQL支持条件判断、循环和变量操作；2.通过存储过程、触发器和函数，用户可以在数据库中执行复杂逻辑操作。

MySQL：世界上最受欢迎的数据库的简介Apr 12, 2025 am 12:18 AM

MySQL是一种开源的关系型数据库管理系统，主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据，以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题，优化建议包括使用索引、优化查询和分表分区。

MySQL的重要性：数据存储和管理Apr 12, 2025 am 12:18 AM

MySQL是一个开源的关系型数据库管理系统，适用于数据存储、管理、查询和安全。1.它支持多种操作系统，广泛应用于Web应用等领域。2.通过客户端-服务器架构和不同存储引擎，MySQL高效处理数据。3.基本用法包括创建数据库和表，插入、查询和更新数据。4.高级用法涉及复杂查询和存储过程。5.常见错误可通过EXPLAIN语句调试。6.性能优化包括合理使用索引和优化查询语句。