[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 姓名標示-相同方式分享 4.0 國際 授權條款授權,文章歡迎轉載,請註明出處,謝謝~~~