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

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



假如 SQL 語句是這樣

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

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

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

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

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

4. 分析器：

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

驗證表名和列名的正確性。

生成查詢樹。

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

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

7. 存儲引擎（如 InnoDB）：

負責(zé)實際執(zhí)行索引掃描，如在 student 表的 name 索引上進行等值查詢，因查詢?nèi)苛校婕暗交乇碓L問磁盤。

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

8. 執(zhí)行器：

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

根據(jù) class_no 對滿足條件的記錄進行分組。

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

在整個查詢執(zhí)行過程中，這些組件共同協(xié)作以高效地執(zhí)行查詢。客戶端負責(zé)發(fā)送查詢，連接器管理客戶端連接，查詢緩存嘗試重用先前查詢結(jié)果，解析器負責(zé)解析查詢，優(yōu)化器選擇最佳執(zhí)行計劃，執(zhí)行器執(zhí)行優(yōu)化器選擇的計劃，存儲引擎（如 InnoDB）負責(zé)管理數(shù)據(jù)存儲和訪問。這些組件的協(xié)同作用使得 MySQL 能夠高效地執(zhí)行查詢并返回結(jié)果集。 根據(jù)索引列過濾條件加載索引的數(shù)據(jù)頁到內(nèi)存這個操作是存儲引擎做的。加載到內(nèi)存中之后，執(zhí)行器會進行索引列和非索引列的過濾條件判斷。

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

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

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

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

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

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

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

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

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

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

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

（10）LIMIT：執(zhí)行器根據(jù) LIMIT 子句中指定的限制條件對查詢結(jié)果進行截斷，只返回部分記錄

3. 表關(guān)聯(lián)查詢 SQL 在 MySQL 架構(gòu)中的各個組件的執(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;

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

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

2. 查詢緩存：MySQL 服務(wù)器在處理查詢之前，會先檢查查詢緩存。如果查詢緩存中已經(jīng)存在相同的查詢及其結(jié)果集，服務(wù)器將直接返回緩存中的結(jié)果，而不再執(zhí)行后續(xù)的查詢處理。由于查詢緩存在 MySQL 8.0 中已被移除，我們在這個示例中不再詳細討論。

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

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

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

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

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

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

存儲引擎會根據(jù)請求查詢相應(yīng)的索引結(jié)構(gòu)。在 student 表中，存儲引擎會找到滿足 age > 18 條件的記錄。在 score 表中，存儲引擎會找到滿足 subject = 'math' AND score > 80 條件的記錄。

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

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

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

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

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

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

前面說過，根據(jù)存儲引擎根據(jù)索引條件加載到內(nèi)存的數(shù)據(jù)頁有多數(shù)據(jù)，可能有不滿足索引條件的數(shù)據(jù)，如果執(zhí)行器不再次進行索引條件判斷， 則無法判斷哪些記錄滿足索引條件的，雖然在存儲引擎判斷過了，但是在執(zhí)行器還是會有索引條件 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 子句中和放在子查詢再關(guān)聯(lián)查詢出的結(jié)果也是有區(qū)別的。 分析一下 從運行結(jié)果來看，對于查詢 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;

在這個查詢中，首先執(zhí)行 LEFT JOIN，將 student 表和 score 表連接起來。連接操作是基于 s.id = sc.student_id 條件進行的。LEFT JOIN 操作會保留左表（student 表）中的所有行，即使它們在右表（score 表）中沒有匹配的行。如果右表中沒有匹配的行，那么右表的列將顯示為 NULL。 然后，WHERE 子句會過濾連接后的結(jié)果集，只保留那些滿足 s.age > 18 and sc.subject = 'math' and sc.score > 80 條件的行。這意味著，右表為 NULL 的記錄將被排除，因為右表的過濾條件 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 subject, score from score where subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id

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

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

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

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

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

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

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

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

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

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

當(dāng)查詢涉及到非聚集索引時，需要回表的操作會導(dǎo)致聚集索引和非聚集索引都被加載到內(nèi)存中。但是，如果查詢只涉及到聚集索引（如主鍵查詢），那么只需要加載聚集索引的數(shù)據(jù)頁即可。 對于查詢 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)化器：決定使用哪些索引進行查詢優(yōu)化，以及確定連接順序。

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

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

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

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

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

當(dāng)單表過濾后的數(shù)據(jù)量較大時，查詢 1 可能更合適，因為它可以更好地利用索引進行關(guān)聯(lián)操作。這樣可以減少關(guān)聯(lián)操作的時間開銷，查詢 2 因為臨時表不繼承索引，表關(guān)聯(lián)的時間開銷比較大。

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

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

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

審核編輯：劉清