基于sql的信息數(shù)據(jù)庫(kù)優(yōu)化研究

基于sql的信息數(shù)據(jù)庫(kù)優(yōu)化研究

數(shù)據(jù)庫(kù)在數(shù)據(jù)庫(kù)領(lǐng)域是領(lǐng)先地位的，數(shù)據(jù)庫(kù)利用率超過40%。它被廣泛應(yīng)用于公司和機(jī)構(gòu)管理的各個(gè)方面。ORACLE不僅在海量數(shù)據(jù)集中處理方面表現(xiàn)突出,而且大量用于銀行、民航、鐵路、電信等部門的聯(lián)機(jī)事務(wù)處理(OLTP)以及聯(lián)機(jī)分析處理(OLAP)中。在聯(lián)機(jī)事務(wù)處理中,查詢操作是各種數(shù)據(jù)庫(kù)操作中使用頻率最高的,而查詢操作所基于的SELECT語句在SQL語句中又是代價(jià)最大的語句。在數(shù)據(jù)量比較小的時(shí)候,感覺不出SQL語句各種寫法的性能優(yōu)劣,對(duì)于海量數(shù)據(jù),劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達(dá)到上百倍。當(dāng)數(shù)據(jù)庫(kù)表的數(shù)據(jù)積累到上百萬甚至上千萬條記錄時(shí),全表掃描一次往往需要數(shù)十分鐘,甚至數(shù)小時(shí)。如果采用添加索引等更好的查詢策略,往往可以使查詢時(shí)間降為幾分鐘。因此,對(duì)于一個(gè)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng),尤其是聯(lián)機(jī)事務(wù)處理系統(tǒng),不僅要界面友好、功能齊全,而且要優(yōu)化數(shù)據(jù)庫(kù)性能,提高系統(tǒng)的響應(yīng)速度。1應(yīng)用程序的優(yōu)化數(shù)據(jù)庫(kù)的優(yōu)化通?？梢阅苓^對(duì)網(wǎng)絡(luò)、硬件、操作系統(tǒng)、數(shù)據(jù)庫(kù)參數(shù)和應(yīng)用程序的優(yōu)化來進(jìn)行。常見的優(yōu)化手段是對(duì)硬件資源的調(diào)整,如更多的磁盤、內(nèi)存、高速緩存CPU等;還有對(duì)RDBMS資源調(diào)整,如增加高速緩存區(qū)、共享內(nèi)存池等。但是,對(duì)網(wǎng)絡(luò)、硬件、操作系統(tǒng)、數(shù)據(jù)庫(kù)參數(shù)進(jìn)行優(yōu)化獲得的性能提升,全部加起來只占數(shù)據(jù)庫(kù)系統(tǒng)性能提升的40%左右,60%系統(tǒng)性能提升來自對(duì)應(yīng)用程序的優(yōu)化。應(yīng)用程序的優(yōu)化通?？煞譃閮蓚€(gè)方面:源代碼和SQL語句。源代碼的優(yōu)化要求管理員不僅熟悉特定的SQL語句,還要熟悉應(yīng)用程序的功能和處理邏輯,在時(shí)間成本和風(fēng)險(xiǎn)上代價(jià)很高;為獲得性能優(yōu)化的應(yīng)用程序,必須從優(yōu)化SQL語句開始。2sql句子的優(yōu)化·不良的SQL語句以低效方式訪問數(shù)據(jù)庫(kù),導(dǎo)致不必要的數(shù)據(jù)掃描和通過網(wǎng)絡(luò)傳輸不必要的數(shù)據(jù),消耗大量數(shù)據(jù)庫(kù)資源?！QL語句優(yōu)化是應(yīng)用程序優(yōu)化的基礎(chǔ)和前提?！QL語句獨(dú)立于程序設(shè)計(jì)邏輯,對(duì)SQL語句進(jìn)行優(yōu)化不會(huì)影響程序邏輯?！QL語句實(shí)現(xiàn)同樣功能有不同寫法,在性能上的差異非常大。SQL優(yōu)化途徑:系統(tǒng)優(yōu)化,用戶查詢計(jì)劃優(yōu)化SQL語句的優(yōu)化是將性能低下的SQL語句轉(zhuǎn)換成為目的相同的、性能優(yōu)異的SQL語句。在Oracle中,系統(tǒng)采用基于成本的和基于規(guī)則的方法優(yōu)化用戶所提交的SQL語句,即由用戶提交的SQL語句是系統(tǒng)優(yōu)化的基礎(chǔ),用戶所寫語句的優(yōu)劣至關(guān)重要,一個(gè)好的查詢計(jì)劃往往可以使程序性能提高數(shù)十倍?？梢圆扇∪N策略提高SQL語句的執(zhí)行速度:ue0c3合理使用索引ue0c3重寫SQL語句ue0c3重構(gòu)數(shù)據(jù)(1)建立索引compond使用索引的根本目的就是為了提高查詢效率,但索引也不是越多越好,使用索引時(shí)應(yīng)遵循以下原則:為不經(jīng)常更新的靜態(tài)大表建立索引;為查詢語句只選擇少量行的表建立索引;在經(jīng)常進(jìn)行連接,但是沒有指定為外鍵的列上建立索引;在頻繁進(jìn)行排序或分組(即進(jìn)行orderby或groupby操作)的列上建立索引;在不同值較多的列上建立檢索;在多個(gè)待排序的列上建立復(fù)合索引(compoundindex);在沒有空值的列上建索引,在含有空值的列上即使建了索引也用不上。(2)使用opla的結(jié)果比較SQL語言使用非常靈活,同樣的應(yīng)用目的可以用不同的語句實(shí)現(xiàn),速度有快有慢?？梢杂肊XPLAINPLAN的結(jié)果來比較兩條語句的執(zhí)行計(jì)劃和執(zhí)行成本,以決定孰優(yōu)孰劣。下面是在RACLE的SQL語句編寫查詢時(shí)的一些體會(huì),例子所用表為ORACLE安裝時(shí)的兩張例表,一個(gè)是EMP表,含有雇員信息;另一個(gè)是DEPT表,含有部門信息。在emp表上掃描下面兩個(gè)查詢語句都返回在EMP表中沒有雇員的DEPT表中部門號(hào),每條語句都通過子查詢?cè)L問EMP表,假設(shè)在EMP表的DEPTNO列上已經(jīng)建立了索引。第一種寫法是:SELECTdname,deptnoFROMdeptWHEREdeptnoNOTIN(SELECTdeptnoFROMemp);盡管在EMP表的DEPTNO列上已經(jīng)建立了索引,ORACLE仍然對(duì)EMP表進(jìn)行全表掃描,因?yàn)閷?duì)EMP瑤子查詢沒有WHERE子句,用不上索引。全表掃描最費(fèi)時(shí)。將上述語句改為下面的寫法,就會(huì)用上索引,加快執(zhí)行速度,返回結(jié)果是相同的:SELECTdname,deptnoFROMdeptWHERENOTEXISTS(SELECTdeptnoFROMempWHEREdept.deptno=emp.deptno);子查詢中的WHERE子句指向表的DEPTNO列,用上了該列索引,減小了掃描范圍,提高了查詢速度。pno含上接口信號(hào)利用外部聯(lián)接“+”,可以替代效率十分低下的notin運(yùn)算,大大提高運(yùn)行速度,例如,下面命令執(zhí)行起來很慢:SELECTa.empnofromempaWHEREa.empnonotin(SELECTempnoFROMempWHEREjob=`SALESMAN');倘若利用外部聯(lián)接,將命令改寫為:SELECTa.empnoFROMempa,empbWHEREa.empno=b.empno(+)ANDb.job!='SALESMAN';可以發(fā)現(xiàn),運(yùn)行速度明顯提高。選擇工具和轉(zhuǎn)換在ORACLE中,LIKE關(guān)鍵字支持通配符匹配,“%”匹配多個(gè)字符,“-”匹配單個(gè)字符,如果匹配模式首字是“%”或“-”,查詢用不上索引,因此,速度要慢,例如,要求在職工表中查詢名字中包含F(xiàn)ORD的人,可以采用如下的查詢語句:SELECT*fromempWHEREenamelike'%FORD%';這里由于通配符(%)在搜尋詞首出現(xiàn),Oracle系統(tǒng)不使用ENAME的索引。當(dāng)通配符出現(xiàn)在字符串其他位置時(shí),優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用:SELECT*fromEMPwhereENAMElike'F%';通配符匹配是相對(duì)較慢的操作,有時(shí)可以把它轉(zhuǎn)換成功能相同的其他形式,如:SELECT*FROMempWHEREsalaryLIKE“98---”即使在Salary字段上建立了索引,也還是采用順序掃描的方式。如果把語句改為SELECT*FROMempWHEREsalary>“98000”,在執(zhí)行查詢時(shí)就會(huì)利用索引來查詢,會(huì)大大提高速度。在下一個(gè)句子中，盡量不使用轉(zhuǎn)換函數(shù)例如:使用WHEREa.order而不用如下形式:WHERETO=TO使用distctDISTINCT的使用引起排序操作,將降低查詢速度。or-or-or,et邏輯運(yùn)算符在SQL語句的WHERE子句中,經(jīng)常使用邏輯表達(dá)式,如GREATERTHAN,LESSTHAN,EQUALTOandNOTEQUALTO等,也經(jīng)常使用AND,OR,NOT操作符,NOT邏輯運(yùn)算符,如OTEQUALTO操作(〈〉),上索引,速度非常慢,通常包含NOT邏輯運(yùn)算符的WHERE子句可以改寫為其他形式,例如:SELECT*FROMempWHEREsal<>3000;可以改寫為:SELECT*FROMempWHEREsal<3000orsal>3000;結(jié)果是一樣的,但第二種形式可以用上SAL列的索引,執(zhí)行速度比第一個(gè)快deptwwellenpo智能化在紐約的雇員中,哪些人的工資高于ScottSQL〉SELECTename,salFROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREloc='NEWYORK')ANDsal>(SELECTsalFROMempWHEREename='SCOTT');用表連接實(shí)現(xiàn):SQL〉SELECTename,sal,emp.deptno,locFROMemp,deptWHEREemp.deptno=dept.deptnoANDsal〉(SELECTsalFROMempwhereANDloc='NEWYORK';對(duì)于大數(shù)據(jù)的計(jì)算，應(yīng)使用單一的sum命令可人為將數(shù)據(jù)分組,利用groupby