原文: SQL開發中容易忽視的一些小地方(五)

??? 背景:

?????????? 索引分類：眾所周知,索引分為聚集索引和非聚集索引。

?????????? 索引優點：加速數據查詢。

?????????? 問題：然而我們真的清楚索引的應用嗎?你寫的查詢語句是否能充分應用上索引,或者說你如何設計你的索引讓它更高效？

?????????? 經歷:以前本人只知道索引的好處,但是是否能夠真正讓它發揮作用,并無太多理論,為些本人做了些DEMO,來簡單說明下什么情況下才能充分利用索引。

? ? 案例:

?????????? 這里建立一個學生表:有如下字段,此時表中沒有建立任何索引。

CREATE TABLE [dbo].[student](
?? ?[ID] [int] IDENTITY(1,1) NOT NULL,--學生ID
?? ?[sUserName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,--學生姓名
?? ?[sAddress] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,--學生地址
?? ?[classID] [int] NULL,--學生所屬班級ID
?? ?[create_date] [datetime] NULL CONSTRAINT [DF_student_create_date]? DEFAULT (getdate()) --入校時間

) ON [PRIMARY]
?

???? 業務需求:

??????????? 查詢班級ID為9的所有學生的姓名和地址。

???? 情況一:
? --字段沒有建立任何索引
select sUserName,sAddress from student

where classID=9

? ? ? ? ? ?? 執行計劃如下圖：

???????????? 結論:在沒有任何索引的情況下,查詢會選擇全表掃描.

?????? 情況二:

???????????????? 給ID自增列創建一個聚集索引,我們很多情況下都是這樣默認的,主鍵上就是聚集索引。同樣的查詢,不同的查詢計劃,發現此時雖然在輸出列和條件中沒有ID,但是查詢選擇了聚集查詢.

???????????????? 執行計劃圖同圖一。

?????? 情況三: 在classID上創建非聚集索引。

???????????????? 結論:雖然條件列中出現了classID索引列,但是輸出列中并沒有創建任何索引,依然選用聚集掃描方式查詢.
?

?????? 情況四: 在sUserName上創建非聚集索引

??????????????? 結論:同上

?????? 情況五: 繼續在sAddress上創建非聚集索引

??????????????? 結論:同上
?

??????? 情況六: 建立sUserName與sAddress的聯合非聚集索引

???????????????? 結論:同上
?

??????? 情況七: 在classID與sUserName上創建索引

????????????????? 結論:同上
?

??????? 情況八: 在classID,sUserName,sAddress上創建聯合非聚集索引

? ? ? ? ? ? ? ? ?? 執行計劃圖如下：

????????????????? 結論:當條件中出現的列加上輸出列和聯合索引列完全匹配時全用上索引掃描.

???????? 情況九： 刪除所有索引，保留ID的聚集索引。以聚集索引列做為條件之一來查詢.

select sUserName,sAddress from student

where ID=10021002

?????? 或者：select sUserName,sAddress from student
where ID=10021002 and classID=9

? ? ? ? ? ? ? ? ??? 執行計劃圖：

?

? ? ? ? ? ? ? ? ? ??

??????? 所有情況總結:

?????????????? 1:當使用 非 聚集索引掃描時的IO情況:表 'student'。掃描計數 1，邏輯讀取 70 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

?????????????? 2:當使用聚集索引掃描時的IO情況( 條件中未出現聚集索引列 ):表 'student'。掃描計數 3，邏輯讀取 8835 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。?

? ? ? ? ? ? ?? 3:當使用聚集索引掃描時的IO情況（ 條件中出現聚集索引列 ） :表 'student'。掃描計數 1，邏輯讀取 3 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

?????????????? 結論:

??????????????????? 1:在沒有正確的索引情況下,會增加表的掃描次數.

??????????????????? 2:數據在查詢時會先找匹配的索引.
?????????????????????? 1):如果在條件列中出現聚集索引列,則無論輸出列是否建立索引都會按聚集索引查找(有聚集索引 ).
?????????????????????? 2):如果在條件列中沒有出現聚集索引列,則查找匹配的非聚集索引,如果有匹配的索引則按相應索引查詢,否則再掃描聚集索引(有聚集索引 ).

?????????????????????? 3):查找匹配的非聚集索引(沒有聚集索引 ).

? ? ? 本文總結：

????????????????? 我只是簡單的寫了些關于索引使用的DEMO，在實際開發中要按實際情況來分析，有時并不能完全使用上索引，但是可以讓查詢產生最少的IO讀取以及表掃描次數。

SQL開發中容易忽視的一些小地方(五)