[MSSQL] 索引入門 (index)

[MSSQL] 索引入門 (index)

是否每次下Query都會覺得很慢,明明就已經是最優化的寫法了

但還是慢得不像話,其中一個可能的原因是沒建索引。

本篇文章是要討論如何查看執行計畫與索引建置的 - 初章,高手請略過。

大綱

1.如何打開執行計畫

2.圖形執行計畫圖示

3.怎看出有無建立索引

4.執行計畫怎麼看 (簡單效能調校)

5.索引命名

6.索引語法

 

1.如何打開執行計畫 (實際)

 

不過我個人習慣直接看預估執行計畫,框語法後輸入 CTRL + L。

 

2.圖形執行計畫圖示 (完整圖示請看:執行計畫圖示)

圖示 運算子 說明
叢集索引掃描運算子圖示 叢集索引掃描 有建索引,但是仍然是掃描
叢集索引搜尋運算子圖示 叢集索引搜尋 有建索引,且有使用索引
非叢集索引掃描運算子圖示 非叢集索引掃描 有建索引,但是仍然是掃描
非叢集索引搜尋運算子圖示 非叢集索引搜尋 有建索引,且有使用索引
資料表掃描運算子圖示 資料表掃描 純堆疊,沒建索引的圖

 

掃描:指資料表從頭到尾跑一次,去抓資料,是最慢的方法 (叢集索引掃描、非叢集索引掃描、資料表掃描)

搜尋:使用索引進行資料的查詢,是有效的查詢方式 (叢集索引搜尋、非叢集索引搜尋)

Q:為什麼有建立索引還會掃描?

A:有很多因素:如索引建立的欄位不洽當、此查詢為基底資料表、select *、查詢條件 (where/ join) 沒有使用到...等。

 

 

3.怎看出有無建立索引 (擇一)

(1) 點開table,再點開索引

 

(2) 打開設計頁面,對左方案右鍵,選「索引/索引鍵」

 

(3) 語法下: sp_helpindex 資料表名

 

 

4.執行計畫怎麼看 (簡單效能調校)

 

解說:

執行計畫是從右邊執行到左邊,所以右邊為起點,左邊為結果。

一般我們在看成本會先看最左邊 [SELECT] 的 [估計的子樹成本]

成本是微軟對於資源使用量的一個估計單位,原則上是越低越好

另一個輔助的計量是 [快取的計畫大小],會隨著索引與資料量而提升,避免不必要的欄位讀取可有效降低快取,此單位在成本評估之後,也是越低越好。

 

解說:

一般我們在執行 Query,就是一個批次

所以單一語法執行時,最上方的 [查詢成本 (相對於批次)] 會是 100%

多個語法則會依照該語法,對整個批次所佔的成本比例去做百分比。

---

而執行圖示裡的 [成本],則是各佔該語法的百分比成本

如中我們可以看到2個資料表做 [資料表掃描],表示沒有建立任何索引

然後在 [雜湊比對] 佔了相當高的比重(73%)

表示他必須在第一張表的每一筆資料,做第2張表的掃描

問題很明顯:缺乏索引所造成的效能瓶頸。

我們分別為 Temp_sqlPlan 和 Temp_sqlPlanDetail 建立關聯欄位的索引


create clustered index CX_Temp_Temp_sqlPlanDetail_sqlPlanID on Temp_sqlPlanDetail(sqlPlanID)
GO

 

然後查詢:

建立索引後

我們的執行計畫成本由 0.028 降為 0.014

雜湊比對圖示也變為巣狀迴圈 (不在是最重的成本)

這就是我們要的。

 

5.索引命名

主鍵:PK_資料表名稱_欄位名稱,如:PK_sysuser_u_no

叢集索引:CX_資料表名稱_欄位名稱,如:CX_sysuser_u_no

非叢集索引:IX_資料表名稱_欄位名稱,如:IX_sysuser_u_no

Q:叢集與非叢集的差異是?

A:

1.一個表只能有一個叢集,有多個非叢集。

2.叢集會對實體資料進行排序,非叢集只會紀錄欄位地址(查閱叢集索引)

想深入了解請看:認識【叢集索引】與【非叢集索引】

 

6.索引語法


alter table sysuser drop constraint PK_sysuser

--建立主鍵 [alter table 資料表名稱 add constraint 主鍵名稱 primary key (欄位名稱)]
alter table sysuser add constraint PK_sysuser_autoid primary key (autoid)

--移除叢集/非叢集索引 [drop index 資料表名稱.索引名稱]
drop index sysuser.CX_sysuser_u_no 
 

--建立叢集索引 [create 索引類別 index 索引名稱 on 資料表名稱.索引名稱]
create clustered index CX_sysuser_u_no on sysuser(years desc, trsno desc)

 

創用 CC 授權條款
本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權,文章歡迎轉載,請註明出處,謝謝~~~