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

1、.數(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)資源的請(qǐng)求，使用快照和顯形圖等分布式數(shù)據(jù)庫(kù)對(duì)象可以減少對(duì)數(shù)據(jù)庫(kù)的查詢次數(shù)。（2）盡量使用相同的或非常類似的SQL語(yǔ)句進(jìn)行查詢，這樣不僅充分利用SQL共享池中的已經(jīng)分析的語(yǔ)法樹，要查詢的數(shù)據(jù)在SGA中命中的可能性也會(huì)大大增加。（ 3）避免不帶任何條件的 SQL語(yǔ)句的執(zhí)行。沒(méi)有任何條件的 SQL語(yǔ)句在執(zhí)行時(shí)，通常要進(jìn)行 FTS，數(shù)據(jù)庫(kù)先定位一個(gè)數(shù)據(jù)塊，然后按順序依次查找其它數(shù)據(jù)，對(duì)于大型表這將是一個(gè)漫長(zhǎng)的過(guò)程。（ 4）如果對(duì)有些表中的數(shù)據(jù)有約束，最好

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

3、QL當(dāng)中盡量不采用IN 操作符。優(yōu)化 sql 時(shí)，經(jīng)常碰到使用 in 的語(yǔ)句，一定要用 exists 把它給換掉，因?yàn)?Oracle 在處理 In 時(shí)是按 Or 的方式做的，即使使用了索引也會(huì)很慢。2、 NOT IN 操作符強(qiáng)列推薦不使用的，因?yàn)樗荒軕?yīng)用表的索引。用NOT EXISTS或（外連接 +判斷為空）方案代替3、IS NULL或 IS NOT NULL操作判斷字段是否為空一般是不會(huì)應(yīng)用索引的，因?yàn)锽 樹索引是不索引空值的。用其它相同功能的操作運(yùn)算代替，a is not null 改為a>0 或 a>等。不允許字段為空，而用一個(gè)缺省值代替空值，如業(yè)擴(kuò)申請(qǐng)中狀態(tài)字段不允許為空

4、，缺省為申請(qǐng)。避免在索引列上使用IS NULL 和 IS NOT NULL 避免在索引中使用任何可以為空的列，ORACLE將無(wú)法使用該索引對(duì)于單列索引，如果列包含空值，索引中將不存在此記錄.對(duì)于復(fù)合索引，如果每個(gè)列都為空，索引中同樣不存在此記錄.如果至少有一個(gè)列不為空，則記錄存在于索引中舉例:如果唯一性索引建立在表的A 列和 B 列上 ,并且表中存在一條記錄的 A,B 值為 (123,null) , ORACLE 將不接受下一條具有相同A,B 值（ 123,null ）的記錄 (插入 ).然而如果所有的索引列都為空，ORACLE 將認(rèn)為整個(gè)鍵值為空而空不等于空.因此你可以插入1000 條具有相

5、同鍵值的記錄,當(dāng)然它們都是空!因?yàn)榭罩挡淮嬖谟谒饕兄?所以 WHERE 子.句中對(duì)索引列進(jìn)行空值比較將使ORACLE停用該索引 .低效 : (索引失效 )SELECTFROM DEPARTMENT WHERE DEPTCODE ISNOTNULL;高效 : (索引有效 )SELECTFROM DEPARTMENT WHERE DEPT_CODE >=0;4、>及 < 操作符（大于或小于操作符）大于或小于操作符一般情況下是不用調(diào)整的，因?yàn)樗兴饕蜁?huì)采用索引查找，但有的情況下可以對(duì)它進(jìn)行優(yōu)化，如一個(gè)表有100 萬(wàn)記錄，一個(gè)數(shù)值型字段A，30 萬(wàn)記錄的A=0，30 萬(wàn)記錄的 A

6、=1， 39 萬(wàn)記錄的 A=2，1 萬(wàn)記錄的 A=3。那么執(zhí)行 A>2 與 A>=3 的效果就有很大的區(qū)別了， 因?yàn)?A>2 時(shí) ORACLE會(huì)先找出為 2 的記錄索引再進(jìn)行比較， 而 A>=3 時(shí) ORACLE則直接找到 =3 的記錄索引。用>=替代 >高效 :SELECTFROM DEPARTMENT WHERE DEPT_CODE >=0;低效 :SELECT*FROM EMPWHERE DEPTNO >3兩者的區(qū)別在于, 前者 DBMS 將直接跳到第一個(gè)DEPT等于 4 的記錄而后者將首先定位到 DEPT NO=3的記錄并且向前掃描到第一

7、個(gè)DEPT大于 3 的記錄 .5、LIKE操作符：LIKE操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢，但是如果用得不好則會(huì)產(chǎn)生性能上的問(wèn)題，如LIKE %5400%這種查詢不會(huì)引用索引，而LIKEX5400%則會(huì)引用范圍索引。一個(gè)實(shí)際例子：用YW_YHJBQK表中營(yíng)業(yè)編號(hào)后面的戶標(biāo)識(shí)號(hào)可來(lái)查詢營(yíng)業(yè)編號(hào)YY_BH LIKE %5400% 這個(gè)條件會(huì)產(chǎn)生全表掃描，如果改成YY_BHLIKE X5400% OR YY_BH LIKE B5400%則會(huì)利用YY_BH的索引進(jìn)行兩個(gè)范圍的查詢，性能肯定大大提高。6、用 EXISTS替換 DISTINCT：當(dāng)提交一個(gè)包含一對(duì)多表信息

8、(比如部門表和雇員表)的查詢時(shí) ,避免在SELECT子句中使用 DISTINCT. 一般可以考慮用 EXIST 替換 , EXISTS使查詢更為迅速 ,因?yàn)?RDBMS核心模塊將在子查詢的條件一旦滿足后 ,立刻返回結(jié)果 .例子：(低效 ):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.

9、DEPT_NO);如：.用 EXISTS替代 IN、用 NOT EXISTS替代 NOT IN：在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下,使用 EXISTS(或 NOT EXISTS)通常將提高查詢的效率.在子查詢中 ,NOT IN 子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況下,NOT IN 都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷 ).為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或 NOT EXISTS.例子：(高效 ):SELECT*FROM EMP基(礎(chǔ)表 )WHERE EMPNO >

10、0ANDEXISTS(SELECT'X'FROM DEPTWHERE DEPT.DEPTNO= .EMPDETNO AND LOC='MELB')(低效 ):SELECT*FROM EMP基(礎(chǔ)表 )WHERE EMPNO >0AND DEPTNOIN(SELECT DEP TNOFROM DEPT WHERE LOC ='MELB')7、用 UNION 替換 OR (適用于索引列 )通常情況下 , 用 UNION 替換 WHERE 子句中的OR 將會(huì)起到較好的效果.對(duì)索引列使用OR 將造成全表掃描. 注意 ,以上規(guī)則只針對(duì)多個(gè)索引列有效.

11、如果有 column 沒(méi)有被索引 , 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR 而降低 . 在下面的例子中, LOC_ID和 REGION 上都建有索引.(高效 ):SELECT LOC_ID,LOC_DESC,REGIONFROM LOCATION WHERE LOC_ID =10UNIONSELECT LOC_ID ,LOC_DESC ,REGIONFROM LOCATION WHERE REGION='MELBOURNE'(低效 ):SELECT LOC_ID,LOC_DESC,REGIONFROMLOCATION WHERE LOC_ID= 10OR REGION = '

12、;MELBOURNE'如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫在最前面.8、用 IN 來(lái)替換 OR這是一條簡(jiǎn)單易記的規(guī)則，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)，在ORACLE8i下，兩者的執(zhí)行路徑似乎是相同的低效 :SELECT.FROM LOCATION WHEREOCL_ID =10OR LOC_ID=20OR LOC_ID=30高效 :SELECTFROM LOCATION WHERE LOC_IN IN (10,20,30);二、 SQL語(yǔ)句結(jié)構(gòu)優(yōu)化1、SELECT子句中避免使用* ：2、用 TRUNCATE替代 DELETE ：.用 TRUNCATE替代 DELETE刪除全

13、表記錄： （大數(shù)據(jù)量的表用次方法）當(dāng)刪除表中的記錄時(shí) ,在通常情況下 ,回滾段 (rollback segments )用來(lái)存放可以被恢復(fù)的信息 . 如果你沒(méi)有COMMIT 事務(wù) ,ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說(shuō)是恢復(fù)到執(zhí)行刪除命令之前的狀況)而當(dāng)運(yùn)用TRUNCATE 時(shí) , 回滾段不再存放任何可被恢復(fù)的信息.3、用 Where 子句替換HAVING 子句：避免使用 HAVING 子句 , HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾.這個(gè)處理需要排序 ,總計(jì)等操作 .如果能通過(guò) WHERE 子句限制記錄的數(shù)目 ,那就能減少這方面的開銷. (非 oracle 中

14、 )on 、where 、having 這三個(gè)都可以加條件的子句中， on 是最先執(zhí)行， where 次之， having 最后，因?yàn)?on 是先把不符合條件的記錄過(guò)濾后才進(jìn)行統(tǒng)計(jì)，它就可以減少中間運(yùn)算要處理的數(shù)據(jù)，按理說(shuō)應(yīng)該速度是最快的，where也應(yīng)該比 having 快點(diǎn)的4、sql 語(yǔ)句用大寫因?yàn)?oracle 總是先解析sql 語(yǔ)句，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行。5、在 Java 代碼中盡量少用連接符“”連接字符串！6、避免改變索引列的類型.:當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí) , ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換 . 假設(shè) EMPNO 是一個(gè)數(shù)值類型的索引列 .SELECTFROM

15、 EMP WHERE EMPNO =123' 實(shí)際上 ,經(jīng)過(guò) ORACLE類型轉(zhuǎn)換 , 語(yǔ)句轉(zhuǎn)化為 :SELECTFROM EMP WHERE EMPNO = TO_NUMBER( 123')幸運(yùn)的是 , 類型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上 ,索引的用途沒(méi)有被改變 .現(xiàn)在 ,假設(shè) EMP_TYPE 是一個(gè)字符類型的索引列 .SELECTFROM EMP WHERE EMP_TYPE =123這個(gè)語(yǔ)句被ORACLE轉(zhuǎn)換為 :SELECTFROM EMP WHERETO_NUMBER(EMPTYPE)=123因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到! 為了避免 ORACLE對(duì)你的 S

16、QL 進(jìn)行隱式的類型轉(zhuǎn)換,最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來(lái).注意當(dāng)字符和數(shù)值比較時(shí), ORACLE會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型7、優(yōu)化 GROUP BY:提高 GROUP BY 語(yǔ)句的效率 , 可以通過(guò)將不需要的記錄在GROUP BY之前過(guò)濾掉 .下面兩個(gè)查詢返回相同結(jié)果但第二個(gè)明顯就快了許多.低效 :1SELECT JOB,AVG(SAL)FROM EMP GROUPby JOBHAVING JOB= 'PRESIDENT' OR JOB ='MANAGER'高效 :1SELECT JOB,AVG(SAL)FROM EMP WHERE JOB ='PRES

17、IDENT'OR JOB='MANAGER'GROUPby JOB.數(shù)據(jù)庫(kù)優(yōu)化方案1. 利用表分區(qū)分區(qū)將數(shù)據(jù)在物理上分隔開， 不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。這樣，當(dāng)對(duì)這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間， 另外處于不同磁盤的分區(qū)也將對(duì)這個(gè)表的數(shù)據(jù)傳輸分散在不同的磁盤I/O ，一個(gè)精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對(duì)磁盤I/O 競(jìng)爭(zhēng)均勻地分散開。對(duì)數(shù)據(jù)量大的時(shí)時(shí)表可采取此方法?？砂丛伦詣?dòng)建表分區(qū)。2. 別名的使用別名是大型數(shù)據(jù)庫(kù)的應(yīng)用技巧，就是表名、列名在查詢中以一個(gè)字母為別名，查詢速度要比建連接表快1.5

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

19、會(huì)更大。第三，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。索引需要維護(hù)：為了維護(hù)系統(tǒng)性能，索引在創(chuàng)建之后，由于頻繁地對(duì)數(shù)據(jù)進(jìn)行增加、刪除、修改等操作使得索引頁(yè)發(fā)生碎塊，因此，必須對(duì)索引進(jìn)行維護(hù)。4. 調(diào)整硬盤 I/O這一步是在信息系統(tǒng)開發(fā)之前完成的。數(shù)據(jù)庫(kù)管理員可以將組成同一個(gè)表空間的數(shù)據(jù)文件放在不同的硬盤上， 做到硬盤之間 I/O 負(fù)載均衡。 在磁盤比較富裕的情況下還應(yīng)該遵循以下原則：將表和索引分開；創(chuàng)造用戶表空間，與系統(tǒng)表空間（system）分開磁盤；創(chuàng)建表和索引時(shí)指定不同的表空間；創(chuàng)建回滾段專用的表空間，防止空間競(jìng)爭(zhēng)影響事務(wù)的完成；創(chuàng)建臨時(shí)表

20、空間用于排序操作，盡可能的防止數(shù)據(jù)庫(kù)碎片存在于多個(gè)表空間中。我們?cè)谑褂梦锘晥D的過(guò)程中基本可以“把它當(dāng)作一個(gè)實(shí)際的數(shù)據(jù)表來(lái)看待” ，不用再擔(dān)心視圖本身的基礎(chǔ)表的效率、優(yōu)化等物化視圖1.對(duì)于復(fù)雜而高消耗的查詢，如果使用頻繁，應(yīng)建成物化視圖2.物化視圖是一種典型的以空間換時(shí)間的性能優(yōu)化方式3.對(duì)于更新頻繁的表慎用物化視圖4.選擇合適的刷新方式.一般的視圖是虛擬的，而物化視圖是實(shí)實(shí)在在的數(shù)據(jù)區(qū)域，是要占據(jù)存儲(chǔ)空間的。當(dāng)然，物化視圖在創(chuàng)建和管理上和一般的視圖有不同的地方。相比來(lái)講， 物化視圖占用了一定的存儲(chǔ)空間， 另外系統(tǒng)刷新物化視圖也需要耗費(fèi)一定的資源， 但是它卻換來(lái)了效率和靈活性。減少 IO 與網(wǎng)

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

22、 or replace procedure guan_add_partition/*/* 為一個(gè)用戶下所有分區(qū)表自動(dòng)增加分區(qū).分區(qū)的列為date 類型 ,分區(qū)名類似 :p200706./*create by David*/asv_table_name varchar2(50);v_partition_name varchar2(50);v_month char(6);v_add_month_1 char(6);v_sql_string varchar2(2000);v_add_month varchar2(20);cursor cur_part is select distinct u.table_name,max(p.partition_name) max_part_name from user_tables u,user_tab_partitions pwhere u.table_name=p.table_name and u.partitioned = 'YES'group b