走向DBA[MSSQL篇] 針對大表設計高效的存儲過程【原理篇】附最差性能sql語句進化過程客串

設計背景

由于曆史原因，線上庫環境資料量及其龐大，很多千萬級以上甚至過億的表。目标是讓N張互相關聯的表按照一張源表為基表，資料搬移歸檔這裡我們舉例N為50 每張表資料5000W

最差性能sql進化客串

2表KeyName 字段意義名稱等相同從bug01 表中取出前500條不在bug02 表中的資料

最差性能：

<code>SELECT</code> <code>TOP</code> <code>500 a.KeyName </code><code>FROM</code> <code>bug01 a </code><code>LEFT</code> <code>JOIN</code> <code>bug02 b </code><code>on</code> <code>a.KeyName = b.KeyName</code>

<code>WHERE</code> <code>(a.KeyName </code><code>not</code> <code>in</code> <code>(</code><code>select</code> <code>distinct</code> <code>b.KeyName </code><code>From</code> <code>bug02))</code>

<code>ORDER</code> <code>BY</code> <code>a.KeyName </code><code>asc</code>

進化體在篇尾揭曉

詳細設計

問題點：性能安全容錯

流程篇為何如此設計在下文中會解釋

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秒後的内容。

　　　　　　是以采取臨時表政策。

<code> </code><code>CREATE</code> <code>TABLE</code> <code>#p</code>

<code> </code><code>OrderID </code><code>varchar</code><code>(50),</code>

<code> </code><code>primary</code> <code>key</code> <code>(OrderID) </code>

<code>SET</code> <code>@temp_text = </code><code>'INSERT INTO #p '</code><code>+@KeyText</code>

<code>--PRINT @temp_text</code>

<code>SET</code> <code>@KeyText = </code><code>'SELECT OrderID FROM #p'</code>

<code>--如果一級表關聯的操作次數比較多那麼可以訪源表操作以臨時表取代實體表</code>

<code>SET</code> <code>@SubKeyText = </code><code>'select 一級表_A_被關聯鍵 From 一級表_A with(nolock) where 一級表_A_關聯源表鍵 in ('</code> <code>+ @KeyText+</code><code>')'</code>

<code>CREATE</code> <code>TABLE</code> <code>#q</code>

<code>SET</code> <code>@temp_text = </code><code>'INSERT INTO #q '</code><code>+@SubKeyText</code>

<code>SET</code> <code>@SubKeyText =</code><code>'SELECT OrderID FROM #q'</code>

<code>--如果一級表關聯的操作次數不多可以直接生成資料過濾池</code>

<code>SET</code> <code>@SubKeyTextforA =</code><code>'select 一級表_B_被二級關聯鍵 From 一級表_B with(nolock) where 一級表_B_關聯源表鍵 in ('</code> <code>+ @KeyText+</code><code>')'</code>

<code>SET</code> <code>@SubKeyTextforB =</code><code>'select 一級表_C_被二級關聯鍵 From 一級表_C with(nolock) where 一級表_C_關聯源表鍵 in ('</code> <code>+ @KeyText+</code><code>')'</code>

<code>--如果存在更多層操作在此處可以繼續關聯資源過濾池 Demo隻做到三層</code>

<code>SET</code> <code>@THKeyTextforA =</code><code>'select 二級表_A_被三級關聯鍵 From 二級表_A with(nolock) where 二級表_A_關聯一級表鍵 in ('</code> <code>+ @SubKeyTextforA+</code><code>')'</code>

<code>--SET @THKeyTextforB ='select 二級表_B_被三級關聯鍵 From 二級表_B with(nolock) where 二級表_B_關聯一級表鍵 in (' + @SubKeyTextforBank+')'</code>

--step.3 分表歸檔操作

這個環節的問題是安全事務如何控制事務的大小如何衡量如何容錯以及如何将程式做得可擴充可維護

大家根據業務場景區分自己的批次範圍拿蟲子這篇demo來說 50張千萬級大表如果是批次5000條以上事務要放在内層處理如果是5000條以下可以放在最外層

事務的大小直接影響性能的波動

容錯的方案大家也可以自己設計蟲子的程式員采用第三類表異常表來重置失敗了就插入下一個批次直接就過濾

<code>--将錯誤的批次訂單号入異常表</code>

<code> </code><code>Insert</code> <code>into</code> <code>異常表(@ExTable) </code><code>SELECT</code> <code>OrderID </code><code>FROM</code> <code>#p</code>

<code>--@ExTable用來存放異常資料如果當期批次出錯則将本次批次訂單資訊入庫@ExTable下一批次則過濾這些資料再執行</code>

<code> </code><code>SET</code> <code>@KeyText = </code><code>'SELECT TOP '</code><code>+</code><code>CAST</code><code>(@SynSize </code><code>AS</code> <code>VARCHAR</code><code>(10))+</code><code>' '</code><code>+@Base_Key+</code><code>' FROM +'</code><code>+@BaseTable+</code><code>'+ WHERE '</code><code>+@Base_Key+</code><code>' not in (select '</code><code>+@Base_Key+</code><code>' From '</code><code>+@ExTable+</code><code>') '</code>

如何讓程式變的漂亮可維護

我們在存儲過程中同樣可以使用面試對象的思想隻不過存儲過程沒有類這樣的概念給我們那麼我們不妨自己設計

用什麼還是臨時表

<code>--一級直接關聯源表主鍵或為二級被關聯的主表</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'一級表_A'</code><code>,@Base_Key,@KeyText,</code><code>''</code><code>) </code><code>--一級表_A</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'一級表_B'</code><code>,@Base_Key,@KeyText,</code><code>''</code><code>) </code><code>--一級表_B</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'一級表_C'</code><code>,@Base_Key,@KeyText,</code><code>''</code><code>) </code><code>--一級表_C</code>

<code>--二級規則間接關聯</code>

<code> </code><code>--@SubKeyText相關</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'二級表_A'</code><code>,</code><code>'二級表_A_關聯一級鍵'</code><code>,@SubKeyText,</code><code>''</code><code>) </code><code>--二級表_A</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'二級表_B'</code><code>,</code><code>'二級表_B_關聯一級鍵'</code><code>,@SubKeyText,</code><code>''</code><code>) </code><code>--二級表_B</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'二級表_C'</code><code>,</code><code>'二級表_C_關聯一級鍵'</code><code>,@SubKeyText,</code><code>''</code><code>) </code><code>--二級表_C</code>

<code> </code><code>--自定義操作</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'特殊表'</code><code>,</code><code>'特殊表關聯鍵'</code><code>,</code><code>'自定義資料過濾方式'</code><code>,</code><code>''</code><code>) </code>

<code> </code><code>--其他自增列處理</code>

<code> </code><code>--修改訂單,及其取消修改訂單狀态曆史表</code>

<code> </code><code>INSERT</code> <code>INTO</code> <code>#k </code><code>VALUES</code> <code>(</code><code>'自增表'</code><code>,@Base_Key,@KeyText,</code><code>'自定義字段'</code><code>)</code>

--step.4 處理細節

遊标循環臨時表針對每一張表操作一次

<code>DECLARE</code> <code>CUR_ORDERHEDER </code><code>INSENSITIVE</code> <code>CURSOR</code> <code>FOR</code> <code>SELECT</code> <code>TableName,KeyName,temptext,colname </code><code>FROM</code> <code>#k</code>

<code> </code><code>OPEN</code> <code>CUR_ORDERHEDER</code>

<code> </code><code>FETCH</code> <code>CUR_ORDERHEDER </code><code>INTO</code> <code>@Cur_Table,@Cur_Key,@Cur_W,@Cur_K</code>

<code> </code><code>WHILE @@FETCH_STATUS = 0</code>

<code> </code><code>BEGIN</code>

<code> </code><code>EXECUTE</code> <code>P_Task_Sub_Synchronization</code>

<code> </code><code>@OutParam = @OutParam </code><code>OUT</code><code>, @OutMessage = @OutMessage </code><code>OUT</code><code>,</code>

<code> </code><code>@KeyText = @Cur_W,@</code><code>Table</code><code>= @Cur_Table,@Extension=@Extension,@IsDelSource=@IsDelSource,@KeyName=@Cur_Key,@ColName=@Cur_K</code>

<code> </code><code>--SET @OutMessage = @OutMessage+@OutMessage</code>

<code> </code><code>--PRINT @OutMessage</code>

<code> </code><code>IF @OutParam <> 0 </code>

<code> </code><code>BEGIN</code>

<code> </code><code>SET</code> <code>@OutMessage = @OutMessage + @Cur_Table +</code><code>'操作失敗'</code>

<code> </code><code>ROLLBACK</code> <code>TRAN</code>

<code> </code><code>--将錯誤的批次訂單号入異常表</code>

<code> </code><code>Insert</code> <code>into</code> <code>異常表(@ExTable) </code><code>SELECT</code> <code>OrderID </code><code>FROM</code> <code>#p</code>

<code> </code><code>DROP</code> <code>TABLE</code> <code>#k</code>

<code> </code><code>DROP</code> <code>TABLE</code> <code>#p</code>

<code> </code><code>DROP</code> <code>TABLE</code> <code>#q</code>

<code> </code><code>RETURN</code>

<code> </code><code>FETCH</code> <code>CUR_ORDERHEDER </code><code>INTO</code> <code>@Cur_Table,@Cur_Key,@Cur_W,@Cur_K</code>

<code> </code><code>ClOSE</code> <code>CUR_ORDERHEDER</code>

<code> </code><code>DEALLOCATE</code> <code>CUR_ORDERHEDER </code>

--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計劃

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))

step.3 何必要用外聯直接過濾不就得了嘿嘿

SELECT TOP 500 a.KeyName FROM bug01 a

WHERE (a.KeyName not in (select c.KeyName From bug02 c))

本篇就講到此處歡迎大家讨論

本文轉自熬夜的蟲子 51CTO部落格，原文連結:http://blog.51cto.com/dubing/712446

走向DBA[MSSQL篇] 針對大表設計高效的存儲過程【原理篇】附最差性能sql語句進化過程客串

繼續閱讀

壓縮編碼M-JPEG、MPEG4、H.264

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

走向DBA[MSSQL篇] 針對大表 設計高效的存儲過程【原理篇】 附最差性能sql語句進化過程客串

繼續閱讀

走向DBA[MSSQL篇] 針對大表設計高效的存儲過程【原理篇】附最差性能sql語句進化過程客串