一红淡、SQL調(diào)優(yōu)的依據(jù) - 測(cè)試

二坐儿、優(yōu)化性能的三架馬車

2.1 DDL設(shè)計(jì)優(yōu)化

整體原則是根據(jù)系統(tǒng)將要進(jìn)行的查詢來設(shè)計(jì)schema碎税。

1）選擇合適的數(shù)據(jù)類型：原則是1??選擇夠用的最小數(shù)據(jù)類型伯襟，好處是占用磁盤、內(nèi)存晃择、CPU緩存空間少冀值，處理時(shí)需要的CPU周期也少；2??優(yōu)先使用簡單類型宫屠，如整型比字符操作代價(jià)低列疗。

具體來說，1??主鍵列優(yōu)先選擇整數(shù)類型浪蹂，速度快且可使用auto increment抵栈；2??用于聯(lián)表查詢的關(guān)聯(lián)列，如film表的filmId和actor表的filmID坤次，無論是否設(shè)置外鍵古劲，推薦使用相同的類型，以避免比較操作時(shí)的類型轉(zhuǎn)換缰猴；3??盡量指定列為not null产艾，尤其是要建索引的列；如果查詢中包含可為null的列滑绒，其索引闷堡，索引統(tǒng)計(jì)，值比較更加復(fù)雜難以優(yōu)化疑故。

2）考慮DDL設(shè)計(jì)的范式與反范式：

范式的好處：1）避免冗余數(shù)據(jù)杠览；2）數(shù)據(jù)表更小可加載到內(nèi)存；3）查詢中更少的使用使用group by和distinct等開銷大的查詢焰扳；缺點(diǎn)是需要大量聯(lián)表操作倦零，通常我們需要控制單個(gè)查詢的聯(lián)表數(shù)目不超過12個(gè)表误续，想起來太陽公司的extract customer大sql吨悍。

反范式的好處：1）避免聯(lián)表；2）更有效的索引策略蹋嵌，如select msgContent from msg join user on userID where user.type=‘vip’ order by msg.published desc limit 10育瓜，索引是msg.published。執(zhí)行計(jì)劃是掃描msg.published索引栽烂，對(duì)每條msg數(shù)據(jù)去user表查看是否vip用戶躏仇，如果vip用戶少則效率低下恋脚；如果是一張表，則用(published, usertype)作為索引可以提升查詢效率焰手。缺點(diǎn)是：1）數(shù)據(jù)冗余糟描；2）數(shù)據(jù)表更大，通常我們需要控制列數(shù)不能達(dá)到數(shù)百列书妻，因?yàn)榉?wù)層和引擎層之間通過‘行緩沖‘拷貝數(shù)據(jù)船响，服務(wù)層把行數(shù)據(jù)解碼成各個(gè)列，列越多則開銷越大躲履。

2.2 索引設(shè)計(jì)優(yōu)化

索引可以提高查詢见间、排序操作的效率。

1）作為開發(fā)工猜，理解什么樣的查詢可以應(yīng)用已有索引米诉。設(shè)計(jì)索引的時(shí)候需要考慮后續(xù)的查詢；后續(xù)設(shè)計(jì)查詢的時(shí)候也需要考慮應(yīng)用已有的索引篷帅。就是兩邊都一起努力史侣，希望盡可能多的查詢操作能夠通過索引完成。

2）Mysql本身魏身，提供自適應(yīng)哈希索引抵窒、聚簇索引、覆蓋索引進(jìn)一步提高查詢效率叠骑。

索引設(shè)計(jì)的注意事項(xiàng)

1）多列索引優(yōu)于多個(gè)單列索引：不建議為每個(gè)列單獨(dú)創(chuàng)建索引李皇，例如對(duì)姓/名/生日分別建立索引，當(dāng)根據(jù)姓/名查詢時(shí)宙枷，mysql同時(shí)使用這兩個(gè)單列索引進(jìn)行掃描掉房，并將結(jié)果合并，合并算法包括與/或慰丛，稱為索引合并卓囚，索引合并也是索引設(shè)計(jì)的壞味道。

2）合理的索引列順序：通常將選擇性更強(qiáng)的列放在前面诅病，將用于范圍查詢的列放在后面哪亿。索引的選擇越強(qiáng)則索引查詢效率越高。索引的選擇性指不重復(fù)的索引值和數(shù)據(jù)表總量的比值贤笆，主鍵索引的選擇性最強(qiáng)蝇棉；如果查詢條件中的索引值(如null)搜索出1萬多條數(shù)據(jù)，就是典型的選擇性差芥永，此時(shí)索引查詢對(duì)于讀操作的效率提升幫助較写垡蟆；再比如埋涧，未登錄用戶的用戶名均為guest板辽，涉及guest用戶的查詢與正常用戶查詢性能相距甚遠(yuǎn)奇瘦。

索引維護(hù)的注意事項(xiàng)

1）刪除重復(fù)索引和冗余索引：重復(fù)索引是在相同的列上按照相同順序創(chuàng)建的相同類型索引，如索引(A)和索引(A)劲弦。索引(A,B)和索引(A)是冗余索引耳标，因?yàn)楹笳呤乔罢叩淖箧I索引。

2）刪除未使用索引

3）減少索引碎片：B+Tree的葉子節(jié)點(diǎn)的物理分布不是連續(xù)的邑跪，InnoDB提供添加/刪除索引功能麻捻，可以通過先刪除，再創(chuàng)建的方式消除索引的碎片化呀袱。

2.3 查詢語句優(yōu)化

2.3.1 時(shí)間都去哪兒了贸毕？

性能是完成某任務(wù)的時(shí)間度量，也就是響應(yīng)時(shí)間夜赵；優(yōu)化查詢性能就是提高查詢的響應(yīng)速度明棍。響應(yīng)時(shí)間包括執(zhí)行時(shí)間和等待時(shí)間，等待時(shí)間又包括等待IO和等待鎖的時(shí)間寇僧。那么查詢的時(shí)間都花在哪兒了呢摊腋？我們可以通過show full processlist查看線程狀態(tài)進(jìn)而查看查詢的生命周期：

1）Sleep：等待客戶端發(fā)送請(qǐng)求；2）Query：正在執(zhí)行查詢嘁傀，或者正在返回結(jié)果給客戶端兴蒸；3）Locked：在服務(wù)器層等待表鎖，等待InnoDB的行鎖并不會(huì)在此顯示细办；4）analysing & statistices：正在收集存儲(chǔ)引擎統(tǒng)計(jì)信息橙凳，生成執(zhí)行計(jì)劃；5）Copying to temp table(on disk)：正在執(zhí)行查詢并把結(jié)果復(fù)制到臨時(shí)表笑撞，在group by岛啸、文件排序和union等操作出現(xiàn)；6）sorting result：正在排序茴肥；7）Sending data：在多個(gè)狀態(tài)間傳送數(shù)據(jù)坚踩，或者正在生成結(jié)果集，或者向客戶端返回?cái)?shù)據(jù)瓤狐；

2.3.2?查詢優(yōu)化的思路

1）客戶端是否向數(shù)據(jù)庫請(qǐng)求了不需要的數(shù)據(jù)：1. 可使用limit減少返回的行瞬铸；2. 可通過避免使用select * 減少返回的列，但有時(shí)select * 配合緩存總體性能也不錯(cuò)础锐；3. 通過緩存避免重復(fù)查詢相同的數(shù)據(jù)嗓节。

2）通過日志中記錄的掃描行數(shù)和返回行數(shù)，查看服務(wù)端是否掃描了不需要的數(shù)據(jù)郁稍。理想情況下赦政，掃描行數(shù)等于返回行數(shù)胜宇；但聯(lián)表查詢時(shí)掃碼多行才能連結(jié)為一行返回耀怜，掃描行數(shù)會(huì)明顯大于返回行數(shù)恢着。Where條件對(duì)應(yīng)的3種處理方式，掃描行數(shù)從少到多依次是：1. 索引作為查詢條件财破，在存儲(chǔ)引擎層完成掰派；2. 索引覆蓋掃描(using index)，服務(wù)層直接從索引中過濾掉不需要的數(shù)據(jù)左痢；3. 服務(wù)層過濾不滿足條件的記錄(using where)靡羡。

更多內(nèi)容詳見：http://www.reibang.com/p/b2d20d93857c

三、優(yōu)化器有所為有所不為

3.1 有所為

1）關(guān)聯(lián)表順序重排俊性，對(duì)于join略步；2）min/max函數(shù)優(yōu)化，基于B+Tree定页；3）提前終止查詢趟薄，如limit；4）in子句優(yōu)化典徊；5）表達(dá)式等價(jià)轉(zhuǎn)換杭煎；6）把子查詢優(yōu)化掉；7）將外連接轉(zhuǎn)化為內(nèi)連接：outer/inner join

3.2 有所不為

1）避免在in中包含子查詢卒落；2）優(yōu)化器不考慮并發(fā)羡铲，也無法利用多核特定來并行執(zhí)行查詢；3）當(dāng)在同一個(gè)表上查詢和更新時(shí)儡毕，通過用as生成臨時(shí)表的方式來解決也切。

四、分析工具

4.1 執(zhí)行計(jì)劃

Mysql優(yōu)化器基于成本選擇最優(yōu)執(zhí)行計(jì)劃并交給執(zhí)行引擎腰湾，執(zhí)行計(jì)劃采用指令樹的形式贾费。用戶可以用explain命令請(qǐng)求優(yōu)化器解釋優(yōu)化過程，查看生成的執(zhí)行計(jì)劃檐盟。

min/max函數(shù)優(yōu)化：1. 能夠使用索引時(shí)褂萧，通過查找B-tree的最左端/最右端優(yōu)化min/max函數(shù)；執(zhí)行計(jì)劃顯示select tables optimized away葵萎，表示優(yōu)化器已經(jīng)在執(zhí)行計(jì)劃中把該表移除导犹，用常數(shù)取代；

需要服務(wù)層進(jìn)行篩選的查詢羡忘，執(zhí)行計(jì)劃的extra顯示為using where谎痢。例如select id from user where id<5 and id <>1; innoDB鎖定id為1-4的數(shù)據(jù)并返回給服務(wù)器層，服務(wù)器層繼而過濾掉id=1卷雕；執(zhí)行計(jì)劃的extra顯示using where节猿。

使用覆蓋索引時(shí)執(zhí)行計(jì)劃的extra為using index。

當(dāng)通過執(zhí)行計(jì)劃看到對(duì)多個(gè)索引做and運(yùn)算時(shí)，說明需要一個(gè)多列索引滨嘱。

聯(lián)表查詢的排序：建議order by中所有的列來自于同一張表峰鄙；如果order by中所有列來自第一個(gè)表，則查詢第一張表時(shí)就進(jìn)行排序太雨，執(zhí)行計(jì)劃顯示using filesort吟榴；否則mysql把查詢結(jié)果放到臨時(shí)表，在關(guān)聯(lián)查詢結(jié)束后進(jìn)行排序囊扳，執(zhí)行計(jì)劃顯示using temporary using filesort吩翻。

4.2 常見命令

1）show status：輸出是計(jì)數(shù)，如created_tmp_tables計(jì)數(shù)器值為3表示創(chuàng)建3個(gè)臨時(shí)表锥咸；handler_read_rnd_next計(jì)數(shù)器值為6478表示有很多沒用到索引的讀操作狭瞎，出現(xiàn)在多表關(guān)聯(lián)查詢，子查詢創(chuàng)建了臨時(shí)表搏予，臨時(shí)表沒有索引脚作；

2）show profile：輸出一個(gè)查詢的各個(gè)子步驟所花費(fèi)時(shí)間，比如等待鎖缔刹、優(yōu)化器優(yōu)化球涛、生成臨時(shí)表、排序校镐；

3）information_schema.index_statistics：用于查看索引使用頻率并刪除未使用的索引亿扁，統(tǒng)計(jì)數(shù)據(jù)來源于InnoDB記錄索引訪問并保存索引統(tǒng)計(jì)信息。

4）show full processlist查看線程狀態(tài)進(jìn)而查看查詢的生命周期鸟廓。

4.3 慢日志查詢

把效率低的查詢捕獲到文件