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

1、數(shù)據(jù)庫優(yōu)化方案1。 高效地進行SQL語句設計：通常情況下，可以采用下面的方法優(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語句的執(zhí)行。沒有任何條件的SQL語句在執(zhí)行時，通常要進行FTS,數(shù)據(jù)庫先定位一個數(shù)據(jù)塊，然后按順序依次查找其它數(shù)據(jù),對于大型表這將是一個漫長的過程.(4）如果對有些表中的數(shù)據(jù)有約束,最好在建表的SQL

2、語句用描述完整性來實現(xiàn),而不是用SQL程序中實現(xiàn).一、操作符優(yōu)化：1、IN操作符用IN寫出來的SQL的優(yōu)點是比較容易寫及清晰易懂，這比較適合現(xiàn)代軟件開發(fā)的風格。但是用IN的SQL性能總是比較低的，從Oracle執(zhí)行的步驟來分析用IN的SQL與不用IN的SQL有以下區(qū)別:ORACLE試圖將其轉換成多個表的連接,如果轉換不成功則先執(zhí)行IN里面的子查詢，再查詢外層的表記錄,如果轉換成功則直接采用多個表的連接方式查詢。由此可見用IN的SQL至少多了一個轉換的過程.一般的SQL都可以轉換成功,但對于含有分組統(tǒng)計等方面的SQL就不能轉換了。在業(yè)務密集的SQL當中盡量不采用IN操作符。優(yōu)化sql時，經(jīng)常碰到

3、使用in的語句，一定要用exists把它給換掉，因為Oracle在處理In時是按Or的方式做的，即使使用了索引也會很慢。2、 NOT IN操作符強列推薦不使用的，因為它不能應用表的索引。用NOT EXISTS或(外連接+判斷為空)方案代替3、IS NULL或IS NOT NULL操作判斷字段是否為空一般是不會應用索引的，因為B樹索引是不索引空值的.用其它相同功能的操作運算代替，a is not null改為 a>0 或a>'等。不允許字段為空，而用一個缺省值代替空值,如業(yè)擴申請中狀態(tài)字段不允許為空，缺省為申請.避免在索引列上使用IS NULL和IS NOT NULL 避免在

4、索引中使用任何可以為空的列，ORACLE將無法使用該索引對于單列索引，如果列包含空值，索引中將不存在此記錄.對于復合索引，如果每個列都為空，索引中同樣不存在此記錄.如果至少有一個列不為空，則記錄存在于索引中舉例：如果唯一性索引建立在表的A 列和B 列上，并且表中存在一條記錄的A，B 值為(123，null） , ORACLE 將不接受下一條具有相同A，B值（123,null)的記錄（插入）。然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等于空.因此你可以插入1000 條具有相同鍵值的記錄，當然它們都是空!因為空值不存在于索引列中，所以WHERE子句中對索引列進行空值比較將使O

5、RACLE停用該索引。低效： (索引失效）SELECT FROM DEPARTMENT WHERE DEPT_CODE ISNOTNULL；高效： （索引有效)SELECT FROM DEPARTMENT WHERE DEPT_CODE =0;4、>及 操作符（大于或小于操作符）大于或小于操作符一般情況下是不用調整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優(yōu)化，如一個表有100萬記錄，一個數(shù)值型字段 A，30萬記錄的A=0,30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3.那么執(zhí)行A2與A>=3的效果就有很大的區(qū)別了,因為A2時ORACLE會先找出為2的記錄

6、索引再進行比較，而A=3時ORACLE則直接找到=3的記錄索引。用>=替代>高效：SELECT FROM DEPARTMENT WHERE DEPT_CODE >=0；低效：SELECTFROM EMPWHERE DEPTNO >3兩者的區(qū)別在于， 前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPT NO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。5、LIKE操作符：LIKE操作符可以應用通配符查詢,里面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會產(chǎn)生性能上的問題,如LIKE %5400這種查詢不會引用索引,而LIKEX54

7、00'則會引用范圍索引。一個實際例子:用YW_YHJBQK表中營業(yè)編號后面的戶標識號可來查詢營業(yè)編號 YY_BH LIKE5400%這個條件會產(chǎn)生全表掃描,如果改成YY_BH LIKE 'X5400 OR YY_BH LIKE B5400%則會利用YY_BH的索引進行兩個范圍的查詢，性能肯定大大提高。6、用EXISTS替換DISTINCT：當提交一個包含一對多表信息（比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST 替換， EXISTS使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結果。例子：（低

8、效）：SELECTDISTINCT DEPT_NO，DEPT_NAMEFROM DEPT D , EMP EWHERE D.DEPT_NO = E.DEPT_NO（高效）：SELECT DEPT_NO，DEPT_NAMEFROM DEPT D WHEREEXISTS（SELECT'X'FROM EMP EWHERE E.DEPT_NO = D。DEPT_NO)；如：用EXISTS 替代IN、用NOT EXISTS替代NOT IN:在許多基于基礎表的查詢中，為了滿足一個條件,往往需要對另一個表進行聯(lián)接.在這種情況下,使用EXISTS（或NOT EXISTS)通常將提高查詢的效率。

9、在子查詢中,NOT IN 子句將執(zhí)行一個內部的排序和合并。 無論在哪種情況下，NOT IN都是最低效的（因為它對子查詢中的表執(zhí)行了一個全表遍歷)。為了避免使用NOT IN ，我們可以把它改寫成外連接(Outer Joins）或NOT EXISTS。例子：（高效）：SELECT*FROM EMP （基礎表）WHERE EMPNO 0ANDEXISTS(SELECTX'FROM DEPTWHERE DEPT。DEPTNO= EMP。DEPTNO AND LOC='MELB）(低效）：SELECT*FROM EMP （基礎表）WHERE EMPNO 0AND DEPTNOIN（SEL

10、ECT DEP TNOFROM DEPT WHERE LOC =MELB）7、用UNION替換OR （適用于索引列)通常情況下， 用UNION替換WHERE 子句中的OR 將會起到較好的效果。對索引列使用OR 將造成全表掃描. 注意，以上規(guī)則只針對多個索引列有效.如果有column 沒有被索引， 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中， LOC_ID和REGION 上都建有索引。 （高效）：SELECT LOC_ID，LOC_DESC，REGIONFROM LOCATION WHERE LOC_ID =10UNIONSELECT LOC_ID ， LOC_DESC ， REG

11、IONFROM LOCATION WHERE REGION ='MELBOURNE(低效)：SELECT LOC_ID，LOC_DESC,REGIONFROM LOCATION WHERE LOC_ID= 10OR REGION = MELBOURNE'如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面。8、用IN來替換OR這是一條簡單易記的規(guī)則,但是實際的執(zhí)行效果還須檢驗，在ORACLE8i下，兩者的執(zhí)行路徑似乎是相同的低效:SELECT。FROM LOCATION WHERE LOC_ID =10OR LOC_ID=20OR LOC_ID=30高效：SELECTF

12、ROM LOCATION WHERE LOC_IN IN （10,20,30）；二、SQL語句結構優(yōu)化1、SELECT子句中避免使用 ：2、用TRUNCATE替代DELETE ：用TRUNCATE替代DELETE刪除全表記錄：（大數(shù)據(jù)量的表用次方法）當刪除表中的記錄時,在通常情況下，回滾段(rollback segments )用來存放可以被恢復的信息。 如果你沒有COMMIT 事務,ORACLE會將數(shù)據(jù)恢復到刪除之前的狀態(tài)(準確地說是恢復到執(zhí)行刪除命令之前的狀況）而當運用TRUNCATE 時， 回滾段不再存放任何可被恢復的信息.3、用Where子句替換HAVING 子句：避免使用HAVING

13、 子句， HAVING 只會在檢索出所有記錄之后才對結果集進行過濾.這個處理需要排序,總計等操作。如果能通過WHERE 子句限制記錄的數(shù)目,那就能減少這方面的開銷. (非oracle中）on、where、having這三個都可以加條件的子句中，on是最先執(zhí)行，where 次之,having 最后，因為on是先把不符合條件的記錄過濾后才進行統(tǒng)計，它就可以減少中間運算要處理的數(shù)據(jù)，按理說應該速度是最快的， where 也應該比having快點的4、sql語句用大寫因為oracle 總是先解析sql語句，把小寫的字母轉換成大寫的再執(zhí)行。5、在Java代碼中盡量少用連接符“”連接字符串!6、避免改變索

14、引列的類型。:當比較不同數(shù)據(jù)類型的數(shù)據(jù)時， ORACLE自動對列進行簡單的類型轉換。 假設EMPNO 是一個數(shù)值類型的索引列。SELECT FROM EMP WHERE EMPNO = 123實際上,經(jīng)過ORACLE類型轉換， 語句轉化為:SELECT FROM EMP WHERE EMPNO = TO_NUMBER（123'）幸運的是，類型轉換沒有發(fā)生在索引列上,索引的用途沒有被改變。現(xiàn)在，假設EMP_TYPE是一個字符類型的索引列.SELECT FROM EMP WHERE EMP_TYPE =123這個語句被ORACLE轉換為:SELECT FROM EMP WHERETO_NU

15、MBER(EMP_TYPE）=123因為內部發(fā)生的類型轉換， 這個索引將不會被用到！ 為了避免ORACLE對你的SQL 進行隱式的類型轉換，最好把類型轉換用顯式表現(xiàn)出來。注意當字符和數(shù)值比較時， ORACLE會優(yōu)先轉換數(shù)值類型到字符類型7、優(yōu)化GROUP BY:提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY之前過濾掉。下面兩個查詢返回相同結果但第二個明顯就快了許多。低效：1SELECT JOB，AVG（SAL）FROM EMP GROUPby JOBHAVING JOB= PRESIDENT OR JOB ='MANAGER高效：1SELECT JOB，A

16、VG(SAL)FROM EMP WHERE JOB =PRESIDENTOR JOB='MANAGERGROUPby JOB數(shù)據(jù)庫優(yōu)化方案1. 利用表分區(qū)分區(qū)將數(shù)據(jù)在物理上分隔開，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里.這樣,當對這個表進行查詢時，只需要在表分區(qū)中進行掃描,而不必進行全表掃描，明顯縮短了查詢時間，另外處于不同磁盤的分區(qū)也將對這個表的數(shù)據(jù)傳輸分散在不同的磁盤I/O,一個精心設置的分區(qū)可以將數(shù)據(jù)傳輸對磁盤I/O競爭均勻地分散開。對數(shù)據(jù)量大的時時表可采取此方法?？砂丛伦詣咏ū矸謪^(qū)。2. 別名的使用別名是大型數(shù)據(jù)庫的應用技巧，就是表名、列名在查詢中以一個字母為別

17、名，查詢速度要比建連接表快1.5倍.3. 索引Index的優(yōu)化設計索引可以大大加快數(shù)據(jù)庫的查詢速度，索引把表中的邏輯值映射到安全的RowID,因此索引能進行快速定位數(shù)據(jù)的物理地址。對一個建有索引的大型表的查詢時，索引數(shù)據(jù)可能會用完所有的數(shù)據(jù)塊緩存空間，ORACLE不得不頻繁地進行磁盤讀寫來獲取數(shù)據(jù)，因此在對一個大型表進行分區(qū)之后，可以根據(jù)相應的分區(qū)建立分區(qū)索引。但是個人覺得不是所有的表都需要建立索引，只針對大數(shù)據(jù)量的表建立索引。缺點:第一，創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加.第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立

18、聚簇索引，那么需要的空間就會更大.第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。索引需要維護：為了維護系統(tǒng)性能，索引在創(chuàng)建之后,由于頻繁地對數(shù)據(jù)進行增加、刪除、修改等操作使得索引頁發(fā)生碎塊,因此,必須對索引進行維護.4. 調整硬盤I/O這一步是在信息系統(tǒng)開發(fā)之前完成的。數(shù)據(jù)庫管理員可以將組成同一個表空間的數(shù)據(jù)文件放在不同的硬盤上，做到硬盤之間I/O負載均衡。在磁盤比較富裕的情況下還應該遵循以下原則:l 將表和索引分開；l 創(chuàng)造用戶表空間,與系統(tǒng)表空間(system）分開磁盤;l 創(chuàng)建表和索引時指定不同的表空間；l 創(chuàng)建回滾段專用的表空間，防止空

19、間競爭影響事務的完成；l 創(chuàng)建臨時表空間用于排序操作，盡可能的防止數(shù)據(jù)庫碎片存在于多個表空間中.我們在使用物化視圖的過程中基本可以“把它當作一個實際的數(shù)據(jù)表來看待”，不用再擔心視圖本身的基礎表的效率、優(yōu)化等物化視圖1.對于復雜而高消耗的查詢,如果使用頻繁,應建成物化視圖2.物化視圖是一種典型的以空間換時間的性能優(yōu)化方式3。對于更新頻繁的表慎用物化視圖4.選擇合適的刷新方式一般的視圖是虛擬的，而物化視圖是實實在在的數(shù)據(jù)區(qū)域，是要占據(jù)存儲空間的.當然,物化視圖在創(chuàng)建和管理上和一般的視圖有不同的地方.相比來講，物化視圖占用了一定的存儲空間,另外系統(tǒng)刷新物化視圖也需要耗費一定的資源，但是它卻換來了效率

20、和靈活性.減少IO與網(wǎng)絡傳輸次數(shù)1.盡量用較少的數(shù)據(jù)庫請求，獲取到需要的數(shù)據(jù)，能一次性取出的不分多次取出2.對于頻繁操作數(shù)據(jù)庫的批量操作,應采用存儲過程，減少不必要的網(wǎng)絡傳輸死鎖與阻塞1.對于需要頻繁更新的數(shù)據(jù)，盡量避免放在長事務中，以免導致連鎖反應2.不是迫不得已,最好不要在ORACLE鎖機制外再加自己設計的鎖3。減少事務大小,及時提交事務4。盡量避免跨數(shù)據(jù)庫的分布式事務，因為環(huán)境的復雜性，很容易導致阻塞5。慎用位圖索引,更新時容易導致死鎖自動增加表分區(qū)：該程序可以做為一個Oracle的JOB執(zhí)行在每月的28日前執(zhí)行（考慮2月28天的原因）,自動為該用戶下的分區(qū)表增加分區(qū).create or

21、 replace procedure guan_add_partition/為一個用戶下所有分區(qū)表自動增加分區(qū)。分區(qū)的列為date類型，分區(qū)名類似：p200706./*create by David/asv_table_namevarchar2（50）；v_partition_namevarchar2（50)；v_month char（6)；v_add_month_1 char（6）；v_sql_stringvarchar2（2000);v_add_monthvarchar2（20）；cursorcur_part is select distinct u.table_name，max（p.partition_name) max_part_name from user_tablesu,user_tab_partitions pwhereu。table_name=p。tab