求助版大大，分表存储过程记录登陆日志，高并发下会有些什么情况发生？

chen357313771 2014-12-16 05:33:41
求助前辈高人，现在有这样一个问题，做一个登陆日志的方案，由于数据量会比较大，所以用分表来记录，一个用户当天登陆只记录一次，分几种来源，每种来源每天记录一次，一个表大概存储一千万数据，然后分二三十张表
目前写了一个存储过程来实现，但是这个过程中几乎都是动态的语句，包括创建表和记录日志
由于并发量不可预测，但以前最大达到过20W，所以就以最大值20W做预案
问题：
1、高并发的情况下，会不会乱（说的浅薄点，比如这边也在创建表，那边也用这个过程在建表，那不是就乱套了？）
2、建表的时候就建好索引，这样的意义有多大，由于不停的插入会不会产生大量的索引碎片之类的影响空间和性能
3、开启事务，只要出错，全部回滚这样并发时候回滚也会占用很多资源是否有问题
..........
存储过程
ALTER PROCEDURE [dbo].[up_CreateAndRecordLoginInTheDays]

	@DBPrefix			VARCHAR(100)

	,@ConfigTableSuffix VARCHAR(100)

	,@Mobile			BIGINT

	,@Source			INT

	--,@InDateTime		DATETIME

	,@TableMaxCount		INT

AS 

SET XACT_ABORT ON   

SET NOCOUNT ON

--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN

	BEGIN TRY

		BEGIN TRANSACTION

		DECLARE @CurrentConfige TABLE(Id INT 

							,LogTableName VARCHAR(200)

							,MaxCount	INT

							,CreateDateTime DATETIME

							,CreateShortDate INT

							,EndDateTime DATETIME

							,EndShortDate INT)

		DECLARE @CurrentTableCount TABLE(TotalCount int) --当前表总数

		DECLARE @ExistsThisDate TABLE(ISCheck int)		--当前表是否有该数据									

		DECLARE @TableConfige VARCHAR(100)

				,@CurrentTable VARCHAR(100)

				,@Sql			NVARCHAR(Max)

				,@DateTime		DATETIME

				,@ShortDate		VARCHAR(10)

		SELECT  @TableConfige=@DBPrefix+@ConfigTableSuffix

				,@DateTime=GETDATE()

				,@ShortDate=CONVERT(VARCHAR(10),GETDATE(),112)

		

		--判断config表不存在则创建

		IF NOT EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(@TableConfige) AND type='U')

			BEGIN

			SELECT @Sql='CREATE TABLE '+@TableConfige+'

						(

							Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL

							,LogTableName VARCHAR(200)

							,MaxCount	INT

							,CreateDateTime DATETIME

							,CreateShortDate INT

							,EndDateTime DATETIME

							,EndShortDate INT

						)'

				EXEC(@Sql)

				

			END

		

		--存在config,取最后一张表插入

		INSERT INTO @CurrentConfige

		EXEC('SELECT TOP 1 * FROM '+@TableConfige+' ORDER BY Id DESC')

		

		--如果不存在日志表

		IF NOT EXISTS(SELECT 1 FROM @CurrentConfige)

			BEGIN

					--插入第一张表名

				SELECT @Sql='INSERT INTO '+@TableConfige+'(LogTableName,MaxCount,CreateDateTime,CreateShortDate)

								SELECT '''+@DBPrefix+'''+''LoginInTheDays''+RIGHT(10001+ISNULL(RIGHT(MAX(LogTableName),4),0),4)

									,0

									,GETDATE()

									,CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS INT)

								FROM '+@TableConfige

				EXECUTE(@Sql)

				

				--存在config,取最后一张表插入

				INSERT INTO @CurrentConfige

				EXEC('SELECT TOP 1 * FROM '+@TableConfige+' ORDER BY Id DESC')

				

			END 

			



		SELECT @CurrentTable =LogTableName from @CurrentConfige

		

		IF NOT EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(@CurrentTable) AND type='U')

			BEGIN

				SELECT @Sql='CREATE TABLE '+@CurrentTable+'

								(

									[Id] [int] NOT NULL IDENTITY(1,1),

									[Mobile] [bigint] NOT NULL,

									[LoginShortDate] [int] NOT NULL,

									[LoginDateTime] [datetime] NOT NULL CONSTRAINT [DF_'+@CurrentTable+'_LoginDateTime] DEFAULT (getdate()),

									[Source] [int] NOT NULL CONSTRAINT [DF_'+@CurrentTable+'_Source] DEFAULT ((2))

								) ON [PRIMARY]

								ALTER TABLE '+@CurrentTable+' ADD CONSTRAINT [PK_'+@CurrentTable+'] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

								CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@CurrentTable+'_Mobile_LoginShortDate_Source] ON '+@CurrentTable+' ([Mobile], [LoginShortDate], [Source]) ON [PRIMARY]

								'

				EXEC(@Sql)

			END

			

		SELECT @Sql='SELECT TOP 1 1 FROM '+@CurrentTable

					+' WITH(NOLOCK) WHERE Mobile='+CAST(@Mobile AS varchar(400))

					+' AND LoginShortDate='

					+@ShortDate+' AND Source='+CAST(@Source AS VARCHAR(400))

			

		INSERT INTO @ExistsThisDate 

		EXEC(@Sql)

		

		SELECT @Sql='SELECT MAX(Id) FROM '+@CurrentTable+' WITH(NOLOCK)'

		INSERT INTO @CurrentTableCount

		EXEC(@Sql)

		

		DECLARE @CurrentTotalCount INT

		SELECT @CurrentTotalCount= ISNULL(TotalCount,0) FROM @CurrentTableCount

		

		IF (@TableMaxCount>@CurrentTotalCount) --表实际count小于配置数量

			BEGIN

				IF NOT EXISTS(SELECT 1 FROM @ExistsThisDate)

				  BEGIN

					 SELECT @Sql='INSERT INTO '+@CurrentTable+'(Mobile,LoginShortDate,LoginDateTime,Source)

									VALUES('+CAST(@Mobile AS Nvarchar(400))+','+@ShortDate+',GETDATE(),'+CAST(@Source AS NVARCHAR(400))+')'

						EXEC(@Sql)	

						SELECT 1

				  END

				ELSE

				  BEGIN

					SELECT 0

				  END

			END 

			ELSE

				BEGIN

				

					SELECT @Sql='UPDATE '+@TableConfige

										+' SET MaxCount='+CAST(@CurrentTotalCount AS NVARCHAR(200))+'

											, EndDateTime=GETDATE()

											, EndShortDate=CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS INT)

										WHERE LogTableName='+''''+@CurrentTable+''''

					

					EXEC(@Sql)

					

					

							

					--创建新表后,@ExistsThisDate 是上一张表的验证,@CurrentTable是新创建的表

					IF NOT EXISTS(SELECT 1 FROM @ExistsThisDate)

					  BEGIN

						SELECT @Sql='INSERT INTO '+@TableConfige+'(LogTableName,MaxCount,CreateDateTime,CreateShortDate)

								SELECT '''+@DBPrefix+'''+''LoginInTheDays''+RIGHT(10001+ISNULL(RIGHT(MAX(LogTableName),4),0),4)

									,0

									,GETDATE()

									,CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS INT)

								FROM '+@TableConfige

						

						EXECUTE(@Sql)

						

						INSERT INTO @CurrentConfige

						EXEC('SELECT TOP 1 * FROM '+@TableConfige+' ORDER BY Id DESC')

						

						SELECT @CurrentTable =LogTableName from @CurrentConfige

					

						IF NOT EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(@CurrentTable) AND type='U')

							BEGIN

								SELECT @Sql='CREATE TABLE '+@CurrentTable+'

												(

													[Id] [int] NOT NULL IDENTITY(1,1),

													[Mobile] [bigint] NOT NULL,

													[LoginShortDate] [int] NOT NULL,

													[LoginDateTime] [datetime] NOT NULL CONSTRAINT [DF_'+@CurrentTable+'_LoginDateTime] DEFAULT (getdate()),

													[Source] [int] NOT NULL CONSTRAINT [DF_'+@CurrentTable+'_Source] DEFAULT ((2))

												) ON [PRIMARY]

												ALTER TABLE '+@CurrentTable+' ADD CONSTRAINT [PK_'+@CurrentTable+'] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]

												CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@CurrentTable+'_Mobile_LoginShortDate_Source] ON '+@CurrentTable+' ([Mobile], [LoginShortDate], [Source]) ON [PRIMARY]

												'

								EXEC(@Sql)

							END

						  

						 SELECT @Sql='INSERT INTO '+@CurrentTable+'(Mobile,LoginShortDate,LoginDateTime,Source)

										VALUES('+CAST(@Mobile AS Nvarchar(400))+','+@ShortDate+',GETDATE(),'+CAST(@Source AS NVARCHAR(400))+')'

										

							EXEC(@Sql)	

							

							SELECT 1

					  END

					ELSE

					  BEGIN

						SELECT 0

					  END

					

				END

		

		COMMIT TRANSACTION

	END TRY

	BEGIN CATCH

		ROLLBACK TRANSACTION

		--SELECT ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_LINE()

		SELECT 0

	END CATCH

END
...全文
278 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
chen357313771 2014-12-22
  • 打赏
  • 举报
 回复
引用 6 楼 Tiger_Zhao 的回复:
1、用每天的定时任务建表，就不会有多个会话建表冲突了。 2、索引常驻内存，碎片不用考虑。性能肯定有点影响，不过能提高查询效率还是值得的。 3、一个事务处理大量数据肯定有这个问题，所以要分批处理，每次事务大概1000条的量。
谢谢您的回答
chen357313771 2014-12-22
  • 打赏
  • 举报
 回复
引用 7 楼 yupeigu 的回复:
[quote=引用 4 楼 chen357313771 的回复:] [quote=引用 2 楼 yupeigu 的回复:] 看上去很复杂啊。 我觉得似乎没必要搞这么复杂，分二三十个表。 因为就算你的记录数很大，那么也只只需要一个表来实现，随着时间的推移，数据量会越来越大，那么通过按照时间字段进行分区就可以了。 另外，毕竟是日志表，通过分区表，可以把一段时间的数据，进行历史归档，放到历史表中，这样再次查询当前表，记录数就没有那么大了。 同时，很重要的时，简化你上面的 存储过程，看着真的很复杂，一堆动态创建语句，现在只需要一个表，上面的语句可以大大简化。
项目组出的方案，就是要分表，因为项目中有地方需要用到这些东西，而且比较紧急，我想知道的是那三个问题[/quote] 1、高并发的情况下，多个会话在建表之前，做一个判断，判断表是否已经创建，就不会混乱了。 2、建表的时候就建好索引，虽然会不断插入，但插入本身并不会引起碎片的激增，而是索引本身会引起插入效率的降低， 如果这个表除了插入，还有大量的update或者是delete，倒是很容易引起碎片问题的。 3、开启事务，只要出错，全部回滚，这个要看你的业务了，如果你的业务就是这样的，那么必须要回滚的，回滚肯定会消耗一定的资源，不过如果这个存储过程运行时间较短，那么回滚的开销也是相对较小的。 虽然，我觉得这个方案，对于这个日志处理来说，效率比较低，但是我知道，作为公司的一个小兵，你也没办法，你也必须要听领导的，既然他们已经决定要这么做了，那你也只能照做。 [/quote]好的，我知道怎么做了，谢谢了
LongRui888 2014-12-21
  • 打赏
  • 举报
 回复
引用 4 楼 chen357313771 的回复:
[quote=引用 2 楼 yupeigu 的回复:] 看上去很复杂啊。 我觉得似乎没必要搞这么复杂，分二三十个表。 因为就算你的记录数很大，那么也只只需要一个表来实现，随着时间的推移，数据量会越来越大，那么通过按照时间字段进行分区就可以了。 另外，毕竟是日志表，通过分区表，可以把一段时间的数据，进行历史归档，放到历史表中，这样再次查询当前表，记录数就没有那么大了。 同时，很重要的时，简化你上面的 存储过程，看着真的很复杂，一堆动态创建语句，现在只需要一个表，上面的语句可以大大简化。
项目组出的方案，就是要分表，因为项目中有地方需要用到这些东西，而且比较紧急，我想知道的是那三个问题[/quote] 1、高并发的情况下，多个会话在建表之前，做一个判断，判断表是否已经创建，就不会混乱了。 2、建表的时候就建好索引，虽然会不断插入，但插入本身并不会引起碎片的激增，而是索引本身会引起插入效率的降低， 如果这个表除了插入，还有大量的update或者是delete，倒是很容易引起碎片问题的。 3、开启事务，只要出错，全部回滚，这个要看你的业务了，如果你的业务就是这样的，那么必须要回滚的，回滚肯定会消耗一定的资源，不过如果这个存储过程运行时间较短，那么回滚的开销也是相对较小的。 虽然，我觉得这个方案，对于这个日志处理来说，效率比较低，但是我知道，作为公司的一个小兵，你也没办法，你也必须要听领导的，既然他们已经决定要这么做了，那你也只能照做。
Tiger_Zhao 2014-12-19
  • 打赏
  • 举报
 回复
1、用每天的定时任务建表，就不会有多个会话建表冲突了。
2、索引常驻内存，碎片不用考虑。性能肯定有点影响，不过能提高查询效率还是值得的。
3、一个事务处理大量数据肯定有这个问题，所以要分批处理，每次事务大概1000条的量。
chen357313771 2014-12-19
  • 打赏
  • 举报
 回复
引用 3 楼 Tiger_Zhao 的回复:
登陆日志属于非常低效、非常庞大的原始数据，应该定时（每天）按使用需要汇总成中间结果，并且清理过期历史。 留太多日志没有意义，只要定时清理，一个表足够用了。
项目组出的方案，就是要分表，因为项目中有地方需要用到这些东西，而且比较紧急，我想知道的是那三个问题
chen357313771 2014-12-19
  • 打赏
  • 举报
 回复
引用 2 楼 yupeigu 的回复:
看上去很复杂啊。 我觉得似乎没必要搞这么复杂，分二三十个表。 因为就算你的记录数很大，那么也只只需要一个表来实现，随着时间的推移，数据量会越来越大，那么通过按照时间字段进行分区就可以了。 另外，毕竟是日志表，通过分区表，可以把一段时间的数据，进行历史归档，放到历史表中，这样再次查询当前表，记录数就没有那么大了。 同时，很重要的时，简化你上面的 存储过程，看着真的很复杂，一堆动态创建语句，现在只需要一个表，上面的语句可以大大简化。
项目组出的方案，就是要分表，因为项目中有地方需要用到这些东西，而且比较紧急，我想知道的是那三个问题
chen357313771 2014-12-17
  • 打赏
  • 举报
 回复
自己顶一下！
Tiger_Zhao 2014-12-17
  • 打赏
  • 举报
 回复
登陆日志属于非常低效、非常庞大的原始数据，应该定时（每天）按使用需要汇总成中间结果，并且清理过期历史。
留太多日志没有意义，只要定时清理，一个表足够用了。
LongRui888 2014-12-17
  • 打赏
  • 举报
 回复
看上去很复杂啊。 我觉得似乎没必要搞这么复杂，分二三十个表。 因为就算你的记录数很大，那么也只只需要一个表来实现，随着时间的推移，数据量会越来越大，那么通过按照时间字段进行分区就可以了。 另外，毕竟是日志表，通过分区表，可以把一段时间的数据，进行历史归档，放到历史表中，这样再次查询当前表，记录数就没有那么大了。 同时，很重要的时，简化你上面的 存储过程，看着真的很复杂，一堆动态创建语句，现在只需要一个表，上面的语句可以大大简化。
数据库分库分表详解和案例实战
本课程深入MySQL 分库分表各个环节，2023年最新课程。每个章节都会以原理深入+实际案例进行讲解。内容数据库安装，主从同步，和分表分表详解和对应的应用场景及注意事项 MySQL在金融互联网行业的企业级安装部署 1 ...
不懂什么是高并发？看完这篇文章你可以去吊打面试官了
如果候选人做过高并发的项目，我通常会让对方谈谈对于高并发的理解，但是能系统性地回答好此问题的人并不多，大概分成这样几类：1、对数据化的指标没有概念：不清楚选择什么样的指标来衡量高并发系统？
这四种情况下，才是考虑分库分表的时候！
在业务service来看，就是可用数据库连接少甚至无连接可用，接下来就可以想象了（并发量、吞吐量、崩溃）。IO瓶颈第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询会产生大量的IO，降低查询速度->...
高并发情况下，雪花ID一秒400W个，以及分布式ID算法（详析）
最近在研究雪花算法，在研究同时，想了一个问题，在高并发的情况下，一秒内，雪花算法能生成多少个ID。 闲话少说，开撸。 https://blog.csdn.net/weixin_43876121/article/details/107744260?...
大型互联网高可用&高并发业务架构设计（2）
高并发业务架构设计高并发业务架构设计实践软件架构中的高并发思考什么是高并发？高并发下系统设计问题的思考如何保障系统的高并发？数据层优化应用层优化前端优化多级缓存应用连接池详解线程池详解Java线程池Tomcat...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章