一、 一條簡單SQL在MySQL執(zhí)行過程

一張簡單的圖說明下，MySQL架構(gòu)有哪些組件和組建間關(guān)系，接下來給大家用SQL語句分析

例如如下SQL語句

SELECT department_id FROM employee WHERE name = 'Lucy' AND age > 18 
GROUP BY department_id

其中name為索引，我們按照時(shí)間順序來分析一下

1. 客戶端：如MySQL命令行工具、Navicat、DBeaver或其他應(yīng)用程序發(fā)送SQL查詢到MySQL服務(wù)器。

2. 連接器：負(fù)責(zé)與客戶端建立連接、管理連接和維護(hù)連接。當(dāng)客戶端連接到MySQL服務(wù)器時(shí)，連接器驗(yàn)證客戶端的用戶名和密碼，然后分配一個(gè)線程來處理客戶端的請求。

3. 查詢緩存：查詢緩存用于緩存先前執(zhí)行過的查詢及其結(jié)果。當(dāng)收到新的查詢請求時(shí)，MySQL首先檢查查詢緩存中是否已有相同的查詢及其結(jié)果。如果查詢緩存中有匹配的查詢結(jié)果，MySQL將直接返回緩存的結(jié)果，而無需再次執(zhí)行查詢。但是，如果查詢緩存中沒有匹配的查詢結(jié)果，MySQL將繼續(xù)執(zhí)行查詢。

4. 分析器：

? 解析查詢語句，檢查語法。

? 驗(yàn)證表名和列名的正確性。

? 生成查詢樹。

5. 優(yōu)化器：分析查詢樹，考慮各種執(zhí)行計(jì)劃，估算不同執(zhí)行計(jì)劃的成本，選擇最佳的執(zhí)行計(jì)劃。在這個(gè)例子中，優(yōu)化器可能會(huì)選擇使用name索引進(jìn)行查詢，因?yàn)?code>name是索引列。

6. 執(zhí)行器：根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃，向存儲(chǔ)引擎發(fā)送請求，獲取滿足條件的數(shù)據(jù)行。

7. 存儲(chǔ)引擎（如InnoDB）：

? 負(fù)責(zé)實(shí)際執(zhí)行索引掃描，如在employee表的name索引上進(jìn)行等值查詢，因查詢?nèi)苛?，涉及到回表訪問磁盤。

? 在訪問磁盤之前，先檢查InnoDB的緩沖池（Buffer Pool）中是否已有所需的數(shù)據(jù)頁。如果緩沖池中有符合條件的數(shù)據(jù)頁，直接使用緩存的數(shù)據(jù)。如果緩沖池中沒有所需的數(shù)據(jù)頁，從磁盤加載數(shù)據(jù)頁到緩沖池中。

8. 執(zhí)行器：

? 對于每個(gè)找到的記錄，再次判斷記錄是否滿足索引條件name。這是因?yàn)榛谒饕龡l件加載到內(nèi)存中是數(shù)據(jù)頁，數(shù)據(jù)頁中也有可能包含不滿足索引條件的記錄，所以還要再判斷一次name條件，滿足name條件則繼續(xù)判斷age > 18過濾條件。

? 根據(jù)department_id對滿足條件的記錄進(jìn)行分組。

? 執(zhí)行器將處理后的結(jié)果集返回給客戶端。

在整個(gè)查詢執(zhí)行過程中，這些組件共同協(xié)作以高效地執(zhí)行查詢?？蛻舳素?fù)責(zé)發(fā)送查詢，連接器管理客戶端連接，查詢緩存嘗試重用先前查詢結(jié)果，解析器負(fù)責(zé)解析查詢，優(yōu)化器選擇最佳執(zhí)行計(jì)劃，執(zhí)行器執(zhí)行優(yōu)化器選擇的計(jì)劃，存儲(chǔ)引擎（如InnoDB）負(fù)責(zé)管理數(shù)據(jù)存儲(chǔ)和訪問。這些組件的協(xié)同作用使得MySQL能夠高效地執(zhí)行查詢并返回結(jié)果集。

根據(jù)索引列過濾條件加載索引的數(shù)據(jù)頁到內(nèi)存這個(gè)操作是存儲(chǔ)引擎做的。加載到內(nèi)存中之后，執(zhí)行器會(huì)進(jìn)行索引列和非索引列的過濾條件判斷。

二、 查詢SQL關(guān)鍵字執(zhí)行順序

執(zhí)行順序，如下：

1、對存儲(chǔ)引擎的操作

（1）FROM：用于查詢SQL的數(shù)據(jù)表。執(zhí)行器會(huì)根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃從存儲(chǔ)引擎中獲取相關(guān)表的數(shù)據(jù)。

（2）ON： 與JOIN一起使用，用于指定連接條件。執(zhí)行器會(huì)根據(jù)ON給定的條件條件從存儲(chǔ)引擎獲取匹配條件的記錄。如果連接條件涉及到索引列，存儲(chǔ)引擎會(huì)使用索引進(jìn)行優(yōu)化。

（3）JOIN：指定表之間連接方式（如INNER JOIN,LEFT JOIN等）。執(zhí)行器會(huì)根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃，從存儲(chǔ)引擎中獲取連接表數(shù)據(jù)。然后執(zhí)行器根據(jù)JOIN連接類型和ON連接條件，對數(shù)據(jù)連接處理。

（4）WHERE：執(zhí)行器對從存儲(chǔ)引擎返回的數(shù)據(jù)進(jìn)行過濾，只保留滿足WHERE子句條件的記錄。過濾條件如有索引，存儲(chǔ)引擎層會(huì)通過索引過濾后返回。

2、對返回結(jié)果集的操作

（5）GROUP BY：執(zhí)行器對滿足WHERE條件的記錄按照GROUP BY指定的列分組。

（6）HAVING：執(zhí)行器在執(zhí)行分組后，根據(jù)HAVING條件對分組后的記錄再次過濾。

（7）SELECT：執(zhí)行器根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃和指定列獲取查詢結(jié)果。

（8）DISTINCT：執(zhí)行器對查詢結(jié)果進(jìn)行去重，只返回不重復(fù)的記錄。

（9）ORDER BY：執(zhí)行器對查詢結(jié)果按照ORDER BY子句中指定的列進(jìn)行排序。

（10）LIMIT：執(zhí)行器根據(jù)LIMIT子句中指定的限制條件對查詢結(jié)果進(jìn)行截?cái)?，只返回部分記?/p>

三、表關(guān)聯(lián)查詢SQL在MySQL中的執(zhí)行過程

SELECT s.id, s.name, s.age, es.subject, es.score 
FROM employee s JOIN employee_score es ON s.id = es.employee_id 
WHERE s.age >18 AND es.subject_id =3 AND es.score >80;

這個(gè)例子中，subject_id和score是聯(lián)合索引，age是索引。 我們按照時(shí)間順序來分析一下

1. 連接器：當(dāng)客戶端連接到MySQL服務(wù)器時(shí)，連接器負(fù)責(zé)建立和管理連接。它驗(yàn)證客戶端提供的用戶名和密碼，確定客戶端具有相應(yīng)的權(quán)限，然后建立連接。

2. 查詢緩存：MySQL服務(wù)器在處理查詢之前，會(huì)先檢查查詢緩存。如果查詢緩存中已經(jīng)存在該結(jié)果集，服務(wù)器將直接返回緩存中的結(jié)果。

3. 解析器：解析并檢查SQL語法正確性。解析器會(huì)將查詢語句分解成多個(gè)組成部分，例如表、列、條件等。在這個(gè)示例中，解析器會(huì)識別出涉及的表（employee和employee_score）以及需要的列（id、name、age、subject、score）。

4. 優(yōu)化器：根據(jù)解析器提供的信息生成執(zhí)行計(jì)劃。優(yōu)化器會(huì)分析多種可能的執(zhí)行策略，并選擇成本最低的策略。在這個(gè)示例中，優(yōu)化器會(huì)選擇age索引和subject_id與score的聯(lián)合索引。對于連接操作，優(yōu)化器還要決定連接策略，例如是否使用Nested-Loop Join或Hash Join等一些連接策略。優(yōu)化器還會(huì)根據(jù)表的大小、索引、查詢條件和統(tǒng)計(jì)信息來決定哪張表作為驅(qū)動(dòng)表，以及選擇最佳的連接策略。例如，如果兩個(gè)表的大小差異很大，**Nested-Loop Join**可能是一個(gè)好的選擇，而對于大小相似的兩個(gè)表，**Hash Join**或**Sort-Merge Join**可能更加高效。

5. 執(zhí)行器：根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃執(zhí)行查詢，向存儲(chǔ)引擎發(fā)送請求，獲取滿足條件的數(shù)據(jù)行。

6. 存儲(chǔ)引擎（如InnoDB）：管理數(shù)據(jù)存儲(chǔ)和檢索。存儲(chǔ)引擎首先接收來自執(zhí)行器的請求，該請求可能是基于優(yōu)化器的執(zhí)行計(jì)劃。

? 存儲(chǔ)引擎首先接收來自執(zhí)行器的請求。請求可能包括獲取滿足查詢條件的數(shù)據(jù)行，以及使用哪種掃描方法（如全表掃描或索引掃描）。

? 假設(shè)執(zhí)行器已經(jīng)決定使用索引掃描。在這個(gè)示例中，存儲(chǔ)引擎可能會(huì)先對employee表進(jìn)行索引掃描（使用age索引），然后對employee_score表進(jìn)行索引掃描（使用subject_id和score的聯(lián)合索引）。

? 存儲(chǔ)引擎會(huì)根據(jù)請求查詢相應(yīng)的索引。在employee索引中會(huì)找到滿足age > 18條件的記錄。在employee_score索引中找到滿足subject_id = 3 AND score > 80條件的記錄。

? 一旦找到了滿足條件的記錄，存儲(chǔ)引擎需要將這些記錄所在的數(shù)據(jù)頁從磁盤加載到內(nèi)存中。存儲(chǔ)引擎首先檢查緩沖池（InnoDB Buffer Pool），看這些數(shù)據(jù)頁是否已經(jīng)存在于內(nèi)存中。如果已經(jīng)存在，則無需再次從磁盤加載。如果不存在，存儲(chǔ)引擎會(huì)將這些數(shù)據(jù)頁從磁盤加載到緩沖池中。

? 加載到緩沖池中的記錄可以被多個(gè)查詢共享，這有助于提高查詢效率。

7. 執(zhí)行器：處理連接、排序、聚合、過濾等操作。

? 在內(nèi)存中執(zhí)行連接操作，將employee表和employee_score表的數(shù)據(jù)行連接起來。

? 對連接后的結(jié)果集進(jìn)行過濾，只保留滿足查詢條件（age > 18、subject_id = 3、score > 80）的數(shù)據(jù)行。

? 將過濾后的數(shù)據(jù)行作為查詢結(jié)果返回給客戶端。

前面說過，根據(jù)存儲(chǔ)引擎根據(jù)索引條件加載到內(nèi)存的數(shù)據(jù)頁有多數(shù)據(jù)，可能有不滿足索引條件的數(shù)據(jù)，如果執(zhí)行器不再次進(jìn)行索引條件判斷， 則無法判斷哪些記錄滿足索引條件的，雖然在存儲(chǔ)引擎判斷過了，但是在執(zhí)行器還是會(huì)有索引條件 age > 18、subject_id = 3、score > 80 的判斷。

我們再以全局視野來分析一下

1. 確定驅(qū)動(dòng)表: 首先，MySQL優(yōu)化器會(huì)選擇一個(gè)表作為"驅(qū)動(dòng)表"。通常，返回記錄數(shù)較少的表會(huì)被選為驅(qū)動(dòng)表。假設(shè)employee_score表中滿足subject_id = 3 AND score > 80條件的記錄數(shù)量較少，那么這張表可能被選為驅(qū)動(dòng)表。這是優(yōu)化器的工作，它預(yù)估哪個(gè)表作為驅(qū)動(dòng)表更為高效，制定執(zhí)行計(jì)劃。雖然驅(qū)動(dòng)表的選擇很大程度上是基于預(yù)估的返回記錄數(shù)，但實(shí)際選擇還會(huì)受其他因素影響，例如表之間的連接類型、可用的索引等。
2. 使用驅(qū)動(dòng)表的索引進(jìn)行篩選: 優(yōu)化器會(huì)首先對驅(qū)動(dòng)表進(jìn)行篩選。如果employee_score是驅(qū)動(dòng)表，優(yōu)化器會(huì)使用subject_id和score的聯(lián)合索引來篩選出subject_id = 3 AND score > 80的記錄。這是執(zhí)行器按照優(yōu)化器的計(jì)劃向存儲(chǔ)引擎發(fā)出請求，獲取需要的數(shù)據(jù)。存儲(chǔ)引擎負(fù)責(zé)訪問索引，并根據(jù)索引定位到實(shí)際的數(shù)據(jù)頁，從而獲取數(shù)據(jù)行。
3. 連接操作: 執(zhí)行器會(huì)基于上一步從驅(qū)動(dòng)表中篩選出的記錄對另一個(gè)表（即employee表）進(jìn)行連接。這時(shí)，執(zhí)行器會(huì)使用employee表上的索引（如id索引）來高效地找到匹配的記錄。
4. 進(jìn)一步的篩選: 在連接的過程中，執(zhí)行器會(huì)考慮employee表的其他篩選條件，如age > 18，通常連接后才過濾篩選，這也是執(zhí)行器的工作，執(zhí)行器在連接過程中或之后，根據(jù)優(yōu)化器制定的計(jì)劃進(jìn)一步篩選結(jié)果集。但是這里employee表的age索引其葉子節(jié)點(diǎn)包含age和主鍵id信息，在進(jìn)行連接時(shí)，可以直接按照age范圍掃描該索引，利用其葉子節(jié)點(diǎn)中的id信息進(jìn)行高效的JOIN操作，因此在連接時(shí)就完成篩選，這個(gè)過程由MySQL優(yōu)化器自動(dòng)完成。從上面可以看到，當(dāng)存在可以被利用的索引時(shí)，MySQL可以在連接過程中執(zhí)行這些過濾操作。
5. 返回結(jié)果: 這是執(zhí)行器最后的步驟，返回最終的查詢結(jié)果。

四、總結(jié)

本文采用一張簡單的架構(gòu)圖說明了MySQL查詢中使用的組件和組件間關(guān)系。

解析了一條sql語句從客戶端請求mysql服務(wù)器到返回給客戶端的整個(gè)生命周期流程。

列舉了單表sql、關(guān)聯(lián)表sql 兩種不同SQL在整個(gè)生命周期中的執(zhí)行順序和及內(nèi)部組件邏輯關(guān)系。

通過如上案例的解析可以讓開發(fā)者們掌握到單表sql、關(guān)聯(lián)表sql的底層sql知識，為理解慢sql的產(chǎn)生和優(yōu)化鑒定基礎(chǔ)。

作者：京東物流 高峰

來源：京東云開發(fā)者社區(qū) 自猿其說Tech 轉(zhuǎn)載請注明來源文章來源地址http://www.zghlxwxcb.cn/news/detail-746006.html

到了這里，關(guān)于【慢SQL性能優(yōu)化】 一條SQL的生命周期的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！