《高效能MySQL》讀書筆記－－查詢效能最佳化，高效能mysql

《高效能MySQL》讀書筆記－－查詢效能最佳化，高效能mysql

對於高效能資料庫操作，只靠設計最優的庫表結構、建立最好的索引是不夠的，還需要合理的設計查詢。如果查詢寫得很糟糕，即使庫表結構再合理、索引再合適，也無法實現高效能。查詢最佳化、索引最佳化、庫表結構最佳化需要齊頭並進，一個不落。

6.1 為什麼查詢速度會慢

通常來說，查詢的生命週期大致可以按照順序來看：從用戶端>>伺服器>>在伺服器上進行解析>>產生執行計畫>>執行>>返回結果給用戶端。其中執行可以認為是整個生命週期中最重要的階段，這其中包括了大量為了檢索資料到儲存引擎的調用以及調用後的資料處理，包括排序、分組等。瞭解查詢的生命週期、清楚查詢的時間消耗情況對於最佳化查詢有很大的意義。

6.2 最佳化資料訪問

查詢效能低下的最基本的原因是訪問的資料太多。大部分效能低下的查詢都可以通過減少訪問的資料量的方式進行最佳化。

1.確認應用程式是否在檢索大量超過需要的資料。這通常意味著訪問了太多的行，但有時候也可能是訪問了太多的列。

2.確認MySQL伺服器層是否在分析大量超過需要的資料行。

6.2.1 是否向資料庫請求了不需要的資料

請求多餘的資料會給MySQL伺服器帶來額外的負擔，並增加網路開銷，另外也會消耗應用伺服器的CPU記憶體和資源。這裡有一些典型案例：

1、查詢不需要的記錄：例如在新聞網站中取出100條記錄，但是只是在頁面上顯示10條。實際上MySQL會查詢出全部的結果烏鴉，用戶端的應用程式會接收全部的結果集資料，然後拋棄其中大部分資料。最簡單有效解決方案就是在這樣的查詢後面加上LIMIT。

2、多表關聯時返回全部列，例如：

3、總是取出全部的列：每次看到SELECT *的時候都需要懷疑是不是真的需要返回全部的列？取出全部列，會主最佳化器無法完成索引覆蓋掃描這類最佳化，還會為伺服器帶來額外的IO、記憶體和CPU的消耗。如果應用程式使用了某種緩衝機制，或者有其他考慮，擷取超過需要的資料也可能有其好處，但不要忘記這樣做的代價是什麼。擷取並緩衝所有的列的查詢，相比多個獨立的只擷取部分列的查詢可能就更有好處。

4、重複查詢相同的資料：不要不斷地重複執行相同的查詢，然後每次都返回完全相同的資料。當初次查詢的時候將這個資料緩衝起來，需要的時候從緩衝中取出，這樣效能顯然更好。

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

對於MySQL，最簡單的衡量查詢開銷的三個指標有：回應時間、掃描的行數、返回的行數。這三個指標都會記錄到MySQL的慢日誌中，所以檢查慢日誌記錄是找出掃描行數過多的查詢的好辦法。

回應時間

回應時間是兩個部分之和：服務時間和排隊時間，一般常見和重要的等待是IO和鎖等待。

掃描的行數和返回的行數

分析查詢時，查看該查詢掃描的行數是非常有協助的。一定程度上能夠說明該查詢找到需要的資料的效率高不高。理想的情況下掃描的行數和返回的行數應該是相同的。當然這隻是理想情況。一般來說掃描的行數對返回的行數的比率通常很小，一般在1：1到10：1之間。

掃描的行數和訪問類型

MySQL有好幾種訪問方式可以尋找並返回一行結果。有些訪問方式可能需要掃描很多行才能返回一行結果，也有些訪問方式可能無須掃描就能返回結果。

在EXPLAIN語句的TYPE列返回了訪問類型。如果查詢沒有辦法找到合適的訪問類型，那麼解決的最好辦法通常就是增加一個合適的索引。索引讓MySQL以最高效、掃描行最少的方式找到需要的記錄。

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

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

2、使用索引覆蓋掃描（在extra列中出現了using index）來返回記錄，直接從索引中過濾不需要的記錄並返回命中的結果。這是在MySQL伺服器層完成的，但無須回表查詢記錄。

3、從資料表中返回資料，然後過濾不滿足條件的記錄（在extra列中出現using where）。這在MySQL伺服器層完成，MySQL需要先從資料表讀出記錄然後過濾。

6.3 重構查詢的方式

6.3.1 一個複雜查詢還是多個簡單查詢

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

6.3.2 切分查詢

有時候對於一個大查詢我們需要“分而治之”，對於刪除舊資料，如果用一個大的語句一次性完成的話，則可能需要一次性鎖住很多資料、佔滿整個交易記錄、耗盡系統資源、阻塞很多小的但重要的查詢。將一個大的DELETE語句切分成多個較小的查詢可以儘可能小地影響MySQL效能，同時還可以減少MySQL複製的延遲。例如我們需要每個月運行一次下面的查詢：

那麼可以用類似下面的辦法來完成同樣的工作：

6.3.3 分解關聯查詢

乍一看這樣做並沒有什麼好處，但其有如下優勢：

1、讓緩衝的效率更高。對MySQL的查詢快取來說，如果關聯中的某個表發生了變化 ，那麼就無法使用查詢快取了，而拆分後，如果某個表很少改變，那麼該表的查詢快取能重複利用 。

2、將查詢後，執行單個查詢可以減少鎖的競爭。

3、查詢效能也有所提升，使用IN（）代替關聯查詢，可以讓MySQL按照ID順序進行查詢，這比隨機的關聯要更高效。

6.4 查詢執行的基礎

當希望MySQL能夠能更高的效能執行查詢時，最好的辦法就是弄清楚MySQL是如何最佳化和執行查詢的。

6.4.1 MySQL用戶端/服務端通訊協定

MySQL用戶端和伺服器之間的通訊協定是“半雙工”的，在任何一個時刻，要麼由伺服器向用戶端向服務端發送資料，要麼是由用戶端向伺服器發送資料，這兩個動作不能同時發生。

一旦用戶端發送了請求，它能做的事情就只是等待結果了，如果查詢太大，服務端會拒絕接收更多的資料並拋出相應錯誤，所以參數max_allowed_packet就特別重要。相反，一般伺服器響應給使用者的資料通常很多，由多個資料包組成。當伺服器開始響應用戶端請求時，用戶端必須完整地接收整個返回結果，而不能簡單地只取前面幾條結果，然後主伺服器停止發送資料。這種情況下，用戶端若接收完整的結果，然後取前面幾條需要的結果，或者接收完幾條結果然後粗暴地中斷連線，都不是好主意。這也是必要的時候需要在查詢中加上limit限制的原因。

換一種方式解釋這種行為：當用戶端從伺服器取資料時，看起來是一個拉資料的過程，但實際上是MySQL在向用戶端推資料的過程。用戶端不斷地接收從伺服器推送的資料，用戶端也沒法讓伺服器停下來。

當使用多數串連MySQL的庫函數從MySQL擷取資料時，其結果看起來都像是從MySQL伺服器擷取資料，而實際上都是從這個庫函數的緩衝擷取資料。多數情況下這沒什麼問題，但是如果需要返回一個很大的結果集時，這樣做並不好，因為庫函數會花很多時間和記憶體來儲存所有的結果集。如果能儘早開始處理這些資料，就能大大減少內在的消耗，這種情況下可以不使用緩衝來記錄結果而是直接處理。PHP的 mysql_query()，此時資料已經到了PHP的緩衝中，而mysql_unbuffered_query()不會緩衝結果。

查詢狀態：可以使用SHOW FULL PROCESSLIST命令查看查詢的執行狀態。Sleep、Query、Locked、Analyzing and statistics、Copying to tmp table[on disk]、Sorting result、Sending data

6.4.2 查詢快取

在解析一個查詢語句之前，如果查詢快取是開啟的，那麼MySQL會優先檢查這個查詢是否命中查詢快取中的資料。這是檢查是通過一個對大小寫敏感的雜湊尋找實現的。如果當前的查詢恰好命中了查詢快取，那麼在返回查詢結果之前MySQL會檢查一次使用者權限。如果許可權沒有問題，MySQL會跳過執行階段，直接從緩衝中拿到結果並返回給用戶端。

6.4.3 查詢最佳化處理

查詢生命週期的下一步是將一個SQL轉換成一個執行計畫，MySQL再依照這個執行計畫和儲存引擎進行互動。這包括多個子階段：解析SQL、預先處理、最佳化SQL執行計畫。

1、文法解析器和預先處理首先MySQL通過關鍵字將SQL語句進行解析，並產生一棵解析樹。MySQL解析器將使用MySQL文法規則驗證和解析查詢。例如是否使用錯誤的關鍵字，或者使用關鍵字的順序是否正確，引號是否能前後正確匹配等。

2、前置處理器則根據一些MySQL規則進一步檢查解析樹是否合法，例如檢查資料表和資料列是否存在，還會解析名字和別名看它們是否有歧義。

3、一下步預先處理會驗證許可權。

查詢最佳化工具：一條語句 可以有很多種執行方式，最後都返回相同的結果。最佳化器的作用就是找到最好的執行計畫。MySQL使用基於成本的最佳化器，它將嘗試預測一個查詢使用某種執行計畫時的成本，並選擇其中成本最小的一個。成本的最小單位是隨機讀取一個4K的資料頁的成本，並加入一些因子來估算某引動操作的代價。可以通過查詢當前會話的Last_query_cost的值來得知MySQL計算的當前查詢的成本。

這是根據一系列的統計資訊計算得來的：每個表或者索引的頁面個數、索引的基數（索引中不同值的數量）、索引和資料行的長度、索引分布情況。

當然很多原因會導致MySQL最佳化器選擇錯誤的執行計畫：例如統計資訊不準確或執行計畫中的成本估算不等同於實際執行的成本。

MySQL如何執行關聯查詢：MySQL對任何關聯都執行嵌套迴圈關聯操作，即MySQL先在一個表中迴圈取出單條資料，然後再嵌套迴圈到一個表中尋找匹配的行，依次下去直到找到的有匹配的行為止。然後根據各個表匹配的行，返回查詢中需要的各個列。（嵌套迴圈關聯）

執行計畫：MySQL產生查詢的一棵指令樹，然後通過儲存引擎執行完成這棵指令樹並返回結果。最終的執行計畫包含了重構查詢的全部資訊。如果對某個查詢執行EXPLAIN EXTENDED，再執行SHOW WARNINGS，就可以看到重構出的查詢。

MySQL的執行計畫是一棵左側深度優先的樹。

不過，如果有超過n個表的關聯，那麼需要檢查n的階乘種關聯順序。我們稱之為所有可能的執行計畫的“搜尋空間”。實際上，當需要關聯的表超過optimizer_search_depth的限制的時候，就會選擇“貪婪”搜尋模式。

排序最佳化：無論如何排序都是一個成本很高的操作，所以從效能角度考慮，應儘可能避免排序或者儘可能避免對大量資料進行排序。如果需要排序的資料量小於排序緩衝區，MySQL使用記憶體進行“快速排序”操作。如果記憶體不夠排序，那麼MySQL會先將資料分塊，對每個獨立的塊使用“快速排序”進行排序，並將各個塊的排序結果存放在磁碟上，然後將各個排序的塊進行合并，最手返回排序結果。

MySQL有兩種排序方法：

    兩次傳輸排序（舊版），讀取行指標和需要排序的欄位，對其進行排序，然後再根據排序結果讀取所需要的資料行。顯然是兩次傳輸，特別是讀取排序後的資料時（第二次）大量隨機I/O，所以兩次傳輸成本高。

    單次傳輸排序（新版），一次讀取出所有需要的或SQL查詢指定的列，然後根據排序列，排序，直接返回排序後的結果。順序I/O，缺點：如果列多，額外佔用空間。

MySQL在進行檔案排序時需要使用的臨時儲存空間可能會比想象的要大得多，因為MySQL在排序時，對每一個排序記錄都會分配一個足夠長的定長空間來存放。這個定長空間必須足夠以容納其中最長的字串。

在關聯查詢的時候如果需要排序，MySQL會分兩種情況來處理這樣的檔案排序。如果ORDER BY子句的所有列都來自關聯的第一個表，那麼MySQL在關聯處理第一個表時就進行檔案排序。如果是這樣那麼在MySQL的EXPLAIN結果中可以看到Extra欄位會有Using filesort。除此之外的所有情況，MySQL都會將關聯的結果存放在一個暫存資料表中，然後在所有的關聯都結束後，再進行檔案排序。這種情況下Extra欄位可以看到Using temporary;Using filesort。如果查詢中有LIMIT的話，LIMIT也會在排序之後應用，所以即使需要返回較少的資料，暫存資料表和需要排序的資料量仍然會非常大。

6.4.4 查詢執行引擎

相對於查詢最佳化，查詢執行簡單些了，MySQL只根據執行計畫輸出的指令逐步執行。指令都是調用儲存引擎的API來完成，一般稱為 handler API，實際上，MySQL最佳化階段為每個表都建立了一個 handler 執行個體，用 handler 執行個體擷取表的相關資訊（列名、索引統計資訊等）。

儲存引擎介面有著非常豐富的功能，但是底層介面卻只有幾十個，這些介面像搭積木一樣能夠完成查詢的大部分操作。例如，有一個查詢某個索引的第一行的介面，再有一個查詢某個索引條件的下一條目的功能，有了這兩個功能就可以完成全索引掃描操作。

6.4.5 返回結果給用戶端

查詢執行的最後一個階段就是將結果返回給用戶端。即使查詢不需要返回結果集給用戶端，MySQL仍然會返回這個查詢的一些資訊，例如該查詢影響到的行數。

MySQL將結果集返回用戶端是一個增量、逐步返回的過程。一旦伺服器處理完最後一個關聯表，開始產生第一條結果時，MySQL就可以開始向用戶端逐步返回結果集了。

這樣處理有兩個好處：服務端無須儲存太多的結果，也就不會因為要返回太多結果而消耗太多記憶體。另外，這樣的處理也讓MySQL用戶端第一時間獲得返回的結果。

當然，最佳化器存在其局限性，以及某些特定的最佳化類型，有興趣的可以在書中找到答案。