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

??? 背景:

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

?????????? 索引優(yōu)點(diǎn)：加速數(shù)據(jù)查詢。

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

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

? ? 案例:

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

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

) ON [PRIMARY]
?

???? 業(yè)務(wù)需求:

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

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

where classID=9

? ? ? ? ? ?? 執(zhí)行計(jì)劃如下圖：

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

?????? 情況二:

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

???????????????? 執(zhí)行計(jì)劃圖同圖一。

?????? 情況三: 在classID上創(chuàng)建非聚集索引。

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

?????? 情況四: 在sUserName上創(chuàng)建非聚集索引

??????????????? 結(jié)論:同上

?????? 情況五: 繼續(xù)在sAddress上創(chuàng)建非聚集索引

??????????????? 結(jié)論:同上
?

??????? 情況六: 建立sUserName與sAddress的聯(lián)合非聚集索引

???????????????? 結(jié)論:同上
?

??????? 情況七: 在classID與sUserName上創(chuàng)建索引

????????????????? 結(jié)論:同上
?

??????? 情況八: 在classID,sUserName,sAddress上創(chuàng)建聯(lián)合非聚集索引

? ? ? ? ? ? ? ? ?? 執(zhí)行計(jì)劃圖如下：

????????????????? 結(jié)論:當(dāng)條件中出現(xiàn)的列加上輸出列和聯(lián)合索引列完全匹配時全用上索引掃描.

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

select sUserName,sAddress from student

where ID=10021002

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

? ? ? ? ? ? ? ? ??? 執(zhí)行計(jì)劃圖：

?

? ? ? ? ? ? ? ? ? ??

??????? 所有情況總結(jié):

?????????????? 1:當(dāng)使用 非 聚集索引掃描時的IO情況:表 'student'。掃描計(jì)數(shù) 1，邏輯讀取 70 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

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

? ? ? ? ? ? ?? 3:當(dāng)使用聚集索引掃描時的IO情況（ 條件中出現(xiàn)聚集索引列 ） :表 'student'。掃描計(jì)數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

?????????????? 結(jié)論:

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

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

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

? ? ? 本文總結(jié)：

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

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