測試的結果在 此處 ?本篇詳解一下原理
設計背景
由于歷史原因，線上庫環境數據量及其龐大，很多千萬級以上甚至過億的表。目標是讓N張互相關聯的表 按照一張源表為基表，數據搬移歸檔 這里我們舉例N為50 每張表數據5000W
最差性能sql進化客串
2表KeyName 字段意義 名稱等相同 從bug01 表中取出前500條不在bug02 表中的數據
最差性能：
SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName
WHERE (a.KeyName not in (select distinct b.KeyName From bug02))
ORDER BY a.KeyName asc
?進化體在篇尾揭曉
詳細設計
問題點：性能 安全 容錯
流程篇 為何如此設計 在下文中會解釋
step.1 源表數據過濾
這部分沒什么好說的 根據大家自己的業務場景設定不同的過濾規則
step.2 源表數據副本
程序的入口點肯定是源表了，擴展表中的內容都是以源表為Key來展開。那么這個展開的過程如何來做。
首先確定一些概念，這50表中的層級關系如何。可能直接和源表key鍵關聯的表只有10張。
例如我統計市內所有圖書館詳細信息，那么我們以圖書館為源表。圖書館關聯書架、地址、會員信息。那么這3中信息我們分為一級別表。
書架關聯圖書類別，地址關聯街道信息，會員關聯用戶借閱信息，那么后面3者我們繼續分為二級表，......按照場景繼續擴展。
方案1：使用游標 循環源表 根據源表key值 處理和key相關的數據? 假設我們沒批次處理500跳源表數據
也就是根據圖書館ID，遍歷所有節點。假設我們不分二級三級表，都是一級表 我們的insert操作次數是500*50。select操作同數據量
這個給誰肯定都不大樂意，而且如果再遍歷2級表3級更難想象。
方案2：對源表key數據進行集合，存進變量，然后用in表達式。貌似可行。直接減少到1/500的操作次數。但是這里有個最恐怖的問題。
變量都有長度，例如varchar 最大長度不能超過65535。
方案3：將源表Key做成一個查詢過濾池(相對于一級表 底層的sql where條件語句 下面會詳細介紹一下) 相對于第二種方案，我們這種似乎又將操作數提高了。
不考慮層級的情況下，insert操作50。select操作50*2可以接受.
方案3擴展： 對于一張大表來說 操作50次也不是什么可以樂觀的數字，并且這個50還有可能變成500，5000，50000。
更有一個問題就是，當你操作這500條的時候，可能會有數據干擾，你1秒前取得的這500條可不一定是1秒后的內容。
所以采取臨時表策略。
CREATE TABLE #p
(
OrderID varchar(50),
primary key (OrderID)
);
SET @temp_text = 'INSERT INTO #p '+@KeyText
--PRINT @temp_text
EXEC (@temp_text)
SET @KeyText = 'SELECT OrderID FROM #p'
--如果一級表關聯的操作次數比較多那么可以訪源表操作 以臨時表取代物理表
SET @SubKeyText = 'select 一級表_A_被關聯鍵 From 一級表_A with(nolock) where 一級表_A_關聯源表鍵 in (' + @KeyText+')'
CREATE TABLE #q
(
OrderID varchar(50),
primary key (OrderID)
);
SET @temp_text = 'INSERT INTO #q '+@SubKeyText
EXEC (@temp_text)
SET @SubKeyText ='SELECT OrderID FROM #q'
--如果一級表關聯的操作次數不多可以直接生成數據過濾池
SET @SubKeyTextforA ='select 一級表_B_被二級關聯鍵 From 一級表_B with(nolock) where 一級表_B_關聯源表鍵 in (' + @KeyText+')'
SET @SubKeyTextforB ='select 一級表_C_被二級關聯鍵 From 一級表_C with(nolock) where 一級表_C_關聯源表鍵 in (' + @KeyText+')'
--如果存在更多層操作在此處可以繼續關聯資源過濾池 Demo只做到三層
SET @THKeyTextforA ='select 二級表_A_被三級關聯鍵 From 二級表_A with(nolock) where 二級表_A_關聯一級表鍵 in (' + @SubKeyTextforA+')'
?--step.3 分表歸檔操作
這個環節的問題是安全 事務如何控制 事務的大小如何衡量 如何容錯 以及如何將程序做得可擴展 可維護
大家根據業務場景 區分自己的批次范圍 拿蟲子這篇demo來說?50張千萬級大表?如果是批次5000條以上 事務要放在內層處理 如果是5000條以下 可以放在最外層
事務的大小直接影響性能的波動
容錯的方案大家也可以自己設計 蟲子的程序員采用第三類表 異常表來重置 失敗了就插入 下一個批次直接就過濾
--將錯誤的批次訂單號入異常表
Insert into 異常表(@ExTable) SELECT OrderID FROM #p
--@ExTable用來存放異常數據 如果當期批次出錯 則將本次批次訂單信息入庫@ExTable下一批次則過濾這些數據再執行
SET @KeyText = 'SELECT TOP '+CAST(@SynSize AS VARCHAR(10))+' '+@Base_Key+' FROM +
'+@BaseTable+'+ WHERE '+@Base_Key+' not in (select '+@Base_Key+' From '+@ExTable+') '
?如何讓程序變的漂亮 可維護
我們在存儲過程中同樣可以使用面試對象的思想 只不過存儲過程沒有類這樣的概念給我們 那么我們不妨自己設計
用什么 還是臨時表
--一級 直接關聯源表主鍵 或為二級被關聯的主表
INSERT INTO #k VALUES ('一級表_A',@Base_Key,@KeyText,'') --一級表_A
INSERT INTO #k VALUES ('一級表_B',@Base_Key,@KeyText,'') --一級表_B
INSERT INTO #k VALUES ('一級表_C',@Base_Key,@KeyText,'') --一級表_C
--二級 規則間接關聯
--@SubKeyText相關
INSERT INTO #k VALUES ('二級表_A','二級表_A_關聯一級鍵',@SubKeyText,'') --二級表_A
INSERT INTO #k VALUES ('二級表_B','二級表_B_關聯一級鍵',@SubKeyText,'') --二級表_B
INSERT INTO #k VALUES ('二級表_C','二級表_C_關聯一級鍵',@SubKeyText,'') --二級表_C
--特殊處理
--自定義操作
INSERT INTO #k VALUES ('特殊表','特殊表關聯鍵','自定義數據過濾方式','')
--其他 自增列處理
--修改訂單,及其取消修改訂單狀態歷史表
INSERT INTO #k VALUES ('自增表',@Base_Key,@KeyText,'自定義字段')
?--step.4 處理細節?
?游標循環臨時表 針對每一張表操作一次
DECLARE CUR_ORDERHEDER INSENSITIVE CURSOR FOR SELECT TableName,KeyName,temptext,colname FROM #k
OPEN CUR_ORDERHEDER
FETCH CUR_ORDERHEDER INTO @Cur_Table,@Cur_Key,@Cur_W,@Cur_K
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE P_Task_Sub_Synchronization
@OutParam = @OutParam OUT, @OutMessage = @OutMessage OUT,
@KeyText = @Cur_W,@Table= @Cur_Table,@Extension=@Extension,@IsDelSource=@IsDelSource,@KeyName=@Cur_Key,@ColName=@Cur_K
--SET @OutMessage = @OutMessage+@OutMessage
--PRINT @OutMessage
IF @OutParam <> 0
BEGIN
SET @OutMessage = @OutMessage + @Cur_Table +'操作失敗'
ROLLBACK TRAN
--將錯誤的批次訂單號入異常表
Insert into 異常表(@ExTable) SELECT OrderID FROM #p
DROP TABLE #k
DROP TABLE #p
DROP TABLE #q
RETURN
END
FETCH CUR_ORDERHEDER INTO @Cur_Table,@Cur_Key,@Cur_W,@Cur_K
END
ClOSE CUR_ORDERHEDER
DEALLOCATE CUR_ORDERHEDER
? --step.5 資源釋放
?--step.6 流程處理
?
這2個部分就不詳細說了 ?
最差性能sql進化過程
step.1 not in了 就別再distinc了 distinc和not in都是臭名昭著的角色 not in后+dinstinc畫蛇添足而已
改后sql：
SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName
WHERE (a.KeyName not in (select? b.KeyName From bug02))
ORDER BY a.KeyName asc
step.2 別名 別小看別名 用圖來說話 原sql計劃
![走向DBA[MSSQL篇] 針對大表 設計高效的存儲過程【原理篇】 附最差性能sql語句進化過程客串](http://img.it610.com/image/product/afe86287d0054dacaa32dde3afb6a3ee.jpg)
改后sql：
?SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName
WHERE (a.KeyName not in (select?
c.KeyName From bug02 c
))
ORDER BY a.KeyName asc
![走向DBA[MSSQL篇] 針對大表 設計高效的存儲過程【原理篇】 附最差性能sql語句進化過程客串](http://img.it610.com/image/product/d60e9a7d4826411c8f65f22e4bc9a0a8.jpg)
step.3 何必要用外聯?直接過濾不就得了 嘿嘿
改后sql：
SELECT TOP 500 a.KeyName FROM bug01 a
WHERE (a.KeyName not in (select? c.KeyName From bug02 c))
ORDER BY a.KeyName asc
step.4 根據luofer同學的建議 再進化一次 直接EXCEPT
SELECT TOP 500 a.KeyName FROM bug01 a except
SELECT b.KeyName from bug02 b
本篇就講到此處 歡迎大家討論
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
