高性能MySQL(4)——查詢性能優化

査詢優化、索引優化、庫表結構優化需要齊頭并進，一個不落。

一、為什么查詢速度為變慢

在嘗試編寫快速的查詢之前,需要清楚一點,真正重要是響應時間。如果把查詢看作是一個任務，那么他由一系列子任務組成，每個子任務都會消耗一定的時間。如果要優化查詢，實際上要優化其子任務，要么消除其中一些子任務，要么減少子任務的執行的次數，要么讓子任務運行得更快。

MySQL在執行查詢的時候有哪些子任務。哪些子任務運行的速度很慢,這里很難給出完整的列表，通常來說查詢的生命周期大致可以按照順序來看：從客戶端，到服務器，然后再服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。其中“執行”可以認為是整個生命周期中最重要的階段，這其中包括了大量為了檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組等。

在完成這些任務的時候，查詢需要在不同的地方花費時間，包括網絡，CPU計算，生成統計信息和執行計劃、鎖等待（互斥等待）等操作，尤其是向底層存儲引擎檢索數據的調用操作，這些調用需要在內存操作、CPU操作和內存不足時導致的I/O操作上消耗時間，根據引擎不同，可能還會產生大量的上下文切換以及系統調用。

在每一個消耗大量時間的查詢案例中，我們都能看到一些不必要的額外操作、某些操作被額外地重復了很多次、某些操作執行得太慢等。優化查詢的目的就是減少和消除這些操作所花費的時間。有了這些概念，我們再一起來看看如何優化査詢。

二、慢查詢基礎:優化數據訪問

査詢性能低下最基本的原因是訪問的數據太多。

大部分性能低下的査詢都可以通過減少訪問的數據量的方式進行優化。對于低效的査詢，我們發現通過下面兩個步驟來分析總是很有效：

確認應用程序是否在檢索大量超過需要的數據。這通常意味著訪問了太多的行，但有時候也可能是訪問了太多的列。
確認MySQL服務器層是否在分析大量超過需要的數據行。

2.1、是否向數據庫請求了不需要的數據

有些査詢會請求超過實際需要的數據，然后這些多余的數據會被應用程序丟棄。這會給MySQL服務器帶來額外的負擔，并增加網絡開銷,另外也會消耗應用服務器的CPU 和內存資源。

2.2、MySQL是否在掃描額外的記錄

最簡單衡量查詢開銷的三個指標

響應時間：分為服務時間和排隊時間
- 服務時間：是指數據庫處理這個查詢真正花了多長時間
- 排隊時間：是指服務器因為等待某些資源而沒有真正執行查詢的時間——坑內是等I/O操作完成，也可能使行鎖等等
掃描的行數
返回的行數

在EXPLAIN語句中的type列反應了訪問的類型。訪問類型有很多種，從全表掃描到索引掃描、范圍掃描、唯一索引查詢、常數引用等。這里列的這些，速度是從慢到快，掃描的行數也是小到大。你不需要記住這些訪問類型，但是要明白掃描表，掃描索引，范圍訪問和單值訪問的概念。如果查詢沒有辦法找到合適的訪問類型，那么最好的辦法通常就是增加一個合適的索引。

一般MySQL能夠使用如下三種應用WHERE條件，從好到壞依次為：

在索引中使用WHERE條件來過濾不匹配的記錄。這是在存儲引擎層完成的。
使用索引覆蓋掃描來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。這是在MySQL服務器層完成的，但無須在回表查詢記錄。
從數據表中返回數據，然后過濾不滿足條件的記錄。這是在MySQL服務器層完成，MySQL需要先從數據表讀出記錄然后過濾。

如果說發現查詢需要掃描大量的數據但只返回少數的行，那么通常可以嘗試下面的技巧去優化它：

使用索引覆蓋掃描，把所有需要用到的列都放到索引中，這樣存儲引擎無須回表獲取對應行就可以返回結果
改變庫表結構。例如使用單獨的匯總表
重寫這個復雜的查詢，讓MySQL優化器能夠以更優化的方式執行這個查詢

三、重構查詢方式

3.1 一個復雜查詢還是多個簡單查詢

MySQL內部每秒能夠掃描內存中上百萬行數據，相比之下，MySQL響應數據給客戶端就慢得多了。在其他條件都相同的時候，使用盡可能少的査詢當然是更好的。但是有時候, 將一個大査詢分解為多個小査詢是很有必要的。

3.2 切分查詢：將大查詢切分成小查詢，每個查詢完全一樣，只完成一小部分，每次只返回一小部分查詢結果

有時候對于一個大査詢我們需要“分而治之”，將大査詢切分成小査詢，每個査詢功能完全一樣，只完成一小部分，每次只返回一小部分査詢結果。

刪除舊的數據就是一個很好的例子。定期地清除大量數據時，如果用一個大的語句一次性完成的話，則可能需要一次鎖住很多數據、占滿整個事務日志、耗盡系統資源、阻塞很多小的但重要的査詢。將一個大的DELETE語句切分成多個較小的查詢可以盡可能小地影響MySQL性能，同時還可以減少MySQL復制的延遲。

3.3 分解關聯查詢

對每一個表進行一次單表查詢，然后再應用程序中進行關聯，例如

mysql> SELECT * FROM tag
-> JOIN tag_post ON tag_post.tag_id=tag.id
-> JOIN post ON tag_post.post_id=post.id
-> WHERE tag.tag='mysql';

可以分解成下面的語句來代替

mysql> SELECT * FROM tag WHERE tag='mysql';
mysql> SELECT * FROM tag_post WHERE tag_id=1234；
mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098,8904)；

使用分解關聯查詢的方式重構查詢有如下的優化：

讓緩存的效率更高。
將查詢分解后，執行單個查詢可以減少鎖的競爭。
在應用層做關聯，可以更容易的對數據庫進行拆分，更容易做到高性能和可擴展。
查詢本身效率也可能會有所提升。
可以減少冗余記錄的查詢。
這樣做相當于在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯。

四、查詢執行的基礎

查詢執行路徑

步驟：

客服端發送一條查詢給服務器
服務器先檢查查詢緩存，如果命中緩存，則立刻返回存儲在緩存中的結果。否則進入下一個階段。
服務器端進行SQL解析、預處理，在由優化器生成對應的執行計劃。
MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢
將結果返回給客戶端

4.1 MySQl客戶端/服務器通信協議

4.1.1、MySQL客戶端和服務器之間的通訊是”雙半工“的，這意味著，在任何一個時刻，要么是由服務器向客戶端發送數據，要么是由客戶端向服務器發送數據，這兩個動作不能同時發生。

4.2.2、查詢狀態：對于一個MySQL連接，或者說一個線程，任何時刻都有一個狀態，表示MySQL當前在做什么。我們使用最簡單的SHOW FULL PROCESSLIST命令（該命令返回結果中的Command列就表示當前的狀態）來查詢。下面將這些狀態列出來，并做一個簡單的解釋：

Sleep：線程正在等待客戶端發送新的請求。
Query：線程正在執行查詢或者正在將結果發送給客戶端。
Locked：在MySQL服務器層，該線程正在等待表鎖。
Analyzing and statistics : 線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃。
Coping to tmp table [on disk]：線程正在執行查詢，并且將其結果都復制到一個臨時表中，這種狀態一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果這個狀態后面還有"on disk"標記，那表示MySQL正在講一個內存臨時表放到磁盤上。
Sorting result：線程正在對結果集進行排序。
Sending data：這表示多種情況：線程可能在對多個狀態之間傳輸數據，或者而在生成結果集，或者在向客戶端返回數據。

4.2 查詢緩存

在解析一個查詢語句之前，如果查詢緩存是打開的，那么MYSQL會優先檢查這個查詢是否命中查詢緩存中的數據。

這個檢查是通過一個對大小寫敏感的哈希查找的。查詢和緩存中的查詢即使只有一個不同，也不會匹配緩存結果。如果命中緩存，那么在但會結果前MySQL會檢查一次用戶權限，有權限則跳過其他步驟直接返回數據

4.3 查詢優化處理

查詢的生命周期的下一步是將一個SQL轉換成執行計劃，MySQL再依照這個執行計劃和存儲引擎進行交互。

4.3.1 語法解析器和預處理

MySQL解析器將使用MySQL語法規則驗證和解析查詢。例如驗證是否使用錯誤的關鍵字、關鍵字順序、引號前后是否匹配等，預處理器則根據一些MySQL 規則進一步解析樹是否合法，例如檢查數據表和數據列是否存在，解析名字和別名是否有歧義等

4.3.2 查詢優化器

一條查詢可以有很多種執行方式，最后都返回相同的結果。優化器的作用就是找到其中最好的執行計劃

有很多中原因導致MySQL優化器選擇錯誤的計劃，如下所示：

統計信息不準確：MySQL依賴存儲引擎提供的統計信息來評估成本，但是有的存儲引擎提供的信息偏差有點大，例如InnoDB因為其MVCC的架構，并不能維護一個數據表的行數的精確統計信息

執行計劃中的成本估算不等于實際的操作成本
MySQL的最優可能和你想的最優不一樣
MySQL從不考慮其他并發執行的查詢
MySQL也并不是任何時候都是基于成本的優化
MySQL不會考慮不受其控制的操作成本。例如執行存儲過程或者用戶自定義函數的成本
優化器有時間無法估算所有可能的執行計劃

MySQL的查詢優化器使用很多策略來生成一個最優的執行計劃。

優化策略可以簡單的分為兩種

靜態優化：靜態優化可以直接對解析樹進行分析，并完成優化。例如優化器可以通過簡單的代數變化將WHERE條件轉換成另外一種等價形式，靜態優化在第一次完成后就一直有效，即使使用不同的參數重復執行查詢也不會變化。可以認為是一種”編譯時優化“
動態優化：和查詢的上下文有關，也可能和其他因素有關，例如WHERE中取值、索引中條目對應的數據行數等。這需要在每次查詢的時候重新評估，可以讓那位u是”運行時優化“。

MySQL能夠處理的優化類型（部分）：

重新定義關聯表順序
將外連接轉化成內連接
使用等價變換規則
優化COUNT() 、MIN() 、 MAX()
預估并轉換為常數表達式
覆蓋索引掃描
子查詢優化
提前終止查詢
等值傳播
列表IN()的比較

4.3.3 數據和索引的統計信息

在服務器層有查詢優化器，卻沒有保存數據和索引的統計信息。統計信息由存儲引擎實現，不同的存儲引擎可能會存儲不同的統計信息，有的引擎根本不存儲任何統計信息，例如Archive引擎。

因為服務器層沒有任何統計信息，所有MySQL查詢優化器在生成查詢的執行計劃時，需要向存儲引擎獲取相應的統計信息，優化器根據這些信息來選擇一個最優的執行計劃。

4.3.4 MySQL如何執行關聯查詢

MySQL中“關聯”認為任何一個查詢都是一次“關聯”，并不僅僅是一個查詢需要到兩個表匹配才叫關聯。素以在MySQL中，每一個查詢，每一個片段（包括子查詢，甚至于單表的SELECT）都可能是關聯。

MySQL關聯查詢的策略很簡單：MySQL對任何關聯都執行嵌套循環關聯操作，即MySQL先在要給表中循環取出單條數據，然后再嵌套循環到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然后根據各個表的行，返回查詢中需要的各個列。

4.3.5 執行計劃

和很多其他關系數據庫不同，MySQL并不會生成查詢字節碼來執行查詢。MySQL生成查詢的一顆指令樹，然后通過存儲引擎執行完成這顆樹并返回結果

4.3.6 關聯查詢優化器

如果優化器給出的并不是最優的關聯順序，這時可以使用STRAIGHT_JOIN關鍵字重寫查詢，讓優化器按照你認為最優的關聯順序執行——不過老實說，人的判斷很難那么精準。絕大多數時候，優化器做出的選擇都比普通人的判斷更精準。

如果超過N個表的關聯，那么需要檢查N的階乘種關聯順序。我們稱之為所有可能的執行計劃的“搜索空間‘，當搜索空間非常大的時候，優化器選擇使用”貪婪“搜索方式查找”最優’的關聯順序。當關聯的表超過optimizer_search_depth的限制的時候，就會選擇“貪婪”搜索模式了。

4.3.7 排序優化

排序優化：無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應盡可能避免排序或者盡可能避免對大量數據進行排序。盡量通過索引進行排序。當不能使用索引生成排序結果的時候，MySQL需要自己
進行排序，如果數據量小則在內存中進行，如果數量大則需要使用磁盤，不過MySQL將這個過程統一稱為文件排序，即使完全是內存排序不需要任何磁盤文件時也是如此。

MySQL有如下兩種排序算法：

兩次傳輸排序(舊版本使用)：讀取行指針和需要排序的字段，對其進行排序，然后再根據排序結果讀取所需要的數據行。需要進行兩次傳輸，即需要從數據表中讀取兩次數據，第二次讀取數據的時候，因為是讀

取排序列進行排序后的所有記錄。這回產生大量的隨機IO。
單次傳輸排序(新版本使用)：先讀取查詢所需要的所有列，然后在根據給定列進行排序，最后直接返回排序結果。效率更高，但占用內存更大。

如果查詢中有LIMIT的話，LIMIT也會在排序之后應用的，所以即使需要返回較少的數據，臨時表和需要排序的數據量仍然后非常大。貌似5.6版本有所改進，會先拋棄不滿足條件的記錄，然后再進行排序。

4.4 查詢執行引擎

在解析和優化階段，MySQL將生成查詢對應的執行計劃，MySQL的查詢執行引擎則根據這個執行計劃來完成整個查詢。這里執行計劃是一個數據結構，而不是和很多其他的關系型數據庫那樣會生成對應的字節碼。

4.5 返回結果給客戶端

即使查詢不需要返回結果集給客戶端，MySQL仍然會返回這個查詢的一些信息，如查詢影響到的行數。如果查詢可以被緩存，那么MySQL在這個階段也會將結果存放到緩存中。

MySQL將結果集返回客戶端是一個增量、逐步返回的過程。開始生成第一條結果時，MySQL就開始向客戶端逐步返回結果集了。

五、優化特定類型的查詢

5.1、優化COUNT()查詢

? 1). COUNT()是一個特殊的函數，有兩種非常不同的作用：它可以統計某個列值的數量，也可以統計行數。在統計列值的時候要求列值是非空的(不統計NULL)。如果COUNT()的括號中指定了列或者列的表達式，則

? 統計的就是這個表達式有值的結果數。最簡單的就是我們使用count(*)的時候，這種情況下通配符*并不會向我們猜想的那樣擴展所有的行，實際上，它會忽略所有的值而直接統計所有的行數。

? 2). 使用近似值：有時候某些業務場景并不要求完全精確的COUNT值，此時可以用近似值來代替。

? 3). 更復雜的優化：覆蓋索引，增加匯總表等。**

5.2 優化關聯查詢：

? 1). 確保ON或者USING子句中的列上有索引。在創建索引的時候就要考慮到關聯的順序。當表A和表B用到列C關聯的時候，如果優化器關聯順序是B、A，那就不需要在B表的對應列上建立索引。沒有用到的索引只會

? 2). 確保任何的GROUP BY 和ORDER BY中的表達式只涉及到一個表中的列。這樣MySQL才有可能使用索引來優化這個過程。**

5.3 優化子查詢：

關于優化子查詢我們給出的最重要的優化建議就是盡可能使用關聯查詢代替，至少當前MySQL版本需要這樣。

5.4 優化GROUP BY和DISTINCT：

? 1). 它們都可以使用索引來優化，這也是最有效的方法。

? 2). 在MySQL中，當無法使用索引的時候，GROUP BY使用兩種策略來完成：使用臨時表或文件排序來做分組。對于任何查詢語句，這兩種策略的性能都有可以提升的地方。可以通過使用提示SQL_BIG_RESULT和

? SQL_SMALL_RESULT來讓優化器按你希望的方式運行。

? 3). 如果需要對關聯查詢分組(GROUP BY)，并且是按照查找表中的某個列進行分組，那么通常采用查找表的標識列分組的效率比其他列更高。**

? 4). 如果沒有通過ORDER BY子句顯式地指定排序列，當查詢使用GROUP BY 子句的時候，結果集會自動按照分組的列進行排序。如果不關心結果集的順序，而這中默認排序又導致了需要文件排序，則可以使用

? ORDER BY NULL，讓MySQL文件不再進行排序。也可以在GROUP BY子句中直接使用DESC或者ASC關鍵字，使分組的結果集按照需要的方向排序。

? 5). 優化GROUP BY WITH ROLLUP：分組查詢的一個變種思想就是要求MySQL對返回的分組結果再做一次超級聚合。最好的辦法盡可能的將WITH ROLLUP 功能轉移到應用程序中處理。**

5.5 優化LIMIT分頁：

? 1). 使用索引

? 2). 要優化這種查詢，要么是在頁面中限制分頁的數量，要么是優化大偏移量的性能。

? 3). 盡肯能的使用索引覆蓋

? 4). 延遲關聯

? 5). 有時候也可以將LIMIT查詢轉換為已知位置的查詢，讓MySQL通過范圍掃描找到對應的結果。

? 6). 其他優化辦法還包括使用預先計算的匯總表，或者關聯一個冗余表，冗余表只包含主鍵列和需要做排序的數據列。

5.6 優化SQL_CALC_FOUND_ROWS：

分頁的時候，另一個常用的技巧是在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示(hint)，這樣就可以獲得去掉LIMIT以滿足條件的行數，因此可以作為分頁的總數。

? 用業務的手段解決：下一頁，獲取更多數據等。

5.7 優化UNION查詢：

? 1). MySQL總是通過創建填充臨時表的方式來執行UNION查詢。因此很多優化策略在UNION查詢中都沒法很好地使用。經常需要手工地將WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各個子查詢中，以

? 便優化器可以充分利用這些條件進行優化。

? 2). 除非確實需要服務器消除重復的行，否則就一定要使用UNION ALL，這一點很重要。如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這回導致對臨時表做唯一性檢查。這樣做的代價非常高，

? 即使有ALL關鍵字，MySQL仍然會使用臨時表存儲結果。事實上，MySQL總是經結果放入臨時表，然后再讀出，再返回給客戶端。**

5.8 靜態查詢分析：

Percona Toolkit中的pt-query-advisor 能夠解析查詢日志、分析查詢模式，然后再給出所有可能存在的潛在問題的查詢，并給出足夠詳細的建議。這像是給MySQL所有的查詢做一次全面的健康

? 檢查，它能檢測出很多問題。

六、總結

如果把創建高性能應用程序比作是一個環環相扣的“難題”，除了前面介紹的schema. 索引和査詢語句設計之外，査詢優化應該是解開“難題”的最后一步了。要想寫一個好的査詢，你必須要理解schema設計、索引設計等，反之亦然。

理解査詢是如何被執行的以及時間都消耗在哪些地方，這依然是前面我們介紹的響應時間的一部分。再加上一些諸如解析和優化過程的知識，就可以更進一步地理解上一章討論的MySQL如何訪問表和索引的內容了。這也從另一個維度幫助讀者理解MySQL在訪問表和索引時査詢和索引的關系。

參考：

《高性能 MySQL 第三版》