【慢SQL效能最佳化】 一條SQL的生命週期

資料庫工作筆記發表於2023-11-15
SQL

一、 一條簡單SQL在MySQL執行過程

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

例如如下SQL語句

SELECT department_id FROM employee WHERE name = 'Lucy' AND age > 18 
GROUP BY department_id

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

  1. 客戶端：如MySQL命令列工具、Navicat、DBeaver或其他應用程式傳送SQL查詢到MySQL伺服器。

  2. 聯結器：負責與客戶端建立連線、管理連線和維護連線。當客戶端連線到MySQL伺服器時，聯結器驗證客戶端的使用者名稱和密碼，然後分配一個執行緒來處理客戶端的請求。

  3. 查詢快取：查詢快取用於快取先前執行過的查詢及其結果。當收到新的查詢請求時，MySQL首先檢查查詢快取中是否已有相同的查詢及其結果。如果查詢快取中有匹配的查詢結果，MySQL將直接返回快取的結果，而無需再次執行查詢。但是，如果查詢快取中沒有匹配的查詢結果，MySQL將繼續執行查詢。

  4. 分析器：

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

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

◦ 生成查詢樹。

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

  2. 執行器：根據最佳化器選擇的執行計劃，向儲存引擎傳送請求，獲取滿足條件的資料行。

  3. 儲存引擎（如InnoDB）：

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

◦ 在訪問磁碟之前，先檢查InnoDB的緩衝池（Buffer Pool）中是否已有所需的資料頁。如果緩衝池中有符合條件的資料頁，直接使用快取的資料。如果緩衝池中沒有所需的資料頁，從磁碟載入資料頁到緩衝池中。

  1. 執行器：

◦ 對於每個找到的記錄，再次判斷記錄是否滿足索引條件name。這是因為基於索引條件載入到記憶體中是資料頁，資料頁中也有可能包含不滿足索引條件的記錄，所以還要再判斷一次name條件，滿足name條件則繼續判斷age > 18過濾條件。

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

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

在整個查詢執行過程中，這些元件共同協作以高效地執行查詢。客戶端負責傳送查詢，聯結器管理客戶端連線，查詢快取嘗試重用先前查詢結果，解析器負責解析查詢，最佳化器選擇最佳執行計劃，執行器執行最佳化器選擇的計劃，儲存引擎（如InnoDB）負責管理資料儲存和訪問。這些元件的協同作用使得MySQL能夠高效地執行查詢並返回結果集。

根據索引列過濾條件載入索引的資料頁到記憶體這個操作是儲存引擎做的。載入到記憶體中之後，執行器會進行索引列和非索引列的過濾條件判斷。

二、 查詢SQL關鍵字執行順序

執行順序，如下：

1、對儲存引擎的操作

（1）FROM：用於查詢SQL的資料表。執行器會根據最佳化器選擇的執行計劃從儲存引擎中獲取相關表的資料。

（2）ON： 與JOIN一起使用，用於指定連線條件。執行器會根據ON給定的條件條件從儲存引擎獲取匹配條件的記錄。如果連線條件涉及到索引列，儲存引擎會使用索引進行最佳化。

（3）JOIN：指定表之間連線方式（如INNER JOIN,LEFT JOIN等）。執行器會根據最佳化器選擇的執行計劃，從儲存引擎中獲取連線表資料。然後執行器根據JOIN連線型別和ON連線條件，對資料連線處理。

（4）WHERE：執行器對從儲存引擎返回的資料進行過濾，只保留滿足WHERE子句條件的記錄。過濾條件如有索引，儲存引擎層會透過索引過濾後返回。

2、對返回結果集的操作

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

（6）HAVING：執行器在執行分組後，根據HAVING條件對分組後的記錄再次過濾。

（7）SELECT：執行器根據最佳化器選擇的執行計劃和指定列獲取查詢結果。

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

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

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

三、表關聯查詢SQL在MySQL中的執行過程

SELECT s.id, s.name, s.age, es.subject, es.score 
FROM employee s JOIN employee_score es ON s.id = es.employee_id 
WHERE s.age >18 AND es.subject_id =3 AND es.score >80;

這個例子中，subject_id和score是聯合索引，age是索引。 我們按照時間順序來分析一下

  1. 聯結器：當客戶端連線到MySQL伺服器時，聯結器負責建立和管理連線。它驗證客戶端提供的使用者名稱和密碼，確定客戶端具有相應的許可權，然後建立連線。

  2. 查詢快取：MySQL伺服器在處理查詢之前，會先檢查查詢快取。如果查詢快取中已經存在該結果集，伺服器將直接返回快取中的結果。

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

  4. 最佳化器：根據解析器提供的資訊生成執行計劃。最佳化器會分析多種可能的執行策略，並選擇成本最低的策略。在這個示例中，最佳化器會選擇age索引和subject_id與score的聯合索引。對於連線操作，最佳化器還要決定連線策略，例如是否使用Nested-Loop Join或Hash Join等一些連線策略。最佳化器還會根據表的大小、索引、查詢條件和統計資訊來決定哪張表作為驅動表，以及選擇最佳的連線策略。例如，如果兩個表的大小差異很大，**Nested-Loop Join**可能是一個好的選擇，而對於大小相似的兩個表，**Hash Join**或**Sort-Merge Join**可能更加高效。

  5. 執行器：根據最佳化器生成的執行計劃執行查詢，向儲存引擎傳送請求，獲取滿足條件的資料行。

  6. 儲存引擎（如InnoDB）：管理資料儲存和檢索。儲存引擎首先接收來自執行器的請求，該請求可能是基於最佳化器的執行計劃。

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

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

◦ 儲存引擎會根據請求查詢相應的索引。在employee索引中會找到滿足age > 18條件的記錄。在employee_score索引中找到滿足subject_id = 3 AND score > 80條件的記錄。

◦ 一旦找到了滿足條件的記錄，儲存引擎需要將這些記錄所在的資料頁從磁碟載入到記憶體中。儲存引擎首先檢查緩衝池（InnoDB Buffer Pool），看這些資料頁是否已經存在於記憶體中。如果已經存在，則無需再次從磁碟載入。如果不存在，儲存引擎會將這些資料頁從磁碟載入到緩衝池中。

◦ 載入到緩衝池中的記錄可以被多個查詢共享，這有助於提高查詢效率。

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

◦ 在記憶體中執行連線操作，將employee表和employee_score表的資料行連線起來。

◦ 對連線後的結果集進行過濾，只保留滿足查詢條件（age > 18、subject_id = 3、score > 80）的資料行。

◦ 將過濾後的資料行作為查詢結果返回給客戶端。

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

我們再以全域性視野來分析一下

  1. 確定驅動表: 首先，MySQL最佳化器會選擇一個表作為"驅動表"。通常，返回記錄數較少的表會被選為驅動表。假設employee_score表中滿足subject_id = 3 AND score > 80條件的記錄數量較少，那麼這張表可能被選為驅動表。這是最佳化器的工作，它預估哪個表作為驅動表更為高效，制定執行計劃。雖然驅動表的選擇很大程度上是基於預估的返回記錄數，但實際選擇還會受其他因素影響，例如表之間的連線型別、可用的索引等。
  2. 使用驅動表的索引進行篩選: 最佳化器會首先對驅動表進行篩選。如果employee_score是驅動表，最佳化器會使用subject_id和score的聯合索引來篩選出subject_id = 3 AND score > 80的記錄。這是執行器按照最佳化器的計劃向儲存引擎發出請求，獲取需要的資料。儲存引擎負責訪問索引，並根據索引定位到實際的資料頁，從而獲取資料行。
  3. 連線操作: 執行器會基於上一步從驅動表中篩選出的記錄對另一個表（即employee表）進行連線。這時，執行器會使用employee表上的索引（如id索引）來高效地找到匹配的記錄。
  4. 進一步的篩選: 在連線的過程中，執行器會考慮employee表的其他篩選條件，如age > 18，通常連線後才過濾篩選，這也是執行器的工作，執行器在連線過程中或之後，根據最佳化器制定的計劃進一步篩選結果集。但是這裡employee表的age索引其葉子節點包含age和主鍵id資訊，在進行連線時，可以直接按照age範圍掃描該索引，利用其葉子節點中的id資訊進行高效的JOIN操作，因此在連線時就完成篩選，這個過程由MySQL最佳化器自動完成。從上面可以看到，當存在可以被利用的索引時，MySQL可以在連線過程中執行這些過濾操作。
  5. 返回結果: 這是執行器最後的步驟，返回最終的查詢結果。

四、總結

本文采用一張簡單的架構圖說明了MySQL查詢中使用的元件和元件間關係。

解析了一條sql語句從客戶端請求mysql伺服器到返回給客戶端的整個生命週期流程。

列舉了單表sql、關聯表sql 兩種不同SQL在整個生命週期中的執行順序和及內部元件邏輯關係。

透過如上案例的解析可以讓開發者們掌握到單表sql、關聯表sql的底層sql知識，為理解慢sql的產生和最佳化鑑定基礎。

作者：京東物流 高峰

來源：京東雲開發者社群 自猿其說Tech 轉載請註明來源

相關文章