MSSQL · 应用案例 · 日志表设计优化与实现

摘要

这篇文章从日志表问题引入、日志表的共有特性、日志表的设计需求、设计思路以及设计详细实现的角度，阐述了在SQL Server数据库中如何最优化设计日志表来降低系统资源的占用和提高系统吞吐量。

问题引入

在平时与客户服务与交流过程中，我们不止一次的被客人问及这样的场景：我们现在面临如何设计SQL Server日志表方案，如何最优化设计数据库日志记录表。因为，日志表设计会面对如下问题：

• 表记录数大：日志表由于记录了应用程序的很多操作日志，有的业务有很多步骤，甚至每个步骤操作都会被记录到日志表中，所以通常日志记录表都很大，表记录数据很多，表空间占用很大。
  
• 事务操作频繁：由于日志记录表写入（INSERT）操作非常频繁，加之表变得很大，通常的做法是会删除过期的日志信息（DELETE），所以事务操作非常频繁。
  
• 占用了昂贵的IOPS资源：日志表原本写入操作就非常频繁了，加之需要删除过时数据操作，这一写一删操作，使得事务量加倍，导致了数据库系统IOPS资源的过度被日志表的操作所占用了。
  
• 吞吐量上不去：日志表事务操作频繁，如果删除操作的事务没有控制好（比如：我见过很多客人一个DELETE语句下去删掉几十、几百万记录数的操作），很有可能会导致锁等待的发生（Blocking），从而影响应用的吞吐量和并发能能力。
  

日志表设计面临的这一系列问题给我们设计带来了不小的挑战，而我们今天这篇文章就是要解决日志表设计面临的这些问题。这篇文章将会分享SQL Server日志表设计的优化方案以及方案的实现细节，聪明的你甚至可以将这个设计方案推广到其他的关系型数据库。

日志表特性

首先，在分享设计方案之前，我们来看看关系型数据中的日志表，具有哪些共同的特性：

事务特性

日志表最为明显的特性是事务特性，或者称之为最重要的特性也不为过，即：写多读少，再准确一点说，是INSERT或者DELETE操作多；SELECT或者UPDATE操作少，这个很好理解。

• INSERT操作：INSERT操作是记录日志信息，当然是非常频繁且是少不了的，几乎是每时每刻都在发生。对系统吞吐量和并发要求高。
  
• UPDATE操作：日志表中记录一旦写入，几乎不会被修改，所以UPDATE操作应该非常少，更或者没有。
  
• SELECT操作：SELECT操作也不会太频繁，只会在排查问题，查询日志的时候使用，所以，概率也比较小。
  
• DELETE操作：而为了保证表记录数尽可能的少和查询操作（SELECT）的高效性，我们往往会定时清理过时的日志记录，所以DELETE操作相对还是很频繁的，和INSERT操作的频繁度是对等的。当然这里是可以通过设计来优化的，这也恰恰是这篇文章的中心思想。
  

设计特性

由于日志表的事务特性，所以，我们对日志表结构的设计就十分考究，比如：

• 主键设计：日志表因为几乎不会被UPDATE，数据都是追加写入，因此，主键最好选择是INT或者BIGINT数据类型的自增列（IDENTITY属性列），且做为CLUSTERED升序排列索引，即表按照主键列物理升序排列。这样设计的好处是，日志记录追加写入数据时，表不会被部分（或全部）重新排序，且几乎不会产生碎片。举一个反例，如果表主键列为UNIQUEIDENTIFIER数据类型，且值通常为默认值NEWID，那么当新追加的记录主键值小于之前的记录主键值时，会导致这一页的数据重排序，而且容易产生索引碎片。
  
• 索引设计适可而止：基于日志表事务特性的分析，我们很少在日志表上进行SELECT查询操作，所以，索引不宜过多，适可而止。理由是：索引过多，影响日志表INSERT操作的性能，因为，系统需要额外维护索引结构中数据和基表数据的一致性。按照我们的经验，通常，我们只需要在时间字段上建立索引即可，然后通过时间来过滤查询结果集。
  
• 索引填充因子设计：由于日志表数据记录按时间升序追加写入特性加之很少UPDATE操作，因此，索引数据页也具备了按时间升序追加写入的相似特性。所以，我们可以把索引的填充因子调高，可以设置为90 - 95都没有问题，换句话说，我们可以让索引数据页仅留下5% ~ 10%的剩余空间。
  
• 索引碎片维护设计：可能没有人会关心发生在两个月前的日志记录，因此，日志表记录会随着时间推移而删除过时的数据（DELETE）。这样也会导致索引碎片随着时间的推移变得越来越高，进而影响查询效率，消耗更多的系统IOPS资源。所以，我们需要定期维护（或重建或者重组）索引。当然，我们可以通过设计优化来避免DELETE操作，从而避免索引碎片的产生，也因此可以避免索引维护的成本。
  

重要性分析

日志表数据的最大功用是供我们排查应用系统问题时使用的，因此重要性相对于核心业务系统中的业务表数据，没有那么重要，所以我们会定期清理日志表过时数据。但是，它的确实实在在占用了我们昂贵的数据库系统资源。包括：存储开销、系统I/O开销、CPU开销、网络开销、连接开销以及系统吞吐量开销等。因此，针对日志表设计优化也显得尤为重要，这也是这篇文章分享的意义所在。

需求分析

基于以上“问题引入”和“日志表特性”章节的描述与分析，我们大致可以得出日志表最优化设计的需求与目标：

• 减少系统IOPS消耗：写入操作（INSERT）无法避免和减少了，我们可以考虑减少或者消除删除操作（DELETE）对IOPS的消耗。
  
• 增大日志表写入的吞吐量：要达到增大日志表写入吞吐量的目的，我们可以采用分区表的方式来解决，或者是分库分表。
  
• 减少索引碎片维护对系统性能消耗：索引维护的目的是为了减少索引的碎片率，提高I/O利用率，使得执行计划更加准确，查询高效。但是，这个操作也会带来数据库系统性能的大量开销，有时候可能会导致锁等待，甚至可能严重到死锁的地步。
  

设计思路

在讲解设计思路之前，我们需要先来看看SQL Server中删除表中所有数据的一个叫着TRUNCATE的语句。

TRUNCATE

TRUNCATE是SQL Server中一种删除表中所有数据的一种执行非常快速的方法，它仅仅只记录非常少的日志信息，哪怕表记录数达到千万级，亿级别，正常情况下同样可以秒级别执行成功。

使用方法

TRUNCATE使用方法非常简单，即：TRUNCATE TABLE后面跟表名字即可。

TRUNCATE TABLE dbo.tb_Table

注意： TRUNCATE TABLE非常简单高效的删除表中所有数据，但是有可能也非常危险，因为，你一旦使用了该方法清理了表中所有的数据，你很难将表中的数据在不使用备份集的情况下把数据找回来。因此，在使用这个操作之前请确保你十分清楚它的风险。

使用场景

在如下的场景中，我们可以考虑使用TRUNCATE操作：

• 重置表状态：当你使用TRUNCATE操作以后，表的所有数据会被清理，Identity属性列值会被重置为初始值。
  
• 快速清理表数据：你想要非常高效，快速清理大表数据，而又不希望导致长时间的锁等待或者死锁。
  
• 清理表数据又不想导致事务日志文件暴涨：常规的DELETE事务操作，在清理大表或者一次性操作大量数据时，会导致数据库的日志文件空间暴涨，而使用TRUNCATE操作因为仅会写入非常少的日志信息，因此不会导致事务日志空间暴涨。
  
• 删除表数据又不想触发表上的Trigger：删除表数据，但不希望触发表上删除操作的Trigger。
  

设计实现

基于以上的详细分析，我们对日志表的特定，需求有了比较明确的认知，接下来，让我们看看日志表的常规的设计和优化方案的设计，以及这些方案的优缺点。

常规设计

以下是关于日志表的常规设计，在我接触的客户场景中，几乎所有客人都是按照这样的思路和方法来实现日志表的，当然有可能表结构的设计还没有这般考究。

实现方法

首先，建立一个日志表，这里在主键、聚集索引、主键列设计上非常有讲究，我们选择BIGINT数据类型的IDENTITY属性列做为聚集索引升序排列的主键列，以最大限度的符合日志表的特性。详情参见下面代码中创建表部分的注释文字。

其次，建立日志表查询必须的索引，这里我们在时间字段上建立索引，以便后面的数据清理工作高效运行，这里我们将索引的FILLFACOTR设置为95。即，索引数据页预留5%的空间，以避免索引数据页中数据的移动。

接下来，实现清理过时数据的方法，这里使用一个存储过程来模拟。这里需要强调的是，请务必采用循环删除的方法，且每批次删除后，需要有一段时间的停顿，以释放进程获取到的资源，这里的资源包含但不仅限于锁资源、IOPS资源、CPU等，供其他的进程使用。我们在平时服务过程中，有的客人没有将日志表的清理过程采用批量删除的方式，有的是直接使用一个大事务删除所有过时数据，导致长时间锁住甚至无法写入。

日志表常规设计的实现方法和数据清理过程，参见下面的代码，请注意代码中的注释，非常重要。

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 

缺点分析

以上的日志表常规设计方法，对于日志量不大（每分钟在1000条记录以内）的场景当然可以应付自如。当随着日志的不断增长，可能会带来如下问题：

• 单表吞吐量上不去：单日志表，日志写入吞吐量始终是有极限的，当每分钟需要写入的日质量大于单表吞吐量时，瓶颈就出现了。
  
• DELETE操作消耗I/O资源：大量的日志操作过期时，会导致DELETE操作的频繁进行，会导致I/O资源的消耗，浪费数据库系统昂贵的I/O资源。
  
• DELETE操作无法按预期删除所有过时数据：因为我们每个删除批次之间有短暂停顿，当日志写入量非常巨大，甚至超过删除的速度时，会导致日志写入量大于删除量，数据清理工作无法按预期清理完毕，进而导致日志表数据不断累加，数据量越来越大。
  
• DELETE操作导致索引碎片：如此频繁的DELETE数据清理操作，会导致时间字段（Indate）上的索引碎片产生，影响查询和数据清理效率。
  
• 索引维护成本：为了解决时间字段Indate上的索引碎片产生带来的问题，我们必须对此索引进行维护重建或者重整工作，进一步导致I/O的消耗，严重者甚至导致锁等待或者死锁，进一步影响日志系统吞吐量。
  

为了解决日志表常规设计中的种种问题和缺点，我们利用TRUNCATE操作来优化设计表的设计和数据清理方案。

优化设计

我们通过“常规设计”中“缺点分析”部分，了解到了常规设计的众多缺点，优化设计的原则就是要来解决这些问题。假设我们的日志表数据记录按天为单位归档，建立分区表和分区视图，那么优化后的具体的设计思路是：

建立分区表

使用循环语句与动态SQL相配合的方式来一次性建立31张表，表的尾号预示写入数据的日期号数。分区表的设计需要：

• 分区字段为LogDay，表示Log写入的表编号，也即是写入的日期号数据。
  
• 分区字段上建立的Check约束是用来实现分区作用。
  
• 分区字段必须是主键的一部分，所以主键建立在RowId和LogDay的联合
  
• 请在Indate字段上建立索引，加快分区视图、分区表的查询效率，FILLFACTOR应当选择90以上，此处选择为95。
  

建立分区视图

为了操作日志表简单方便，实现一个分区视图用来直接操作数据，否则，我们的日志写入应用必须要先确定当前日志写入哪一张表，然后才能写入到对应的表中。使用分区视图的话，我们就可以一股脑儿的直接写入到分区视图中，不用事先区分写入的表。分区视图的实现方法也非常简单，只需要将所有的分区表UNION ALL起来，构成成一个分区视图即可。

注意： 分区视图中的分区表RowID不能为Identity属性列。

清理过时数据

使用TRUNCATE TABLE方法清理过时数据，仅会写非常少的事务日志信息，事务日志不会记录数据回滚所需要的所有信息，并且在很少概率情况下会发生锁等待，所以，该操作非常简单、快捷、高效而又节约系统资源。即使表数据记录数达到亿级甚至十亿百亿级，这个操作也会在秒级别完成。

实现代码

基于以上的实现方法分析，具体的实现代码如下，请注意参考代码中的注释。

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

最后总结

这篇文章从日志表特性、日志表设计思路、和两种日志表设计方案等方面分享了日志表的优化设计方案与具体实现。最优化的设计方案，减少了系统资源的占用的同时，还进一步提高了系统的吞吐量，非常有价值。