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

高效率SQL的基礎(chǔ)知識(shí)一個(gè)高效率的數(shù)據(jù)庫(kù)系統(tǒng)是從兩個(gè)方面來(lái)評(píng)價(jià)的：響應(yīng)時(shí)間和吞吐量。在應(yīng)用系統(tǒng)開發(fā)階段，由于開發(fā)庫(kù)上的數(shù)據(jù)比較少，在SQL語(yǔ)句的編寫上感覺(jué)不出各種寫法的性能差異，在將應(yīng)用系統(tǒng)提交實(shí)際應(yīng)用后，隨著數(shù)據(jù)庫(kù)中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就會(huì)成為最需要解決的主要問(wèn)題之一?？s短系統(tǒng)的響應(yīng)時(shí)間，增加操作的并發(fā)度，可以提高系統(tǒng)的吞吐量。要縮短系統(tǒng)的響應(yīng)時(shí)間，就需要可以高效率執(zhí)行的SQL語(yǔ)句。高效SQL語(yǔ)句的基本原則，是充分合理的利用索引，避免表掃描。理解索引大多數(shù)情況下，數(shù)據(jù)庫(kù)使用索引來(lái)檢索表，優(yōu)化器根據(jù)用戶定義的索引來(lái)提高執(zhí)行性能。但是，如果在SQL語(yǔ)句的where子句中寫的SQL代碼不合理，就會(huì)造成優(yōu)化器忽略索引而采用全表掃描，而這種SQL語(yǔ)句就是所謂的劣質(zhì)SQL語(yǔ)句。在編寫SQL語(yǔ)句時(shí)需要了解優(yōu)化器根據(jù)何種原則來(lái)使用索引，這將有助于寫出高性能的SQL語(yǔ)句。ISNULL與ISNOTNULL不要用null值作索引，任何包含null值的列都將不會(huì)被包含在索引中以NULL值做條件時(shí)，將無(wú)法使用包含NULL值的列上的索引。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說(shuō)如果某列存在空值，在使用NULL值做條件時(shí)，即使對(duì)該列建索引也不會(huì)提高性能。列的聯(lián)接本節(jié)應(yīng)該闡述列被包含到表達(dá)式中導(dǎo)致不能使用索引。對(duì)于有聯(lián)接的列，即使最后的聯(lián)接值為一個(gè)靜態(tài)值，優(yōu)化器是不會(huì)使用索引的。例：假定有一個(gè)職工表（employee），對(duì)于一個(gè)職工的姓和名分成兩列存放（FIRST_NAME和LAST_NAME），現(xiàn)在要查詢一個(gè)叫比爾.克林頓（BillCliton）的職工。下面是一個(gè)采用聯(lián)接查詢的SQL語(yǔ)句，棋selec奸t*f擠rome孔mploy巖ss鼓w孕here碎first吊_name獨(dú)||''|糾|last參_name沾='Bi焰llCl堤iton'待;改進(jìn)方法：拉selec成t*f體rome剛mploy勇ee蓬where合firs趙t_nam舟e=‘愚Bill’鮮and康last_盼name勁=‘Cl播iton’味帶通配符（寶%）的li脖ke語(yǔ)句寄selec秀t*f誠(chéng)rome喚mploy芹eewh半erel覽ast_n戒amel皂ike'好%clit務(wù)on%';悔周由于通配符哨（臟%）在搜尋肆詞首出現(xiàn)，面所以數(shù)據(jù)庫(kù)嘴將不使用l慘ast_n怪ame的索廚引。在很多訴情況下可能疾無(wú)法避免這薄種情況，但螞是一定要心施中有數(shù)，通饒配符如此使估用會(huì)降低查監(jiān)詢速度。窮當(dāng)通配符出涂現(xiàn)在字符串弓其他位置時(shí)進(jìn)，優(yōu)化器就紫能利用索引印。在下面的昏查詢中索引載得到了使用否：筆selec英t*f澡rome壩mploy愈eewh廁erel犯ast_n岡amel雁ike'嫩c%';愿Order消by語(yǔ)句友ORDER江BY語(yǔ)句碌決定了數(shù)據(jù)保庫(kù)如何將返欠回的查詢結(jié)銷果排序。O燙rder碑by語(yǔ)句對(duì)絮要排序的列販沒(méi)有什么特惰別的限制，信也可以將函慨?dāng)?shù)加入列中昌（象聯(lián)接或遺者附加等）芽。任何在O轉(zhuǎn)rder里by語(yǔ)句的匪非索引項(xiàng)或式者有計(jì)算表丘達(dá)式都將降催低查詢速度基。尸需要仔細(xì)檢疫查哈order隆by語(yǔ)句禁以找出非索術(shù)引項(xiàng)或者表勒達(dá)式，它們傭會(huì)降低性能緩。伸解決這個(gè)問(wèn)派題的辦法就鞋是重寫核order香by語(yǔ)句姑以使用索引祥，也可以為僻所使用的列塊建立另外一贊個(gè)索引，同局時(shí)應(yīng)絕對(duì)避捷免在ord既erby點(diǎn)子句中使用鞋表達(dá)式。NOT蛾not(攤statu殲s='V鵲ALID'鬼)鳳和丟(sta珠tus送<>抱'VALI輪D')仇哪個(gè)效率更雕高？注salar街y<>30仔00艱和脖salar濫y<300括0or營(yíng)salar城y>300道0另和真salar描y<珠＝后2999筋ors乖alary陡>魯=溉300規(guī)1(假設(shè)是皆整數(shù))哪一貼個(gè)效率更高旬？壁在地where惜子句使用一沖些邏輯表達(dá)鎮(zhèn)式，如大于充、小于、等雅于以及不等歐于等等，也動(dòng)可以使用a雙nd（與）臘、or（或乏）以及no誠(chéng)t（非）。最NOT可用甚來(lái)對(duì)任何邏警輯運(yùn)算符號(hào)伏取反。例：螞...w騾here旅not(椒statu跳s='V伙ALID'腥)亭要使用罵NOT，則煙應(yīng)在取反的評(píng)短語(yǔ)前面加兩上括號(hào)，并繪在短語(yǔ)前面盾加上NOT雨運(yùn)算符。N超OT運(yùn)算符槽包含在另外逮一個(gè)邏輯運(yùn)佩算符中，這褲就是不等于伍（<>）運(yùn)巴算符。換句袖話說(shuō)，即使申不在查詢w挪here子臣句中顯式地枕加入NOT學(xué)詞，NOT銳仍在運(yùn)算符造中，見(jiàn)下例抵：凳...w貌here恰statu淹s<>'期INVAL械ID';再例：銜selec沿t*f還rome鎖mploy是eewh懂ere欠salar餃y(cè)<>30航00;患解決方法，慈不使用糟NOT：距selec田t*f冷rome洗mploy夜eewh漏ere芹salar惜y<300辟0or刃salar滔y>300餅0;泄這兩種查詢敞的結(jié)果一樣旁，但是第二終種查詢方案得會(huì)比第一種肉查詢方案更靜快些。第二肢種查詢會(huì)對(duì)耕salar售y列使用索宏引，而第一過(guò)種查詢則不陶會(huì)使用索引燈。吐IN和EX判ISTS頁(yè)將一列和一橋系列值相比述較，常用的捎方法是在辜where腐子句中使用炸子查詢。曬在哀where支子句中可以硬使用兩種格促式的子查詢荷。駛第一種格式態(tài)是使用泳IN操作符測(cè)：柔...w穗here恩colum優(yōu)nin(掘selec播t*f預(yù)rom.跌..wh棵ere.雨..);召第二種格式僑是使用皆EXIST敞操作符：恢...w晃here刮exist獨(dú)s(se侮lect鴨'X'f礙rom.最..whe熊re..礦.);津采用第二種膏格式要比第敞一種格式的退效率高。被第二種格式賠中，子查詢捆以堆‘陪selec圾t'X'奴開始。運(yùn)用棗EXIST廳S子句不管叔子查詢從表潛中抽取什么倦數(shù)據(jù)它只查曠看wher惡e子句。這皆樣優(yōu)化器就罰不必遍歷整健個(gè)表而僅根巷據(jù)索引就可技完成工作（四這里假定在紡where倒語(yǔ)句中使用駁的列存在索雹引）。謎使用舌EXIST亞，數(shù)據(jù)庫(kù)系伸統(tǒng)會(huì)首先檢羊查主查詢，醒然后運(yùn)行子弊查詢直到它疏找到第一個(gè)毯匹配項(xiàng)。陣使用損IN子查詢?nèi)袝r(shí)，首先執(zhí)突行子查詢，跟并將獲得的果結(jié)果列表存灶放在在一個(gè)招加了索引的查臨時(shí)表中。停在執(zhí)行子查宰詢之前，系伙統(tǒng)先將主查所詢掛起，待品子查詢執(zhí)行演完畢，存放繡在臨時(shí)表中癢以后再執(zhí)行倦主查詢。識(shí)所以使用擺EXIST估S通常比使諒用IN查詢珍速度快。蒸應(yīng)盡可能使四用弱NOTE求XISTS三來(lái)代替NO拜TIN，妹盡管二者都臥使用了NO卻T（不能使掛用索引而降貴低速度），快但NOT堅(jiān)EXIST侵S要比NO橡TIN查逃詢效率高?？坎怀浞莸倪B湊接條件邁例：表衡card有蹈7896行朵，在car玻d_no上越有一個(gè)非聚順集索引，表罰accou萌nt有19玉1122行氧，在ac題count欣_no上有潮一個(gè)非聚集南索引，在不爸同的表連接胃條件下，兩圣個(gè)SQL的箱執(zhí)行情況：夕驅(qū)selec路tsum賴(a.am蒜ount)霞from杜acco忘unta州,card愚b觸where噸a.ca唱rd_no瞇=b.杯card_鈔no（20歐秒）振將友SQL改為產(chǎn)：蹦selec蜜tsum煙(a.am仰ount)闊from聞acco誓unta免,card公b原where矮a.ca務(wù)rd_no皇=b.喊card極_no翅anda美.acco矩unt_n矮o=b.a理ccoun饞t_no（叨<1秒）碌在第一個(gè)連鮮接條件下，襯最佳查詢方師案是將禿accou盟nt作外層嗚表，car欺d作內(nèi)層表霜，利用ca臉rd上的索老引，其I/鉛O次數(shù)可由節(jié)以下公式估白算為：拒外層表該accou躬nt上的2奴2541頁(yè)茶+（外層表脹accou謙nt的19獅1122行旅*內(nèi)層表c睬ard上對(duì)蔑應(yīng)外層表第析一行所要查世找的3頁(yè)）榮=5959容07次I/黎O固在第二個(gè)連宣接條件下，忠最佳查詢方剪案是將后card作朗外層表，a貫ccoun野t作內(nèi)層表聚，利用ac裕count次上的索引，查其I/O次反數(shù)可由以下恰公式估算為診：飲外層表鹿card上琴的1944絨頁(yè)+（外層揮表card敏的7896缺行*內(nèi)層表舌accou篩nt上對(duì)應(yīng)卻外層表每一伙行所要查找場(chǎng)的4頁(yè)）=斜3352醉8次I/O捷墨只有充份的宴連接條件，糞真正的最佳年方案才會(huì)被愚執(zhí)行。再多表操作在悼被實(shí)際執(zhí)行屑前，查詢優(yōu)槳化器會(huì)根據(jù)婆連接條件，情列出幾組可絲能的連接方掘案并從中找盼出系統(tǒng)開銷版最小的最佳恩方案。促連接條件要眼充份考慮帶賤有索引的表懂、行數(shù)多的灶表；閑內(nèi)外表的選湊擇可由公式粗：外層表中奏的匹配行數(shù)念*內(nèi)層表中哈每一次查找蹈的次數(shù)確定私，乘積最小貸為最佳方案服。優(yōu)不可優(yōu)化的劈where帆子句純下列逮SQL條件真語(yǔ)句中的列副都建有嘉恰當(dāng)?shù)乃饕龔V，但執(zhí)行速暫度卻非常慢次selec虹t*f怪romr山ecord貝wher咽e車subst床ring(辱card_伙no,1,樣4)='5墓378'(咱13秒)畏selec陪t*f捐romr潤(rùn)ecord炭wher散e踢amoun湊t/30<漂1000孝（11秒）竟selec鑼t*f星romr惹ecord黨wher灣e遍conve拜rt(ch賢ar(10飛),dat底e,112豬)='19朋99120樣1'（10拋秒）分析：鴨where睜子句中對(duì)列輔的任何操作箏結(jié)果都是在雜SQL運(yùn)行且時(shí)逐列計(jì)算惜得到的，因季此它不得不渾進(jìn)行表搜索它，而沒(méi)有使元用該列上面構(gòu)的索引；如明果這些結(jié)果妖在查詢編譯爸時(shí)就能得到慢，那么就可鑒以被SQL萍優(yōu)化器優(yōu)化撈，使用索引先，避免表搜換索，因此將濱SQL重寫陸成下面這樣胖：西selec隱t*f餓romr墾ecord歡wher熱ecar知d_no老like向'5378餡%'（<后1秒）臉selec塵t*f肥romr挨ecord岸wher規(guī)eamo旬unt撿<100斥0*30（溉<1秒）攏selec島t*f含romr支ecord農(nóng)wher辛edat統(tǒng)e='1錘999/1彼2/01'銹（廳<1秒）擁表拜stuff翼有2000惑00行，i榮d_no上改有非群集索曬引，如下：蘿selec創(chuàng)tcou川nt(*)墳from遮stuf鵲fwhe賄reid軍_noi名n('0'息,'1')努（23秒）分析：忠where信條件中的'滾in'在邏礦輯上相當(dāng)于攏'or'，百所以語(yǔ)法分戚析器會(huì)將i摩n('0栽','1'邀)轉(zhuǎn)化為i局d_no崖='0'凍orid蹈_no='熊1'來(lái)執(zhí)行漂。我們期望溝它會(huì)根據(jù)每檔個(gè)or子句外分別查找，女再將結(jié)果相薄加，這樣可斬以利用id瞇_no上的飯索引；但實(shí)興際上（根據(jù)鬧showp臨lan）,剃它卻采用了沫"OR策略涉"底，即先取出輩滿足每個(gè)縫or子句的尸行，存入臨損時(shí)數(shù)據(jù)庫(kù)的明工作表中，瞇再建立唯一嫂索引以去掉攤重復(fù)行，最看后從這個(gè)臨鉆時(shí)表中計(jì)算國(guó)結(jié)果。因此裳，實(shí)際過(guò)程門沒(méi)有利用i罰d_no上清索引，并且藝完成時(shí)間還敵要受tem概pdb數(shù)據(jù)禾庫(kù)性能的影友響。推表的行數(shù)越迫多，工作表證的性能就越爬差，將包or子句分紀(jì)開：辱selec貪tcou捆nt(*)純from扶stuf姨fwhe徒reid塵_no='茅0'看selec陽(yáng)tcou旋nt(*)樣from憐stuf塔fwhe繞reid條_no='備1'塔得到兩個(gè)結(jié)贏果，再作一纖次加法合算侄。因?yàn)槊烤鋵俣际褂昧怂饔梢瑘?zhí)行時(shí)柜間只有曾3秒，在6掛20000只行下，時(shí)間誕只有4秒。撇更好的方法懷，寫一個(gè)簡(jiǎn)悠單的存儲(chǔ)過(guò)榨程：儀creat偉epro消ccou屬nt_st缺uffa告s真decla爆re@a調(diào)int爪decla斷re@b議int嘆decla匠re@c椅int微decla灘re@d鴉char擱(10)begin漢selec滋t@a=唱count覺(jué)(*)f鎖roms侵tuff曠where良id_n歇o='0'斷selec鋪t@b=獄count燒(*)f繳roms陶tuff弱where倚id_n株o='1'end放selec片t@c=叼@a+@b升selec轎t@d=銜conve搞rt(ch失ar(10惕),@c)否print雙@d之直接算出結(jié)即果，執(zhí)行時(shí)貢間同上面一母樣快。小結(jié)拼所謂優(yōu)化即奶where氣子句利用了寫索引，不可個(gè)優(yōu)化即發(fā)生值了表掃描或?qū)W額外開銷。善圾任何對(duì)列的看操作都將導(dǎo)禁致表掃描，斧它包括數(shù)據(jù)比庫(kù)函數(shù)、計(jì)棄算表達(dá)式等息等，查詢時(shí)存要盡可能將辦操作移至等功號(hào)右邊。春左右不重要德，重要潑的是表達(dá)式耗中是否包含告列。謙in、or悄子句常會(huì)使揀用工作表，牧使索引失效蹈；如果不產(chǎn)緊生大量重復(fù)兼值，可以考皇慮把子句拆技開；拆開的蔥子句中應(yīng)該脅包含索引。紀(jì)融要善于使用雀存儲(chǔ)過(guò)程，票它使鬼SQL變得比更加靈活和捕高效。榜SQL優(yōu)化關(guān)的實(shí)質(zhì)就是游在結(jié)果正確透的前提下，魄用優(yōu)化器可磨以識(shí)別的語(yǔ)前句，充份利汽用索引，減騰少表掃描的難I/O次數(shù)江，盡量避免早表搜索的發(fā)顧生。枕不合理的索網(wǎng)引設(shè)計(jì)恐例：表浸recor遺d有620造000行，蠻試看在不同招的索引下，俯下面幾個(gè)潑SQL的運(yùn)宋行情況：志在葬date上拳建有一非個(gè)斤群集索引態(tài)selec谷tcou注nt(*)其from檔reco扣rdwh立ered怎ate>羊'1999豈1201'壁and偶date施<'19授99121秒4'and貸amou煉nt>熔2000(25秒)暑selec岸tdat暗e,sum佳(amou錫nt)f倡romr況ecord須grou劍pby鄭date氧(55秒)鑰崗selec種tcou饞nt(*)鑰from陳reco脈rdwh圍ered逢ate>圾'1999鋸0901'旺and警place愁in(膽'BJ',攜'SH')(27秒)分析：壞date上連有大量的重嘩復(fù)值，在非拒群集索引下做，數(shù)據(jù)在物免理上隨機(jī)存呆放在數(shù)據(jù)頁(yè)蓄上，在范圍陜查找時(shí)，必陵須執(zhí)行一次目表掃描才能丘找到這一范尖圍內(nèi)的全部溫行。攤在哄date上乏的一個(gè)群集故索引滿selec缸tcou解nt(*)少from所reco避rdwh吼ered數(shù)ate>坡減'1999濃1201'隨and竟date蜘<'19狡99121倦4'an痰damo擇unt>移2000刪（14秒）什selec財(cái)tdat奔e,sum飯(amou核nt)f辰romr干ecord援grou并pby答date（28秒）緣selec睛tcou瞧nt(*)廳from灰reco波rdwh要ered鬧ate>遞'1999肯0901'灑and前place羊in(池'BJ',斗'SH')繩（14秒）分析：勤在群集索引豆下，數(shù)據(jù)在誦物理上按順隱序在數(shù)據(jù)頁(yè)暑上，重復(fù)值液也排列在一吹起，因而在擴(kuò)范圍查找時(shí)光，可以先找?guī)诺竭@個(gè)范圍隆的起末點(diǎn)，攪且只在這個(gè)假范圍內(nèi)掃描娛數(shù)據(jù)頁(yè)，避響免了大范圍漸掃描，提高沉了查詢速度巡。懼在慈place僑，date運(yùn)，amou終nt上的組僚合索引堅(jiān)selec浸tcou腦nt(*)首from孝reco劈rdwh接ered簽ate>接'1999球1201'圖and揪date曉<'19霞99121彼4'an根damo煙unt>2000（26秒）播selec疾tdat初e,sum糊(amou漢nt)f倦romr想ecord鋸grou叢pby浩date（27秒）柴selec蹲tcou忽nt(*)道from城reco遭rdwh啊ered諒ate>鋸'1999鈔0901'波and蘇place嬌in(驚'BJ,穗'SH')優(yōu)（鴨<1秒）分析：轎這是一個(gè)不誦很合理的組團(tuán)合索引，因湊為它的前導(dǎo)準(zhǔn)列是蹈place推，第一和第微二條SQL蠟沒(méi)有引用p土lace，真因此也沒(méi)有皂利用上索引催；第三個(gè)