資料庫索引深入淺出(二)

Agenda

前文

本系列文章

兩種基本索引

  • Clustered Index(叢集索引)
  • NonClustered Index(非叢集索引)

兩種Lookup(如果NonClustered Index無法滿足查詢結果執行)

  • RID Lookup
  • Key Lookup

本篇會介紹其他種類Index

Covering Index

我們先來看看Covering Index語法.

最主要使在NONCLUSTERED INDEX後面加上INCLUDE欄位.

1
2
3
4
5
6
CREATE NONCLUSTERED INDEX IX_T_Id_Convering on dbo.T(
id
) INCLUDE (
UserId,
UserGroup
)

加入INCLUDE欄位含意

NONCLUSTERED INDEXColumn加入INCLUDE區域後此NONCLUSTERED INDEX會把此欄位資料加入至子頁層.之後如果要查找資料時就不用在Lookup回去

所以我們可以把Covering Index當作是偽CLUSTERED INDEX.
如果每次只需要SELECT少部分欄位且範圍較大又須排序,Covering Index執行效率會比CLUSTERED INDEX來的快.

Covering欄位只會在子頁層儲存資料,並不會在中葉層儲存相關資訊。

儲存方式如下圖會把資料存在子頁層中,並不會把Include資料存在中葉層

適合Covering Index很適合用在查出來Column不需要當作Key

案例解說

樣本資料一樣使用上一篇的資料

1
2
3
4
5
6
7
SELECT *
FROM dbo.T
WHERE id = 10000

SELECT *
FROM dbo.T with(index(IX_T_Id))
WHERE id = 10000

有兩段語法一段是有使用Hint,執行出來後會有兩個執行計畫.

第一個執行計畫是上面的語法,第二個執行計畫是下面的語法

建立完Convering Index後我們使用的查詢就會變成只使用Seek,而且在執行成本也大幅降低.

Convering Index有幾個缺點

  1. 假如在此次update有包含index include columns時,此次修改也會對於Index子頁層進行資料更新,這會增加I/O和Transaction log.
  2. 因為會把include columns增加在NonClustered Index子頁層這會增加硬碟儲存Index的額外空間.

所以建議只新增有用到的include columns.

Covering欄位只會在子頁層儲存資料,並不會在中葉層儲存相關資訊。

儲存方式如下圖會把資料存在子頁層中,並不會把Include資料存在中葉層

Filter Index

在SQL-Server 2008之後,支援使用filter index.他可以節省index大小和維護成本

Filter Index語法就是在最後寫where條件

1
2
3
4
5
6
7
CREATE NONCLUSTERED INDEX FIX_T_Id_UserGroup on  dbo.T(
id
) INCLUDE (
UserId,
UserGroup
)
where UserGroup = 8

上面語法意思是只針對於UserGroup = 8Row建立資料在子頁層,Filter Index主要是提升維護性和降低Index大小.

filter index的限制

  1. filter index只支援簡單過濾條件,在where查詢如果有使用到OR、function、計算欄位,可能會讓filter index失效
  2. 因為sql-server會cache執行計畫,所以filter index無法在參數化查詢發揮作用

關於第二點我們可以看下面查詢,假如我們建立一個fitler index(IDX_Data_Unprocessed_Filtered)因為我們使用參數化查詢所以導致此index無法正常發揮

1
2
3
4
5
6
7
8
9
create nonclustered index IDX_Data_Unprocessed_Filtered
on dbo.Data(RecId)
include(Processed)
where Processed = 0;

select top 1000 RecId
from dbo.Data
where Processed = @Processed
order by RecId;

所以假如此查詢有使用到filter index請在查詢使用硬變數或是可以使用option(recompile)不讓執行計畫被cache.

1
2
3
4
5
6
7
8
9
10
select top 1000 RecId
from dbo.Data
where Processed = 0
order by RecId;

select top 1000 RecId
from dbo.Data
where Processed = @Processed
order by RecId;
option(recompile)

注意:如果有使用到Filter IndexSPScript,如果沒有加上SET QUOTED_IDENTIFIER ON就會造成錯誤,所以在撰寫Script時要養成加上面語法的好習慣.

Index Intersection

SQL-Server可透過多個Index完成一段查詢(通常選擇子集合較小)在透過JOIN完成查詢

假如我們有兩個一個查詢會用到UserIdId Column當作條件我們可能會建立下面這個索引.

1
2
3
4
CREATE CLUSTERED INDEX IX_T_UserId_Id on dbo.T(
UserId,
Id
)

但除了同時利用UserIdId Column當作條件外還可能個別當作查詢條件.

我們就可以考慮把這個Index拆開成兩個,這樣可以提高索引使用率(因為執行計畫透過統計值來產生,而Index統計值計算是由Index第一個Column來當計算)

注意:把Index拆成兩個或許可以增加查詢效率,但每個Index就是一個B+ Tree,這會造成維護上成本

1
2
3
4
5
6
7
CREATE CLUSTERED INDEX IX_T_UserId on dbo.T(
UserId
)

CREATE CLUSTERED INDEX IX_T_Id on dbo.T(
Id
)

Primary Key

SQL-Server很常使用PRIMARY KEY但你知道他代表甚麼含意嗎?

PRIMARY KEY是也是一個Index,他可以設定NonClustered Index或是Clustered Index

PRIMARY KEY有幾個特徵

  1. 資料不能重複(Unique)
  2. Columns都必須定義成NOT NULL
  3. PRIMARY KEY是一個Index

預設建立的PRIMARY KEY是Clustered Index,但我們使用語法自行建立為NonClustered Index的PRIMARY KEY

如下範例我們可以建立一個NONCLUSTERED的PRIMARY KEY

1
2
3
4
5
6
CREATE TABLE T(
ID INT NOT NULL,
CONSTRAINT [PK_T] PRIMARY KEY NONCLUSTERED (
ID
)
)

選擇Clustered Index要訣

在 SQL-Server 每張資料表只有一個 Clustered Index,每個資料表只能有一個Cluster index,資料表會依照cluster index方式排列,Clustered Index跟資料一起放置在left pag子頁層,Cluster index好比書籍目錄。每本書只能有一個目錄

所以選擇合適 Clustered Index 就尤為重要

建立Clustered Index欄位有幾個重點

  1. 常用於查詢欄位
  2. 可識別度高(唯一性較高 or 密度高)
  3. 避免過多欄位在Clustered Index中(因為NonClustered會包含Clustered Index Key)

我個人建議選擇 Clustered Index 可以依照下面幾個準則

  • 最好是唯一性:提高查詢效率
  • 寬度窄或窄複合組成:因為NonClustered Index中頁層會包含Clustered Index資訊,如果Clustered Index太肥會造成NonClustered Index page變多.
  • 靜態性:Clustered Index盡量少被Update(更新時需要一併更新NonClustered Index中Clustered Key資料,會造成I/O效能消耗)
  • 連續性佳:避免索引破碎(能避免就避免使用GUID當Clustered Index)

假如確定建立的Clustered index資料是唯一的,請加上Unique,因為沒加上UniqueClustered index會在Page中的每列資料加上一個uniquifiers 2 bytes的Column

下圖是我使用DBCC PAGE查看三種不一樣的Clustered Index結果

其中第一,第二個結果集顯示就算Clustered Index資料不重複沒加上Unique對於儲存上會有差異

所以確定建立的Clustered index資料是唯一的,請加上Unique

此文作者:Daniel Shih(石頭)
此文地址https://isdaniel.github.io/dbindex-2/
版權聲明:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!


如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^