摘要：一條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的共享表空間檔案儲存所有的資料和索引，包括聚集索引和二級索引（又稱非聚集索引或輔助索引）。