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

Presentation on theme: "SQL Server 2005 Query Optimizer 善用索引加快查詢效率 (參考實習課課本第 13 章)"— Presentation transcript:

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

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

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

4 索引的結構

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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