Agenda

前文
Covering Index
- 加入INCLUDE欄位含意
- 案例解說
Filter Index
filter index的限制
Index Intersection
Primary Key
選擇Clustered Index要訣

前文

本系列文章

兩種基本索引

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

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

RID Lookup
Key Lookup

本篇會介紹其他種類Index

Covering Index

我們先來看看Covering Index語法.

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

CREATE NONCLUSTERED INDEX IX_T_Id_Convering on dbo.T(
	id
) INCLUDE (
	UserId,
	UserGroup
)

加入INCLUDE欄位含意

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

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

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

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

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

案例解說

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

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有幾個缺點

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

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

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

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

Filter Index

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

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

CREATE NONCLUSTERED INDEX FIX_T_Id_UserGroup on  dbo.T(
	id
) INCLUDE (
	UserId,
	UserGroup
)
where UserGroup = 8

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

filter index的限制

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

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

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.

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 Index的SP或Script，如果沒有加上SET QUOTED_IDENTIFIER ON就會造成錯誤，所以在撰寫Script時要養成加上面語法的好習慣.

Index Intersection

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

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

CREATE CLUSTERED INDEX IX_T_UserId_Id on dbo.T(
	UserId,
	Id
)

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

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

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

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有幾個特徵

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

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

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

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欄位有幾個重點

常用於查詢欄位
可識別度高(唯一性較高 or 密度高)
避免過多欄位在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,因為沒加上Unique的Clustered 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 許可協議。轉載請註明出處！

Hello World

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