SQL Server 2005 Query Optimizer 善用索引加快查詢效率 (參考實習課課本第 13 章) 第 11 章補充 SQL Server 2005 Query Optimizer 善用索引加快查詢效率 (參考實習課課本第 13 章)

本章提要 13-1 索引簡介 13-2 叢集索引與非叢集索引 13-3 Unique 與 Composite 索引 13-4 由系統自動建立的索引 13-5 建立索引的注意事項 13-6 使用 SQL Server Management Studio 管理工具建立索引 13-8 用 SQL 語法處理索引 13-9 檢視查詢的執行計劃

13-1 索引簡介 在資料庫中查詢一筆記錄時, 如果我們將所有的記錄一筆一筆做比對, 就如同要在一堆散亂的書籍中找一本書一樣, 是非常沒有效率的。若是能夠善用索引的功能, 將記錄依照順序排列整齊, 如此就能夠提高查詢的效率。 索引雖可加快搜尋的速度, 但並非資料表的每個欄位都需要建立索引。因為多了索引之後, 當新增、修改、或刪除記錄時, 除了要將異動存入資料表之外, 伺服器還必須付出時間來更新索引, 而且索引也會佔用儲存空間, 因此一般只會建立在經常用來做搜尋的欄位上 (例如經常用在 WHERE 子句中的欄位)。

索引的結構

13-2 叢集索引與非叢集索引 索引可分為叢集索引 (Clustered) 與非叢集索引 (Non-clustered) 二種。設定叢集索引時, 資料本身也會依照該索引的順序來存放, 例如一個資料表中的記錄如下：

叢集索引與非叢集索引 那麼將『ID』欄位設為叢集索引後 此資料表的內容便會自動依照 ID 的大小來排列：

13-3 Unique 與 Composite 索引 不管是叢集索引或非叢集索引, 皆可再依下列二種標準做分類： 索引值是否唯一： 如果索引值設為唯一 (不可重複), 則稱為唯一索引(Unique index), 此與資料表的 Primary key 特性類似。 是否只用單一欄位做索引： 如果您使用兩個或多個欄位組合起來做索引, 則稱為複合索引 (Compos i t e index)。如果複合索引同時也是唯一索引, 那麼多個欄位組合起來的值就不可重複, 但單獨的欄位則允許重複。

13-4 由系統自動建立的索引 在 SQL Server 中, 索引不一定要由資料庫設計者自己建立。如果在建立資料表時, 設定了 Primary key 或 UNIQUE 條件約束, SQL Server 就會自動幫我們建好索引。 UNIQUE 欄位 Primary key 欄位

UNIQUE 欄位 當資料表中有設為 UNIQUE 的欄位時, 則 SQL Server 會用此欄位自動建立一個非叢集索引的唯一索引, 以確保此欄位的唯一性。 查詢索引的分佈情形： dbcc show_statistics(QOPRODUCT,PK__QOPRODUCT__07F6335A) ; 檔案群或資料分割配置名稱： 指定索引要存放在哪一個檔案群組 (File group)。在建立資料庫 (CREATE DATABASE) 時, 每個資料庫都有一個預設的檔案群組稱為 PRIMARY, 另外使用者也可以自行定義新的檔案群組。

Primary key 欄位 當資料表中有設定 Primary key (主索引鍵) 時, 則 SQL Server 會在 Primary key 欄位建立一個叢集索引

13-5 建立索引的注意事項 在資料表中建立索引可以提高搜尋資料的效率, 但是建立索引是有一些限制要注意的。以下是 SQL Server 對索引的限制條件： 一個資料表中只能有一個叢集索引, 因為資料表會依照叢集索引來排列其內的記錄。在必要時,我們可以將多個欄位組合起來做為叢集索引。 一個索引所使用的欄位最多只能包括 16 個欄位, 而且 ntext、text、及 image 型別的欄位不能做為索引。

13-6 使用 SQL Server Management Studio 管理工具建立索引

使用 SQL Server Management Studio 管理工具建立索引

使用 SQL Server Management Studio 管理工具建立索引

使用 SQL Server Management Studio 管理工具建立索引

使用 SQL Server Management Studio 管理工具建立索引

13-8 用 SQL 語法處理索引 這一節我們要使用 SQL 語法來建立、重建與刪除索引。 建立索引的語法 刪除索引 修改或重建索引 (skip)

建立索引的完整語法(參考一下)

刪除索引 不可刪除的索引 資料表中有些特殊的索引是不能用上述語法刪除的, 那就是在資料表中設定 Primary key 或 UNIQUE 條件約束時, 由 SQL Server 自動產生的索引。

13-9 檢視查詢的執行計劃 如果想要看看在建立索引之後, 實際執行查詢時是否有使用到索引, 可以在輸入查詢的 SQL 敘述後, 按下工具列的顯示估計執行計劃鈕 , 便能檢視查詢的方式。我們以 13-6 節建立在員工資料表的姓名索引為例。筆者輸入以下敘述：

檢視查詢的執行計劃 敘述輸入完成後先不要執行查詢, 請按下工具列的顯示估計執行計劃鈕 ：

檢視查詢的執行計劃 如果我們將員工資料表中的索引全部刪除, 然後再執行以上的查詢時：

使用索引加快查詢 我們以課本的例子來展示。 查詢選項請設定 statistics time 及 statistics io 如下， 再比較有加 V_NAME 與 P_PRICE 的索引後，p.444 及 p. 448 之 SQL 查尋效率：