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

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