MySQL系列之一條SQL查詢語句的執行過程

瑜戈發表於2018-11-21
MySql

極客時間《MySQL實戰》專欄的學習收穫

最近剛剛購買了極客時間的課程《MySQL實戰45講》，我會在這裡將學習到的知識點做一個總結。

本節主要是講MySQL的基礎架構。比如執行以下語句時：

mysql> select * from T where ID=10;
複製程式碼

這條語句在MySQL內部是如何處理的。

基礎架構圖

MySQL系列之一條SQL查詢語句的執行過程

如圖 主體分為兩部分:Server和儲存引擎部分。

Server包括：聯結器、快取查詢、分析器、優化器、執行器。儲存過程、觸發器、檢視等功能都在Server層處理。

儲存引擎負責資料的儲存和提取。常見的有InnoDB、MyISAM。

聯結器

聯結器與客戶端建立連線、獲取許可權、維持和管理連線。

常見的命令：

mysql -uroot -ppassword

用來和伺服器建立連線，TCP握手後完成認證過程。

  • 賬號密碼不對，報錯："Access denied for user"
  • 認證通過，在許可權表中查詢擁有的許可權

只有在連線過程才會從許可權表中讀取許可權資訊，中途對許可權的修改不會影響已經建立的連線，只有重新登陸後才會使用新的許可權資訊。

建立連線後如果長時間處於空閒狀態，"show processlist" 命令可以看到到處於sleep狀態的連線。
若規定時間內無活動，則會自動斷開連線。規定時間 由 wait_timeout控制，預設為8小時。斷開後，再次發請回會提示 "Lost connection to MySQL server during query" ,只能重新連線。

防止資料庫中出現佔用大量記憶體的情況，可以用一下方法解決：

  1. 定期斷開長連線或者佔用記憶體過大的連線。
  2. MySQL5.7及以上版本，每次執行一個較大的操作，可執行 "mysql_reset_connection"命令來初始化連線資源，該操作不會重連和重新獲取授權，只是恢復到剛建立連線的狀態。

查詢快取

建立連線後，就可以執行select操作，這是會執行第二部分：查詢快取。

一個請求進來，首先查詢快取，是否存在該記錄。之前執行過的語句，則會以語句為key,執行後的結果集為value儲存在記憶體中。

  • 如果在快取中查詢到，則回直接返回給客戶端。
  • 如果沒有找到記錄，繼續執行後面的操作，並將該語句與結果分別以key-value存入記憶體。

不建議使用查詢快取，此操作弊大於利 ( MySQL8.0後將會刪除該功能 )

對於一個表，只要有更新便會將涉及到該表的快取全部清空。
只適用於不常更新的靜態表
推薦：
-將query_cache_type 設定為 DEMAND,預設SQL不使用查詢快取，針對需要查詢快取的語句，使用SQL_cache顯示指定，例如：

select SQL_CACHE * from T where ID = 10;
複製程式碼

分析器

這條SQL要做什麼

  1. 詞法分析
    識別SQL關鍵字，提出主要成分。MySQL根據"select"得出這是一條查詢語句，根據"from T"識別表T，將"ID"識別為列名。
  2. 語法分析
    根據語法規則判斷是否符合SQL的語法。如果出現錯誤，則提示："You have an error in your SQL syntax...",後面是錯誤的地方，需要你關注"use naer"後的內容。

優化器

這條SQL如何做

這個步驟將會選取最優的執行方式，例如：

  1. 當涉及到多個索引時，決定用哪個索引
  2. 多表關聯時，決定連線順序
    select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
複製程式碼
    該步驟決定了先從t1表中取出c=10的ID值還是先查詢出t2表中d=20的ID值。

執行器

真正的執行步驟

根據聯結器取到的許可權判斷，是否對錶T有許可權。

  • 若有許可權，開啟表繼續操作。

開啟表後，根據表的引擎定義，使用引擎提供的介面。 例：

  • 表T中ID欄位無索引：

    • 呼叫InnoDB提供的介面，取出第一行，當ID=10時，將該行資料放入結果集。若不符合則字啊次呼叫引擎介面獲取下一行，再次判斷，直至最後一行資料。
    • 將結果集存入快取、返回給客戶端。

  • 表T中ID欄位有索引：

    • 呼叫InnoDB的"獲取滿足條件的第一行"介面，server層還會再判斷一次值是否正確，然後放入結果集，接著繼續訪問"滿足條件的下一行"介面，這些介面是引擎已經定義好的。

在MySQL的慢查詢日誌中，rows_examined欄位表示該語句執行過程中掃描了多少行，這個值就是呼叫引擎獲取資料行的時候新增的。

某些場景下，執行器呼叫一次，引擎內部會掃描很多行，因此引擎掃描行數跟rows_examined並不完全相同，這一點後文會詳細說明。

評論區知識點：

  • 問題：如果查詢語句中的欄位不存在，會在哪個階段報錯？

    • 答案：會在分析器階段報錯。
    • 《高效能MySQL》提到解析器和前處理器 解析器：處理語法和解析查詢，生成一顆對應的解析樹。 前處理器：進一步檢查解析樹的合法性，比如：資料表和資料列是否存在，別名是否有歧義等。如果通過則生成新的解析樹，在提交給優化器。

  • Connect_timeout 指的是“連線過程中”的等待時間

  • wait_timeout指的是“連線完成後，使用過程中”的等待時間

本文中含有極客時間《MySQL實戰》的圖和部分原文，如有侵權，請聯絡我立刻刪除
第二節：MySQL系列之一條更新SQL的生命歷程

相關文章