MS-Sqlserver大表水平切割自定义方案

rgfsd2

概述：

    针对某些数据量较大的表，比如几千万的日志数据表，单从源表中查询所需数据（可能包含复杂查询），已经比较慢，所以可以将大表分为多个小表将数据进行水平切割，在此按日期进行切割，用到之前实现的复制库，所有操作在复制库中进行，比如登录日志数据表，每隔5天建立一张新登录表，名称使用LoginLog20150320样式，将5天前的数据导入新表，删除旧表5天前的数据

    STEP1：

        复制库中定义一张日志表，记录生成新表的信息，新建作业每天向新表中导入旧表中的数据，并且将旧表中的数据删除，确保单表的数据量不会过大。

1 2 3 4 5 6

CREATE TABLE [Z_LogCreateTable](     [ID] [int] IDENTITY(1,1) NOT NULL,     [STable] [nvarchar](50) NOT NULL,     [DTable] [nvarchar](50) NOT NULL,     [ExecTime] [date] 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 [dbo].[TB_PLogUsersLogin'+@extTableTag+'](             [ID] [int] NOT NULL,             [UserID] [int] NOT NULL,             [UserName] [nvarchar](50) NOT NULL,             [LoginIP] [nvarchar](50) NOT NULL,             [LoginDate] [datetime] NOT NULL,             [LoginPosition] [int] 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 每天将新生成的数据导入到新表，并在旧表中将新生成数据删除。应用程序根据时间间隔去不同的数据表中查询数据。

    复制库作为一个查询库存在，所以在源库中的历史数据也可以删除，扩大一点，可以将复制库单独作为一个查询系统存在，将源库上的大表查询去掉，减轻源库的查询负担。