一條SQL如何被MySQL架構(gòu)中的各個(gè)組件操作執(zhí)行的？

1. 單表查詢 SQL 在 MySQL 架構(gòu)中的各個(gè)組件的執(zhí)行過程

簡單用一張圖說明下，MySQL 架構(gòu)有哪些組件，接下來給大家用 SQL 語句分析



假如 SQL 語句是這樣

SELECT class_no FROM student WHERE name = 'lcy' AND age > 18 GROUP BY class_no

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

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

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

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

4. 分析器：

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

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

生成查詢樹。

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

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

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

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

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

8. 執(zhí)行器：

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

根據(jù) class_no 對(duì)滿足條件的記錄進(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)行索引列和非索引列的過濾條件判斷。

2. SELECT 的各個(gè)關(guān)鍵字在哪里執(zhí)行？

根據(jù)執(zhí)行順序，如下：

（1）FROM：FROM 子句用于指定查詢所涉及的數(shù)據(jù)表。在查詢執(zhí)行過程中，執(zhí)行器需要根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃從存儲(chǔ)引擎中獲取指定表的數(shù)據(jù)。

（2）ON：ON 子句用于指定連接條件，它通常與 JOIN 子句一起使用。在查詢執(zhí)行過程中，執(zhí)行器會(huì)根據(jù) ON 子句中的條件從存儲(chǔ)引擎獲取滿足條件的記錄。如果連接條件涉及到索引列，存儲(chǔ)引擎可能會(huì)使用索引進(jìn)行優(yōu)化。

（3）JOIN：JOIN 子句用于指定表之間的連接方式（如 INNER JOIN, LEFT JOIN 等）。在查詢執(zhí)行過程中，執(zhí)行器會(huì)根據(jù)優(yōu)化器選擇的執(zhí)行計(jì)劃，從存儲(chǔ)引擎中獲取需要連接的表的數(shù)據(jù)。然后，執(zhí)行器根據(jù) JOIN 子句的類型和 ON 子句中的連接條件，對(duì)數(shù)據(jù)進(jìn)行連接操作。

（4）WHERE：執(zhí)行器對(duì)從存儲(chǔ)引擎返回的數(shù)據(jù)進(jìn)行過濾，只保留滿足 WHERE 子句條件的記錄。部分過濾條件如果涉及到索引，在存儲(chǔ)引擎層就已經(jīng)進(jìn)行了過濾。

（5）GROUP BY：執(zhí)行器對(duì)滿足 WHERE 子句條件的記錄按照 GROUP BY 子句中指定的列進(jìn)行分組。

（6）HAVING：執(zhí)行器在進(jìn)行分組后，根據(jù) HAVING 子句條件對(duì)分組后的記錄進(jìn)行進(jìn)一步過濾。

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

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

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

（10）LIMIT：執(zhí)行器根據(jù) LIMIT 子句中指定的限制條件對(duì)查詢結(jié)果進(jìn)行截?cái)啵环祷夭糠钟涗?/p>

3. 表關(guān)聯(lián)查詢 SQL 在 MySQL 架構(gòu)中的各個(gè)組件的執(zhí)行過程

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

這個(gè)例子中，student_id 和 subject 是聯(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é)果，而不再執(zhí)行后續(xù)的查詢處理。由于查詢緩存在 MySQL 8.0 中已被移除，我們?cè)谶@個(gè)示例中不再詳細(xì)討論。

3. 解析器：解析器的主要任務(wù)是解析 SQL 查詢語句，確保查詢語法正確。解析器會(huì)將查詢語句分解成多個(gè)組成部分，例如表、列、條件等。在這個(gè)示例中，解析器會(huì)識(shí)別出涉及的表（student 和 score）以及需要的列（id、name、age、subject、score）。

4. 優(yōu)化器：優(yōu)化器的職責(zé)是根據(jù)解析器提供的信息生成執(zhí)行計(jì)劃。它會(huì)分析多種可能的執(zhí)行策略，并選擇成本最低的策略。在這個(gè)示例中，優(yōu)化器可能會(huì)分析各種表掃描和索引掃描的組合，最終選擇一種成本最低的執(zhí)行計(jì)劃。

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

6. 存儲(chǔ)引擎（如 InnoDB）：存儲(chǔ)引擎負(fù)責(zé)管理數(shù)據(jù)的存儲(chǔ)和檢索。

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

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

存儲(chǔ)引擎會(huì)根據(jù)請(qǐng)求查詢相應(yīng)的索引結(jié)構(gòu)。在 student 表中，存儲(chǔ)引擎會(huì)找到滿足 age > 18 條件的記錄。在 score 表中，存儲(chǔ)引擎會(huì)找到滿足 subject = 'math' 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í)行連接操作，將 student 表和 score 表的數(shù)據(jù)行連接起來。

對(duì)連接后的結(jié)果集進(jìn)行過濾，只保留滿足查詢條件（age > 18、subject = 'math'、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 = 'math'、score > 80 的判斷。

4. LEFT JOIN 將過濾條件放在子查詢中再關(guān)聯(lián)和放在 WHERE 子句上有什么區(qū)別？

先看例子 查詢 1

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

查詢 2

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id

查詢 3

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id AND s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

先給出結(jié)論：查詢 2 和 3 是一樣的，也就是過濾條件放在子查詢中和放在 on 上面是一樣的，后面就只討論查詢 1、2，查詢 1 和查詢 2 是不一樣的，過濾條件放在 where 子句中和放在子查詢?cè)訇P(guān)聯(lián)查詢出的結(jié)果也是有區(qū)別的。 分析一下 從運(yùn)行結(jié)果來看，對(duì)于查詢 1

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

在這個(gè)查詢中，首先執(zhí)行 LEFT JOIN，將 student 表和 score 表連接起來。連接操作是基于 s.id = sc.student_id 條件進(jìn)行的。LEFT JOIN 操作會(huì)保留左表（student 表）中的所有行，即使它們?cè)谟冶恚╯core 表）中沒有匹配的行。如果右表中沒有匹配的行，那么右表的列將顯示為 NULL。 然后，WHERE 子句會(huì)過濾連接后的結(jié)果集，只保留那些滿足 s.age > 18 and sc.subject = 'math' and sc.score > 80 條件的行。這意味著，右表為 NULL 的記錄將被排除，因?yàn)橛冶淼倪^濾條件 sc.subject = 'math' and sc.score > 80 條件不滿足。 對(duì)于查詢 2：

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (select id, name, age from student where age > 18) s
LEFT JOIN (select subject, score from score where subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id

在這個(gè)查詢中，我們首先執(zhí)行兩個(gè)子查詢。第一個(gè)子查詢從 student 表中選擇所有 age > 18 的行，而第二個(gè)子查詢從 score 表中選擇所有 subject = 'math' and score > 80 的行。這意味著，在進(jìn)行連接操作之前，我們已經(jīng)對(duì)兩個(gè)表分別進(jìn)行了過濾。 接下來，執(zhí)行 LEFT JOIN 操作，將過濾后的 s 和 sc 子查詢的結(jié)果集連接起來，基于 s.id = sc.student_id 條件。因?yàn)?LEFT JOIN 操作會(huì)保留左表（s 子查詢的結(jié)果集）中的所有行，右表為 NULL 的記錄包含了。 結(jié)果差異： 查詢 1 和查詢 2 的主要區(qū)別在于 WHERE 子句和子查詢的使用。

查詢 1 在連接操作后應(yīng)用過濾條件，這可能導(dǎo)致右表為 NULL 的關(guān)聯(lián)記錄因?yàn)橛冶淼倪^濾條件而被排除在外。而查詢 2 在連接操作之前就已經(jīng)過濾了表中的數(shù)據(jù)，這意味著查詢結(jié)果會(huì)包含所有左表過濾條件的記錄，以及右表過濾條件的記錄和 NULL 的記錄。 如果查詢 1 想保留右表為 NULL 的記錄，只需要改為 WHERE s.age > 18 AND (sc.student_id is null OR (sc.subject = 'math' AND sc.score> 80)); 這樣查詢 1 和 2 會(huì)有相同的結(jié)果集。 我們分析一下這兩個(gè)查詢?cè)?MySQL 架構(gòu)中各個(gè)組件中執(zhí)行的區(qū)別 對(duì)于查詢 1：

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

連接器：客戶端與服務(wù)器建立連接。

查詢緩存：檢查緩存是否存在此查詢的結(jié)果。如果有，直接返回結(jié)果。否則，繼續(xù)執(zhí)行。

解析器：解析查詢語句，檢查語法是否正確。

優(yōu)化器：對(duì)查詢進(jìn)行優(yōu)化，生成執(zhí)行計(jì)劃，決定連接和過濾條件的順序等。

執(zhí)行器：開始請(qǐng)求執(zhí)行查詢。

存儲(chǔ)引擎（InnoDB）：從磁盤或者緩沖池讀取滿足條件的數(shù)據(jù)行（s.id = sc.student_id），因?yàn)槭?left join，所以即便 sc.student_id 為 null 也會(huì)被關(guān)聯(lián)。

執(zhí)行器：將從存儲(chǔ)引擎獲取的數(shù)據(jù)行進(jìn)行左連接，應(yīng)用過濾條件 s.age > 18 and sc.subject = 'math' and sc.score > 80 進(jìn)行過濾，將結(jié)果集返回給客戶端。

當(dāng)查詢包含索引列的條件時(shí)，MySQL 的存儲(chǔ)引擎會(huì)首先利用索引在磁盤上定位到滿足索引條件的記錄。接著，將這些索引數(shù)據(jù)對(duì)應(yīng)的數(shù)據(jù)頁加載到內(nèi)存中的緩沖池。然后，執(zhí)行器在內(nèi)存中對(duì)這些記錄進(jìn)行進(jìn)一步的過濾，根據(jù)索引條件和非索引列的條件來過濾數(shù)據(jù)。

當(dāng)查詢涉及到非聚集索引時(shí)，需要回表的操作會(huì)導(dǎo)致聚集索引和非聚集索引都被加載到內(nèi)存中。但是，如果查詢只涉及到聚集索引（如主鍵查詢），那么只需要加載聚集索引的數(shù)據(jù)頁即可。 對(duì)于查詢 2

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id

連接器：客戶端與服務(wù)器建立連接。

查詢緩存：檢查緩存是否存在此查詢的結(jié)果。如果有，直接返回結(jié)果。否則，繼續(xù)執(zhí)行。

解析器：解析查詢語句，檢查語法是否正確。

優(yōu)化器：決定使用哪些索引進(jìn)行查詢優(yōu)化，以及確定連接順序。

執(zhí)行器：開始請(qǐng)求執(zhí)行子查詢。

存儲(chǔ)引擎（InnoDB）：首先，對(duì) student 表進(jìn)行掃描，將滿足條件 s.age > 18 的記錄對(duì)應(yīng)的數(shù)據(jù)頁加載到緩沖池 (如果緩沖池沒有這個(gè)頁的數(shù)據(jù))。然后，使用 subject = 'math' AND score > 80 對(duì) score 表進(jìn)行掃描，將滿足條件的記錄對(duì)應(yīng)的數(shù)據(jù)頁加載到緩沖池 (如果緩沖池沒有這個(gè)頁的數(shù)據(jù))。

執(zhí)行器：對(duì)從存儲(chǔ)引擎獲取的數(shù)據(jù)應(yīng)用所有的過濾條件，過濾后的結(jié)果存入臨時(shí)表，執(zhí)行主查詢，從臨時(shí)表中獲取數(shù)據(jù)，將 s 和 sc 進(jìn)行左連接，根據(jù) s.id = sc.student_id 組合結(jié)果。將連接后的結(jié)果返回給客戶端。

從這里我們可以看出，查詢 2 是先過濾后連接，每張表的索引都很重要，如果沒設(shè)置好索引，單表過濾會(huì)全表掃描。 寫 SQL 的時(shí)候，查詢 1 和查詢 2 到底采用哪種方式呢？ 根據(jù)不同情況各有應(yīng)用場景，需要注意的是，對(duì)于查詢 2，子查詢的結(jié)果集被存儲(chǔ)在一個(gè)臨時(shí)表中，臨時(shí)表不會(huì)繼承原始索引，包括聚集索引和非聚集索引，所以剛剛的例子中，臨時(shí)表中 s.id 和 sc.student_id 已經(jīng)不是任何索引列了。對(duì)于查詢 1，最終滿足關(guān)聯(lián)條件 s.id = sc.student_id 的所有記錄都會(huì)被加載到內(nèi)存后再進(jìn)行過濾。

當(dāng)單表過濾后的數(shù)據(jù)量較小時(shí)，查詢 2 可能是一個(gè)更好的選擇，因?yàn)樗梢詼p少關(guān)聯(lián)操作的數(shù)據(jù)量，從而提高查詢效率。子查詢階段，MySQL 依然會(huì)利用原始表上的索引進(jìn)行過濾。子查詢執(zhí)行完成后，將過濾后的數(shù)據(jù)存儲(chǔ)在臨時(shí)表中。所以查詢 2 的方式可以優(yōu)化的點(diǎn)就是在單表查詢時(shí)盡可能的利用索引。

當(dāng)單表過濾后的數(shù)據(jù)量較大時(shí)，查詢 1 可能更合適，因?yàn)樗梢愿玫乩盟饕M(jìn)行關(guān)聯(lián)操作。這樣可以減少關(guān)聯(lián)操作的時(shí)間開銷，查詢 2 因?yàn)榕R時(shí)表不繼承索引，表關(guān)聯(lián)的時(shí)間開銷比較大。

5. 聚集索引和全表掃描有什么區(qū)別呢？

走PRIMARY 索引（聚集索引）和全表掃描有什么區(qū)別呢？準(zhǔn)確來說，使用 InnoDB 存儲(chǔ)引擎的情況下，全表掃描的數(shù)據(jù)和聚集索引的數(shù)據(jù)在 InnoDB 表空間中的存儲(chǔ)位置是相同的，也就是說它們的內(nèi)存地址也是相同的。所以你也可以理解為，他們其實(shí)都是在聚集索引上操作的（聚集索引 B + 樹的葉子結(jié)點(diǎn)是根據(jù)主鍵排好序的完整的用戶記錄，包含表里的所有字段），區(qū)別就在于 全表掃描將聚集索引 B + 樹的葉子結(jié)點(diǎn)從左到右依次順序掃描并判斷條件。 聚集索引是利用二分思想將聚集索引 B + 樹到指定范圍區(qū)間進(jìn)行掃描，比如 select * from demo_info where id in (1, 2) 這種條件字段是主鍵 id，可以很好的利用 PRIMARY 索引進(jìn)行二分的快速查詢。

在 MyISAM 中，全表掃描的數(shù)據(jù)和索引數(shù)據(jù)的存儲(chǔ)位置是分開的。然而 MyISAM 已經(jīng)被 InnoDB 取代，不再是 MySQL 的推薦存儲(chǔ)引擎，從 MySQL5.5 開始，InnoDB 就成了 MySQL 的默認(rèn)存儲(chǔ)引擎。 默認(rèn)情況下，InnoDB 使用一個(gè)名為 ibdata1 的共享表空間文件存儲(chǔ)所有的數(shù)據(jù)和索引，包括聚集索引和二級(jí)索引（又稱非聚集索引或輔助索引）。

審核編輯：劉清