[T-SQL] NCL INDEX 欄位選擇效能影響-解析

来源:http://www.cnblogs.com/KingJaja/archive/2016/02/23/5211157.html
-Advertisement-
Play Games

因為這篇文章寫的比較長一些，我就將總結先列出來 總結 1. 除了WHERE條件外，JOIN欄位除了記得建立索引，也要註意到選擇性的高低，如果真的找不到可用的欄位，可以考慮在兩邊關聯的表上加入super eky，再做JOIN如果單純想測試這個欄位的選擇性，可以透過這個語法SELECT 1 / CAST


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

總結

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

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

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

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

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

正文開始 ~

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

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

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

clip_image003

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

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

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

clip_image005

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

clip_image006

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

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

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

clip_image007

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

clip_image008

clip_image009

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

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

額外補充-優化過程

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

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

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

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

clip_image010

圖一

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

clip_image011

圖二

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

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

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

clip_image012

圖三

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

clip_image013

圖四

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

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

clip_image014

圖五

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

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

以下是相關的統計資訊

clip_image015

圖六 CL_INDEX

clip_image016

圖七 NCL_INDEX

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

clip_image017

圖八

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

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

clip_image018

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

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

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

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

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

clip_image019

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

但更有趣的地方在後面

clip_image020

我們可以先看到綠色註解的部分，這個部分是最一開始額外新增的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來看看測試結果。

clip_image021.

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

再來看我們的測試語法

 

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

最後一個區塊就只是一個簡單的JOIN測試，這裡就直接看TotoalsubtreeCost 大約是2.226左clip_image022右

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

clip_image023

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

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

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

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

我們下次見

參考文章：

http://www.sqlpassion.at/archive/2014/01/28/inside-the-statistics-histogram-density-vector/

http://www.cnblogs.com/CareySon/archive/2011/12/27/2303508.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 解決定時器在主線程不工作的問題
    NSTimer *timer = [NSTimer timerWithTimeInterval:2 target:self selector:@selector(someAction) userInfo:nil repeats:YES]; [[NSRunLoop mainRunLoop] addTi
  • MySQL入門（一）
    最近一直都在搞新員工的培訓材料，MySQL的培訓PPT我居然寫了100多頁，我都佩服我自己的毅力了。不如現在把這些總結一下，慢慢寫到博客里，供入門者參考。 一 關係型資料庫 關係型資料庫的理論提出於上世紀七十年代，由IBM的研究員E.F.Codd的論文提出。 關係型資料庫的理論基礎可以參考王珊和薩師
  • 知方可補不足~SQL為大數據引入分區表
    回到目錄 一些概念 分區表在oracle和sqlserver中都上存在的，當數據表的數據量過大時，上千萬，上億的數據，在進行數據查詢時需要顯得比較慢，性能很差，這時是時候引入分區表了，分區表顧名思義，就是把物理表用一些文件NDF進行分塊存儲，以緩減ＩＯ的壓力，因為當你的ＳＱＬ文件過大的，這其實對系統
  • 一個小時內學習SQLite資料庫
    一個小時內學習SQLite資料庫 2012-05-11 10:24 紅薯 OSCHINA 字型大小：T | T SQLite 是一個開源的嵌入式關係資料庫，實現自包容、零配置、支持事務的SQL資料庫引擎。 其特點是高度便攜、使用方便、結構緊湊、高效、可靠。 與其他資料庫管理系統不同，SQLite 的安裝
  • 通過SQL語句來備份,還原資料庫
    這裡僅僅用到了一種方式而已,把資料庫文件備份到磁碟然後在恢復. /* 2: 通過SQL 語句備份資料庫 3: */ 4: BACKUP DATABASE mydb 5: TO DISK ='C:\DBBACK\mydb.BAK' 6: --這裡指定需要備份資料庫的路徑和文件名,註意:路徑的文件夾是必
  • Oracle 如何對中文欄位進行排序
    Oracle 中對中文欄位進行排序通常有三種方式 1）按筆畫排序 select * from Table order by nlssort(columnName,'NLS_SORT=SCHINESE_STROKE_M') 2）按部首排序 select * from Table order by nl
  • 存儲過程帶事務，拼接id，返回值
    出處：http://www.cnblogs.com/cmsdn/archive/2012/04/25/2469568.html 以下SQL以防以後還需用到，特此備份 刪除一條留言信息會級聯刪除回覆信息，這時我們需要用到事務，如下SQL 1 ALTER PROCEDURE [dbo].[proc_tb
  • SQL Server代理(11/12)：維護計劃作業
    SQL Server代理是所有實時資料庫的核心。代理有很多不明顯的用法，因此系統的知識，對於開發人員還是DBA都是有用的。這系列文章會通俗介紹它的很多用法。 在這一系列的上一篇，我們看了使用代理帳戶模仿Windows安全上下文完成作業步驟的工作。大多數子系統支持代理賬戶，同時子系統限制代理賬戶， 要
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類