原文: 怎樣才能充分利用SQL索引

???? 背景: 目前WEB的普及太快,很多網(wǎng)站都會因為大流量的數(shù)據(jù)而發(fā)生服務(wù)器習(xí)慣性死機(jī),一個查詢語句只能適用于一定的網(wǎng)絡(luò)環(huán)境.沒有優(yōu)化的查詢當(dāng)遇上大數(shù)據(jù)量時就不適用了.

???? 本文主旨 :討論什么情況下能利用上索引.

???? 索引 :創(chuàng)建索引可以根據(jù)查詢業(yè)務(wù)的不同分為兩種:單一列的索引,聯(lián)合索引. 顧名思義,單一列索引就是指在表的某一列上創(chuàng)建索引,聯(lián)合索引是在多個列上聯(lián)合創(chuàng)建索引.

???? 優(yōu)缺點比較:

???????? 1):索引所占用空間:單一列索引相對要小.

???????? 2):索引創(chuàng)建時間:單一列索引相對短.

? ? ? ?? 3):索引對insert,update,delete的影響程序:單一列索引要相對低.

? ? ? ?? 4):在多條件查詢時,聯(lián)合索引效率要高.

??? 索引的使用范圍: 單一列索引可以出現(xiàn)在where 條件中的任何位置,而聯(lián)合索引需要按一定的順序來寫.

? ? 本文所用測試軟件環(huán)境如下:SQL05 ?

? ? ? DEMO: 創(chuàng)建一個人員表,包含人員ID,姓名.在人員ID上創(chuàng)建一個聚集索引,在first_name和last_name上創(chuàng)建一個聯(lián)合

索引.?

create table person (id int, last_name varchar(30), first_name varchar(30))
create unique clustered index person_id on person (id)
create index person_name on person (last_name, first_name)

????

? ?? 在上例中,id上創(chuàng)建了聚集索引,下面的查詢都會用了聚集索引.

??? where id=1
??? where id>1
??? where id<1
??? where id between 1 and n
??? where id like '1%'

??? where id in(1,2,3...)

? ?? 說明:? id 列出現(xiàn)在條件中的位置并不一定要求第一列,不受位置影響.

???? 不過下面的查詢方式則不會用上聚集索引.
??? where person_id +1=n
??? where person_id like '%5'
??? where person_id like '%5%'
??? where person_id abs(15)

??? 聯(lián)合索引列比起單一列索引最大的好處在于,對于多條件的查詢它比起單一列索引更加精確.拿上面的人員表來說吧,如果

要查詢一個人的全名,只知道first_name是很難馬上找到這個人的全名的,如果知道first_name和last_name則會非常容易找到.下面根據(jù)不同的條件與輸出列順序說明索引的應(yīng)用.

???? 第一種情況: --條件和輸出列和索引列順序相同
select last_name,first_name from person where last_name='1' and first_name='1'
stmtText
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]

AND [bdg_web_vaction].[dbo].[person].[first_name]=[@2]) ORDERED FORWARD)

????? 結(jié)果:利用person_name聯(lián)合索引查找

????? 第二種情況: --條件列與索引列順序不同,但輸出列相同
select last_name,first_name from person where first_name='1' and last_name='1'
stmtText
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@2] AND [bdg_web_vaction].
[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

?

?????? 結(jié)果:利用person_name聯(lián)合索引查找

?????? 第三種情況: --條件列與輸出列與索引列的順序都不相同
select first_name,last_name from person where first_name='1' and last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
SEEK:([bdg_web_vaction].[dbo].[person].
[last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

?

??????? 結(jié)果:利用person_name聯(lián)合索引查找

??????? 第四種情況: --條件列在first_name和last_name中間加入另外一個條件
SELECT id, first_name,last_name from person where first_name='1' AND? id=1 and last_name='1'
???? Clustered Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_id]),
?SEEK:([bdg_web_vaction].[dbo].[person].[id]=CONVERT_IMPLICIT(int,[@2],0)), ?
WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1] AND [bdg_web_vaction].[dbo].[person].[las

??????? 結(jié)果:不能利用person_name聯(lián)合索引查找

?????? 第五種情況: --在輸出列中分開first_name和last_name
SELECT? first_name, id ,last_name from person where first_name='1' and last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
?SEEK:([bdg_web_vaction].[dbo].[person].
[last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1])
?ORDERED FORWARD)

?

?????? 結(jié)果:利用person_name聯(lián)合索引查找

?????? 第六種情況: 條件列沒有出現(xiàn)聯(lián)合索引的第一列
SELECT? first_name,id,last_name from person where first_name='1'
SELECT? first_name,last_name from person where first_name='1'
SELECT? last_name ,first_name from person where first_name='1'

Index Scan(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

? WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1]))

?

?????? 結(jié)果:不能利用person_name聯(lián)合索引.

????? 第七種情況: --條件列出現(xiàn)聯(lián)合索引的第一列
SELECT? first_name,id,last_name from person where last_name='1'
SELECT? first_name,last_name from person where last_name='1'
SELECT? last_name ,first_name from person where last_name='1'
Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),
?SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]) ORDERED FORWARD)

?

????? 結(jié)果:利用person_name聯(lián)合索引查找

? ? ? 聯(lián)合索引使用總結(jié):

???????? 1):查詢條件中出現(xiàn)聯(lián)合索引第一列,或者全部,則能利用聯(lián)合索引.

???????? 2):條件列中只要條件相連在一起,以本文例子來說就是:

????????????? last_name='1' and first_name='1'

????????????? 與

? ? ? ? ? ? ? first_name='1' and last_name='1'

???????????????? ,無論前后,都會利用上聯(lián)合索引.

? ? ? ?? 3):查詢條件中沒有出現(xiàn)聯(lián)合索引的第一列,而出現(xiàn)聯(lián)合索引的第二列,或者第三列,都不會利用聯(lián)合索引查詢.

? ?? 單一列索引的應(yīng)用總結(jié):

????????? 1):只要條件列中出現(xiàn)索引列,無論在什么位置,都能利用索引查詢.

? ? ? 兩者的共同點:

????????? 1):要想利用索引,都要符合 SARG標(biāo)準(zhǔn).

? ? ? ? ? 2) :都是為了提高查詢速度.

????????? 3):都需要額外的系統(tǒng)開銷,磁盤空間.

? ?? 補(bǔ)充說明: stmtText信息來產(chǎn)生,在查詢語句前面加上:SET STATISTICS PROFILE on.可以通過運行它,來觀察你的查詢是否合理,這樣才能真正做到優(yōu)化.

????? 總結(jié): 即使表上創(chuàng)建了索引,但如果查詢語句寫的不科學(xué)的話(不符合 SARG標(biāo)準(zhǔn) ),也于事無補(bǔ),要根據(jù)表索引情況來優(yōu)化查詢語句,如沒有合適的索引可用,則要創(chuàng)建相應(yīng)索引.

?

?

? ? ? ? ??

?

??

?

怎樣才能充分利用SQL索引