SQL數據庫優化年夜總結之百萬級數據庫優化計劃

SQL數據庫優化年夜總結之百萬級數據庫優化計劃。本站提示廣大學習愛好者：（SQL數據庫優化年夜總結之百萬級數據庫優化計劃）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL數據庫優化年夜總結之百萬級數據庫優化計劃正文

網上關於SQL優化的教程許多，然則比擬混亂。近日有空整頓了一下，寫出來跟年夜家分享一下，個中有毛病和缺乏的處所，還請年夜家改正彌補。

（1） 選擇最有用率的表名次序(只在基於規矩的搜索引擎優化/' target='_blank'>優化器中有用)： ORACLE 的解析器依照從右到左的次序處置FROM子句中的表名，FROM子句中寫在最初的表(基本表 driving table)將被最早處置，在FROM子句中包括多個表的情形下,你必需選擇記載條數起碼的表作為基本表。假如有3個以上的表銜接查詢, 那就須要選擇穿插表(intersection table)下載地址 作為基本表, 穿插表是指誰人被其他表所援用的表.

（2） WHERE子句中的銜接次序．： ORACLE采取自下而上的次序解析WHERE子句,依據這個道理,表之間的銜接必需寫在其他WHERE前提之前, 那些可以過濾失落最年夜數目記載的前提必需寫在WHERE子句的末尾.

（3） SELECT子句中防止應用 ‘ * ‘： ORACLE在解析的進程中, 會將'*' 順次轉換成一切的列名, 這個任務是經由過程查詢數據字典完成的, 這意味著將消耗更多的時光

（4） 削減拜訪get='_blank'>數據庫的次數： ORACLE在外部履行了很多任務: 解析SQL語句, 預算索引的應用率, 綁定變量 , 讀數據塊等；

（5） 在

SQL*Plus , SQL*Forms和Pro*C

中從新設置ARRAYSIZE參數, 可以增長每次get='_blank'>數據庫拜訪的檢索數據量 ,建議值為200

（6） 應用DECODE函數來削減處置時光： 應用DECODE函數可以免反復掃描雷同記載或反復銜接雷同的表.

（7） 整合簡略,有關聯的get='_blank'>數據庫拜訪： 假如你有幾個簡略的get='_blank'>數據庫查詢語句,你可以把它們整合到一個查詢中(即便它們之間沒有關系)

（8） 刪除反復記載： 最高效的刪除反復記載辦法 ( 由於應用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（9） 用TRUNCATE替換DELETE： 當刪除表中的記載時,在平日情形下, 回滾段(rollback segments ) 用來寄存可以被恢復的信息. 假如你沒有COMMIT事務,ORACLE會將數據恢復到刪除之前的狀況(精確地說是恢復到履行刪除敕令之前的狀態) 而當應用TRUNCATE時, 回滾段不再寄存任何可被恢復的信息.當敕令運轉後,數據不克不及被恢復.是以很少的資本被挪用,履行時光也會很短. (譯者按: TRUNCATE只在刪除全表實用,TRUNCATE是DDL不是DML)

（10） 盡可能多應用COMMIT： 只需有能夠,在法式中盡可能多應用COMMIT, 如許法式的機能獲得進步,需求也會由於COMMIT所釋放的資本而削減: COMMIT所釋放的資本: a. 回滾段上用於恢單數據的信息. b. 被法式語句取得的鎖 c. redo log buffer 中的空間 d. ORACLE為治理上述3種資本中的外部消費

（11） 用Where子句調換HAVING子句： 防止應用HAVING子句, HAVING 只會在檢索出一切記載以後才對成果集停止過濾. 這個處置須要排序,總計等操作. 假如能經由過程WHERE子句限制記載的數量,那就可以削減這方面的開支. (非oracle中)on、where、having這三個都可以加前提的子句中，on是最早履行，where次之，having最初，由於on是先把不相符前提的記載過濾後才停止統計，它便可以削減中央運算要處置的數據，按理說應當速度是最快的，where也應當比having快點的，由於它過濾數據後才停止sum，在兩個表聯接時才用on的，所以在一個表的時刻，就剩下where跟having比擬了。在這單表查詢統計的情形下，假如要過濾的前提沒有觸及到要盤算字段，那它們的成果是一樣的，只是where可使用rushmore技巧，而having就不克不及，在速度上後者要慢假如要觸及到盤算的字段，就表現在沒盤算之前，這個字段的值是不肯定的，依據上篇寫的任務流程，where的感化時光是在盤算之前就完成的，而having就是在盤算後才起感化的，所以在這類情形下，二者的成果會分歧。在多表聯接查詢時，on比where更夙興感化。體系起首依據各個表之間的聯接前提，把多個表分解一個暫時表後，再由where停止過濾，然後再盤算，盤算完後再由having停止過濾。因而可知，要想過濾前提起到准確的感化，起首要明確這個前提應當在甚麼時刻起感化，然後再決議放在那邊

（12） 削減對表的查詢： 在含有子查詢的SQL語句中,要特殊留意削減對表的查詢.例子：

 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

（13） 經由過程外部函數進步SQL效力.： 龐雜的SQL常常就義了履行效力. 可以或許控制下面的應用函數處理成績的辦法在現實任務中長短常成心義的

（14） 應用表的別號(Alias)： 當在SQL語句中銜接多個表時, 請應用表的別號並把別號前綴於每一個Column上.如許一來,便可以削減解析的時光並削減那些由Column歧義惹起的語法毛病.

（15） 用EXISTS替換IN、用NOT EXISTS替換NOT IN： 在很多基於基本表的查詢中,為了知足一個前提,常常須要對另外一個表停止聯接.在這類情形下, 應用EXISTS(或NOT EXISTS)平日將進步查詢的效力. 在子查詢中,NOT IN子句將履行一個外部的排序和歸並. 不管在哪一種情形下,NOT IN都是最低效的 (由於它對子查詢中的表履行了一個全表遍歷). 為了不應用NOT IN ,我們可以把它改寫成外銜接(Outer Joins)或NOT EXISTS. 例子：

（高效）SELECT * FROM EMP (基本表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB') (低效)SELECT * FROM EMP (基本表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（16） 辨認'低效履行'的SQL語句： 固然今朝各類關於SQL搜索引擎優化/' target='_blank'>優化的圖形化對象層見疊出,然則寫出本身的SQL對象來處理成績一直是一個最好的辦法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

（17） 用索引進步效力： 索引是表的一個概念部門,用來進步檢索數據的效力，ORACLE應用了一個龐雜的自均衡B-tree構造. 平日,經由過程索引查詢數據比全表掃描要快. 當ORACLE找出履行查詢和Update語句的最好途徑時, ORACLE搜索引擎優化/' target='_blank'>優化器將應用索引. 異樣在聯絡多個表時應用索引也能夠進步效力. 另外一個應用索引的利益是,它供給了主鍵(primary key)的獨一性驗證.。那些LONG或LONG RAW數據類型, 你可以索引簡直一切的列. 平日, 在年夜型表中應用索引特殊有用. 固然,你也會發明, 在掃描小表時,應用索引異樣能進步效力. 固然應用索引能獲得查詢效力的進步,然則我們也必需留意到它的價值. 索引須要空間來存儲,也須要按期保護, 每當有記載在表中增減或索引列被修正時, 索引自己也會被修正. 這意味著每筆記錄的INSERT , DELETE , UPDATE將為此多支付4 , 5 次的磁盤I/O . 由於索引須要額定的存儲空間和處置,那些不用要的索引反而會使查詢反響時光變慢.。按期的重構索引是有需要的.：

 ALTER INDEX REBUILD

(18） 用EXISTS調換DISTINCT： 當提交一個包括一對多表信息(好比部分表和雇員表)的查詢時,防止在SELECT子句中應用DISTINCT. 普通可以斟酌用EXIST調換, EXISTS 使查詢更加敏捷,由於RDBMS焦點模塊將在子查詢的前提一旦知足後,連忙前往成果. 例子： (低效):

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

（19） server/' target='_blank'>sql語句用年夜寫的；由於oracle老是先解析server/' target='_blank'>sql語句，把小寫的字母轉換成年夜寫的再履行

（20） 在java代碼中盡可能罕用銜接符“＋”銜接字符串！

（21） 防止在索引列上應用NOT 平日，　 我們要防止在索引列上應用NOT, NOT會發生在和在索引列上應用函數雷同的影響. 當ORACLE”碰到”NOT,他就會停滯應用索引轉而履行全表掃描.

（22） 防止在索引列上應用盤算． WHERE子句中，假如索引列是函數的一部門．搜索引擎優化/' target='_blank'>優化器將不應用索引而應用全表掃描．

舉例: 低效：

 SELECT … FROM DEPT WHERE SAL * 12 > 25000; 

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

（23） 用>=替換> 高效: SELECT * FROM EMP WHERE DEPTNO >=4 低效: SELECT * FROM EMP WHERE DEPTNO >3 二者的差別在於, 前者DBMS將直接跳到第一個DEPT等於4的記載爾後者將起首定位到DEPTNO=3的記載而且向前掃描到第一個DEPT年夜於3的記載.

（24） 用UNION調換OR (實用於索引列) 平日情形下, 用UNION調換WHERE子句中的OR將會起到較好的後果. 對索引列應用OR將形成全表掃描. 留意, 以上規矩只針對多個索引列有用. 假如有column沒有被索引, 查詢效力能夠會由於你沒有選擇OR而下降. 鄙人面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 假如你保持要用OR, 那就須要前往記載起碼的索引列寫在最後面.

（25） 用IN來調換OR 這是一條簡略易記的規矩，然則現實的履行後果還須磨練，在ORACLE8i下，二者的履行途徑仿佛是雷同的．　

低效:

SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 

高效

SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

（26） 防止在索引列上應用IS NULL和IS NOT NULL 防止在索引中應用任何可認為空的列，ORACLE將沒法應用該索引．關於單列索引，假如列包括空值，索引中將不存在此記載. 關於復合索引，假如每一個列都為空，索引中異樣不存在此記載.　假如至多有一個列不為空，則記載存在於索引中．舉例: 假如獨一性索引樹立在表的A列和B列上, 而且表中存在一筆記錄的A,B值為(123,null) , ORACLE將不接收下一條具有雷同A,B值（123,null）的記載(拔出). 但是假如一切的索引列都為空，ORACLE將以為全部鍵值為空而空不等於空. 是以你可以拔出1000 條具有雷同鍵值的記載,固然它們都是空! 由於空值不存在於索引列中,所以WHERE子句中對索引列停止空值比擬將使ORACLE停用該索引. 低效: (索引掉效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有用) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

（27） 老是應用索引的第一個列： 假如索引是樹立在多個列上, 只要在它的第一個列(leading column)被where子句援用時,搜索引擎優化/' target='_blank'>優化器才會選擇應用該索引. 這也是一條簡略而主要的規矩，當僅援用索引的第二個列時,搜索引擎優化/' target='_blank'>優化器應用了全表掃描而疏忽了索引

(28） 用UNION-ALL 調換UNION ( 假如有能夠的話)： 當SQL 語句須要UNION兩個查詢成果聚集時,這兩個成果聚集會以UNION-ALL的方法被歸並, 然後在輸入終究成果進步行排序. 假如用UNION ALL替換UNION, 如許排序就不是需要了. 效力就會是以獲得進步. 須要留意的是，UNION ALL 將反復輸入兩個成果聚集中雷同記載. 是以列位照樣要從營業需求剖析應用UNION ALL的可行性. UNION 將對成果聚集排序,這個操作會應用到SORT_AREA_SIZE這塊內存. 關於這塊內存的搜索引擎優化/' target='_blank'>優化也是相當主要的. 上面的SQL可以用來查詢排序的消費量

低效：

SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' 

高效:

 SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

（29） 用WHERE替換ORDER BY： ORDER BY 子句只在兩種嚴厲的前提下應用索引. ORDER BY中一切的列必需包括在雷同的索引中並堅持在索引中的分列次序. ORDER BY中一切的列必需界說為非空. WHERE子句應用的索引和ORDER BY子句中所應用的索引不克不及並列.

例如:

表DEPT包括以以下:

DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL

低效: (索引不被應用)

 SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE

高效:(應用索引)

 SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

（30） 防止轉變索引列的類型.: 當比擬分歧數據類型的數據時, ORACLE主動對列停止簡略的類型轉換. 假定 EMPNO是一個數值類型的索引列.

SELECT … FROM EMP WHERE EMPNO = ‘123' 

現實上,經由ORACLE類型轉換, 語句轉化為:

SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 

榮幸的是,類型轉換沒有產生在索引列上,索引的用處沒有被轉變. 如今,假定EMP_TYPE是一個字符類型的索引列.

SELECT … FROM EMP WHERE EMP_TYPE = 123 

這個語句被ORACLE轉換為:

SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 

由於外部產生的類型轉換, 這個索引將不會被用到! 為了不ORACLE對你的SQL停止隱式的類型轉換, 最好把類型轉換用顯式表示出來. 留意當字符和數值比擬時, ORACLE會優先轉換數值類型到字符類型

（31） 須要小心的WHERE子句: 某些SELECT 語句中的WHERE子句不應用索引. 這裡有一些例子. 鄙人面的例子裡, (1)‘!=' 將不應用索引. 記住, 索引只能告知你甚麼存在於表中, 而不克不及告知你甚麼不存在於表中. (2) ‘||'是字符銜接函數. 就象其他函數那樣, 停用了索引. (3) ‘+'是數學函數. 就象其他數學函數那樣, 停用了索引. (4)雷同的索引列不克不及相互比擬,這將會啟用全表掃描.

（32） a. 假如檢索數據量跨越30%的表中記載數.應用索引將沒有明顯的效力進步. b. 在特定情形下, 應用索引或許會比全表掃描慢, 但這是統一個數目級上的差別. 而平日情形下,應用索引比全表掃描要塊幾倍甚至幾千倍!

（33） 防止應用消耗資本的操作: 帶有

DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL

語句會啟動SQL引擎 履行消耗資本的排序(SORT)功效. DISTINCT須要一次排序操作, 而其他的至多須要履行兩次排序. 平日, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方法重寫. 假如你的get='_blank'>數據庫的SORT_AREA_SIZE分配得好, 應用UNION , MINUS, INTERSECT也是可以斟酌的, 究竟它們的可讀性很強

（34） 搜索引擎優化/' target='_blank'>優化GROUP BY: 進步GROUP BY 語句的效力, 可以經由過程將不須要的記載在GROUP BY 之前過濾失落.上面兩個查詢前往雷同成果但第二個顯著就快了很多.

低效:

SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 

高效:

 SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB