数据库内核月报 － 2017 / 09

TRUNCATE TABLE dbo.tb_Table

USE master
GO
-- 创建测试数据库
IF DB_ID('TestDb') IS NULL
	CREATE DATABASE [TestDb];
GO

USE [TestDb]
GO

-- 创建测试表
IF OBJECT_ID('dbo.tb_ApplicationLogs', 'U') IS NOT NULL
	DROP TABLE dbo.tb_ApplicationLogs
GO

/*
创建表的时候，需要特别注意以下几点：
1、RowID被设计为IDENTITY属性，以便日志表追加写入
2、RowID做为表CLUSTERED（聚集）类型主键，且升序排列
3、Indate表示记录进入表中的时间，采用GETDATE做为默认值
4、需要在Indate字段上建立索引，以提高查询和清理数据效率
*/
CREATE TABLE dbo.tb_ApplicationLogs(
	RowID BIGINT IDENTITY(1,1) NOT NULL,
	AppName SYSNAME NOT NULL,
	HostName SYSNAME NOT NULL,
	Indate DATETIME NOT NULL 
		CONSTRAINT DF_Indate DEFAULT(GETDATE()),
	LogInfo NVARCHAR(2000) NULL,
		CONSTRAINT PK_tb_ApplicationLogs_RowID 
		PRIMARY KEY CLUSTERED (RowID ASC)
);


--创建时间字段上的索引，以加快数据清理和查询效率
CREATE NONCLUSTERED INDEX IX_Indate
ON dbo.tb_ApplicationLogs(Indate) WITH(FILLFACTOR = 95)
;
GO


IF OBJECT_ID('dbo.Up_CleanAppLogs', 'P') IS NOT NULL
	DROP PROC dbo.Up_CleanAppLogs
GO
/*
Function：
这个存储过程用来模拟清理dbo.tb_ApplicationLogs日志表中的数据，
采用循环删除超过@days_before天的数据。

参数含义：
@days_before：超过这个参数值天数的数据将会被删除掉。
@batch_Size: 每个批次删除的记录条数

注意：
1、在计算时间的地方，需要使用DATEADD(day, -ABS(@days_before), GETDATE())方法，
这里的取绝对值非常重要，否则当传入一个负数时，表中的所有数据都会被删除。
2、为了避免一个大的事务，一定是采用循环删除的方式，这里一次性删除万条
3、每个删除万条数据的批次之间，要有一段时间的停顿，释放资源供其他进程使用。

*/

CREATE PROC dbo.Up_CleanAppLogs(
	@days_before INT = 7,
	@batch_Size INT = 10000
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE
		@dt DATETIME
		,@row_affected INT
		,@row_removed_total INT
	;
	SELECT
		@dt = DATEADD(day, -ABS(@days_before), GETDATE())
		,@row_removed_total = 0
	;
	
	WHILE EXISTS(
				SELECT TOP 1 1
				FROM dbo.tb_ApplicationLogs WITH(NOLOCK)
				WHERE Indate <= @dt)
	BEGIN
		DELETE TOP(@batch_Size) A
		FROM dbo.tb_ApplicationLogs AS A
		WHERE Indate <= @dt	--请注意，这个条件非常重要，否则有可能会导致当前数据被误删除
		
		SELECT 
			@row_affected = @@ROWCOUNT
			,@row_removed_total = @row_removed_total + @row_affected
		;
		
		RAISERROR('--%d rows removed, totaly %d rows cleared and waiting for next batch', 10, 1, @row_affected, @row_removed_total) WITH NOWAIT
		WAITFOR DELAY '00:00:02'
	END	
END
GO

USE master
GO

-- 创建测试数据库
IF DB_ID('TestDb') IS NULL
	CREATE DATABASE [TestDb];
GO

USE [TestDb]
GO

-- 删除表之前，需要先删除分区视图
IF OBJECT_ID('dbo.V_ApplicationAllLogs', 'V') IS NOT NULL
	DROP VIEW dbo.V_ApplicationAllLogs
GO

/**
-- 循环创建张表，每张表以数字尾号结尾，比如：XXX_01,XXX_02,...,XXX_31
-- 这里需要注意，为了方便数据写入直接使用分区视图，表有如下限制：
1. 分区字段为logDay，表示Log写入的表编号
2. 分区字段上建立Check约束来实现分区
3. 分区字段必须是主键的一部分，所以主键建立在RowId和LogDay的联合
4. 为了使用分区视图直接操作数据，RowID不能为Identity属性列

请在Indate字段上建立索引，FILLFACTOR选择以上。
*/
DECLARE
	@drop_sql NVARCHAR(MAX)
	,@create_sql NVARCHAR(MAX)
	,@do INT = 1
	,@loop INT = 31
	,@postfix CHAR(2)
;

WHILE @do <= @loop
BEGIN
	SELECT
		@postfix = RIGHT(CAST(@do+100 AS VARCHAR(3)), 2)
		,@drop_sql = N'
	IF OBJECT_ID(''dbo.tb_ApplicationLogs_' + @postfix + N''', ''U'') IS NOT NULL
			DROP TABLE dbo.tb_ApplicationLogs_' + @postfix
		,@create_sql = N'
	CREATE TABLE dbo.tb_ApplicationLogs_' + @postfix +N'(
		RowID BIGINT NOT NULL,
		AppName SYSNAME NOT NULL,
		HostName SYSNAME NOT NULL,
		LogDay INT,
			CONSTRAINT CHK_tb_ApplicationLogs_' + @postfix +N'_LogDay CHECK(LogDay= ' + CAST(@do AS NVARCHAR(2)) + N'),
		Indate DATETIME NOT NULL,
		LogInfo NVARCHAR(2000) NULL,
			CONSTRAINT PK_tb_ApplicationLogs_' + @postfix + N'_RowID 
			PRIMARY KEY CLUSTERED (RowID ASC,LogDay ASC)
	);
	CREATE NONCLUSTERED INDEX IX_Indate
	ON dbo.tb_ApplicationLogs_' + @postfix + N'(Indate) WITH(FILLFACTOR = 95)
	;
	'
	
	EXEC sys.sp_executesql @drop_sql

	--print @create_sql
	EXEC sys.sp_executesql @create_sql
	
	SET @do = @do + 1;
END
GO


USE [TestDb]
GO

IF OBJECT_ID('dbo.V_ApplicationAllLogs', 'V') IS NOT NULL
	DROP VIEW dbo.V_ApplicationAllLogs
GO

/*
-- 将所有的分区表UNION ALL起来，构成分区视图
供我们查询、数据写入和数据更新
*/
CREATE VIEW dbo.V_ApplicationAllLogs
AS
SELECT * FROM dbo.tb_ApplicationLogs_01 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_02 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_03 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_04 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_05 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_06 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_07 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_08 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_09 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_10 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_11 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_12 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_13 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_14 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_15 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_16 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_17 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_18 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_19 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_20 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_21 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_22 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_23 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_24 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_25 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_26 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_27 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_28 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_29 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_30 WITH(NOLOCK)
UNION ALL
SELECT * FROM dbo.tb_ApplicationLogs_31 WITH(NOLOCK)
GO


USE [TestDb]
GO

-- 这里模拟应用程序写入日志信息
;WITH a 
AS (
	SELECT * 
	FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(100)
	RowId = ROW_NUMBER() OVER (ORDER BY a.a)
	,AppName = NEWID()
	,HostName = NEWID()
	,Indate = GETDATE() - 8
	,LogInfo = NEWID()
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
)
INSERT INTO dbo.V_ApplicationAllLogs(RowId, AppName, LogDay, HostName,Indate, LogInfo)
SELECT
	RowId 
	,AppName
	,LogDay = RowId % 31 + 1
	,HostName
	,Indate = CAST('2017-08-1 22:54:39.873' AS DATETIME)+ RowId % 31
	,LogInfo
FROM RoundData;
GO

-- 从分区视图中查询日志信息
SELECT * FROM dbo.V_ApplicationAllLogs WITH(NOLOCK)
WHERE Indate >='2017-08-1' AND Indate <= '2017-08-3'



IF OBJECT_ID('dbo.Up_CleanAppLogs', 'P') IS NOT NULL
	DROP PROC dbo.Up_CleanAppLogs
GO

USE [TestDb]
GO
/*
Function：
这个存储过程用来模拟清理dbo.tb_ApplicationLogs日志表中的数据，
采用TRUNATE TABLE的方式清理超过@days_before天的数据，非常迅速。

参数含义：
@days_before：超过这个参数值天数的数据将会被直接TRUNCATE删除掉。


注意：
1、在计算时间的地方，需要使用DATEADD(day, -ABS(@days_before), GETDATE())方法，
这里的取绝对值非常重要，否则当传入一个负数时，表中的所有数据都会被删除。
2、这个存储过程只需要每天执行一次就好了
*/

CREATE PROC dbo.Up_CleanAppLogs(
	@days_before INT = 7
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE
		@log_postfix INT
		,@sql NVARCHAR(MAX)
	;
	
	SELECT
		@log_postfix = DATEPART(dd, DATEADD(day, -ABS(@days_before), GETDATE()))
		,@sql = N'TRUNCATE TABLE dbo.tb_ApplicationLogs_' + RIGHT(CAST(@log_postfix+100 AS VARCHAR(3)), 2)
	;
	
	EXEC sys.sp_executesql @sql
	RAISERROR('Executing sql: %s', 10, 1, @sql) WITH NOWAIT
END;
GO