高效率SQL的基礎(chǔ)知識

高效率SQL的基礎(chǔ)知識一個高效率的數(shù)據(jù)庫系統(tǒng)是從兩個方面來評價的：響應(yīng)時間和吞吐量。在應(yīng)用系統(tǒng)開發(fā)階段，由于開發(fā)庫上的數(shù)據(jù)比較少，在SQL語句的編寫上感覺不出各種寫法的性能差異，在將應(yīng)用系統(tǒng)提交實際應(yīng)用后，隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就會成為最需要解決的主要問題之一?？s短系統(tǒng)的的響應(yīng)時時間，增增加操作作的并發(fā)發(fā)度，可可以提高高系統(tǒng)的的吞吐量量。要縮縮短系統(tǒng)統(tǒng)的響應(yīng)應(yīng)時間，就就需要可可以高效效率執(zhí)行行的SQQL語句句。高效效SQLL語句的的基本原原則，是是充分合合理的利利用索引引，避免免表掃描描。理解索引大多數(shù)情況況下，數(shù)數(shù)據(jù)庫使使用索引引來檢索索表，優(yōu)優(yōu)化器根根據(jù)用戶戶定義的的索引來來提高執(zhí)執(zhí)行性能能。但是是，如果果在SQQL語句句的whheree子句中中寫的SSQL代代碼不合合理，就就會造成成優(yōu)化器器忽略索索引而采采用全表表掃描，而而這種SSQL語語句就是是所謂的的劣質(zhì)SSQL語語句。在在編寫SSQL語語句時需需要了解解優(yōu)化器器根據(jù)何何種原則則來使用用索引，這這將有助助于寫出出高性能能的SQQL語句句。ISNUULL與IISNNOTNULLL不要用nuull值值作索引引，任何何包含nnulll值的列列都將不不會被包包含在索索引中以以NULLL值做做條件時時，將無無法使用用包含NNULLL值的列列上的索索引。即即使索引引有多列列這樣的的情況下下，只要要這些列列中有一一列含有有nulll，該該列就會會從索引引中排除除。也就就是說如如果某列列存在空空值，在在使用NNULLL值做條條件時，即使對對該列建建索引也也不會提提高性能能。列的聯(lián)接本節(jié)應(yīng)該闡闡述列被被包含到到表達式式中導(dǎo)致致不能使使用索引引。對于有聯(lián)接接的列，即即使最后后的聯(lián)接接值為一一個靜態(tài)態(tài)值，優(yōu)優(yōu)化器是是不會使使用索引引的。例：假定有有一個職職工表（employee），對于一個職工的姓和名分成兩列存放（FIRST_NAME和LAST_NAME），現(xiàn)在要查詢一個叫比爾.克林頓（BillCliton）的職工。下面是一個個采用聯(lián)聯(lián)接查詢詢的SQQL語句句，selecct**frromempployysswhereefiirstt_naame|||'''||llastt_naame='BBilllCllitoon';;改進方法：：selecct**frromempployyeewhereefiirstt_naame=‘‘Billl’anddlaast__namme==‘CClitton’’帶通配符（%）的like語句selecct**frromempployyeewheerelasst_nnameeliike'%cclitton%%';由于通配符符（%）在在搜尋詞詞首出現(xiàn)現(xiàn)，所以以數(shù)據(jù)庫庫將不使使用laast__namme的索索引。在在很多情情況下可可能無法法避免這這種情況況，但是是一定要要心中有有數(shù)，通通配符如如此使用用會降低低查詢速速度。當通配符出出現(xiàn)在字字符串其其他位置置時，優(yōu)優(yōu)化器就就能利用用索引。在在下面的的查詢中中索引得得到了使使用：selecct**frromempployyeewheerelasst_nnameeliike'c%%';Orderrbyy語句ORDERRBYY語句決決定了數(shù)數(shù)據(jù)庫如如何將返返回的查查詢結(jié)果果排序。OOrdeerbby語句句對要排排序的列列沒有什什么特別別的限制制，也可可以將函函數(shù)加入入列中（象象聯(lián)接或或者附加加等）。任任何在OOrdeerbby語句句的非索索引項或或者有計計算表達達式都將將降低查查詢速度度。需要仔細檢檢查orrderrbyy語句以以找出非非索引項項或者表表達式，它它們會降降低性能能。解決這個問問題的辦辦法就是是重寫oordeerbby語句句以使用用索引，也也可以為為所使用用的列建建立另外外一個索索引，同同時應(yīng)絕絕對避免免在orrderrbyy子句中中使用表表達式。NOTnot((staatuss=''VALLID'')和(sstattus<<>'VVALIID'))哪個效效率更高高？salarry<>>30000和和saalarry<330000orrsaalarry>330000和saalarry<＝＝29999oorssalaary>>=30001(假假設(shè)是整整數(shù))哪哪一個效效率更高高？在wherre子句句使用一一些邏輯輯表達式式，如大大于、小小于、等等于以及及不等于于等等，也也可以使使用annd（與與）、oor（或或）以及及nott（非）。NNOT可可用來對對任何邏邏輯運算算符號取取反。例：...wwherrennot(sttatuus=='VAALIDD')要使用NOOT，則則應(yīng)在取取反的短短語前面面加上括括號，并并在短語語前面加加上NOOT運算算符。NNOT運運算符包包含在另另外一個個邏輯運運算符中中，這就就是不等等于（<<>）運運算符。換換句話說說，即使使不在查查詢whheree子句中中顯式地地加入NNOT詞詞，NOOT仍在在運算符符中，見見下例：：...wwherresstattus<>''INVVALIID';;再例：selecct**frromempployyeewheeresaalarry<>>30000;解決方法，不不使用NNOT：：selecct**frromempployyeewheeresaalarry<330000orrsaalarry>330000;這兩種查詢詢的結(jié)果果一樣，但但是第二二種查詢詢方案會會比第一一種查詢詢方案更更快些。第第二種查查詢會對對sallaryy列使用用索引，而而第一種種查詢則則不會使使用索引引。IN和EXXISTTS將一列和一一系列值值相比較較，常用用的方法法是在wwherre子句句中使用用子查詢詢。在wherre子句句中可以以使用兩兩種格式式的子查查詢。第一種格式式是使用用IN操操作符：：...wwherreccoluumnin((sellectt*froom....wheere....);第二種格式式是使用用EXIIST操操作符：：...wwherreeexissts(seelecct''X'froom....wwherre....));采用第二種種格式要要比第一一種格式式的效率率高。第二種格式式中，子子查詢以以‘sellectt'XX'開始始。運用用EXIISTSS子句不不管子查查詢從表表中抽取取什么數(shù)數(shù)據(jù)它只只查看wwherre子句句。這樣樣優(yōu)化器器就不必必遍歷整整個表而而僅根據(jù)據(jù)索引就就可完成成工作（這這里假定定在whheree語句中中使用的的列存在在索引）。使用EXIIST，數(shù)數(shù)據(jù)庫系系統(tǒng)會首首先檢查查主查詢詢，然后后運行子子查詢直直到它找找到第一一個匹配配項。使用IN子子查詢時時，首先先執(zhí)行子子查詢，并并將獲得得的結(jié)果果列表存存放在在在一個加加了索引引的臨時時表中。在在執(zhí)行子子查詢之之前，系系統(tǒng)先將將主查詢詢掛起，待待子查詢詢執(zhí)行完完畢，存存放在臨臨時表中中以后再再執(zhí)行主主查詢。所以使用EEXISSTS通通常比使使用INN查詢速速度快。應(yīng)盡可能使使用NOOTEEXISSTS來來代替NNOTIN，盡盡管二者者都使用用了NOOT（不不能使用用索引而而降低速速度），但但NOTTEXXISTTS要比比NOTTINN查詢效效率高。不充份的連連接條件件例：表caard有有78996行，在在carrd_nno上有有一個非非聚集索索引，表表acccounnt有11911122行行，在acccounnt_nno上有有一個非非聚集索索引，在在不同的的表連接接條件下下，兩個個SQLL的執(zhí)行行情況：：selecctssum((a.aamouunt))frromacccounntaa,caardbwhereea..carrd_nno==b..carrd_nno（220秒）將SQL改改為：selecctssum((a.aamouunt))frromacccounntaa,caardbwhereea..carrd_nno==b..carrd_nnoandaa.acccouunt__no==b.aaccoountt_noo（<1秒）在第一個連連接條件件下，最最佳查詢詢方案是是將acccouunt作作外層表表，caard作作內(nèi)層表表，利用用carrd上的的索引，其其I/OO次數(shù)可可由以下下公式估估算為：：外層表acccouunt上上的2225411頁+（外外層表aaccoountt的19911222行**內(nèi)層表表carrd上對對應(yīng)外層層表第一一行所要要查找的的3頁）==59559077次I//O在第二個連連接條件件下，最最佳查詢詢方案是是將caard作作外層表表，acccouunt作作內(nèi)層表表，利用用acccounnt上的的索引，其其I/OO次數(shù)可可由以下下公式估估算為：：外層表caard上上的19944頁頁+（外外層表ccardd的78896行行*內(nèi)層層表acccouunt上上對應(yīng)外外層表每每一行所所要查找找的4頁頁）=335528次次I/OO只有充份的的連接條條件，真真正的最最佳方案案才會被被執(zhí)行。多表操作在在被實際際執(zhí)行前前，查詢詢優(yōu)化器器會根據(jù)據(jù)連接條條件，列列出幾組組可能的的連接方方案并從從中找出出系統(tǒng)開開銷最小小的最佳佳方案。連接條件要要充份考考慮帶有有索引的的表、行行數(shù)多的的表；內(nèi)外表的選選擇可由由公式：：外層表表中的匹匹配行數(shù)數(shù)*內(nèi)層層表中每每一次查查找的次次數(shù)確定定，乘積積最小為為最佳方方案。不可優(yōu)化的的wheere子子句下列SQLL條件語語句中的的列都建建有恰當當?shù)乃饕?，但?zhí)執(zhí)行速度度卻非常常慢selecct**frromreccorddwhhereesubsttrinng(ccardd_noo,1,,4)=='53378''(133秒)selecct**frromreccorddwhhereeamounnt/330<10000（111秒）selecct**frromreccorddwhhereeconveert((chaar(110),,datte,1112))='11999912001'（110秒）分析：wheree子句中中對列的的任何操操作結(jié)果果都是在在SQLL運行時時逐列計計算得到到的，因因此它不不得不進進行表搜搜索，而而沒有使使用該列列上面的的索引；；如果這這些結(jié)果果在查詢詢編譯時時就能得得到，那那么就可可以被SSQL優(yōu)優(yōu)化器優(yōu)優(yōu)化，使使用索引引，避免免表搜索索，因此此將SQQL重寫寫成下面面這樣：：selecct**frromreccorddwhhereecaard__nolikke'53788%'（<<1秒秒）selecct**frromreccorddwhhereeammounnt<10000*330（<<1秒秒）selecct**frromreccorddwhhereedaate=='119999/122/011'（<1秒秒）表stufff有22000000行行，idd_noo上有非非群集索索引，如如下：selecctccounnt(**)ffrommsttufffwhhereeidd_nooinn('00',''1'))（23秒）分析：wheree條件中中的'iin'在在邏輯上上相當于于'orr'，所所以語法法分析器器會將iin(('0'','11')轉(zhuǎn)轉(zhuǎn)化為iid_nno=='0''orridd_noo='11'來執(zhí)執(zhí)行。我我們期望望它會根根據(jù)每個個or子子句分別別查找，再再將結(jié)果果相加，這這樣可以以利用iid_nno上的的索引；；但實際際上（根根據(jù)shhowpplann）,它它卻采用用了"OOR策略略"，即即先取出出滿足每每個orr子句的的行，存存入臨時時數(shù)據(jù)庫庫的工作作表中，再再建立唯唯一索引引以去掉掉重復(fù)行行，最后后從這個個臨時表表中計算算結(jié)果。因因此，實實際過程程沒有利利用idd_noo上索引引，并且且完成時時間還要要受teempddb數(shù)據(jù)據(jù)庫性能能的影響響。表的行數(shù)越越多，工工作表的的性能就就越差，將將or子子句分開開：selecctccounnt(**)ffrommsttufffwhhereeidd_noo='00'selecctccounnt(**)ffrommsttufffwhhereeidd_noo='11'得到兩個結(jié)結(jié)果，再再作一次次加法合合算。因因為每句句都使用用了索引引，執(zhí)行行時間只只有3秒秒，在66200000行行下，時時間只有有4秒。更好的方法法，寫一一個簡單單的存儲儲過程：：creattepprocccoountt_sttufffassdeclaare@ainttdeclaare@binttdeclaare@cinttdeclaare@dchaar(110)beginnselecct@@a=ccounnt(**)ffrommsttufffwhhereeidd_noo='00'selecct@@b=ccounnt(**)ffrommsttufffwhhereeidd_noo='11'endselecct@@c=@@a+@@bselecct@@d=cconvvertt(chhar((10)),@cc)printt@dd直接算出結(jié)結(jié)果，執(zhí)執(zhí)行時間間同上面面一樣快快。小結(jié)所謂優(yōu)化即即wheere子子句利用用了索引引，不可可優(yōu)化即即發(fā)生了了表掃描描或額外外開銷。任何對列的的操作都都將導(dǎo)致致表掃描描，它包包括數(shù)據(jù)據(jù)庫函數(shù)數(shù)、計算算表達式式等等，查查詢時要要盡可能能將操作作移至等等號右邊邊。左右右不重要要，重要要的是表表達式中中是否包包含列。in、orr子句常常會使用用工作表表，使索索引失效效；如果果不產(chǎn)生生大量重重復(fù)值，可可以考慮慮把子句句拆開；；拆開的的子句中中應(yīng)該包包含索引引。要善于使用用存儲過過程，它它使SQQL變得得更加靈靈活和高高效。SQL優(yōu)化化的實質(zhì)質(zhì)就是在在結(jié)果正正確的前前提下，用用優(yōu)化器器可以識識別的語語句，充充份利用用索引，減減少表掃掃描的II/O次次數(shù)，盡盡量避免免表搜索索的發(fā)生生。不合理的索索引設(shè)計計例：表reecorrd有66200000行行，試看看在不同同的索引引下，下下面幾個個SQQL的運運行情況況：在datee上建有有一非個個群集索索引selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999912001'andddaate<''1999912214''anddammounnt>>20000(25秒))selecctddatee,suum(aamouunt))frromreccorddgrrouppbyydaate(55秒))selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999909001'anddpllaceeinn(''BJ'','SSH'))(27秒))分析：date上上有大量量的重復(fù)復(fù)值，在在非群集集索引下下，數(shù)據(jù)據(jù)在物理理上隨機機存放在在數(shù)據(jù)頁頁上，在在范圍查查找時，必必須執(zhí)行行一次表表掃描才才能找到到這一范范圍內(nèi)的的全部行行。在datee上的一一個群集集索引selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999912001'andddaate<''1999912214''anndaamouunt>2000（144秒）selecctddatee,suum(aamouunt))frromreccorddgrrouppbyydaate（28秒）selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999909001'anddpllaceeinn(''BJ'','SSH'))（144秒）分析：在群集索引引下，數(shù)數(shù)據(jù)在物物理上按按順序在在數(shù)據(jù)頁頁上，重重復(fù)值也也排列在在一起，因因而在范范圍查找找時，可可以先找找到這個個范圍的的起末點點，且只只在這個個范圍內(nèi)內(nèi)掃描數(shù)數(shù)據(jù)頁，避避免了大大范圍掃掃描，提提高了查查詢速度度。在placce，ddatee，ammounnt上的的組合索索引selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999912001'andddaate<''1999912214''anndaamouunt>2000（26秒）selecctddatee,suum(aamouunt))frromreccorddgrrouppbyydaate（27秒）selecctccounnt(**)ffrommreecorrdwwherreddatee>'1999909001'anddpllaceeinn(''BJ,,'SSH'))（<1秒秒）分析：這是一個不不很合理理的組合合索引，因因為它的的前導(dǎo)列列是pllacee，第一一和第二二條SQQL沒有有引用pplacce，因因此也沒沒有利用用