轉(zhuǎn) # https://www.cnblogs.com/easypass/archive/2010/12/
08/1900127.html
1.數(shù)據(jù)庫訪問優(yōu)化法則
要正確的優(yōu)化SQL屈藐，我們需要快速定位能性的瓶頸點(diǎn)食磕，也就是說快速找到我們SQL主要的開銷在哪里鲁捏？而大多數(shù)情況性能最慢的設(shè)備會是瓶頸點(diǎn)属拾，如下載時網(wǎng)絡(luò)速度可能會是瓶頸點(diǎn)舞萄，本地復(fù)制文件時硬盤可能會是瓶頸點(diǎn)见转，為什么這些一般的工作我們能快速確認(rèn)瓶頸點(diǎn)呢，因?yàn)槲覀儗@些慢速設(shè)備的性能數(shù)據(jù)有一些基本的認(rèn)識旗笔，如網(wǎng)絡(luò)帶寬是2Mbps彪置，硬盤是每分鐘7200轉(zhuǎn)等等。因此蝇恶，為了快速找到SQL的性能瓶頸點(diǎn)拳魁，我們也需要了解我們計算機(jī)系統(tǒng)的硬件基本性能指標(biāo)，下圖展示的當(dāng)前主流計算機(jī)性能指標(biāo)數(shù)據(jù)艘包。
**[圖片上傳失敗...(image-af0c31-1520414439366)]
**
從圖上可以看到基本上每種設(shè)備都有兩個指標(biāo)：
延時（響應(yīng)時間）：表示硬件的突發(fā)處理能力的猛；
帶寬（吞吐量）：代表硬件持續(xù)處理能力耀盗。
從上圖可以看出想虎，計算機(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ù)庫知識舌厨，我們可以列出每種硬件主要的工作內(nèi)容：
CPU及內(nèi)存：緩存數(shù)據(jù)訪問、比較忿薇、排序裙椭、事務(wù)檢測、SQL解析署浩、函數(shù)或邏輯運(yùn)算揉燃；
網(wǎng)絡(luò)：結(jié)果數(shù)據(jù)傳輸、SQL請求筋栋、遠(yuǎn)程數(shù)據(jù)庫訪問（dblink）炊汤；
硬盤：數(shù)據(jù)訪問、數(shù)據(jù)寫入、日志記錄抢腐、大數(shù)據(jù)量排序姑曙、大表連接。
根據(jù)當(dāng)前計算機(jī)硬件的基本性能指標(biāo)及其在數(shù)據(jù)庫中主要操作內(nèi)容迈倍，可以整理出如下圖所示的性能基本優(yōu)化法則：
**[圖片上傳失敗...(image-af7745-1520414439366)]
**
這個優(yōu)化法則歸納為5個層次：
1伤靠、 減少數(shù)據(jù)訪問（減少磁盤訪問）
2、 返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤訪問）
3啼染、 減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）
4宴合、 減少服務(wù)器CPU開銷（減少CPU及內(nèi)存開銷）
5、 利用更多資源（增加資源）
由于每一層優(yōu)化法則都是解決其對應(yīng)硬件的性能問題提完，所以帶來的性能提升比例也不一樣形纺。傳統(tǒng)數(shù)據(jù)庫系統(tǒng)設(shè)計是也是盡可能對低速設(shè)備提供優(yōu)化方法，因此針對低速設(shè)備問題的可優(yōu)化手段也更多徒欣，優(yōu)化成本也更低逐样。我們?nèi)魏我粋€SQL的性能優(yōu)化都應(yīng)該按這個規(guī)則由上到下來診斷問題并提出解決方案，而不應(yīng)該首先想到的是增加資源解決問題打肝。
以下是每個優(yōu)化法則層級對應(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
|
高
|
接下來脂新，我們針對5種優(yōu)化法則列舉常用的優(yōu)化手段并結(jié)合實(shí)例分析。
二粗梭、Oracle數(shù)據(jù)庫兩個基本概念
數(shù)據(jù)塊(Block)
數(shù)據(jù)塊是數(shù)據(jù)庫中數(shù)據(jù)在磁盤中存儲的最小單位争便，也是一次IO訪問的最小單位，一個數(shù)據(jù)塊通扯弦剑可以存儲多條記錄滞乙，數(shù)據(jù)塊大小是DBA在創(chuàng)建數(shù)據(jù)庫或表空間時指定，可指定為2K鉴嗤、4K斩启、8K、16K或32K字節(jié)醉锅。下圖是一個Oracle數(shù)據(jù)庫典型的物理結(jié)構(gòu)兔簇，一個數(shù)據(jù)庫可以包括多個數(shù)據(jù)文件，一個數(shù)據(jù)文件內(nèi)又包含多個數(shù)據(jù)塊硬耍；
[圖片上傳失敗...(image-e91d1c-1520414439370)]
ROWID
ROWID是每條記錄在數(shù)據(jù)庫中的唯一標(biāo)識垄琐，通過ROWID可以直接定位記錄到對應(yīng)的文件號及數(shù)據(jù)塊位置。ROWID內(nèi)容包括文件號经柴、對像號狸窘、數(shù)據(jù)塊號、記錄槽號坯认，如下圖所示：
[圖片上傳失敗...(image-84abd7-1520414439364)]
三翻擒、數(shù)據(jù)庫訪問優(yōu)化法則詳解
1介杆、減少數(shù)據(jù)訪問
1.1、創(chuàng)建并使用正確的索引
數(shù)據(jù)庫索引的原理非常簡單韭寸，但在復(fù)雜的表中真正能正確使用索引的人很少春哨，即使是專業(yè)的DBA也不一定能完全做到最優(yōu)。
索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷恩伺，正確的索引可以讓性能提升100赴背，1000倍以上，不合理的索引也可能會讓性能下降100倍晶渠，因此在一個表中創(chuàng)建什么樣的索引需要平衡各種業(yè)務(wù)需求凰荚。
索引常見問題：
索引有哪些種類？
常見的索引有B-TREE索引褒脯、位圖索引便瑟、全文索引，位圖索引一般用于數(shù)據(jù)倉庫應(yīng)用番川，全文索引由于使用較少到涂，這里不深入介紹。B-TREE索引包括很多擴(kuò)展類型颁督，如組合索引践啄、反向索引、函數(shù)索引等等沉御，以下是B-TREE索引的簡單介紹：
B-TREE索引也稱為平衡樹索引(Balance Tree)屿讽，它是一種按字段排好序的樹形目錄結(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)一個數(shù)據(jù)塊中不能放下所有索引字段數(shù)據(jù)時，就會形成樹形的根節(jié)點(diǎn)或分支節(jié)點(diǎn)效斑，根節(jié)點(diǎn)與分支節(jié)點(diǎn)保存了索引樹的順序及各層級間的引用關(guān)系非春。
一個普通的BTREE索引結(jié)構(gòu)示意圖如下所示：
[圖片上傳失敗...(image-72f233-1520414439368)]
如果我們把一個表的內(nèi)容認(rèn)為是一本字典柱徙，那索引就相當(dāng)于字典的目錄缓屠，如下圖所示：
[圖片上傳失敗...(image-fba549-1520414439368)]
[圖片上傳失敗...(image-a4e809-1520414439368)]
圖中是一個字典按部首+筆劃數(shù)的目錄，相當(dāng)于給字典建了一個按部首+筆劃的組合索引护侮。
一個表中可以建多個索引敌完，就如一本字典可以建多個目錄一樣（按拼音、筆劃羊初、部首等等）滨溉。
一個索引也可以由多個字段組成什湘，稱為組合索引，如上圖就是一個按部首+筆劃的組合目錄晦攒。
SQL什么條件會使用索引闽撤？
當(dāng)字段上建有索引時，通常以下情況會使用索引：
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN <= ?
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||'%'（后導(dǎo)模糊查詢）
T1. INDEX_COLUMN=T2. COLUMN1（兩個表通過索引字段關(guān)聯(lián)）
SQL什么條件不會使用索引脯颜？
|
查詢條件
|
不能使用索引原因
|
|
INDEX_COLUMN <> ?
INDEX_COLUMN not in (?,?,...,?)
|
不等于操作不能使用索引
|
|
function(INDEX_COLUMN) = ?
INDEX_COLUMN + 1 = ?
INDEX_COLUMN || 'a' = ?
|
經(jīng)過普通運(yùn)算或函數(shù)運(yùn)算后的索引字段不能使用索引
|
|
INDEX_COLUMN like '%'||?
INDEX_COLUMN like '%'||?||'%'
|
含前導(dǎo)模糊查詢的Like語法不能使用索引
|
|
INDEX_COLUMN is null
|
B-TREE索引里不保存字段為NULL值記錄哟旗，因此IS NULL不能使用索引
|
|
NUMBER_INDEX_COLUMN='12345'
CHAR_INDEX_COLUMN=12345
|
Oracle在做數(shù)值比較時需要將兩邊的數(shù)據(jù)轉(zhuǎn)換成同一種數(shù)據(jù)類型，如果兩邊數(shù)據(jù)類型不同時會對字段值隱式轉(zhuǎn)換栋操，相當(dāng)于加了一層函數(shù)處理闸餐，所以不能使用索引。
|
|
a.INDEX_COLUMN=a.COLUMN_1
|
給索引查詢的值應(yīng)是已知數(shù)據(jù)矾芙，不能是未知字段值舍沙。
|
|
注：
經(jīng)過函數(shù)運(yùn)算字段的字段要使用可以使用函數(shù)索引，這種需求建議與DBA溝通剔宪。
有時候我們會使用多個字段的組合索引拂铡，如果查詢條件中第一個字段不能使用索引，那整個查詢也不能使用索引
如：我們company表建了一個id+name的組合索引葱绒，以下SQL是不能使用索引的
Select * from company where name=?
Oracle9i后引入了一種index skip scan的索引方式來解決類似的問題和媳，但是通過index skip scan提高性能的條件比較特殊，使用不好反而性能會更差哈街。
|
我們一般在什么字段上建索引留瞳？
這是一個非常復(fù)雜的話題，需要對業(yè)務(wù)及數(shù)據(jù)充分分析后再能得出結(jié)果骚秦。主鍵及外鍵通常都要有索引她倘，其它需要建索引的字段應(yīng)滿足以下條件：
1、字段出現(xiàn)在查詢條件中作箍，并且查詢條件可以使用索引硬梁；
2、語句執(zhí)行頻率高胞得，一天會有幾千次以上荧止；
3、通過字段條件可篩選的記錄集很小阶剑，那數(shù)據(jù)篩選比例是多少才適合跃巡？
這個沒有固定值，需要根據(jù)表數(shù)據(jù)量來評估牧愁，以下是經(jīng)驗(yàn)公式素邪，可用于快速評估：
小表(記錄數(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
|
|
有對像或身份標(biāo)識意義字段
|
HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO
|
|
索引慎用字段,需要進(jìn)行數(shù)據(jù)分布及使用場景詳細(xì)評估
|
日期
|
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ù)據(jù)量沽甥、主機(jī)負(fù)載及緩存等因素的影響声邦，有時數(shù)據(jù)全在緩存里，可能全表訪問的時間比索引訪問時間還少摆舟。要準(zhǔn)確知道索引是否正確使用翔忽，需要到數(shù)據(jù)庫中查看SQL真實(shí)的執(zhí)行計劃，這個話題比較復(fù)雜盏檐，詳見SQL執(zhí)行計劃專題介紹歇式。
索引對DML(INSERT,UPDATE,DELETE)附加的開銷有多少？
這個沒有固定的比例胡野，與每個表記錄的大小及索引字段大小密切相關(guān)材失，以下是一個普通表測試數(shù)據(jù)氛琢，僅供參考：
索引對于Insert性能降低56%
索引對于Update性能降低47%
索引對于Delete性能降低29%
因此對于寫IO壓力比較大的系統(tǒng)琐驴，表的索引需要仔細(xì)評估必要性，另外索引也會占用一定的存儲空間带到。
1.2熊响、只通過索引訪問數(shù)據(jù)
有些時候旨别，我們只是訪問表中的幾個字段，并且字段內(nèi)容較少汗茄，我們可以為這幾個字段單獨(dú)建立一個組合索引秸弛，這樣就可以直接只通過訪問索引就能得到數(shù)據(jù)，一般索引占用的磁盤空間比表小很多洪碳，所以這種方式可以大大減少磁盤IO開銷递览。
如：select id,name from company where type='2';
如果這個SQL經(jīng)常使用，我們可以在type,id,name上創(chuàng)建組合索引
create index my_comb_index on company(type,id,name);
有了這個組合索引后瞳腌，SQL就可以直接通過my_comb_index索引返回數(shù)據(jù)绞铃，不需要訪問company表。
還是拿字典舉例：有一個需求嫂侍，需要查詢一本漢語字典中所有漢字的個數(shù)儿捧，如果我們的字典沒有目錄索引，那我們只能從字典內(nèi)容里一個一個字計數(shù)挑宠，最后返回結(jié)果菲盾。如果我們有一個拼音目錄，那就可以只訪問拼音目錄的漢字進(jìn)行計數(shù)痹栖。如果一本字典有1000頁亿汞，拼音目錄有20頁瞭空，那我們的數(shù)據(jù)訪問成本相當(dāng)于全表訪問的50分之一揪阿。
切記疗我，性能優(yōu)化是無止境的，當(dāng)性能可以滿足需求時即可南捂，不要過度優(yōu)化吴裤。在實(shí)際數(shù)據(jù)庫中我們不可能把每個SQL請求的字段都建在索引里，所以這種只通過索引訪問數(shù)據(jù)的方法一般只用于核心應(yīng)用溺健，也就是那種對核心表訪問量最高且查詢字段數(shù)據(jù)量很少的查詢麦牺。
1.3、優(yōu)化SQL執(zhí)行計劃
SQL執(zhí)行計劃是關(guān)系型數(shù)據(jù)庫最核心的技術(shù)之一鞭缭，它表示SQL執(zhí)行時的數(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í)行計劃的算法約300種墩瞳，而且一直在增加，所以SQL執(zhí)行計劃是一個非常復(fù)雜的課題氏豌，一個普通DBA能掌握50種就很不錯了喉酌，就算是資深DBA也不可能把每個執(zhí)行計劃的算法描述清楚。雖然有這么多種算法泵喘，但并不表示我們無法優(yōu)化執(zhí)行計劃瞭吃，因?yàn)槲覀兂Ｓ玫腟QL執(zhí)行計劃算法也就十幾個，如果一個程序員能把這十幾個算法搞清楚涣旨，那就掌握了80%的SQL執(zhí)行計劃調(diào)優(yōu)知識歪架。
由于篇幅的原因，SQL執(zhí)行計劃需要專題介紹霹陡，在這里就不多說了和蚪。
2、返回更少的數(shù)據(jù)
2.1烹棉、數(shù)據(jù)分頁處理
一般數(shù)據(jù)分頁方式有：
2.1.1攒霹、客戶端(應(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)：首次交互時間長催束，占用客戶端內(nèi)存
適應(yīng)場景：客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)延時較大，但要求后續(xù)操作流暢伏社，如手機(jī)GPRS抠刺，超遠(yuǎn)程訪問（跨國）等等塔淤。
2.1.2、應(yīng)用服務(wù)器分頁
將數(shù)據(jù)從數(shù)據(jù)庫服務(wù)器全部下載到應(yīng)用服務(wù)器速妖，在應(yīng)用服務(wù)器內(nèi)部再進(jìn)行數(shù)據(jù)篩選高蜂。以下是一個應(yīng)用服務(wù)器端Java程序分頁的示例：
List list=executeQuery(“select * from employee order by id”);
Int count= list.size();
List subList= list.subList(10, 20);
優(yōu)點(diǎn)：編碼簡單，只需要一次SQL交互罕容，總數(shù)據(jù)與分頁數(shù)據(jù)差不多時性能較好备恤。
缺點(diǎn)：總數(shù)據(jù)量較多時性能較差。
適應(yīng)場景：數(shù)據(jù)庫系統(tǒng)不支持分頁處理锦秒，數(shù)據(jù)量較小并且可控露泊。
2.1.3、數(shù)據(jù)庫SQL分頁
采用數(shù)據(jù)庫SQL分頁需要兩次SQL完成
一個SQL計算總數(shù)量
一個SQL返回分頁后的數(shù)據(jù)
優(yōu)點(diǎn)：性能好
缺點(diǎn)：編碼復(fù)雜旅择，各種數(shù)據(jù)庫語法不同滤淳，需要兩次SQL交互。
oracle數(shù)據(jù)庫一般采用rownum來進(jìn)行分頁砌左，常用分頁語法有如下兩種：
直接通過rownum分頁：
select * from (
select a.*,rownum rn from
(select * from product a where company_id=? order by status) a
where rownum<=20)
where rn>10;
數(shù)據(jù)訪問開銷=索引IO+索引全部記錄結(jié)果對應(yīng)的表數(shù)據(jù)IO
采用rowid分頁語法
優(yōu)化原理是通過純索引找出分頁記錄的ROWID脖咐，再通過ROWID回表返回數(shù)據(jù)，要求內(nèi)層查詢和排序字段全在索引里汇歹。
create index myindex on product(company_id,status);
select b.* from (
select * from (
select a.*,rownum rn from
(select rowid rid,status from product a where company_id=? order by status) a
where rownum<=20)
where rn>10) a, product b
where a.rid=b.rowid;
數(shù)據(jù)訪問開銷=索引IO+索引分頁結(jié)果對應(yīng)的表數(shù)據(jù)IO
實(shí)例：
一個公司產(chǎn)品有1000條記錄屁擅，要分頁取其中20個產(chǎn)品，假設(shè)訪問公司索引需要50個IO产弹，2條記錄需要1個表數(shù)據(jù)IO派歌。
那么按第一種ROWNUM分頁寫法，需要550(50+1000/2)個IO痰哨，按第二種ROWID分頁寫法胶果，只需要60個IO(50+20/2);
2.2、只返回需要的字段
通過去除不必要的返回字段可以提高性能斤斧，例：
調(diào)整前：select * from product where company_id=?;
調(diào)整后：select id,name from product where company_id=?;
優(yōu)點(diǎn)：
1早抠、減少數(shù)據(jù)在網(wǎng)絡(luò)上傳輸開銷
2、減少服務(wù)器數(shù)據(jù)處理開銷
3撬讽、減少客戶端內(nèi)存占用
4蕊连、字段變更時提前發(fā)現(xiàn)問題，減少程序BUG
5游昼、如果訪問的所有字段剛好在一個索引里面甘苍，則可以使用純索引訪問提高性能。
缺點(diǎn)：增加編碼工作量
由于會增加一些編碼工作量烘豌，所以一般需求通過開發(fā)規(guī)范來要求程序員這么做载庭，否則等項(xiàng)目上線后再整改工作量更大。
如果你的查詢表中有大字段或內(nèi)容較多的字段，如備注信息囚聚、文件內(nèi)容等等靖榕，那在查詢表時一定要注意這方面的問題，否則可能會帶來嚴(yán)重的性能問題靡挥。如果表經(jīng)常要查詢并且請求大內(nèi)容字段的概率很低序矩，我們可以采用分表處理鸯绿，將一個大表分拆成兩個一對一的關(guān)系表跋破，將不常用的大內(nèi)容字段放在一張單獨(dú)的表中。如一張存儲上傳文件的表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）
我們可以分拆成兩張一對一的關(guān)系表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）
T_FILECONTENT（ID, FILE_CONTENT）
通過這種分拆瓶蝴，可以大大提少T_FILE表的單條記錄及總大小毒返，這樣在查詢T_FILE時性能會更好，當(dāng)需要查詢FILE_CONTENT字段內(nèi)容時再訪問T_FILECONTENT表舷手。
3拧簸、減少交互次數(shù)
3.1、batch DML
數(shù)據(jù)庫訪問框架一般都提供了批量提交的接口男窟，jdbc支持batch的提交處理方法盆赤，當(dāng)你一次性要往一個表中插入1000萬條數(shù)據(jù)時，如果采用普通的executeUpdate處理歉眷，那么和服務(wù)器交互次數(shù)為1000萬次牺六，按每秒鐘可以向數(shù)據(jù)庫服務(wù)器提交10000次估算，要完成所有工作需要1000秒汗捡。如果采用批量提交模式淑际，1000條提交一次，那么和服務(wù)器交互次數(shù)為1萬次扇住，交互次數(shù)大大減少春缕。采用batch操作一般不會減少很多數(shù)據(jù)庫服務(wù)器的物理IO，但是會大大減少客戶端與服務(wù)端的交互次數(shù)艘蹋，從而減少了多次發(fā)起的網(wǎng)絡(luò)延時開銷锄贼，同時也會降低數(shù)據(jù)庫的CPU開銷。
假設(shè)要向一個普通表插入1000萬數(shù)據(jù)女阀，每條記錄大小為1K字節(jié)咱娶，表上沒有任何索引，客戶端與數(shù)據(jù)庫服務(wù)器網(wǎng)絡(luò)是100Mbps强品，以下是根據(jù)現(xiàn)在一般計算機(jī)能力估算的各種batch大小性能對比值：
|
單位：ms
|
No batch
|
Batch=10
|
Batch=100
|
Batch=1000
|
Batch=10000
|
|
服務(wù)器事務(wù)處理時間
|
0.1
|
0.1
|
0.1
|
0.1
|
0.1
|
|
服務(wù)器IO處理時間
|
0.02
|
0.2
|
2
|
20
|
200
|
|
網(wǎng)絡(luò)交互發(fā)起時間
|
0.1
|
0.1
|
0.1
|
0.1
|
0.1
|
|
網(wǎng)絡(luò)數(shù)據(jù)傳輸時間
|
0.01
|
0.1
|
1
|
10
|
100
|
|
小計
|
0.23
|
0.5
|
3.2
|
30.2
|
300.2
|
|
平均每條記錄處理時間
|
0.23
|
0.05
|
0.032
|
0.0302
|
0.03002
|
從上可以看出膘侮，Insert操作加大Batch可以對性能提高近8倍性能，一般根據(jù)主鍵的Update或Delete操作也可能提高2-3倍性能的榛，但不如Insert明顯琼了，因?yàn)閁pdate及Delete操作可能有比較大的開銷在物理IO訪問。以上僅是理論計算值，實(shí)際情況需要根據(jù)具體環(huán)境測量雕薪。
3.2昧诱、In List
很多時候我們需要按一些ID查詢數(shù)據(jù)庫記錄，我們可以采用一個ID一個請求發(fā)給數(shù)據(jù)庫所袁，如下所示：
for :var in ids[] do begin
select * from mytable where id=:var;
end;
我們也可以做一個小的優(yōu)化盏档， 如下所示，用ID INLIST的這種方式寫SQL：
select * from mytable where id in(:id1,id2,...,idn);
通過這樣處理可以大大減少SQL請求的數(shù)量燥爷，從而提高性能蜈亩。那如果有10000個ID，那是不是全部放在一條SQL里處理呢前翎？答案肯定是否定的稚配。首先大部份數(shù)據(jù)庫都會有SQL長度和IN里個數(shù)的限制，如ORACLE的IN里就不允許超過1000個值港华。
另外當(dāng)前數(shù)據(jù)庫一般都是采用基于成本的優(yōu)化規(guī)則道川，當(dāng)IN數(shù)量達(dá)到一定值時有可能改變SQL執(zhí)行計劃，從索引訪問變成全表訪問立宜，這將使性能急劇變化冒萄。隨著SQL中IN的里面的值個數(shù)增加，SQL的執(zhí)行計劃會更復(fù)雜橙数，占用的內(nèi)存將會變大尊流，這將會增加服務(wù)器CPU及內(nèi)存成本。
評估在IN里面一次放多少個值還需要考慮應(yīng)用服務(wù)器本地內(nèi)存的開銷商模，有并發(fā)訪問時要計算本地數(shù)據(jù)使用周期內(nèi)的并發(fā)上限奠旺，否則可能會導(dǎo)致內(nèi)存溢出。
綜合考慮施流，一般IN里面的值個數(shù)超過20個以后性能基本沒什么太大變化响疚，也特別說明不要超過100，超過后可能會引起執(zhí)行計劃的不穩(wěn)定性及增加數(shù)據(jù)庫CPU及內(nèi)存成本瞪醋，這個需要專業(yè)DBA評估忿晕。
3.3、設(shè)置Fetch Size
當(dāng)我們采用select從數(shù)據(jù)庫查詢數(shù)據(jù)時银受，數(shù)據(jù)默認(rèn)并不是一條一條返回給客戶端的践盼，也不是一次全部返回客戶端的，而是根據(jù)客戶端fetch_size參數(shù)處理宾巍，每次只返回fetch_size條記錄咕幻，當(dāng)客戶端游標(biāo)遍歷到尾部時再從服務(wù)端取數(shù)據(jù)，直到最后全部傳送完成顶霞。所以如果我們要從服務(wù)端一次取大量數(shù)據(jù)時肄程，可以加大fetch_size锣吼，這樣可以減少結(jié)果數(shù)據(jù)傳輸?shù)慕换ゴ螖?shù)及服務(wù)器數(shù)據(jù)準(zhǔn)備時間，提高性能蓝厌。
以下是jdbc測試的代碼玄叠，采用本地數(shù)據(jù)庫，表緩存在數(shù)據(jù)庫CACHE中拓提，因此沒有網(wǎng)絡(luò)連接及磁盤IO開銷读恃，客戶端只遍歷游標(biāo)，不做任何處理代态，這樣更能體現(xiàn)fetch參數(shù)的影響：
String vsql ="select * from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(1000);
ResultSet rs = pstmt.executeQuery(vsql);
int cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int i = 1; i <= cnt; i++) {
o = rs.getObject(i);
}
}
測試示例中的employee表有100000條記錄寺惫，每條記錄平均長度135字節(jié)
以下是測試結(jié)果，對每種fetchsize測試5次再取平均值：
|
fetchsize
|
elapse_time（s）
|
|
1
|
20.516
|
|
2
|
11.34
|
|
4
|
6.894
|
|
8
|
4.65
|
|
16
|
3.584
|
|
32
|
2.865
|
|
64
|
2.656
|
|
128
|
2.44
|
|
256
|
2.765
|
|
512
|
3.075
|
|
1024
|
2.862
|
|
2048
|
2.722
|
|
4096
|
2.681
|
|
8192
|
2.715
|
[圖片上傳失敗...(image-7f1f0-1520414439368)]
Oracle jdbc fetchsize默認(rèn)值為10胆数，由上測試可以看出fetchsize對性能影響還是比較大的肌蜻，但是當(dāng)fetchsize大于100時就基本上沒有影響了互墓。fetchsize并不會存在一個最優(yōu)的固定值必尼，因?yàn)檎w性能與記錄集大小及硬件平臺有關(guān)。根據(jù)測試結(jié)果建議當(dāng)一次性要取大量數(shù)據(jù)時這個值設(shè)置為100左右篡撵，不要小于40判莉。注意，fetchsize不能設(shè)置太大育谬，如果一次取出的數(shù)據(jù)大于JVM的內(nèi)存會導(dǎo)致內(nèi)存溢出券盅，所以建議不要超過1000，太大了也沒什么性能提高膛檀，反而可能會增加內(nèi)存溢出的危險锰镀。
注：圖中fetchsize在128以后會有一些小的波動，這并不是測試誤差咖刃，而是由于resultset填充到具體對像時間不同的原因篱瞎，由于resultset已經(jīng)到本地內(nèi)存里了误算，所以估計是由于CPU的L1,L2 Cache命中率變化造成，由于變化不大，所以筆者也未深入分析原因赠幕。
iBatis的SqlMapping配置文件可以對每個SQL語句指定fetchsize大小，如下所示：
<select id="getAllProduct" resultMap="HashMap"** fetchSize="1000"**>
select * from employee
</select>
3.4朦拖、使用存儲過程
大型數(shù)據(jù)庫一般都支持存儲過程刃鳄，合理的利用存儲過程也可以提高系統(tǒng)性能。如你有一個業(yè)務(wù)需要將A表的數(shù)據(jù)做一些加工然后更新到B表中箩帚，但是又不可能一條SQL完成真友，這時你需要如下3步操作：
a：將A表數(shù)據(jù)全部取出到客戶端；
b：計算出要更新的數(shù)據(jù)紧帕；
c：將計算結(jié)果更新到B表盔然。
如果采用存儲過程你可以將整個業(yè)務(wù)邏輯封裝在存儲過程里，然后在客戶端直接調(diào)用存儲過程處理，這樣可以減少網(wǎng)絡(luò)交互的成本轻纪。
當(dāng)然油额，存儲過程也并不是十全十美，存儲過程有以下缺點(diǎn)：
a刻帚、不可移植性潦嘶，每種數(shù)據(jù)庫的內(nèi)部編程語法都不太相同，當(dāng)你的系統(tǒng)需要兼容多種數(shù)據(jù)庫時最好不要用存儲過程崇众。
b掂僵、學(xué)習(xí)成本高，DBA一般都擅長寫存儲過程顷歌，但并不是每個程序員都能寫好存儲過程锰蓬，除非你的團(tuán)隊有較多的開發(fā)人員熟悉寫存儲過程，否則后期系統(tǒng)維護(hù)會產(chǎn)生問題眯漩。
c芹扭、業(yè)務(wù)邏輯多處存在，采用存儲過程后也就意味著你的系統(tǒng)有一些業(yè)務(wù)邏輯不是在應(yīng)用程序里處理赦抖，這種架構(gòu)會增加一些系統(tǒng)維護(hù)和調(diào)試成本舱卡。
d、存儲過程和常用應(yīng)用程序語言不一樣队萤，它支持的函數(shù)及語法有可能不能滿足需求轮锥，有些邏輯就只能通過應(yīng)用程序處理。
e要尔、如果存儲過程中有復(fù)雜運(yùn)算的話舍杜，會增加一些數(shù)據(jù)庫服務(wù)端的處理成本，對于集中式數(shù)據(jù)庫可能會導(dǎo)致系統(tǒng)可擴(kuò)展性問題赵辕。
f既绩、為了提高性能，數(shù)據(jù)庫會把存儲過程代碼編譯成中間運(yùn)行代碼(類似于java的class文件)匆帚，所以更像靜態(tài)語言熬词。當(dāng)存儲過程引用的對像(表、視圖等等)結(jié)構(gòu)改變后吸重，存儲過程需要重新編譯才能生效互拾，在24*7高并發(fā)應(yīng)用場景，一般都是在線變更結(jié)構(gòu)的嚎幸，所以在變更的瞬間要同時編譯存儲過程颜矿，這可能會導(dǎo)致數(shù)據(jù)庫瞬間壓力上升引起故障(Oracle數(shù)據(jù)庫就存在這樣的問題)。
個人觀點(diǎn)：普通業(yè)務(wù)邏輯盡量不要使用存儲過程嫉晶，定時性的ETL任務(wù)或報表統(tǒng)計函數(shù)可以根據(jù)團(tuán)隊資源情況采用存儲過程處理骑疆。
3.5田篇、優(yōu)化業(yè)務(wù)邏輯
要通過優(yōu)化業(yè)務(wù)邏輯來提高性能是比較困難的，這需要程序員對所訪問的數(shù)據(jù)及業(yè)務(wù)流程非常清楚箍铭。
舉一個案例：
某移動公司推出優(yōu)惠套參泊柬，活動對像為VIP會員并且2010年1，2诈火，3月平均話費(fèi)20元以上的客戶兽赁。
那我們的檢測邏輯為：
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
select vip_flag from member where phone_no='13988888888';
if avg_money>20 and vip_flag=true then
begin
執(zhí)行套參();
end;
如果我們修改業(yè)務(wù)邏輯為:
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
begin
select vip_flag from member where phone_no='13988888888';
if vip_flag=true then
begin
執(zhí)行套參();
end;
end;
通過這樣可以減少一些判斷vip_flag的開銷，平均話費(fèi)20元以下的用戶就不需要再檢測是否VIP了冷守。
如果程序員分析業(yè)務(wù)刀崖，VIP會員比例為1%，平均話費(fèi)20元以上的用戶比例為90%拍摇，那我們改成如下：
select vip_flag from member where phone_no='13988888888';
if vip_flag=true then
begin
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
begin
執(zhí)行套參();
end;
end;
這樣就只有1%的VIP會員才會做檢測平均話費(fèi)亮钦，最終大大減少了SQL的交互次數(shù)。
以上只是一個簡單的示例充活，實(shí)際的業(yè)務(wù)總是比這復(fù)雜得多蜂莉，所以一般只是高級程序員更容易做出優(yōu)化的邏輯，但是我們需要有這樣一種成本優(yōu)化的意識堪唐。
3.6巡语、使用ResultSet游標(biāo)處理記錄
現(xiàn)在大部分Java框架都是通過jdbc從數(shù)據(jù)庫取出數(shù)據(jù)翎蹈，然后裝載到一個list里再處理淮菠，list里可能是業(yè)務(wù)Object，也可能是hashmap荤堪。
由于JVM內(nèi)存一般都小于4G合陵，所以不可能一次通過sql把大量數(shù)據(jù)裝載到list里。為了完成功能澄阳，很多程序員喜歡采用分頁的方法處理拥知，如一次從數(shù)據(jù)庫取1000條記錄，通過多次循環(huán)搞定碎赢，保證不會引起JVM Out of memory問題低剔。
以下是實(shí)現(xiàn)此功能的代碼示例，t_employee表有10萬條記錄肮塞，設(shè)置分頁大小為1000：
d1 = Calendar.getInstance().getTime();
vsql = "select count(*) cnt from t_employee";
pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
cnt = rs.getInt("cnt");
}
Integer lastid=0;
Integer pagesize=1000;
System.out.println("cnt:" + cnt);
String vsql = "select count(*) cnt from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
cnt = rs.getInt("cnt");
}
Integer lastid = 0;
Integer pagesize = 1000;
System.out.println("cnt:" + cnt);
for (int i = 0; i <= cnt / pagesize; i++) {
vsql = "select * from (select * from t_employee where id>? order by id) where rownum<=?";
pstmt = conn.prepareStatement(vsql);
pstmt.setFetchSize(1000);
pstmt.setInt(1, lastid);
pstmt.setInt(2, pagesize);
rs = pstmt.executeQuery();
int col_cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
}
lastid = rs.getInt("id");
}
rs.close();
pstmt.close();
}
以上代碼實(shí)際執(zhí)行時間為6.516秒
很多持久層框架為了盡量讓程序員使用方便襟齿，封裝了jdbc通過statement執(zhí)行數(shù)據(jù)返回到resultset的細(xì)節(jié)，導(dǎo)致程序員會想采用分頁的方式處理問題枕赵。實(shí)際上如果我們采用jdbc原始的resultset游標(biāo)處理記錄猜欺，在resultset循環(huán)讀取的過程中處理記錄，這樣就可以一次從數(shù)據(jù)庫取出所有記錄拷窜。顯著提高性能开皿。
這里需要注意的是涧黄，采用resultset游標(biāo)處理記錄時，應(yīng)該將游標(biāo)的打開方式設(shè)置為FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)赋荆，否則會把結(jié)果緩存在JVM里笋妥，造成JVM Out of memory問題。
代碼示例：
String vsql ="select * from t_employee";
PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
pstmt.setFetchSize(100);
ResultSet rs = pstmt.executeQuery(vsql);
int col_cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
}
}
調(diào)整后的代碼實(shí)際執(zhí)行時間為3.156秒
從測試結(jié)果可以看出性能提高了1倍多窄潭，如果采用分頁模式數(shù)據(jù)庫每次還需發(fā)生磁盤IO的話那性能可以提高更多挽鞠。
iBatis等持久層框架考慮到會有這種需求，所以也有相應(yīng)的解決方案狈孔，在iBatis里我們不能采用queryForList的方法信认，而應(yīng)用該采用queryWithRowHandler加回調(diào)事件的方式處理，如下所示：
MyRowHandler myrh=new MyRowHandler();
sqlmap.queryWithRowHandler("getAllEmployee", myrh);
class MyRowHandler implements RowHandler {
**public** **void** handleRow(Object o) {
//todo something
}
}
iBatis的queryWithRowHandler很好的封裝了resultset遍歷的事件處理均抽，效果及性能與resultset遍歷一樣嫁赏，也不會產(chǎn)生JVM內(nèi)存溢出。
4油挥、減少數(shù)據(jù)庫服務(wù)器CPU運(yùn)算
4.1潦蝇、使用綁定變量
綁定變量是指SQL中對變化的值采用變量參數(shù)的形式提交，而不是在SQL中直接拼寫對應(yīng)的值深寥。
非綁定變量寫法：Select * from employee where id=1234567
綁定變量寫法：
Select * from employee where id=?
Preparestatement.setInt(1,1234567)
Java中Preparestatement就是為處理綁定變量提供的對像攘乒，綁定變量有以下優(yōu)點(diǎn)：
1、防止SQL注入
2惋鹅、提高SQL可讀性
3则酝、提高SQL解析性能，不使用綁定變更我們一般稱為硬解析闰集，使用綁定變量我們稱為軟解析沽讹。
第1和第2點(diǎn)很好理解，做編碼的人應(yīng)該都清楚武鲁，這里不詳細(xì)說明爽雄。關(guān)于第3點(diǎn)，到底能提高多少性能呢沐鼠，下面舉一個例子說明：
假設(shè)有這個這樣的一個數(shù)據(jù)庫主機(jī)：
2個4核CPU
100塊磁盤挚瘟，每個磁盤支持IOPS為160
業(yè)務(wù)應(yīng)用的SQL如下：
select * from table where pk=?
這個SQL平均4個IO（3個索引IO+1個數(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時間忽略乘盖，要求計算系統(tǒng)對如上應(yīng)用采用硬解析與采用軟解析支持的每秒最大并發(fā)數(shù)：
|
是否使用綁定變量
|
CPU支持最大并發(fā)數(shù)
|
磁盤IO支持最大并發(fā)數(shù)
|
|
不使用
|
241000=8000
|
100160=16000*
|
|
使用
|
241000/0.02=400000
|
100160=16000*
|
從以上計算可以看出，不使用綁定變量的系統(tǒng)當(dāng)并發(fā)達(dá)到8000時會在CPU上產(chǎn)生瓶頸排拷，當(dāng)使用綁定變量的系統(tǒng)當(dāng)并行達(dá)到16000時會在磁盤IO上產(chǎn)生瓶頸侧漓。所以如果你的系統(tǒng)CPU有瓶頸時請先檢查是否存在大量的硬解析操作。
使用綁定變量為何會提高SQL解析性能监氢，這個需要從數(shù)據(jù)庫SQL執(zhí)行原理說明布蔗，一條SQL在Oracle數(shù)據(jù)庫中的執(zhí)行過程如下圖所示：
[圖片上傳失敗...(image-7aad1d-1520414439366)]
當(dāng)一條SQL發(fā)送給數(shù)據(jù)庫服務(wù)器后藤违，系統(tǒng)首先會將SQL字符串進(jìn)行hash運(yùn)算，得到hash值后再從服務(wù)器內(nèi)存里的SQL緩存區(qū)中進(jìn)行檢索纵揍，如果有相同的SQL字符顿乒，并且確認(rèn)是同一邏輯的SQL語句，則從共享池緩存中取出SQL對應(yīng)的執(zhí)行計劃泽谨，根據(jù)執(zhí)行計劃讀取數(shù)據(jù)并返回結(jié)果給客戶端璧榄。
如果在共享池中未發(fā)現(xiàn)相同的SQL則根據(jù)SQL邏輯生成一條新的執(zhí)行計劃并保存在SQL緩存區(qū)中，然后根據(jù)執(zhí)行計劃讀取數(shù)據(jù)并返回結(jié)果給客戶端吧雹。
為了更快的檢索SQL是否在緩存區(qū)中骨杂，首先進(jìn)行的是SQL字符串hash值對比，如果未找到則認(rèn)為沒有緩存雄卷，如果存在再進(jìn)行下一步的準(zhǔn)確對比搓蚪，所以要命中SQL緩存區(qū)應(yīng)保證SQL字符是完全一致，中間有大小寫或空格都會認(rèn)為是不同的SQL丁鹉。
如果我們不采用綁定變量妒潭，采用字符串拼接的模式生成SQL,那么每條SQL都會產(chǎn)生執(zhí)行計劃，這樣會導(dǎo)致共享池耗盡揣钦，緩存命中率也很低雳灾。
一些不使用綁定變量的場景：
a、數(shù)據(jù)倉庫應(yīng)用冯凹，這種應(yīng)用一般并發(fā)不高谎亩，但是每個SQL執(zhí)行時間很長，SQL解析的時間相比SQL執(zhí)行時間比較小谈竿，綁定變量對性能提高不明顯团驱。數(shù)據(jù)倉庫一般都是內(nèi)部分析應(yīng)用，所以也不太會發(fā)生SQL注入的安全問題空凸。
b、數(shù)據(jù)分布不均勻的特殊邏輯寸痢，如產(chǎn)品表呀洲，記錄有1億，有一產(chǎn)品狀態(tài)字段啼止，上面建有索引道逗，有審核中，審核通過献烦，審核未通過3種狀態(tài)滓窍，其中審核通過9500萬，審核中1萬巩那，審核不通過499萬吏夯。
要做這樣一個查詢：
select count(*) from product where status=?
采用綁定變量的話此蜈，那么只會有一個執(zhí)行計劃，如果走索引訪問噪生，那么對于審核中查詢很快裆赵，對審核通過和審核不通過會很慢；如果不走索引跺嗽，那么對于審核中與審核通過和審核不通過時間基本一樣战授；
對于這種情況應(yīng)該不使用綁定變量，而直接采用字符拼接的方式生成SQL桨嫁，這樣可以為每個SQL生成不同的執(zhí)行計劃植兰，如下所示。
select count(*) from product where status='approved'; //不使用索引
select count(*) from product where status='tbd'; //不使用索引
select count() from product where status='auditing';//使用索引*
4.2璃吧、合理使用排序
Oracle的排序算法一直在優(yōu)化钉跷，但是總體時間復(fù)雜度約等于nLog(n)。普通OLTP系統(tǒng)排序操作一般都是在內(nèi)存里進(jìn)行的肚逸，對于數(shù)據(jù)庫來說是一種CPU的消耗爷辙，曾在PC機(jī)做過測試，單核普通CPU在1秒鐘可以完成100萬條記錄的全內(nèi)存排序操作朦促，所以說由于現(xiàn)在CPU的性能增強(qiáng)膝晾，對于普通的幾十條或上百條記錄排序?qū)ο到y(tǒng)的影響也不會很大。但是當(dāng)你的記錄集增加到上萬條以上時务冕，你需要注意是否一定要這么做了血当，大記錄集排序不僅增加了CPU開銷，而且可能會由于內(nèi)存不足發(fā)生硬盤排序的現(xiàn)象禀忆，當(dāng)發(fā)生硬盤排序時性能會急劇下降臊旭，這種需求需要與DBA溝通再決定，取決于你的需求和數(shù)據(jù)箩退，所以只有你自己最清楚离熏，而不要被別人說排序很慢就嚇倒。
以下列出了可能會發(fā)生排序操作的SQL語法：
Order by
Group by
Distinct
Exists子查詢
Not Exists子查詢
In子查詢
Not In子查詢
Union（并集）戴涝，Union All也是一種并集操作滋戳，但是不會發(fā)生排序，如果你確認(rèn)兩個數(shù)據(jù)集不需要執(zhí)行去除重復(fù)數(shù)據(jù)操作啥刻，那請使用Union All 代替Union奸鸯。
Minus（差集）
Intersect（交集）
Create Index
Merge Join，這是一種兩個表連接的內(nèi)部算法可帽，執(zhí)行時會把兩個表先排序好再連接娄涩，應(yīng)用于兩個大表連接的操作。如果你的兩個表連接的條件都是等值運(yùn)算映跟，那可以采用Hash Join來提高性能蓄拣，因?yàn)镠ash Join使用Hash 運(yùn)算來代替排序的操作扬虚。具體原理及設(shè)置參考SQL執(zhí)行計劃優(yōu)化專題。
4.3弯蚜、減少比較操作
我們SQL的業(yè)務(wù)邏輯經(jīng)常會包含一些比較操作孔轴，如a=b，a<b之類的操作碎捺，對于這些比較操作數(shù)據(jù)庫都體現(xiàn)得很好路鹰，但是如果有以下操作，我們需要保持警惕：
Like模糊查詢收厨，如下所示：
a like ‘%abc%’
Like模糊查詢對于數(shù)據(jù)庫來說不是很擅長剂习，特別是你需要模糊檢查的記錄有上萬條以上時缤苫，性能比較糟糕疗琉，這種情況一般可以采用專用Search或者采用全文索引方案來提高性能簿寂。
不能使用索引定位的大量In List，如下所示：
a in (:1,:2,:3,…,:n) ----n>20
如果這里的a字段不能通過索引比較拧额，那數(shù)據(jù)庫會將字段與in里面的每個值都進(jìn)行比較運(yùn)算碑诉，如果記錄數(shù)有上萬以上，會明顯感覺到SQL的CPU開銷加大侥锦，這個情況有兩種解決方式：
a进栽、 將in列表里面的數(shù)據(jù)放入一張中間小表，采用兩個表Hash Join關(guān)聯(lián)的方式處理恭垦；
b快毛、 采用str2varList方法將字段串列表轉(zhuǎn)換一個臨時表處理，關(guān)于str2varList方法可以在網(wǎng)上直接查詢番挺，這里不詳細(xì)介紹唠帝。
以上兩種解決方案都需要與中間表Hash Join的方式才能提高性能，如果采用了Nested Loop的連接方式性能會更差玄柏。
如果發(fā)現(xiàn)我們的系統(tǒng)IO沒問題但是CPU負(fù)載很高襟衰，就有可能是上面的原因，這種情況不太常見禁荸，如果遇到了最好能和DBA溝通并確認(rèn)準(zhǔn)確的原因右蒲。
4.4、大量復(fù)雜運(yùn)算在客戶端處理
什么是復(fù)雜運(yùn)算赶熟，一般我認(rèn)為是一秒鐘CPU只能做10萬次以內(nèi)的運(yùn)算。如含小數(shù)的對數(shù)及指數(shù)運(yùn)算陷嘴、三角函數(shù)映砖、3DES及BASE64數(shù)據(jù)加密算法等等。
如果有大量這類函數(shù)運(yùn)算灾挨，盡量放在客戶端處理邑退，一般CPU每秒中也只能處理1萬-10萬次這樣的函數(shù)運(yùn)算竹宋，放在數(shù)據(jù)庫內(nèi)不利于高并發(fā)處理。
5地技、利用更多的資源
5.1蜈七、客戶端多進(jìn)程并行訪問
多進(jìn)程并行訪問是指在客戶端創(chuàng)建多個進(jìn)程(線程)，每個進(jìn)程建立一個與數(shù)據(jù)庫的連接莫矗，然后同時向數(shù)據(jù)庫提交訪問請求飒硅。當(dāng)數(shù)據(jù)庫主機(jī)資源有空閑時，我們可以采用客戶端多進(jìn)程并行訪問的方法來提高性能作谚。如果數(shù)據(jù)庫主機(jī)已經(jīng)很忙時三娩，采用多進(jìn)程并行訪問性能不會提高，反而可能會更慢妹懒。所以使用這種方式最好與DBA或系統(tǒng)管理員進(jìn)行溝通后再決定是否采用雀监。
例如：
我們有10000個產(chǎn)品ID，現(xiàn)在需要根據(jù)ID取出產(chǎn)品的詳細(xì)信息眨唬，如果單線程訪問会前，按每個IO要5ms計算，忽略主機(jī)CPU運(yùn)算及網(wǎng)絡(luò)傳輸時間匾竿，我們需要50s才能完成任務(wù)瓦宜。如果采用5個并行訪問，每個進(jìn)程訪問2000個ID搂橙，那么10s就有可能完成任務(wù)歉提。
那是不是并行數(shù)越多越好呢，開1000個并行是否只要50ms就搞定区转，答案肯定是否定的苔巨，當(dāng)并行數(shù)超過服務(wù)器主機(jī)資源的上限時性能就不會再提高，如果再增加反而會增加主機(jī)的進(jìn)程間調(diào)度成本和進(jìn)程沖突機(jī)率废离。
以下是一些如何設(shè)置并行數(shù)的基本建議：
如果瓶頸在服務(wù)器主機(jī)侄泽，但是主機(jī)還有空閑資源，那么最大并行數(shù)取主機(jī)CPU核數(shù)和主機(jī)提供數(shù)據(jù)服務(wù)的磁盤數(shù)兩個參數(shù)中的最小值蜻韭，同時要保證主機(jī)有資源做其它任務(wù)悼尾。
如果瓶頸在客戶端處理，但是客戶端還有空閑資源肖方，那建議不要增加SQL的并行闺魏，而是用一個進(jìn)程取回數(shù)據(jù)后在客戶端起多個進(jìn)程處理即可，進(jìn)程數(shù)根據(jù)客戶端CPU核數(shù)計算俯画。
如果瓶頸在客戶端網(wǎng)絡(luò)析桥，那建議做數(shù)據(jù)壓縮或者增加多個客戶端，采用map reduce的架構(gòu)處理。
如果瓶頸在服務(wù)器網(wǎng)絡(luò)泡仗，那需要增加服務(wù)器的網(wǎng)絡(luò)帶寬或者在服務(wù)端將數(shù)據(jù)壓縮后再處理了埋虹。
5.2、數(shù)據(jù)庫并行處理
數(shù)據(jù)庫并行處理是指客戶端一條SQL的請求娩怎，數(shù)據(jù)庫內(nèi)部自動分解成多個進(jìn)程并行處理搔课，如下圖所示：
[圖片上傳失敗...(image-a5d963-1520414439366)]
并不是所有的SQL都可以使用并行處理，一般只有對表或索引進(jìn)行全部訪問時才可以使用并行截亦。數(shù)據(jù)庫表默認(rèn)是不打開并行訪問爬泥，所以需要指定SQL并行的提示，如下所示：
select /+parallel(a,4)/ *** from employee;
并行的優(yōu)點(diǎn)：
使用多進(jìn)程處理魁巩，充分利用數(shù)據(jù)庫主機(jī)資源（CPU,IO）急灭，提高性能。
并行的缺點(diǎn)：
1谷遂、單個會話占用大量資源葬馋，影響其它會話，所以只適合在主機(jī)負(fù)載低時期使用肾扰；
2畴嘶、只能采用直接IO訪問，不能利用緩存數(shù)據(jù)集晚，所以執(zhí)行前會觸發(fā)將臟緩存數(shù)據(jù)寫入磁盤操作窗悯。
注：
1、并行處理在OLTP類系統(tǒng)中慎用偷拔，使用不當(dāng)會導(dǎo)致一個會話把主機(jī)資源全部占用蒋院，而正常事務(wù)得不到及時響應(yīng)，所以一般只是用于數(shù)據(jù)倉庫平臺莲绰。
2欺旧、一般對于百萬級記錄以下的小表采用并行訪問性能并不能提高，反而可能會讓性能更差蛤签。
SQL性能優(yōu)化 --- 面試題
今天面試辞友，我簡歷上寫了熟悉sql的性能優(yōu)化，但是今天面試震肮，一時想不起別的称龙，就僅僅說出了一條，在這里再總結(jié)一些戳晌，完善自己的知識點(diǎn)鲫尊。
我經(jīng)常用的數(shù)據(jù)庫是oracle，所以我的sql優(yōu)化是程序員針對于oracle的沦偎。
總結(jié)马昨，這個sql優(yōu)化是針對程序員的竞帽，而不是針對dba的扛施，主要就是第一鸿捧，盡量防止模糊，明確指出疙渣，即用列名代替*匙奴，第二，在where語句上下工夫妄荔。第三多表查詢和子查詢泼菌，第四盡量使用綁定。
數(shù)據(jù)庫性能優(yōu)化之SQL語句優(yōu)化1
一啦租、問題的提出
在應(yīng)用系統(tǒng)開發(fā)初期哗伯，由于開發(fā)數(shù)據(jù)庫數(shù)據(jù)比較少，對于查詢SQL語句篷角，復(fù)雜視圖的的編寫等體會不出SQL語句各種寫法的性能優(yōu)劣焊刹，但是如果將應(yīng)用系統(tǒng)提交實(shí)際應(yīng)用后，隨著數(shù)據(jù)庫中數(shù)據(jù)的增加恳蹲，系統(tǒng)的響應(yīng)速度就成為目前系統(tǒng)需要解決的最主要的問題之一虐块。系統(tǒng)優(yōu)化中一個很重要的方面就是SQL語句的優(yōu)化。對于海量數(shù)據(jù)嘉蕾，劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達(dá)到上百倍贺奠，可見對于一個系統(tǒng)不是簡單地能實(shí)現(xiàn)其功能就可，而是要寫出高質(zhì)量的SQL語句错忱，提高系統(tǒng)的可用性儡率。
在多數(shù)情況下，Oracle使用索引來更快地遍歷表以清，優(yōu)化器主要根據(jù)定義的索引來提高性能儿普。但是，如果在SQL語句的where子句中寫的SQL代碼不合理玖媚，就會造成優(yōu)化器刪去索引而使用全表掃描箕肃，一般就這種SQL語句就是所謂的劣質(zhì)SQL語句。在編寫SQL語句時我們應(yīng)清楚優(yōu)化器根據(jù)何種原則來刪除索引今魔，這有助于寫出高性能的SQL語句勺像。
二、SQL語句編寫注意問題
下面就某些SQL語句的where子句編寫中需要注意的問題作詳細(xì)介紹错森。在這些where子句中吟宦，即使某些列存在索引，但是由于編寫了劣質(zhì)的SQL涩维，系統(tǒng)在運(yùn)行該SQL語句時也不能使用該索引殃姓，而同樣使用全表掃描袁波，這就造成了響應(yīng)速度的極大降低。
1. 操作符優(yōu)化
(a) IN 操作符
用IN寫出來的SQL的優(yōu)點(diǎn)是比較容易寫及清晰易懂蜗侈，這比較適合現(xiàn)代軟件開發(fā)的風(fēng)格篷牌。但是用IN的SQL性能總是比較低的，從Oracle執(zhí)行的步驟來分析用IN的SQL與不用IN的SQL有以下區(qū)別：
ORACLE試圖將其轉(zhuǎn)換成多個表的連接踏幻，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢枷颊，再查詢外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個表的連接方式查詢该面。由此可見用IN的SQL至少多了一個轉(zhuǎn)換的過程夭苗。一般的SQL都可以轉(zhuǎn)換成功，但對于含有分組統(tǒng)計等方面的SQL就不能轉(zhuǎn)換了隔缀。
推薦方案：在業(yè)務(wù)密集的SQL當(dāng)中盡量不采用IN操作符题造，用EXISTS 方案代替。
(b) NOT IN操作符
此操作是強(qiáng)列不推薦使用的猾瘸，因?yàn)樗荒軕?yīng)用表的索引界赔。
推薦方案：用NOT EXISTS 方案代替
(c) IS NULL 或IS NOT NULL操作（判斷字段是否為空）
判斷字段是否為空一般是不會應(yīng)用索引的，因?yàn)樗饕遣凰饕罩档男肫蕖２荒苡胣ull作索引仔蝌，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下荒吏，只要這些列中有一列含有null敛惊，該列就會從索引中排除。也就是說如果某列存在空值绰更，即使對該列建索引也不會提高性能瞧挤。任何在where子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的。
推薦方案：用其它相同功能的操作運(yùn)算代替儡湾，如：a is not null 改為 a>0 或a>’’等特恬。不允許字段為空，而用一個缺省值代替空值徐钠，如申請中狀態(tài)字段不允許為空癌刽，缺省為申請。
(d) > 及 < 操作符（大于或小于操作符）
大于或小于操作符一般情況下是不用調(diào)整的尝丐，因?yàn)樗兴饕蜁捎盟饕檎蚁园荩械那闆r下可以對它進(jìn)行優(yōu)化，如一個表有100萬記錄爹袁，一個數(shù)值型字段A远荠，30萬記錄的A=0，30萬記錄的A=1失息，39萬記錄的A=2譬淳，1萬記錄的A=3档址。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因?yàn)锳>2時ORACLE會先找出為2的記錄索引再進(jìn)行比較邻梆，而A>=3時ORACLE則直接找到=3的記錄索引守伸。
(e) LIKE操作符
LIKE操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢确虱，但是如果用得不好則會產(chǎn)生性能上的問題含友，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用范圍索引校辩。
一個實(shí)際例子：用YW_YHJBQK表中營業(yè)編號后面的戶標(biāo)識號可來查詢營業(yè)編號 YY_BH LIKE ‘%5400%’ 這個條件會產(chǎn)生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進(jìn)行兩個范圍的查詢辆童，性能肯定大大提高宜咒。
帶通配符(%)的like語句：
同樣以上面的例子來看這種情況。目前的需求是這樣的把鉴，要求在職工表中查詢名字中包含cliton的人故黑。可以采用如下的查詢SQL語句:
select * from employee where last_name like '%cliton%';
這里由于通配符(%)在搜尋詞首出現(xiàn)庭砍，所以O(shè)racle系統(tǒng)不使用last_name的索引场晶。在很多情況下可能無法避免這種情況，但是一定要心中有底怠缸，通配符如此使用會降低查詢速度诗轻。然而當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引揭北。在下面的查詢中索引得到了使用:
select * from employee where last_name like 'c%';
(f) UNION操作符
UNION在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄扳炬，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果搔体。實(shí)際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄恨樟，最常見的是過程表與歷史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
這個SQL在運(yùn)行時先取出兩個表的結(jié)果疚俱，再用排序空間進(jìn)行排序刪除重復(fù)的記錄劝术，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進(jìn)行排序呆奕。
推薦方案：采用UNION ALL操作符替代UNION养晋，因?yàn)閁NION ALL操作只是簡單的將兩個結(jié)果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
(g) 聯(lián)接列
對于有聯(lián)接的列登馒，即使最后的聯(lián)接值為一個靜態(tài)值匙握，優(yōu)化器是不會使用索引的。我們一起來看一個例子陈轿，假定有一個職工表(employee)圈纺，對于一個職工的姓和名分成兩列存放(FIRST_NAME和LAST_NAME)秦忿，現(xiàn)在要查詢一個叫比爾.克林頓(Bill Cliton)的職工。
下面是一個采用聯(lián)接查詢的SQL語句：
select * from employss where first_name||''||last_name ='Beill Cliton';
上面這條語句完全可以查詢出是否有Bill Cliton這個員工蛾娶，但是這里需要注意灯谣，系統(tǒng)優(yōu)化器對基于last_name創(chuàng)建的索引沒有使用。當(dāng)采用下面這種SQL語句的編寫蛔琅，Oracle系統(tǒng)就可以采用基于last_name創(chuàng)建的索引胎许。
*** where first_name ='Beill' and last_name ='Cliton';
(h) Order by語句
ORDER BY語句決定了Oracle如何將返回的查詢結(jié)果排序塞赂。Order by語句對要排序的列沒有什么特別的限制抬旺，也可以將函數(shù)加入列中(象聯(lián)接或者附加等)雌澄。任何在Order by語句的非索引項(xiàng)或者有計算表達(dá)式都將降低查詢速度寇仓。
仔細(xì)檢查order by語句以找出非索引項(xiàng)或者表達(dá)式油讯，它們會降低性能故爵。解決這個問題的辦法就是重寫order by語句以使用索引槽惫，也可以為所使用的列建立另外一個索引论泛，同時應(yīng)絕對避免在order by子句中使用表達(dá)式职恳。
(i) NOT
我們在查詢時經(jīng)常在where子句使用一些邏輯表達(dá)式所禀，如大于、小于放钦、等于以及不等于等等色徘，也可以使用and(與)、or(或)以及not(非)操禀。NOT可用來對任何邏輯運(yùn)算符號取反褂策。下面是一個NOT子句的例子:
... where not (status ='VALID')
如果要使用NOT，則應(yīng)在取反的短語前面加上括號床蜘，并在短語前面加上NOT運(yùn)算符辙培。NOT運(yùn)算符包含在另外一個邏輯運(yùn)算符中，這就是不等于(<>)運(yùn)算符邢锯。換句話說扬蕊，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運(yùn)算符中丹擎，見下例:
... where status <>'INVALID';
對這個查詢尾抑，可以改寫為不使用NOT：
select * from employee where salary<3000 or salary>3000;
雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些蒂培。第二種查詢允許Oracle對salary列使用索引再愈，而第一種查詢則不能使用索引。
2. SQL書寫的影響
(a) 同一功能同一性能不同寫法SQL的影響护戳。
如一個SQL在A程序員寫的為 Select * from zl_yhjbqk
B程序員寫的為 Select * from dlyx.zl_yhjbqk（帶表所有者的前綴）
C程序員寫的為 Select * from DLYX.ZLYHJBQK（大寫表名）
D程序員寫的為 Select * from DLYX.ZLYHJBQK（中間多了空格）
以上四個SQL在ORACLE分析整理之后產(chǎn)生的結(jié)果及執(zhí)行的時間是一樣的翎冲，但是從ORACLE共享內(nèi)存SGA的原理，可以得出ORACLE對每個SQL 都會對其進(jìn)行一次分析媳荒，并且占用共享內(nèi)存抗悍，如果將SQL的字符串及格式寫得完全相同驹饺，則ORACLE只會分析一次，共享內(nèi)存也只會留下一次的分析結(jié)果缴渊，這不僅可以減少分析SQL的時間赏壹，而且可以減少共享內(nèi)存重復(fù)的信息，ORACLE也可以準(zhǔn)確統(tǒng)計SQL的執(zhí)行頻率衔沼。
(b) WHERE后面的條件順序影響
WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會產(chǎn)生直接的影響蝌借。如：
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上兩個SQL中dy_dj（電壓等級）及xh_bz（銷戶標(biāo)志）兩個字段都沒進(jìn)行索引，所以執(zhí)行的時候都是全表掃描指蚁，第一條SQL的dy_dj = '1KV以下'條件在記錄集內(nèi)比率為99%菩佑，而xh_bz=1的比率只為0.5%，在進(jìn)行第一條SQL的時候99%條記錄都進(jìn)行dy_dj及xh_bz的比較欣舵，而在進(jìn)行第二條SQL的時候0.5%條記錄都進(jìn)行dy_dj及xh_bz的比較擎鸠，以此可以得出第二條SQL的CPU占用率明顯比第一條低。
(c) 查詢表順序的影響
在FROM后面的表中的列表順序會對SQL執(zhí)行性能影響缘圈，在沒有索引及ORACLE沒有對表進(jìn)行統(tǒng)計分析的情況下，ORACLE會按表出現(xiàn)的順序進(jìn)行鏈接袜蚕，由此可見表的順序不對時會產(chǎn)生十分耗服物器資源的數(shù)據(jù)交叉糟把。（注：如果對表進(jìn)行了統(tǒng)計分析，ORACLE會自動先進(jìn)小表的鏈接牲剃，再進(jìn)行大表的鏈接）
3. SQL語句索引的利用
(a) 對條件字段的一些優(yōu)化
采用函數(shù)處理的字段不能利用索引遣疯，如：
substr(hbs_bh,1,4)=’5400’，優(yōu)化處理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)凿傅， 優(yōu)化處理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
進(jìn)行了顯式或隱式的運(yùn)算的字段不能進(jìn)行索引缠犀，如：ss_df+20>50，優(yōu)化處理：ss_df>30
‘X’ || hbs_bh>’X5400021452’聪舒，優(yōu)化處理：hbs_bh>’5400021542’
sk_rq+5=sysdate辨液，優(yōu)化處理：sk_rq=sysdate-5
hbs_bh=5401002554，優(yōu)化處理：hbs_bh=’ 5401002554’箱残，注：此條件對hbs_bh 進(jìn)行隱式的to_number轉(zhuǎn)換滔迈，因?yàn)閔bs_bh字段是字符型。
條件內(nèi)包括了多個本表的字段運(yùn)算時不能進(jìn)行索引被辑，如：
ys_df>cx_df燎悍，無法進(jìn)行優(yōu)化
qc_bh || kh_bh=’5400250000’，優(yōu)化處理：qc_bh=’5400’ and kh_bh=’250000’
4. 更多方面SQL優(yōu)化資料分享
（1） 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)：
ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名盼理，F(xiàn)ROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理谈山，在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個被其他表所引用的表.
（2） WHERE子句中的連接順序：
ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.
（3） SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的過程中, 會將'*' 依次轉(zhuǎn)換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時間宏怔。
（4） 減少訪問數(shù)據(jù)庫的次數(shù)：
ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等奏路。
（5） 在SQLPlus , SQLForms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量 ,建議值為200畴椰。
（6） 使用DECODE函數(shù)來減少處理時間：
使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.
（7） 整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問：
如果你有幾個簡單的數(shù)據(jù)庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關(guān)系) 。
（8） 刪除重復(fù)記錄：
最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)思劳。
（9） 用TRUNCATE替代DELETE：
當(dāng)刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息. 如果你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運(yùn)用TRUNCATE時, 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML) 迅矛。
（10） 盡量多使用COMMIT：
只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因?yàn)镃OMMIT所釋放的資源而減少，COMMIT所釋放的資源:
a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息.
b. 被程序語句獲得的鎖
c. redo log buffer 中的空間
d. ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)
（11） 用Where子句替換HAVING子句：
避免使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷. (非oracle中)on潜叛、where秽褒、having這三個都可以加條件的子句中，on是最先執(zhí)行威兜，where次之销斟，having最后，因?yàn)閛n是先把不符合條件的記錄過濾后才進(jìn)行統(tǒng)計椒舵，它就可以減少中間運(yùn)算要處理的數(shù)據(jù)蚂踊，按理說應(yīng)該速度是最快的，where也應(yīng)該比having快點(diǎn)的笔宿，因?yàn)樗^濾數(shù)據(jù)后才進(jìn)行sum犁钟，在兩個表聯(lián)接時才用on的，所以在一個表的時候泼橘，就剩下where跟having比較了涝动。在這單表查詢統(tǒng)計的情況下，如果要過濾的條件沒有涉及到要計算字段炬灭，那它們的結(jié)果是一樣的醋粟，只是where可以使用rushmore技術(shù)，而having就不能重归，在速度上后者要慢如果要涉及到計算的字 段米愿，就表示在沒計算之前，這個字段的值是不確定的鼻吮，根據(jù)上篇寫的工作流程育苟，where的作用時間是在計算之前就完成的，而having就是在計算后才起作 用的狈网，所以在這種情況下宙搬，兩者的結(jié)果會不同。在多表聯(lián)接查詢時拓哺，on比where更早起作用勇垛。系統(tǒng)首先根據(jù)各個表之間的聯(lián)接條件，把多個表合成一個臨時表 后士鸥，再由where進(jìn)行過濾闲孤，然后再計算，計算完后再由having進(jìn)行過濾。由此可見讼积，要想過濾條件起到正確的作用肥照，首先要明白這個條件應(yīng)該在什么時候起作用，然后再決定放在那里勤众。
（12） 減少對表的查詢：
在含有子查詢的SQL語句中,要特別注意減少對表的查詢.例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13） 通過內(nèi)部函數(shù)提高SQL效率：
復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的舆绎。
（14） 使用表的別名(Alias)：
當(dāng)在SQL語句中連接多個表時, 請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。
（15） 用EXISTS替代IN们颜、用NOT EXISTS替代NOT IN：
在許多基于基礎(chǔ)表的查詢中,為了滿足一個條件,往往需要對另一個表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS吕朵。
例子：
（高效）SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
（16） 識別'低效執(zhí)行'的SQL語句：
雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫出自己的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） 用索引提高效率：
索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證.窥突。那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會發(fā)現(xiàn), 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因?yàn)樗饕枰~外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢.努溃。定期的重構(gòu)索引是有必要的：
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
（18） 用EXISTS替換DISTINCT：
當(dāng)提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果. 例子：
(低效):
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） sql語句用大寫的；因?yàn)閛racle總是先解析sql語句阻问，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行梧税。
（20） 在java代碼中盡量少用連接符“＋”連接字符串！
（21） 避免在索引列上使用NOT称近，通常我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在索引列上使用函數(shù)相同的影響. 當(dāng)ORACLE”遇到”NOT,他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描第队。
（22） 避免在索引列上使用計算 WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．舉例:
低效：
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
兩者的區(qū)別在于, 前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄刨秆。
（24） 用UNION替換OR (適用于索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因?yàn)槟銢]有選擇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
這是一條簡單易記的規(guī)則斥铺，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)，在ORACLE8i下坛善，兩者的執(zhí)行路徑似乎是相同的．
低效:
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將無法使用該索引．對于單列索引邻眷，如果列包含空值眠屎，索引中將不存在此記錄. 對于復(fù)合索引，如果每個列都為空肆饶，索引中同樣不存在此記錄. 如果至少有一個列不為空改衩，則記錄存在于索引中．舉例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果所有的索引列都為空，ORACLE將認(rèn)為整個鍵值為空而空不等于空. 因此你可以插入1000 條具有相同鍵值的記錄,當(dāng)然它們都是空! 因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對索引列進(jìn)行空值比較將使ORACLE停用該索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27） 總是使用索引的第一個列：
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優(yōu)化器才會選擇使用該索引. 這也是一條簡單而重要的規(guī)則驯镊，當(dāng)僅引用索引的第二個列時,優(yōu)化器使用了全表掃描而忽略了索引葫督。
（28） 用UNION-ALL 替換UNION ( 如果有可能的話)：
當(dāng)SQL 語句需要UNION兩個查詢結(jié)果集合時,這兩個結(jié)果集合會以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進(jìn)行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是，UNION ALL 將重復(fù)輸出兩個結(jié)果集合中相同記錄. 因此各位還是要從業(yè)務(wù)需求分析使用UNION ALL的可行性. UNION 將對結(jié)果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存. 對于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的. 下面的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 子句只在兩種嚴(yán)格的條件下使用索引.
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） 避免改變索引列的類型:
當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時, ORACLE自動對列進(jìn)行簡單的類型轉(zhuǎn)換.
假設(shè) EMPNO是一個數(shù)值類型的索引列.
SELECT … FROM EMP WHERE EMPNO = ‘123'
實(shí)際上,經(jīng)過ORACLE類型轉(zhuǎn)換, 語句轉(zhuǎn)化為:
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')
幸運(yùn)的是,類型轉(zhuǎn)換沒有發(fā)生在索引列上,索引的用途沒有被改變.
現(xiàn)在,假設(shè)EMP_TYPE是一個字符類型的索引列.
SELECT … FROM EMP WHERE EMP_TYPE = 123
這個語句被ORACLE轉(zhuǎn)換為:
SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123
因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進(jìn)行隱式的類型轉(zhuǎn)換, 最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來. 注意當(dāng)字符和數(shù)值比較時, ORACLE會優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型板惑。
分析select emp_name form employee where salary > 3000 在此語句中若salary是Float類型的橄镜，則優(yōu)化器對其進(jìn)行優(yōu)化為Convert(float,3000)，因?yàn)?000是個整數(shù)冯乘，我們應(yīng)在編程時使用3000.0而不要等運(yùn)行時讓DBMS進(jìn)行轉(zhuǎn)化洽胶。同樣字符和整型數(shù)據(jù)的轉(zhuǎn)換。
（31） 需要當(dāng)心的WHERE子句:
某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子.
在下面的例子里, (1)‘!=' 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中. (2) ‘ | |'是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引. (3) ‘+'是數(shù)學(xué)函數(shù). 就象其他數(shù)學(xué)函數(shù)那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.
（32） a. 如果檢索數(shù)據(jù)量超過30%的表中記錄數(shù).使用索引將沒有顯著的效率提高. b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數(shù)量級上的區(qū)別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!
（33） 避免使用耗費(fèi)資源的操作：
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費(fèi)資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強(qiáng)裆馒。
（34） 優(yōu)化GROUP BY：
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結(jié)果但第二個明顯就快了許多.
低效:
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
轉(zhuǎn)自：http://my.oschina.net/xianggao/blog/87216
另有參考 http://my.oschina.net/xianggao/blog/87448 數(shù)據(jù)庫性能優(yōu)化之SQL語句優(yōu)化2
http://my.oschina.net/xianggao/blog/87450 數(shù)據(jù)庫性能優(yōu)化之SQL語句優(yōu)化3
http://my.oschina.net/xianggao/blog/87453 數(shù)據(jù)庫性能優(yōu)化之SQL語句優(yōu)化4
http://my.oschina.net/xianggao/blog/87223 關(guān)于如何形成一個好的數(shù)據(jù)庫設(shè)計
SQL性能優(yōu)化二
-
優(yōu)化目標(biāo)
- 減少 IO 次數(shù)
IO永遠(yuǎn)是數(shù)據(jù)庫最容易瓶頸的地方姊氓，這是由數(shù)據(jù)庫的職責(zé)所決定的丐怯，大部分?jǐn)?shù)據(jù)庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮翔横，當(dāng)然读跷，也是收效最明顯的優(yōu)化手段。 - 降低 CPU 計算
除了 IO 瓶頸之外禾唁，SQL優(yōu)化中需要考慮的就是 CPU 運(yùn)算量的優(yōu)化了效览。order by, group by,distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理內(nèi)存中的數(shù)據(jù)比較運(yùn)算）。當(dāng)我們的 IO 優(yōu)化做到一定階段之后蟀俊，降低 CPU 計算也就成為了我們 SQL 優(yōu)化的重要目標(biāo)* 優(yōu)化方法 - 改變 SQL 執(zhí)行計劃
明確了優(yōu)化目標(biāo)之后钦铺，我們需要確定達(dá)到我們目標(biāo)的方法。對于 SQL 語句來說肢预，達(dá)到上述2個目標(biāo)的方法其實(shí)只有一個矛洞，那就是改變 SQL 的執(zhí)行計劃，讓他盡量“少走彎路”烫映，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)沼本，以達(dá)到 “減少 IO 次數(shù)” 和 “降低 CPU 計算” 的目標(biāo)* 常見誤區(qū) - count(1)和count(primary_key) 優(yōu)于 count()
很多人為了統(tǒng)計記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count() 锭沟，他們認(rèn)為這樣性能更好抽兆，其實(shí)這是一個誤區(qū)。對于有些場景族淮，這樣做可能性能會更差辫红，應(yīng)為數(shù)據(jù)庫對 count(*) 計數(shù)操作做了一些特別的優(yōu)化。 - count(column) 和 count() 是一樣的
這個誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在祝辣，很多人都會認(rèn)為這是理所當(dāng)然的贴妻。實(shí)際上，count(column) 和 count() 是一個完全不一樣的操作蝙斜，所代表的意義也完全不一樣名惩。
count(column) 是表示結(jié)果集中有多少個column字段不為空的記錄
count(*) 是表示整個結(jié)果集有多少條記錄 - select a,b from … 比 select a,b,c from … 可以讓數(shù)據(jù)庫訪問更少的數(shù)據(jù)量
這個誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據(jù)庫的存儲原理不是太了解孕荠。
實(shí)際上娩鹉，大多數(shù)關(guān)系型數(shù)據(jù)庫都是按照行（row）的方式存儲，而數(shù)據(jù)存取操作都是以一個固定大小的IO單元（被稱作 block 或者 page）為單位稚伍，一般為4KB弯予，8KB… 大多數(shù)時候，每個IO單元中存儲了多行槐瑞，每行都是存儲了該行的所有字段（lob等特殊類型字段除外）熙涤。
所以，我們是取一個字段還是多個字段，實(shí)際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實(shí)是一樣的祠挫。
當(dāng)然那槽，也有例外情況，那就是我們的這個查詢在索引中就可以完成等舔，也就是說當(dāng)只取 a,b兩個字段的時候骚灸，不需要回表，而c這個字段不在使用的索引中慌植，需要回表取得其數(shù)據(jù)甚牲。在這樣的情況下，二者的IO量會有較大差異蝶柿。 - order by 一定需要排序操作
我們知道索引數(shù)據(jù)實(shí)際上是有序的丈钙，如果我們的需要的數(shù)據(jù)和某個索引的順序一致，而且我們的查詢又通過這個索引來執(zhí)行交汤，那么數(shù)據(jù)庫一般會省略排序操作雏赦，而直接將數(shù)據(jù)返回，因?yàn)閿?shù)據(jù)庫知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了芙扎。
實(shí)際上星岗，利用索引來優(yōu)化有排序需求的 SQL，是一個非常重要的優(yōu)化手段
延伸閱讀：MySQL ORDER BY 的實(shí)現(xiàn)分析 戒洼，MySQL 中 GROUP BY 基本實(shí)現(xiàn)原理 以及 MySQL DISTINCT 的基本實(shí)現(xiàn)原理 這3篇文章中有更為深入的分析俏橘，尤其是第一篇 - 執(zhí)行計劃中有 filesort 就會進(jìn)行磁盤文件排序
有這個誤區(qū)其實(shí)并不能怪我們，而是因?yàn)?MySQL 開發(fā)者在用詞方面的問題圈浇。filesort 是我們在使用 explain 命令查看一條 SQL 的執(zhí)行計劃的時候可能會看到在 “Extra” 一列顯示的信息寥掐。
實(shí)際上，只要一條 SQL 語句需要進(jìn)行排序操作磷蜀，都會顯示“Using filesort”曹仗，這并不表示就會有文件排序操作。
延伸閱讀：理解 MySQL Explain 命令輸出中的filesort蠕搜，我在這里有更為詳細(xì)的介紹* 基本原則 - 盡量少 join
MySQL 的優(yōu)勢在于簡單，但這在某些方面其實(shí)也是其劣勢收壕。MySQL 優(yōu)化器效率高妓灌，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多蜜宪。對于復(fù)雜的多表 Join虫埂，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠圃验，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離掉伏。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。 - 盡量少排序
排序操作會消耗較多的 CPU 資源斧散，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應(yīng)時間供常。
對于MySQL來說，減少排序有多種辦法鸡捐，比如：- 上面誤區(qū)中提到的通過利用索引來排序的方式進(jìn)行優(yōu)化
- 減少參與排序的記錄條數(shù)
- 非必要不對數(shù)據(jù)進(jìn)行排序
- …
- 盡量避免 select *
很多人看到這一點(diǎn)后覺得比較難理解栈暇，上面不是在誤區(qū)中剛剛說 select 子句中字段的多少并不會影響到讀取的數(shù)據(jù)嗎？
是的箍镜，大多數(shù)時候并不會影響到 IO 量源祈，但是當(dāng)我們還存在 order by 操作的時候，select 子句中的字段多少會在很大程度上影響到我們的排序效率色迂，這一點(diǎn)可以通過我之前一篇介紹 MySQL ORDER BY 的實(shí)現(xiàn)分析 的文章中有較為詳細(xì)的介紹香缺。
此外，上面誤區(qū)中不是也說了歇僧，只是大多數(shù)時候是不會影響到 IO 量图张，當(dāng)我們的查詢結(jié)果僅僅只需要在索引中就能找到的時候，還是會極大減少 IO 量的馏慨。 - 盡量用 join 代替子查詢
雖然 Join 性能并不佳埂淮，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計劃一直存在較大的問題写隶，雖然這個問題已經(jīng)存在多年北救，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善牵祟。雖然官方也在很早就承認(rèn)這一問題攻旦，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題冕房。 - 盡量少 or
當(dāng) where 子句中存在多個條件以“或”并存的時候躏啰，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式耙册，造成了其性能比較低下给僵，很多時候使用 union all 或者是union（必要的時候）的方式來代替“or”會得到更好的效果。 - 盡量用 union all 代替 union
union 和 union all 的差異主要是前者需要將兩個（或者多個）結(jié)果集合并后再進(jìn)行唯一性過濾操作详拙，這就會涉及到排序帝际，增加大量的 CPU 運(yùn)算，加大資源消耗及延遲饶辙。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時候蹲诀，盡量使用 union all 而不是 union。 - 盡量早過濾
這一優(yōu)化策略其實(shí)最常見于索引的優(yōu)化設(shè)計中（將過濾性更好的字段放得更靠前）弃揽。
在 SQL 編寫中同樣可以使用這一原則來優(yōu)化一些 Join 的 SQL脯爪。比如我們在多個表進(jìn)行分頁數(shù)據(jù)查詢的時候则北，我們最好是能夠在一個表上先過濾好數(shù)據(jù)分好頁，然后再用分好頁的結(jié)果集與另外的表 Join痕慢，這樣可以盡可能多的減少不必要的 IO 操作尚揣，大大節(jié)省 IO 操作所消耗的時間。 - 避免類型轉(zhuǎn)換
這里所說的“類型轉(zhuǎn)換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換：- 人為在column_name 上通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
直接導(dǎo)致 MySQL（實(shí)際上其他數(shù)據(jù)庫也會有同樣的問題）無法使用索引守屉，如果非要轉(zhuǎn)換惑艇，應(yīng)該在傳入的參數(shù)上進(jìn)行轉(zhuǎn)換 - 由數(shù)據(jù)庫自己進(jìn)行轉(zhuǎn)換
如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時我們又沒有做任何類型轉(zhuǎn)換處理拇泛，MySQL 可能會自己對我們的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作滨巴，也可能不進(jìn)行處理而交由存儲引擎去處理，這樣一來俺叭，就會出現(xiàn)索引無法使用的情況而造成執(zhí)行計劃問題恭取。
- 人為在column_name 上通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
- 優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL
對于破壞性來說熄守，高并發(fā)的 SQL 總是會比低頻率的來得大蜈垮，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們?nèi)魏未⒌臋C(jī)會就會將系統(tǒng)壓跨裕照。而對于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL攒发，由于頻率低，即使遇到晋南，最多就是讓整個系統(tǒng)響應(yīng)慢一點(diǎn)惠猿，但至少可能撐一會兒，讓我們有緩沖的機(jī)會负间。 - 從全局出發(fā)優(yōu)化偶妖，而不是片面調(diào)整
SQL 優(yōu)化不能是單獨(dú)針對某一個進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL政溃，尤其是在通過調(diào)整索引優(yōu)化 SQL 的執(zhí)行計劃的時候趾访，千萬不能顧此失彼，因小失大董虱。 - 盡可能對每一條運(yùn)行在數(shù)據(jù)庫中的SQL進(jìn)行 explain
優(yōu)化 SQL扼鞋，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計劃才能判斷是否有優(yōu)化余地愤诱，才能判斷是否存在執(zhí)行計劃問題藏鹊。在對數(shù)據(jù)庫中運(yùn)行的 SQL 進(jìn)行了一段時間的優(yōu)化之后，很明顯的問題 SQL 可能已經(jīng)很少了转锈，大多都需要去發(fā)掘，這時候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計劃楚殿，并判斷是否需要進(jìn)行優(yōu)化
Mysql性能優(yōu)化之引擎的選擇
MySQL 的存儲引擎可能是所有關(guān)系型數(shù)據(jù)庫產(chǎn)品中最具有特色的了撮慨，不僅可以同時使用多種存儲引擎竿痰，而且每種存儲引擎和MySQL之間使用插件方式這種非常松的耦合關(guān)系。
由于各存儲引擎功能特性差異較大砌溺，這篇文章主要是介紹如何來選擇合適的存儲引擎來應(yīng)對不同的業(yè)務(wù)場景影涉。
- MyISAM
- 特性
- 不支持事務(wù)：MyISAM存儲引擎不支持事務(wù)，所以對事務(wù)有要求的業(yè)務(wù)場景不能使用
- 表級鎖定：其鎖定機(jī)制是表級索引规伐，這雖然可以讓鎖定的實(shí)現(xiàn)成本很小但是也同時大大降低了其并發(fā)性能
- 讀寫互相阻塞：不僅會在寫入的時候阻塞讀取蟹倾，MyISAM還會在讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀
- 只會緩存索引：MyISAM可以通過key_buffer緩存以大大提高訪問性能減少磁盤IO猖闪，但是這個緩存區(qū)只會緩存索引鲜棠，而不會緩存數(shù)據(jù)
- 適用場景
- 不需要事務(wù)支持（不支持）
- 并發(fā)相對較低（鎖定機(jī)制問題）
- 數(shù)據(jù)修改相對較少（阻塞問題）
- 以讀為主
- 數(shù)據(jù)一致性要求不是非常高
- 最佳實(shí)踐
- 盡量索引（緩存機(jī)制）
- 調(diào)整讀寫優(yōu)先級，根據(jù)實(shí)際需求確保重要操作更優(yōu)先
- 啟用延遲插入改善大批量寫入性能
- 盡量順序操作讓insert數(shù)據(jù)都寫入到尾部培慌，減少阻塞
- 分解大的操作豁陆，降低單個操作的阻塞時間
- 降低并發(fā)數(shù)，某些高并發(fā)場景通過應(yīng)用來進(jìn)行排隊機(jī)制
- 對于相對靜態(tài)的數(shù)據(jù)吵护，充分利用Query Cache可以極大的提高訪問效率
- MyISAM的Count只有在全表掃描的時候特別高效盒音，帶有其他條件的count都需要進(jìn)行實(shí)際的數(shù)據(jù)訪問
- 特性
- InnoDB
- 特性
- 具有較好的事務(wù)支持：支持4個事務(wù)隔離級別，支持多版本讀
- 行級鎖定：通過索引實(shí)現(xiàn)馅而，全表掃描仍然會是表鎖祥诽，注意間隙鎖的影響
- 讀寫阻塞與事務(wù)隔離級別相關(guān)
- 具有非常高效的緩存特性：能緩存索引，也能緩存數(shù)據(jù)
- 整個表和主鍵以Cluster方式存儲瓮恭，組成一顆平衡樹
- 所有Secondary Index都會保存主鍵信息
- 適用場景
- 需要事務(wù)支持（具有較好的事務(wù)特性）
- 行級鎖定對高并發(fā)有很好的適應(yīng)能力雄坪，但需要確保查詢是通過索引完成
- 數(shù)據(jù)更新較為頻繁的場景
- 數(shù)據(jù)一致性要求較高
- 硬件設(shè)備內(nèi)存較大，可以利用InnoDB較好的緩存能力來提高內(nèi)存利用率偎血，盡可能減少磁盤 IO
- 最佳實(shí)踐
- 主鍵盡可能小诸衔，避免給Secondary index帶來過大的空間負(fù)擔(dān)
- 避免全表掃描，因?yàn)闀褂帽礞i
- 盡可能緩存所有的索引和數(shù)據(jù)颇玷，提高響應(yīng)速度
- 在大批量小插入的時候笨农，盡量自己控制事務(wù)而不要使用autocommit自動提交
- 合理設(shè)置innodb_flush_log_at_trx_commit參數(shù)值，不要過度追求安全性
- 避免主鍵更新帖渠，因?yàn)檫@會帶來大量的數(shù)據(jù)移動
- 特性
- NDBCluster
- 特性
- 分布式：分布式存儲引擎谒亦，可以由多個NDBCluster存儲引擎組成集群分別存放整體數(shù)據(jù)的一部分
- 支持事務(wù)：和Innodb一樣，支持事務(wù)
- 可與mysqld不在一臺主機(jī)：可以和mysqld分開存在于獨(dú)立的主機(jī)上空郊，然后通過網(wǎng)絡(luò)和mysqld通信交互
- 內(nèi)存需求量巨大：新版本索引以及被索引的數(shù)據(jù)必須存放在內(nèi)存中份招，老版本所有數(shù)據(jù)和索引必須存在與內(nèi)存中
- 適用場景
- 具有非常高的并發(fā)需求
- 對單個請求的響應(yīng)并不是非常的critical
- 查詢簡單，過濾條件較為固定狞甚，每次請求數(shù)據(jù)量較少锁摔，又不希望自己進(jìn)行水平Sharding
- 最佳實(shí)踐
- 盡可能讓查詢簡單，避免數(shù)據(jù)的跨節(jié)點(diǎn)傳輸
- 盡可能滿足SQL節(jié)點(diǎn)的計算性能哼审，大一點(diǎn)的集群SQL節(jié)點(diǎn)會明顯多余Data節(jié)點(diǎn)
- 在各節(jié)點(diǎn)之間盡可能使用萬兆網(wǎng)絡(luò)環(huán)境互聯(lián)谐腰，以減少數(shù)據(jù)在網(wǎng)絡(luò)層傳輸過程中的延時
- 特性
Mysql性能優(yōu)化 --- 包含SQL孕豹、表結(jié)構(gòu)、索引和緩存
- 優(yōu)化目標(biāo)
- 減少 IO 次數(shù)
IO永遠(yuǎn)是數(shù)據(jù)庫最容易瓶頸的地方十气，這是由數(shù)據(jù)庫的職責(zé)所決定的励背，大部分?jǐn)?shù)據(jù)庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮砸西，當(dāng)然叶眉，也是收效最明顯的優(yōu)化手段。 - 降低 CPU 計算
除了 IO 瓶頸之外芹枷，SQL優(yōu)化中需要考慮的就是 CPU 運(yùn)算量的優(yōu)化了衅疙。order by, group by,distinct … 都是消耗 CPU 的大戶（這些操作基本上都是 CPU 處理內(nèi)存中的數(shù)據(jù)比較運(yùn)算）。當(dāng)我們的 IO 優(yōu)化做到一定階段之后杖狼，降低 CPU 計算也就成為了我們 SQL 優(yōu)化的重要目標(biāo)
- 減少 IO 次數(shù)
- 優(yōu)化方法
- 改變 SQL 執(zhí)行計劃
明確了優(yōu)化目標(biāo)之后炼蛤，我們需要確定達(dá)到我們目標(biāo)的方法。對于 SQL 語句來說蝶涩，達(dá)到上述2個目標(biāo)的方法其實(shí)只有一個理朋，那就是改變 SQL 的執(zhí)行計劃，讓他盡量“少走彎路”绿聘，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)嗽上，以達(dá)到 “減少 IO 次數(shù)” 和 “降低 CPU 計算” 的目標(biāo)
- 改變 SQL 執(zhí)行計劃
- 常見誤區(qū)
- count(1)和count(primary_key) 優(yōu)于 count()
很多人為了統(tǒng)計記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count() 熄攘，他們認(rèn)為這樣性能更好兽愤，其實(shí)這是一個誤區(qū)。對于有些場景挪圾，這樣做可能性能會更差浅萧，應(yīng)為數(shù)據(jù)庫對 count(*) 計數(shù)操作做了一些特別的優(yōu)化。 - count(column) 和 count() 是一樣的
這個誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在哲思，很多人都會認(rèn)為這是理所當(dāng)然的洼畅。實(shí)際上，count(column) 和 count() 是一個完全不一樣的操作棚赔，所代表的意義也完全不一樣帝簇。
count(column) 是表示結(jié)果集中有多少個column字段不為空的記錄
count(*) 是表示整個結(jié)果集有多少條記錄 - select a,b from … 比 select a,b,c from … 可以讓數(shù)據(jù)庫訪問更少的數(shù)據(jù)量
這個誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據(jù)庫的存儲原理不是太了解靠益。
實(shí)際上丧肴，大多數(shù)關(guān)系型數(shù)據(jù)庫都是按照行（row）的方式存儲，而數(shù)據(jù)存取操作都是以一個固定大小的IO單元（被稱作 block 或者 page）為單位胧后，一般為4KB芋浮，8KB… 大多數(shù)時候，每個IO單元中存儲了多行壳快，每行都是存儲了該行的所有字段（lob等特殊類型字段除外）纸巷。
所以江醇，我們是取一個字段還是多個字段，實(shí)際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實(shí)是一樣的何暇。
當(dāng)然，也有例外情況凛驮，那就是我們的這個查詢在索引中就可以完成裆站，也就是說當(dāng)只取 a,b兩個字段的時候，不需要回表黔夭，而c這個字段不在使用的索引中宏胯，需要回表取得其數(shù)據(jù)。在這樣的情況下本姥，二者的IO量會有較大差異肩袍。 - order by 一定需要排序操作
我們知道索引數(shù)據(jù)實(shí)際上是有序的，如果我們的需要的數(shù)據(jù)和某個索引的順序一致婚惫，而且我們的查詢又通過這個索引來執(zhí)行氛赐，那么數(shù)據(jù)庫一般會省略排序操作，而直接將數(shù)據(jù)返回先舷，因?yàn)閿?shù)據(jù)庫知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了艰管。
實(shí)際上，利用索引來優(yōu)化有排序需求的 SQL蒋川，是一個非常重要的優(yōu)化手段
延伸閱讀：MySQL ORDER BY 的實(shí)現(xiàn)分析 牲芋，MySQL 中 GROUP BY 基本實(shí)現(xiàn)原理 以及 MySQL DISTINCT 的基本實(shí)現(xiàn)原理 這3篇文章中有更為深入的分析，尤其是第一篇 - 執(zhí)行計劃中有 filesort 就會進(jìn)行磁盤文件排序
有這個誤區(qū)其實(shí)并不能怪我們捺球，而是因?yàn)?MySQL 開發(fā)者在用詞方面的問題缸浦。filesort 是我們在使用 explain 命令查看一條 SQL 的執(zhí)行計劃的時候可能會看到在 “Extra” 一列顯示的信息。
實(shí)際上氮兵，只要一條 SQL 語句需要進(jìn)行排序操作裂逐，都會顯示“Using filesort”，這并不表示就會有文件排序操作胆剧。
延伸閱讀：理解 MySQL Explain 命令輸出中的filesort絮姆，我在這里有更為詳細(xì)的介紹
- count(1)和count(primary_key) 優(yōu)于 count()
- 基本原則
- 盡量少 join
MySQL 的優(yōu)勢在于簡單，但這在某些方面其實(shí)也是其劣勢秩霍。MySQL 優(yōu)化器效率高篙悯，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多铃绒。對于復(fù)雜的多表 Join鸽照，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠颠悬，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離矮燎。但如果是簡單的單表查詢定血，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。 - 盡量少排序
排序操作會消耗較多的 CPU 資源诞外，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應(yīng)時間澜沟。
對于MySQL來說，減少排序有多種辦法峡谊，比如：- 上面誤區(qū)中提到的通過利用索引來排序的方式進(jìn)行優(yōu)化
- 減少參與排序的記錄條數(shù)
- 非必要不對數(shù)據(jù)進(jìn)行排序
- 避免使用耗費(fèi)資源的操作茫虽，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎 執(zhí)行，耗費(fèi)資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序
- …
- 盡量避免 select *
很多人看到這一點(diǎn)后覺得比較難理解既们，上面不是在誤區(qū)中剛剛說 select 子句中字段的多少并不會影響到讀取的數(shù)據(jù)嗎濒析？
是的，大多數(shù)時候并不會影響到 IO 量啥纸，但是當(dāng)我們還存在 order by 操作的時候号杏，select 子句中的字段多少會在很大程度上影響到我們的排序效率，這一點(diǎn)可以通過我之前一篇介紹 MySQL ORDER BY 的實(shí)現(xiàn)分析 的文章中有較為詳細(xì)的介紹斯棒。
此外盾致，上面誤區(qū)中不是也說了，只是大多數(shù)時候是不會影響到 IO 量名船，當(dāng)我們的查詢結(jié)果僅僅只需要在索引中就能找到的時候绰上，還是會極大減少 IO 量的。 - 盡量用 join 代替子查詢
雖然 Join 性能并不佳渠驼，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢蜈块。MySQL 的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經(jīng)存在多年迷扇，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在百揭，一直沒有太大改善。雖然官方也在很早就承認(rèn)這一問題蜓席，并且承諾盡快解決器一，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。 - 盡量少 or
當(dāng) where 子句中存在多個條件以“或”并存的時候厨内，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題祈秕，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式，造成了其性能比較低下雏胃，很多時候使用 union all 或者是union（必要的時候）的方式來代替“or”會得到更好的效果请毛。 - 盡量用 union all 代替 union
union 和 union all 的差異主要是前者需要將兩個（或者多個）結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會涉及到排序瞭亮，增加大量的 CPU 運(yùn)算方仿，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時候，盡量使用 union all 而不是 union仙蚜。 - 盡量早過濾
這一優(yōu)化策略其實(shí)最常見于索引的優(yōu)化設(shè)計中（將過濾性更好的字段放得更靠前）此洲。
在 SQL 編寫中同樣可以使用這一原則來優(yōu)化一些 Join 的 SQL。比如我們在多個表進(jìn)行分頁數(shù)據(jù)查詢的時候委粉，我們最好是能夠在一個表上先過濾好數(shù)據(jù)分好頁呜师，然后再用分好頁的結(jié)果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作贾节，大大節(jié)省 IO 操作所消耗的時間匣掸。 - 避免類型轉(zhuǎn)換
這里所說的“類型轉(zhuǎn)換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉(zhuǎn)換：人為在column_name 上通過轉(zhuǎn)換函數(shù)進(jìn)行轉(zhuǎn)換
直接導(dǎo)致 MySQL（實(shí)際上其他數(shù)據(jù)庫也會有同樣的問題）無法使用索引，如果非要轉(zhuǎn)換氮双，應(yīng)該在傳入的參數(shù)上進(jìn)行轉(zhuǎn)換-
|
<pre style="white-space:pre-wrap; word-wrap:break-word">SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369</pre>|
由數(shù)據(jù)庫自己進(jìn)行轉(zhuǎn)換
如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時我們又沒有做任何類型轉(zhuǎn)換處理霎匈，MySQL 可能會自己對我們的數(shù)據(jù)進(jìn)行類型轉(zhuǎn)換操作戴差，也可能不進(jìn)行處理而交由存儲引擎去處理，這樣一來铛嘱，就會出現(xiàn)索引無法使用的情況而造成執(zhí)行計劃問題暖释。
- 優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL
對于破壞性來說墨吓，高并發(fā)的 SQL 總是會比低頻率的來得大球匕，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們?nèi)魏未⒌臋C(jī)會就會將系統(tǒng)壓跨帖烘。而對于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL亮曹，由于頻率低，即使遇到秘症，最多就是讓整個系統(tǒng)響應(yīng)慢一點(diǎn)照卦，但至少可能撐一會兒，讓我們有緩沖的機(jī)會乡摹。 - 從全局出發(fā)優(yōu)化役耕，而不是片面調(diào)整
SQL 優(yōu)化不能是單獨(dú)針對某一個進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL聪廉，尤其是在通過調(diào)整索引優(yōu)化 SQL 的執(zhí)行計劃的時候瞬痘，千萬不能顧此失彼，因小失大板熊。 - 盡可能對每一條運(yùn)行在數(shù)據(jù)庫中的SQL進(jìn)行 explain
優(yōu)化 SQL框全，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計劃才能判斷是否有優(yōu)化余地邻邮，才能判斷是否存在執(zhí)行計劃問題竣况。在對數(shù)據(jù)庫中運(yùn)行的 SQL 進(jìn)行了一段時間的優(yōu)化之后，很明顯的問題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘丹泉，這時候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計劃情萤，并判斷是否需要進(jìn)行優(yōu)化。
- 盡量少 join
二摹恨、MySQL 數(shù)據(jù)庫性能優(yōu)化之表結(jié)構(gòu)
很多人都將 數(shù)據(jù)庫設(shè)計范式 作為數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計“圣經(jīng)”筋岛，認(rèn)為只要按照這個范式需求設(shè)計，就能讓設(shè)計出來的表結(jié)構(gòu)足夠優(yōu)化晒哄，既能保證性能優(yōu)異同時還能滿足擴(kuò)展性要求睁宰。殊不知，在N年前被奉為“圣經(jīng)”的數(shù)據(jù)庫設(shè)計3范式早就已經(jīng)不完全適用了寝凌。這里我整理了一些比較常見的數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計方面的優(yōu)化技巧柒傻，希望對大家有用。由于MySQL數(shù)據(jù)庫是基于行（Row）存儲的數(shù)據(jù)庫较木，而數(shù)據(jù)庫操作 IO 的時候是以 page（block）的方式红符，也就是說，如果我們每條記錄所占用的空間量減小伐债，就會使每個page中可存放的數(shù)據(jù)行數(shù)增大预侯，那么每次 IO 可訪問的行數(shù)也就增多了。反過來說峰锁，處理相同行數(shù)的數(shù)據(jù)萎馅，需要訪問的 page 就會減少伟桅，也就是 IO 操作次數(shù)降低熄浓，直接提升性能。此外妖枚，由于我們的內(nèi)存是有限的魄衅，增加每個page中存放的數(shù)據(jù)行數(shù)耍目，就等于增加每個內(nèi)存塊的緩存數(shù)據(jù)量，同時還會提升內(nèi)存換中數(shù)據(jù)命中的幾率徐绑，也就是緩存命中率邪驮。
- 數(shù)據(jù)類型選擇
數(shù)據(jù)庫操作中最為耗時的操作就是 IO 處理，大部分?jǐn)?shù)據(jù)庫操作 90% 以上的時間都花在了 IO 讀寫上面傲茄。所以盡可能減少 IO 讀寫量毅访，可以在很大程度上提高數(shù)據(jù)庫操作的性能。我們無法改變數(shù)據(jù)庫中需要存儲的數(shù)據(jù)盘榨，但是我們可以在這些數(shù)據(jù)的存儲方式方面花一些心思喻粹。下面的這些關(guān)于字段類型的優(yōu)化建議主要適用于記錄條數(shù)較多，數(shù)據(jù)量較大的場景草巡，因?yàn)榫?xì)化的數(shù)據(jù)類型設(shè)置可能帶來維護(hù)成本的提高守呜，過度優(yōu)化也可能會帶來其他的問題：- 數(shù)字類型：非萬不得已不要使用DOUBLE，不僅僅只是存儲長度的問題，同時還會存在精確性的問題查乒。同樣弥喉，固定精度的小數(shù)，也不建議使用DECIMAL玛迄，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲由境，可以大大節(jié)省存儲空間，且不會帶來任何附加維護(hù)成本蓖议。對于整數(shù)的存儲虏杰，在數(shù)據(jù)量較大的情況下，建議區(qū)分開 TINYINT / INT / BIGINT 的選擇勒虾，因?yàn)槿咚加玫拇鎯臻g也有很大的差別纺阔，能確定不會使用負(fù)數(shù)的字段，建議添加unsigned定義修然。當(dāng)然州弟，如果數(shù)據(jù)量較小的數(shù)據(jù)庫，也可以不用嚴(yán)格區(qū)分三個整數(shù)類型低零。
- 字符類型：非萬不得已不要使用 TEXT 數(shù)據(jù)類型，其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理拯杠。定長字段掏婶，建議使用 CHAR 類型，不定長字段盡量使用 VARCHAR潭陪，且僅僅設(shè)定適當(dāng)?shù)淖畲箝L度雄妥，而不是非常隨意的給一個很大的最大長度限定，因?yàn)椴煌拈L度范圍依溯，MySQL也會有不一樣的存儲處理老厌。
- 時間類型：盡量使用TIMESTAMP類型，因?yàn)槠浯鎯臻g只需要 DATETIME 類型的一半黎炉。對于只需要精確到某一天的數(shù)據(jù)類型枝秤，建議使用DATE類型，因?yàn)樗拇鎯臻g只需要3個字節(jié)慷嗜，比TIMESTAMP還少淀弹。不建議通過INT類型類存儲一個unix timestamp 的值，因?yàn)檫@太不直觀庆械，會給維護(hù)帶來不必要的麻煩薇溃，同時還不會帶來任何好處。
- ENUM & SET：對于狀態(tài)字段缭乘，可以嘗試使用 ENUM 來存放沐序，因?yàn)榭梢詷O大的降低存儲空間，而且即使需要增加新的類型，只要增加于末尾策幼，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)邑时。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢？可以嘗試使用SET類型垄惧，即使存在多種屬性刁愿，同樣可以游刃有余，同時還可以節(jié)省不小的存儲空間到逊。
- LOB類型：強(qiáng)烈反對在數(shù)據(jù)庫中存放 LOB 類型數(shù)據(jù)铣口，雖然數(shù)據(jù)庫提供了這樣的功能，但這不是他所擅長的觉壶，我們更應(yīng)該讓合適的工具做他擅長的事情脑题，才能將其發(fā)揮到極致。在數(shù)據(jù)庫中存儲 LOB 數(shù)據(jù)就像讓一個多年前在學(xué)校學(xué)過一點(diǎn)Java的營銷專業(yè)人員來寫 Java 代碼一樣铜靶。
- 字符編碼
字符集直接決定了數(shù)據(jù)在MySQL中的存儲編碼方式叔遂，由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會有較大的差異，所以通過使用合適的字符集争剿，可以幫助我們盡可能減少數(shù)據(jù)量已艰，進(jìn)而減少IO操作次數(shù)。- 純拉丁字符能表示的內(nèi)容蚕苇，沒必要選擇 latin1 之外的其他字符編碼哩掺，因?yàn)檫@會節(jié)省大量的存儲空間
- 如果我們可以確定不需要存放多種語言，就沒必要非得使用UTF8或者其他UNICODE字符類型涩笤，這回造成大量的存儲空間浪費(fèi)
- MySQL的數(shù)據(jù)類型可以精確到字段嚼吞，所以當(dāng)我們需要大型數(shù)據(jù)庫中存放多字節(jié)數(shù)據(jù)的時候，可以通過對不同表不同字段使用不同的數(shù)據(jù)類型來較大程度減小數(shù)據(jù)存儲量蹬碧，進(jìn)而降低 IO 操作次數(shù)并提高緩存命中率
- 適當(dāng)拆分
有些時候舱禽，我們可能會希望將一個完整的對象對應(yīng)于一張數(shù)據(jù)庫表，這對于應(yīng)用程序開發(fā)來說是很有好的恩沽，但是有些時候可能會在性能上帶來較大的問題誊稚。當(dāng)我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時候，如果我們大部分訪問這張表的時候都不需要這個字段罗心，我們就該義無反顧的將其拆分到另外的獨(dú)立表中片吊，以減少常用數(shù)據(jù)所占用的存儲空間。這樣做的一個明顯好處就是每個數(shù)據(jù)塊中可以存儲的數(shù)據(jù)條數(shù)可以大大增加协屡，既減少物理 IO 次數(shù)俏脊，也能大大提高內(nèi)存中的緩存命中率。
上面幾點(diǎn)的優(yōu)化都是為了減少每條記錄的存儲空間大小肤晓，讓每個數(shù)據(jù)庫中能夠存儲更多的記錄條數(shù)爷贫，以達(dá)到減少 IO 操作次數(shù)认然，提高緩存命中率。下面這個優(yōu)化建議可能很多開發(fā)人員都會覺得不太理解漫萄，因?yàn)檫@是典型的反范式設(shè)計卷员，而且也和上面的幾點(diǎn)優(yōu)化建議的目標(biāo)相違背。
- 適度冗余
為什么我們要冗余腾务？這不是增加了每條數(shù)據(jù)的大小毕骡，減少了每個數(shù)據(jù)塊可存放記錄條數(shù)嗎？確實(shí)岩瘦，這樣做是會增大每條記錄的大小未巫，降低每條記錄中可存放數(shù)據(jù)的條數(shù)，但是在有些場景下我們?nèi)匀贿€是不得不這樣做：- 被頻繁引用且只能通過 Join 2張（或者更多）大表的方式才能得到的獨(dú)立小字段
這樣的場景由于每次Join僅僅只是為了取得某個小字段的值启昧，Join到的記錄又大叙凡，會造成大量不必要的 IO，完全可以通過空間換取時間的方式來優(yōu)化密末。不過握爷，冗余的同時需要確保數(shù)據(jù)的一致性不會遭到破壞，確保更新的同時冗余字段也被更新
- 被頻繁引用且只能通過 Join 2張（或者更多）大表的方式才能得到的獨(dú)立小字段
- 盡量使用 NOT NULL
NULL 類型比較特殊严里，SQL 難優(yōu)化新啼。雖然 MySQL NULL類型和 Oracle 的NULL 有差異，會進(jìn)入索引中刹碾，但如果是一個組合索引燥撞，那么這個NULL 類型的字段會極大影響整個索引的效率。此外教硫，NULL 在索引中的處理也是特殊的，也會占用額外的存放空間辆布。
很多人覺得 NULL 會節(jié)省一些空間瞬矩，所以盡量讓NULL來達(dá)到節(jié)省IO的目的，但是大部分時候這會適得其反锋玲，雖然空間上可能確實(shí)有一定節(jié)省景用，倒是帶來了很多其他的優(yōu)化問題，不但沒有將IO量省下來惭蹂，反而加大了SQL的IO量伞插。所以盡量確保 DEFAULT 值不是 NULL，也是一個很好的表結(jié)構(gòu)設(shè)計優(yōu)化習(xí)慣盾碗。
三媚污、MySQL 數(shù)據(jù)庫性能優(yōu)化之索引優(yōu)化
大家都知道索引對于數(shù)據(jù)訪問的性能有非常關(guān)鍵的作用，都知道索引可以提高數(shù)據(jù)訪問效率廷雅。為什么索引能提高數(shù)據(jù)訪問性能耗美？他會不會有“副作用”京髓？是不是索引創(chuàng)建越多，性能就越好商架？到底該如何設(shè)計索引堰怨，才能最大限度的發(fā)揮其效能？這篇文章主要是帶著上面這幾個問題來做一個簡要的分析蛇摸，同時排除了業(yè)務(wù)場景所帶來的特殊性备图，請不要糾結(jié)業(yè)務(wù)場景的影響。
- 索引為什么能提高數(shù)據(jù)訪問性能赶袄？
很多人只知道索引能夠提高數(shù)據(jù)庫的性能揽涮，但并不是特別了解其原理，其實(shí)我們可以用一個生活中的示例來理解弃鸦。我們讓一位不太懂計算機(jī)的朋友去圖書館確認(rèn)一本叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》的書是否在藏绞吁，這樣對他說：“請幫我借一本計算機(jī)類的數(shù)據(jù)庫書籍，是屬于 MySQL 數(shù)據(jù)庫范疇的唬格，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》”家破。朋友會根據(jù)所屬類別，前往存放“計算機(jī)”書籍區(qū)域的書架购岗，然后再尋找“數(shù)據(jù)庫”類存放位置汰聋，再找到一堆講述“MySQL”的書籍，最后可能發(fā)現(xiàn)目標(biāo)在藏（也可能已經(jīng)借出不在書架上）喊积。在這個過程中： “計算機(jī)”->“數(shù)據(jù)庫”->“MySQL”->“在藏”->《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》其實(shí)就是一個“根據(jù)索引查找數(shù)據(jù)”的典型案例烹困，“計算機(jī)”->“數(shù)據(jù)庫”->“MySQL”->“在藏” 就是朋友查找書籍的索引。假設(shè)沒有這個索引乾吻，那查找這本書的過程會變成怎樣呢髓梅？朋友只能從圖書館入口一個書架一個書架的“遍歷”，直到找到《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》這本書為止绎签。如果幸運(yùn)枯饿，可能在第一個書架就找到。但如果不幸呢诡必，那就慘了奢方，可能要將整個圖書館所有的書架都找一遍才能找到我們想要的這本書。注：這個例子中的“索引”是記錄在朋友大腦中的爸舒，實(shí)際上蟋字，每個圖書館都會有一個非常全的實(shí)際存在的索引系統(tǒng)（大多位于入口顯眼處），由很多個貼上了明顯標(biāo)簽的小抽屜構(gòu)成扭勉。這個索引系統(tǒng)中存放這非常齊全詳盡的索引數(shù)據(jù)鹊奖，標(biāo)識出我們需要查找的“目標(biāo)”在某個區(qū)域的某個書架上。而且每當(dāng)有新的書籍入庫涂炎，舊的書籍銷毀以及書記信息修改嫉入，都需要對索引系統(tǒng)進(jìn)行及時的修正焰盗。
下面我們通過上面這個生活中的小示例，來分析一下索引咒林，看看能的出哪些結(jié)論熬拒？
- 索引有哪些“副作用”？
- 圖書的變更（增垫竞，刪澎粟，改）都需要修訂索引，索引存在額外的維護(hù)成本
- 查找翻閱索引系統(tǒng)需要消耗時間欢瞪，索引存在額外的訪問成本
- 這個索引系統(tǒng)需要一個地方來存放活烙，索引存在額外的空間成本
- 索引是不是越多越好？
- 如果我們的這個圖書館只是一個進(jìn)出中轉(zhuǎn)站遣鼓，里面的新書進(jìn)來后很快就會轉(zhuǎn)發(fā)去其他圖書館而從這個館藏中“清除”啸盏，那我們的索引就只會不斷的修改，而很少會被用來查找圖書
所以骑祟，對于類似于這樣的存在非常大更新量的數(shù)據(jù)回懦，索引的維護(hù)成本會非常高，如果其檢索需求很少次企，而且對檢索效率并沒有非常高的要求的時候怯晕，我們并不建議創(chuàng)建索引，或者是盡量減少索引缸棵。 - 如果我們的書籍量少到只有幾本或者就只有一個書架舟茶，索引并不會帶來什么作用，甚至可能還會浪費(fèi)一些查找索引所花費(fèi)的時間堵第。
所以吧凉，對于數(shù)據(jù)量極小到通過索引檢索還不如直接遍歷來得快的數(shù)據(jù)，也并不適合使用索引踏志。 - 如果我們的圖書館只有一個10平方的面積阀捅，現(xiàn)在連放書架都已經(jīng)非常擁擠，而且館藏還在不斷增加狰贯，我們還能考慮創(chuàng)建索引嗎也搓？
所以赏廓，當(dāng)我們連存儲基礎(chǔ)數(shù)據(jù)的空間都捉襟見肘的時候涵紊，我們也應(yīng)該盡量減少低效或者是去除索引。
- 如果我們的這個圖書館只是一個進(jìn)出中轉(zhuǎn)站遣鼓，里面的新書進(jìn)來后很快就會轉(zhuǎn)發(fā)去其他圖書館而從這個館藏中“清除”啸盏，那我們的索引就只會不斷的修改，而很少會被用來查找圖書
- 索引該如何設(shè)計才高效幔摸？
如果我們僅僅只是這樣告訴對方的：“幫我確認(rèn)一本數(shù)據(jù)庫類別的講述 MySQL 的叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》的書是否在藏”摸柄，結(jié)果又會如何呢？朋友只能一個大類區(qū)域一個大類區(qū)域的去尋找“數(shù)據(jù)庫”類別既忆，然后再找到 “MySQL”范疇驱负，再看到我們所需是否在藏嗦玖。由于我們少說了一個“計算機(jī)類”，朋友就必須到每一個大類去尋找跃脊。
所以宇挫，我們應(yīng)該盡量讓查找條件盡可能多的在索引中，盡可能通過索引完成所有過濾酪术，回表只是取出額外的數(shù)據(jù)字段器瘪。如果我們是這樣說的：“幫我確認(rèn)一本講述 MySQL 的數(shù)據(jù)庫范疇的計算機(jī)叢書，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計》绘雁，看是否在藏”橡疼。如果這位朋友并不知道計算機(jī)是一個大類，也不知道數(shù)據(jù)庫屬于計算機(jī)大類庐舟，那這位朋友就悲劇了欣除。首先他得遍歷每個類別確認(rèn)“MySQL”存在于哪些類別中，然后從包含 “MySQL” 書籍中再看有哪些是“數(shù)據(jù)庫”范疇的（有可能部分是講述PHP或者其他開發(fā)語言的）挪略，然后再排除非計算機(jī)類的（雖然可能并沒有必要）历帚，然后才能確認(rèn)。
所以瘟檩，字段的順序?qū)M合索引效率有至關(guān)重要的作用抹缕，過濾效果越好的字段需要更靠前。如果我們還有這樣一個需求（雖然基本不可能）：“幫我將圖書館中所有的計算機(jī)圖書借來”墨辛。朋友如果通過索引來找卓研，每次都到索引柜找到計算機(jī)書籍所在的區(qū)域，然后從書架上搬下一格（假設(shè)只能以一格為單位從書架上取下睹簇，類比數(shù)據(jù)庫中以block/page為單位讀茸嘧浮），取出第一本太惠，然后再從索引柜找到計算機(jī)圖書所在區(qū)域磨淌，再搬下一格，取出一本… 如此往復(fù)直至取完所有的書凿渊。如果他不通過索引來找又會怎樣呢梁只？他需要從地一個書架一直往后找，當(dāng)找到計算機(jī)的書埃脏，搬下一格搪锣，取出所有計算機(jī)的書，再往后彩掐，直至所有書架全部看一遍构舟。在這個過程中，如果計算機(jī)類書籍較多堵幽，通過索引來取所花費(fèi)的時間很可能要大于直接遍歷狗超，因?yàn)椴粩嗤鶑?fù)的索引翻閱所消耗的時間會非常長弹澎。（延伸閱讀：這里有一篇以前寫的關(guān)于Oracle的文章，索引掃描還是全表掃描（Index Scan Or Full Table Scan））
所以努咐，當(dāng)我們需要讀取的數(shù)據(jù)量占整個數(shù)據(jù)量的比例較大抑或者說索引的過濾效果并不是太好的時候苦蒿，使用索引并不一定優(yōu)于全表掃描。如果我們的朋友不知道“數(shù)據(jù)庫”這個類別可以屬于“計算機(jī)”這個大類渗稍，抑或者圖書館的索引系統(tǒng)中這兩個類別屬性并沒有關(guān)聯(lián)關(guān)系刽肠，又會怎樣呢？也就是說免胃，朋友得到的是2個獨(dú)立的索引音五，一個是告知“計算機(jī)”這個大類所在的區(qū)域，一個是“數(shù)據(jù)庫”這個小類所在的區(qū)域（很可能是多個區(qū)域）羔沙，那么他只能二者選其一來搜索我的需求躺涝。即使朋友可以分別通過2個索引檢索然后自己在腦中取交集再找，那這樣的效率實(shí)際過程中也會比較低下扼雏。
所以坚嗜，在實(shí)際使用過程中，一次數(shù)據(jù)訪問一般只能利用到1個索引诗充，這一點(diǎn)在索引創(chuàng)建過程中一定要注意苍蔬，不是說一條SQL語句中Where子句里面每個條件都有索引能對應(yīng)上就可以了。-
最后總結(jié)一下法則：不要在建立的索引的數(shù)據(jù)列上進(jìn)行下列操作:
◆避免對索引字段進(jìn)行計算操作◆避免在索引字段上使用not蝴蜓，碟绑，!=◆避免在索引列上使用IS NULL和IS NOT NULL◆避免在索引列上出現(xiàn)數(shù)據(jù)類型轉(zhuǎn)換◆避免在索引字段上使用函數(shù)◆避免建立索引的列中使用空值。
四茎匠、MySQL 數(shù)據(jù)庫性能優(yōu)化之緩存參數(shù)優(yōu)化
數(shù)據(jù)庫屬于 IO 密集型的應(yīng)用程序格仲，其主要職責(zé)就是數(shù)據(jù)的管理及存儲工作。而我們知道诵冒，從內(nèi)存中讀取一個數(shù)據(jù)庫的時間是微秒級別凯肋，而從一塊普通硬盤上讀取一個IO是在毫秒級別，二者相差3個數(shù)量級汽馋。所以侮东，要優(yōu)化數(shù)據(jù)庫，首先第一步需要優(yōu)化的就是 IO豹芯，盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO悄雅。本文先從 MySQL 數(shù)據(jù)庫IO相關(guān)參數(shù)（緩存參數(shù)）的角度來看看可以通過哪些參數(shù)進(jìn)行IO優(yōu)化：
- query_cache_size/query_cache_type （global） Query cache 作用于整個 MySQL Instance，主要用來緩存 MySQL 中的 ResultSet告组，也就是一條SQL語句執(zhí)行的結(jié)果集煤伟，所以僅僅只能針對select語句癌佩。當(dāng)我們打開了 Query Cache 功能木缝，MySQL在接受到一條select語句的請求后诅炉，如果該語句滿足Query Cache的要求（未顯式說明不允許使用Query Cache呵曹，或者已經(jīng)顯式申明需要使用Query Cache），MySQL 會直接根據(jù)預(yù)先設(shè)定好的HASH算法將接受到的select語句以字符串方式進(jìn)行hash，然后到Query Cache 中直接查找是否已經(jīng)緩存秽澳。也就是說，如果已經(jīng)在緩存中烹棉，該select請求就會直接將數(shù)據(jù)返回慷蠕，從而省略了后面所有的步驟（如 SQL語句的解析，優(yōu)化器優(yōu)化以及向存儲引擎請求數(shù)據(jù)等）厘托，極大的提高性能友雳。當(dāng)然，Query Cache 也有一個致命的缺陷铅匹，那就是當(dāng)某個表的數(shù)據(jù)有任何任何變化押赊，都會導(dǎo)致所有引用了該表的select語句在Query Cache 中的緩存數(shù)據(jù)失效。所以包斑，當(dāng)我們的數(shù)據(jù)變化非常頻繁的情況下流礁，使用Query Cache 可能會得不償失。Query Cache的使用需要多個參數(shù)配合罗丰，其中最為關(guān)鍵的是 query_cache_size 和 query_cache_type 神帅，前者設(shè)置用于緩存 ResultSet 的內(nèi)存大小，后者設(shè)置在何場景下使用 Query Cache萌抵。在以往的經(jīng)驗(yàn)來看找御，如果不是用來緩存基本不變的數(shù)據(jù)的MySQL數(shù)據(jù)庫，query_cache_size 一般 256MB 是一個比較合適的大小绍填。當(dāng)然萎坷，這可以通過計算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）來進(jìn)行調(diào)整。query_cache_type可以設(shè)置為0(OFF)沐兰，1(ON)或者2(DEMOND)哆档，分別表示完全不使用query cache，除顯式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache住闯，只有顯示要求才使用query cache（使用sql_cache）瓜浸。
- binlog_cache_size （global） Binlog Cache 用于在打開了二進(jìn)制日志（binlog）記錄功能的環(huán)境，是 MySQL 用來提高binlog的記錄效率而設(shè)計的一個用于短時間內(nèi)臨時緩存binlog數(shù)據(jù)的內(nèi)存區(qū)域比原。一般來說插佛，如果我們的數(shù)據(jù)庫中沒有什么大事務(wù)，寫入也不是特別頻繁量窘，2MB～4MB是一個合適的選擇雇寇。但是如果我們的數(shù)據(jù)庫大事務(wù)較多，寫入量比較大，可與適當(dāng)調(diào)高binlog_cache_size锨侯。同時嫩海，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設(shè)置的binlog_cache_size是否足夠，是否有大量的binlog_cache由于內(nèi)存大小不夠而使用臨時文件（binlog_cache_disk_use）來緩存了囚痴。
- key_buffer_size （global） Key Buffer 可能是大家最為熟悉的一個 MySQL 緩存參數(shù)了叁怪，尤其是在 MySQL 沒有更換默認(rèn)存儲引擎的時候，很多朋友可能會發(fā)現(xiàn)深滚，默認(rèn)的 MySQL 配置文件中設(shè)置最大的一個內(nèi)存參數(shù)就是這個參數(shù)了奕谭。key_buffer_size 參數(shù)用來設(shè)置用于緩存 MyISAM存儲引擎中索引文件的內(nèi)存區(qū)域大小。如果我們有足夠的內(nèi)存痴荐，這個緩存區(qū)域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引血柳，以盡可能提高性能。此外生兆，當(dāng)我們在使用MyISAM 存儲的時候有一個及其重要的點(diǎn)需要注意混驰，由于 MyISAM 引擎的特性限制了他僅僅只會緩存索引塊到內(nèi)存中，而不會緩存表數(shù)據(jù)庫塊皂贩。所以栖榨，我們的 SQL 一定要盡可能讓過濾條件都在索引中，以便讓緩存幫助我們提高查詢效率明刷。
- bulk_insert_buffer_size （thread）和key_buffer_size一樣婴栽，這個參數(shù)同樣也僅作用于使用 MyISAM存儲引擎，用來緩存批量插入數(shù)據(jù)的時候臨時緩存寫入數(shù)據(jù)辈末。當(dāng)我們使用如下幾種數(shù)據(jù)寫入語句的時候愚争，會使用這個內(nèi)存區(qū)域來緩存批量結(jié)構(gòu)的數(shù)據(jù)以幫助批量寫入數(shù)據(jù)文件：insert … select …
insert … values (…) ,(…),(…)…
load data infile… into… (非空表) - innodb_buffer_pool_size（global）當(dāng)我們使用InnoDB存儲引擎的時候，innodb_buffer_pool_size 參數(shù)可能是影響我們性能的最為關(guān)鍵的一個參數(shù)了挤聘，他用來設(shè)置用于緩存 InnoDB 索引及數(shù)據(jù)塊的內(nèi)存區(qū)域大小轰枝，類似于 MyISAM 存儲引擎的 key_buffer_size 參數(shù)，當(dāng)然组去，可能更像是 Oracle 的 db_cache_size鞍陨。簡單來說，當(dāng)我們操作一個 InnoDB 表的時候从隆，返回的所有數(shù)據(jù)或者去數(shù)據(jù)過程中用到的任何一個索引塊诚撵，都會在這個內(nèi)存區(qū)域中走一遭。和key_buffer_size 對于 MyISAM 引擎一樣键闺，innodb_buffer_pool_size 設(shè)置了 InnoDB 存儲引擎需求最大的一塊內(nèi)存區(qū)域的大小寿烟，直接關(guān)系到 InnoDB存儲引擎的性能，所以如果我們有足夠的內(nèi)存辛燥，盡可將該參數(shù)設(shè)置到足夠打筛武，將盡可能多的 InnoDB 的索引及數(shù)據(jù)都放入到該緩存區(qū)域中缝其，直至全部。我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算緩存命中率徘六，并根據(jù)命中率來調(diào)整 innodb_buffer_pool_size 參數(shù)大小進(jìn)行優(yōu)化内边。
- innodb_additional_mem_pool_size（global）這個參數(shù)我們平時調(diào)整的可能不是太多，很多人都使用了默認(rèn)值硕噩，可能很多人都不是太熟悉這個參數(shù)的作用。innodb_additional_mem_pool_size 設(shè)置了InnoDB存儲引擎用來存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小缭贡，所以當(dāng)我們一個MySQL Instance中的數(shù)據(jù)庫對象非常多的時候炉擅，是需要適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問效率的。這個參數(shù)大小是否足夠還是比較容易知道的阳惹，因?yàn)楫?dāng)過小的時候谍失，MySQL 會記錄 Warning 信息到數(shù)據(jù)庫的 error log 中，這時候你就知道該調(diào)整這個參數(shù)大小了莹汤。
- innodb_log_buffer_size （global）這是 InnoDB 存儲引擎的事務(wù)日志所使用的緩沖區(qū)快鱼。類似于 Binlog Buffer，InnoDB 在寫事務(wù)日志的時候纲岭，為了提高性能抹竹，也是先將信息寫入 Innofb Log Buffer 中，當(dāng)滿足 innodb_flush_log_trx_commit 參數(shù)所設(shè)置的相應(yīng)條件（或者日志緩沖區(qū)寫滿）之后止潮，才會將日志寫到文件（或者同步到磁盤）中窃判。可以通過 innodb_log_buffer_size 參數(shù)設(shè)置其可以使用的最大內(nèi)存空間喇闸。
注：innodb_flush_log_trx_commit 參數(shù)對 InnoDB Log 的寫入性能有非常關(guān)鍵的影響袄琳。該參數(shù)可以設(shè)置為0，1燃乍，2唆樊，解釋如下：0：log buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到log file中，且同時會進(jìn)行文件系統(tǒng)到磁盤的同步操作刻蟹，但是每個事務(wù)的commit并不會觸發(fā)任何log buffer 到log file的刷新或者文件系統(tǒng)到磁盤的刷新操作逗旁；
1：在每次事務(wù)提交的時候?qū)og buffer 中的數(shù)據(jù)都會寫入到log file，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步舆瘪；
2：事務(wù)提交會觸發(fā)log buffer 到log file的刷新痢艺，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外介陶，每秒會有一次文件系統(tǒng)到磁盤同步操作堤舒。此外，MySQL文檔中還提到哺呜，這幾種設(shè)置中的每秒同步一次的機(jī)制舌缤，可能并不會完全確保非常準(zhǔn)確的每秒就一定會發(fā)生同步，還取決于進(jìn)程調(diào)度的問題。實(shí)際上国撵，InnoDB 能否真正滿足此參數(shù)所設(shè)置值代表的意義正常 Recovery 還是受到了不同 OS 下文件系統(tǒng)以及磁盤本身的限制陵吸，可能有些時候在并沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經(jīng)完成了磁盤同步。 - innodb_max_dirty_pages_pct （global）這個參數(shù)和上面的各個參數(shù)不同介牙，他不是用來設(shè)置用于緩存某種數(shù)據(jù)的內(nèi)存大小的一個參數(shù)壮虫，而是用來控制在 InnoDB Buffer Pool 中可以不用寫入數(shù)據(jù)文件中的Dirty Page 的比例（已經(jīng)被修但還沒有從內(nèi)存中寫入到數(shù)據(jù)文件的臟數(shù)據(jù)）。這個比例值越大环础，從內(nèi)存到磁盤的寫入操作就會相對減少囚似，所以能夠一定程度下減少寫入操作的磁盤IO。但是线得，如果這個比例值過大饶唤，當(dāng)數(shù)據(jù)庫 Crash 之后重啟的時間可能就會很長，因?yàn)闀写罅康氖聞?wù)數(shù)據(jù)需要從日志文件恢復(fù)出來寫入數(shù)據(jù)文件中贯钩。同時募狂，過大的比例值同時可能也會造成在達(dá)到比例設(shè)定上限后的 flush 操作“過猛”而導(dǎo)致性能波動很大。
上面這幾個參數(shù)是 MySQL 中為了減少磁盤物理IO而設(shè)計的主要參數(shù)角雷，對 MySQL 的性能起到了至關(guān)重要的作用祸穷。
—EOF—
按照 mcsrainbow 朋友的要求，這里列一下根據(jù)以往經(jīng)驗(yàn)得到的相關(guān)參數(shù)的建議值：
- query_cache_type : 如果全部使用innodb存儲引擎勺三，建議為0粱哼，如果使用MyISAM 存儲引擎，建議為2檩咱，同時在SQL語句中顯式控制是否是喲你gquery cache
- query_cache_size: 根據(jù) 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）進(jìn)行調(diào)整揭措，一般不建議太大，256MB可能已經(jīng)差不多了刻蚯，大型的配置型靜態(tài)數(shù)據(jù)可適當(dāng)調(diào)大
- binlog_cache_size: 一般環(huán)境2MB～4MB是一個合適的選擇绊含，事務(wù)較大且寫入頻繁的數(shù)據(jù)庫環(huán)境可以適當(dāng)調(diào)大，但不建議超過32MB
- key_buffer_size: 如果不使用MyISAM存儲引擎炊汹，16MB足以躬充，用來緩存一些系統(tǒng)表信息等。如果使用 MyISAM存儲引擎讨便，在內(nèi)存允許的情況下充甚，盡可能將所有索引放入內(nèi)存，簡單來說就是“越大越好”
- bulk_insert_buffer_size: 如果經(jīng)常性的需要使用批量插入的特殊語句（上面有說明）來插入數(shù)據(jù)霸褒，可以適當(dāng)調(diào)大該參數(shù)至16MB～32MB伴找，不建議繼續(xù)增大，某人8MB
- innodb_buffer_pool_size: 如果不使用InnoDB存儲引擎废菱，可以不用調(diào)整這個參數(shù)技矮，如果需要使用抖誉，在內(nèi)存允許的情況下，盡可能將所有的InnoDB數(shù)據(jù)文件存放如內(nèi)存中衰倦，同樣將但來說也是“越大越好”
- innodb_additional_mem_pool_size: 一般的數(shù)據(jù)庫建議調(diào)整到8MB～16MB袒炉，如果表特別多，可以調(diào)整到32MB樊零，可以根據(jù)error log中的信息判斷是否需要增大
- innodb_log_buffer_size: 默認(rèn)是1MB我磁，系的如頻繁的系統(tǒng)可適當(dāng)增大至4MB～8MB。當(dāng)然如上面介紹所說驻襟，這個參數(shù)實(shí)際上還和另外的flush參數(shù)相關(guān)夺艰。一般來說不建議超過32MB
- innodb_max_dirty_pages_pct: 根據(jù)以往的經(jīng)驗(yàn)，重啟恢復(fù)的數(shù)據(jù)如果要超過1GB的話塑悼，啟動速度會比較慢劲适，幾乎難以接受楷掉，所以建議不大于 1GB/innodb_buffer_pool_size(GB)*100 這個值厢蒜。當(dāng)然，如果你能夠忍受啟動時間比較長烹植，而且希望盡量減少內(nèi)存至磁盤的flush斑鸦，可以將這個值調(diào)整到90，但不建議超過90
注：以上取值范圍僅僅只是我的根據(jù)以往遇到的數(shù)據(jù)庫場景所得到的一些優(yōu)化經(jīng)驗(yàn)值草雕，并不一定適用于所有場景巷屿，所以在實(shí)際優(yōu)化過程中還需要大家自己不斷的調(diào)整分析，也歡迎大家隨時通過 Mail 與我聯(lián)系溝通交流優(yōu)化或者是架構(gòu)方面的技術(shù)墩虹，一起探討相互學(xué)習(xí)嘱巾。
Mysql優(yōu)化總結(jié)
一、索引
1诫钓、創(chuàng)建索引：
（1）．ALTER TABLE
ALTER TABLE用來創(chuàng)建普通索引旬昭、UNIQUE索引或PRIMARY KEY索引。ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
（2）菌湃、CREATE INDEX
CREATE INDEX可對表增加普通索引或UNIQUE索引问拘。CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
2、查看索引mysql> show index from tblname;
mysql> show keys from tblname;
3惧所、刪除索引
可利用ALTER TABLE或DROP INDEX語句來刪除索引骤坐。類似于CREATE INDEX語句，DROP INDEX可以在ALTER TABLE 內(nèi)部作為一條語句處理下愈，語法如下纽绍。
DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
索引：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
**explain +select ·····用來獲取select語句的執(zhí)行的相關(guān)信息及索引的使用等
**describe table table_name;
**analyze table table_name;查看表的信息，幫助優(yōu)化
**show 查看執(zhí)行狀態(tài)二势似、my.ini中的配置
http://www.chinaz.com/program/2009/1210/100740.shtml
mysql > show status; 可以查看具體的設(shè)置 服務(wù)器的狀態(tài)
具體的配置呀什么顶岸，沒有親自試驗(yàn)過三腔彰、數(shù)據(jù)表引擎
1、MyISAM：mysql默認(rèn)的
2辖佣、InnoDB：支持事務(wù)霹抛、鎖、外鍵卷谈、聚簇索引
引擎介紹：http://blog.csdn.net/cheungjustin/article/details/5999880
http://limaolinjia.blog.163.com/blog/static/539162282011012145139/四杯拐、索引的類型：
1、B-Tree索引
2世蔗、hash索引
具體的參考還是一）五端逼、事務(wù)
數(shù)據(jù)表引擎使用InnoDB
http://www.cnblogs.com/winner/archive/2011/11/09/2242272.html六、存儲過程
經(jīng)編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中污淋，運(yùn)行效率高顶滩，可以降低客戶機(jī)和服務(wù)器之間的通信量，有利于集中控制寸爆，易于維護(hù) （P247）
http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html七礁鲁、mysql profiling（mysql性能分析器）優(yōu)化sql語句
查看SQL執(zhí)行消耗系統(tǒng)資源的信息
++++需要開啟+++
具體使用：http://www.jiunile.com/mysql-profiling%E7%9A%84%E4%BD%BF%E7%94%A8.html八、慢查詢?nèi)罩?br> ++++需要開啟++++
通過慢日志查詢可以知道哪些SQL語句執(zhí)行效率低下赁豆，那些sql語句使用的頻率高等
對MySQL查詢語句的監(jiān)控仅醇、分析、優(yōu)化是MySQL優(yōu)化非常重要的一步魔种。開啟慢查詢?nèi)罩竞笪龆捎谌罩居涗洸僮鳎谝欢ǔ潭壬蠒加肅PU資源影響mysql的性能节预，但是可以階段性開啟來定位性能瓶頸叶摄。
具體參考：http://blog.csdn.net/renzhenhuai/article/details/8839874關(guān)于mysql的一些講解：http://www.ccvita.com/category/mysql
- 減少 IO 次數(shù)
