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