Loading...

這篇文章將介紹 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

3分鐘小測驗,找到自己的web開發學習入口

成為企業渴求的程式人才!

在家學會 JavaScript 網路開發

全新「全端 Web App 開發」課程,給你看得見的學習成效!
超過 90% 轉職成功,400 位來自亞洲各國的 ALPHA Camp 校友,畢業後達成轉職、創業、出國工作的夢想!

3 分鐘選課指南

給期待創新改變的你

前端x後端x全端 完整工程師技能樹

90% 學生轉職成功,職涯競爭力更上層樓
最專業的「全端 Web App 開發」課程,上班族邊工作也能同時培養第二專長!

3 分鐘選課指南

學期一|程式設計入門

零基礎也學得會的程式入門課!

開始學帶得走的技能,為自己未來的成長鋪路

學期二|掌握網頁開發

系統化學習 JavaScript

實作打好前後端基礎,成為扎實的網頁開發者

學期三|軟體工程師養成

養成業界接軌的實戰能力

前端/全端工程師專修路徑,完成技能與求職準備,成為業界即戰力