查詢數據庫,資料分佈探討

最近有次在修改某段程式時,發現一段程式算法看起來簡單。

最近在群裡有個朋友問了個問題是這樣的

因為這篇文章寫的比較長一些,我就將總結先列出來

但背後因為多次查詢數據庫,導致效能問題。

用户表有一千多万行,主键是用户ID,我做了分区。但经常查询时,其它的表根据用户ID来关联,这样跨区查询,reads非常高。有什么好的处理办法?不分区的话,索引维护要好久的时间

總結

這段程式主要是利用 EPPLUS 讀取 Excel 資料,檢查資料是否已存在數據庫中,若有就將已存在的序號回傳

在查看了他提供的分區資訊後,發現只有23個分區(包含一定要有的Null分區)

1. 除了WHERE條件外,JOINColumn除了記得建立索引,也要注意到選擇性的高低,如果真的找不到可用的Column,可以考慮在兩邊關聯的表上加入super eky,再做JOIN
如果單純想測試這個Column的選擇性,可以透過這個語法
SELECT 1 / CAST(COUNT(DISTINCT {COLUMN_NAME} ) AS NUMERIC(18, 2)) FROM {TABLE_NAME}

優化前的案例原碼如下

图片 1

2. 某些情境下(像是本文的例子),索引Column其實是因為商業邏輯沒有考慮周全,導致該Column的值重複性太高,如果在早期就發現這個問題了,可以與開發人員溝通,想辦法讓該Column的值重複性下降。我們都知道索引越接近唯一值效能就會越好(不用付出額外的查找成本)

 //預先檢查是否有存在的編號
                string duplicateNumber = "";
                for (int row = startRow; row <= worksheet.Dimension.End.Row; row  )
                {
                    if (worksheet.Cells[row, 1].Value != null)
                    {
                        var result = Conn.QueryFirst<int>(@"
                    SELECT 1 FROM TemplateNumber 
                    WHERE SerialNumber = @SerialNumber ",
                            new
                            {
                                SerialNumber = worksheet.Cells[row, 1].Value,
                            });
                        if (result == 1)
                        {
                            duplicateNumber  = worksheet.Cells[row, 1].Value;
                        }
                    }
                }

Null分區在這裡的定義其實很簡單,當你的資料沒有辦法放到你先前建立的分區時,就會將該資料放到所謂的Null分區(預設分區)。

  1. 在SQL Server中,索引有一欄可以勾選是否唯一。該Column的設定值是會影響到SQL Server如何判斷查找,如果確定該欄不會有重複的資料,就勾起來吧!
    图片 2

從上述可以看到,對於每一行Row的資料,都進一次庫去做查詢比對

因此如果在探尋分區規則時沒有依照現有的資料進行分區的設計,將會很容易導致資料偏斜(Data Bias)),一但資料出現了偏斜時在查找時就會很容易在NULL區出現過多的讀取

4. 要注意的是,不是用到了索引就會速度飛快,實際上你要先明白你資料集預期是會有多少筆,如果SQL Server實際在運作時,撈取的資料超過預期。就需要去找出原因,否則實際上不只是索引撈的資料量太多,後續SQL Server在做處理時,會一併用高耗能的方式去處理大量資料
e.g. 平行資料流、Hash Join 、Table Pool

那在這種情況要怎麼修改呢? 其實仔細想一下查詢語句的轉換就行了

以今天的案例來看待,當要比對的ID不在這22個分區中時就會到NULL分區進行查找的動作。而在群友提供的資料中其實有出現了oGpI0w_ 、mGpI0w等字眼

5. 利用子查詢或CTE做資料查詢拆分並重新Join時,也要注意到選擇的Column值要從重複性較低的資料表上選取

List<string> querySerialNum = new List<string>();

                for (int row = startRow; row <= worksheet.Dimension.End.Row; row  )
                {
                    if (worksheet.Cells[row, 1].Value != null)
                    {
                        querySerialNum.Add(worksheet.Cells[row, 1].Value.ToString());
                    }
                }
                var result = Conn.Query<string>(@"
                    SELECT SerialNumber FROM TemplateNumber 
                    WHERE SerialNumber IN @SerialNumber ",
                    new
                    {
                        SerialNumber = querySerialNum
                    });
                if (result.Any())
                {
                    throw Exception(string.Join(";",result));
                }

可以想見的是,該NULL分區的資料是相當多的

正文開始 ~

可以看到這次我把 Excel 的內容先放到一個陣例裡,最後改用 IN 的方式去將已存在的資料取出

以下就一個測試情境來探討在分區規則不同時的效能比較

筆者前陣子因為有user反應SSRS的報表無法一次匯出全部的資料,當下心想應該是T-SQL語法的問題。除了先將該問題處理之外,也一併將舊的語法全面改用CTE撰寫。

這樣原本 N 次的庫查詢動作,現在只需要 1 次庫的查詢即可

首先建立二張結構一樣的表,資料量約一千二百萬筆

一般會用CTE寫的原因不外乎是當一個查詢裡面的子查詢(Sub-query)過多時,語法會顯得非常複雜也很難維護。透過CTE可以很直覺的將資料集拆成一小區一小區後,再聯合平做一次JOIN來達到我們的需求

看完後是不是感覺很簡單? 各位可以查看一下同事們寫的代碼裡,是否也有出現這樣的問題唷

图片 3

這次在改寫的過程中也為了效能有特別注意執行計畫這一塊,也找到了索引對於整體查詢效能影響的關鍵

咱們下次見!

接下來分別建立給表Demo1與Demo2的表分區函數(請注意圖中的註解)
(注意,以下示範並沒有利用到分區FileGroup優化,當你用了分區時請一定要同時利用FileGroup進行優化)

图片 4

一個是利用UserID前五碼分區另一個則利用前一碼進行分區

先來看看這個因為選錯INNER JOIN Column導致的效能問題吧!可以看到上圖雖然用了NCL index seek但是撈出來的資料量顯然是非常多阿!
图片 5

這裡要注意的是SQL Server 2016一個資料表或索引最多可以有 15,000 個資料分割

正常來說應該只有這樣1856筆。
兩者相差了快178倍 。

SQL Server 2005 與 2008 則需為SP2才可使用 (否則只能合計有1000個分區)

在這個案例中我們首先看到的是 執行數目 232  這一個值。這裡其實就是INNER JOIN(NESTED)背後的OUTER Table表上的筆數。這裡我們把232先記下來

Refer : New Limit for Number of Partitions in SQL Server 2008 SP2

图片 6

图片 7

提到索引,接下來為了分析為何用了第一個索引會撈出33萬筆這麼多的資料。我們要來查看這個索引的統計資訊(Statistics) ,這裡為了方便我們講解,我已經截取出我們所需要的資料到Excel上並且也實際算了一下,來證明我們的猜想

Demo1表分區函數

图片 8

图片 9

可以看到第一個索引會用到的HISTOGRAM是這幾筆鍵值。第一欄的CP_1、CP_2基本上就是我們的ability_ID,這裡也可以看到因為EQ_ROW平均都有1500筆左右的資料,所以乘上我們OUTER Table的筆數232。最後加總取一個平均,就是SQL Server取出的資料量。

Demo2表分區函數

而這就也就是為什麼當我們用第一個索引時,SQL Server會取出這麼多資料,原因其實就是在於CP_1、CP_2鍵值資料太多導致SQL Server在查找(Seek)時,每次都要掃將近1500筆。

而在表中不重複前五碼的資料筆數約9百多萬,如下圖
(可以想見的是在NULL區中會有大量的資料存放)

第二個就比較簡單了,同樣的從HISTOGRAM來看,SQL Server 只需要查找(Seek) 一個區間的資料,而且這個區間資料筆數也非常的小。這裡透過CourseID取得的只有8筆再乘上我們剛剛提到的232,也就剛好為1856

图片 10

图片 11

接下來我們來看看分區後的Demo1與Demo2分區表資料分佈情形

透過實際了解SQL Server最終是如何撈取這麼多的資料列後,我們可以簡單的看看兩邊索引的選擇性

图片 12

图片 13

 

图片 14

Demo1表分區資料分佈

可以很明顯的看到第二個索引的選擇性很明顯是優於第一個的

图片 15

但這裡要注意的是,雖然我們能透過索引的選擇性去比較這個索引的好壞。但實際上仍要去檢查這個Column的資料是不是與當初預期的規劃一樣,以這個案例來說,當初設計Ability_ID Column時並沒有將重複值這件事考慮進去,所以最後也導致了SQL Server要額外付出成本從重複的資料集中做搜尋(Seek)的動作。

 

額外補充-優化過程

图片 16

其實筆者這次用CTE,改寫時!發現到,SQL Server並沒有像我預期的,先將某些資料取出成小資料集後再進行JOIN的動作,而是間接去將原始表撈取出了大量資料!

Demo2表分區資料分佈

以下的語法JOIN關聯有經過調整,所以會跟上面的例子有點不太一樣

案例:當利用LIKE做前綴查找

如果有遇到這種問題,可以先透過QUERY HITS的方式來強迫SQL Server依照T-SQL語句的順序執行,透過這種方式我們可以很容易找出問題點。(但前提是你對於資料表執行的順序有一定的了解)

這裡從前述的資訊可以知道在Demo1 a0%最少有6個區需要查找

我們先來看看原本的SQL Server認為的最佳執行語句

而Demo2只有一個區需要查找

图片 17

接下來我們先簡單的看一下兩張表在相同查詢時IO的差異 (可以看到第二張表較優)

圖一

图片 18

由圖一我們可以發現到整體TotalSubtreeCoast大約有4.414左右,而比較執行吃重的部分我已經用黃色標記起來了。這裡我們就先稍微看一下

接下來我們仔細看一下相關的執行計畫與查找的分區數
可以發現在執行時Demo1會查找七個分區,而Demo2只會從一個分區中進行查找

图片 19

图片 20

圖二

案例:當從預設分區查找

一樣我們看到圖二黃色標注的地方,但不一樣的是,這次我利用了OPTION (FORCE ORDER) 強迫SQL Server 依照我認為最佳的執行順序去執行,而發生問題的資料筆所撈的筆數也只有232筆。

這次我們簡單的查找z開頭的UserID ,從先前的資訊可以知道。

很顯然相同語法SQL Server選了一個看起來比較差的執行計畫去執行。

表Demo1並沒有建立z開頭的分區,因此z相關的資料將會存放到預設分區(Null區)

不過也因為有完整的執行計畫參考,我們就依照這當中一些可能的問題,進行調整吧!

表Demo1的預設分區統計約有643萬筆,而表Demo2的z分區約有45萬筆

图片 21

由此可見在Demo2表上查找應該會優於Demo1的(當資料筆數再更多時,差異會更大)
見下圖

圖三

图片 22

一開始看到執行計畫時,SQL Server會貼心的提醒DBA們,是否有用到的Column缺少索引,由上圖可以發現到在SQLServer有建議在Survey_Question表上建立一個NCL Index 。而下圖的執行計畫圖示中也顯示出Survey_Question是採用Index Scan的方式進行資料查找。這裡我們就先依照SQL Server的建議建立相關的索引
(但要注意的是,SQL Server給的建議是在語法非常的簡單的情形下,一但語法過度複雜時,SQL Server就無法判斷了,需要DBA們依照經驗進行索引的調整唷!)

以上便是今天的表分區探討,替各位總結一下。

图片 23

1.在規劃表分區時,首先要注意該表的相關查詢語句,以最常用在條件式的字段做為分區依據是較佳的。

圖四

2.承上,即使使用最常用的字段做為分區依據,仍然要確認資料是否適合做為分區。

調整之後可以發現到原本的Index Scan已經因為能用到適合的索引,而變成Index Seek了。但是我們仍發現到左邊粉紅色標註的地方,有一個Index Scan的動作但是這時候SQL Server並未顯示有索引缺漏。

例如:即使常用的查詢字段為姓別 (男、女),用此字段做為分區,僅能將資料最多分為三個區。在大資料時,性能並無法顯著的增加。簡單的評估可以用目前的資料筆數除以分區數,可得知每個分區的資料分佈進而做分區建立的評估依據

我們來詳看一下這段做了什麼事

比如可以用下列這種簡單的語法計算每個分區數

图片 24

--12228608 / 37 
SELECT COUNT(1) / 
(
SELECT COUNT(1) FROM 
(
SELECT 1 as Counts FROM Demo1 GROUP BY SUBSTRING(UserID,1,1)
) as X
)
FROM Demo1

圖五

後記

透過上圖可以更詳細的了解SQL Server實際上,是將兩張表Survey_question與Question_option做了Hash Join動作,而這個選擇是沒有錯的,因為我們看到最底下,Question_option這裡撈取了29萬筆之多的資料。也因為這樣的資料量太大,用Nested Join是非常沒有效率的。但在先前提到的,我們利用OPTION (FORCE ORDER)的結果,並沒有這樣大量的資料出現,所以也代表的是,這肯定是還有優化的空間。下一步就先分析兩邊索引的統計資訊

在寫本篇時,還發現了一個需要注意的問題,當利用VARCHAR字段做為分區依據時。

我們先看Question_option這張表,首先是他利用了一個CL Index做了Scan的動作,但是原本的資料表上明明有建立了以question_id為主的NCL,怎麼會沒有使用呢?

在查詢時需要在該字段使用 LIKE 而不是一般的Equal (=)做為查找。
如果採用一般的Equal(=)做為查找時,該執行計畫會顯示查找了所有分區內容
具體原因如果有朋友知道,還請協助解答。

以下是相關的統計資訊

以下是查找的比較圖

图片 25

图片 26

圖六 CL_INDEX

使用Equal(=)查找

图片 27

图片 28

圖七 NCL_INDEX

使用LIKE查找

依照我們目前會用到的RANGE_HI_KEY (已經分別用黃色標註) ,可以看到如果是用NCL_INDEX應該會是比較好的選擇,這裡我們直接利用TABLE HITS測試看看

本次用來查詢表分區相關資訊的語法

图片 29

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number,
 p.partition_id, i.data_space_id, f.function_id, f.type_desc, 
 r.boundary_id, r.value AS BoundaryValue,p.rows
FROM sys.tables AS t  
JOIN sys.indexes AS i  
  ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
  ON i.object_id = p.object_id AND i.index_id = p.index_id  
JOIN sys.partition_schemes AS s  
  ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f  
  ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r  
  ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE t.name = '已分區表名稱' AND i.type <= 1  
ORDER BY p.partition_number;

圖八

最後謝謝各位觀看囉!如果有問題歡迎在底下留言與我討論

可以見到,雖然用了我們認為比較好的NCL_INDEX,但資料仍然有26萬筆之多。並沒有達到很明顯的下降

這裡我們回頭往上一層追,也就是15行顯示的R_Course_Stu_Ability資料表 。並且根據之前利用
OPTION (FORCE ORDER)執行後的執行計畫,來做一個簡單的對照。(這裡是因為要得知在最好的情況下,各個資料表的筆數應該有多少,藉此比對我們較差的語法,是否仍可以做修正

图片 30

圖九 OPTION (FORCE ORDER)的執行計畫結果

可以從圖九得知,在最好的情形下,我們僅需要232筆資料即可關聯出我們所需的結果

此外透過圖八我們也可以知道需要針對AbilityID這個Column做修正 (原因在一開始有提到,因為這個Column的資料重複性太高)

所以我們想辦法讓兩張表JOIN的資料重複性降低 (也就是選用更多的Column來做JOIN,SQL Server就會認為回傳的筆數會比較少)

最後可以看到修改後的結果如下

图片 31

雖然原本R_Course_Stu_Ability的資料從5817筆上升到11281筆,但是我們一開始的Question_options資料的筆數直接從26萬筆之多,下降到只有232筆!這一來一往之間。其實效能是有提升的 (可以看到TotalSubtreeCost 從2.76下降成1.98)

但更有趣的地方在後面

图片 32

我們可以先看到綠色註解的部分,這個部分是最一開始額外新增的JOIN Column,而另一部分則是修改後的Column。

這裡的差別僅僅是從不同的資料表中,抓取我們所需的Column而已。

但是再看到執行計畫,可以發現到這個執行計畫的R_Course_Stu_Ability資料表筆數從原本的1萬多筆,也下降為232筆。而TotoalSubtreeCost更是再度下降到1.328

為什麼只是從A表的Column選擇換到B表的Column選擇,居然效能可以提昇如此之多呢?

其實原因很簡單,以這個案例來說Survey_main在執行資料查詢時,只會回傳一筆資料,但是R_Course_Ability是會回傳多筆資料的。自然而然當我們選擇Ability表時,SQL Server會認為即使資料的重複性下降了,但還是要回傳一定的資料筆數,所以在產生執行計畫時,會以較多的資料筆數為考量。而如果選擇main表時,因為僅會回傳一筆資料,當然在推估的過程中就是以一筆資料為基準去產生整個執行計畫。

這裡為了證明,我就簡單用兩張表搭配CTE來看看測試結果。

图片 33.

可以看到我特意在A表上額外建了兩個Column,這裡組合起來會是一個唯一值,而B表上也是弄了一樣的Column,但這裡面的值是會有重複的

再來看我們的測試語法

 

第一個區塊就是我提到的JOIN Column選擇,這裡要額外注意的是WHERE條件記得要選用具有主鍵的那張表(這裡是A表),不要選錯選到B表去了喔(有時候會剛好兩張表有相同的Column名稱)!

最後一個區塊就只是一個簡單的JOIN測試,這裡就直接看TotoalsubtreeCost 大約是2.226左图片 34

好啦!最後就來看看如果我們JOIN  Column是改選擇A資料表上面呢?

图片 35

資料筆數我就不多加說明了,直接看到TotoalsubtreeCost 大約是1.745左右

是不是很明顯比上一個語法還要好呢?

所以各位在寫Sub-Query或是拆分成CTE後要重新做JOIN時,不要忘記了。一但Column是要做為JOIN使用的話,記得從資料重複性較低的那張資料表上選唷!

天呀~~這篇文章因為需要測試與推估過程,寫了超級久的。如果覺得不錯的話,再給我個讚吧!

我們下次見

參考文章:

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:查詢數據庫,資料分佈探討

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。