mysql优化之query优化
? ?主要概述:在 MySQL 中有一個(gè)專門負(fù)責(zé)優(yōu)化 SELECT 語(yǔ)句的優(yōu)化器模塊，這就是我們本節(jié)將要重點(diǎn)分析的 MySQL Optimizer，其主要的功能就是通過(guò)計(jì)算分析系統(tǒng)中收集的各種統(tǒng)計(jì)信息，為客戶端請(qǐng)求的 Query 給出他認(rèn)為最優(yōu)的執(zhí)行計(jì)劃，也就是他認(rèn)為最優(yōu)的數(shù)據(jù)檢索方式。當(dāng) MySQL Optimizer 接收到從 Query Parser （解析器）送過(guò)來(lái)的 Query 之后，會(huì)根據(jù) MySQLQuery 語(yǔ)句的相應(yīng)語(yǔ)法對(duì)該 Query 進(jìn)行分解分析的同時(shí)，還會(huì)做很多其他的計(jì)算轉(zhuǎn)化工作。如常量轉(zhuǎn)化，無(wú)效內(nèi)容刪除，常量計(jì)算等等。所有這些工作都只為了 Optimizer 工作的唯一目的，分析出最優(yōu)的數(shù)據(jù)檢索方式，也就是我們常說(shuō)的執(zhí)行計(jì)劃。
?原理:
在分析 MySQL Optimizer 的工作原理之前，先了解一下 MySQL 的 Query Tree。MySQL 的 Query Tree
是通過(guò)優(yōu)化實(shí)現(xiàn) DBXP 的經(jīng)典數(shù)據(jù)結(jié)構(gòu)和 Tree 構(gòu)造器而生成的一個(gè)指導(dǎo)完成一個(gè) Query 語(yǔ)句的請(qǐng)求所
需要處理的工作步驟，我們可以簡(jiǎn)單的認(rèn)為就是一個(gè)的數(shù)據(jù)處理流程規(guī)劃，只不過(guò)是以一個(gè) Tree 的數(shù)據(jù)
結(jié)構(gòu)存放而已。通過(guò) Query Tree 我們可以很清楚的知道一個(gè) Query 的完成需要經(jīng)過(guò)哪些步驟的處理，
每一步的數(shù)據(jù)來(lái)源在哪里，處理方式是怎樣的。在整個(gè) DBXP 的 Query Tree 生成過(guò)程中，MySQL 使用了
LEX 和 YACC 這兩個(gè)功能非常強(qiáng)大的語(yǔ)法（詞法）分析工具。MySQL Query Optimizer 的所有工作都是基
于這個(gè) Query Tree 所進(jìn)行的。各位讀者朋友如果對(duì) MySQL Query Tree 實(shí)現(xiàn)生成的詳細(xì)信息比較感興
趣，可以參考 Chales A. Bell 的《Expert MySQL》這本書，里面有比較詳細(xì)的介紹。
MySQL Query Optimizer 并不是一個(gè)純粹的 CBO（Cost Base Optimizer），而是在 CBO 的基礎(chǔ)上增
加了一個(gè)被稱為 Heuristic Optimize（啟發(fā)式優(yōu)化）的功能。也就是說(shuō)，MySQL Query Optimizer 在優(yōu)
化一個(gè) Query 選擇出他認(rèn)為的最優(yōu)執(zhí)行計(jì)劃的時(shí)候，并不一定完全按照系數(shù)據(jù)庫(kù)的元信息和系統(tǒng)統(tǒng)計(jì)信
息，而是在此基礎(chǔ)上增加了某些特定的規(guī)則。其實(shí)我個(gè)人的理解就是在 CBO 的實(shí)現(xiàn)中增加了部分
RBO（Rule Base Optimizer）的功能，以確保在某些特別的場(chǎng)景下控制 Query 按照預(yù)定的方式生成執(zhí)行
計(jì)劃。
當(dāng)客戶端向 MySQL 請(qǐng)求一條 Query ，到命令解析器模塊完成請(qǐng)求分類區(qū)別出是 SELECT 并轉(zhuǎn)發(fā)給
Query Optimizer 之后，Query Optimizer 首先會(huì)對(duì)整條 Query 進(jìn)行，優(yōu)化處理掉一些常量表達(dá)式的預(yù)
算，直接換算成常量值。并對(duì) Query 中的查詢條件進(jìn)行簡(jiǎn)化和轉(zhuǎn)換，如去掉一些無(wú)用或者顯而易見(jiàn)的條
件，結(jié)構(gòu)調(diào)整等等。然后則是分析 Query 中的 Hint 信息（如果有），看顯示 Hint 信息是否可以完全
確定該 Query 的執(zhí)行計(jì)劃。如果沒(méi)有 Hint 或者 Hint 信息還不足以完全確定執(zhí)行計(jì)劃，則會(huì)讀取所涉
及對(duì)象的統(tǒng)計(jì)信息，根據(jù) Query 進(jìn)行寫相應(yīng)的計(jì)算分析，然后再得出最后的執(zhí)行計(jì)劃。
Query Optimizer 是一個(gè)數(shù)據(jù)庫(kù)軟件非常核心的功能，雖然在這里說(shuō)起來(lái)只是簡(jiǎn)單的幾句話，但是
在 MySQL 內(nèi)部，Query Optimizer 實(shí)際上是經(jīng)過(guò)了很多復(fù)雜的運(yùn)算分析，才得出最后的執(zhí)行計(jì)劃。對(duì)于
MySQL Query Optimizer 更多的信息，各位讀者可以通過(guò) MySQL Internal 文檔進(jìn)行更為全面的了解。
在分析如何優(yōu)化 MySQL Query 之前，我們需要先了解一下 Query 語(yǔ)句優(yōu)化的基本思路和原則。一
般來(lái)說(shuō)，Query 語(yǔ)句的優(yōu)化思路和原則主要提現(xiàn)在以下幾個(gè)方面：
1. 優(yōu)化更需要優(yōu)化的 Query；
2. 定位優(yōu)化對(duì)象的性能瓶頸；
3. 明確的優(yōu)化目標(biāo)；
4. 從 Explain 入手；
5. 多使用 profile
6. 永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集；
7. 盡可能在索引中完成排序；
8. 只取出自己需要的 Columns；
9. 僅僅使用最有效的過(guò)濾條件；
10. 盡可能避免復(fù)雜的 Join 和子查詢；
上面所列的幾點(diǎn)信息，前面 4 點(diǎn)可以理解為 Query 優(yōu)化的一個(gè)基本思路，后面部分則是我們優(yōu)化中
的基本原則。
下面我們先針對(duì) Query 優(yōu)化的基本思路做一些簡(jiǎn)單的分析，理解為什么我們的 Query 優(yōu)化到底該
如何進(jìn)行。
優(yōu)化更需要優(yōu)化的 Query
為什么我們需要優(yōu)化更需要優(yōu)化的 Query？這個(gè)地球人都知道的“并不能成為問(wèn)題的問(wèn)題”我想就
并不需要我過(guò)多解釋吧，哈哈。
那什么樣的 Query 是更需要優(yōu)化呢？對(duì)于這個(gè)問(wèn)題我們需要從對(duì)整個(gè)系統(tǒng)的影響來(lái)考慮。什么
Query 的優(yōu)化能給系統(tǒng)整體帶來(lái)更大的收益，就更需要優(yōu)化。一般來(lái)說(shuō)，高并發(fā)低消耗（相對(duì)）的
Query 對(duì)整個(gè)系統(tǒng)的影響遠(yuǎn)比低并發(fā)高消耗的 Query 大。我們可以通過(guò)以下一個(gè)非常簡(jiǎn)單的案例分析來(lái)
充分說(shuō)明問(wèn)題。
假設(shè)有一個(gè) Query 每小時(shí)執(zhí)行 10000 次，每次需要 20 個(gè) IO。另外一個(gè) Query 每小時(shí)執(zhí)行 10 次，
每次需要 20000 個(gè) IO。
我們先通過(guò) IO 消耗方面來(lái)分析?？梢钥闯?#xff0c;兩個(gè) Query 每小時(shí)所消耗的 IO 總數(shù)目是一樣的，都是
200000 IO/小時(shí)。假設(shè)我們優(yōu)化第一個(gè) Query，從 20 個(gè) IO 降低到 18 個(gè) IO，也就是僅僅降低了 2 個(gè) IO，
則我們節(jié)省了 2 * 10000 = 20000 （IO/小時(shí)）。而如果希望通過(guò)優(yōu)化第二個(gè) Query 達(dá)到相同的效果，
我們必須要讓每個(gè) Query 減少 20000 / 10 = 2000 IO。我想大家都會(huì)相信讓第一個(gè) Query 節(jié)省 2 個(gè) IO
遠(yuǎn)比第二個(gè) Query 節(jié)省 2000 個(gè) IO 來(lái)的容易。
其次，如果通過(guò) CPU 方面消耗的比較，原理和上面的完全一樣。只要讓第一個(gè) Query 稍微節(jié)省一
小塊資源，就可以讓整個(gè)系統(tǒng)節(jié)省出一大塊資源，尤其是在排序，分組這些對(duì) CPU 消耗比較多的操作中
尤其突出。
最后，我們從對(duì)整個(gè)系統(tǒng)的影響來(lái)分析。一個(gè)頻繁執(zhí)行的高并發(fā) Query 的危險(xiǎn)性比一個(gè)低并發(fā)的
Query 要大很多。當(dāng)一個(gè)低并發(fā)的 Query 走錯(cuò)執(zhí)行計(jì)劃，所帶來(lái)的影響主要只是該 Query 的請(qǐng)求者的
體驗(yàn)會(huì)變差，對(duì)整體系統(tǒng)的影響并不會(huì)特別的突出，之少還屬于可控范圍。但是，如果我們一個(gè)高并發(fā)
的 Query 走錯(cuò)了執(zhí)行計(jì)劃，那所帶來(lái)的后可很可能就是災(zāi)難性的，很多時(shí)候可能連自救的機(jī)會(huì)都不給你
就會(huì)讓整個(gè)系統(tǒng) Crash 掉。曾經(jīng)我就遇到這樣一個(gè)案例，系統(tǒng)中一個(gè)并發(fā)度較高的 Query 語(yǔ)句走錯(cuò)執(zhí)
行計(jì)劃，系統(tǒng)頃刻間 Crash，甚至我都還沒(méi)有反應(yīng)過(guò)來(lái)是怎么回事。當(dāng)重新啟動(dòng)數(shù)據(jù)庫(kù)提供服務(wù)后，系
統(tǒng)負(fù)載立刻直線飆升，甚至都來(lái)不及登錄數(shù)據(jù)庫(kù)查看當(dāng)時(shí)有哪些 Active 的線程在執(zhí)行哪些 Query。如
果是遇到一個(gè)并發(fā)并不太高的 Query 走錯(cuò)執(zhí)行計(jì)劃，至少我們還可以控制整個(gè)系統(tǒng)不至于系統(tǒng)被直接壓
跨，甚至連問(wèn)題根源都難以抓到。
定位優(yōu)化對(duì)象的性能瓶頸
當(dāng)我們拿到一條需要優(yōu)化的 Query 之后，第一件事情是什么？是反問(wèn)自己，這條 Query 有什么問(wèn)
題？我為什么要優(yōu)化他？只有明白了這些問(wèn)題，我們才知道我們需要做什么，才能夠找到問(wèn)題的關(guān)鍵。
而不能就只是覺(jué)得某個(gè) Query 好像有點(diǎn)慢，需要優(yōu)化一下，然后就開(kāi)始一個(gè)一個(gè)優(yōu)化方法去輪番嘗試。
這樣很可能整個(gè)優(yōu)化過(guò)程會(huì)消耗大量的人力和時(shí)間成本，甚至可能到最后還是得不到一個(gè)好的優(yōu)化結(jié)
果。這就像看病一樣，醫(yī)生必須要清楚的知道我們病的根源才能對(duì)癥下藥。如果只是知道我們什么地方
不舒服，然后就開(kāi)始通過(guò)各種藥物嘗試治療，那這樣所帶來(lái)的后果可能就非常嚴(yán)重了。
所以，在拿到一條需要優(yōu)化的 Query 之后，我們首先要判斷出這個(gè) Query 的瓶頸到底是 IO 還是
CPU。到底是因?yàn)樵跀?shù)據(jù)訪問(wèn)消耗了太多的時(shí)間，還是在數(shù)據(jù)的運(yùn)算（如分組排序等）方面花費(fèi)了太多資
源？
一般來(lái)說(shuō)，在 MySQL 5.0 系列版本中，我們可以通過(guò)系統(tǒng)自帶的 PROFILING 功能很清楚的找出一個(gè)
Query 的瓶頸所在。當(dāng)然，如果讀者朋友為了使用 MySQL 的某些在 5.1 版本中才有的新特性（如
Partition，EVENT 等）亦或者是比較喜歡嘗試新事務(wù)而早早使用的 MySQL 5.1 的預(yù)發(fā)布版本，可能就沒(méi)
辦法使用這個(gè)功能了，因?yàn)樵摴δ茉?MySQL5.1 系列剛開(kāi)始的版本中并不支持，不過(guò)讓人非常興奮的是該
功能在最新出來(lái)的 MySQL 5.1 正式版（5.1.30）又已經(jīng)提供了。而如果讀者朋友正在使用的 MySQL 是
4.x 版本，那可能就只能通過(guò)自行分析 Query 的各個(gè)執(zhí)行步驟，找到性能損失最大的地方。
明確的優(yōu)化目標(biāo)
當(dāng)我們定為到了一條 Query 的性能瓶頸之后，就需要通過(guò)分析該 Query 所完成的功能和 Query 對(duì)
系統(tǒng)的整體影響制訂出一個(gè)明確的優(yōu)化目標(biāo)。沒(méi)有一個(gè)明確的目標(biāo)，優(yōu)化過(guò)程將是一個(gè)漫無(wú)目的而且低
效的過(guò)程，也很難達(dá)收到一個(gè)理想的效果。尤其是對(duì)于一些實(shí)現(xiàn)應(yīng)用中較為重要功能點(diǎn)的 Query 更是如
此。
如何設(shè)定優(yōu)化目標(biāo)？這可能是很多人都非常頭疼的問(wèn)題，對(duì)于我自己也一樣。要設(shè)定一個(gè)合理的優(yōu)
化目標(biāo)，不能過(guò)于理想也不能放任自由，確實(shí)是一件非常頭疼的事情。一般來(lái)說(shuō)，我們首先需要清楚的
了解數(shù)據(jù)庫(kù)目前的整體狀態(tài)，同時(shí)也要清楚的知道數(shù)據(jù)庫(kù)中與該 Query 相關(guān)的數(shù)據(jù)庫(kù)對(duì)象的各種信息，
而且還要了解該 Query 在整個(gè)應(yīng)用系統(tǒng)中所實(shí)現(xiàn)的功能。了解了數(shù)據(jù)庫(kù)整體狀態(tài)，我們就能知道數(shù)據(jù)庫(kù)
所能承受的最大壓力，也就清楚了我們能夠接受的最悲觀情況。把握了該 Query 相關(guān)數(shù)據(jù)庫(kù)對(duì)象的信
息，我們就應(yīng)該知道實(shí)現(xiàn)該 Query 的消耗最理想情況下需要消耗多少資源，最糟糕又需要消耗多少資
源。最后，通過(guò)該 Query 所實(shí)現(xiàn)的功能點(diǎn)在整個(gè)應(yīng)用系統(tǒng)中的重要地位，我們可以大概的分析出該
Query 可以占用的系統(tǒng)資源比例，而且我們也能夠知道該 Query 的效率給客戶帶來(lái)的體驗(yàn)影響到底有多
大。
當(dāng)我們清楚了這些信息之后，我們基本可以得出該 Query 應(yīng)該滿足的一個(gè)性能范圍是怎樣的，這也
就是我們的優(yōu)化目標(biāo)范圍，然后就是通過(guò)尋找相應(yīng)的優(yōu)化手段來(lái)解決問(wèn)題了。如果該 Query 實(shí)現(xiàn)的應(yīng)用
系統(tǒng)功能比較重要，我們就必須讓目標(biāo)更偏向于理想值一些，即使在其他某些方面作出一些讓步與犧
牲，比如調(diào)整 schema 設(shè)計(jì)，調(diào)整索引組成等，可能都是需要的。而如果該 Query 所實(shí)現(xiàn)的是一些并不
是太關(guān)鍵的功能，那我們可以讓目標(biāo)更偏向悲觀值一些，而盡量保證其他更重要的 Query 的性能。這種
時(shí)候，即使需要調(diào)整商業(yè)需求，減少功能實(shí)現(xiàn)，也不得不應(yīng)該作出讓步。
從 Explain 入手
現(xiàn)在，優(yōu)化目標(biāo)也已經(jīng)明確了，自然是奧開(kāi)始動(dòng)手的時(shí)候了。我們的優(yōu)化到底該從何處入手呢？答
案只有一個(gè)，從 Explain 開(kāi)始入手。為什么？因?yàn)橹挥?Explain 才能告訴你，這個(gè) Query 在數(shù)據(jù)庫(kù)中是
以一個(gè)什么樣的執(zhí)行計(jì)劃來(lái)實(shí)現(xiàn)的。
但是，有一點(diǎn)我們必須清楚，Explain 只是用來(lái)獲取一個(gè) Query 在當(dāng)前狀態(tài)的數(shù)據(jù)庫(kù)中的執(zhí)行計(jì)
劃，在優(yōu)化動(dòng)手之前，我們比需要根據(jù)優(yōu)化目標(biāo)在自己頭腦中有一個(gè)清晰的目標(biāo)執(zhí)行計(jì)劃。只有這樣，
優(yōu)化的目標(biāo)才有意義。一個(gè)優(yōu)秀的 SQL 調(diào)優(yōu)人員（或者成為 SQL Performance Tuner），在優(yōu)化任何一
個(gè) SQL 語(yǔ)句之前，都應(yīng)該在自己頭腦中已經(jīng)先有一個(gè)預(yù)定的執(zhí)行計(jì)劃，然后通過(guò)不斷的調(diào)整嘗試，再借
助 Explain 來(lái)驗(yàn)證調(diào)整的結(jié)果是否滿足自己預(yù)定的執(zhí)行計(jì)劃。對(duì)于不符合預(yù)期的執(zhí)行計(jì)劃需要不斷分析
Query 的寫法和數(shù)據(jù)庫(kù)對(duì)象的信息，繼續(xù)調(diào)整嘗試，直至得到預(yù)期的結(jié)果。
當(dāng)然，人無(wú)完人，并不一定每次自己預(yù)設(shè)的執(zhí)行計(jì)劃都肯定是最優(yōu)的，在不斷調(diào)整測(cè)試的過(guò)程中，
如果發(fā)現(xiàn) MySQL Optimizer 所選擇的執(zhí)行計(jì)劃的實(shí)際執(zhí)行效果確實(shí)比自己預(yù)設(shè)的要好，我們當(dāng)然還是應(yīng)
該選擇使用 MySQL optimizer 所生成的執(zhí)行計(jì)劃。
上面的這個(gè)優(yōu)化思路，只是給大家指了一個(gè)優(yōu)化的基本方向，實(shí)際操作還需要讀者朋友不斷的結(jié)合
具體應(yīng)用場(chǎng)景不斷的測(cè)試實(shí)踐來(lái)體會(huì)。當(dāng)然也并不一定所有的情況都非要嚴(yán)格遵循這樣一個(gè)思路，規(guī)則
是死的，人是活的，只有更合理的方法，沒(méi)有最合理的規(guī)則。
在了解了上面這些優(yōu)化的基本思路之后，我們?cè)賮?lái)看看優(yōu)化的幾個(gè)基本原則。
永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大的結(jié)果集
很多人喜歡在優(yōu)化 SQL 的時(shí)候說(shuō)用小表驅(qū)動(dòng)大表，個(gè)人認(rèn)為這樣的說(shuō)法不太嚴(yán)謹(jǐn)。為什么？因
為大表經(jīng)過(guò) WHERE 條件過(guò)濾之后所返回的結(jié)果集并不一定就比小表所返回的結(jié)果集大，可能反而更小。
在這種情況下如果仍然采用小表驅(qū)動(dòng)大表，就會(huì)得到相反的性能效果。
其實(shí)這樣的結(jié)果也非常容易理解，在 MySQL 中的 Join，只有 Nested Loop 一種 Join 方式，也就是
MySQL 的 Join 都是通過(guò)嵌套循環(huán)來(lái)實(shí)現(xiàn)的。驅(qū)動(dòng)結(jié)果集越大，所需要循環(huán)的此時(shí)就越多，那么被驅(qū)動(dòng)表
的訪問(wèn)次數(shù)自然也就越多，而每次訪問(wèn)被驅(qū)動(dòng)表，即使需要的邏輯 IO 很少，循環(huán)次數(shù)多了，總量自然也
不可能很小，而且每次循環(huán)都不能避免的需要消耗 CPU ，所以 CPU 運(yùn)算量也會(huì)跟著增加。所以，如果
我們僅僅以表的大小來(lái)作為驅(qū)動(dòng)表的判斷依據(jù)，假若小表過(guò)濾后所剩下的結(jié)果集比大表多很多，結(jié)果就
是需要的嵌套循環(huán)中帶來(lái)更多的循環(huán)次數(shù)，反之，所需要的循環(huán)次數(shù)就會(huì)更少，總體 IO 量和 CPU 運(yùn)算
量也會(huì)少。而且，就算是非 Nested Loop 的 Join 算法，如 Oracle 中的 Hash Join，同樣是小結(jié)果集
驅(qū)動(dòng)大的結(jié)果集是最優(yōu)的選擇。
所以，在優(yōu)化 Join Query 的時(shí)候，最基本的原則就是“小結(jié)果集驅(qū)動(dòng)大結(jié)果集”，通過(guò)這個(gè)原則
來(lái)減少嵌套循環(huán)中的循環(huán)次數(shù)，達(dá)到減少 IO 總量以及 CPU 運(yùn)算的次數(shù)。
盡可能在索引中完成排序
只取出自己需要的 Columns
任何時(shí)候在 Query 中都只取出自己需要的 Columns，尤其是在需要排序的 Query 中。為什么？
對(duì)于任何 Query，返回的數(shù)據(jù)都是需要通過(guò)網(wǎng)絡(luò)數(shù)據(jù)包傳回給客戶端，如果取出的 Column 越多，
需要傳輸?shù)臄?shù)據(jù)量自然會(huì)越大，不論是從網(wǎng)絡(luò)帶寬方面考慮還是從網(wǎng)絡(luò)傳輸?shù)木彌_區(qū)來(lái)看，都是一個(gè)浪
費(fèi)。
如果是需要排序的 Query 來(lái)說(shuō)，影響就更大了。在 MySQL 中存在兩種排序算法，一種是在
MySQL4.1 之前的老算法，實(shí)現(xiàn)方式是先將需要排序的字段和可以直接定位到相關(guān)行數(shù)據(jù)的指針信息取
出，然后在我們所設(shè)定的排序區(qū)（通過(guò)參數(shù) sort_buffer_size 設(shè)定）中進(jìn)行排序，完成排序之后再次
通過(guò)行指針信息取出所需要的 Columns，也就是說(shuō)這種算法需要訪問(wèn)兩次數(shù)據(jù)。第二種排序算法是從
MySQL4.1 版本開(kāi)始使用的改進(jìn)算法，一次性將所需要的 Columns 全部取出，在排序區(qū)中進(jìn)行排序后直
接將數(shù)據(jù)返回給請(qǐng)求客戶端。改行算法只需要訪問(wèn)一次數(shù)據(jù)，減少了大量的隨機(jī) IO，極大的提高了帶有
排序的 Query 語(yǔ)句的效率。但是，這種改進(jìn)后的排序算法需要一次性取出并緩存的數(shù)據(jù)比第一種算法
要多很多，如果我們將并不需要的 Columns 也取出來(lái)，就會(huì)極大的浪費(fèi)排序過(guò)程所需要的內(nèi)存。在
MySQL4.1 之后的版本中，我們可以通過(guò)設(shè)置 max_length_for_sort_data 參數(shù)大小來(lái)控制 MySQL 選擇
第一種排序算法還是第二種排序算法。當(dāng)所取出的 Columns 的單條記錄總大小
max_length_for_sort_data 設(shè)置的大小的時(shí)候，MySQL 就會(huì)選擇使用第一種排序算法，反之，則會(huì)選
擇第二種優(yōu)化后的算法。為了盡可能提高排序性能，我們自然是更希望使用第二種排序算法，所以在
Query 中僅僅取出我們所需要的 Columns 是非常有必要的。
僅僅使用最有效的過(guò)濾條件
很多人在優(yōu)化 Query 語(yǔ)句的時(shí)候很容易進(jìn)入一個(gè)誤區(qū)，那就是覺(jué)得 WHERE 子句中的過(guò)濾條件越多
越好，實(shí)際上這并不是一個(gè)非常正確的選擇。其實(shí)我們分析 Query 語(yǔ)句的性能優(yōu)劣最關(guān)鍵的就是要讓他
選擇一條最佳的數(shù)據(jù)訪問(wèn)路徑，如何做到通過(guò)訪問(wèn)最少的數(shù)據(jù)量完成自己的任務(wù)。
為什么說(shuō)過(guò)濾條件多不一定是好事呢？請(qǐng)看下面示例：
需求： 查找某個(gè)用戶在所有 group 中所發(fā)的討論 message 基本信息。
場(chǎng)景： 1、知道用戶 ID 和用戶 nick_name
2、信息所在表為 group_message
3、group_message 中存在用戶 ID(user_id)和 nick_name(author)兩個(gè)索引
方案一：將用戶 ID 和用戶 nick_name 兩者都作為過(guò)濾條件放在 WHERE 子句中來(lái)查詢，Query 的執(zhí)行計(jì)
劃如下：
sky@localhost : example 11:29:37> EXPLAIN SELECT * FROM group_message
-> WHERE user_id = 1 AND author='1111111111'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: group_message_author_ind,group_message_uid_ind
key: group_message_author_ind
key_len: 98
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
方案二：僅僅將用戶 ID 作為過(guò)濾條件放在 WHERE 子句中來(lái)查詢，Query 的執(zhí)行計(jì)劃如下：
sky@localhost : example 11:30:45> EXPLAIN SELECT * FROM group_message
-> WHERE user_id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: group_message_uid_ind
key: group_message_uid_ind
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
方案二：僅將用戶 nick_name 作為過(guò)濾條件放在 WHERE 子句中來(lái)查詢，Query 的執(zhí)行計(jì)劃如下：
sky@localhost : example 11:38:45> EXPLAIN SELECT * FROM group_message
-> WHERE author = '1111111111'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: group_message_author_ind
key: group_message_author_ind
key_len: 98
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
初略一看三個(gè)執(zhí)行計(jì)劃好像都挺好的啊，每一個(gè) Query 的執(zhí)行類型都利用到了索引，而且都是
“ref”類型?？墒亲屑?xì)一分析，就會(huì)發(fā)現(xiàn)，group_message_uid_ind 索引的索引鍵長(zhǎng)度為 4（key_len:
4），由于 user_id 字段類型為 int，所以我們可以判定出 Query Optimizer 給出的這個(gè)索引鍵長(zhǎng)度是
完全準(zhǔn)確的。而 group_message_author_ind 索引的索引鍵長(zhǎng)度為 98（key_len: 98），因?yàn)?author 字
段定義為 varchar(32) ，而所使用的字符集是 utf8，32 * 3 + 2 = 98。而且，由于 user_id 與
author（來(lái)源于 nick_name）全部都是一一對(duì)應(yīng)的，所以同一個(gè) user_id 有哪些記錄，那么所對(duì)應(yīng)的
author 也會(huì)有完全相同的記錄。所以，同樣的數(shù)據(jù)在 group_message_author_ind 索引中所占用的存儲(chǔ)
空間要遠(yuǎn)遠(yuǎn)大于 group_message_uid_ind 索引所占用的空間。占用空間更大，代表我們?cè)L問(wèn)該索引所需
要讀取的數(shù)據(jù)量就會(huì)更多。所以，選擇 group_message_uid_ind 的執(zhí)行計(jì)劃才是最有的執(zhí)行計(jì)劃。也就
是說(shuō)，上面的方案二才是最有方案，而使用了更多的 WHERE 條件的方案一反而沒(méi)有僅僅使用 user_id
一個(gè)過(guò)濾條件的方案一優(yōu)。
可能有些人會(huì)說(shuō)，那如果將 user_id 和 author 兩者建立聯(lián)合索引呢？告訴你，效果可能比沒(méi)有這
個(gè)索引的時(shí)候更差，因?yàn)檫@個(gè)聯(lián)合索引的索引鍵更長(zhǎng)，索引占用的空間將會(huì)更大。
這個(gè)示例并不一定能代表所有場(chǎng)景，僅僅是希望讓大家明白，并不是任何時(shí)候都是使用的過(guò)濾條件
越多性能會(huì)越好。在實(shí)際應(yīng)用場(chǎng)景中，肯定會(huì)存在更多更復(fù)雜的情形，怎樣使我們的 Query 有一個(gè)更優(yōu)
化的執(zhí)行計(jì)劃，更高效的性能，還需要靠大家仔細(xì)分析各種執(zhí)行計(jì)劃的具體差別，才能選擇出更優(yōu)化的
Query。
盡可能避免復(fù)雜的 Join 和子查詢
我們都知道，MySQL 在并發(fā)這一塊做的并不是太好，當(dāng)并發(fā)量太高的時(shí)候，系統(tǒng)整體性能可能會(huì)急
劇下降，尤其是遇到一些較為復(fù)雜的 Query 的時(shí)候更是如此。這主要與 MySQL 內(nèi)部資源的爭(zhēng)用鎖定控
制有關(guān)，如讀寫相斥等等。對(duì)于 Innodb 存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定可能還要稍微好一些，如果使用
的 MyISAM 存儲(chǔ)引擎，并發(fā)一旦較高的時(shí)候，性能下降非常明顯。所以，我們的 Query 語(yǔ)句所涉及到的
表越多，所需要鎖定的資源就越多。也就是說(shuō)，越復(fù)雜的 Join 語(yǔ)句，所需要鎖定的資源也就越多，所
阻塞的其他線程也就越多。相反，如果我們將比較復(fù)雜的 Query 語(yǔ)句分拆成多個(gè)較為簡(jiǎn)單的 Query 語(yǔ)
句分步執(zhí)行，每次鎖定的資源也就會(huì)少很多，所阻塞的其他線程也要少一些。
可能很多讀者會(huì)有疑問(wèn)，將復(fù)雜 Join 語(yǔ)句分拆成多個(gè)簡(jiǎn)單的 Query 語(yǔ)句之后，那不是我們的網(wǎng)絡(luò)
交互就會(huì)更多了嗎？網(wǎng)絡(luò)延時(shí)方面的總體消耗也就更大了啊，完成整個(gè)查詢的時(shí)間不是反而更長(zhǎng)了嗎？
是的，這種情況是可能存在，但也并不是肯定就會(huì)如此。我們可以再分析一下，一個(gè)復(fù)雜的 Join Query
語(yǔ)句在執(zhí)行的時(shí)候，所需要鎖定的資源比較多，可能被別人阻塞的概率也就更大，如果是一個(gè)簡(jiǎn)單的
Query，由于需要鎖定的資源較少，被阻塞的概率也會(huì)小很多。所以 較為復(fù)雜的 Join Query 也有可能
在執(zhí)行之前被阻塞而浪費(fèi)更多的時(shí)間。而且，我們的數(shù)據(jù)庫(kù)所服務(wù)的并不是單單這一個(gè) Query 請(qǐng)求，還
有很多很多其他的請(qǐng)求，在高并發(fā)的系統(tǒng)中，犧牲單個(gè) Query 的短暫響應(yīng)時(shí)間而提高整體處理能力也是
非常值得的。優(yōu)化本身就是一門平衡與取舍的藝術(shù)，只有懂得取舍，平衡整體，才能讓系統(tǒng)更優(yōu)。
對(duì)于子查詢，可能不需要我多說(shuō)很多人就明白為什么會(huì)不被推薦使用。在 MySQL 中，子查詢的實(shí)現(xiàn)
目前還比較差，很難得到一個(gè)很好的執(zhí)行計(jì)劃，很多時(shí)候明明有索引可以利用，可 Query Optimizer 就
是不用。從 MySQL 官方給出的信息說(shuō)，這一問(wèn)題將在 MySQL6.0 中得到較好的解決，將會(huì)引入
SemiJoin 的執(zhí)行計(jì)劃，可 MySQL6.0 離我們投入生產(chǎn)環(huán)境使用恐怕還有很遙遠(yuǎn)的一段時(shí)間。所以，在
Query 優(yōu)化的過(guò)程中，能不用子查詢的時(shí)候就盡量不要使用子查詢。
上面這些僅僅只是一些常用的優(yōu)化原則，并不是說(shuō)在 Query 優(yōu)化中就只需要做到這些原則就可以，
更不是說(shuō) Query 優(yōu)化只能通過(guò)這些原則來(lái)優(yōu)化。在實(shí)際優(yōu)化過(guò)程中，我們還可能會(huì)遇到很多帶有較為復(fù)
雜商業(yè)邏輯的場(chǎng)景，具體的優(yōu)化方法就只能根據(jù)不同的應(yīng)用場(chǎng)景來(lái)具體分析，逐步調(diào)整。其實(shí)，最有效
的優(yōu)化，就是不要用，也就是不要實(shí)現(xiàn)這個(gè)商業(yè)需求。
轉(zhuǎn)載于:https://blog.51cto.com/jinjiang2009/1340516
總結(jié)
以上是生活随笔為你收集整理的mysql优化之query优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： myeclipse9 maven web
- 下一篇： 江湖行