1. 單表查詢 SQL 在 MySQL 架構中的各個組件的執行過程

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



假如 SQL 語句是這樣

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

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

1. 客戶端：客戶端（如 MySQL 命令行工具、Navicat、MySQL Workbench 或其他應用程序）發送 SQL 查詢到 MySQL 服務器。

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

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

4. 分析器：

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

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

生成查詢樹。

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

6. 執行器：根據優化器選擇的執行計劃，向存儲引擎發送請求，獲取滿足條件的數據行。

7. 存儲引擎（如 InnoDB）：

負責實際執行索引掃描，如在 student 表的 name 索引上進行等值查詢，因查詢全部列，涉及到回表訪問磁盤。

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

8. 執行器：

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

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

執行器將處理后的結果集返回給客戶端。

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

2. SELECT 的各個關鍵字在哪里執行？

根據執行順序，如下：

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

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

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

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

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

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

（7）SELECT：執行器根據優化器選擇的執行計劃來獲取查詢結果。

（8）DISTINCT：執行器對查詢結果進行去重，只返回不重復的記錄。

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

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

3. 表關聯查詢 SQL 在 MySQL 架構中的各個組件的執行過程

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 是聯合索引，age 是索引。 我們按照時間順序來分析一下

1. 連接器：當客戶端連接到 MySQL 服務器時，連接器負責建立和管理連接。它驗證客戶端提供的用戶名和密碼，確定客戶端具有相應的權限，然后建立連接。

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

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

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

5. 執行器：根據優化器生成的執行計劃處理查詢，向存儲引擎發送請求，獲取滿足條件的數據行。

6. 存儲引擎（如 InnoDB）：存儲引擎負責管理數據的存儲和檢索。

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

假設執行器已經決定使用索引掃描。在這個示例中，存儲引擎可能會先對 student 表進行索引掃描（使用 age 索引），然后對 score 表進行索引掃描（使用 student_id 和 subject 的聯合索引）。

存儲引擎會根據請求查詢相應的索引結構。在 student 表中，存儲引擎會找到滿足 age > 18 條件的記錄。在 score 表中，存儲引擎會找到滿足 subject = 'math' AND score > 80 條件的記錄。

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

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

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

在內存中執行連接操作，將 student 表和 score 表的數據行連接起來。

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

將過濾后的數據行作為查詢結果返回給客戶端。

前面說過，根據存儲引擎根據索引條件加載到內存的數據頁有多數據，可能有不滿足索引條件的數據，如果執行器不再次進行索引條件判斷， 則無法判斷哪些記錄滿足索引條件的，雖然在存儲引擎判斷過了，但是在執行器還是會有索引條件 age > 18、subject = 'math'、score > 80 的判斷。

4. LEFT JOIN 將過濾條件放在子查詢中再關聯和放在 WHERE 子句上有什么區別？

先看例子 查詢 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;

先給出結論：查詢 2 和 3 是一樣的，也就是過濾條件放在子查詢中和放在 on 上面是一樣的，后面就只討論查詢 1、2，查詢 1 和查詢 2 是不一樣的，過濾條件放在 where 子句中和放在子查詢再關聯查詢出的結果也是有區別的。 分析一下 從運行結果來看，對于查詢 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;

在這個查詢中，首先執行 LEFT JOIN，將 student 表和 score 表連接起來。連接操作是基于 s.id = sc.student_id 條件進行的。LEFT JOIN 操作會保留左表（student 表）中的所有行，即使它們在右表（score 表）中沒有匹配的行。如果右表中沒有匹配的行，那么右表的列將顯示為 NULL。 然后，WHERE 子句會過濾連接后的結果集，只保留那些滿足 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

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

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

連接器：客戶端與服務器建立連接。

查詢緩存：檢查緩存是否存在此查詢的結果。如果有，直接返回結果。否則，繼續執行。

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

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

執行器：開始請求執行查詢。

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

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

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

當查詢涉及到非聚集索引時，需要回表的操作會導致聚集索引和非聚集索引都被加載到內存中。但是，如果查詢只涉及到聚集索引（如主鍵查詢），那么只需要加載聚集索引的數據頁即可。 對于查詢 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

連接器：客戶端與服務器建立連接。

查詢緩存：檢查緩存是否存在此查詢的結果。如果有，直接返回結果。否則，繼續執行。

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

優化器：決定使用哪些索引進行查詢優化，以及確定連接順序。

執行器：開始請求執行子查詢。

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

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

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

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

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

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

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

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

審核編輯：劉清