導(dǎo)數(shù)中的最小化日志記錄：背景和理論_MySQL

當(dāng)數(shù)據(jù)庫(kù)的恢復(fù)模式為SIMPLE或者BULK_LOGGED時(shí)，對(duì)于最小化日志類型的操作，事務(wù)日志不記錄單獨(dú)每個(gè)數(shù)據(jù)行的日志，而是記錄對(duì)應(yīng)頁(yè)和區(qū)結(jié)構(gòu)的修改日志。

這樣顯著減少了操作產(chǎn)生的事務(wù)日志數(shù)量。例如，向某個(gè)數(shù)據(jù)頁(yè)上插入200行數(shù)據(jù)，在最小化日志記錄的情況下，只會(huì)記錄一條此數(shù)據(jù)頁(yè)變化的日志，而不是200條Insert日志。

最小化日志類型的操作

SELECT INTO

Bulk導(dǎo)數(shù)操作，包括 BULK INSERT和BCP

INSERT INTO . . . SELECT，包括兩種情況：

a) SELECT中使用OPENROWSET(BULK. . .)

b)目標(biāo)表不具有非聚集索引，向其插入超過8頁(yè)的數(shù)據(jù)量，并且使用了TABLOCK時(shí)。如果目標(biāo)表為空，可以有聚集索引，如果不為空，則不可以。

部分更新大值類型的列

UPDATE中使用.WRITE插入數(shù)據(jù)或追加數(shù)據(jù)時(shí)

對(duì)LOB字段使用WRITETEXT和UPDATETEXT插入或者追加新數(shù)據(jù)，不包括更新。

索引操作，包括在表/視圖上CREATE INDEX，ALTER INDEX REBUILD，DBCC DBREINDEX，DROP INDEX(新堆的重新生成將按最小方式記錄)

數(shù)據(jù)導(dǎo)入中的最小化日志記錄

本文關(guān)注的是數(shù)據(jù)導(dǎo)入的最小化日志記錄，指BULK INSERT導(dǎo)數(shù)操作。很多理論在其它類型的操作上是通用的。

1. 普通的INSERT

SQL Server中使用鎖和日志記錄來保證數(shù)據(jù)庫(kù)事務(wù)的ACID屬性。在插入一行數(shù)據(jù)的整個(gè)事務(wù)期間，為了避免并發(fā)事務(wù)訪問，這一行會(huì)被鎖定;

同樣這一行還會(huì)被寫入日志記錄。插入一行數(shù)據(jù)的大概的步驟如下：

通過行鎖鎖定行。

寫入日志記錄。日志記錄包含被插入行的完整數(shù)據(jù)。

數(shù)據(jù)行被寫入數(shù)據(jù)頁(yè)。

多行插入時(shí)，每一行都會(huì)重復(fù)以上步驟。這里指大概操作原型，實(shí)際處理復(fù)雜的多，如鎖升級(jí)，約束檢查等等

2. BULK導(dǎo)入

當(dāng)BULK導(dǎo)入提交事務(wù)時(shí)，事務(wù)使用到的所有數(shù)據(jù)頁(yè)會(huì)被寫入磁盤，這樣來保證事務(wù)原子性。相當(dāng)于每次提交事務(wù)時(shí)都做一次CHECKPOINT。如果需要回滾BULK事務(wù)，SQL Server會(huì)檢索日志獲取事務(wù)涉及的頁(yè)或者區(qū)信息，然后將之重新標(biāo)記為未使用。備份事務(wù)日志時(shí)會(huì)將BULK涉及的數(shù)據(jù)頁(yè)和索引頁(yè)都備份到日志備份中。還原包含BULK事務(wù)的日志備份時(shí)，不支持還原到指定時(shí)間點(diǎn)。

每個(gè)數(shù)據(jù)文件第八個(gè)頁(yè)是BCM頁(yè)(BULK Chandged Map)，之后每隔511230頁(yè)會(huì)有一個(gè)BCM頁(yè)。BCM上的每一位(Bit)代表著一個(gè)區(qū)，如果此位為1，則表示自上次BACKUP LOG后，這個(gè)區(qū)被BULK類型操作修改過。再下次日志備份時(shí)，會(huì)將這些被修改過的區(qū)復(fù)制到日志備份中。

3. 使用最小日志記錄導(dǎo)入數(shù)據(jù)時(shí)需要滿足的條件

并不是任何情況下都可以實(shí)現(xiàn)最小日志導(dǎo)數(shù)，判斷邏輯如下(來自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開始可以使用跟蹤標(biāo)記610和排它鍵范圍鎖，實(shí)現(xiàn)空/非空聚集索引表的最小化日志操作。

排他鍵范圍鎖的作用例子：聚集索引表tb(id INT),目前有4行數(shù)據(jù)，分別為1，1000，2000，3000。現(xiàn)在需要向表中插入500行數(shù)據(jù)，這些數(shù)據(jù)的值區(qū)間為[1001,1500]。

當(dāng)插入時(shí)，SQL Server不需要獲取聚集索引整體的排它鎖(像tablock這種)，而只是獲取原有鍵值區(qū)間的排它鍵范圍鎖。這里就是在(1000,2000)區(qū)間上獲取X KEY-RANGE LOCK。而不在這個(gè)區(qū)間的數(shù)據(jù)，仍然可以被其它進(jìn)程訪問。如果要實(shí)現(xiàn)非空索引表的最小化日志記錄導(dǎo)數(shù)，需要預(yù)先將導(dǎo)入數(shù)據(jù)按目標(biāo)表的索引鍵值列進(jìn)行排序，并啟用跟蹤標(biāo)記610。

從上面的判斷邏輯可以看出，實(shí)現(xiàn)最小日志記錄的大前提是：數(shù)據(jù)庫(kù)不是完整恢復(fù)模式且表沒有標(biāo)記為復(fù)制。對(duì)于堆表總是需要使用TABLOCK。對(duì)于索引表，則要分為空表和非空表兩種情況來處理。這部分內(nèi)容在后文的例子再展開來說明。

觀察BULK導(dǎo)入的日志

使用未公開的系統(tǒng)函數(shù)sys.fn_dblog查找相關(guān)的日志內(nèi)容。fn_dblog接受兩個(gè)參數(shù)用以指定要查詢的日志區(qū)間，分別表示開始和結(jié)束的LSN。輸出字段中，此文需要關(guān)注的是Operation, Context, Log Record Length和AllocUnitName。因?yàn)槭俏垂_的的函數(shù)，所以輸出內(nèi)容代表的意義，需要結(jié)合個(gè)人經(jīng)驗(yàn)和大家的“共識(shí)”來解讀。

Operation(LOP)：表示執(zhí)行何種日志操作, 例如修改行為L(zhǎng)OP_MODIFY_ROW，設(shè)置位圖頁(yè)時(shí)為L(zhǎng)OP_SET_BITS等等。

Context(LCX)：日志操作的上下文，一般表示受影響的對(duì)象類型。例如LCX_GAM,LCX_HEAP,LCX_PFS等。

Log Record Length：以byte為單位的日志長(zhǎng)度

AllocUnitName：表示受影響的具體對(duì)象

使用如下腳本進(jìn)行分析，腳本來自Jakub K

-- 日志條目錄數(shù)據(jù)和總大小
SELECT COUNT(*)AS numrecords,
 CAST((COALESCE(SUM([Log Record LENGTH]), 0))
 / 1024. / 1024. AS NUMERIC(12, 2)) AS size_mb
FROM sys.fn_dblog(NULL, NULL) AS D
WHERE AllocUnitName = 'dbo.tableName' OR AllocUnitName LIKE 'dbo.tableName.%';

-- 各類型日志的平均長(zhǎng)度和數(shù)量
SELECT Operation, Context,
 AVG([Log Record LENGTH]) AS AvgLen, COUNT(*) AS Cnt
FROM sys.fn_dblog(NULL, NULL) AS D
WHERE AllocUnitName = 'dbo.tableName' OR AllocUnitName LIKE 'dbo.tableName.%'
GROUP BY Operation, Context, ROUND([Log Record LENGTH], -2)
ORDER BY AvgLen, Operation, Context;

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com