面向程序員的數(shù)據(jù)庫訪問性能優(yōu)化法則

./面向程序員的數(shù)據(jù)庫訪問性能優(yōu)化法則特別說明：1、本文只是面對(duì)數(shù)據(jù)庫應(yīng)用開發(fā)的程序員,不適合專業(yè)DBA,DBA在數(shù)據(jù)庫性能優(yōu)化方面需要了解更多的知識(shí)；2、本文許多示例與概念是基于Oracle數(shù)據(jù)庫描述,對(duì)于其它關(guān)系型數(shù)據(jù)庫也可以參考,但許多觀點(diǎn)不適合于KV數(shù)據(jù)庫或內(nèi)存數(shù)據(jù)庫或者是基于SSD技術(shù)的數(shù)據(jù)庫；3、本文未深入數(shù)據(jù)庫優(yōu)化中最核心的執(zhí)行計(jì)劃分析技術(shù)。讀者對(duì)像：開發(fā)人員：如果你是做數(shù)據(jù)庫開發(fā),那本文的內(nèi)容非常適合,因?yàn)楸疚氖菑某绦騿T的角度來談數(shù)據(jù)庫性能優(yōu)化。架構(gòu)師：如果你已經(jīng)是數(shù)據(jù)庫應(yīng)用的架構(gòu)師,那本文的知識(shí)你應(yīng)該清楚90%,否則你可能是一個(gè)喜歡折騰的架構(gòu)師。DBA〔數(shù)據(jù)庫管理員〕：大型數(shù)據(jù)庫優(yōu)化的知識(shí)非常復(fù)雜,本文只是從程序員的角度來談性能優(yōu)化,DBA除了需要了解這些知識(shí)外,還需要深入數(shù)據(jù)庫的內(nèi)部體系架構(gòu)來解決問題。引言在網(wǎng)上有很多文章介紹數(shù)據(jù)庫優(yōu)化知識(shí),但是大部份文章只是對(duì)某個(gè)一個(gè)方面進(jìn)行說明,而對(duì)于我們程序員來說這種介紹并不能很好的掌握優(yōu)化知識(shí),因?yàn)楹芏嘟榻B只是對(duì)一些特定的場(chǎng)景優(yōu)化的,所以反而有時(shí)會(huì)產(chǎn)生誤導(dǎo)或讓程序員感覺不明白其中的奧妙而對(duì)數(shù)據(jù)庫優(yōu)化感覺很神秘。很多程序員總是問如何學(xué)習(xí)數(shù)據(jù)庫優(yōu)化,有沒有好的教材之類的問題。在書店也看到了許多數(shù)據(jù)庫優(yōu)化的專業(yè)書籍,但是感覺更多是面向DBA或者是PL/SQL開發(fā)方面的知識(shí),個(gè)人感覺不太適合普通程序員。而要想做到數(shù)據(jù)庫優(yōu)化的高手,不是花幾周,幾個(gè)月就能達(dá)到的,這并不是因?yàn)閿?shù)據(jù)庫優(yōu)化有多高深,而是因?yàn)橐龊脙?yōu)化一方面需要有非常好的技術(shù)功底,對(duì)操作系統(tǒng)、存儲(chǔ)硬件網(wǎng)絡(luò)、數(shù)據(jù)庫原理等方面有比較扎實(shí)的基礎(chǔ)知識(shí),另一方面是需要花大量時(shí)間對(duì)特定的數(shù)據(jù)庫進(jìn)行實(shí)踐測(cè)試與總結(jié)。作為一個(gè)程序員,我們也許不清楚線上正式的服務(wù)器硬件配置,我們不可能像DBA那樣專業(yè)的對(duì)數(shù)據(jù)庫進(jìn)行各種實(shí)踐測(cè)試與總結(jié),但我們都應(yīng)該非常了解我們SQL的業(yè)務(wù)邏輯,我們清楚SQL中訪問表與字段的數(shù)據(jù)情況,我們其實(shí)只關(guān)心我們的SQL是否能盡快返回結(jié)果。那程序員如何利用已知的知識(shí)進(jìn)行數(shù)據(jù)庫優(yōu)化？如何能快速定位SQL性能問題并找到正確的優(yōu)化方向？面對(duì)這些問題,筆者總結(jié)了一些面向程序員的基本優(yōu)化法則,本文將結(jié)合實(shí)例來坦述數(shù)據(jù)庫開發(fā)的優(yōu)化知識(shí)。一、數(shù)據(jù)庫訪問優(yōu)化法則簡介要正確的優(yōu)化SQL,我們需要快速定位能性的瓶頸點(diǎn),也就是說快速找到我們SQL主要的開銷在哪里？而大多數(shù)情況性能最慢的設(shè)備會(huì)是瓶頸點(diǎn),如下載時(shí)網(wǎng)絡(luò)速度可能會(huì)是瓶頸點(diǎn),本地復(fù)制文件時(shí)硬盤可能會(huì)是瓶頸點(diǎn),為什么這些一般的工作我們能快速確認(rèn)瓶頸點(diǎn)呢,因?yàn)槲覀儗?duì)這些慢速設(shè)備的性能數(shù)據(jù)有一些基本的認(rèn)識(shí),如網(wǎng)絡(luò)帶寬是2Mbps,硬盤是每分鐘7200轉(zhuǎn)等等。因此,為了快速找到SQL的性能瓶頸點(diǎn),我們也需要了解我們計(jì)算機(jī)系統(tǒng)的硬件基本性能指標(biāo),下圖展示的當(dāng)前主流計(jì)算機(jī)性能指標(biāo)數(shù)據(jù)。從圖上可以看到基本上每種設(shè)備都有兩個(gè)指標(biāo)：延時(shí)〔響應(yīng)時(shí)間〕：表示硬件的突發(fā)處理能力；帶寬〔吞吐量〕：代表硬件持續(xù)處理能力。從上圖可以看出,計(jì)算機(jī)系統(tǒng)硬件性能從高到代依次為：CPU——Cache<L1-L2-L3>——內(nèi)存——SSD硬盤——網(wǎng)絡(luò)——硬盤由于SSD硬盤還處于快速發(fā)展階段,所以本文的內(nèi)容不涉與SSD相關(guān)應(yīng)用系統(tǒng)。根據(jù)數(shù)據(jù)庫知識(shí),我們可以列出每種硬件主要的工作內(nèi)容：CPU與內(nèi)存：緩存數(shù)據(jù)訪問、比較、排序、事務(wù)檢測(cè)、SQL解析、函數(shù)或邏輯運(yùn)算；網(wǎng)絡(luò)：結(jié)果數(shù)據(jù)傳輸、SQL請(qǐng)求、遠(yuǎn)程數(shù)據(jù)庫訪問〔dblink〕；硬盤：數(shù)據(jù)訪問、數(shù)據(jù)寫入、日志記錄、大數(shù)據(jù)量排序、大表連接。根據(jù)當(dāng)前計(jì)算機(jī)硬件的基本性能指標(biāo)與其在數(shù)據(jù)庫中主要操作內(nèi)容,可以整理出如下圖所示的性能基本優(yōu)化法則：這個(gè)優(yōu)化法則歸納為5個(gè)層次：1、減少數(shù)據(jù)訪問〔減少磁盤訪問〕2、返回更少數(shù)據(jù)〔減少網(wǎng)絡(luò)傳輸或磁盤訪問〕3、減少交互次數(shù)〔減少網(wǎng)絡(luò)傳輸〕4、減少服務(wù)器CPU開銷〔減少CPU與內(nèi)存開銷〕5、利用更多資源〔增加資源〕由于每一層優(yōu)化法則都是解決其對(duì)應(yīng)硬件的性能問題,所以帶來的性能提升比例也不一樣。傳統(tǒng)數(shù)據(jù)庫系統(tǒng)設(shè)計(jì)是也是盡可能對(duì)低速設(shè)備提供優(yōu)化方法,因此針對(duì)低速設(shè)備問題的可優(yōu)化手段也更多,優(yōu)化成本也更低。我們?nèi)魏我粋€(gè)SQL的性能優(yōu)化都應(yīng)該按這個(gè)規(guī)則由上到下來診斷問題并提出解決方案,而不應(yīng)該首先想到的是增加資源解決問題。以下是每個(gè)優(yōu)化法則層級(jí)對(duì)應(yīng)優(yōu)化效果與成本經(jīng)驗(yàn)參考：優(yōu)化法則性能提升效果優(yōu)化成本減少數(shù)據(jù)訪問1~1000低返回更少數(shù)據(jù)1~100低減少交互次數(shù)1~20低減少服務(wù)器CPU開銷1~5低利用更多資源~10高接下來,我們針對(duì)5種優(yōu)化法則列舉常用的優(yōu)化手段并結(jié)合實(shí)例分析。二、Oracle數(shù)據(jù)庫兩個(gè)基本概念數(shù)據(jù)塊<Block>數(shù)據(jù)塊是數(shù)據(jù)庫中數(shù)據(jù)在磁盤中存儲(chǔ)的最小單位,也是一次IO訪問的最小單位,一個(gè)數(shù)據(jù)塊通?？梢源鎯?chǔ)多條記錄,數(shù)據(jù)塊大小是DBA在創(chuàng)建數(shù)據(jù)庫或表空間時(shí)指定,可指定為2K、4K、8K、16K或32K字節(jié)。下圖是一個(gè)Oracle數(shù)據(jù)庫典型的物理結(jié)構(gòu),一個(gè)數(shù)據(jù)庫可以包括多個(gè)數(shù)據(jù)文件,一個(gè)數(shù)據(jù)文件內(nèi)又包含多個(gè)數(shù)據(jù)塊；ROWIDROWID是每條記錄在數(shù)據(jù)庫中的唯一標(biāo)識(shí),通過ROWID可以直接定位記錄到對(duì)應(yīng)的文件號(hào)與數(shù)據(jù)塊位置。ROWID內(nèi)容包括文件號(hào)、對(duì)像號(hào)、數(shù)據(jù)塊號(hào)、記錄槽號(hào),如下圖所示：三、數(shù)據(jù)庫訪問優(yōu)化法則詳解1、減少數(shù)據(jù)訪問1.1、創(chuàng)建并使用正確的索引數(shù)據(jù)庫索引的原理非常簡單,但在復(fù)雜的表中真正能正確使用索引的人很少,即使是專業(yè)的DBA也不一定能完全做到最優(yōu)。索引會(huì)大大增加表記錄的DML<INSERT,UPDATE,DELETE>開銷,正確的索引可以讓性能提升100,1000倍以上,不合理的索引也可能會(huì)讓性能下降100倍,因此在一個(gè)表中創(chuàng)建什么樣的索引需要平衡各種業(yè)務(wù)需求。索引常見問題：索引有哪些種類？常見的索引有B-TREE索引、位圖索引、全文索引,位圖索引一般用于數(shù)據(jù)倉庫應(yīng)用,全文索引由于使用較少,這里不深入介紹。B-TREE索引包括很多擴(kuò)展類型,如組合索引、反向索引、函數(shù)索引等等,以下是B-TREE索引的簡單介紹：B-TREE索引也稱為平衡樹索引<BalanceTree>,它是一種按字段排好序的樹形目錄結(jié)構(gòu),主要用于提升查詢性能和唯一約束支持。B-TREE索引的內(nèi)容包括根節(jié)點(diǎn)、分支節(jié)點(diǎn)、葉子節(jié)點(diǎn)。葉子節(jié)點(diǎn)內(nèi)容：索引字段內(nèi)容+表記錄ROWID根節(jié)點(diǎn),分支節(jié)點(diǎn)內(nèi)容：當(dāng)一個(gè)數(shù)據(jù)塊中不能放下所有索引字段數(shù)據(jù)時(shí),就會(huì)形成樹形的根節(jié)點(diǎn)或分支節(jié)點(diǎn),根節(jié)點(diǎn)與分支節(jié)點(diǎn)保存了索引樹的順序與各層級(jí)間的引用關(guān)系。一個(gè)普通的BTREE索引結(jié)構(gòu)示意圖如下所示：如果我們把一個(gè)表的內(nèi)容認(rèn)為是一本字典,那索引就相當(dāng)于字典的目錄,如下圖所示：圖中是一個(gè)字典按部首+筆劃數(shù)的目錄,相當(dāng)于給字典建了一個(gè)按部首+筆劃的組合索引。一個(gè)表中可以建多個(gè)索引,就如一本字典可以建多個(gè)目錄一樣〔按拼音、筆劃、部首等等〕。一個(gè)索引也可以由多個(gè)字段組成,稱為組合索引,如上圖就是一個(gè)按部首+筆劃的組合目錄。SQL什么條件會(huì)使用索引？當(dāng)字段上建有索引時(shí),通常以下情況會(huì)使用索引：INDEX_COLUMN=?INDEX_COLUMN>?INDEX_COLUMN>=?INDEX_COLUMN<?INDEX_COLUMN<=?INDEX_COLUMNbetween?and?INDEX_COLUMNin<?,?,...,?>INDEX_COLUMNlike?||'%'〔后導(dǎo)模糊查詢〕T1.INDEX_COLUMN=T2.COLUMN1〔兩個(gè)表通過索引字段關(guān)聯(lián)〕SQL什么條件不會(huì)使用索引？查詢條件不能使用索引原因INDEX_COLUMN<>?INDEX_COLUMNnotin<?,?,...,?>不等于操作不能使用索引function<INDEX_COLUMN>=?INDEX_COLUMN+1=?INDEX_COLUMN||'a'=?經(jīng)過普通運(yùn)算或函數(shù)運(yùn)算后的索引字段不能使用索引INDEX_COLUMNlike'%'||?INDEX_COLUMNlike'%'||?||'%'含前導(dǎo)模糊查詢的Like語法不能使用索引INDEX_COLUMNisnullB-TREE索引里不保存字段為NULL值記錄,因此ISNULL不能使用索引NUMBER_INDEX_COLUMN='12345'CHAR_INDEX_COLUMN=12345Oracle在做數(shù)值比較時(shí)需要將兩邊的數(shù)據(jù)轉(zhuǎn)換成同一種數(shù)據(jù)類型,如果兩邊數(shù)據(jù)類型不同時(shí)會(huì)對(duì)字段值隱式轉(zhuǎn)換,相當(dāng)于加了一層函數(shù)處理,所以不能使用索引。a.INDEX_COLUMN=a.COLUMN_1給索引查詢的值應(yīng)是已知數(shù)據(jù),不能是未知字段值。注：經(jīng)過函數(shù)運(yùn)算字段的字段要使用可以使用函數(shù)索引,這種需求建議與DBA溝通。有時(shí)候我們會(huì)使用多個(gè)字段的組合索引,如果查詢條件中第一個(gè)字段不能使用索引,那整個(gè)查詢也不能使用索引如：我們company表建了一個(gè)id+name的組合索引,以下SQL是不能使用索引的Select*fromcompanywherename=?Oracle9i后引入了一種indexskipscan的索引方式來解決類似的問題,但是通過indexskipscan提高性能的條件比較特殊,使用不好反而性能會(huì)更差。

我們一般在什么字段上建索引？這是一個(gè)非常復(fù)雜的話題,需要對(duì)業(yè)務(wù)與數(shù)據(jù)充分分析后再能得出結(jié)果。主鍵與外鍵通常都要有索引,其它需要建索引的字段應(yīng)滿足以下條件：1、字段出現(xiàn)在查詢條件中,并且查詢條件可以使用索引；2、語句執(zhí)行頻率高,一天會(huì)有幾千次以上；3、通過字段條件可篩選的記錄集很小,那數(shù)據(jù)篩選比例是多少才適合？這個(gè)沒有固定值,需要根據(jù)表數(shù)據(jù)量來評(píng)估,以下是經(jīng)驗(yàn)公式,可用于快速評(píng)估：小表<記錄數(shù)小于10000行的表>：篩選比例<10%；大表：<篩選返回記錄數(shù)><<表總記錄數(shù)*單條記錄長度>/10000/16單條記錄長度≈字段平均內(nèi)容長度之和+字段數(shù)*2以下是一些字段是否需要建B-TREE索引的經(jīng)驗(yàn)分類：

字段類型常見字段名需要建索引的字段主鍵ID,PK外鍵PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID有對(duì)像或身份標(biāo)識(shí)意義字段HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO索引慎用字段,需要進(jìn)行數(shù)據(jù)分布與使用場(chǎng)景詳細(xì)評(píng)估日期GMT_CREATE,GMT_MODIFIED年月YEAR,MONTH狀態(tài)標(biāo)志PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG類型ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE區(qū)域COUNTRY,PROVINCE,CITY操作人員CREATOR,AUDITOR數(shù)值LEVEL,AMOUNT,SCORE長字符ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT不適合建索引的字段描述備注DESCRIPTION,REMARK,MEMO,DETAIL大字段FILE_CONTENT,EMAIL_CONTENT如何知道SQL是否使用了正確的索引？簡單SQL可以根據(jù)索引使用語法規(guī)則判斷,復(fù)雜的SQL不好辦,判斷SQL的響應(yīng)時(shí)間是一種策略,但是這會(huì)受到數(shù)據(jù)量、主機(jī)負(fù)載與緩存等因素的影響,有時(shí)數(shù)據(jù)全在緩存里,可能全表訪問的時(shí)間比索引訪問時(shí)間還少。要準(zhǔn)確知道索引是否正確使用,需要到數(shù)據(jù)庫中查看SQL真實(shí)的執(zhí)行計(jì)劃,這個(gè)話題比較復(fù)雜,詳見SQL執(zhí)行計(jì)劃專題介紹。索引對(duì)DML<INSERT,UPDATE,DELETE>附加的開銷有多少？這個(gè)沒有固定的比例,與每個(gè)表記錄的大小與索引字段大小密切相關(guān),以下是一個(gè)普通表測(cè)試數(shù)據(jù),僅供參考：索引對(duì)于Insert性能降低56%索引對(duì)于Update性能降低47%索引對(duì)于Delete性能降低29%因此對(duì)于寫IO壓力比較大的系統(tǒng),表的索引需要仔細(xì)評(píng)估必要性,另外索引也會(huì)占用一定的存儲(chǔ)空間。1.2、只通過索引訪問數(shù)據(jù)有些時(shí)候,我們只是訪問表中的幾個(gè)字段,并且字段內(nèi)容較少,我們可以為這幾個(gè)字段單獨(dú)建立一個(gè)組合索引,這樣就可以直接只通過訪問索引就能得到數(shù)據(jù),一般索引占用的磁盤空間比表小很多,所以這種方式可以大大減少磁盤IO開銷。如：selectid,namefromcompanywheretype='2';如果這個(gè)SQL經(jīng)常使用,我們可以在type,id,name上創(chuàng)建組合索引createindexmy_comb_indexoncompany<type,id,name>;有了這個(gè)組合索引后,SQL就可以直接通過my_comb_index索引返回?cái)?shù)據(jù),不需要訪問company表。還是拿字典舉例：有一個(gè)需求,需要查詢一本漢語字典中所有漢字的個(gè)數(shù),如果我們的字典沒有目錄索引,那我們只能從字典內(nèi)容里一個(gè)一個(gè)字計(jì)數(shù),最后返回結(jié)果。如果我們有一個(gè)拼音目錄,那就可以只訪問拼音目錄的漢字進(jìn)行計(jì)數(shù)。如果一本字典有1000頁,拼音目錄有20頁,那我們的數(shù)據(jù)訪問成本相當(dāng)于全表訪問的50分之一。切記,性能優(yōu)化是無止境的,當(dāng)性能可以滿足需求時(shí)即可,不要過度優(yōu)化。在實(shí)際數(shù)據(jù)庫中我們不可能把每個(gè)SQL請(qǐng)求的字段都建在索引里,所以這種只通過索引訪問數(shù)據(jù)的方法一般只用于核心應(yīng)用,也就是那種對(duì)核心表訪問量最高且查詢字段數(shù)據(jù)量很少的查詢。1.3、優(yōu)化SQL執(zhí)行計(jì)劃SQL執(zhí)行計(jì)劃是關(guān)系型數(shù)據(jù)庫最核心的技術(shù)之一,它表示SQL執(zhí)行時(shí)的數(shù)據(jù)訪問算法。由于業(yè)務(wù)需求越來越復(fù)雜,表數(shù)據(jù)量也越來越大,程序員越來越懶惰,SQL也需要支持非常復(fù)雜的業(yè)務(wù)邏輯,但SQL的性能還需要提高,因此,優(yōu)秀的關(guān)系型數(shù)據(jù)庫除了需要支持復(fù)雜的SQL語法與更多函數(shù)外,還需要有一套優(yōu)秀的算法庫來提高SQL性能。目前ORACLE有SQL執(zhí)行計(jì)劃的算法約300種,而且一直在增加,所以SQL執(zhí)行計(jì)劃是一個(gè)非常復(fù)雜的課題,一個(gè)普通DBA能掌握50種就很不錯(cuò)了,就算是資深DBA也不可能把每個(gè)執(zhí)行計(jì)劃的算法描述清楚。雖然有這么多種算法,但并不表示我們無法優(yōu)化執(zhí)行計(jì)劃,因?yàn)槲覀兂Ｓ玫腟QL執(zhí)行計(jì)劃算法也就十幾個(gè),如果一個(gè)程序員能把這十幾個(gè)算法搞清楚,那就掌握了80%的SQL執(zhí)行計(jì)劃調(diào)優(yōu)知識(shí)。由于篇幅的原因,SQL執(zhí)行計(jì)劃需要專題介紹,在這里就不多說了。2、返回更少的數(shù)據(jù)2.1、數(shù)據(jù)分頁處理一般數(shù)據(jù)分頁方式有：、客戶端<應(yīng)用程序或?yàn)g覽器>分頁將數(shù)據(jù)從應(yīng)用服務(wù)器全部下載到本地應(yīng)用程序或?yàn)g覽器,在應(yīng)用程序或?yàn)g覽器內(nèi)部通過本地代碼進(jìn)行分頁處理優(yōu)點(diǎn)：編碼簡單,減少客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)交互次數(shù)缺點(diǎn)：首次交互時(shí)間長,占用客戶端內(nèi)存適應(yīng)場(chǎng)景：客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)延時(shí)較大,但要求后續(xù)操作流暢,如手機(jī)GPRS,超遠(yuǎn)程訪問〔跨國〕等等。、應(yīng)用服務(wù)器分頁將數(shù)據(jù)從數(shù)據(jù)庫服務(wù)器全部下載到應(yīng)用服務(wù)器,在應(yīng)用服務(wù)器內(nèi)部再進(jìn)行數(shù)據(jù)篩選。以下是一個(gè)應(yīng)用服務(wù)器端Java程序分頁的示例：Listlist=executeQuery<"select*fromemployeeorderbyid">;Intcount=list.size<>;ListsubList=list.subList<10,20>;優(yōu)點(diǎn)：編碼簡單,只需要一次SQL交互,總數(shù)據(jù)與分頁數(shù)據(jù)差不多時(shí)性能較好。缺點(diǎn)：總數(shù)據(jù)量較多時(shí)性能較差。適應(yīng)場(chǎng)景：數(shù)據(jù)庫系統(tǒng)不支持分頁處理,數(shù)據(jù)量較小并且可控。、數(shù)據(jù)庫SQL分頁采用數(shù)據(jù)庫SQL分頁需要兩次SQL完成一個(gè)SQL計(jì)算總數(shù)量一個(gè)SQL返回分頁后的數(shù)據(jù)優(yōu)點(diǎn)：性能好缺點(diǎn)：編碼復(fù)雜,各種數(shù)據(jù)庫語法不同,需要兩次SQL交互。oracle數(shù)據(jù)庫一般采用rownum來進(jìn)行分頁,常用分頁語法有如下兩種：直接通過rownum分頁：select*from<

selecta.*,rownumrnfrom

<select*fromproductawherecompany_id=?orderbystatus>a

whererownum<=20>wherern>10;數(shù)據(jù)訪問開銷=索引IO+索引全部記錄結(jié)果對(duì)應(yīng)的表數(shù)據(jù)IO采用rowid分頁語法優(yōu)化原理是通過純索引找出分頁記錄的ROWID,再通過ROWID回表返回?cái)?shù)據(jù),要求內(nèi)層查詢和排序字段全在索引里。createindexmyindexonproduct<company_id,status>;selectb.*from<

select*from<

selecta.*,rownumrnfrom

<selectrowidrid,statusfromproductawherecompany_id=?orderbystatus>a

whererownum<=20>

wherern>10>a,productbwherea.rid=b.rowid;數(shù)據(jù)訪問開銷=索引IO+索引分頁結(jié)果對(duì)應(yīng)的表數(shù)據(jù)IO實(shí)例：一個(gè)公司產(chǎn)品有1000條記錄,要分頁取其中20個(gè)產(chǎn)品,假設(shè)訪問公司索引需要50個(gè)IO,2條記錄需要1個(gè)表數(shù)據(jù)IO。那么按第一種ROWNUM分頁寫法,需要550<50+1000/2>個(gè)IO,按第二種ROWID分頁寫法,只需要60個(gè)IO<50+20/2>;2.2、只返回需要的字段通過去除不必要的返回字段可以提高性能,例：調(diào)整前：select*fromproductwherecompany_id=?;調(diào)整后：selectid,namefromproductwherecompany_id=?;優(yōu)點(diǎn)：1、減少數(shù)據(jù)在網(wǎng)絡(luò)上傳輸開銷2、減少服務(wù)器數(shù)據(jù)處理開銷3、減少客戶端內(nèi)存占用4、字段變更時(shí)提前發(fā)現(xiàn)問題,減少程序BUG5、如果訪問的所有字段剛好在一個(gè)索引里面,則可以使用純索引訪問提高性能。缺點(diǎn)：增加編碼工作量由于會(huì)增加一些編碼工作量,所以一般需求通過開發(fā)規(guī)X來要求程序員這么做,否則等項(xiàng)目上線后再整改工作量更大。如果你的查詢表中有大字段或內(nèi)容較多的字段,如備注信息、文件內(nèi)容等等,那在查詢表時(shí)一定要注意這方面的問題,否則可能會(huì)帶來嚴(yán)重的性能問題。如果表經(jīng)常要查詢并且請(qǐng)求大內(nèi)容字段的概率很低,我們可以采用分表處理,將一個(gè)大表分拆成兩個(gè)一對(duì)一的關(guān)系表,將不常用的大內(nèi)容字段放在一X單獨(dú)的表中。如一X存儲(chǔ)上傳文件的表：T_FILE〔ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT〕我們可以分拆成兩X一對(duì)一的關(guān)系表：T_FILE〔ID,FILE_NAME,FILE_SIZE,FILE_TYPE〕T_FILECONTENT〔ID,FILE_CONTENT〕通過這種分拆,可以大大提少T_FILE表的單條記錄與總大小,這樣在查詢T_FILE時(shí)性能會(huì)更好,當(dāng)需要查詢FILE_CONTENT字段內(nèi)容時(shí)再訪問T_FILECONTENT表。3、減少交互次數(shù)3.1、batchDML數(shù)據(jù)庫訪問框架一般都提供了批量提交的接口,jdbc支持batch的提交處理方法,當(dāng)你一次性要往一個(gè)表中插入1000萬條數(shù)據(jù)時(shí),如果采用普通的executeUpdate處理,那么和服務(wù)器交互次數(shù)為1000萬次,按每秒鐘可以向數(shù)據(jù)庫服務(wù)器提交10000次估算,要完成所有工作需要1000秒。如果采用批量提交模式,1000條提交一次,那么和服務(wù)器交互次數(shù)為1萬次,交互次數(shù)大大減少。采用batch操作一般不會(huì)減少很多數(shù)據(jù)庫服務(wù)器的物理IO,但是會(huì)大大減少客戶端與服務(wù)端的交互次數(shù),從而減少了多次發(fā)起的網(wǎng)絡(luò)延時(shí)開銷,同時(shí)也會(huì)降低數(shù)據(jù)庫的CPU開銷。假設(shè)要向一個(gè)普通表插入1000萬數(shù)據(jù),每條記錄大小為1K字節(jié),表上沒有任何索引,客戶端與數(shù)據(jù)庫服務(wù)器網(wǎng)絡(luò)是100Mbps,以下是根據(jù)現(xiàn)在一般計(jì)算機(jī)能力估算的各種batch大小性能對(duì)比值：單位：msNobatchBatch=10Batch=100Batch=1000Batch=10000服務(wù)器事務(wù)處理時(shí)間0.10.1服務(wù)器IO處理時(shí)間0.020.2220200網(wǎng)絡(luò)交互發(fā)起時(shí)間0.10.1網(wǎng)絡(luò)數(shù)據(jù)傳輸時(shí)間0.010.1110100小計(jì)30.2300.2平均每條記錄處理時(shí)間0.230.050.0320.03020.03002從上可以看出,Insert操作加大Batch可以對(duì)性能提高近8倍性能,一般根據(jù)主鍵的Update或Delete操作也可能提高2-3倍性能,但不如Insert明顯,因?yàn)閁pdate與Delete操作可能有比較大的開銷在物理IO訪問。以上僅是理論計(jì)算值,實(shí)際情況需要根據(jù)具體環(huán)境測(cè)量。3.2、InList很多時(shí)候我們需要按一些ID查詢數(shù)據(jù)庫記錄,我們可以采用一個(gè)ID一個(gè)請(qǐng)求發(fā)給數(shù)據(jù)庫,如下所示：for:varinids[]dobegin

select*frommytablewhereid=:var;end;我們也可以做一個(gè)小的優(yōu)化,如下所示,用IDINLIST的這種方式寫SQL：select*frommytablewhereidin<:id1,id2,...,idn>;通過這樣處理可以大大減少SQL請(qǐng)求的數(shù)量,從而提高性能。那如果有10000個(gè)ID,那是不是全部放在一條SQL里處理呢？答案肯定是否定的。首先大部份數(shù)據(jù)庫都會(huì)有SQL長度和IN里個(gè)數(shù)的限制,如ORACLE的IN里就不允許超過1000個(gè)值。另外當(dāng)前數(shù)據(jù)庫一般都是采用基于成本的優(yōu)化規(guī)則,當(dāng)IN數(shù)量達(dá)到一定值時(shí)有可能改變SQL執(zhí)行計(jì)劃,從索引訪問變成全表訪問,這將使性能急劇變化。隨著SQL中IN的里面的值個(gè)數(shù)增加,SQL的執(zhí)行計(jì)劃會(huì)更復(fù)雜,占用的內(nèi)存將會(huì)變大,這將會(huì)增加服務(wù)器CPU與內(nèi)存成本。評(píng)估在IN里面一次放多少個(gè)值還需要考慮應(yīng)用服務(wù)器本地內(nèi)存的開銷,有并發(fā)訪問時(shí)要計(jì)算本地?cái)?shù)據(jù)使用周期內(nèi)的并發(fā)上限,否則可能會(huì)導(dǎo)致內(nèi)存溢出。綜合考慮,一般IN里面的值個(gè)數(shù)超過20個(gè)以后性能基本沒什么太大變化,也特別說明不要超過100,超過后可能會(huì)引起執(zhí)行計(jì)劃的不穩(wěn)定性與增加數(shù)據(jù)庫CPU與內(nèi)存成本,這個(gè)需要專業(yè)DBA評(píng)估。3.3、設(shè)置FetchSize當(dāng)我們采用select從數(shù)據(jù)庫查詢數(shù)據(jù)時(shí),數(shù)據(jù)默認(rèn)并不是一條一條返回給客戶端的,也不是一次全部返回客戶端的,而是根據(jù)客戶端fetch_size參數(shù)處理,每次只返回fetch_size條記錄,當(dāng)客戶端游標(biāo)遍歷到尾部時(shí)再從服務(wù)端取數(shù)據(jù),直到最后全部傳送完成。所以如果我們要從服務(wù)端一次取大量數(shù)據(jù)時(shí),可以加大fetch_size,這樣可以減少結(jié)果數(shù)據(jù)傳輸?shù)慕换ゴ螖?shù)與服務(wù)器數(shù)據(jù)準(zhǔn)備時(shí)間,提高性能。以下是jdbc測(cè)試的代碼,采用本地?cái)?shù)據(jù)庫,表緩存在數(shù)據(jù)庫CACHE中,因此沒有網(wǎng)絡(luò)連接與磁盤IO開銷,客戶端只遍歷游標(biāo),不做任何處理,這樣更能體現(xiàn)fetch參數(shù)的影響：Stringvsql="select*fromt_employee";PreparedStatementpstmt=conn.prepareStatement<vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY>;pstmt.setFetchSize<1000>;ResultSetrs=pstmt.executeQuery<vsql>;intcnt=rs.getMetaData<>.getColumnCount<>;Objecto;while<rs.next<>>{

for<inti=1;i<=cnt;i++>{

o=rs.getObject<i>;

}}測(cè)試示例中的employee表有100000條記錄,每條記錄平均長度135字節(jié)以下是測(cè)試結(jié)果,對(duì)每種fetchsize測(cè)試5次再取平均值：fetchsize

elapse_time〔s〕120.516211.3446.89484.65163.584322.865642.6561282.442562.7655123.07510242.86220482.72240962.68181922.715Oraclejdbcfetchsize默認(rèn)值為10,由上測(cè)試可以看出fetchsize對(duì)性能影響還是比較大的,但是當(dāng)fetchsize大于100時(shí)就基本上沒有影響了。fetchsize并不會(huì)存在一個(gè)最優(yōu)的固定值,因?yàn)檎w性能與記錄集大小與硬件平臺(tái)有關(guān)。根據(jù)測(cè)試結(jié)果建議當(dāng)一次性要取大量數(shù)據(jù)時(shí)這個(gè)值設(shè)置為100左右,不要小于40。注意,fetchsize不能設(shè)置太大,如果一次取出的數(shù)據(jù)大于JVM的內(nèi)存會(huì)導(dǎo)致內(nèi)存溢出,所以建議不要超過1000,太大了也沒什么性能提高,反而可能會(huì)增加內(nèi)存溢出的危險(xiǎn)。注：圖中fetchsize在128以后會(huì)有一些小的波動(dòng),這并不是測(cè)試誤差,而是由于resultset填充到具體對(duì)像時(shí)間不同的原因,由于resultset已經(jīng)到本地內(nèi)存里了,所以估計(jì)是由于CPU的L1,L2Cache命中率變化造成,由于變化不大,所以筆者也未深入分析原因。iBatis的SqlMapping配置文件可以對(duì)每個(gè)SQL語句指定fetchsize大小,如下所示：<selectid="getAllProduct"resultMap="HashMap"fetchSize="1000">select*fromemployee</select>3.4、使用存儲(chǔ)過程大型數(shù)據(jù)庫一般都支持存儲(chǔ)過程,合理的利用存儲(chǔ)過程也可以提高系統(tǒng)性能。如你有一個(gè)業(yè)務(wù)需要將A表的數(shù)據(jù)做一些加工然后更新到B表中,但是又不可能一條SQL完成,這時(shí)你需要如下3步操作：a：將A表數(shù)據(jù)全部取出到客戶端；b：計(jì)算出要更新的數(shù)據(jù)；c：將計(jì)算結(jié)果更新到B表。如果采用存儲(chǔ)過程你可以將整個(gè)業(yè)務(wù)邏輯封裝在存儲(chǔ)過程里,然后在客戶端直接調(diào)用存儲(chǔ)過程處理,這樣可以減少網(wǎng)絡(luò)交互的成本。當(dāng)然,存儲(chǔ)過程也并不是十全十美,存儲(chǔ)過程有以下缺點(diǎn)：a、不可移植性,每種數(shù)據(jù)庫的內(nèi)部編程語法都不太相同,當(dāng)你的系統(tǒng)需要兼容多種數(shù)據(jù)庫時(shí)最好不要用存儲(chǔ)過程。b、學(xué)習(xí)成本高,DBA一般都擅長寫存儲(chǔ)過程,但并不是每個(gè)程序員都能寫好存儲(chǔ)過程,除非你的團(tuán)隊(duì)有較多的開發(fā)人員熟悉寫存儲(chǔ)過程,否則后期系統(tǒng)維護(hù)會(huì)產(chǎn)生問題。c、業(yè)務(wù)邏輯多處存在,采用存儲(chǔ)過程后也就意味著你的系統(tǒng)有一些業(yè)務(wù)邏輯不是在應(yīng)用程序里處理,這種架構(gòu)會(huì)增加一些系統(tǒng)維護(hù)和調(diào)試成本。d、存儲(chǔ)過程和常用應(yīng)用程序語言不一樣,它支持的函數(shù)與語法有可能不能滿足需求,有些邏輯就只能通過應(yīng)用程序處理。e、如果存儲(chǔ)過程中有復(fù)雜運(yùn)算的話,會(huì)增加一些數(shù)據(jù)庫服務(wù)端的處理成本,對(duì)于集中式數(shù)據(jù)庫可能會(huì)導(dǎo)致系統(tǒng)可擴(kuò)展性問題。f、為了提高性能,數(shù)據(jù)庫會(huì)把存儲(chǔ)過程代碼編譯成中間運(yùn)行代碼<類似于java的class文件>,所以更像靜態(tài)語言。當(dāng)存儲(chǔ)過程引用的對(duì)像<表、視圖等等>結(jié)構(gòu)改變后,存儲(chǔ)過程需要重新編譯才能生效,在24*7高并發(fā)應(yīng)用場(chǎng)景,一般都是在線變更結(jié)構(gòu)的,所以在變更的瞬間要同時(shí)編譯存儲(chǔ)過程,這可能會(huì)導(dǎo)致數(shù)據(jù)庫瞬間壓力上升引起故障<Oracle數(shù)據(jù)庫就存在這樣的問題>。個(gè)人觀點(diǎn)：普通業(yè)務(wù)邏輯盡量不要使用存儲(chǔ)過程,定時(shí)性的ETL任務(wù)或報(bào)表統(tǒng)計(jì)函數(shù)可以根據(jù)團(tuán)隊(duì)資源情況采用存儲(chǔ)過程處理。3.5、優(yōu)化業(yè)務(wù)邏輯要通過優(yōu)化業(yè)務(wù)邏輯來提高性能是比較困難的,這需要程序員對(duì)所訪問的數(shù)據(jù)與業(yè)務(wù)流程非常清楚。舉一個(gè)案例：某移動(dòng)公司推出優(yōu)惠套參,活動(dòng)對(duì)像為VIP會(huì)員并且20xx1,2,3月平均話費(fèi)20元以上的客戶。那我們的檢測(cè)邏輯為：ifavg_money>20andvip_flag=truethenbegin執(zhí)行套參<>;end;如果我們修改業(yè)務(wù)邏輯為:ifavg_money>20thenbegin

ifvip_flag=truethen

begin執(zhí)行套參<>;

end;end;通過這樣可以減少一些判斷vip_flag的開銷,平均話費(fèi)20元以下的用戶就不需要再檢測(cè)是否VIP了。如果程序員分析業(yè)務(wù),VIP會(huì)員比例為1%,平均話費(fèi)20元以上的用戶比例為90%,那我們改成如下：ifvip_flag=truethenbegin

ifavg_money>20then

begin執(zhí)行套參<>;

end;end;這樣就只有1%的VIP會(huì)員才會(huì)做檢測(cè)平均話費(fèi),最終大大減少了SQL的交互次數(shù)。以上只是一個(gè)簡單的示例,實(shí)際的業(yè)務(wù)總是比這復(fù)雜得多,所以一般只是高級(jí)程序員更容易做出優(yōu)化的邏輯,但是我們需要有這樣一種成本優(yōu)化的意識(shí)。3.6、使用ResultSet游標(biāo)處理記錄現(xiàn)在大部分Java框架都是通過jdbc從數(shù)據(jù)庫取出數(shù)據(jù),然后裝載到一個(gè)list里再處理,list里可能是業(yè)務(wù)Object,也可能是hashmap。由于JVM內(nèi)存一般都小于4G,所以不可能一次通過sql把大量數(shù)據(jù)裝載到list里。為了完成功能,很多程序員喜歡采用分頁的方法處理,如一次從數(shù)據(jù)庫取1000條記錄,通過多次循環(huán)搞定,保證不會(huì)引起JVMOutofmemory問題。以下是實(shí)現(xiàn)此功能的代碼示例,t_employee表有10萬條記錄,設(shè)置分頁大小為1000：d1=Calendar.getInstance<>.getTime<>;vsql="selectcount<*>cntfromt_employee";pstmt=conn.prepareStatement<vsql>;ResultSetrs=pstmt.executeQuery<>;Integercnt=0;while<rs.next<>>{

cnt=rs.getInt<"cnt">;}Integerlastid=0;Integerpagesize=1000;System.out.println<"cnt:"+cnt>;Stringvsql="selectcount<*>cntfromt_employee";PreparedStatementpstmt=conn.prepareStatement<vsql>;ResultSetrs=pstmt.executeQuery<>;Integercnt=0;while<rs.next<>>{

cnt=rs.getInt<"cnt">;}Integerlastid=0;Integerpagesize=1000;System.out.println<"cnt:"+cnt>;for<inti=0;i<=cnt/pagesize;i++>{

vsql="select*from<select*fromt_employeewhereid>?orderbyid>whererownum<=?";

pstmt=conn.prepareStatement<vsql>;

pstmt.setFetchSize<1000>;

pstmt.setInt<1,lastid>;

pstmt.setInt<2,pagesize>;

rs=pstmt.executeQuery<>;

intcol_cnt=rs.getMetaData<>.getColumnCount<>;

Objecto;

while<rs.next<>>{

for<intj=1;j<=col_cnt;j++>{

o=rs.getObject<j>;

}

lastid=rs.getInt<"id">;

}

rs.close<>;

pstmt.close<>;}以上代碼實(shí)際執(zhí)行時(shí)間為6.516秒很多持久層框架為了盡量讓程序員使用方便,封裝了jdbc通過statement執(zhí)行數(shù)據(jù)返回到resultset的細(xì)節(jié),導(dǎo)致程序員會(huì)想采用分頁的方式處理問題。實(shí)際上如果我們采用jdbc原始的resultset游標(biāo)處理記錄,在resultset循環(huán)讀取的過程中處理記錄,這樣就可以一次從數(shù)據(jù)庫取出所有記錄。顯著提高性能。這里需要注意的是,采用resultset游標(biāo)處理記錄時(shí),應(yīng)該將游標(biāo)的打開方式設(shè)置為FORWARD_READONLY模式<ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY>,否則會(huì)把結(jié)果緩存在JVM里,造成JVMOutofmemory問題。代碼示例：Stringvsql="select*fromt_employee";PreparedStatementpstmt=conn.prepareStatement<vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY>;pstmt.setFetchSize<100>;ResultSetrs=pstmt.executeQuery<vsql>;intcol_cnt=rs.getMetaData<>.getColumnCount<>;Objecto;while<rs.next<>>{

for<intj=1;j<=col_cnt;j++>{

o=rs.getObject<j>;

}}調(diào)整后的代碼實(shí)際執(zhí)行時(shí)間為3.156秒從測(cè)試結(jié)果可以看出性能提高了1倍多,如果采用分頁模式數(shù)據(jù)庫每次還需發(fā)生磁盤IO的話那性能可以提高更多。iBatis等持久層框架考慮到會(huì)有這種需求,所以也有相應(yīng)的解決方案,在iBatis里我們不能采用queryForList的方法,而應(yīng)用該采用queryWithRowHandler加回調(diào)事件的方式處理,如下所示：MyRowHandlermyrh=newMyRowHandler<>;sqlmap.queryWithRowHandler<"getAllEmployee",myrh>;classMyRowHandlerimplementsRowHandler{publicvoidhandleRow<Objecto>{//todosomething

}}iBatis的queryWithRowHandler很好的封裝了resultset遍歷的事件處理,效果與性能與resultset遍歷一樣,也不會(huì)產(chǎn)生JVM內(nèi)存溢出。4、減少數(shù)據(jù)庫服務(wù)器CPU運(yùn)算4.1、使用綁定變量綁定變量是指SQL中對(duì)變化的值采用變量參數(shù)的形式提交,而不是在SQL中直接拼寫對(duì)應(yīng)的值。非綁定變量寫法：Select*fromemployeewhereid=1234567綁定變量寫法：Select*fromemployeewhereid=?Preparestatement.setInt<1,1234567>Java中Preparestatement就是為處理綁定變量提供的對(duì)像,綁定變量有以下優(yōu)點(diǎn)：1、防止SQL注入2、提高SQL可讀性3、提高SQL解析性能,不使用綁定變更我們一般稱為硬解析,使用綁定變量我們稱為軟解析。第1和第2點(diǎn)很好理解,做編碼的人應(yīng)該都清楚,這里不詳細(xì)說明。關(guān)于第3點(diǎn),到底能提高多少性能呢,下面舉一個(gè)例子說明：假設(shè)有這個(gè)這樣的一個(gè)數(shù)據(jù)庫主機(jī)：2個(gè)4核CPU

100塊磁盤,每個(gè)磁盤支持IOPS為160業(yè)務(wù)應(yīng)用的SQL如下：select*fromtablewherepk=?這個(gè)SQL平均4個(gè)IO〔3個(gè)索引IO+1個(gè)數(shù)據(jù)IO〕IO緩存命中率75%〔索引全在內(nèi)存中,數(shù)據(jù)需要訪問磁盤〕SQL硬解析CPU消耗：1ms

〔常用經(jīng)驗(yàn)值〕SQL軟解析CPU消耗：0.02ms〔常用經(jīng)驗(yàn)值〕假設(shè)CPU每核性能是線性增長,訪問內(nèi)存Cache中的IO時(shí)間忽略,要求計(jì)算系統(tǒng)對(duì)如上應(yīng)用采用硬解析與采用軟解析支持的每秒最大并發(fā)數(shù)：是否使用綁定變量CPU支持最大并發(fā)數(shù)磁盤IO支持最大并發(fā)數(shù)不使用2*4*1000=8000100*160=16000使用2*4*1000/0.02=400000100*160=16000從以上計(jì)算可以看出,不使用綁定變量的系統(tǒng)當(dāng)并發(fā)達(dá)到8000時(shí)會(huì)在CPU上產(chǎn)生瓶頸,當(dāng)使用綁定變量的系統(tǒng)當(dāng)并行達(dá)到16000時(shí)會(huì)在磁盤IO上產(chǎn)生瓶頸。所以如果你的系統(tǒng)CPU有瓶頸時(shí)請(qǐng)先檢查是否存在大量的硬解析操作。使用綁定變量為何會(huì)提高SQL解析性能,這個(gè)需要從數(shù)據(jù)庫SQL執(zhí)行原理說明,一條SQL在Oracle數(shù)據(jù)庫中的執(zhí)行過程如下圖所示：當(dāng)一條SQL發(fā)送給數(shù)據(jù)庫服務(wù)器后,系統(tǒng)首先會(huì)將SQL字符串進(jìn)行hash運(yùn)算,得到hash值后再從服務(wù)器內(nèi)存里的SQL緩存區(qū)中進(jìn)行檢索,如果有相同的SQL字符,并且確認(rèn)是同一邏輯的SQL語句,則從共享池緩存中取出SQL對(duì)應(yīng)的執(zhí)行計(jì)劃,根據(jù)執(zhí)行計(jì)劃讀取數(shù)據(jù)并返回結(jié)果給客戶端。如果在共享池中未發(fā)現(xiàn)相同的SQL則根據(jù)SQL邏輯生成一條新的執(zhí)行計(jì)劃并保存在SQL緩存區(qū)中,然后根據(jù)執(zhí)行計(jì)劃讀取數(shù)據(jù)并返回結(jié)果給客戶端。為了更快的檢索SQL是否在緩存區(qū)中,首先進(jìn)行的是SQL字符串hash值對(duì)比,如果未找到則認(rèn)為沒有緩存,如果存在再進(jìn)行下一步的準(zhǔn)確對(duì)比,所以要命中SQL緩存區(qū)應(yīng)保證SQL字符是完全一致,中間有大小寫或空格都會(huì)認(rèn)為是不同的SQL。如果我們不采用綁定變量,采用字符串拼接的模式生成SQL,那么每條SQL都會(huì)產(chǎn)生執(zhí)行計(jì)劃,這樣會(huì)導(dǎo)致共享池耗盡,緩存命中率也很低。一些不使用綁定變量的場(chǎng)景：a、數(shù)據(jù)倉庫應(yīng)用,這種應(yīng)用一般并發(fā)不高,但是每個(gè)SQL執(zhí)行時(shí)間很長,SQL解析的時(shí)間相比SQL執(zhí)行時(shí)間比較小,綁定變量對(duì)性能提高不明顯。數(shù)據(jù)倉庫一般都是內(nèi)部分析應(yīng)用,所以也不太會(huì)發(fā)生SQL注入的安全問題。b、數(shù)據(jù)分布不均勻的特殊邏輯,如產(chǎn)品表,記錄有1億,有一產(chǎn)品狀態(tài)字段,上面建有索引,有審核中,審核通過,審核未通過3種狀態(tài),其中審核通過9500萬,審核中1萬,審核不通過499萬。要做這樣一個(gè)查詢：selectcount<*>fromproductwherestatus=?采用綁定變量的話,那么只會(huì)有一個(gè)執(zhí)行計(jì)劃,如果走索引訪問,那么對(duì)于審核中查詢很快,對(duì)審核通過和審核不通過會(huì)很慢；如果不走索引,那么對(duì)于審核中與審核通過和審核不通過時(shí)間基本一樣；對(duì)于這種情況應(yīng)該不使用綁定變量,而直接采用字符拼接的方式生成SQL,這樣可以為每個(gè)SQL生成不同的執(zhí)行計(jì)劃,如下所示。selectcount<*>fromproductwherestatus='approved';//不使用索引selectcount<*>fromproductwherestatus='tbd';//不使用索引selectcount<*>fromproductwherestatus='auditing';//使用索引4.2、合理使用排序Oracle的排序算法一直在優(yōu)化,但是總體時(shí)間復(fù)雜度約等于nLog<n>。普通OLTP系統(tǒng)排序操作一般都是在內(nèi)存里進(jìn)行的,對(duì)于數(shù)據(jù)庫來說是一種CPU的消耗,曾在PC機(jī)做過測(cè)試,單核普通CPU在1秒鐘可以完成100萬條記錄的全內(nèi)存排序操作,所以說由于現(xiàn)在CPU的性能增強(qiáng),對(duì)于普通的幾十條或上百條記錄排序?qū)ο到y(tǒng)的影響也不會(huì)很大。但是當(dāng)你的記錄集增加到上萬條以上時(shí),你需要注意是否一定要這么做了,大記錄集排序不僅增加了CPU開銷,而且可能會(huì)由于內(nèi)存不足發(fā)生硬盤排序的現(xiàn)象,當(dāng)發(fā)生硬盤排序時(shí)性能會(huì)急劇下降,這種需求需要與DBA溝通再?zèng)Q定,取決于你的需求和數(shù)據(jù),所以只有你自己最清楚,而不要被別人說排序很慢就嚇倒。以下列出了可能會(huì)發(fā)生排序操作的SQL語法：OrderbyGroupbyDistinctExists子查詢NotExists子查詢In子查詢NotIn子查詢Union〔并集〕,UnionAll也是一種并集操作,但是不會(huì)發(fā)生排序,如果你確認(rèn)兩個(gè)數(shù)據(jù)集不需要執(zhí)行去除重復(fù)數(shù)據(jù)操作,那請(qǐng)使用UnionAll代替Union。Minus〔差集〕Intersect〔交集〕CreateIndexMergeJoin,這是一種兩個(gè)表連接的內(nèi)部算法,執(zhí)行時(shí)會(huì)把兩個(gè)表先排序好再連接,應(yīng)用于兩個(gè)大表連接的操作。如果你的兩個(gè)表連接的條件都是等值運(yùn)算,那可以采用HashJoin來提高性能,因?yàn)镠ashJoin使用Hash運(yùn)算來代替排序的操