求助前辈高人，现在有这样一个问题，做一个登陆日志的方案，由于数据量会比较大，所以用分表来记录，一个用户当天登陆只记录一次，分几种来源，每种来源每天记录一次，一个表大概存储一千万数据，然后分二三十张表
目前写了一个存储过程来实现，但是这个过程中几乎都是动态的语句，包括创建表和记录日志
由于并发量不可预测，但以前最大达到过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
------解决思路----------------------
1、用每天的定时任务建表，就不会有多个会话建表冲突了。
2、索引常驻内存，碎片不用考虑。性能肯定有点影响，不过能提高查询效率还是值得的。
3、一个事务处理大量数据肯定有这个问题，所以要分批处理，每次事务大概1000条的量。