一條SQL如何被MySQL架構中的各個組件操作執行的？

摘要：一條SQL如何被MySQL架構中的各個組件操作執行的，執行器做了什麼？存儲引擎做了什麼？表關聯查詢是怎麼在存儲引擎和執行器被分步執行的？本文帶你探探究竟！本文分享自華為雲社區《一條SQL如何被MySQL架構中的各個組件操作執行的？》，作者：磚業洋__。 1. 單表查詢SQL在MySQL架構中 ...

摘要：一條SQL如何被MySQL架構中的各個組件操作執行的，執行器做了什麼？存儲引擎做了什麼？表關聯查詢是怎麼在存儲引擎和執行器被分步執行的？本文帶你探探究竟！

本文分享自華為雲社區《一條SQL如何被MySQL架構中的各個組件操作執行的？》，作者：磚業洋__。

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的共用表空間文件存儲所有的數據和索引，包括聚集索引和二級索引（又稱非聚集索引或輔助索引）。

點擊關註，第一時間瞭解華為雲新鮮技術~