????????前面的章節(jié)我們介紹了如何設(shè)計(jì)最優(yōu)的庫表結(jié)構(gòu)、 如何建立最好的索引， 這些對于高性能來說是必不可少的。 但這些還不夠一一還需要合理的設(shè)計(jì)查詢。 如果查詢寫得很糟糕，即使庫表結(jié)構(gòu)再合理、 索引再合適， 也無法實(shí)現(xiàn)高性能。

????????查詢優(yōu)化、 索引優(yōu)化、 庫表結(jié)構(gòu)優(yōu)化需要齊頭井進(jìn)， 一個不落。 在獲得編寫MySQL查 詢的經(jīng)驗(yàn)的同時(shí)， 也將學(xué)習(xí)到如何為高效的查詢設(shè)計(jì)表和索引。 同樣的， 也可以學(xué)習(xí)到在優(yōu)化庫表結(jié)構(gòu)時(shí)會影響到哪些類型的查詢。 這個過程需要時(shí)間， 所以建議大家在學(xué)習(xí)后面章節(jié)的時(shí)候多回頭看看這三章的內(nèi)容。

6.1為什么查詢速度會慢

????????真正重要是響應(yīng)時(shí)間.如果把查詢看作是一個任務(wù)， 那么它由一系列子任務(wù)組成， 每個子任務(wù)都會消耗一定的時(shí)間。 如果要優(yōu)化查詢， 實(shí)際上要優(yōu)化其子任務(wù)， 要么消除其中一些子任務(wù)， 要么減少子任務(wù)的執(zhí)行次數(shù)，要么讓子任務(wù)運(yùn)行得更快.

????????在每一個消耗大量時(shí)間的查詢案例中， 我們都能看到一些不必要的額外操作、 某些操作被額外地重復(fù)了很多次、 某些操作執(zhí)行得太慢等。優(yōu)化查詢的目的就是減少和消除這些操作所花費(fèi)的時(shí)間。

6.2慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問

????????查詢性能低下最基本的原因是訪問的數(shù)據(jù)太多。某些查詢可能不可避免地需要篩選大量 數(shù)據(jù)， 但這井不常見。大部分性能低下的查詢都可以通過減少訪問的數(shù)據(jù)量的方式進(jìn)行 優(yōu)化。對于低效的查詢， 我們發(fā)現(xiàn)通過下面兩個步驟來分析總是很有效：

1. 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。這通常意味著訪問了太多的行， 但有時(shí)候也可能是訪問了太多的列。

2. 確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。

6.2.1是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

????????有些查詢會請求超過實(shí)際需要的數(shù)據(jù)， 然后這些多余的數(shù)據(jù)會被應(yīng)用程序丟棄。這會給MySQL服務(wù)器帶來額外的負(fù)擔(dān)， 并增加網(wǎng)絡(luò)開銷。另外也會消耗應(yīng)用服務(wù)器的CPU 和內(nèi)存資源。

6.2.2MySQL是否在掃描額外的記錄

????????在確定查詢只返回需要的數(shù)據(jù)以后，接下來應(yīng)該看看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過多 的數(shù)據(jù)。對于MySQL, 最簡單的衡量查詢開銷的三個指標(biāo)如下：

? 響應(yīng)時(shí)間

? 掃描的行數(shù)

? 返回的行數(shù)

? ??????沒有哪個指標(biāo)能夠完美地衡量查詢的開銷，但它們大致反映了MySQL在內(nèi)部執(zhí)行查詢時(shí)需要訪問多少數(shù)據(jù)，并可以大概推算出查詢運(yùn)行的時(shí)間。 這三個指標(biāo)都會記錄到MySQL的慢日志中，所以檢查慢日志記錄是找出掃描行數(shù)過多的查詢的好辦法。

響應(yīng)時(shí)間

????????要記住，響應(yīng)時(shí)間只是一個表面上的值。響應(yīng)時(shí)間是兩個部分之和：服務(wù)時(shí)間和排隊(duì)時(shí)間。 服務(wù)時(shí)間是指數(shù)據(jù)庫處理這個查詢真正花了多長時(shí)間。 排隊(duì)時(shí)間是指服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行查詢的時(shí)間——可能是等I/O操作完成，也可能是等待行鎖，等等。遺憾的是，我們無法把響應(yīng) 時(shí)間細(xì)分到上面這些部分，除非有什么辦法能夠逐個測量上面這些消耗，不過很難做到。 一般最常見和重要的等待是I/O和鎖等待，但是實(shí)際情況更加復(fù)雜。

掃描的行數(shù)和返回的行數(shù)

????????分析查詢時(shí)，查看該查詢掃描的行數(shù)是非常有幫助的。 這在一定程度上能夠說明該查詢找到需要的數(shù)據(jù)的效率高不高。

????????對于找出那些 “糟糕” 的查詢，這個指標(biāo)可能還不夠完美， 因?yàn)椴⒉皇撬械男械脑L問代價(jià)都是相同的。 較短的行的訪問速度更快， 內(nèi)存中的行也比磁盤中的行的訪問速度要快得多。

????????理想情況下掃描的行數(shù)和返回的行數(shù)應(yīng)該是相同的。 但實(shí)際情況中這種 “美事” 井不多。例如在做一個關(guān)聯(lián)查詢時(shí)，服務(wù)器必須要掃描多行才能生成結(jié)果集中的一行。 掃描的行數(shù)對返回的行數(shù)的比率通常很小，一般在1:1和10:1之間，不過有時(shí)候這個值也可能非常非常大。

掃描的行數(shù)和訪問類型

????????在評估查詢開銷的時(shí)候，需要考慮一下從表中找到某一行數(shù)據(jù)的成本。 MySQL有好兒 種訪問方式可以查找井返回一行結(jié)果。 有些訪問方式可能需要掃描很多行才能返回一行結(jié)果，也有些訪問方式可能無須掃描就能返回結(jié)果。

????????在EXPLAIN語句中的type列反應(yīng)了訪問類型。 訪問類型有很多種，從全表掃描到索引掃描、 范圍掃描、 唯一索引查詢、 常數(shù)引用等。 這里列的這些，速度是從慢到快，掃描的 行數(shù)也是從小到大。 你不需要記住這些訪問類型，但需要明白掃描表、 掃描索引、 范圍訪問和單值訪問的概念。

MySQL能夠使用如下三種方式應(yīng)用WHERE條件， 從好到壞依次為 ：

? 在索引中使用WHERE條件來過濾不匹配的記錄。 這是在存儲引擎層完成的。

? 使用索引覆蓋掃描（在Extra列中出現(xiàn)了Using index) 來返回記錄， 直接從索引中過濾不需要的記錄并返回命中的結(jié)果。 這是在MySQL服務(wù)器層完成的， 但無須再回表查詢記錄。

? 從數(shù)據(jù)表中返回?cái)?shù)據(jù)，然后過濾不滿足條件的記錄（在Extra列中出現(xiàn)Using Where)。這在MySQL服務(wù)器層完成，MySQL需要先從數(shù)據(jù)表讀出記錄然后過濾。

????????如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)但只返回少數(shù)的行， 那么通?？梢試L試下面的技巧去優(yōu)化它：

? 使用索引覆蓋掃描， 把所有需要用的列都放到索引中， 這樣存儲引擎無須回表獲取對應(yīng)行就可以返回結(jié)果了（在前面的章節(jié)中我們已經(jīng)討論過了）。

? 改變庫表結(jié)構(gòu)。例如使用單獨(dú)的匯總表（這是我們在第4章中討論的辦法）。

? 重寫這個復(fù)雜的查詢， 讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個查詢（這是本章后續(xù)需要討論的問題）。

6.3重構(gòu)查詢的方式

6.3.1一個復(fù)雜查詢還是多個簡單查詢

????????設(shè)計(jì)查詢的時(shí)候一個需要考慮的重要問題是， 是否需要將一個復(fù)雜的查詢分成多個簡單的查詢。在傳統(tǒng)實(shí)現(xiàn)中， 總是強(qiáng)調(diào)需要數(shù)據(jù)庫層完成盡可能多的工作， 這樣做的邏輯在于以前總是認(rèn)為網(wǎng)絡(luò)通信、 查詢解析和優(yōu)化是一件代價(jià)很高的事情。

? ??????但是這樣的想法對于MySQL并不適用，MySQL從設(shè)計(jì)上讓連接和斷開連接都很輕量級，在返回一個小的查詢結(jié)果方面很高效。 現(xiàn)代的網(wǎng)絡(luò)速度比以前要快很多， 無論是帶寬還是延遲。 在某些版本的MySQL上， 即使在一個通用服務(wù)器上， 也能夠運(yùn)行每秒超過10萬的查詢， 即使是一個千兆網(wǎng)卡也能輕松滿足每秒超過2000次的查詢。 所以運(yùn)行多個小查詢現(xiàn)在已經(jīng)不是大問題了。

????????MySQL內(nèi)部每秒能夠掃描內(nèi)存中上百萬行數(shù)據(jù)， 相比之下， MySQL響應(yīng)數(shù)據(jù)給客戶端就慢得多了。在其他條件都相同的時(shí)候，使用盡可能少的查詢當(dāng)然是更好的。但是有時(shí)候，將一個大查詢分解為多個小查詢是很有必要的。 別害怕這樣做， 好好衡量一下這樣做是 不是會減少工作量。 稍后我們將通過本章的一個示例來展示這個技巧的優(yōu)勢。

????????不過， 在應(yīng)用設(shè)計(jì)的時(shí)候， 如果一個查詢能夠勝任時(shí)還寫成多個獨(dú)立查詢是不明智的。例如， 我們看到有些應(yīng)用對一個數(shù)據(jù)表做10次獨(dú)立的查詢來返回10行數(shù)據(jù)， 每個查詢返回一條結(jié)果， 查詢10次！

6.3.2切分查詢

????????有時(shí)候?qū)τ谝粋€大查詢我們需要 “分而治之”，將大查詢切分成小查詢， 每個查詢功能完全一樣， 只完成一小部分， 每次只返回一小部分查詢結(jié)果。

6.3.3分解關(guān)聯(lián)查詢

????????很多高性能的應(yīng)用都會對關(guān)聯(lián)查詢進(jìn)行分解。簡單地， 可以對每一個表進(jìn)行一次單表查詢， 然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。

? ??????到底為什么要這樣做？乍一看， 這樣做并沒有什么好處， 原本一條查詢， 這里卻變成多 條查詢， 返回的結(jié)果又是一模一樣的。 事實(shí)上， 用分解關(guān)聯(lián)查詢的方式重構(gòu)查詢有如下 的優(yōu)勢：

? 讓緩存的效率更高。 許多應(yīng)用程序可以方便地緩存單表查詢對應(yīng)的結(jié)果對象。 另外， 對MySQL的查詢緩存來說 , 如果關(guān)聯(lián)中的某個表發(fā)生了變化，那么就無法使用查詢緩存了， 而拆分后， 如果某個表很少改變， 那么基于該表的查詢就可以重復(fù)利用查詢緩存結(jié)果了。

? 將查詢分解后，執(zhí)行單個查詢可以減少鎖的競爭。

? 在應(yīng)用層做關(guān)聯(lián)， 可以更容易對數(shù)據(jù)庫進(jìn)行拆分， 更容易做到高性能和可擴(kuò)展。

? 查詢本身效率也可能會有所提升。 這個例子中， 使用IN()代替關(guān)聯(lián)查詢， 可以讓MySQL按照ID順序進(jìn)行查詢， 這可能比隨機(jī)的關(guān)聯(lián)要更高效。 我們后續(xù)將詳細(xì)介紹這點(diǎn)。

? 可以減少冗余記錄的查詢。 在應(yīng)用層做關(guān)聯(lián)查詢， 意味著對千某條記錄應(yīng)用只需要 查詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢，則可能需要重復(fù)地訪問一部分?jǐn)?shù)據(jù)。 從這點(diǎn)看， 這樣的重構(gòu)還可能會減少網(wǎng)絡(luò)和內(nèi)存的消耗。

? 更進(jìn)一步， 這樣做相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)， 而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)。 某些場景哈希關(guān)聯(lián)的效率要高很多（本章后續(xù)我們將討論這點(diǎn)）。

6.4查詢執(zhí)行的基礎(chǔ)

????????當(dāng)希望MySQL能夠以更高的性能運(yùn)行查詢時(shí)， 最好的辦法就是弄清楚MySQL是如何一點(diǎn)， 很多查詢優(yōu)化工作實(shí)際上就是遵循一些原則讓優(yōu)優(yōu)化和執(zhí)行查詢的。 一且理解這一點(diǎn)，很多查詢優(yōu)化工作實(shí)際上就是遵循一些原則讓優(yōu)化器能夠按照預(yù)想的合理的方式運(yùn)行。

1. 客戶端發(fā)送一條查詢給服務(wù)器。

2. 服務(wù)器先檢查查詢緩存， 如果命中了緩存， 則立刻返回存儲在緩存中的結(jié)果。 否則進(jìn)入下一階段。

3. 服務(wù)器端進(jìn)行SQL解析、 預(yù)處理， 再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃。

4. MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃， 調(diào)用存儲引擎的API來執(zhí)行查詢。

5. 將結(jié)果返回給客戶端。

6.4.1 MySQL客戶端／服務(wù)器通信協(xié)議

????????一般來說，不需要去理解MySQL通信協(xié)議的內(nèi)部實(shí)現(xiàn)細(xì)節(jié)，只需要大致理解通信協(xié)議是如何工作的。 MySQL客戶端和服務(wù)器之間的通信協(xié)議是 “半雙工 ” 的， 這意味著，在任何一個時(shí)刻， 要么是由服務(wù)器向客戶端發(fā)送數(shù)據(jù)， 要么是由客戶端向服務(wù)器發(fā)送數(shù)據(jù)， 這兩個動作不能同時(shí)發(fā)生。 所以， 我們無法也無須將一個消息切成小塊獨(dú)立來 發(fā)送。

????????這種協(xié)議讓MySQL通信簡單快速， 但是也從很多地方限制了MySQL。 一個明顯的限制 是， 這意味著沒法進(jìn)行流量控制。 一且一端開始發(fā)生消息， 另一端要接收完整個消息才能響應(yīng)它。 這就像來回拋球的游戲：在任何時(shí)刻， 只有一個人能控制球， 而且只有控制球的人才能將球拋回去（發(fā)送消息）。

查詢狀態(tài)

????????對于一 個MySQL 連接，或者說一個線程，任何時(shí)刻都有一 個狀態(tài)，該狀態(tài)表示了 MySQL當(dāng)前正在做什么。有很多種方式能查看當(dāng)前的狀態(tài)，最簡單的是使用SHOW FULL PROCESS LIST命令（該命令返回 結(jié)果中的Command 列就表示當(dāng)前的狀態(tài)）。在一 個查詢的生命周期中，狀態(tài)會變化很多次。MySQL官方手冊中對這 些狀態(tài)值的含義有最權(quán)威的解釋，下面 將這些狀態(tài)列出來，并做一 個簡單的解釋。

Sleep

線程正在等待客戶端發(fā)送新的請求。

Query

線程正在執(zhí)行查詢 或者正在將結(jié)果發(fā)送給客戶端。

Locked

在MySQL服務(wù)器層，該線程正在等待表 鎖。在存儲引擎級別實(shí)現(xiàn)的鎖，例如 InnoDB的行鎖，并不會體現(xiàn)在線程狀態(tài)中。對千MyISAM來說這是一 個比較典型的狀態(tài)，但在其他沒有行鎖的引擎中也經(jīng)常會出現(xiàn)。

Analyzing and statistics

線程正在收集存儲引擎的統(tǒng)計(jì)信息，井生成查詢的執(zhí)行計(jì)劃。?

Copying to tmp table [on disk]

線程正在執(zhí)行查詢，并且將其結(jié)果集都復(fù)制到一個臨時(shí)表中，這種狀態(tài)一般要么是

在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果這個狀態(tài)后面

還有"on disk"標(biāo)記，那表示MySQL正在將一個內(nèi)存臨時(shí)表放到磁盤上。

Sorting result

線程正在對結(jié)果集進(jìn)行排序。

Sending data

這表示多種情況：線程可能在多個狀態(tài)之間傳送數(shù)據(jù)， 或者在生成結(jié)果集， 或者在向客戶端返回?cái)?shù)據(jù)。

6.4.2查詢緩存

????????在解析一個查詢語句之前， 如果查詢緩存是打開的， 那么MySQL會優(yōu)先檢查這個查詢是否命中查詢緩存中的數(shù)據(jù)。 這個檢查是通過一個對大小寫敏感的哈希查找實(shí)現(xiàn)的。 查詢和緩存中的查詢即使只有一個字節(jié)不同， 那也不會匹配緩存結(jié)果注 11' 這種情況下查詢就會進(jìn)入下一階段的處理。

????????如果當(dāng)前的查詢恰好命中了查詢緩存， 那么在返回查詢結(jié)果之前MySQL會檢查一次用戶權(quán)限。 這仍然是無須解析查詢SQL語句的， 因?yàn)樵诓樵兙彺嬷幸呀?jīng)存放了 當(dāng)前查詢需要訪問的表信息。 如果權(quán)限沒有問題， MySQL會跳過所有 其他階段， 直接從緩存中拿到結(jié)果并返回給客戶端。 這種情況下，查詢不會被解析，不用生成執(zhí)行計(jì)劃，不會被執(zhí)行。在第7章中的查詢緩存一節(jié)， 你將學(xué)習(xí)到更多細(xì)節(jié)。

6.4.3查詢優(yōu)化處理

????????查詢的生命周期的下一步是將一個SQL轉(zhuǎn)換成一個執(zhí)行計(jì)劃， MySQL再依照這個執(zhí)行計(jì)劃和存儲引擎進(jìn)行交互。 這包括多個子階段：解析SQL、 預(yù)處理 、 優(yōu)化SQL執(zhí)行計(jì)劃。這個過程中任何錯誤（例如語法錯誤）都可能終止查詢。 這里不打算詳細(xì)介紹MySQL內(nèi)部實(shí)現(xiàn)， 而只是選擇性地介紹其中幾個獨(dú)立的部分， 在實(shí)際執(zhí)行中， 這幾部分可能一起執(zhí)行也可能單獨(dú)執(zhí)行。 我們的目的是幫助大家理解MySQL如何執(zhí)行查詢， 以便寫出更優(yōu)秀的查詢。

語法解析器和預(yù)處理

????????首先， MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析， 并生成 一棵對應(yīng)的 “ 解析樹 ” 。 MySQL解析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢。 例如， 它將驗(yàn)證是否使用錯誤的關(guān)鍵字，或者使用關(guān)鍵字的順序是否正確等，再或者它還會驗(yàn)證引號是否能前后正確匹配。

????????預(yù)處理器則根據(jù)一些MySQL規(guī)則進(jìn)一步檢查解析樹是否合法，例如，這里將檢查數(shù)據(jù)表和數(shù)據(jù)列是否存在，還會解析名字和別名，看看它們是否有歧義。????

? ? ? ? 下一步預(yù)處理器會驗(yàn)證權(quán)限。 這通常很快，除非服務(wù)器上有非常多的權(quán)限配置。

查詢優(yōu)化器

????????現(xiàn)在語法樹被認(rèn)為是合法的了，并且由優(yōu)化器將其轉(zhuǎn)化成執(zhí)行計(jì)劃。 一條查詢可以有很 多種執(zhí)行方式，最后都返回相同的結(jié)果。 優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃。

????????MySQL使用基于成本的優(yōu)化器，它將嘗試預(yù)測一個查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個。 最初，成本的最小單位是隨機(jī)讀取一個4K數(shù)據(jù)頁的成本，后來（成本計(jì)算公式）變得更加復(fù)雜，井且引入了一些 “因子” 來估算某些操作的代價(jià)， 如當(dāng)執(zhí)行一次WHERE條件比較的成本。 可以通過查詢當(dāng)前會話的Last_query_cost的值來得知MySQL計(jì)算的當(dāng)前查詢的成本。

????????有很多種原因會導(dǎo)致MySQL優(yōu)化器選擇錯誤的執(zhí)行計(jì)劃，如下所示：

? 統(tǒng)計(jì)信息不準(zhǔn)確。 MySQL依賴存儲引擎提供的統(tǒng)計(jì)信息來評估成本，但是有的存儲引擎提供的信息是準(zhǔn)確的，有的偏差可能非常大。 例如，InnoDB因?yàn)槠銶VCC的架構(gòu)，井不能維護(hù)一個數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息。

? 執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際執(zhí)行的成本。 所以即使統(tǒng)計(jì)信息精準(zhǔn)，優(yōu)化器給出的執(zhí)行計(jì)劃也可能不是最優(yōu)的。 例如有時(shí)候某個執(zhí)行計(jì)劃雖然需要讀取更多的頁面，但是它的成本卻更小。因?yàn)槿绻@些頁面都是順序讀或者這些頁面都已經(jīng)在內(nèi)存中的話，那么它的訪問成本將很小。

? MySQL層面并不知道哪些頁面在內(nèi)存中、哪些在磁盤上，所以查詢實(shí)際執(zhí)行過程中到底需要多少次物理1/0是無法得知的。 MySQL的最優(yōu)可能和你想的最優(yōu)不一樣。你可能希望執(zhí)行時(shí)間盡可能的短，但是MySQL只是基于其成本模型選擇最優(yōu)的執(zhí)行計(jì)劃，而有些時(shí)候這井不是最快的執(zhí)行 方式。所以，這里我們看到根據(jù)執(zhí)行成本來選擇執(zhí)行計(jì)劃并不是完美的模型。

? MySQL從不考慮其他并發(fā)執(zhí)行的查詢，這可能會影響到當(dāng)前查詢的速度。?

? MySQL也井不是任何時(shí)候都是基于成本的優(yōu)化。有時(shí)也會基千一些固定的規(guī)則，例如，如果存在全文搜索的MATCH()子句，則在存在全文索引的時(shí)候就使用全文索引。即使有時(shí)候使用別的索引和WHERE條件可以遠(yuǎn)比這種方式要快，MySQL也仍然會使用對應(yīng)的全文索引。

? MySQL不會考慮不受其控制的操作的成本，例如執(zhí)行存儲過程或者用戶自定義函數(shù)的成本。

? 后面我們還會看到，優(yōu)化器有時(shí)候無法去估算所有可能的執(zhí)行計(jì)劃，所以它可能錯過實(shí)際上最優(yōu)的執(zhí)行計(jì)劃。

????????MySQL的查詢優(yōu)化器是一個非常復(fù)雜的部件，它使用了很多優(yōu)化策略來生成一個最優(yōu)的執(zhí)行計(jì)劃。優(yōu)化策略可以簡單地分為兩種，一種是靜態(tài)優(yōu)化，一種是動態(tài)優(yōu)化。靜態(tài)優(yōu)化可以直接對解析樹進(jìn)行分析，井完成優(yōu)化。例如，優(yōu)化器可以通過一些簡單的代數(shù)變換將WHERE條件轉(zhuǎn)換成另一種等價(jià)形式。靜態(tài)優(yōu)化不依賴于特別的數(shù)值，如WHERE條件中帶入的一些常數(shù)等。靜態(tài)優(yōu)化在第一次完成后就一直有效，即使使用不同的參數(shù)重復(fù)執(zhí)行查詢也不會發(fā)生變化。可以認(rèn)為這是一種“ 編譯時(shí)優(yōu)化”。

????????相反，動態(tài)優(yōu)化則和查詢的上下文有關(guān)，也可能和很多其他因素有關(guān)，例如WHERE條件中的取值、索引中條目對應(yīng)的數(shù)據(jù)行數(shù)等。這需要在每次查詢的時(shí)候都重新評估，可以認(rèn)為這是“運(yùn)行時(shí)優(yōu)化”。

????????在執(zhí)行語句和存儲過程的時(shí)候，動態(tài)優(yōu)化和靜態(tài)優(yōu)化的區(qū)別非常重要。MySQL對查詢的靜態(tài)優(yōu)化只需要做一次，但對查詢的動態(tài)優(yōu)化則在每次執(zhí)行時(shí)都需要重新評估。有時(shí)候甚至在查詢的執(zhí)行過程中也會重新優(yōu)化。注12

下面是一些MySQL能夠處理的優(yōu)化類型：

重新定義關(guān)聯(lián)表的順序

????????數(shù)據(jù)表的關(guān)聯(lián)井不總是按照在查詢中指定的順序進(jìn)行。決定關(guān)聯(lián)的順序是優(yōu)化器很重要的一部分功能，本章后面將深入介紹這一點(diǎn)。

將外連接轉(zhuǎn)化成內(nèi)連接

????????并不是所有的OUTER JOIN語句都必須以外連接的方式執(zhí)行。諸多因素，例如WHERE條件、庫表結(jié)構(gòu)都可能會讓外連接等價(jià)于一個內(nèi)連接。MySQL能夠識別這點(diǎn)并重寫查詢，讓其可以調(diào)整關(guān)聯(lián)順序。

使用等價(jià)變換規(guī)則

????????MySQL可以使用一些等價(jià)變換來簡化并規(guī)范表達(dá)式。它可以合并和減少一些比較，還可以移除一些恒成立和一些恒不成立的判斷。這些規(guī)則對于我們編寫條件語句很有用，我們將在本章后續(xù)繼續(xù)討論。

優(yōu)化COUNT()、MIN()和MAX()

????????索引和列是否可為空通?？梢詭椭鶰ySQL優(yōu)化這類表達(dá)式。例如，要找到某一列的最小值，只需要查詢對應(yīng)B-Tree索引最左端的記錄，MySQL可以直接獲取索引的第一行記錄。在優(yōu)化器生成執(zhí)行計(jì)劃的時(shí)候就可以利用這一點(diǎn)，在B-Tree索引中，優(yōu)化器會將這個表達(dá)式作為一個常數(shù)對待。類似的，如果要查找一個最大值，也只需讀取B-Tree索引的最后一條記錄。如果MySQL使用了這種類型的優(yōu)化，那么在EXPLAIN中就可以看到"Select tables optimized away"。從字面意思可以看出，它表示優(yōu)化器已經(jīng)從執(zhí)行計(jì)劃中移除了該表，并以一個常數(shù)取而代之。類似的，沒有任何WHERE條件的COUNT(*)查詢通常也可以使用存儲引擎提供的一些優(yōu)化（例如，MylSAM維護(hù)了一個變量來存放數(shù)據(jù)表的行數(shù)）。

預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式

????????當(dāng)MySQL檢測到一個表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候，就會一直把該表達(dá)式作為常數(shù)進(jìn)行優(yōu)化處理。例如，一個用戶自定義變址在查詢中沒有發(fā)生變化時(shí)就可以轉(zhuǎn)換為一個常數(shù)。數(shù)學(xué)表達(dá)式則是另一種典型的例子。

????????讓人驚訝的是，在優(yōu)化階段，有時(shí)候甚至一個查詢也能夠轉(zhuǎn)化為一個常數(shù)。一個例子是在索引列上執(zhí)行MIN()函數(shù)。甚至是主鍵或者唯一鍵查找語句也可以轉(zhuǎn)換為常數(shù)表達(dá)式。如果WHERE子句中使用了該類索引的常數(shù)條件，MySQL可以在查詢開始階段就先查找到這些值，這樣優(yōu)化器就能夠知道井轉(zhuǎn)換為常數(shù)表達(dá)式。

覆蓋索引掃描

????????當(dāng)索引中的列包含所有查詢中需要使用的列的時(shí)候，MySQL就可以使用索引返回需要的數(shù)據(jù)， 而無須查詢對應(yīng)的數(shù)據(jù)行， 在前面的章節(jié)中我們已經(jīng)討論過這點(diǎn)了。

子查詢優(yōu)化

????????MySQL在某些情況下可以將子查詢轉(zhuǎn)換一種效率更高的形式， 從而減少多個查詢多次對數(shù)據(jù)進(jìn)行訪問。

提前終止查詢

????????在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候，MySQL總是能夠立刻終止查詢。一個典型的例子就是當(dāng)使用了LIMIT子句的時(shí)候。除此之外，MySQL還有幾類情況也會提前終止查詢， 例如發(fā)現(xiàn)了一個不成立的條件， 這時(shí)MySQL可以立刻返回一個空結(jié)果。

等值傳播

如果 兩個列的值通過等式關(guān)聯(lián)，那么MySQL能夠把其中一個列的WHERE條件傳遞到另一列上。

列表IN()的比較

????????在很多數(shù)據(jù)庫系統(tǒng)中，IN()完全等同于多個OR條件的子句， 因?yàn)檫@ 兩者是完全等價(jià)的。 在MySQL中這點(diǎn)是不成立的，MySQL將 IN()列表中的數(shù)據(jù)先進(jìn)行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件，這是一個 O(log n)復(fù)雜度的操作，等價(jià)地轉(zhuǎn)換成OR查詢的復(fù)雜度為 O(n), 對千IN()列表中有大量取值的時(shí)候，MySQL的處理速度將會更快。

????????上面列舉的遠(yuǎn)不是MySQL優(yōu)化器的全部，MySQL還會做大量其他的優(yōu)化，即使本章全 部用來描述也會篇幅不足，但上面的這些例子已經(jīng)足以讓大家明白優(yōu)化器的復(fù)雜性和智能性了。

數(shù)據(jù)和索引的統(tǒng)計(jì)信息

MySQL如何執(zhí)行關(guān)聯(lián)查詢

????????MySQL中“關(guān)聯(lián)” 詞所包含的意義比一般意義上理解的要更廣泛?？偟膩碚f，MySQL認(rèn)為任何一個查詢都是一次“關(guān)聯(lián)”—— 并不僅僅是一個查詢需要到兩個表

匹配才叫關(guān)聯(lián)，所以在MySQL中，每一個查詢，每一個片段（包括子查詢，甚至基于單表的SELECT) 都可能是關(guān)聯(lián)。

????????所以，理解MySQL如何執(zhí)行關(guān)聯(lián)查詢至關(guān)重要。我們先來看一個UNION查詢的例子。對于UNION查詢，MySQL先將一系列的單個查詢結(jié)果放到一個臨時(shí)表中，然后再重新讀出臨時(shí)表數(shù)據(jù)來完成UNION查詢。在MySQL的概念中，每個查詢都是一次關(guān)聯(lián)，所以讀取結(jié)果臨時(shí)表也是一次關(guān)聯(lián)。

????????當(dāng)前MySQL關(guān)聯(lián)執(zhí)行的策略很簡單： MySQL對任何關(guān)聯(lián)都執(zhí)行嵌套循環(huán)關(guān)聯(lián)操作，即MySQL先在一個表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套循環(huán)到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然后根據(jù)各個表匹配的行，返回查詢中需要的各個列。MySQL會嘗試在最后一個關(guān)聯(lián)表中找到所有匹配的行，如果最后一個關(guān)聯(lián)表無法找到更多的行以后， MySQL返回到上一層次關(guān)聯(lián)表， 看是否能夠找到更多的匹配記錄， 依此類推迭代執(zhí)行。?

? ??????按照這樣的方式查找第一個表記錄， 再嵌套查詢下一個關(guān)聯(lián)表， 然后回溯到上一個表，在MySQL中是通過嵌套循環(huán)的方式實(shí)現(xiàn)一正如其名 “嵌套循環(huán)關(guān)聯(lián)”。

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

????????和很多其他關(guān)系數(shù)據(jù)庫不同， MySQL井不會生成查詢字節(jié)碼來執(zhí)行查詢。 MySQL生成 查詢的一棵指令樹， 然后通過存儲引擎執(zhí)行完成這棵指令樹井返回結(jié)果。 最終的執(zhí)行計(jì)劃包含了重構(gòu)查詢的全部信息。 如果對某個查詢執(zhí)行EXPLAIN EXTENDED后， 再執(zhí)行SHOW WARNINGS, 就可以看到重構(gòu)出的查詢.

關(guān)聯(lián)查詢優(yōu)化器

????????MySQL優(yōu)化器最重要的一部分就是關(guān)聯(lián)查詢優(yōu)化， 它決定了多個表關(guān)聯(lián)時(shí)的順序。 通常多表關(guān)聯(lián)的時(shí)候， 可以有多種不同的關(guān)聯(lián)順序來獲得相同的執(zhí)行結(jié)果。 關(guān)聯(lián)查詢優(yōu)化器則通過評估不同順序時(shí)的成本來選擇一個代價(jià)最小的關(guān)聯(lián)順序。

排序優(yōu)化

????????無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應(yīng)盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進(jìn)行排序。

? ??????如果需要排序的數(shù)據(jù)量小于 “排序緩沖區(qū)", MySQL使用內(nèi)存進(jìn)行 “快速排序” 操作。 如果內(nèi)存不夠排序，那么MySQL會先將數(shù)據(jù)分塊， 對每個獨(dú)立的塊使用 “快速排序”?進(jìn)行排序，并將各個塊的排序結(jié)果存放在磁盤上，然后將各個排好序的塊進(jìn)行合并 最后返回排序結(jié)果。

單次傳輸排序（新版本使用）

? ??????先讀取查詢所需要的所有列，然后再根據(jù)給定列進(jìn)行排序，最后直接返回排序結(jié)果。這個算法只在MySQL 4.1和后續(xù)更新的版本才引入。 因?yàn)椴辉傩枰獜臄?shù)據(jù)表中讀取兩次數(shù)據(jù)，對于I/O密集型的應(yīng)用，這樣做的效率高了很多。另外，相比兩次傳輸排序，這個算法只需要一次順序1/0 讀取所有的數(shù)據(jù)，而無須任何的隨機(jī)I/O。 缺點(diǎn)是，如果需要返回的列非常多、 非常大，會額外占用大量的空間，而這些列 對排序操作本 身來說是沒有任何作用的。 因?yàn)閱螚l排序記錄很大，所以可能會有更多的排序塊需要合并。

6.4.4查詢執(zhí)行引擎

????????在解析和優(yōu)化階段，MySQL將生成查詢對應(yīng)的執(zhí)行計(jì)劃，MySQL的查詢執(zhí)行引擎則根據(jù)這個執(zhí)行計(jì)劃來完成整個查詢。 這里執(zhí)行計(jì)劃是一個數(shù)據(jù)結(jié)構(gòu)， 而不是和很多其他的關(guān)系型數(shù)據(jù)庫那樣會生成對應(yīng)的字節(jié)碼。

? ??????相對于查詢優(yōu)化階段， 查詢執(zhí)行階段不是那么復(fù)雜：MySQL只是簡單地根據(jù)執(zhí)行計(jì)劃給出的指令逐步執(zhí)行。

6.4.5返回結(jié)果給客戶端

????????查詢執(zhí)行的最后一個階段是將結(jié)果返回給客戶端。 即使查詢不需要返回結(jié)果集給客戶端， MySQL仍然會返回這個查詢的一些信息， 如該查詢影響到的行數(shù)。

????????如果查詢可以被緩存， 那么MySQL在這個階段也會將結(jié)果存放到查詢緩存中。MySQL將結(jié)果集返回客戶端是一個增量、 逐步返回的過程。

? ??????這樣處理有兩個好處：服務(wù)器端無須存儲太多的結(jié)果， 也就不會因?yàn)橐祷靥嘟Y(jié)果而 消耗太多內(nèi)存。 另外， 這樣的處理也讓MySQL客戶端第一時(shí)間獲得返回的結(jié)果。

? ??????結(jié)果集中的每一行都會以一個滿足MySQL客戶端／服務(wù)器通信協(xié)議的封包發(fā)送， 再通過TCP協(xié)議進(jìn)行傳輸， 在TCP傳輸?shù)倪^程中， 可能對MySQL的封包進(jìn)行緩存然后批量傳輸。

6.5 MySQL查詢優(yōu)化器的局限性

????????MySQL的萬能 ”嵌套循環(huán)” 并不是對每種查詢都是最優(yōu)的。 不過還好， MySQL查詢優(yōu)化器只對少部分查詢不適用， 而且我們往往可以通過改寫查詢讓MySQL高效地完成工作。

6.5.1關(guān)聯(lián)子查詢

? ??????MySQL的子查詢實(shí)現(xiàn)得非常糟糕。 最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句。使用IN()加子查詢， 性能經(jīng)常會非常糟， 所以 通常建議使用EXISTS()等效的改寫查詢來獲取更好的效率。

如何用好關(guān)聯(lián)子查詢

????????井不是所有關(guān)聯(lián)子查詢的性能都會很差。 如果有人跟你說：“別用關(guān)聯(lián)子查詢"' 那么不要理他。 先測試，然后做出自己的判斷。 很多時(shí)候，關(guān)聯(lián)子查詢是一種非常合理、 自然， 甚至是性能最好的寫法。

6.5.2 UNION的限制

????????有時(shí)，MySQL無法將限制條件從外層 “下推” 到內(nèi)層，這使得原本能夠限制部分返回結(jié)果的條件無法應(yīng)用到內(nèi)層查詢的優(yōu)化上。

? ??????如果希望UNION的各個子句能夠根據(jù)LIMIT只取部分結(jié)果集，或者希望能夠先排好序再合并結(jié)果集的話，就需要在UNION的各個子句中分別使用這些子句。

6.5.3索引合并優(yōu)化

????????在5.0和更新的版本中，當(dāng)WHERE子句中包含多個復(fù)雜條件的 時(shí)候，MySQL能夠訪問單個表的多個索引以合井和交叉過濾的方式來定位需要查找的行

6.5.4等值傳遞

????????某些時(shí)候，等值傳遞會帶來一些意想不到的額外消耗。 例如，有一個非常大的IN()列表，而MySQL優(yōu)化器發(fā)現(xiàn)存在WHERE、 ON或者USING的子句， 將這個列表的值和另一個表的某個列相關(guān)聯(lián)。

? ??????那么優(yōu)化器會將IN()列表都復(fù)制應(yīng)用到關(guān)聯(lián)的各個表中。 通常， 因?yàn)楦鱾€表新增了過濾條件， 優(yōu)化器可以更高效地從存儲引擎過濾記錄。 但是如果這個列表非常大， 則會導(dǎo)致優(yōu)化和執(zhí)行都會變慢。

6.5.5并行執(zhí)行

????????MySQL無法利用多核特性來井行執(zhí)行查詢。 很多其他的關(guān)系型數(shù)據(jù)庫能夠提供這個特性，但是MySQL做不到。 這里特別指出是想告訴讀者不要花時(shí)間去嘗試尋找并行執(zhí)行查詢的方法。

6.7優(yōu)化特定類型的查詢

6.7.1優(yōu)化COUNT()查詢

????????COUNT ()是一個特殊的函數(shù)， 有兩種非常不同的作用：它可以統(tǒng)計(jì)某個列值的數(shù)量， 也 可以統(tǒng)計(jì)行數(shù)。 在統(tǒng)計(jì)列值時(shí)要求列值是非空的（不統(tǒng)計(jì) NULL)。如果在 COUNT() 的括號中指定了列或者列的表達(dá)式， 則統(tǒng)計(jì)的就是這個表達(dá)式有值的結(jié)果數(shù)注 24。因?yàn)楹芏嗳?對 NULL 理解有問題， 所以這里很容易產(chǎn)生誤解。 如果想了解更多關(guān)于SQL語句中 NULL 的含義， 建議閱讀一些關(guān)千SQL語句基礎(chǔ)的書籍。（關(guān)千這個話題， 互聯(lián)網(wǎng)上的一些信息是不夠精確的。）

????????COUNT ()的另一個作用是統(tǒng)計(jì)結(jié)果集的行數(shù)。 當(dāng)MySQL確認(rèn)括號內(nèi)的表達(dá)式值不可能為空時(shí)， 實(shí)際上就是 在統(tǒng)計(jì)行數(shù)。 最簡單的就是當(dāng)我們使用 COUNT(*) 的時(shí)候， 這種情況下通配符＊井不會像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上，它會忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)。

6.7.2優(yōu)化關(guān)聯(lián)查詢

這個話題基本上整本書都在討論， 這里需要特別提到的是：

? 確保 ON 或者US ING子句中的列上有索引。在創(chuàng)建索引的時(shí)候就要考慮到關(guān)聯(lián)的順序。當(dāng)表A和表B用列c關(guān)聯(lián)的時(shí)候， 如果優(yōu)化器的關(guān)聯(lián)順序是B、A, 那么就不需要在 B表的對應(yīng)列上建上索引。 沒有用到的索引只會帶來額外的負(fù)擔(dān)。 一般來說， 除非 有其他理由， 否則只需要在關(guān)聯(lián)順序中的第二個表的相應(yīng)列上創(chuàng)建索引。

? 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個表中的列， 這樣MySQL才有可能使用索引來優(yōu)化這個過程。

? 當(dāng)升級MySQL的時(shí)候需要注意：關(guān)聯(lián)語法 、 運(yùn)算符優(yōu)先級等其他可能會發(fā)生變化 的地方。 因?yàn)橐郧笆瞧胀P(guān)聯(lián)的地方可能會變成笛卡兒積， 不同類型的關(guān)聯(lián)可能會 生成不同的結(jié)果等。

6.7.3優(yōu)化子查詢

????????關(guān)于子查詢優(yōu)化我們給出的最重要的優(yōu)化建議就是盡可能使用關(guān)聯(lián)查詢代替， 至少當(dāng)前的MySQL版本需要這樣。 本章的前面章節(jié)已經(jīng)詳細(xì)介紹了這點(diǎn)。 “盡可能使用關(guān)聯(lián)” 并不是絕對的， 如果使用的是MySQL5.6或更新的版本或者M(jìn)ariaDB, 那么就可以直接忽略關(guān)于子查詢的這些建議了。

6.7.4優(yōu)化GROUP BY和DISTINCT

????????在MySQL中， 當(dāng)無法使用索引的時(shí)候， GROUP BY使用兩種策略來完成：使用臨時(shí)表或者文件排序來做分組。

? ??????如果需要對關(guān)聯(lián)查詢做分組(GROUP BY), 并且是按照查找表中的某個列進(jìn)行分組， 那么通常采用查找表的標(biāo)識列分組的效率會比其他列更高。

6.7.5優(yōu)化LIMIT分頁

????????優(yōu)化此類分頁查詢的一個最簡單的辦法就是盡可能地使用索引覆蓋掃描， 而不是查詢所 有的列。 然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。 對于偏移量很大的時(shí)候， 這樣做的效率會提升非常大。

????????這里的 延遲關(guān)聯(lián)將大大提升查詢效率，它讓MySQL掃描盡可能少的頁面，獲取需要訪問的記錄 后再根據(jù)關(guān)聯(lián)列回原表查詢需要的所有列。這個技術(shù)也可以用于優(yōu)化關(guān)聯(lián)查詢中的LIMIT子句。

6.7.7優(yōu)化UNION查詢

????????除非確實(shí)需要服務(wù)器消除 重復(fù)的行，否則就一定要使用UNION ALL, 這一點(diǎn)很重要。 如 果沒有ALL關(guān)鍵字，MySQL會給臨時(shí)表加上 DISTINCT選項(xiàng)，這會導(dǎo)致對整個臨時(shí)表的 數(shù)據(jù)做唯一性檢查。 這樣做的代價(jià)非常高。 即使有 ALL關(guān)鍵字， MySQL仍然會使用臨時(shí)表存儲結(jié)果。 事實(shí)上，MySQL總是將結(jié)果放入臨時(shí)表，然后再讀出，再返回給客戶端。雖然很多時(shí)候這樣做是沒有必要的（例如，MySQL可以直接把這些 結(jié)果返回給客戶端）。

6.9總結(jié)

????????如果把創(chuàng)建高性能應(yīng)用程序比作是一個環(huán)環(huán)相扣的“難題”， 除了前面介紹的schema、索引和查詢語句設(shè)計(jì)之外， 查詢優(yōu)化應(yīng)該是解開“難題” 的最后一步了。要想寫一個好的查詢， 你必須要理解schema設(shè)計(jì)、索引設(shè)計(jì)等， 反之亦然。

????????理解查詢是如何被執(zhí)行的以及時(shí)間都消耗在哪些地方， 這依然是前面我們介紹的響應(yīng)時(shí)間的一部分。再加上一些諸如解析和優(yōu)化過程的知識， 就可以更進(jìn)一步地理解上一章討論的MySQL如何訪問表和索引的內(nèi)容了。這也從另一個維度幫助讀者理解MySQL在訪問表和索引時(shí)查詢和索引的關(guān)系。

????????優(yōu)化通常都需要三管齊下：不做、少做、快速地做。我們希望這里的案例能夠幫助你將理論和實(shí)踐聯(lián)系起來。

????????除了這些基礎(chǔ)的手段， 包括查詢、表結(jié)構(gòu)、索引等， MySQL還有一些高級的特性可以幫助你優(yōu)化應(yīng)用， 例如分區(qū)， 分區(qū)和索引有些類似但是原理不同。MySQL還支持查詢緩存，它可以幫你緩存查詢結(jié)果，當(dāng)完全相同的查詢再次執(zhí)行時(shí)，直接使用緩存結(jié)果（回想一下， “不做")。我們將在下一章中介紹這些特性。