饒淑珍

摘要：針對當(dāng)前較為常見的Oracle數(shù)據(jù)庫應(yīng)用，尤其是數(shù)據(jù)體量越來越大的現(xiàn)狀下，文章提出了幾種切合實際應(yīng)用的優(yōu)化方式，在一定程度上提高了SQL語句的執(zhí)行效率，極大地加快了數(shù)據(jù)的查詢檢索等操作，充分實現(xiàn)了優(yōu)化的目的。

關(guān)鍵詞：oracle；數(shù)據(jù)庫；SQL語句；查詢；優(yōu)化

中圖分類號：TP311.13 文獻標(biāo)識碼：A 文章編號：1009-3044（2018）08-0012-02

隨著大數(shù)據(jù)、物聯(lián)網(wǎng)等概念的興起，關(guān)于數(shù)據(jù)的分析應(yīng)用逐步從后臺走向了前臺，生活中各類的軟件開發(fā)、終端應(yīng)用都離不開背后支撐整個環(huán)境的數(shù)據(jù)本身，動輒上億甚至百千億級別的數(shù)據(jù)亦不罕見，面對如此龐大的數(shù)據(jù)量，效率“二字”也被提升上了一個空前的高度之上。作為目前使用最為廣泛應(yīng)用的SQL數(shù)據(jù)庫語句，如何對其做性能優(yōu)化，提升工作效率，去促成更強的決策能力、洞察力與最優(yōu)化處理，成為信息產(chǎn)業(yè)所有人聚焦的核心。本文從Oracle常用的SQL入手，結(jié)合工作教學(xué)，指出了幾種常見的性能優(yōu)化方式。

1引言

就目前而言，Oracle先天存在的跨平臺、可伸縮并行、分布式計算等特性都良好的適配了當(dāng)前大數(shù)據(jù)的背景，其應(yīng)用范圍也在逐漸擴大，對應(yīng)的數(shù)據(jù)體量和復(fù)雜度也不斷增加，提高數(shù)據(jù)庫的處理性能只能依靠硬件資源的升級換代和SQL語句的優(yōu)化，相比之下，SQL的語句優(yōu)化貫穿持續(xù)整個軟件運行周期，其必然性不言而喻。

2 SQL語句優(yōu)化的原則

從程序開發(fā)角度來看，對于Oracle數(shù)據(jù)的操作是通過SQL語句來實現(xiàn)的，其數(shù)據(jù)的精煉程度，直接決定了交互的效率，是提高整個數(shù)據(jù)庫性能的最立竿見影且成本最低的方式。優(yōu)化的原則就是從成本人手，減少數(shù)據(jù)訪問量、減少交互次數(shù)，從而返回更少的數(shù)據(jù)，達到減少CPU和內(nèi)存開銷，最終增加可用資源，即漏斗原則。

3 Oracle數(shù)據(jù)庫中常見的優(yōu)化策略

1）合理的創(chuàng)建和使用索引

根據(jù)表的大小和使用率來創(chuàng)建索引，作為數(shù)據(jù)庫管理員來說，索引一樣使用系統(tǒng)開銷，并非所有的表都需要創(chuàng)建索引，為合適的表創(chuàng)建索引而非為所有的表創(chuàng)建索引。一般來說不需要為比較小的表創(chuàng)建索引，因為即使創(chuàng)建了索引，其性能并沒有得到改善，反而要付出建立索引的開銷和維護成本，得不償失。而對于較大的表，要首先分析表中需要查詢的數(shù)據(jù)量，若經(jīng)常需要查詢的數(shù)據(jù)不超過15%，則創(chuàng)建索引的必要性就需要斟酌了，15%只是實際的一個經(jīng)驗數(shù)據(jù)，也可以測試一下全表查詢的時間，和建立索引相對比時間是否縮短，來評估索引是否建立的必要性。

按照經(jīng)驗來說，索引可以提高Oracle數(shù)據(jù)庫的查詢效率，但數(shù)據(jù)庫進行更新時，包括增加、刪除、改寫等，都會對索引進行更新，當(dāng)索引越多時，占用資源的開銷就會越大，需要在兩個之間尋求一個平衡點。原則上當(dāng)表的記錄更新為主時，不要見太多索引，當(dāng)表數(shù)據(jù)需要頻繁調(diào)用時，則需要比較多的索引。

2）大數(shù)據(jù)量查詢記錄數(shù)優(yōu)化

在目前大數(shù)據(jù)的背景下，以億為單位數(shù)據(jù)時常遇到，一般數(shù)據(jù)庫操作人員習(xí)慣使用Count（*）來統(tǒng)計表的記錄數(shù)，Count命令執(zhí)行的是Table Full Scan，也就是全表掃描，會占用極大地資源開銷。實際上，無論是否建有索引，都應(yīng)該使用sysindexes來進行查詢，可以很快的返回結(jié)果。

3）盡量避免通配符首位出現(xiàn)

當(dāng)通配符（%）在搜索詞的首位出現(xiàn)時，Oracle將不適用數(shù)據(jù)表的索引，雖然很多情況下我們無法避免此類情況，但要慎重執(zhí)行，通配符首位出現(xiàn)時會降低查詢的效率。例如我們在sample表中查詢包含“子”的人，通常采用：

Select*from sample where sample_name hke‘%子%；

此時索引無法被利用，但當(dāng)通配符出現(xiàn)在其他位置時，就可以利用索引，例如：

Select*from sample where sample_name hke‘李%；

4）盡量使用UNION ALL代替UNION命令

UNION執(zhí)行的是表連接并去重復(fù)的操作，包含了多表的連接、結(jié)果集的排序、重復(fù)數(shù)據(jù)的剔除，而在實際的運用場景下，尤其是已經(jīng)確定了主鍵的表，并不會產(chǎn)生重復(fù)的數(shù)據(jù)，應(yīng)盡可能地使用UNION ALL代替UNION來進行操作，直接對多表進行合并操作，節(jié)約不必要的資源開銷，尤其是大數(shù)據(jù)量的操作時。

5）用MERGE改寫UPDATE子查詢

MERGE命令是Oracle9i及以后版本新增的命令，其字面上的意思就是合并、兼并，用來合并UPDATE和INSERT語法。通過MERGE命令，根據(jù)一張表或子查詢的連接條件對另外一張表進行查詢，當(dāng)連接條件匹配的時候進行數(shù)據(jù)更新，執(zhí)行UP-DATE操作，無法匹配時為這個表新增一條數(shù)據(jù)，執(zhí)行INSERT操作。這個命的執(zhí)行效率遠高于INSERT+UPDATE，僅進行一次全表掃描就完成了所有工作。例如：

MERGE INTO Target AS A

USING Source AS B

ON A.aac002=B.aac002

WHEN MATCHED——-當(dāng)aac002匹配時，目標(biāo)表進行數(shù)據(jù)更新

THEN UPDATE SET A.aac001=B.aac001

WHEN NOT MATCHED——-當(dāng)目標(biāo)表未查詢到，在原表有的則進行插入

THEN INSERT VALUES（A.aac002，B.aac001）

WHEN WHEN NOT MATCHED BY SOURCE——目標(biāo)表存在，而原表不存在時進行查詢

THEN DELETE

即簡潔完整的執(zhí)行了一系列更新、插入、刪除的操作，又相比傳統(tǒng)的UPDATE和INSERT極大地提高了效率。

6）習(xí)慣使用COMMIT命令

當(dāng)執(zhí)行數(shù)據(jù)操縱語言之后，盡可能地多使用COMMIT命令進行提交操作，對占用的rollback回滾記錄進行釋放，以釋放資源提高整體性能，即釋放以下開銷：

①回滾段上用于恢復(fù)數(shù)據(jù)的記錄信息；

②被程序語句獲得的鎖；

③redo log buffer中的占用的空間；

④ORACLE為管理上述3種資源占用的內(nèi)部開銷。

7）盡量使用WHERE代替HAVING語句

盡可能地減少HAVING語句的使用范圍，在一個完整的SELECT語句中ON最先執(zhí)行，其次是WHERE，再次是聚合函數(shù)計算，最后才是HAVING和排序操作等，如果能在HAVING的前置環(huán)節(jié)限制檢索的數(shù)據(jù)量，就可以減少之后的分組運算開銷，即在分組之前過濾數(shù)據(jù)。綜合來看，一類是WHERE語句和HAVING語句同時存在的SQL語言，通過WHERE字句限制數(shù)據(jù)記錄的數(shù)量，減少后續(xù)聚合運算、分組篩選、排序操作的資源開銷。另一類是WHERE字句和HAVING字句都可以實現(xiàn)的SQL語句，應(yīng)該直接使用WHERE字句代替HAVING字句，此時在篩選排序之前就先行進行了過濾，效率遠高于HAVING字句，例如同樣兩個語句：

8）增加執(zhí)行命令的重復(fù)使用率

SQL語句執(zhí)行完畢后會駐留在高速緩存中，多數(shù)時候命令的執(zhí)行具有重復(fù)性，要盡量保證同一功能SQL語句的一致性，盡量確保重復(fù)執(zhí)行時直接調(diào)用駐留在高速緩存中的執(zhí)行計劃。在某些大數(shù)據(jù)平臺上，一些基礎(chǔ)的SQL語句每秒鐘會重復(fù)執(zhí)行上千次，重復(fù)調(diào)用執(zhí)行計劃可以大大的提高資源使用效率，建議一是使用對象（如表和視圖）的完全合法一致的名稱，二是在應(yīng)用程序中規(guī)范使用變量，最大限度保持全局一致，提高重復(fù)使用率。

4借助優(yōu)化軟件或者購買服務(wù)進行SQL優(yōu)化

無論人工優(yōu)化還是機器軟件優(yōu)化，其目的都是為了更高的提高效率，相比較人工而言，高速發(fā)展的人工智能可以無疲勞、不間斷的進行各種語句性能比較，找到最優(yōu)的路徑，未來的SQL優(yōu)化也將逐步實現(xiàn)智能化的優(yōu)化方式。

例如SQL Turning、SQLExpert更優(yōu)化軟件，以及Tkprof等跟蹤軟件，都是為了采用機器或者量化的方式進行語句的優(yōu)化，使得原本枯燥的語句分析變得更加直觀。而對于一些實力略顯不足的開發(fā)者而言，購買第三方服務(wù)也不失為一個選擇，甚至隨著分工的精細化，在大數(shù)據(jù)的產(chǎn)業(yè)鏈中，會衍生圍繞優(yōu)化的服務(wù)產(chǎn)業(yè)并蓬勃發(fā)展。

5結(jié)束語

其實關(guān)于Oracle的SQL語句優(yōu)化是一個復(fù)雜的過程，上述的一些心得僅僅是應(yīng)用層的一些表現(xiàn)，關(guān)于數(shù)據(jù)庫的運維還涉及底層的資源分配、網(wǎng)絡(luò)層的流量控制和操作系統(tǒng)的構(gòu)架，甚至相同的平臺的不同類型數(shù)據(jù)，其優(yōu)化方式的原則都要隨之改變，而不是教條式的簡單修改，作為我們使用者而言，應(yīng)該多加探索，借助優(yōu)化軟件、跟蹤分析，加以人工辨別，找到一個適合自己的行之有效優(yōu)化方式。