mysql學習筆記-底層原理詳解

邱志強發表於2022-03-22
MySql筆記
前言
我相信每一個程式設計師都避免不了和資料庫打交道，其中Mysql以其輕量、開源成為當下最流行的關係型資料庫。Mysql5.0以前以MyISAM作為預設儲存引擎，在5.5版本以後，以InnoDB作為預設儲存引擎，相比MyISAM，InnoDB完整的支援ACID事務特性,同時支援行級鎖，支援事務這一特性也決定了InnoDB代替MyISAM成為主流儲存引擎的一大決定性因素。

Mysql系統架構圖

mysql整體包含四大部分：

連線層：包含各種元件和連線互動介面(Connectors:各種語言可以操控SQL的基礎，支援各種語言)
核心層：也被稱作SQL Laye,包括安全、許可權、sql解析、快取、執行優化。我們常用的檢視、儲存過程、觸發器等功能的實現也集中在這一層
儲存引擎層：也被稱為Storage Engine Layer，由多種儲存引擎共同組成，負責儲存和獲取資料。伺服器是通過儲存引擎API來與它們互動的。MySQL中的儲存引擎可以實現外掛式管理，它提供了一系列標準的管理和服務支援。
系統層：實際儲存一些資料庫檔案以及日誌檔案等

Sql執行流程圖

連線層

如上圖(Sql執行流程)所示，我們Sql執行首先是客戶端向MySQL伺服器傳送一條查詢請求，與connectors互動，連線池認證相關處理。
  • 半雙工通訊：Mysql客戶端/服務端通訊協議是一種半雙工的通訊通道，什麼叫半雙工呢？半雙工指的是允許客戶端和服務端雙方相互通訊，但是同一時刻只允許存在單向通訊。對於查詢sql來講，大多數都是客戶端傳送的查詢資料包較小且為單個資料包，服務端返回的資料包較大且較大時大多拆分為多個資料包，多個資料包都需要被客戶端完整接收才算是查詢結束，這也是為什麼在實際開發中要求我們避免使用select *以及增加limlt查詢條件的原因之一。
  • Connectors(聯結器)的職責就是維護上述過程中的連線通到，包括建立連線、許可權表驗證、維持連線和關閉連線。新建和關閉應該不用過多描述；許可權表驗證是從user、host、db等表查出許可權，放置在連線的上下文中(也就是說已經開啟的連結不受許可權變更影響)。維持連線則指的是Connectors需要保障完全接收伺服器響應的資料包，不能出現丟包的情況。
  • Connection Pool(連線池) ：作為一個單程式多執行緒的應用，mysql連線也參照實現一種池化

對於mysql連線，任何時刻都有一個狀態，該狀態表示了mysql當前正在做什麼。使用show full processlist命令檢視當前狀態。下面是這些狀態的解釋：

  • sleep：執行緒正在等待客戶端傳送新的請求；
  • query：執行緒正在執行查詢或者正在將結果傳送給客戶端；
  • locked：在mysql伺服器層，該執行緒正在等待表鎖。在儲存引擎級別實現的鎖，例如InnoDB的行鎖，並不會體現線上程狀態中。對於MyISAM來說這是一個比較典型的狀態。
  • analyzing and statistics：執行緒正在收集儲存引擎的統計資訊，並生成查詢的執行計劃；
  • copying to tmp table：執行緒在執行查詢，並且將其結果集複製到一個臨時表中，這種狀態一般要麼是做group by操作，要麼是檔案排序操作，或者union操作。如果這個狀態後面還有on disk標記，那表示mysql正在將一個記憶體臨時表放到磁碟上。
  • sorting Result：執行緒正在對結果集進行排序。
  • sending data：執行緒可能在多個狀態間傳送資料，或者在生成結果集，或者在想客戶端返回資料。

核心層

我們之前說過，核心層包括安全許可權(Management Services & Utilities 、)、sql解析(Parser)、快取(cache)、執行優化(Optimizer)四塊，不過還應該再加上SQL Interface(Sql介面)，這一塊主要是承擔和連線層的互動的作用。所以歸納而言
名稱 說明
Management Services & Utilities MySQL 的系統管理和控制工具，包括備份恢復、MySQL 複製、叢集等。
SQL Interface（SQL 介面） 用來接收使用者的 SQL 命令，返回使用者需要查詢的結果。
Parser（查詢解析器） 包含驗證和解析兩部分，以便可以轉換為MySQL優化器可以識別的資料結構或返回 SQL 語句的錯誤。
Optimizer（查詢優化器） 驗證許可權和優化查詢。舉個例子 SELECT id, name FROM student WHERE sex = "女";,SELECT 查詢先根據 WHERE 語句進行選取，而不是將表全部查詢出來以後再進行sex過濾。這就屬於一種優化。
SELECT 查詢先根據 id 和 name 進行屬性投影，而不是將屬性全部取出以後再進行過濾，將這兩個查詢條件連線起來生成最終查詢結果。所以說Mysql是使用“選取-投影-連線”策略進行查詢。
Caches & Buffers（查詢快取） 查詢的時候如果發現快取中有(hash實現)，就直接返回快取中的結果。這個快取機制是由一系列小快取組成的，比如表快取、記錄快取、key 快取、許可權快取等。

Caches & Buffers（查詢快取）

對select查詢結果做快取，這個快取可能包含多個小快取，快取的key值是通過查詢本身、當前要查詢的資料庫、客戶端協議版本號等一些可能影響結果的資訊計算得來。所以兩個查詢在任何字元上的不同 (例如 : 空格、註釋)，都會導致快取不會命中。

MySQL 8.0版本中查詢快取的功能已經被刪除

Management Services & Utilities

主要為Mysql的管理服務和一些工具元件，主要作用是對資料的恢復、回滾，以及資料遷移、複製、後設資料的管理。主要為以下功能
  • 資料庫備份和恢復
  • 資料庫安全管理，如使用者及許可權管理
  • 資料庫複製管理
  • 資料庫叢集管理
  • 資料庫分割槽，分庫，分表管理
  • 資料庫後設資料管理

SQL Interface（SQL 介面）

主要是用來接收Sql資訊和返回執行結果.大體可以分以下幾類：
  • Data Manipulation Language (DML).
  • Data Definition Language (DDL).
  • 儲存過程
  • 檢視
  • 觸發器

Parser（查詢解析器)

  主要是對傳遞過來sql的分解，先對語法進行驗證檢查。語法檢查通過後，解析器會查詢快取，如果快取中有對應的語句，就直接返回結果不進行接下來的優化執行操作。

ps: 快取中資料被修改，會被清出快取。

Optimizer（查詢優化器）

  Optimizer階段主要就是對sql的優化了，通過系統規則選定最優的執行方案。這個過程包括選擇語法、常量轉換與計算、無效程式碼排除、AND/OR等等，必要時還可能查詢儲存引擎，獲得最優策略。

Pluggable Storage Engine(儲存引擎層)

作為Mysql最具有特色的一塊地方，Mysql將儲存引擎作為一個抽象類，InnoDB、MyISAM、BDB、Memory等等都是其子類。5.5版本以後以InnoDB作為其預設實現。目前除mysql以外其他大多都是單一儲存引擎。

ps:儲存引擎是基於表的，而不是資料庫

InnoDB(預設儲存引擎)

  • 完整的支援ACID事務
  • 支援行級鎖
  • 支援外來鍵
  • 使用聚集索引，索引和資料綁在一起在一個邏輯空間上，b+樹葉子節點對於主鍵索引儲存的是資料，對於輔助索引(二級索引)對應的則是主鍵的值
  • count掃全表
  • 必須存在唯一索引(主鍵)

MyISAM

  • 不支援事務，但是插入和更新更快
  • 支援表級鎖
  • 不支援外來鍵
  • 使用非聚集索引，索引和資料分離的，b+樹葉子結點均儲存的是實體資料檔案地址的指標
  • count走變數不走全表
  • 唯一索引非必要

對於儲存引擎的分析在下一篇部落格會著重介紹，這裡先介紹整個Mysql的架構，通過架構可以反應出一個sql的執行流程

檔案系統層

    磁碟最小單位是512位元組，作業系統是4KB，mysql裡最小的是page（頁面）有16K

因為基於儲存引擎的不同，底層檔案結構也會有些不同，比如InnoDB：frm是表定義檔案，ibd是資料檔案，而MyISAM：myd是資料檔案，myi是索引檔案。InnoDB還有redo Log、undo Log

結尾

本篇部落格作為一個Mysql全域性概覽的介紹，由連線層自上而下的說明了整個Mysql的結構組成，但是Mysql核心還是在儲存引擎上，所以後面會專門拿出一篇博文來介紹InnoDB預設引擎，鞏固自己的知識點。

掃一掃，關注我的公眾號

相關文章