數(shù)據(jù)庫(kù)優(yōu)化方案樣本

數(shù)據(jù)庫(kù)優(yōu)化方案1.高效地進(jìn)行SQL語(yǔ)句設(shè)計(jì)：普通狀況下，可以采用下面辦法優(yōu)化SQL對(duì)數(shù)據(jù)操作體現(xiàn)：（1）減少對(duì)數(shù)據(jù)庫(kù)查詢次數(shù)，即減少對(duì)系統(tǒng)資源祈求，使用快照和顯形圖等分布式數(shù)據(jù)庫(kù)對(duì)象可以減少對(duì)數(shù)據(jù)庫(kù)查詢次數(shù)。（2）盡量使用相似或非常類(lèi)似SQL語(yǔ)句進(jìn)行查詢，這樣不但充分運(yùn)用SQL共享池中已經(jīng)分析語(yǔ)法樹(shù)，要查詢數(shù)據(jù)在SGA中命中也許性也會(huì)大大增長(zhǎng)。（3）避免不帶任何條件SQL語(yǔ)句執(zhí)行。沒(méi)有任何條件SQL語(yǔ)句在執(zhí)行時(shí)，普通要進(jìn)行FTS，數(shù)據(jù)庫(kù)先定位一種數(shù)據(jù)塊，然后按順序依次查找其他數(shù)據(jù)，對(duì)于大型表這將是一種漫長(zhǎng)過(guò)程。（4）如果對(duì)有些表中數(shù)據(jù)有約束，最佳在建表SQL語(yǔ)句用描述完整性來(lái)實(shí)現(xiàn)，而不是用SQL程序中實(shí)現(xiàn)。一、操作符優(yōu)化：1、IN操作符用IN寫(xiě)出來(lái)SQL長(zhǎng)處是比較容易寫(xiě)及清晰易懂，這比較適合當(dāng)代軟件開(kāi)發(fā)風(fēng)格。但是用INSQL性能總是比較低，從Oracle執(zhí)行環(huán)節(jié)來(lái)分析用INSQL與不用INSQL有如下區(qū)別：ORACLE試圖將其轉(zhuǎn)換成各種表連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面子查詢，再查詢外層表記錄，如果轉(zhuǎn)換成功則直接采用各種表連接方式查詢。由此可見(jiàn)用INSQL至少多了一種轉(zhuǎn)換過(guò)程。普通SQL都可以轉(zhuǎn)換成功，但對(duì)于具有分組記錄等方面SQL就不能轉(zhuǎn)換了。在業(yè)務(wù)密集SQL當(dāng)中盡量不采用IN操作符。優(yōu)化sql時(shí)，經(jīng)常遇到使用in語(yǔ)句，一定要用exists把它給換掉，由于Oracle在解決In時(shí)是按Or方式做，雖然使用了索引也會(huì)很慢。2、NOTIN操作符強(qiáng)列推薦不使用，由于它不能應(yīng)用表索引。用NOTEXISTS或（外連接+判斷為空）方案代替3、ISNULL或ISNOTNULL操作判斷字段與否為空普通是不會(huì)應(yīng)用索引，由于B樹(shù)索引是不索引空值。用其他相似功能操作運(yùn)算代替，aisnotnull改為a>0或a>’’等。不容許字段為空，而用一種缺省值代替空值，如業(yè)擴(kuò)申請(qǐng)中狀態(tài)字段不容許為空，缺省為申請(qǐng)。避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可覺(jué)得空列，ORACLE將無(wú)法使用該索引．對(duì)于單列索引，如果列包括空值，索引中將不存在此記錄.對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄.如果至少有一種列不為空，則記錄存在于索引中．舉例:如果唯一性索引建立在表A列和B列上,并且表中存在一條記錄A,B值為(123,null)，ORACLE將不接受下一條具備相似A,B值（123,null）記錄(插入).然而如果所有索引列都為空，ORACLE將以為整個(gè)鍵值為空而空不等于空.因而你可以插入1000條具備相似鍵值記錄,固然它們都是空!由于空值不存在于索引列中,因此WHERE子句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引.低效：(索引失效)SELECT…FROMDEPARTMENTWHEREDEPT_CODEISNOTNULL;高效：(索引有效)SELECT…FROMDEPARTMENTWHEREDEPT_CODE>=0;4、>及<操作符（不不大于或不大于操作符）不不大于或不大于操作符普通狀況下是不用調(diào)節(jié)，由于它有索引就會(huì)采用索引查找，但有狀況下可以對(duì)它進(jìn)行優(yōu)化，如一種表有100萬(wàn)記錄，一種數(shù)值型字段A，30萬(wàn)記錄A=0，30萬(wàn)記錄A=1，39萬(wàn)記錄A=2，1萬(wàn)記錄A=3。那么執(zhí)行A>2與A>=3效果就有很大區(qū)別了，由于A>2時(shí)ORACLE會(huì)先找出為2記錄索引再進(jìn)行比較，而A>=3時(shí)ORACLE則直接找到=3記錄索引。用>=代替>高效:SELECT…FROMDEPARTMENTWHEREDEPT_CODE>=0;低效:SELECT*FROMEMPWHEREDEPTNO>3兩者區(qū)別在于，前者DBMS將直接跳到第一種DEPT等于4記錄而后者將一方面定位到DEPTNO=3記錄并且向前掃描到第一種DEPT不不大于3記錄.5、LIKE操作符：LIKE操作符可以應(yīng)用通配符查詢，里面通配符組合也許達(dá)到幾乎是任意查詢，但是如果用得不好則會(huì)產(chǎn)生性能上問(wèn)題，如LIKE‘%5400%’這種查詢不會(huì)引用索引，而LIKE‘X5400%’則會(huì)引用范疇索引。一種實(shí)際例子：用YW_YHJBQK表中營(yíng)業(yè)編號(hào)背面戶標(biāo)記號(hào)可來(lái)查詢營(yíng)業(yè)編號(hào)YY_BHLIKE‘%5400%’這個(gè)條件會(huì)產(chǎn)生全表掃描，如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’則會(huì)運(yùn)用YY_BH索引進(jìn)行兩個(gè)范疇查詢，性能必定大大提高。6、用EXISTS替代DISTINCT：當(dāng)提交一種包括一對(duì)多表信息(例如部門(mén)表和雇員表)查詢時(shí),避免在SELECT子句中使用DISTINCT.普通可以考慮用EXIST替代，EXISTS使查詢更為迅速,由于RDBMS核心模塊將在子查詢條件一旦滿足后,立即返回成果.例子：(低效):SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD，EMPEWHERED.DEPT_NO=E.DEPT_NO(高效):SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT'X'FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);如：用EXISTS代替IN、用NOTEXISTS代替NOTIN：在許多基于基本表查詢中,為了滿足一種條件,往往需要對(duì)另一種表進(jìn)行聯(lián)接.在這種狀況下,使用EXISTS(或NOTEXISTS)普通將提高查詢效率.在子查詢中,NOTIN子句將執(zhí)行一種內(nèi)部排序和合并.無(wú)論在哪種狀況下,NOTIN都是最低效(由于它對(duì)子查詢中表執(zhí)行了一種全表遍歷).為了避免使用NOTIN,咱們可以把它改寫(xiě)成外連接(OuterJoins)或NOTEXISTS.例子：(高效):SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDEXISTS(SELECT'X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC='MELB')(低效):SELECT*FROMEMP(基本表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC='MELB')7、用UNION替代OR(合用于索引列)普通狀況下，用UNION替代WHERE子句中OR將會(huì)起到較好效果.對(duì)索引列使用OR將導(dǎo)致全表掃描.注意,以上規(guī)則只針對(duì)各種索引列有效.如果有column沒(méi)有被索引，查詢效率也許會(huì)由于你沒(méi)有選取OR而減少.在下面例子中，LOC_ID和REGION上都建有索引.(高效):SELECTLOC_ID,LOC_DESC,REGIONFROMLOCATIONWHERELOC_ID=10UNIONSELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHEREREGION='MELBOURNE'(低效):SELECTLOC_ID,LOC_DESC,REGIONFROMLOCATIONWHERELOC_ID=10ORREGION='MELBOURNE'如果你堅(jiān)持要用OR，那就需要返回記錄至少索引列寫(xiě)在最前面.8、用IN來(lái)替代OR這是一條簡(jiǎn)樸易記規(guī)則，但是實(shí)際執(zhí)行效果還須檢查，在ORACLE8i下，兩者執(zhí)行途徑似乎是相似．低效:SELECT….FROMLOCATIONWHERELOC_ID=10ORLOC_ID=20ORLOC_ID=30高效:SELECT…FROMLOCATIONWHERELOC_ININ(10,20,30);二、SQL語(yǔ)句構(gòu)造優(yōu)化1、SELECT子句中避免使用‘*‘：2、用TRUNCATE代替DELETE：用TRUNCATE代替DELETE刪除全表記錄：（大數(shù)據(jù)量表用次辦法）當(dāng)刪除表中記錄時(shí),在普通狀況下,回滾段(rollbacksegments)用來(lái)存儲(chǔ)可以被恢復(fù)信息.如果你沒(méi)有COMMIT事務(wù),ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前狀態(tài)(精確地說(shuō)是恢復(fù)到執(zhí)行刪除命令之前狀況)而當(dāng)運(yùn)用TRUNCATE時(shí)，回滾段不再存儲(chǔ)任何可被恢復(fù)信息.3、用Where子句替代HAVING子句：避免使用HAVING子句，HAVING只會(huì)在檢索出所有記錄之后才對(duì)成果集進(jìn)行過(guò)濾.這個(gè)解決需要排序,總計(jì)等操作.如果能通過(guò)WHERE子句限制記錄數(shù)目,那就能減少這方面開(kāi)銷(xiāo).(非oracle中)on、where、having這三個(gè)都可以加條件子句中，on是最先執(zhí)行，where次之，having最后，由于on是先把不符合條件記錄過(guò)濾后才進(jìn)行記錄，它就可以減少中間運(yùn)算要解決數(shù)據(jù)，按理說(shuō)應(yīng)當(dāng)速度是最快，where也應(yīng)當(dāng)比having快點(diǎn)4、sql語(yǔ)句用大寫(xiě)由于oracle總是先解析sql語(yǔ)句，把小寫(xiě)字母轉(zhuǎn)換成大寫(xiě)再執(zhí)行。5、在Java代碼中盡量少用連接符“＋”連接字符串！6、避免變化索引列類(lèi)型.:當(dāng)比較不同數(shù)據(jù)類(lèi)型數(shù)據(jù)時(shí)，ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)樸類(lèi)型轉(zhuǎn)換.假設(shè)EMPNO是一種數(shù)值類(lèi)型索引列.SELECT…FROMEMPWHEREEMPNO=‘123'事實(shí)上,通過(guò)ORACLE類(lèi)型轉(zhuǎn)換，語(yǔ)句轉(zhuǎn)化為:SELECT…FROMEMPWHEREEMPNO=TO_NUMBER(‘123')幸運(yùn)是,類(lèi)型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上,索引用途沒(méi)有被變化.當(dāng)前,假設(shè)EMP_TYPE是一種字符類(lèi)型索引列.SELECT…FROMEMPWHEREEMP_TYPE=123這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為:SELECT…FROMEMPWHERETO_NUMBER(EMP_TYPE)=123由于內(nèi)部發(fā)生類(lèi)型轉(zhuǎn)換，這個(gè)索引將不會(huì)被用到！為了避免ORACLE對(duì)你SQL進(jìn)行隱式類(lèi)型轉(zhuǎn)換,最佳把類(lèi)型轉(zhuǎn)換用顯式體現(xiàn)出來(lái).注意當(dāng)字符和數(shù)值比較時(shí)，ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類(lèi)型到字符類(lèi)型7、優(yōu)化GROUPBY:提高GROUPBY語(yǔ)句效率，可以通過(guò)將不需要記錄在GROUPBY之前過(guò)濾掉.下面兩個(gè)查詢返回相似成果但第二個(gè)明顯就快了許多.低效:1SELECTJOB,AVG(SAL)FROMEMPGROUPbyJOBHAVINGJOB='PRESIDENT'ORJOB='MANAGER'高效:1SELECTJOB,AVG(SAL)FROMEMPWHEREJOB='PRESIDENT'ORJOB='MANAGER'GROUPbyJOB數(shù)據(jù)庫(kù)優(yōu)化方案運(yùn)用表分區(qū)分區(qū)將數(shù)據(jù)在物理上分隔開(kāi)，不同分區(qū)數(shù)據(jù)可以制定保存在處在不同磁盤(pán)上數(shù)據(jù)文獻(xiàn)里。這樣，當(dāng)對(duì)這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間，此外處在不同磁盤(pán)分區(qū)也將對(duì)這個(gè)表數(shù)據(jù)傳播分散在不同磁盤(pán)I/O，一種精心設(shè)立分區(qū)可以將數(shù)據(jù)傳播對(duì)磁盤(pán)I/O競(jìng)爭(zhēng)均勻地分散開(kāi)。對(duì)數(shù)據(jù)量大時(shí)時(shí)表可采用此辦法?？砂丛伦詣?dòng)建表分區(qū)。別名使用別名是大型數(shù)據(jù)庫(kù)應(yīng)用技巧，就是表名、列名在查詢中以一種字母為別名，查詢速度要比建連接表快1.5倍。索引Index優(yōu)化設(shè)計(jì)索引可以大大加快數(shù)據(jù)庫(kù)查詢速度，索引把表中邏輯值映射到安全RowID，因而索引能進(jìn)行迅速定位數(shù)據(jù)物理地址。對(duì)一種建有索引大型表查詢時(shí)，索引數(shù)據(jù)也許會(huì)用完所有數(shù)據(jù)塊緩存空間，ORACLE不得不頻繁地進(jìn)行磁盤(pán)讀寫(xiě)來(lái)獲取數(shù)據(jù)，因而在對(duì)一種大型表進(jìn)行分區(qū)之后，可以依照相應(yīng)分區(qū)建立分區(qū)索引。但是個(gè)人覺(jué)得不是所有表都需要建立索引，只針對(duì)大數(shù)據(jù)量表建立索引。缺陷：第一，創(chuàng)立索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量增長(zhǎng)而增長(zhǎng)。第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一種索引還要占一定物理空間，如果要建立聚簇索引，那么需要空間就會(huì)更大。第三，當(dāng)對(duì)表中數(shù)據(jù)進(jìn)行增長(zhǎng)、刪除和修改時(shí)候，索引也要?jiǎng)討B(tài)維護(hù)，這樣就減少了數(shù)據(jù)維護(hù)速度。索引需要維護(hù)：為了維護(hù)系統(tǒng)性能，索引在創(chuàng)立之后，由于頻繁地對(duì)數(shù)據(jù)進(jìn)行增長(zhǎng)、刪除、修改等操作使得索引頁(yè)發(fā)生碎塊，因而，必要對(duì)索引進(jìn)行維護(hù)。調(diào)節(jié)硬盤(pán)I/O這一步是在信息系統(tǒng)開(kāi)發(fā)之前完畢。數(shù)據(jù)庫(kù)管理員可以將構(gòu)成同一種表空間數(shù)據(jù)文獻(xiàn)放在不同硬盤(pán)上，做到硬盤(pán)之間I/O負(fù)載均衡。在磁盤(pán)比較富裕狀況下還應(yīng)當(dāng)遵循如下原則：將表和索引分開(kāi)；創(chuàng)造顧客表空間，與系統(tǒng)表空間（system）分開(kāi)磁盤(pán)；創(chuàng)立表和索引時(shí)指定不同表空間；創(chuàng)立回滾段專(zhuān)用表空間，防止空間競(jìng)爭(zhēng)影響事務(wù)完畢；創(chuàng)立暫時(shí)表空間用于排序操作，盡量防止數(shù)據(jù)庫(kù)碎片存在于各種表空間中。咱們?cè)谑褂梦锘晥D過(guò)程中基本可以“把它當(dāng)作一種實(shí)際數(shù)據(jù)表來(lái)看待”，不用再緊張視圖自身基本表效率、優(yōu)化等物化視圖1.對(duì)于復(fù)雜而高消耗查詢，如果使用頻繁，應(yīng)建成物化視圖2.物化視圖是一種典型以空間換時(shí)間性能優(yōu)化方式3.對(duì)于更新頻繁表慎用物化視圖4.選取適當(dāng)刷新方式普通視圖是虛擬，而物化視圖是實(shí)實(shí)在在數(shù)據(jù)區(qū)域，是要占據(jù)存儲(chǔ)空間。固然，物化視圖在創(chuàng)立和管理上和普通視圖有不同地方。相比來(lái)講，物化視圖占用了一定存儲(chǔ)空間，此外系統(tǒng)刷新物化視圖也需要耗費(fèi)一定資源，但是它卻換來(lái)了效率和靈活性。減少I(mǎi)O與網(wǎng)絡(luò)傳播次數(shù)1.盡量用較少數(shù)據(jù)庫(kù)祈求，獲取到需要數(shù)據(jù)，能一次性取出不分多次取出2.對(duì)于頻繁操作數(shù)據(jù)庫(kù)批量操作，應(yīng)采用存儲(chǔ)過(guò)程，減少不必要網(wǎng)絡(luò)傳播死鎖與阻塞1.對(duì)于需要頻繁更新數(shù)據(jù)，盡量避免放在長(zhǎng)事務(wù)中，以免導(dǎo)致連鎖反映2.不是迫不得已，最佳不要在ORACLE鎖機(jī)制外再加自己設(shè)計(jì)鎖3.減少事務(wù)大小，及時(shí)提交事務(wù)4.盡量避免跨數(shù)據(jù)庫(kù)分布式事務(wù)，由于環(huán)境復(fù)雜性，很容易導(dǎo)致阻塞5.慎用位圖索引，更新時(shí)容易導(dǎo)致死鎖自動(dòng)增長(zhǎng)表分區(qū)：該程序可以做為一種OracleJOB執(zhí)行在每月28日前執(zhí)行(考慮2月28天因素),自動(dòng)為該顧客下分區(qū)表增長(zhǎng)分區(qū).createorreplaceprocedureguan_add_partition/*/*為一種顧客下所有分區(qū)表自動(dòng)增長(zhǎng)分區(qū).分區(qū)列為date類(lèi)型,分區(qū)名類(lèi)似:p06./*createbyDavid*/asv_table_namevarchar2(50);v_partition_namevarchar2(50);v_monthchar(6);v_add_month_1char(6);v_sql_stringvarchar2();v_add_monthvarchar2(20);cursorcur_partisselectdistinctu.table_name,max(p.partition_name)max_part_namefromuser_tablesu,user_tab_partitionspwhereu.table_name=p.table_namea