MS-Sqlserver大表水平切割自定义方案
概述：针对某些数据量较大的表，比如几千万的日志数据表，单从源表中查询所需数据（可能包含复杂查询），已经比较慢，所以可以将大表分为多个小表将数据进行水平切割，在此按日期进行切割，用到之前实现的复制库，所有操作在复制库中进行，比如登录日志数据表，每隔5天建立一张新登录表，名称使用LoginLog20150320样式，将5天前的数据导入新表，删除旧表5天前的数据
STEP1：
复制库中定义一张日志表，记录生成新表的信息，新建作业每天向新表中导入旧表中的数据，并且将旧表中的数据删除，确保单表的数据量不会过大。
1
2
3
4
5
6
CREATE TABLE (
IDENTITY(1,1) NOT NULL,
(50) NOT NULL,
(50) NOT NULL,
NOT NULL
)
STEP2：
复制库中定义入口存储过程，Z_SP_PartitionTableAndData，判断当前执行时间与上次执行的时间差，如果大于等于5天则生成新表并导入数据，如小于5天则直接导入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
create proc Z_SP_PartitionTableAndData
as
begin
declare @STableTB_PLogUsersLogin nvarchar(50)
set @STableTB_PLogUsersLogin='TB_PLogUsersLogin'
declare @DTableTB_PLogUsersLogin nvarchar(50)
declare @lastExecTime date
select @lastExecTime=MAX(ExecTime) from Z_LogCreateTable
declare @dayInterval int
select @dayInterval=DATEDIFF(day,@lastExecTime,GETDATE())
if(@dayInterval>=5)
begin
--执行 创建表并且向新表中导入数据
declare @extTableTag nvarchar(10)
set @extTableTag=REPLACE(CAST(GETDATE() as DATE),'-','')
--建表并导入数据
EXEC Z_SP_CreateTableAndTransferData @extTableTag
end
else
begin
--执行 导入数据
declare @starttime date
declare @endtime date
select @DTableTB_PLogUsersLogin =DTable from Z_LogCreateTable where STable=@STableTB_PLogUsersLogin and ExecTime=@lastExecTime
set @starttime=CAST(GETDATE() as DATE)
set @endtime=CAST(Dateadd(day,1,GETDATE()) as DATE)
--导入数据
EXEC Z_SP_TransferData @DTableTB_PLogUsersLogin ,@starttime,@endtime,'LoginDate'
end
end
复制库中定义存储过程，Z_SP_CreateTableAndTransferData，动态创建表并导入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create proc Z_SP_CreateTableAndTransferData
(
@extTableTag nvarchar(10)
)
as
begin
declare @DTableTB_PLogUsersLogin nvarchar(50)
set @DTableTB_PLogUsersLogin='TB_PLogUsersLogin'+@extTableTag
declare @createtablesql nvarchar(max)
set @createtablesql='
CREATE TABLE .(
NOT NULL,
NOT NULL,
(50) NOT NULL,
(50) NOT NULL,
NOT NULL,
NOT NULL
）'
--生成新的数据表
exec(@createtablesql1);
declare @starttime date
declare @endtime date
set @starttime=CAST(GETDATE() as DATE)
set @endtime=CAST(Dateadd(day,1,GETDATE()) as DATE)
--向新表中写入数据
EXEC Z_SP_TransferData @DTableTB_PLogUsersLogin,@starttime,@endtime,'LoginDate'
insert into Z_LogCreateTable values('TB_PLogUsersLogin',@DTableTB_PLogUsersLogin,CAST(GETDATE() as date))
复制库中定义存储过程，Z_SP_TransferData，导入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create proc ZBzw_WSP_TransferData
(
@tablename nvarchar(50),
@starttime date,
@endtime date,
@timefieldname nvarchar(20)
)
as
begin
declare @insertdatasql nvarchar(max)
begin
set @insertdatasql='
insert into '+@tablename+'
select * from '+substring(@tablename,0,LEN(@tablename)-7)+' where '+@timefieldname+' between '''+cast(@starttime as nvarchar(10))+''' and '''+cast(@endtime as nvarchar(10))+''';
delete from '+substring(@tablename,0,LEN(@tablename)-7)+' where '+@timefieldname+' between '''+cast(@starttime as nvarchar(10))+''' and '''+cast(@endtime as nvarchar(10))+''';
'
end
EXEC(@insertdatasql)
end
STEP3：新建作业，步骤执行存储过程 Z_SP_PartitionTableAndData 每天将新生成的数据导入到新表，并在旧表中将新生成数据删除。应用程序根据时间间隔去不同的数据表中查询数据。
复制库作为一个查询库存在，所以在源库中的历史数据也可以删除，扩大一点，可以将复制库单独作为一个查询系统存在，将源库上的大表查询去掉，减轻源库的查询负担。
页:
[1]