培訓sql性能優(yōu)化交流資料ppt課件

1、SQL 性能優(yōu)化交流資料 1交流提綱 SQL 性能優(yōu)化意義 SQL 優(yōu)化建議-系統(tǒng) SQL 優(yōu)化建議-方案 SQL 優(yōu)化建議-語法 SQL 優(yōu)化建議-函數(shù)2一、 SQL 性能優(yōu)化意義1、數(shù)據(jù)庫運用程序的優(yōu)化程序設計中的一個著名定律是20的代碼用去了80的時間；兩種方式優(yōu)化：源代碼的優(yōu)化和SQL語句的優(yōu)化。源代碼的優(yōu)化在時間本錢和風險上代價很高；另一方面，源代碼的優(yōu)化對數(shù)據(jù)庫系統(tǒng)性能的提升收效有限。3一、 SQL 性能優(yōu)化意義2、SQL腳本優(yōu)化的主要緣由SQL語句是對數(shù)據(jù)庫(數(shù)據(jù))進展操作的獨一途徑； SQL語句耗費了70%90%的數(shù)據(jù)庫資源；SQL語句獨立于程序設計邏輯，相對于對程序源代碼的優(yōu)

2、化，對SQL語句的優(yōu)化在時間本錢和風險上的代價都很低；SQL語句可以有不同的寫法； SQL語句易學，難知曉 4交流提綱 SQL 性能優(yōu)化意義 SQL 優(yōu)化建議-系統(tǒng) SQL 優(yōu)化建議-方案 SQL 優(yōu)化建議-語法 SQL 優(yōu)化建議-函數(shù)5二、 SQL 優(yōu)化建議-系統(tǒng)ORACLE 性能優(yōu)化主要方法硬件晉級(CPU、內存、硬盤)；版本及參數(shù)設置；存儲設計表空間、表分區(qū)、索引；6二、 SQL 優(yōu)化建議-系統(tǒng)1、硬件晉級CPU：在任何機器中CPU的數(shù)據(jù)處置才干往往是衡量計算機性能的一個標志，并且ORACLE是一個提供并行才干的數(shù)據(jù)庫系統(tǒng)，假設運轉隊列數(shù)目超越了CPU處置的數(shù)目，性能就會下降；內存：衡量

3、機器性能的另外一個目的就是內存的多少了，在ORACLE中內存和我們在建數(shù)據(jù)庫中的交換區(qū)進展數(shù)據(jù)的交換，讀數(shù)據(jù)時，磁盤I/O必需等待物理I/O操作完成，在出現(xiàn)ORACLE的內存瓶頸時，我們第一個要思索的是添加內存，由于I/O的呼應時間是影響ORACLE性能的主要參數(shù)；網(wǎng)絡條件：NET*SQL擔任數(shù)據(jù)在網(wǎng)絡上的來往，大量的SQL會令網(wǎng)絡速度變慢。比如10M的網(wǎng)卡和100的網(wǎng)卡就對NET*SQL有非常明顯的影響，還有交換機、集線器等等網(wǎng)絡設備的性能對網(wǎng)絡的影響很明顯，建議在任何網(wǎng)絡中不要試圖用3個集線器來將網(wǎng)段互聯(lián)。7二、 SQL 優(yōu)化建議-系統(tǒng)2、版本及參數(shù)設置8二、 SQL 優(yōu)化建議-系統(tǒng)2、參

4、數(shù)-降低ORACLE的競爭： freelists 和 freelist 組：他們擔任ORACLE的處置表和索引的空間管理；pctfree 及 pctused：該參數(shù)決議了freelists 和 freelist 組的行為，pctfree 和pctused 參數(shù)的獨一目的就是為了控制塊如何在 freelists 中進出設置好pctfree 及 pctused對塊在freelists的移走和讀取很重要。9二、 SQL 優(yōu)化建議-系統(tǒng)2、其它參數(shù)1、包括SGA區(qū)系統(tǒng)全局區(qū)：系統(tǒng)全局區(qū)SGA是一個分配給Oracle 的包含一個 Oracle 實例的數(shù)據(jù)庫的控制信息內存段。主要包括數(shù)據(jù)庫高速緩存(the

5、 database buffer cache)，重演日志緩存the redo log buffer，共享池the shared pool，數(shù)據(jù)字典緩存the data dictionary cache以及其它各方面的信息2、db_block_buffers數(shù)據(jù)高速緩沖區(qū)訪問過的數(shù)據(jù)都放在這一片內存區(qū)域，該參數(shù)越大，Oracle在內存中找到一樣數(shù)據(jù)的能夠性就越大，也即加快了查詢速度。3、share_pool_size SQL共享緩沖池：該參數(shù)是庫高速緩存和數(shù)據(jù)字典的高速緩存。4、Log_buffer 重演日志緩沖區(qū)5、sort_area_size排序區(qū))6、processes 同時銜接的進程數(shù)7

6、、db_block_size 數(shù)據(jù)庫塊大小：Oracle默許塊為2KB，太小了，由于假設我們有一個8KB的數(shù)據(jù)，那么2KB塊的數(shù)據(jù)庫要讀4次盤，才干讀完，而8KB塊的數(shù)據(jù)庫只需1次就讀完了，大大減少了I/O操作。數(shù)據(jù)庫安裝完成后，就不能再改動db_block_size的值了，只能重新建立數(shù)據(jù)庫并且建庫時，要選擇手工安裝數(shù)據(jù)庫。8、open_links 同時翻開的鏈接數(shù)9、dml_locks 10、open_cursors 翻開光標數(shù)11、dbwr_io_slaves 后臺寫進程數(shù)10交流提綱 SQL 性能優(yōu)化意義 SQL 優(yōu)化建議-系統(tǒng) SQL 優(yōu)化建議-方案 SQL 優(yōu)化建議-語法 SQL

7、優(yōu)化建議-函數(shù)11三、 SQL 優(yōu)化建議-方案事務型SQL優(yōu)化目的：SQL查詢前往數(shù)據(jù)的時長應控制在10秒內，最遲不超越90秒方案：SQL語法、函數(shù)優(yōu)化合理建索引、分區(qū)表運用自定義函數(shù)、存儲過程運用中間表數(shù)據(jù)匯總表或數(shù)據(jù)集中表12三、 SQL 優(yōu)化建議-方案后臺處置型SQL優(yōu)化目的：提高整體執(zhí)行效率，減少系統(tǒng)資源耗費方案：結合事務型SQL優(yōu)化方法優(yōu)化單條SQL語句運用中間表多層次數(shù)據(jù)匯總表或數(shù)據(jù)集中表設計合理的數(shù)據(jù)存儲構造星型、雪花型13交流提綱 SQL 性能優(yōu)化意義 SQL 優(yōu)化建議-系統(tǒng) SQL 優(yōu)化建議-方案 SQL 優(yōu)化建議-語法 SQL 優(yōu)化建議-函數(shù)14四、 SQL 優(yōu)化建議-語法

8、1、合理運用索引在建索引時，也不是索引越多越好，由于OLTP系統(tǒng)每表超越5個索引即會降低性能，按照實踐運用環(huán)境建立單字段索引或復合索引，選擇合理的索引類型Normal、Unique、Bitmap;當我們用到GROUP BY和ORDER BY時,ORACLE就會自動對數(shù)據(jù)進展排序,而INIT.ORA中決議了sort_area_size區(qū)的大小,當排序不能在我們給定的排序區(qū)完成時,ORACLE就會在磁盤暫時表空間中進展排序,過多的磁盤排序將會令 free buffer waits 的值變高,而這個區(qū)間并不只是用于排序的；select,update,delete 語句中的子查詢該當有規(guī)律地查找少于2

9、0%的表行.假設一個語句查找的行數(shù)超越總行數(shù)的20%,它將不能經(jīng)過運用索引獲得性能上的提高.15四、 SQL 優(yōu)化建議-語法2、選擇最有效率的表名順序ORACLE的解析器按照從右到左的順序處置FROM子句中的表名,因此FROM子句中寫在最后的表(根底表 driving table)將被最先處置； 在FROM子句中包含多個表的情況下,他必需選擇記錄條數(shù)最少的表作為根底表。當ORACLE處置多個表時, 會運用排序及合并的方式銜接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進展派序,然后掃描第二個表(FROM子句中最后第二個表),最后將一切從第二個表中檢索出的記錄與第一個表中適宜記

10、錄進展合并。假設有3個以上的表銜接查詢, 那就需求選擇交叉表(intersection table)作為根底表, 交叉表是指那個被其他表所援用的表 。16四、 SQL 優(yōu)化建議-語法2、選擇最有效率的表名順序例如: 表 TAB1 16,384 條記錄 表 TAB2 1 條記錄選擇TAB2作為根底表 (最好的方法)select count(*) from tab1,tab2 執(zhí)行時間0.96秒 選擇TAB1作為根底表 (不佳的方法)select count(*) from tab2,tab1 執(zhí)行時間26.09秒 17四、 SQL 優(yōu)化建議-語法3、WHERE子句中的銜接順序 ORACLE采用自

11、下而上的順序解析WHERE子句；根據(jù)這個原理,表之間的銜接必需寫在其他WHERE條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必需寫在WHERE子句的末尾。例子：SELECT * FROM EMP E WHERE 25 5000 AND JOB = MANAGER18四、 SQL 優(yōu)化建議-語法4、用TRUNCATE替代DELETE 當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的信息. 假設他沒有COMMIT事務,ORACLE會將數(shù)據(jù)恢復到刪除之前的形狀(準確地說是恢復到執(zhí)行刪除命令之前的情況) 而當運用TRUNCATE時, 回滾段不再存

12、放任何可被恢復的信息.當命令運轉后,數(shù)據(jù)不能被恢復.因此很少的資源被調用,執(zhí)行時間也會很短. 19四、 SQL 優(yōu)化建議-語法5、用Where子句交換HAVING子句 防止運用HAVING子句, HAVING 只會在檢索出一切記錄之后才對結果集進展過濾，這個處置需求排序、總計等操作； 假設能經(jīng)過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.。20四、 SQL 優(yōu)化建議-語法5、用Where子句交換HAVING子句 例子優(yōu)化前：SELECT JOB, SUM(SAL) FROM EMP E GROUP BY JOBHAVING JOB = MANAGER例子優(yōu)化后：SELECT JOB,

13、 SUM(SAL) FROM EMP E WHERE JOB = MANAGER GROUP BY JOB21四、 SQL 優(yōu)化建議-語法6、用EXISTS替代IN 在許多基于根底表的查詢中,為了滿足一個條件,往往需求對另一個表進展聯(lián)接.在這種情況下, 運用EXISTS(或NOT EXISTS)通常將提高查詢的效率 22四、 SQL 優(yōu)化建議-語法6、用EXISTS替代IN 例子優(yōu)化前：SELECT * FROM EMP(根底表) WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB)例子優(yōu)化后：SELECT * FROM EMP(

14、根底表) WHERE EXISTS (SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)23四、 SQL 優(yōu)化建議-語法7、防止采用LIKE通配符匹配查詢 通配符匹配查詢特別耗費時間。即使在條件字段上建立了索引，在這種情況下也還是采用順序掃描的方式。 例子優(yōu)化前：SELECT * FROM customer WHERE zipcode LIKE 524%例子優(yōu)化后：SELECT * FROM customer WHERE ZipCode = 524000 AND ZipCode =12031五、 SQL 優(yōu)化建議-函數(shù)2、 集合Union：并集，剔除反復值Union All：并集Intersect：交集Minus：減集32五、 SQL 優(yōu)化建議-函數(shù)3、 極值LEAST：最小值GREATEST：最大值se