一條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;

1. 連接器：客戶端與服務器建立連接，
2. 查詢快取：檢查快取是否存在此查詢的結果，如果有，直接回傳結果，否則，繼續執行，
3. 決議器：決議查詢陳述句，檢查語法是否正確，
4. 優化器：對查詢進行優化，生成執行計劃，決定連接和過濾條件的順序等，
5. 執行器：開始請求執行查詢，
6. 存盤引擎（InnoDB）：從磁盤或者緩沖池讀取滿足條件的資料行（s.id = sc.student_id），因為是left join，所以即便sc.student_id為null也會被關聯，
7. 執行器：將從存盤引擎獲取的資料行進行左連接，應用過濾條件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

1. 連接器：客戶端與服務器建立連接，
2. 查詢快取：檢查快取是否存在此查詢的結果，如果有，直接回傳結果，否則，繼續執行，
3. 決議器：決議查詢陳述句，檢查語法是否正確，
4. 優化器：決定使用哪些索引進行查詢優化，以及確定連接順序，
5. 執行器：開始請求執行子查詢，
6. 存盤引擎（InnoDB）：首先，對student表進行掃描，將滿足條件s.age > 18的記錄對應的資料頁加載到緩沖池(如果緩沖池沒有這個頁的資料)，然后，使用subject = 'math' AND score > 80對score表進行掃描，將滿足條件的記錄對應的資料頁加載到緩沖池(如果緩沖池沒有這個頁的資料)，
7. 執行器：對從存盤引擎獲取的資料應用所有的過濾條件，過濾后的結果存入臨時表，執行主查詢，從臨時表中獲取資料，將s和sc進行左連接，根據s.id = sc.student_id組合結果，將連接后的結果回傳給客戶端，

從這里我們可以看出，查詢2是先過濾后連接，每張表的索引都很重要，如果沒設定好索引，單表過濾會全表掃描，

寫SQL的時候，查詢1和查詢2到底采用哪種方式呢？

根據不同情況各有應用場景，需要注意的是，對于查詢2，子查詢的結果集被存盤在一個臨時表中，臨時表不會繼承原始索引，包括聚集索引和非聚集索引，所以剛剛的例子中，臨時表中s.id和sc.student_id已經不是任何索引列了，對于查詢1，最終滿足關聯條件s.id = sc.student_id的所有記錄都會被加載到記憶體后再進行過濾，

1. 當單表過濾后的資料量較小時，查詢2可能是一個更好的選擇，因為它可以減少關聯操作的資料量，從而提高查詢效率，子查詢階段，MySQL依然會利用原始表上的索引進行過濾，子查詢執行完成后，將過濾后的資料存盤在臨時表中，所以查詢2的方式可以優化的點就是在單表查詢時盡可能的利用索引，
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的共享表空間檔案存盤所有的資料和索引，包括聚集索引和二級索引（又稱非聚集索引或輔助索引），

點擊關注，第一時間了解華為云新鮮技術~

標籤：其他

上一篇：第1章 大資料概述

下一篇：返回列表