[[403535]]

本文轉(zhuǎn)載自微信公眾號「小豬notebook」，作者鐘Ger。轉(zhuǎn)載本文請聯(lián)系小豬notebook公眾號。

上一期我們深入理解了MySQL的索引，有了MySQL索引可以一定程度上提高M(jìn)ySQL的查詢速度。這一期我們來學(xué)習(xí)下MySQL查詢性能優(yōu)化的一般方法。

1 為什么查詢速度會慢?

其實很簡單，在一個應(yīng)用程序中，我們要查詢一些數(shù)據(jù)，通常是從客戶端出發(fā)，請求經(jīng)過網(wǎng)絡(luò)傳輸?shù)竭_(dá)服務(wù)端后，在服務(wù)端進(jìn)行解析，然后把查詢命令發(fā)送給MySQL，MySQL經(jīng)過一系列解析、優(yōu)化等，最終將結(jié)果查詢出來，返回給客戶端，最終給到我們用戶。

在這一系列操作里，由于網(wǎng)絡(luò)時延、CPU、內(nèi)存、鎖競爭、系統(tǒng)調(diào)用、上下文切換、存儲引擎檢索數(shù)據(jù)觸發(fā)的一系列操作等，查詢的速度或多或少會受到一定的影響，條件不利時，查詢速度就會變慢。

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

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

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

• 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)行
• 確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行

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

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

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

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

對于MySQL，衡量性能開銷的三個指標(biāo)是：響應(yīng)時間、掃描行數(shù)、返回行數(shù)。

響應(yīng)時間

響應(yīng)時間的分類

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

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

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

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

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

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

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

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

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

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

3.1 一個復(fù)雜查詢or多個簡單查詢

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

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

3.2 切分查詢

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

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

將MySQL多表關(guān)聯(lián)查詢拆分成多個單表查詢，然后將查詢結(jié)果在應(yīng)用程序邏輯層進(jìn)行處理，可以提升性能。優(yōu)勢如下：

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

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

前面講了這么多關(guān)于查詢優(yōu)化的內(nèi)容，現(xiàn)在我們了解下MySQL執(zhí)行一個查詢的過程：

查詢SQL執(zhí)行路徑

有一個大致的流程后，我們來具體看下每一步的細(xì)節(jié)。

4.1 MySQL客戶端與服務(wù)端之間的通信協(xié)議

由于客戶端與服務(wù)端之間傳輸?shù)臄?shù)據(jù)都必須是要完整可靠的，顯然是使用TCP協(xié)議來建立連接。

MySQL客戶端與服務(wù)端需要進(jìn)行通信，在任意一個時刻，要么是服務(wù)端發(fā)送數(shù)據(jù)給客戶端，要么是客戶端發(fā)送數(shù)據(jù)給服務(wù)端，即半雙工通信。

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

書中講到：當(dāng)客戶端從服務(wù)端獲取數(shù)據(jù)時，看起來是一個拉數(shù)據(jù)的過程，實際上是服務(wù)端在向客戶端推送數(shù)據(jù)的過程?？蛻舳瞬粩嗟亟邮軓姆?wù)端推送來的數(shù)據(jù)，且沒辦法讓服務(wù)端停下來，像從消防水管喝水一樣。

所以通常，使用查詢緩存可以減少服務(wù)器壓力，讓查詢早點(diǎn)結(jié)束并釋放相關(guān)資源。

查詢狀態(tài)

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

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

4.2 查詢緩存

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

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

查詢優(yōu)化處理分為多個子階段：解析SQL、預(yù)處理、優(yōu)化SQL執(zhí)行計劃。

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

MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，生成一棵對應(yīng)的解析樹，MySQL解析器將使用MySQL語法規(guī)則驗證和解析查詢。

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

4.3.2 查詢優(yōu)化器

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

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

MySQL優(yōu)化器可能選擇錯誤的執(zhí)行計劃的情況：

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

在這里插入圖片描述

MySQL能夠處理的優(yōu)化類型：

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

4.3.3 關(guān)聯(lián)優(yōu)化

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

4.3.4 排序優(yōu)化

排序是一個成本很高的操作，故從性能上來講，應(yīng)該盡可能地避免排序或?qū)Υ罅繑?shù)據(jù)進(jìn)行排序。當(dāng)MySQL不能使用索引進(jìn)行排序時，它需要進(jìn)行文件排序(數(shù)據(jù)量小在內(nèi)存中進(jìn)行，數(shù)據(jù)量大需要使用磁盤)。

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

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

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

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

除此之外，MySQL會將關(guān)聯(lián)的結(jié)果先存放到一個臨時表中，然后再進(jìn)行文件排序

4.4 查詢執(zhí)行引擎

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

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

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

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

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

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

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

小結(jié)

本期主要對MySQL的查詢過程進(jìn)行了簡要的梳理，理解了一條SQL執(zhí)行的過程需要經(jīng)過MySQL的各種組件，下一期，我們將重點(diǎn)探索下MySQL查詢性能優(yōu)化的方法。我是Zhongger，一個在互聯(lián)網(wǎng)公司摸魚寫代碼的打工人，你們的支持是我創(chuàng)作的最大動力，我們下期見～