你的位置:首页 > 数据库

[数据库]导数中的最小化日志记录:背景和理论


什么是最小化日志(Minimal Logging)?
  当数据库的恢复模式为SIMPLE或者BULK_LOGGED时,对于最小化日志类型的操作,事务日志不记录单独每个数据行的日志,而是记录对应页和区结构的修改日志。
这样显著减少了操作产生的事务日志数量。例如,向某个数据页上插入200行数据,在最小化日志记录的情况下,只会记录一条此数据页变化的日志,而不是200条Insert日志。
 
最小化日志类型的操作
  • SELECT INTO 
  • Bulk导数操作,包括 BULK INSERT和BCP
  • INSERT INTO . . . SELECT,包括两种情况:
            a) SELECT中使用OPENROWSET(BULK. . .) 
            b)目标表不具有非聚集索引,向其插入超过8页的数据量,并且使用了TABLOCK时。如果目标表为空,可以有聚集索引,如果不为空,则不可以。
  • 部分更新大值类型的列
  • UPDATE中使用.WRITE插入数据或追加数据时
  • 对LOB字段使用WRITETEXT和UPDATETEXT插入或者追加新数据,不包括更新。
  • 索引操作,包括在表/视图上CREATE INDEX,ALTER INDEX REBUILD,DBCC DBREINDEX,DROP INDEX(新堆的重新生成将按最小方式记录)

 
数据导入中的最小化日志记录
  本文关注的是数据导入的最小化日志记录,指BULK INSERT导数操作。很多理论在其它类型的操作上是通用的。
  
1. 普通的INSERT
    SQL Server中使用锁和日志记录来保证数据库事务的ACID属性。在插入一行数据的整个事务期间,为了避免并发事务访问,这一行会被锁定;
同样这一行还会被写入日志记录。插入一行数据的大概的步骤如下:
  1. 通过行锁锁定行。
  2. 写入日志记录。日志记录包含被插入行的完整数据
  3. 数据行被写入数据页。

多行插入时,每一行都会重复以上步骤。这里指大概操作原型,实际处理复杂的多,如锁升级,约束检查等等
 
2. BULK导入
  当BULK导入提交事务时,事务使用到的所有数据页会被写入磁盘,这样来保证事务原子性。相当于每次提交事务时都做一次CHECKPOINT。如果需要回滚BULK事务,SQL Server会检索日志获取事务涉及的页或者区信息,然后将之重新标记为未使用。备份事务日志时会将BULK涉及的数据页和索引页都备份到日志备份中。还原包含BULK事务的日志备份时,不支持还原到指定时间点。
  每个数据文件第八个页是BCM页(BULK Chandged Map),之后每隔511230页会有一个BCM页。BCM上的每一位(Bit)代表着一个区,如果此位为1,则表示自上次BACKUP LOG后,这个区被BULK类型操作修改过。再下次日志备份时,会将这些被修改过的区复制到日志备份中。
 
3. 使用最小日志记录导入数据时需要满足的条件
  并不是任何情况下都可以实现最小日志导数,判断逻辑如下(来自Itzik Ben-Gan)
      a) SQL Server 2008之前的版本判断逻辑:
non-FULL recovery model
AND NOT replicated
AND TABLOCK
AND (
               Heap
               OR (B-tree AND empty)
       )
 
      b) SQL Server 2008及以后版本的判断逻辑:
Non-FULL recovery model
AND NOT replicated
AND (
          (Heap AND TABLOCK)
          OR (B-tree AND empty AND TABLOCK)
          OR (B-tree AND empty AND TF-610)
          OR (B-tree AND nonempty AND TF-610 AND key-range)
  从SQL 2008开始可以使用跟踪标记610和排它键范围锁,实现空/非空聚集索引表的最小化日志操作。
  排他键范围锁的作用例子:聚集索引表tb(id INT),目前有4行数据,分别为1,1000,2000,3000。现在需要向表中插入500行数据,这些数据的值区间为[1001,1500]。
当插入时,SQL Server不需要获取聚集索引整体的排它锁(像tablock这种),而只是获取原有键值区间的排它键范围锁。这里就是在(1000,2000)区间上获取X KEY-RANGE LOCK。而不在这个区间的数据,仍然可以被其它进程访问。如果要实现非空索引表的最小化日志记录导数,需要预先将导入数据按目标表的索引键值列进行排序,并启用跟踪标记610。
  从上面的判断逻辑可以看出,实现最小日志记录的大前提是:数据库不是完整恢复模式且表没有标记为复制。对于堆表总是需要使用TABLOCK。对于索引表,则要分为空表和非空表两种情况来处理。这部分内容在后文的例子再展开来说明。
 
观察BULK导入的日志
  使用未公开的系统函数sys.fn_dblog查找相关的日志内容。fn_dblog接受两个参数用以指定要查询的日志区间,分别表示开始和结束的LSN。输出字段中,此文需要关注的是Operation, Context, Log Record Length和AllocUnitName。因为是未公开的的函数,所以输出内容代表的意义,需要结合个人经验和大家的“共识”来解读。
  • Operation(LOP):表示执行何种日志操作, 例如修改行为LOP_MODIFY_ROW,设置位图页时为LOP_SET_BITS等等。
  • Context(LCX):日志操作的上下文,一般表示受影响的对象类型。例如LCX_GAM,LCX_HEAP,LCX_PFS等。
  • Log Record Length:以byte为单位的日志长度
  • AllocUnitName:表示受影响的具体对象

使用如下脚本进行分析,脚本来自Jakub K 
-- 日志条目录数据和总大小SELECT COUNT(*)AS numrecords, CAST((COALESCE(SUM([Log Record LENGTH]), 0))  / 1024. / 1024. AS NUMERIC(12, 2)) AS size_mbFROM sys.fn_dblog(NULL, NULL) AS DWHERE AllocUnitName = 'dbo.tableName' OR AllocUnitName LIKE 'dbo.tableName.%'; -- 各类型日志的平均长度和数量SELECT Operation, Context, AVG([Log Record LENGTH]) AS AvgLen, COUNT(*) AS CntFROM sys.fn_dblog(NULL, NULL) AS DWHERE AllocUnitName = 'dbo.destination_pk' OR AllocUnitName LIKE 'dbo.destination_pk.%'GROUP BY Operation, Context, ROUND([Log Record LENGTH], -2)ORDER BY AvgLen, Operation, Context;