?
?
《Replication的犄角旮旯》系列導讀
Replication的犄角旮旯(一)--變更訂閱端表名的應用場景
Replication的犄角旮旯(二)--尋找訂閱端丟失的記錄
Replication的犄角旮旯(三)--聊聊@bitmap
Replication的犄角旮旯(四)--關于事務復制的監控
Replication的犄角旮旯(五)--關于復制identity列
Replication的犄角旮旯(六)-- 一個DDL引發的血案(上)(如何近似估算DDL操作進度)
Replication的犄角旮旯(七)-- 一個DDL引發的血案(下)(聊聊logreader的延遲)
Replication的犄角旮旯(八)-- 訂閱與發布異構的問題
Replication的犄角旮旯(九)-- sp_setsubscriptionxactseqno,賦予訂閱活力的工具
---------------------------------------華麗麗的分割線--------------------------------------------
?
關于replication中的bitmap,貌似介紹的文檔不多;本文將從對此參數做一初步的簡析,并介紹如何利用這個參數處理一些特定環境下的問題;
再次強調, 本方法雖多次經受驗證無誤,但多次被MS supporter們建議不要嘗試使用此方法,還望各位DBA三思!
先來看看@bitmap在哪里出現
我們先創建一個表的復制訂閱,表結構如下

1 USE [ test_aaa ] 2 GO 3 4 /* ***** Object: Table [dbo].[test_b] Script Date: 2014/1/23 16:12:28 ***** */ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 SET ANSI_PADDING ON 12 GO 13 14 CREATE TABLE [ dbo ] . [ test_b ] ( 15 [ id1 ] [ int ] NOT NULL , 16 [ id2 ] [ int ] NOT NULL , 17 [ id3 ] [ int ] NOT NULL , 18 [ id4 ] [ int ] NOT NULL , 19 [ name ] [ varchar ] ( 10 ) NULL , 20 [ remark1 ] [ varchar ] ( 100 ) NULL , 21 [ remark2 ] [ varchar ] ( 100 ) NULL , 22 [ remark3 ] [ varchar ] ( 100 ) NULL , 23 [ remark4 ] [ varchar ] ( 100 ) NULL , 24 CONSTRAINT [ pk_id1_id2_id3_id4 ] PRIMARY KEY CLUSTERED 25 ( 26 [ id1 ] ASC , 27 [ id2 ] ASC , 28 [ id3 ] ASC , 29 [ id4 ] ASC 30 ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] 31 ) ON [ PRIMARY ] 32 33 GO 34 35 SET ANSI_PADDING OFF 36 GO
到訂閱庫的存儲過程中,找到sp_MSupd_dbotest_b,生成腳本

1 USE [ test_byxl1 ] 2 GO 3 /* ***** Object: StoredProcedure [dbo].[sp_MSupd_dbotest_b] Script Date: 2014/1/23 14:28:46 ***** */ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER procedure [ dbo ] . [ sp_MSupd_dbotest_b ] 9 @c1 int = NULL , 10 @c2 int = NULL , 11 @c3 int = NULL , 12 @c4 int = NULL , 13 @c5 varchar ( 10 ) = NULL , 14 @c6 varchar ( 100 ) = NULL , 15 @c7 varchar ( 100 ) = NULL , 16 @c8 varchar ( 100 ) = NULL , 17 @c9 varchar ( 100 ) = NULL , 18 @pkc1 int = NULL , 19 @pkc2 int = NULL , 20 @pkc3 int = NULL , 21 @pkc4 int = NULL , 22 @bitmap binary ( 2 ) 23 as 24 begin 25 if ( substring ( @bitmap , 1 , 1 ) & 1 = 1 ) or 26 ( substring ( @bitmap , 1 , 1 ) & 2 = 2 ) or 27 ( substring ( @bitmap , 1 , 1 ) & 4 = 4 ) or 28 ( substring ( @bitmap , 1 , 1 ) & 8 = 8 ) 29 begin 30 update [ dbo ] . [ test_b ] 31 set [ id1 ] = case substring ( @bitmap , 1 , 1 ) & 1 when 1 then @c1 else [ id1 ] end , 32 [ id2 ] = case substring ( @bitmap , 1 , 1 ) & 2 when 2 then @c2 else [ id2 ] end , 33 [ id3 ] = case substring ( @bitmap , 1 , 1 ) & 4 when 4 then @c3 else [ id3 ] end , 34 [ id4 ] = case substring ( @bitmap , 1 , 1 ) & 8 when 8 then @c4 else [ id4 ] end , 35 [ name ] = case substring ( @bitmap , 1 , 1 ) & 16 when 16 then @c5 else [ name ] end , 36 [ remark1 ] = case substring ( @bitmap , 1 , 1 ) & 32 when 32 then @c6 else [ remark1 ] end , 37 [ remark2 ] = case substring ( @bitmap , 1 , 1 ) & 64 when 64 then @c7 else [ remark2 ] end , 38 [ remark3 ] = case substring ( @bitmap , 1 , 1 ) & 128 when 128 then @c8 else [ remark3 ] end , 39 [ remark4 ] = case substring ( @bitmap , 2 , 1 ) & 1 when 1 then @c9 else [ remark4 ] end 40 where [ id1 ] = @pkc1 and [ id2 ] = @pkc2 and [ id3 ] = @pkc3 and [ id4 ] = @pkc4 41 if @@rowcount = 0 42 if @@microsoftversion > 0x07320000 43 exec sp_MSreplraiserror 20598 44 end 45 else 46 begin 47 update [ dbo ] . [ test_b ] 48 set [ name ] = case substring ( @bitmap , 1 , 1 ) & 16 when 16 then @c5 else [ name ] end , 49 [ remark1 ] = case substring ( @bitmap , 1 , 1 ) & 32 when 32 then @c6 else [ remark1 ] end , 50 [ remark2 ] = case substring ( @bitmap , 1 , 1 ) & 64 when 64 then @c7 else [ remark2 ] end , 51 [ remark3 ] = case substring ( @bitmap , 1 , 1 ) & 128 when 128 then @c8 else [ remark3 ] end , 52 [ remark4 ] = case substring ( @bitmap , 2 , 1 ) & 1 when 1 then @c9 else [ remark4 ] end 53 where [ id1 ] = @pkc1 and [ id2 ] = @pkc2 and [ id3 ] = @pkc3 and [ id4 ] = @pkc4 54 if @@rowcount = 0 55 if @@microsoftversion > 0x07320000 56 exec sp_MSreplraiserror 20598 57 end 58 end
看到這么多@bitmap,是不是有種升仙的感覺?
@bitmap 是binary類型,即二進制串;簡單來說,它是用來表示所操作的字段位置的參數,通過@bitmap,分發代理從distribution.dbo.msrepl_commands中讀取命令時(update操作),才會知道哪些列進行了更新;
我們先來解析一下這個存儲過程;
1、根據表結構的code,我們知道這個表共有9個字段,其中id1~id4被定義為聯合主鍵;
???? 由于binary(1)表示1個字節(8位的2進制),因此我們表示9個字段的@bitmap就只能用binary(2)來容納了;
其次,有的童鞋說,他們看到的update存儲過程只有一個程序段,而我的例子中有兩部分(29行~44行、46行~57行)。這個是由于存在聯合主鍵造成的;即當被訂閱的表中含有聯合主鍵(2個或以上的字段一同作為主鍵)的時候才會出現兩段代碼,前者是更新主鍵列,后者則是更新非主鍵列;
2、根據更新列的位置不同,@bitmap中的對應的值也不同;
? substring(@bitmap,1,1) & 1 = 1 表示第一列有更新;
? substring(@bitmap,1,1) & 2 = 2 表示第二列有更新;
? substring(@bitmap,1,1) & 4 = 4 表示第三列有更新;
???? 以此類推
? substring(@bitmap,1,1) & 128 = 128 表示第八列有更新;
???? 那第九位呢?? =256么?? 由于1個字節只有8位,而128=2^7,當第九位出現時就要進位了
? substring(@bitmap,2,1) & 1 = 1
???? 怎么樣,不難理解吧?
定義4個字段的聯合主鍵只是為了舉例說明的時候方便一些,實際的生產環境中可能不太經常能遇到;
?
再來看一下@bitmap在哪里可以獲取到呢?我先更新一條記錄,更新之前先關閉相應的分發代理(此處不需要分發命令應用到訂閱端)
我們去distribution里看看具體的分發命令(具體做法請見《 Replication的犄角旮旯(二)--尋找訂閱端丟失的記錄 》)
從命令中我們可以看出,更新的列位置為第5(name)、7(remark2)、8(remark3)列,按照二進制的表示方法為(注意反取,即←表示第1至第9位)
0 1101 0000
由于第九位沒有更新,因此為0,所以bitmap就是 1101 0000,換成十六進制就是0xD0,由于bitmap超出1個字節,因此后面再補0,就是我們看到的0xd000了
可能看到這里,有些童鞋會說,這太麻煩了,遇到一個很寬的表,光數逗號就數死了……盡管我們明白逗號是為了分割字段的,但系統為什么會這樣生成呢,這個問題可以參考: http://msdn.microsoft.com/zh-cn/library/ms152489%28v=sql.120%29.aspx
這個和article的屬性,調用訂閱端存儲過程方式有關(SCALL),這個不是本文的重點,在此不做贅述;
?
根據上面的算法,我們就可以知道,當我們要更新一個表時,可以根據更新列的位置,推算出實際的bitmap值,但這又有什么用呢?
?
-------------------------------------我是華麗麗的分割線-------------------------------------
?
應用場景:一個表(還是說商品表吧,比較典型),保存商品信息、簡介等內容,都是varchar(max)或text類型;商家在促銷活動前通常會大批量的更新這些內容(比如加個促銷活動介紹等等),更新大字段是復制環境中最頭疼的問題;
由于一條復制命令有長度限制(1K左右),如果一條更新記錄中的更新內容過大,就會被拆分成多條命令寫到msrepl_commands中(我曾經碰到過1條記錄的更新操作被拆成100個復制命令),如果高峰時期有用戶大量的進行這種操作,那作為DBA就可以升級為“張三瘋”了;
這時候我們就可以根據具體的update命令(一般除了更新必要的字段外,還會捎帶更新updatetime這樣的時間戳,具體就去找研發兄弟們要吧),計算出相應的bitmap,然后在相應的存儲過程中加個判斷,屏蔽掉這樣的操作;
具體操作就是,在sp_MSupd_dbotest_b這里的第24、25行之間加上
if @bitmap=0xd000
return;
再打開分發代理,這樣就屏蔽了@bitmap=0xd000的全部操作;
按照前面的操作,并沒有進行下面的更新
而我在此之后又從發布服務器insert了一條主鍵為2,2,2,2的記錄,以證明該操作在update之后已經傳到了訂閱端,而update被屏蔽了;
需要注意的是,由于按位操作是絕對嚴格,對于只更新column1和同時更新column1、column2將產生不同的bitmap,操作時一定要謹慎;
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
