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

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

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

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

4、數(shù)-降低ORACLE的競(jìng)爭(zhēng)： freelists 和 freelist 組：他們擔(dān)任ORACLE的處置表和索引的空間管理；pctfree 及 pctused：該參數(shù)決議了freelists 和 freelist 組的行為，pctfree 和pctused 參數(shù)的獨(dú)一目的就是為了控制塊如何在 freelists 中進(jìn)出設(shè)置好pctfree 及 pctused對(duì)塊在freelists的移走和讀取很重要。9二、 SQL 優(yōu)化建議-系統(tǒng)2、其它參數(shù)1、包括SGA區(qū)系統(tǒng)全局區(qū)：系統(tǒng)全局區(qū)SGA是一個(gè)分配給Oracle 的包含一個(gè) Oracle 實(shí)例的數(shù)據(jù)庫(kù)的控制信息內(nèi)存段。主要包括數(shù)據(jù)庫(kù)高速緩存(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ū)訪問(wèn)過(guò)的數(shù)據(jù)都放在這一片內(nèi)存區(qū)域，該參數(shù)越大，Oracle在內(nèi)存中找到一樣數(shù)據(jù)的能夠性就越大，也即加快了查詢速度。3、share_pool_size SQL共享緩沖池：該參數(shù)是庫(kù)高速緩存和數(shù)據(jù)字典的高速緩存。4、Log_buffer 重演日志緩沖區(qū)5、sort_area_size排序區(qū))6、processes 同時(shí)銜接的進(jìn)程數(shù)7

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

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

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

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

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

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

12、放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)轉(zhuǎn)后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短. 19四、 SQL 優(yōu)化建議-語(yǔ)法5、用Where子句交換HAVING子句 防止運(yùn)用HAVING子句, HAVING 只會(huì)在檢索出一切記錄之后才對(duì)結(jié)果集進(jìn)展過(guò)濾，這個(gè)處置需求排序、總計(jì)等操作； 假設(shè)能經(jīng)過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開(kāi)銷.。20四、 SQL 優(yōu)化建議-語(yǔ)法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)化建議-語(yǔ)法6、用EXISTS替代IN 在許多基于根底表的查詢中,為了滿足一個(gè)條件,往往需求對(duì)另一個(gè)表進(jìn)展聯(lián)接.在這種情況下, 運(yùn)用EXISTS(或NOT EXISTS)通常將提高查詢的效率 22四、 SQL 優(yōu)化建議-語(yǔ)法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)化建議-語(yǔ)法7、防止采用LIKE通配符匹配查詢 通配符匹配查詢特別耗費(fèi)時(shí)間。即使在條件字段上建立了索引，在這種情況下也還是采用順序掃描的方式。 例子優(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：并集，剔除反復(fù)值Union All：并集Intersect：交集Minus：減集32五、 SQL 優(yōu)化建議-函數(shù)3、 極值LEAST：最小值GREATEST：最大值se