站長(zhǎng)資訊網(wǎng)今天MySQL教程欄目為大家介紹我所理解的基礎(chǔ)架構(gòu)。
作為一個(gè)正經(jīng)的 CRUD 工程師，與數(shù)據(jù)庫(kù)的交互是日常工作中比重較大的內(nèi)容，比如日常迭代的增刪改查、處理歷史數(shù)據(jù)、優(yōu)化 SQL 性能等等。隨著項(xiàng)目數(shù)據(jù)量的增長(zhǎng)，從前為了趕項(xiàng)目進(jìn)度而埋下的深坑正慢慢顯露它們的威力，這也讓我不得不全面且深入的學(xué)習(xí) MySQL，而不僅僅是停留在基礎(chǔ)的 CRUD 上。
MySQL 系列的第一篇，主要介紹 MySQL 的基礎(chǔ)架構(gòu)以及各個(gè)組成部分的功能，包括 Server 層的 bin log 和 InnoDB 特有的 redo log 這兩種日志模塊。
1. MySQL 架構(gòu)簡(jiǎn)介
根據(jù) DB-Engines 發(fā)布的最受歡迎的數(shù)據(jù)庫(kù)管理系統(tǒng)排行榜，MySQL 穩(wěn)坐第二把交椅。
作為最受歡迎的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，MySQL 采用的是C/S架構(gòu)，即 Client & Server 架構(gòu)。比如開(kāi)發(fā)者使用 Navicat 連接到 MySQL，那么前者就是客戶(hù)端，后者就是服務(wù)端。
同時(shí)，MySQL 也是單進(jìn)程多線(xiàn)程的數(shù)據(jù)庫(kù)。這很好理解，正在運(yùn)行的 MySQL 實(shí)例就是那個(gè)“單進(jìn)程”，而在這個(gè)進(jìn)程中會(huì)有很多個(gè)線(xiàn)程，比如主線(xiàn)程 Master Thread，IO Thread 等，這些線(xiàn)程被用于處理不同的任務(wù)。
2. MySQL 組成部分
前面說(shuō)到 MySQL 采用的是C/S架構(gòu)，用戶(hù)通過(guò)客戶(hù)端連接到 MySQL 服務(wù)器，然后提交 SQL 語(yǔ)句到服務(wù)器，然后服務(wù)器就會(huì)把執(zhí)行結(jié)果返回給客服端。
在這一小節(jié)的內(nèi)容中，我們主要關(guān)注 MySQL 服務(wù)端的邏輯組成，先來(lái)看一張圖。
從上圖可以看到，與客戶(hù)端的交互中，MySQL 的服務(wù)端分別經(jīng)過(guò)了連接器、查詢(xún)緩存、分析器、優(yōu)化器、執(zhí)行器和存儲(chǔ)引擎這幾部分。
下面就以一條簡(jiǎn)單的查詢(xún)語(yǔ)句來(lái)描述 MySQL 服務(wù)端的各組成部分及它們所起的作用。
2.1 連接器
在客戶(hù)端提交查詢(xún)語(yǔ)句之前，需要與服務(wù)端建立連接。所以最先來(lái)到的是連接器，連接器的作用就是負(fù)責(zé)與客戶(hù)端建立、管理連接，同時(shí)查詢(xún)用戶(hù)的權(quán)限。
需要注意的是：
- 連接器只獲取用戶(hù)的權(quán)限，并不做校驗(yàn)，校驗(yàn)是在查詢(xún)緩存或執(zhí)行器才進(jìn)行。
- 一旦建立連接同時(shí)獲取用戶(hù)的權(quán)限之后，只有建立新的連接才會(huì)刷新用戶(hù)權(quán)限。
- 對(duì)于長(zhǎng)時(shí)間沒(méi)有發(fā)送請(qǐng)求的客戶(hù)端，連接器會(huì)自動(dòng)斷開(kāi)連接。這里的「長(zhǎng)時(shí)間」是由 wait_timeout 參數(shù)來(lái)決定的，它的默認(rèn)值為8小時(shí)。
2.2 查詢(xún)緩存
在經(jīng)過(guò)連接器的建立連接、獲取用戶(hù)權(quán)限之后，接下來(lái)用戶(hù)可以提交查詢(xún)語(yǔ)句了。
最先經(jīng)過(guò)的是查詢(xún)緩存部分，由它的名字也能夠猜到，查詢(xún)緩存的作用就是查詢(xún) MySQL 是否執(zhí)行過(guò)客戶(hù)端提交的查詢(xún)語(yǔ)句，如果這條 SQL 之前執(zhí)行過(guò)，并且用戶(hù)對(duì)該表有執(zhí)行該語(yǔ)句的權(quán)限，就會(huì)直接返回之前執(zhí)行的結(jié)果。
所以在某些時(shí)候，多次執(zhí)行一句 SQL 并不能得到它的平均執(zhí)行時(shí)間，因?yàn)椴樵?xún)緩存的關(guān)系，后面的執(zhí)行時(shí)間往往比第一次執(zhí)行要短。
如果你不想使用緩存，可以在每次查詢(xún)后都用 update 語(yǔ)句更新表，當(dāng)然這是非常麻煩并且憨的方法。MySQL也提供了相應(yīng)的配置項(xiàng)—— query_cache_type，你可以在 my.cnf 文件中將 query_cache_type 設(shè)置為0以關(guān)閉查詢(xún)緩存。
需要注意的是：
- 查詢(xún)緩存部分是以
key-value形式進(jìn)行存儲(chǔ)的，key 為查詢(xún)語(yǔ)句，value 是查詢(xún)結(jié)果。 - 當(dāng)對(duì)數(shù)據(jù)表進(jìn)行更新時(shí)，關(guān)于這張表的所有查詢(xún)緩存都會(huì)失效，所以一般來(lái)說(shuō)查詢(xún)緩存的命中率是很低的。
- 在
MySQL 8.0的版本中，查詢(xún)緩存的功能已經(jīng)被刪除。
2.3 分析器
我使用的 MySQL 版本是5.7.21，所以客戶(hù)端提交的查詢(xún)語(yǔ)句會(huì)走查詢(xún)緩存，如果沒(méi)有命中，那么將繼續(xù)往下走，來(lái)到分析器。
分析器會(huì)對(duì)提交的語(yǔ)句進(jìn)行詞法分析（解析語(yǔ)句）和語(yǔ)法分析（判斷語(yǔ)句是否符合 MySQL 的語(yǔ)法規(guī)則），所以分析器的作用就是解析 SQL 語(yǔ)句并檢查其合法性。
需要注意的是：
- MySQL 在檢查 SQL 語(yǔ)句合法性時(shí)，僅會(huì)在最先不符合 MySQL 語(yǔ)法規(guī)則的地方提示錯(cuò)誤，并不會(huì)將 SQL 語(yǔ)句中所有語(yǔ)法錯(cuò)誤的地方全部展示。
舉個(gè)例子：
select * form user_info limit 1;復(fù)制代碼
上面這句 SQL 有兩個(gè)錯(cuò)誤，第一是 from 拼寫(xiě)錯(cuò)誤，第二是不存在 user_info 這張表，在執(zhí)行之后，MySQL只會(huì)提醒一個(gè)錯(cuò)誤，下面展示了三次執(zhí)行 SQL 的結(jié)果信息。
第一次的執(zhí)行信息： 1064 - 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 'form user_info limit 1' at line 1, Time: 0.000000s 修改為from后第二次的執(zhí)行信息：1146 - Table 'windfall.user_info' doesn't exist, Time: 0.000000s 修改為 user 表后第三次的執(zhí)行信息： OK, Time: 0.000000s復(fù)制代碼
2.4 優(yōu)化器
在校驗(yàn)了 SQL 語(yǔ)句的合法性之后，MySQL 已經(jīng)知道用戶(hù)提交的語(yǔ)句是干什么的了，但是在真正執(zhí)行之前，還需要經(jīng)過(guò)非?！靶W(xué)”的優(yōu)化器。
優(yōu)化器的作用是為 SQL 語(yǔ)句生成最優(yōu)的執(zhí)行計(jì)劃。
之所以說(shuō)優(yōu)化器很“玄學(xué)”，是因?yàn)樗趦?yōu)化 SQL 語(yǔ)句的過(guò)程中可能會(huì)生成出乎用戶(hù)意料之外的執(zhí)行計(jì)劃（索引選擇、多表關(guān)聯(lián)連接順序、隱式函數(shù)轉(zhuǎn)換等）。當(dāng)然優(yōu)化器有時(shí)候也會(huì)“選錯(cuò)”索引，這與數(shù)據(jù)量、索引統(tǒng)計(jì)信息等因素有關(guān)。
需要注意的是：
- 如果你需要優(yōu)化一條生產(chǎn)環(huán)境的 SQL，請(qǐng)盡量在本地還原與生產(chǎn)環(huán)境數(shù)據(jù)量相同的表，然后根據(jù)執(zhí)行計(jì)劃進(jìn)行優(yōu)化。
- 在寫(xiě)查詢(xún)語(yǔ)句的時(shí)候，一定要考慮到索引的最左匹配原則（關(guān)于最左匹配原則的整理在索引篇再寫(xiě)）。
關(guān)于 MySQL 優(yōu)化器的工作流程，可以看看這篇博客：MySQL 優(yōu)化器原來(lái)是這樣工作的
MySQL 的執(zhí)行計(jì)劃也是一項(xiàng)必須要掌握的技能，這篇博客寫(xiě)得非常詳細(xì)，值得一讀：不會(huì)看 Explain執(zhí)行計(jì)劃，勸你簡(jiǎn)歷別寫(xiě)熟悉 SQL優(yōu)化
2.5 執(zhí)行器
在優(yōu)化器生成了 MySQL 認(rèn)為最優(yōu)的執(zhí)行計(jì)劃之后，最后來(lái)到了執(zhí)行器，執(zhí)行器的作用當(dāng)然就是執(zhí)行SQL語(yǔ)句了。
但是在執(zhí)行之前，先要做權(quán)限驗(yàn)證，驗(yàn)證用戶(hù)對(duì)表是否有查詢(xún)權(quán)限。然后再根據(jù)表定義的引擎類(lèi)型，去使用相對(duì)應(yīng)引擎提供的接口來(lái)對(duì)該表進(jìn)行條件查詢(xún)，最后將該表所有滿(mǎn)足條件的數(shù)據(jù)行作為結(jié)果集返回客戶(hù)端，這樣整個(gè) SQL 的執(zhí)行就結(jié)束了。
需要注意的是：
- 在執(zhí)行器執(zhí)行 SQL 語(yǔ)句前會(huì)做校驗(yàn)：判斷用戶(hù)對(duì)表是否具有操作權(quán)限。
2.6 存儲(chǔ)引擎
MySQL 支持的存儲(chǔ)引擎有很多種，比如：InnoDB、MyISAM、Memory 等等。
2.6.1 InnoDB
InnoDB 是當(dāng)下最常用的的 MySQL 存儲(chǔ)引擎，同時(shí)也是 MySQL 5.5 之后的默認(rèn)存儲(chǔ)引擎。
InnoDB 支持事務(wù)、MVCC（多版本并發(fā)控制）、外鍵、行級(jí)鎖和自增列。但是 InnoDB 不支持全文索引，同時(shí)它占用的數(shù)據(jù)空間更大。
2.6.2 MyISAM
MyISAM 是 MySQL 5.1 及之前的默認(rèn)存儲(chǔ)引擎，支持全文索引、壓縮、空間函數(shù)、表級(jí)鎖。
MyISAM 的數(shù)據(jù)以緊密格式存儲(chǔ)所以占用空間更小，它擁有較高的插入和查詢(xún)速度，但是 MyISAM 不支持事務(wù)，且崩潰后無(wú)法安全恢復(fù)。
2.6.3 Memory
Memory 的所有數(shù)據(jù)都保存的內(nèi)存中，由于不需要磁盤(pán) I/O，所以它的速度比 MyISAM 和 InnoDB 快了一個(gè)數(shù)量級(jí)。但如果數(shù)據(jù)庫(kù)關(guān)閉或重啟，Memory 引擎的數(shù)據(jù)就會(huì)消失。
Memory 支持 Hash 索引，但由于它使用表級(jí)鎖，因此并發(fā)寫(xiě)入的性能比較低。
值得一提的是，MySQL 中的臨時(shí)表，一般是用 Memory 表保存的，如果中間表數(shù)據(jù)量過(guò)大或含有 BLOB 類(lèi)型或 TEXT 類(lèi)型的字段，就會(huì)使用 MyISAM 表。
關(guān)于存儲(chǔ)引擎，由于本人接觸的比較少，等看完《MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎》之后再整理，這里只是簡(jiǎn)單地提一下。
3. 日志模塊
前面所說(shuō)的執(zhí)行流程主要是描述查詢(xún)語(yǔ)句，如果是更新語(yǔ)句還涉及到 MySQL 的日志模塊。
從客戶(hù)端到執(zhí)行器的之間的邏輯查詢(xún)語(yǔ)句和更新語(yǔ)句是相同的，只是在到執(zhí)行器這一層的時(shí)候，更新語(yǔ)句會(huì)和 MySQL 的日志模塊產(chǎn)生交互，這是查詢(xún)語(yǔ)句和更新語(yǔ)句不一樣的地方。
3.1 物理日志 redo log
3.1.1 redo log 中記錄的內(nèi)容
對(duì)于 InnoDB 存儲(chǔ)引擎來(lái)說(shuō)，它有一個(gè)特有的日志模塊——物理日志（重做日志）redo log，它是 InnoDB 存儲(chǔ)引擎的日志，它所記錄的是數(shù)據(jù)頁(yè)的物理修改。
舉個(gè)例子，現(xiàn)在有一張 user 表，有一條主鍵 id=1，age=18 的數(shù)據(jù)，然后用戶(hù)提交了下面這條 SQL，執(zhí)行器準(zhǔn)備執(zhí)行。
update user set age=age+1 where id=1;復(fù)制代碼
對(duì)于這條 SQL，在 redo log 中記錄的內(nèi)容大致是：將 user 表中主鍵 id=1 行的 age 字段值修改為19。
3.1.2 WAL
MySQL 的更新持久化邏輯運(yùn)用到了 WAL(Write-Ahead Logging，寫(xiě)前日志記錄) 的思想：先寫(xiě)日志，再寫(xiě)磁盤(pán)。
需要注意的是這里的寫(xiě)日志也是寫(xiě)到磁盤(pán)中，但由于日志是順序?qū)懭?/strong>的，所以速度很快。而如果沒(méi)有 redo log，直接更新磁盤(pán)中的數(shù)據(jù)，那么首先需要找到那條記錄，然后再把新的值更新進(jìn)入，由于查詢(xún)和讀寫(xiě)I/O，就相對(duì)會(huì)慢一些。
最后，當(dāng) InnoDB 引擎空閑的時(shí)候，它會(huì)去執(zhí)行 redo log 中的邏輯，將數(shù)據(jù)持久化到磁盤(pán)中。
3.1.3 redo log 日志文件
redo log 日志文件大小是固定的，我把它理解為一個(gè)循環(huán)鏈表，鏈表的每個(gè)節(jié)點(diǎn)都可以存放日志，在這個(gè)鏈表中有兩個(gè)指針：write（黑） 和 read（白）。
最開(kāi)始這兩個(gè)指針都指向同一個(gè)節(jié)點(diǎn)，且節(jié)點(diǎn)日志元素都為空，表示此時(shí) redo log 為空。當(dāng)用戶(hù)開(kāi)始提交更新語(yǔ)句，write 節(jié)點(diǎn)開(kāi)始往前移動(dòng)，假設(shè)移動(dòng)到3的位置。而此時(shí)的情況就是 redo log 中有1-3這三個(gè)日志元素需要被持久化到磁盤(pán)中，當(dāng) InnoDB 空閑時(shí)，read 指針往前移動(dòng)，就代表著將 redo log 持久化到磁盤(pán)。
但這里有一種特殊情況，就是 InnoDB 一直沒(méi)有空閑，write 指針一直在寫(xiě)入日志，直到它寫(xiě)到5的位置，再往前寫(xiě)又回到了最開(kāi)始1的位置（也就是上圖的位置，但不同的是鏈表節(jié)點(diǎn)中都存在日志數(shù)據(jù)）。
此時(shí)發(fā)現(xiàn)1的位置已經(jīng)有日志數(shù)據(jù)了，同時(shí) read 指針也在。那么這時(shí)候 write 指針就會(huì)暫停寫(xiě)入，InnoDB 引擎開(kāi)始催動(dòng) read 指針移動(dòng)，把 redo log 清空掉一部分之后再讓 write 指針寫(xiě)入日志文件。
3.1.4 redo log 的作用
我們已經(jīng)知道，redo log 中記錄的是數(shù)據(jù)頁(yè)的物理修改，所以 redo log 能夠保證在數(shù)據(jù)庫(kù)發(fā)生異常重啟時(shí)，記錄尚未寫(xiě)入磁盤(pán)，但是在重啟后可以通過(guò) redo log 來(lái)“redo”，從而不會(huì)發(fā)生記錄丟失的情況，保證了事務(wù)的持久性。
這一能力也被稱(chēng)作 crash-safe。
3.2 歸檔日志 bin log
前面說(shuō)到 redo log 是 InnoDB 特有的日志，而 bin log 則是屬于 MySQL Server 層的日志，在默認(rèn)的 Statement Level 下它記錄的是更新語(yǔ)句的原始邏輯，即 SQL 本身。
另外需要注意的是：
- bin log 的日志文件大小并不固定，它是“追加寫(xiě)入”的模式，寫(xiě)完一個(gè)文件后會(huì)切換到下一個(gè)文件寫(xiě)入。
- bin log 沒(méi)有 crash-safe 的能力。
- bin log 是在事務(wù)最終提交前寫(xiě)入的，而 redo log 是在事務(wù)執(zhí)行中不斷寫(xiě)入的。
3.2.1 bin log 的作用
與 redo log 不同的是，bin log 常用于恢復(fù)數(shù)據(jù)，比如說(shuō)主從復(fù)制，從節(jié)點(diǎn)根據(jù)父節(jié)點(diǎn)的 bin log 來(lái)進(jìn)行數(shù)據(jù)同步，實(shí)現(xiàn)主從同步。
3.3 兩階段提交
為了讓 redo log 和 bin log 的狀態(tài)保持一致，MySQL 使用兩階段提交的方式來(lái)寫(xiě)入 redo log 日志。
在執(zhí)行器調(diào)用 InnoDB 引擎的接口將寫(xiě)入更新數(shù)據(jù)時(shí)，InnoDB 引擎會(huì)將本次更新記錄到 redo log 中，同時(shí)將 redo log 的狀態(tài)標(biāo)記為 prepare，表示可以提交事務(wù)。
隨后執(zhí)行器生成本次操作的 bin log 數(shù)據(jù)，并寫(xiě)入 bin log 的日志文件中。
最后執(zhí)行器調(diào)用 InnoDB 的提交事務(wù)接口，存儲(chǔ)引擎把剛寫(xiě)入的 redo log 記錄狀態(tài)修改為 commit，本次更新結(jié)束。
在這個(gè)過(guò)程中有三個(gè)步驟 add redo log and mark as prepare -> add bin log -> commit，即：
- 寫(xiě)入 redo log 日志并標(biāo)記為 prepare
- 寫(xiě)入 bin log
- 提交事務(wù)
如果在第二個(gè)步驟，也就是寫(xiě)入 bin log 之前系統(tǒng)崩潰或重啟，啟動(dòng)后由于 bin log 中沒(méi)有記錄，會(huì)將 redo log 中的記錄回滾至執(zhí)行本次更新語(yǔ)句前。
如果在第三個(gè)步驟前，也就是提交之前系統(tǒng)崩潰或重啟，即便沒(méi)有 commit 但是滿(mǎn)足 redo log 中記錄為 prepare 狀態(tài)并且 bin log 中也有完整記錄，在重啟后會(huì)自動(dòng) commit，并不會(huì)回滾。
4. 小結(jié)
本文主要介紹 MySQL 的基礎(chǔ)架構(gòu)以及各個(gè)組成部分的功能，最后介紹了 MySQL Server 層的 bin log 和 InnoDB 特有的 redo log 這兩種日志模塊。
5. 溫故知新
以下的幾個(gè)問(wèn)題是對(duì)本文所描述內(nèi)容的提問(wèn)，鞏固知識(shí)，正所謂“溫故而知新，可以為師矣”。
- 如果查詢(xún)語(yǔ)句中字段不存在、字段有歧義、關(guān)鍵字拼寫(xiě)錯(cuò)誤，是由哪個(gè)部分報(bào)錯(cuò)？
- 如果用戶(hù)對(duì)表沒(méi)有查詢(xún)權(quán)限，是哪個(gè)部分報(bào)錯(cuò)？
- 為什么 MySQL 的查詢(xún)緩存會(huì)無(wú)效？
- 一條 select 查詢(xún)語(yǔ)句是如何執(zhí)行的？
- MySQL 常用的存儲(chǔ)引擎有哪些？
- MySQL 的日志模塊有哪些？分別起到什么作用？
- redo log 寫(xiě)滿(mǎn)了怎么辦？
- 如何理解 redo log 的兩階段提交？
-
redo log 和 bin log 的區(qū)別？
