none
建立非叢集索引時,索引鍵資料行、包含資料行的順序是否影響查詢效能?

    問題

  • 新手發問XD

    想把一直以來建立非叢集索引的疑惑解決一下

    以下是我Table的結構

    Create Table myTable
    (
       PK_column int not null identity primary key,
       UserName nvarchar(10) not null default(''),
       UserSex nvarchar(1) not null default(''),
       UserAddress nvarchar(100) not null default(''),
       IsEnable bit not null default(1)
    )

    現在我有一個SQL語法如下:

    Select PK_column, UserSex,UserAddress
    From myTable
    Where UserName='Jack' And IsEnable=1

    然後我建立的非叢集索引↓

    Create Nonclustered Index on myTable (UserName,IsEnable) 
    --↑想詢問UserName和IsEnable順序對調是否影響查詢效能?例如:成本、IO、耗時
    Include ( PK_column, UserSex,UserAddress ) 
    --↑同上問題UserSex,UserAddress,PK_column順序是否有差影響查詢效能?
    Go

    另外詢問,以我目前的SQL語句有Select一個PK_column資料行,那我建立索引時究竟該不該Include(PK_column)呢?

    我查到的Stackoverflow討論說 "沒差"

    https://stackoverflow.com/questions/5328179/good-doesnt-matter-bad-to-include-primary-key-in-covering-index

    以上幾個問題是我在校能調校時,發現"似乎"都不影響查詢效能,但自己無法100%肯定,所以上來向大家確認,麻煩大大們解惑了,謝謝~


    ====以下是簽名檔,不是針對發帖者的回覆==== My Blog 高級打字員的技術雲:http://www.dotblogs.com.tw/shadow /*提醒網友們理性和平討論,酸言酸語不只曝露你是憤青的直男癌,更是一種網路霸凌*/ /*If my concept is wrong ,please correct me.Thanks.*/

    2018年7月6日 上午 01:14

解答

所有回覆

  • 順序應該是不影響效能, 另外可以不要加入PK_Column, 請參考:

    How important is the order of columns in indexes?

    • 已標示為解答 Shadow .Net 2018年7月6日 上午 08:14
    2018年7月6日 上午 04:52
  • –↑想詢問UserName和IsEnable順序對調是否影響查詢效能?例如:成本、IO、耗時

    不影響

    –↑同上問題UserSex,UserAddress,PK_column順序是否有差影響查詢效能?

    不影響

    範例測試:

    線上測試連結

    • 已標示為解答 Shadow .Net 2018年7月6日 上午 08:14
    • 已取消標示為解答 Shadow .Net 2018年7月8日 上午 01:26
    2018年7月6日 上午 05:02
  • 順序應該是不影響效能, 另外可以不要加入PK_Column, 請參考:

    How important is the order of columns in indexes?

    老師您說不影響效能,但給的連結討論卻說會影響效能耶XD

    後來我多找幾個大數據資料表反覆實驗,確實索引鍵資料行要把選擇性高的資料行順序擺到愈前面查詢效能(成本、IO)愈好


    ====以下是簽名檔,不是針對發帖者的回覆==== My Blog 高級打字員的技術雲:http://www.dotblogs.com.tw/shadow /*提醒網友們理性和平討論,酸言酸語不只曝露你是憤青的直男癌,更是一種網路霸凌*/ /*If my concept is wrong ,please correct me.Thanks.*/

    2018年7月9日 上午 01:06
  • –↑同上問題UserSex,UserAddress,PK_column順序是否有差影響查詢效能?

    不影響

    範例測試:

    線上測試連結

    後來我再細看您給的SQL語法發現,您沒使用Where語句,直接抓資料表全部資料,難怪會Index Scan(沒發揮索引效用)

    還有「搜尋(Seek)」、「掃描(Scan)」是兩種不同的term

    您似乎搞混了,可以參考此篇文章網友整理的圖示:https://dotblogs.com.tw/henryli/2015/07/10/151809


    ====以下是簽名檔,不是針對發帖者的回覆==== My Blog 高級打字員的技術雲:http://www.dotblogs.com.tw/shadow /*提醒網友們理性和平討論,酸言酸語不只曝露你是憤青的直男癌,更是一種網路霸凌*/ /*If my concept is wrong ,please correct me.Thanks.*/

    2018年7月9日 上午 01:12
  • 後來我再細看您給的SQL語法發現,您沒使用Where語句

    在我印象中"是否走索引"不是以where為準,而是以select為主
    假如where的欄位都在索引設定,但是select有沒設定的欄位
    還是不會走索引篩選。

    直接抓資料表全部資料,難怪會Index Scan(沒發揮索引效用)

    有發揮的。
    Index Scan的來源是誰?
    還是Nonclustered Index。

    大大覺得Index Seek才是發揮索引功效
    但是在我的觀念中 Index Scan也是索引功效
    ,而且"效能不一定會輸給 Index Seek"。

    而要使用Index Seek還是Index Scan是靠RMDB的運算邏輯決定的。

    ===============================


    在DEMO案例中:

    1.Seek跟Scan在Nonclustered Index
    都是可以不照順序的

    2.多條件情況下:走seek篩選
    單條件情況下:會直接走scan篩選
    這是RMDB優化機制

    3.前兩種情況來源都是從Nonclustered Index而來

    以上是我的理解,有錯誤地方可以跟我說。

    ====

    線上測試連結

    • 已編輯 ITWeiHan 2018年7月9日 上午 03:10
    2018年7月9日 上午 02:23
  • 以上是我的理解,有錯誤地方可以跟我說。

    ====

    線上測試連結

    我再細看一下,您給的連結

    有幾個地方您得留意,以避免效能調校失真

    1.資料量太少了~  ,最好以百萬筆(不然10萬筆以上)數據來調校看看,比較能看出效能調校明顯差異

    建議您可以拿公司資料庫的會員主檔、訂單、產品等等(真實世界)資料表調試看看。

    2.由於資料量少,只有一筆,是我寧願不建立索引(少佔資料庫空間),直接資料表掃描(Table Scan)說不定還比較快XD

    3.您得在自己的SQL Server資料庫,下SQL語法

    SET STATISTICS IO ON  ;
    SET STATISTICS TIME ON; --線上工具,不支援這兩句

    從IO、耗時、和實際執行計畫查看Select語句成本查看查詢效能。

    4.

    另外在我印象中"是否走索引"不是以where為準,而是以select為主假如where的欄位都在索引設定,但是select有沒設定的欄位 還是不會走索引篩選。

    這時候我的情況是會發生索引搜尋+索引鍵查閱,而不是你的Table Scan耶~

    您可以考慮使用Include語句來建立cover index

    https://dba.stackexchange.com/questions/1930/nonclustered-index-column-order

    剛在查找資料中,發現一篇文章的論點是Include語句裡資料行的順序就沒那麼重要

    https://ask.sqlservercentral.com/questions/18159/does-the-order-of-include-columns-matter-in-an-ind.html

    順手貼上來,給後人參考~


    ====以下是簽名檔,不是針對發帖者的回覆==== My Blog 高級打字員的技術雲:http://www.dotblogs.com.tw/shadow /*提醒網友們理性和平討論,酸言酸語不只曝露你是憤青的直男癌,更是一種網路霸凌*/ /*If my concept is wrong ,please correct me.Thanks.*/

    2018年7月9日 上午 04:37
  • 對,大大是我的測試失真了
    不好意思我這邊做另外的測試
    感謝大大。

    ====

    這時候我的情況是會發生索引搜尋+索引鍵查閱,而不是你的Table Scan耶~

    以下是另外的測試補充:

    資料量小的時候系統有時候會判斷"全表格搜尋"為最佳,而不走索引
    舉例:
    當資料量小的時候(測試資料5000筆數)就算條件都在索引設定內,還是會走全表格搜尋

    select [col1], [col2], [col3] from TestTable
    where [col1] = '' and [col2] = ''  ;
    

    但有時候會判定走索引

    select [col1],[col2] from TestTable3
    where [col1] = '' and [col2] = ''
    

    當資料量達到一定程度(測試資料100000筆數),會走索引。


    ps.

    3.您得在自己的SQL Server資料庫,下SQL語法

    SET STATISTICS IO ON ;
    SET STATISTICS TIME ON; --線上工具,不支援這兩句

    另外線上測試可以看IO的

    • 已編輯 ITWeiHan 2018年7月10日 上午 12:21
    2018年7月9日 上午 10:18