MySQL 的基本架構

MySQL 主要分成兩個層面

  • Server 層
  • 儲存引擎層

資料來源:MySQL 實戰 45 講

Server 層

在 Server 層內有又可分為

  • 連接器
  • 緩存
  • 分析器
  • 優化器
  • 執行器

一般而言執行順序會依 連接器 -> 分析器 -> 優化器 -> 執行器這個順序進行。
如果 client 的 query 在緩存有結果的話,會直接返回。

連結器

主要功能:

  1. 與 client 建立連線
  2. 取得權限
  3. 維持、管理連線

連結完成後,如果沒有動作,會依然保持連線直到被斷開。 我們可以透過 show processlist 來知道當前 server 內有多少連線。

使用長連線的優缺點:

  • 優點
    • 不會頻繁的建立連線
  • 缺點
    • 長連線可能會佔用內存,這些內存會需要等到斷開連結後才會釋放

但如果你不想斷開連結,又想釋放這些內存的話,可以執行 mysql_reset_connection

分析器

主要功能:

  • 分析語法
  • 判斷語法是否正確,如該表是否存在、該列是否存在

如果出現錯誤會出現像下面的提示

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

優化器

經過分析器之後,一段語法有可能會有多種操作都可以達到相同的效果。 優化器這裡會幫你決定哪個是最有效率的。

執行器

舉例來說

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

執行順序

  • 確認 client 有沒有讀這個表 T 的權限
  • 打開表從第一行判斷 ID 是不是 10 ,如果是存在結果集中
  • 遍歷完後把結果返回給 client

我們有時候會想要知道一個 query 到底掃描過了多少資料我們可以透過 row_examine 來取得,但有可能跟儲存引擎所得到的數據並不相同。

Redo log & Bin log

Redo Log

Redo Log 的主要功能:我們常會需要對資料進行更新等操作,但如果我們每次都去對 HD 做操作會很沒有效率,所以我們會需要一個暫時紀錄的地方,等到有空的時候,再把所有的紀錄都記到 HD 內。

資料來源:MySQL 實戰 45 講

Redo Log 特點:

  • InnoDB 特有
  • 屬於物理 Log 紀錄某個數據頁上做了什麼修正 (diff)
  • 循環寫入
  • write_pos 會隨著寫入 Log 往後移,直到遇到了 check_point
  • 如果 write_pos 遇到 check_point 就會先把一些資料寫入 HD 內,再將 check_point 往後移

Bin Log

特點:

  • MySQL 獨有的 Log ,所有的儲存引擎都可以使用
  • Bin Log 是邏輯日誌,紀錄的是 query 的原始邏輯

Update Query 流程

資料來源:MySQL 實戰 45 講 | 淺色為 InnoDB 內執行 | 深色為執行器內執行

二階段提交

上圖中可以看到最後三步是

  1. 寫入 redo log ,進入 prepare 階段
  2. 寫 bin log
  3. 提交 commit

為什麼會需要兩階段提交?

因為如果有任一 log 出現失敗的情況就會發生,數據不一致的狀況。這樣我們拿著些 log 來回復資料庫就會出現錯誤。
所以我們透過二階段提交可以確保兩個 log 的資料一致(因為要兩個 log 寫完後才 commit)。
所以只要其一失敗,就可以透過 rollback 來回復到先前狀態。

Transaction 隔離

Transaction 的主要功能:確保一系列的 database 操作,全部成功或是全部失敗。

Transaction 特色:

  • 實作在引擎層
  • ACID
  • 隔離做得越好,效率越低

為什麼會需要 Transaction 隔離?

因為 Database 讀取的時候會有三個主要問題:

  1. Dirty Read :在讀取的時候,有另一個 transaction 也在更新相同資料但尚未 commit 就讀到了。
  2. Non-repeatable reads:你在一個 transaction 內有兩個相同的 query 語法,但取得的資料卻不同(因為被別的 transaction 修改了)
  3. Phantom reads:一個 transaction 內讀取兩次得到的數量不同(其他 transaction 增加或刪除筆數)

所以 transaction 提供不同的隔離級別來防止上方的問題(由低至高排序):

  1. read uncommitted: transaction 可以讀到其他 transaction 還沒 commit 前的資料
  2. read committed: transaction 只能讀到其他 transaction commit 後的資料(解決 dirty read)
  3. repeatable read: 在同一個 transaction 內只要 query 相同,讀到的資料就會相同。
  4. serializable: 讀寫都加鎖

Transaction Isolation 的實現

資料來源:MySQL 實戰 45 講

每個 transaction 在開始的時候都會從 log 中取得一個 read-view 。 透過者個方式可以確保,在這個 transaction 內都會得到相同的資料。

小結

以上都是我在 MySQL 實戰 45 講上的一些統整。

如果有錯誤的地方歡迎來信。