第 12 章規劃與建立索引

第12章規劃與建立索引 • 12-1 索引的基礎 • 12-2 資料表的索引規劃 • 12-3 SQL Server自動建立的索引 • 12-4 建立資料表的索引 • 12-5 修改、重建與刪除索引 • 12-6 檢視SQL Server的執行計劃 • 12-7 建立檢視表與計算欄位的索引 • 12-8 篩選索引

12-1 索引的基礎 • 12-1-1 索引簡介 • 12-1-2 索引的種類 • 12-1-3 SQL Server的索引結構

12-1-1 索引簡介-說明 • 索引（Index）可以幫助資料庫引擎在磁碟中定位記錄資料，以便在資料表的龐大資料中加速找到資料。換句話說，建立資料表的索引可以提昇SQL查詢效率，讓我們更快取得資料庫的查詢結果。 • 在資料表建立索引需要額外的參考資料，資料庫管理系統可以將資料表的部分欄位資料預先進行排序，此欄位稱為「索引欄位」（Index Columns），索引欄位值稱為鍵值（Key Value）。

12-1-1 索引簡介-圖例 • 一般來說，索引資料包含兩個欄位值：一為索引欄位；一為指標（Pointer）欄位，它是指向對應到資料表記錄位置的值，如下圖所示：

12-1-2 索引的種類-主索引 • 主索引（Primary Index）就是將資料表的主索引鍵建立成索引，一個資料表只能擁有一個主索引。在資料表建立主索引的索引欄位，欄位值一定不能重覆，即欄位值是唯一，而且不允許是空值（NULL）。 • 在主索引的索引欄位可以是一個或多個欄位的組合，如果是由多個資料表欄位所組合，稱為複合索引（Composite Index）或結合索引（Concatenated Index），在主索引的複合索引中，個別欄位允許重複值，但是整個組合值仍然需要是唯一值。

12-1-2 索引的種類-唯一索引 • 唯一索引（Unique Index）的欄位值也是唯一的，不同於主索引只能有一個，在一個資料表可以擁有多個唯一索引，這也是與主索引唯一的差別。

12-1-2 索引的種類-一般索引 • 一般索引（Regular Index）的索引欄位值並不需要是唯一的，其主要目的是加速資料表的搜尋與排序。在一個資料表可以擁有多個一般索引。換句話說，我們可以在資料表選擇一些欄位來建立一般索引，其主要目的就是在增進查詢效能。

12-1-3 SQL Server的索引結構-M路搜尋樹(說明) • B樹（B-Trees）是資料結構的一種樹狀搜尋結構，它是擴充自二元搜尋樹的一種平衡的M路搜尋樹。 • M路搜尋樹（M-way Search Trees）是指樹的每一個節點都擁有至多M個子樹和M-1個鍵值，鍵值是以遞增方式由小至大來排序，其節點結構如下圖所示：

12-1-3 SQL Server的索引結構-M路搜尋樹(圖例) • 例如：四路搜尋樹的每一個節點最多有3鍵值和4個子樹，如下圖所示：

12-1-3 SQL Server的索引結構-B樹 (說明) • B樹（B-Tree）屬於一種樹狀搜尋結構，它是擴充自二元搜尋樹的一種平衡的M路搜尋樹。M為B樹的度數（Order），由Bayer和McCreight提出的一種平衡的M路搜尋樹，其定義如下所示： • B樹的每一個節點最多擁有M個子樹。 • B樹根節點和葉節點之外的中間節點，至少擁有ceil(M/2)個子節點，ceil()函數可以大於等於參數的最小整數，例如：ceil(4) = 4、ceil(4.33) = 5、ceil(1.89) = 2和ceil(5.01) = 6。 • B樹的根節點可以少於2個子節點。葉節點至少擁有ceil(M/2) - 1個鍵值。 • B樹的所有葉節點都位在樹最底層的同一階層（Level），換句話說，從根節點開始走訪到各葉節點所經過的節點數都相同，它是一棵相當平衡的樹狀搜尋結構。

12-1-3 SQL Server的索引結構-B樹 (圖例) • 例如：一棵度數5的B樹，所有中間節點至少擁有ceil(5/2) = 3個子節點（即至少2個鍵值），最多5個子節點（4個鍵值），葉節點至少擁有2個鍵值，最多為4個鍵值，如下圖所示：

12-1-3 SQL Server的索引結構-SQL Server的索引結構(說明) • SQL Server索引結構（Index Organization）是組成索引分頁的方法，可以分為叢集索引和非叢集索引兩種。 • 在SQL Server資料表只能擁有一個叢集索引，通常就是主索引，主索引的索引欄位可以是單一欄位，或多欄位的複合索引。 • 在一個資料表可以擁有多個非叢集索引，它可以是唯一索引或一般索引，當然也可以是多索引欄位的複合索引。

12-1-3 SQL Server的索引結構-SQL Server的索引結構(叢集索引) • 叢集索引（Clustered Indexes）是一種B樹結構，當SQL Server資料表建立叢集索引後，資料表的記錄資料會依叢集索引欄位的鍵值來排序，如下圖所示：

12-1-3 SQL Server的索引結構-SQL Server的索引結構(非叢集索引-說明) • 非叢集索引（Nonclustered Indexes）是一種類似叢集索引的B樹結構，其差異在於資料表的記錄並不會依據非叢集索引的鍵值來排序，而且非叢集索引的葉節點是索引分頁，並不是資料分頁。 • 非叢集索引葉節點的索引分頁內容是非叢集索引鍵值，和指向資料表記錄的記錄定位（Row Locator）指標。在叢集資料表建立非叢集索引，因為資料表本身已經擁有叢集索引，所以葉節點的索引分頁中，記錄定位值是對應的叢集索引鍵值。

12-1-3 SQL Server的索引結構-SQL Server的索引結構(非叢集索引-圖例)

12-2 資料表的索引規劃 • 12-2-1 索引的優缺點 • 12-2-2 建立索引的注意事項 • 12-2-3 選擇索引欄位

12-2-1 索引的優缺點 • 索引的優點：索引可以加速資料存取，因為不用一筆一筆比較來搜尋記錄，資料庫引擎可以透過索引結構來快速找到指定記錄，它能夠讓SQL語言的合併查詢、排序和群組操作更加的有效率。 • 索引的缺點：在資料表建立索引需要額外的磁碟空間和維護成本，因為資料表在插入、更新和刪除記錄時，資料庫引擎需要花費額外時間和資源來更新索引資料。

12-2-2 建立索引的注意事項-建立索引的限制條件 • 因為資料表的記錄資料是使用叢集索引的順序來排列，所以SQL Server資料庫的每一個資料表只能建立一個叢集索引，但是可以在資料表的多個欄位建立多個非叢集索引。 • 在一個資料表最多只能有一個叢集索引和249個非叢集索引。 • 複合索引欄位數最多只能有16個欄位。 • 單一索引欄位或複合索引欄位的總長度需在900位元組以內，而且不能替ntext、text和image資料型別的欄位建立索引。

12-2-2 建立索引的注意事項-如何建立複合索引 • 複合索引是指索引欄位超過一個的索引，我們可以選擇資料表的多個欄位集合來建立複合索引。一般來說，在資料表應該儘量避免建立複合索引，而是以多個單一欄位索引來取代，因為複合索引的索引欄位尺寸通常比較大，需要更多的磁碟讀取，進而影響整體的執行效能。 • 不只如此，SQL Server複合索引在使用上有一些限制，只有當SELECT指令的WHERE子句使用第1個欄位進行查詢時，才會使用複合索引來增加查詢效率。

12-2-3 選擇索引欄位-應該作為索引的欄位 • 對於資料表中查詢頻繁的欄位，我們應該替這些欄位建立索引，例如：主鍵、外來鍵、經常需要合併查詢的欄位、排序欄位和需要查詢指定範圍的欄位。 • 一般來說，資料表的主鍵建議建立叢集索引（SQL Server預設會自動建立），其他欄位建立成非叢集索引。

12-2-3 選擇索引欄位-不應該作為索引的欄位 • 對於資料表查詢時很少參考到的欄位、大量重複值欄位（例如：欄位值只有男或女）或bit、text與image等資料型別的欄位，就不應該替它們建立索引。

12-3 SQL Server自動建立的索引 • 12-3-1 PRIMARY KEY欄位的索引 • 12-3-2 UNIQUE欄位的索引

12-3 SQL Server自動建立的索引 • 當在SQL Server資料庫建立資料表時，資料表指定為PRIMARY KEY或UNIQUE的欄位，SQL Server都會自動替這些欄位建立索引。

12-3-1 PRIMARY KEY欄位的索引 • 在建立資料表時指定為PRIMARY KEY的欄位（即主索引鍵或稱為主鍵），SQL Server預設將它自動建立成叢集索引，換句話說，資料表的記錄是使用主索引鍵欄位值來排列。 • 例如：【學生】資料表指定【學號】欄位的主索引鍵，預設就會建立此欄位的叢集索引。

12-3-2 UNIQUE欄位的索引-說明 • 在建立資料表時指定為UNIQUE的欄位，SQL Server都會預設自動建立成非叢集索引，其型別是唯一索引鍵，表示欄位值需要是唯一。

12-3-2 UNIQUE欄位的索引-範例 SQL指令碼檔：Ch12-3-2.sql • 在【教務系統】資料庫新增【熱銷產品】資料表，內含UNIQUE的【產品名稱】欄位，如下所示： CREATE TABLE 熱銷產品 ( 產品編號 char(5) NOT NULL PRIMARY KEY , 產品名稱 varchar(30) UNIQUE , 定價 money )

12-4 建立資料表的索引 • 12-4-1 使用Management Studio建立索引 • 12-4-2 使用T-SQL指令建立索引

12-4-1 使用Management Studio建立索引-選擇索引欄位 • Management Studio提供圖形化介面來建立資料表的索引。例如：替【學生】資料表建立【姓名】欄位的非叢集索引，如下圖所示：

12-4-1 使用Management Studio建立索引-選擇內含欄位 • 接著加入索引內含的欄位，請在左邊選【內含資料行】頁面。按【加入】鈕新增索引內含的欄位，可以看到勾選資料表欄位的對話方塊。

12-4-1 使用Management Studio建立索引-內含欄位說明 • 在資料表建立非叢集索引時，建議新增內含欄位，以便讓SQL Server能夠最佳化索引的使用。例如：查詢學生生日的SELECT指令，如下所示： SELECT 生日 FROM 學生 WHERE 姓名 = '陳會安' • 上述SELECT指令可以使用【姓名_索引】的非叢集索引，因為有在索引包含【生日】欄位。 • 如果沒有包含【生日】欄位且沒有【生日】欄位的索引，SQL Server仍然會使用主索引的叢集索引來進行搜尋，而不會使用【姓名_索引】的非叢集索引。

12-4-2 使用T-SQL指令建立索引-語法 • 在T-SQL語言是使用CREATE INDEX指令建立資料表的索引，其基本語法如下所示： CREATE [ UNIQUE ] [ CLUSTERED ] INDEX 索引名稱 ON 資料表名稱 (欄位名稱[(長度)][ ASC | DESC ][,..n] ) [ INCLUDE (欄位清單) ] [ WITH 索引選項 ] [ ON filegroup_name ]

12-4-2 使用T-SQL指令建立索引-索引選項 • 在WITH子句可以指定索引選項，如果有多個，請使用「,」逗號分隔。常用的索引選項說明，如下表所示：

12-4-2 使用T-SQL指令建立索引-範例1 SQL指令碼檔：Ch12-4-2-01.sql • 在【教務系統】資料庫的【員工】資料表新增【姓名】欄位的非叢集索引【員工姓名_索引】，索引包含【電話】和【薪水】欄位，如下所示： CREATE INDEX 員工姓名_索引 ON 員工(姓名) INCLUDE (電話, 薪水)

12-4-2 使用T-SQL指令建立索引-範例2 SQL指令碼檔：Ch12-4-2-02.sql • 在【教務系統】資料庫的【課程】資料表新增【名稱】和【學分】欄位的非叢集索引【名稱學分_索引】，這是一個唯一的複合索引，如下所示： CREATE UNIQUE INDEX 名稱學分_索引 ON 課程(名稱, 學分)

12-5 修改、重建與刪除索引 • 12-5-1 使用Management Studio修改與重建資料表的索引 • 12-5-2 使用T-SQL指令修改與重建索引 • 12-5-3 刪除資料表的索引

12-5-1 使用Management Studio修改與重建資料表的索引-方法1 • 在「物件總管」視窗【員工姓名_索引】上，執行【右】鍵快顯功能表的【屬性】指令，可以看到「索引屬性」對話方塊來修改索引。

12-5-1 使用Management Studio修改與重建資料表的索引-方法2 • 在修改資料表欄位定義資料時，執行「資料表設計工具」→「索引/索引鍵」指令，開啟「索引/索引鍵」對話方塊來修改索引，如下圖所示：

12-5-1 使用Management Studio修改與重建資料表的索引-重建索引 • 如果需要重建資料表的索引，請在Management Studio「物件總管」視窗的索引上，執行【右】鍵快顯功能表的【重建】指令，就可以重建索引。

12-5-2 使用T-SQL指令修改與重建索引-修改索引(說明) • T-SQL語言修改索引仍然是使用CREATE INDEX指令，只是加上DROP_EXISTING選項。 • 在CREATE INDEX指令建立索引時，如果加上DROP_EXISTING選項，表示我們準備建立新索引來取代同名的存在索引。請注意！當加上DROP_EXISTING選項時，一定需要存在同名的索引，如果索引不存在，執行時就會產生錯誤。

12-5-2 使用T-SQL指令修改與重建索引-修改索引(範例) SQL指令碼檔：Ch12-5-2-01.sql • 在【教務系統】資料庫的【員工】資料表修改【員工姓名_索引】索引，將它改為唯一索引、加上IGNORE_DUP_KEY選項和新增包含【城市】欄位，如下所示： CREATE UNIQUE INDEX 員工姓名_索引 ON 員工(姓名) INCLUDE (電話, 薪水, 城市) WITH IGNORE_DUP_KEY, DROP_EXISTING

12-5-2 使用T-SQL指令修改與重建索引-重建索引(語法) • T-SQL的ALTER INDEX指令可以停用、重建索引、重組索引或設定索引選項來修改存在的索引，其基本語法如下所示： ALTER INDEX 索引名稱 [ALL] ON 資料表名稱 [ REBUILD [WITH (索引選項清單)] | REORGANIZE | DISABLE | SET (索引選項清單)]

12-5-2 使用T-SQL指令修改與重建索引-重建索引(範例1) SQL指令碼檔：Ch12-5-2-02.sql • 重建【教務系統】資料庫【員工】資料表的所有索引，並且將填滿因數改為80%，如下所示： ALTER INDEX ALL ON 員工 REBUILD WITH (FILLFACTOR = 80)

12-5-2 使用T-SQL指令修改與重建索引-重建索引(範例2) SQL指令碼檔：Ch12-5-2-03.sql • 在【教務系統】資料庫停用【員工】資料表的【員工姓名_索引】索引，如下所示： ALTER INDEX 員工姓名_索引 ON 員工 DISABLE

12-5-3 刪除資料表的索引-使用Management Studio • 在Management Studio刪除資料表的索引，請在「物件總管」視窗的索引上，執行【右】鍵快顯功能表的【刪除】指令來刪除索引。 • 我們也可以在「索引/索引鍵」對話方塊刪除索引，請在左邊選取索引後，按下方的【刪除】鈕來刪除資料表的索引。

12-5-3 刪除資料表的索引-使用T-SQL指令 • T-SQL語言的DROP INDEX指令可以刪除資料表的索引，其基本語法如下所示： DROP INDEX 資料表名稱1.索引名稱1 [, 資料表名稱2.索引名稱2, …] SQL指令碼檔：Ch12-5-3.sql • 在【教務系統】資料庫刪除【員工】資料表的【員工姓名_索引】索引，如下所示： DROP INDEX 員工.員工姓名_索引

12-6 檢視SQL Server的執行計劃-說明 • 在Management Studio工具可以使用圖形化方式來顯示SQL Server執行計劃，執行計劃是SQL Server查詢最佳化模組選擇的資料擷取方法，我們可以透過檢視執行計劃來了解查詢特性，並且幫助我們進行查詢最佳化。 • Management Studio的估計執行計劃並不會真的執行T-SQL查詢或批次。不過，此估計執行計劃仍有可能是資料庫引擎最後使用的執行計劃。

12-6 檢視SQL Server的執行計劃-圖例

12-7 建立檢視表與計算欄位的索引 • 12-7-1 建立計算欄位的索引 • 12-7-2 建立檢視表索引

12-7-1 建立計算欄位的索引-說明 • 在資料表新增計算欄位就是為了建立計算欄位的索引，例如：查詢【估價單】資料表平均單價在100和200元之間的記錄資料，如下所示： SELECT 產品編號 FROM 估價單 WHERE (總價 / 數量) BETWEEN 100.00 AND 200.00 • 在WHERE子句條件因為擁有運算式，當【估價單】資料表的記錄資料十分龐大時，我們就需要在資料表新增計算欄位【平均單價】（即SQL指令碼：Ch7-4-1.sql），然後建立此計算欄位的索引來增進查詢效率。

12-7-1 建立計算欄位的索引-需求條件1 • 擁有權需求（Ownership Requirements）：計算欄位與資料表必須是同一位擁有者，即計算欄位的運算式不能使用其他資料表的欄位。 • 決定性需求（Determinism Requirements）：計算欄位的值是由運算式的欄位值決定，只需輸入的欄位值相同，就會輸出相同的結果。 • 精確性需求（Precision Requirements）：計算欄位的運算式結果需要是精確的（Precise），也就是沒有使用float或real型別的欄位。

第 12 章 規劃與建立索引