基Oracle數據庫的幾種常見SQL優(yōu)化策略

饒淑珍

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

關鍵詞：oracle；數據庫；SQL語句；查詢；優(yōu)化

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

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

1引言

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

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

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

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

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

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

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

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

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

3）盡量避免通配符首位出現

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

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

此時索引無法被利用，但當通配符出現在其他位置時，就可以利用索引，例如：

Select*from sample where sample_name hke‘李%；

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

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

5）用MERGE改寫UPDATE子查詢

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

MERGE INTO Target AS A

USING Source AS B

ON A.aac002=B.aac002

WHEN MATCHED——-當aac002匹配時，目標表進行數據更新

THEN UPDATE SET A.aac001=B.aac001

WHEN NOT MATCHED——-當目標表未查詢到，在原表有的則進行插入

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

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

THEN DELETE

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

6）習慣使用COMMIT命令

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

①回滾段上用于恢復數據的記錄信息；

②被程序語句獲得的鎖；

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

④ORACLE為管理上述3種資源占用的內部開銷。

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

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

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

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

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

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

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

5結束語

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