[[403535]]

本文轉載自微信公眾號「小豬notebook」，作者鐘Ger。轉載本文請聯系小豬notebook公眾號。

上一期我們深入理解了MySQL的索引，有了MySQL索引可以一定程度上提高MySQL的查詢速度。這一期我們來學習下MySQL查詢性能優化的一般方法。

1 為什么查詢速度會慢?

其實很簡單，在一個應用程序中，我們要查詢一些數據，通常是從客戶端出發，請求經過網絡傳輸到達服務端后，在服務端進行解析，然后把查詢命令發送給MySQL，MySQL經過一系列解析、優化等，最終將結果查詢出來，返回給客戶端，最終給到我們用戶。

在這一系列操作里，由于網絡時延、CPU、內存、鎖競爭、系統調用、上下文切換、存儲引擎檢索數據觸發的一系列操作等，查詢的速度或多或少會受到一定的影響，條件不利時，查詢速度就會變慢。

大致地知道查詢速度受到這些因素影響后，我們就可以找到優化查詢速度的一些方向了。

2 慢查詢基礎：優化數據訪問

查詢性能低的最基本原因是訪問的數據太多，我們可以通過以下兩步來分析低效查詢：

• 確認應用程序是否在檢索大量超過需要的數據行
• 確認MySQL服務器層是否在分析大量超過需要的數據行

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

應用程序從MySQL查詢請求了較多不需要數據時，這些多余數據其實會在應用程序的邏輯層中被丟棄掉，這種多余的操作會給MySQL服務器帶來額外的負擔，并增加網絡開銷，還會消耗應用服務器的CPU和內存資源。以下幾種情況均是這種類型：

• 查詢了不需要的記錄。像應用程序select * 時其實并不需要全部的數據，卻沒有去加limit進行限制，從而把全表的數據都撈出來，顯然多此一舉。
• 多表關聯時返回全部列。多個表進行關聯，像一些沒有什么意義的列也全部查出來，也是會影響性能。
• 總是取出全部的列。像select * 這類查詢，取出全部列，不一定會完成索引覆蓋這類優化，會觸發各種回表查詢，為服務器帶來額外的IO、內存和CPU消耗。
• 重復查詢相同的數據。對于每次查詢都返回同樣結果的這類查詢，其實查一遍就夠了，把結果存到Redis這類緩存中，減輕MySQL的壓力。

2.2 MySQL是否掃描了額外的記錄

對于MySQL，衡量性能開銷的三個指標是：響應時間、掃描行數、返回行數。

響應時間

響應時間的分類

掃描的行數與返回的行數

理想情況下，掃描的行與返回的行之間的比率通常要小，MySQL額外掃描的記錄就少。

掃描的行數與訪問類型

在評估查詢開銷時，需要考慮下從表中找到某一行數據的成本。MySQL有些訪問方式可能要掃描很多行才能返回一行結果。

使用explain語句中的type列反應了訪問類型。 訪問類型有索引掃描、范圍掃描、唯一索引查詢、常數引用等。

一般MySQL能使用這下列三種方式應用where條件，從好到壞依次為：

• 在索引中使用where條件來過濾不匹配的記錄，這是在存儲引擎完成的
• 使用索引覆蓋掃描(在extra列中出現using index)來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。這是在MySQL服務器層完成的
• 從數據表中返回數據，然后過濾不滿足條件的記錄(在extra列中出現using where)，這在MySQL服務器層完成，MySQL需要先從數據表讀出記錄然后過濾

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

• 使用索引覆蓋掃描，把需要查詢到的列都放到索引中，這樣存儲引擎就無須回表查詢就可以返回結果
• 改變庫表結構，使用一些匯總表來存儲結果，來避免各種聯合查詢
• 重寫復雜的查詢，讓MySQL優化器可以以更優化的方式執行這個查詢

3 重構查詢的方式

3.1 一個復雜查詢or多個簡單查詢

MySQL其實在設計上是讓連接和斷開都很輕量級，在返回一個小的查詢結果方面很高效。如果想用一個復雜的查詢，而這個查詢涉及了多個表的關聯，那其實性能還遠不如將這個查詢分解成的多個簡單查詢。

因此，一般情況下，能用多個簡單查詢，就不要用一個復雜查詢。

3.2 切分查詢

對于一個大查詢可以采用分而治之，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。

3.3 分解關聯查詢

將MySQL多表關聯查詢拆分成多個單表查詢，然后將查詢結果在應用程序邏輯層進行處理，可以提升性能。優勢如下：

• 查詢分解后，執行單個查詢可以減少鎖競爭。這是因為復雜的關聯查詢時間一般較長，事務之間的鎖競爭一般會更激烈，鎖等待時間一般也會更長
• 在應用程序邏輯層進行數據結果關聯，可以更容易做分庫分表、提高性能和擴展性
• 查詢本身效率也會有所提升，單表查詢走索引的SQL語句更容易編寫、使用in()代替關聯查詢可以讓MySQL按照id順序進行查詢，這會比隨機關聯更高效(后面會介紹到)
• 可以減少冗余記錄的查詢，多表關聯查詢時可能會重復地訪問一部分數據，而應用程序邏輯層關聯，只需要MySQL將某部分數據只查詢一次返回給應用程序即可
• 讓緩存的效率更高，應用程序邏輯層可以方便地緩存單表查詢對應的結果;對于在MySQL的查詢緩存而言，如果關聯查詢中某個表發生了變化，那么查詢緩存就失效了

4 查詢執行的基礎

前面講了這么多關于查詢優化的內容，現在我們了解下MySQL執行一個查詢的過程：

查詢SQL執行路徑

有一個大致的流程后，我們來具體看下每一步的細節。

4.1 MySQL客戶端與服務端之間的通信協議

由于客戶端與服務端之間傳輸的數據都必須是要完整可靠的，顯然是使用TCP協議來建立連接。

MySQL客戶端與服務端需要進行通信，在任意一個時刻，要么是服務端發送數據給客戶端，要么是客戶端發送數據給服務端，即半雙工通信。

這種通信協議讓MySQL客戶端與服務端之間通信簡單，但也限制了MySQL，例如一端必須完整地接受了另外一端發送來的數據，才能夠給另外一端響應數據，就當我們使用像DataGrip、Navicat等客戶端連接好MySQL服務端時，我們要select * from一張數據量很大的表，那么我們只能等服務端返回結果了。這一個查詢請求占用了大量的資源，如果有很多個這樣的查詢請求，那MySQL服務端的壓力肯定是很大的咯。所以，從數據庫撈全表的數據而不使用limit加以限制，客戶端和服務端都很難頂的。

書中講到：當客戶端從服務端獲取數據時，看起來是一個拉數據的過程，實際上是服務端在向客戶端推送數據的過程。客戶端不斷地接受從服務端推送來的數據，且沒辦法讓服務端停下來，像從消防水管喝水一樣。

所以通常，使用查詢緩存可以減少服務器壓力，讓查詢早點結束并釋放相關資源。

查詢狀態

對于每一個MySQL連接，也可以說一個線程，任意時刻都有一個狀態，該狀態表示了MySQL當前正在做的事情。

• sleep 線程正在等待客戶端發送新的請求
• query 線程正在執行查詢或將查詢結果返回給客戶端
• locked MySQ服務器層，表示線程正在等待表鎖
• analyzing and statistics 線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃
• copying to tmp table [on disk] 線程正在執行查詢，并且將其結果集都復制到一個臨時表中(group by、文件排序、union操作等)，若有[on disk]標記，則表示MySQL正將一個內存臨時表放到磁盤上
• sorting result 線程對結果集進行排序
• sending data 線程在多個狀態間傳送數據;或在生成結果集;或在向客戶端返回數據

4.2 查詢緩存

在解析一個查詢語句前，若MySQL的查詢緩存功能開啟，那么MySQL會優先檢查該查詢是否命中查詢緩存中的數據。如果命中了查詢緩存，則返回結果;若未命中，則繼續后續流程。

4.3 查詢優化處理

查詢優化處理分為多個子階段：解析SQL、預處理、優化SQL執行計劃。

4.3.1 語法解析器和預處理

MySQL通過關鍵字將SQL語句進行解析，生成一棵對應的解析樹，MySQL解析器將使用MySQL語法規則驗證和解析查詢。

預處理器根據MySQL規則進一步檢查解析樹是否合法，如將檢查數據表和列屬否存在、解析名字和別名、看看是否有歧義。接下來會進一步驗證權限。

4.3.2 查詢優化器

查詢優化器的主要作用是找到執行一條SQL語句的最好執行計劃。MySQL使用基于成本的優化器，它將嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個。

優化器的評估成本時對要進行的隨機IO次數的統計信息計算主要是受到每個表或索引頁個數、索引基數、索引分布和數據行的長度、索引分布情況等影響。優化器在評估成本時并不會考慮任何層面的緩存，它假設讀取任何數據都需要一次磁盤IO。

MySQL優化器可能選擇錯誤的執行計劃的情況：

• 統計隨機IO次數的信息不準確。受存儲引擎的影響，例如InnoDB因為MVCC機制，不能很好地維護一個數據表行數的精確統計信息
• 執行計劃中的成本估算并不等同于實際執行的成本。因為MySQL分析執行計劃時并不知道哪些頁在內存、哪些頁在磁盤，所以在查詢的執行過程中具體需要多少次磁盤IO是很難估計準確的
• MySQL是基于成本模型而選擇最優的執行計劃，而我們用戶是希望查詢時間盡可能短
• MySQL不考慮并發執行的查詢，這可能會在實際執行過程中影響查詢的速度
• 有些無法預知的問題也會影響MySQL實際的執行

在這里插入圖片描述

MySQL能夠處理的優化類型：

• 重新定義關聯表的順序。可能SQL語句是select * from a inner join b on a.id = b.id，但MySQL查詢優化器關聯表的順序可能是先b表后a表。
• 將外連接轉化為內連接。可能outer join會因為where條件、庫表的結構被MySQL優化器變成一個內連接。
• 使用等價變換規則，簡化表達式。例如：5=5 and a>5可以被簡化為a>5
• 可以優化count()、min()、max()。MySQL的索引在此起到了極大的作用，例如查找某一列的最小值，只需要查詢B+樹最左端的葉子節點并直接獲取葉子節點的第一條記錄即可。同理，查找某一列的最大值，只需要查詢B+樹最右端的葉子節點并直接獲取葉子節點的最后一條記錄即可。對于統計count()，不同類型的存儲引擎有不同的優化方式，例如MyISAM維護了一個變量來存表的記錄數，這使得count計數的時間復雜度直接優化成O(1)。
• 預估并轉換為常數表達式。例如where 1=1，此外，使用了主鍵或者唯一索引也可以轉為常數表達式。例如：select a.name, b.money from a inner join b using(a.id) where a.id = 2021，a表的id是主鍵索引，且a.id有一個確定的值為2021，那就將a表中返回的a.id為2021這一列的記錄當作是一個常數來處理，然后再去b表進行查詢。同時，using子句也讓MySQL知道a.id是一個常量。
• 覆蓋索引。當索引中的列包含要查詢的列時，MySQL會直接使用索引來返回需要的數據。
• 子查詢優化。MySQL會將子查詢轉換成高效的形式，從而避免多個查詢多次對數據進行訪問。
• 提前終止查詢。當MySQL發現已經滿足查詢需求時或者查詢條件不成立等情況，MySQL就會終止查詢。如：使用limit;where條件不成立，MySQL會立即返回空結構;存儲引擎檢索不同取值或者判斷值的存在性等。
• 等值傳播。若兩個列的值通過等值關聯，MySQL能夠把其中一個列的where條件傳遞到另外一列上。例如：select * from a inner join b on a.id = b.id where a.id = 2021，a表與b表通過相同的id關聯，a.id = 2021這個條件同樣適用于b表。
• 列表IN()的比較。MySQL將IN中的數據先排序，然后通過二分查找的方式來確定IN中的條件是否滿足條件，這就是一個O(log n)的操作了。所以當表有大量數據時，查詢條件有多個，可以考慮使用IN語句來優化查詢。

4.3.3 關聯優化

MySQL在執行連接查詢時，往往會先執行子查詢，并將子查詢的結果存放到一個臨時表中，然后將臨時表中的結果當作條件來執行父查詢。MySQL的優化器會對select a.id, b.name from a inner join b on a.id = b.id之類的關聯查詢進行優化。優化器會對多個表關聯時的順序進行優化，它通過評估不同順序時的成本來選擇一個代價最小的關聯順序來執行查詢。

4.3.4 排序優化

排序是一個成本很高的操作，故從性能上來講，應該盡可能地避免排序或對大量數據進行排序。當MySQL不能使用索引進行排序時，它需要進行文件排序(數據量小在內存中進行，數據量大需要使用磁盤)。

如果需要排序的數據量小于“排序緩沖區”，MySQL使用內存進行快速排序。如果內存不夠排序，MySQL會將數據分塊，對每個獨立的塊使用快速排序，并將各個塊的排序結果存放在磁盤上，然后將各個排序的塊進行合并，最后返回排序結果。

MySQL 5.6以上，排序的算法是單次傳輸排序：先讀取查詢所需要的所有列，然后再根據給定列進行排序，最后直接返回排序結果。雖然這個排序算法只需要一次順序IO讀取所有的數據，但如果需要返回的列非常多、非常大，會額外占用大量的空間，而這些列對排序操作本身是沒有什么用的，所以有利有弊吧。

值得一提的是，在關聯查詢的時候如果需要排序，MySQL會分情況來處理這樣的文件排序

如果order by子句中的所有列都來自于關聯的第一個表，那么MySQL在關聯處理第一個表時就會進行文件排序

除此之外，MySQL會將關聯的結果先存放到一個臨時表中，然后再進行文件排序

4.4 查詢執行引擎

在解析和優化階段，MySQL將生成查詢對應的執行計劃，MySQL的查詢執行引擎則根據這個執行計劃來完成整個查詢。查詢執行引擎會根據執行計劃給出的指令逐步執行。在執行過程中，有大量操作需要通過調用存儲引擎實現的接口來完成，接口稱為“handler API”。MySQL在優化階段就為每個表創建了一個handler實例，優化器會根據這些實例的接口獲取表的相關信息(列名、索引統計信息等)。

并非所有操作均有handler完成。例如，MySQL需要進行表鎖時，handler可能會實現特定級別、更細粒度的鎖，如InnoDB就實現了自己的行基本鎖，但這并不能代替服務器層的表鎖。

4.5 返回結果給客戶端

查詢執行的最后一個階段是將結果返回給客戶端，即使查詢無需返回結果集，MySQL仍然會返回查詢的一些信息，例如查詢影響到的行數等。

若查詢可以被緩存，MySQL返回結果給客戶端前會將結果存儲到查詢緩存中。

MySQL將結果集返回給客戶端是一個增量、逐步返回的過程。這樣處理的好處是：服務端無需存儲太多結果，也不會因為要返回太多結果而消耗太多內存;客戶端也能夠快速地獲取到返回的結果。

結果集中的每一行都會以一個滿足MySQL客戶端/服務端通信協議的封包發送，然后通過TCP協議傳輸，在TCP傳輸過程中，可能對MySQL的封包進行緩存然后批量傳輸。

小結

本期主要對MySQL的查詢過程進行了簡要的梳理，理解了一條SQL執行的過程需要經過MySQL的各種組件，下一期，我們將重點探索下MySQL查詢性能優化的方法。我是Zhongger，一個在互聯網公司摸魚寫代碼的打工人，你們的支持是我創作的最大動力，我們下期見～