Web 技術

MySQL 基本運作介紹，從資料庫交易與 ACID 特性開始

發佈日期: 作者:

這篇文章將介紹 MySQL 的基本運作，MySQL 是如何處理來自使用者或應用程式的請求、如何處理資料修改、不同的 isolation，以及他們與資料庫 ACID (Atomicity, Consistency, Isolation, Durability) 特性之間的關係。

資料庫 (database) 在網路開發 (web development) 當中扮演不可或缺角色，只要應用程式 (application) 有儲存資料的需求，那麼就一定得使用資料庫。雖然資料庫的角色很重要，但卻也是常被忽略的學習主題。

在學習網路開發的初期，通常會先專注在前端頁面的設計、後端 server 的架設，滿足使用者的請求。

至於資料庫的部分，只要學會下載、在本機安裝資料庫，接著使用 ORM  或 ODM (e.g. Sequelize or Mongoose) 操作資料庫，搭配 MySQLWorkbench 或是 Robo3T 來查看資料庫狀態即可。

過去似乎沒有機會去瞭解到資料庫本身是如何運作的，直到有一天學生問了：

「為什麼已經裝了 Sequelize，還要另外安裝開發過程中完全不會碰到的 mysql2，才能使用 MySQL 資料庫呢？」

這才發現自己對於資料庫的認識非常的少。因此就趁這個機會，一起初步認識一下那熟悉又陌生的 MySQL 資料庫吧！

這篇文章將介紹 MySQL 的基本運作，不懂 SQL 語法也是完全沒有問題。這裡不會介紹如何安裝 MySQL、MySQL 的操作語法，更不會介紹 ORM 喔。

延伸閱讀：該用 MySQL 或 MongoDB？選擇資料庫前你該了解的事

MySQL vs. Web application

資料庫最主要的工作，就是接收使用者的讀寫請求，然後實際操作電腦中的檔案系統。這聽起來，其實就像是常見的 web application。和 web application 一樣，資料庫需要

  • 啟動 server 來監聽 port（通訊埠），接收使用者的請求
  • 分析使用者請求、回傳結果
  • 優化效能

當我們啟動資料庫的時候，其實就是啟動資料庫的 server，此時資料庫會監聽 port 3306 並透過這個 port 來接收、回應來自應用程式的請求。

另一方面，應用程式端也需要建立與資料庫的連線，以 Node.js 來說，就是需要安裝 mysql2 這個 database driver 來建立與 MySQL 的連線，並遵循資料庫連線的 protocol。

不過跟一般的應用程式比起來，資料庫更重要的任務，是在操作上滿足 ACID (Atomicity, Consistency, Isolation, Durability) 特性。ACID 分別代表

  • Atomicity (原子性) : 資料操作不能只有部分完成。一次的 transaction 只能有兩種結果：成功或失敗
  • Consistency (一致性)：transaction 完成前後，資料都必須永遠符合 schema 的規範，保持資料與資料庫的一致性
  • Isolation (隔離性)：資料庫允許多個 transactions 同時對其資料進行操作，但也同時確保這些 transaction 的交叉執行，不會導致數據的不一致
  • Durability (耐久性)：transaction 完成後，對資料的操作就是永久的，即便系統故障也不會丟失

在介紹 MySQL 如何滿足 ACID 之前，讓我們先來看看 MySQL 是如何處理來自使用者或應用程式的請求。

MySQL 如何處理請求

如果從 MySQL 的邏輯架構來看，簡單示意圖如下：

由上往下，我們會依序看到

Connection: 

負責與使用者或應用程式建立連線，也負責授權、連線安全等功能。也就是在這裡開始接收使用者的請求

Parser:

負責解析請求當中的 SQL 語法，如果請求不符合 SQL 語法，將會回傳錯誤訊息。

Optimiser:

通過 parser 之後，server 就會知道實際上使用者或應用程式要請求什麼，不過在實際執行之前，會先通過 optimiser 的處理。optimiser 會自動選擇效率較高的操作方式，譬如決定使用什麼 index，或是決定 tables 的連結順序等。

Executor:

在 optimiser 決定最終的操作方式後，executor 會呼叫指定的 storage engine 來執行操作。 MySQL 目前預設的 storage engine 為 InnoDB，不過使用者也可以自行指定 storage engine 來進行資料上的操作。

Storage engines:

Storage engines 負責與電腦的檔案系統互動，實際讀取與寫入硬碟資料。MySQL 提供不同的 storage engines 選擇，像是 InnoDB, MyISAM, CSV, Memory … 等。使用者可以在同一個專案當中，選擇用不同的 engine 來處理不同的資料。

Other services:

上面介紹的是 MySQL 當中的基本功能，當然 MySQL 本身還帶有許多的功能，像是 backup & recovery, security, log 等。

原本 MySQL 有提供 cache (快取) 的功能，當請求送入的時候，可以先查詢快取，看過去是否有同樣的請求，若有，則直接回傳快取當中的結果。不過在 MySQL 8 之後就不提供這樣的功能了。

看到這裡，就會對於 MySQL 處理請求的流程有個基本的認識。不過如果使用者的請求只是想要讀取資料，那麼只要能夠順利連線到資料庫就沒有什麼問題，即便過程中斷線，再次連線就行了。但如果使用者想要修改（寫入、更新、刪除）資料，可能就會遇到一些問題，像是

  • 如何確保資料變動後能夠維持一致性與耐久性
  • 如何確保使用者的請求 transaction 能夠順利完成
  • 如何確保不同的 transactions 間不會互相影響

transaction 是由一個或多個 SQL statement 所組成，目的是確保一個完整的邏輯能夠被完成。譬如 A 匯款給 B，那麼 「從 A 帳戶扣款」和 「存款至 B 帳戶」兩個動作需要被完成，才算是完成一個完整邏輯，也就是完成一個 transaction。

上面這些問題，跟先前提到的 ACID 特性息息相關。接下來，就一起來看看 MySQL 如何處理資料變動的狀況。

透過專案實作，學會後端必懂的資料庫設計

MySQL 如何處理資料修改

當修改資料的請求通過先前提到的 parser, optimiser, executor 之後，storage engine 要真正將資料寫入硬碟之前，還會經過以下步驟

  1. InnoDB 將更新內容紀錄在 redo log，此時狀態為 prepare
  2. MySQL server 將更新內容紀錄在 binlog
  3. InnoDB 將 redo log 狀態改為 commit
  4. InnoDB 在適當時間將資料寫入硬碟

流程看起來好像沒有想像中的簡單，而且此時心中可能會冒出幾個問題：

  1. 為什麼資料不是直接寫入硬碟？而是先更新 redo log 和 binlog？
  2. 什麼是 redo log 和 binlog?
  3. 為什麼 redo log 要分開處理兩次？

關於第一個問題，其實是出於效率上的考量。資料寫入快取記憶體的速度，快過於寫入硬碟的速度，但為了要達到資料耐久性，還是必須將資料寫入硬碟。如果在每次處理修改資料的請求時，都要實際寫入硬碟的話，可能會造成系統的阻塞，降低運作效率。

因此，這裡採用了 WAL (Write-ahead logging) 的方式，也就是將資料變動的紀錄先寫入快取記憶體當中，之後再批次寫入硬碟，以減少寫入次數，以及隨機寫入所造成的效率浪費。

MySQL binlog 和 InnoDB redo log

剛剛提到，所有的資料變動都不會直接被寫入硬碟，而是會先被 server 層的 binlog (binary log) 和 InnoDB 的 redo log 來記錄，如果在操作資料庫的過程中，因為各種原因導致資料毀損或是無法完成完整的資料操作，那麼就可以靠 binlog 和 redo log 將資料回溯，藉此達到資料耐久性的要求。

不過之所有會有兩種 log，是因為當年預設 storage engine 還不是 InnoDB 的時候，並不是所有的 storage engine 都有可以回溯資料的  log 功能，因此 MySQL server 需要自帶一個 binary log。

另一方面。即使 InnoDB 有 redo log，實務上還是需要同步紀錄在 binlog 當中，因為 redo log 的設計是在「有限的」儲存空間下進行紀錄，如果儲存空間用盡，則會開始刪除最舊的資料。因此，如果單單使用 InnoDB 的 redo log 的話，只能回溯一段長度時間的資料。而 binlog 則是會開啟新的檔案空間進行紀錄，所以基本上可以回溯所有時間的操作。

有 log，若執行到一半的 transaction 發生問題，就會依據 log 的紀錄將狀態回溯到執行前，藉此讓 transaction 能夠只有成功或失敗的狀態，不會留下半完成的狀態 (Atomicity)。

MySQL 官方建議將兩種 log 的功能同時開啟，以確保資料的耐久性。

最後提一下關於 binlog 和 redo log 的差別，前者是 “logical log”˙，也就是操作邏輯上的變動；後者則是 “physical log”，也就是實際資料上的變動。

Two-phase commit

有兩種 log 就像是開了雙保險，讓人感覺安心不少，但這時候又有另外一個問題：如何讓兩種 log 的紀錄內容同步呢？如果兩種 log 的內容不同步，那麼在回溯資料的時候豈不就天下大亂了！

為了確保紀錄同步，MySQL 採用了 Two-phase commit 的操作。當請求出現之後，redo log  會先寫下這筆紀錄，並通知 binlog，這時候在 redo log 的紀錄狀態僅僅只是 “prepare”。當 binlog 收到通知並完成紀錄之後，會回頭通知 redo log ，redo log 會正式 “commit” 紀錄。這也就是為什麼，會看到在修改資料的過程中， redo log 會有兩次的操作。

在 two-phase commit 的過程中，如果兩方有任何一方沒有成功回應並完成動作，那麼就會將狀態回溯並退回該請求，藉此確保了資料的一致性。

不同的 Isolation

看完了 MySQL 如何處理請求、透過 redo log 和 binlog 來達到資料的 “‘Atomicity”, Consistency”, “Durability”，最後來看看 ACID 當中的 “Isolation” 是如何運作的。

Isolation 的意思是，同時間多個 transactions 不會互相影響，這裡的影響包含了讀取資料以及寫入資料。在 SQL 的標準當中，有四種不同的 Isolation 等級，分別定義了「資料變動的可見程度」，簡單說明如下：

Read Uncommitted: 

Transaction A 尚未 commit 的變更，可以被 transaction B 讀到。在這樣的情況下，很有可能發生 transaction A 並沒有完成 commit 然後 rollback，但 transaction B 卻看到操作過程中出現、但已經不存在的變更。這樣的狀況也稱作 “dirty read”

Read Committed: 

只有 transaction A 已經 commit 的變更，才可以被 transaction B 讀到。這樣聽起來很合理，但如果 transaction B 開始時，先讀了一次某筆資料，這時 transaction A 更改了該資料後，transaction B 再讀一次同樣的資料，就會發現看到不一樣的結果，這樣變成同一次 transaction 讀同一筆資料可能會讀到不同的值。

Repeatable Read: 

同樣只有 transaction A 已經 commit 的變更，才可以被 transaction B 讀到，而且從  transaction B 開始到結束的過程中，讀同一筆資料的值都會是一樣的，不會被其他 transaction 所影響。

Serializable:

最後，更萬無一失的作法是，同一筆資料的「讀」和「寫」，同一時間只能被一個 transaction 所操作。如果 transaction A 先讀取了一筆資料，此時 transaction B 想要讀取同一筆資料，就需要等待 transaction A 完成 commit 才能讀取。

這樣的聽起來很不錯，但實際上 Isolation 的程度越嚴謹，就會消耗更多效能。另外在 Serializable 的情況有加入了讀鎖、寫鎖，因此會導致浪費互相等待的時間，甚至是請求超時。

因此，MySQL 預設的 Isolation 等級是 Repeatable Read 而不是 Serializable。

舉個例子來說明，現在有兩個 transactions，分別在不同的時間點執行不同的操作。資料 k 的值為 1，在某個時間點會被 transaction B 修改為 k = k + 1，也就是值為 2。在不同的 Isolation 等級下，來看看 transaction A 在不同的時間看到的 k 值 (X1, X2, X3) 為多少。

在 Read Uncommitted 下，X1、 X2、X3 分別為 2、2、2，因此任何時間點都可以讀取變動後的資料，不需要等待 commit。

在 Read Committed 下分別為 1 、2、2，只有在 commit 之後才讀得到變動後的資料

在 Repeatable Read 下則會是 1、1、2，因為會確保在 transaction A 當中看到的 k 值會一樣。

最後在 Serializable 的情況下，看到的值同樣是 1、1、2，但因為 transaction A 先執行（查詢 k），因此會先把 k 給鎖住，導致 transaction B 要等到 transaction A commit 之後，才能修改 k 值。

結語

文章中簡單介紹了MySQL 如何處理請求、如何處理資料修改、不同的 isolation，以及他們與資料庫 ACID 特性之間的關係。當然，有許多的細節沒有在這裡提到或深入探究，資料庫本身是一門博大精深的學問，無法單用一篇文章就能夠好好介紹。希望這能讓讀者對資料庫有初步的認識，並且產生更多的好奇與興趣，開啟另外一扇學習的大門

參考資料:

（本文作者是ALPHA Camp助教TD）