重生之從零設(shè)計(jì) MySQL 架構(gòu)

作者：碼哥 2024-11-11 08:31:32

這一層至關(guān)重要，里面還還會(huì)劃分為「連接與安全管理」和「SQL 解析和優(yōu)化」兩大模塊。服務(wù)層是 MySQL 中的核心組件，負(fù)責(zé)提供各種數(shù)據(jù)庫(kù)操作所需的基本功能，如 SQL 語(yǔ)法處理、事務(wù)管理、鎖管理等。

我叫蕭劍臣，今年 34 歲，2024 年快接近尾聲，在一線城市「淺圳」打拼，在一家名叫「網(wǎng)訊」的互聯(lián)網(wǎng)大廠工作，是一名資深后端架構(gòu)師。

已經(jīng)連續(xù)加班到 23:00 半年多才下班，現(xiàn)在已是亥時(shí)，push 完代碼后拿出我在「拼夕夕」買的「zippo」火機(jī)點(diǎn)上一支煙，看著它逐漸沒(méi)有痕跡，空氣中聞到一股股淡淡的味道。

我彈落的煙灰如此的黯然，黯然如我，思緒萬(wàn)千。我閉上眼睛就是天黑，一種撕裂的感覺(jué)。

我掐滅了煙頭，又重新點(diǎn)上一支煙，沉浸在淡藍(lán)色的煙霧中，是那么的溫柔，那么的迷蒙，那么的深情。

Christina，我想起你了，發(fā)信息給你的手在鍵盤敲很輕，我給的思念很小心。

你說(shuō)：我們不適合，每天 996，起得比雞早，睡得比狗晚，每天忙忙碌碌。天天加班，肚子那么大，頭發(fā)那么少，血糖高、尿素高、脂肪肝。

回到工位，我寫下了一段愿望，希望世間再無(wú) 996，多金身材好，女朋友漂亮，左擁右抱….

或許是因?yàn)殚L(zhǎng)期加班的緣故。忽然，我只覺(jué)心里難受，胸悶氣短，眼前一片黑，我想要努力的睜開(kāi)眼睛，可是卻什么都看不見(jiàn)，逐漸聽(tīng)不見(jiàn)周邊的聲音……

重生

當(dāng)我醒來(lái)睜開(kāi)眼睛的時(shí)候，我看到辦公桌的電腦長(zhǎng)這個(gè)樣子。

啥情況，我猝死了還是穿越了？還在我一臉懵逼的時(shí)候……

系統(tǒng)提示：蕭劍臣，你已經(jīng) dead 過(guò)一次了，現(xiàn)在在另一個(gè)世界獲得重生，你需要在這個(gè)世界完成一個(gè)任務(wù)，設(shè)計(jì)一款數(shù)據(jù)庫(kù) MySQL，就能就能回到原來(lái)的世界，迎娶白富美，脫離 996，并且在這個(gè)世界的成就也能帶回原來(lái)的世界。

打開(kāi)這個(gè)電腦一看，時(shí)間是 1979 年。那時(shí) Bill Gates 退學(xué)沒(méi)多久，微軟公司也才剛剛起步，世間還沒(méi)有 996 和福報(bào)，沒(méi)有過(guò)勞猝死……

這個(gè)狗血，我居然重生了，我的機(jī)會(huì)來(lái)了……

1990 年，我接到了一個(gè)項(xiàng)目，客戶需要為當(dāng)時(shí)的 UNIREG 提供更加通用的 SQL 接口，公司的另一個(gè)團(tuán)隊(duì)負(fù)責(zé)人「郝紀(jì)曉」提議直接使用商用數(shù)據(jù)庫(kù)。

重生后的第一個(gè)副本出現(xiàn)了，我不能讓「郝紀(jì)曉」的提議達(dá)成，否則將永遠(yuǎn) 「狗 dead」。

經(jīng)過(guò)一番測(cè)試后，我發(fā)現(xiàn)商用數(shù)據(jù)庫(kù)的速度并不盡如人意，無(wú)法滿足客戶的需求。于是我聯(lián)合 Monty Widenius，他雄心大起，設(shè)計(jì)一個(gè)數(shù)據(jù)庫(kù)的任務(wù)就此開(kāi)始……

MySQL 架構(gòu)設(shè)計(jì)

腦子里有 MySQL 8.0 版本的架構(gòu)設(shè)計(jì)思路，我在這里豈不是如魚得水，起飛，起飛，必須起飛。于是我把 MySQL 的架構(gòu)設(shè)計(jì)圖畫了出來(lái)，如圖 1-1 所示。

圖 1-1 圖 1-1

看到如此層級(jí)的架構(gòu)設(shè)計(jì)，分層明確，職責(zé)清晰，眾人驚呆了！！

但郝紀(jì)曉不懈的說(shuō)到：劃分這么多層，有什么意義？該不會(huì)是脫褲子放屁多此一舉吧。

我心想，難道這是副第一個(gè)關(guān)卡出現(xiàn)的一個(gè) boss，我看了看他的工牌，上面寫著資深架構(gòu)師——郝紀(jì)曉。

系統(tǒng)提示音：新手任務(wù)，畫出 MySQL 系統(tǒng)架構(gòu)設(shè)計(jì)圖，并解釋每一層以及每層組件的主要作用，讓眾人理解并清晰認(rèn)識(shí)該架構(gòu)，讓大家按照此架構(gòu)開(kāi)發(fā)。

好家伙，這個(gè)任務(wù)難度不大，我自信的給眾人解釋到：這個(gè)數(shù)據(jù)庫(kù)名叫 MySQL，至上而下一共分為四層，重點(diǎn)是 Server 服務(wù)層和存儲(chǔ)引擎層。

客戶端由不同語(yǔ)言開(kāi)發(fā)的客戶端，Server 層包括連接池、安全管理、線程管理、緩存、SQL 接口、解析器、優(yōu)化器等，涵蓋了 MySQL 的大多數(shù)核心功能。

而存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)的讀取。這是一個(gè)插件式架構(gòu)，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。

客戶端 Client

這是一個(gè) CS 架構(gòu)，支持各種語(yǔ)言的客戶端連接器連接到數(shù)據(jù)庫(kù)，比如 Java、C++、JDBC 等。同時(shí)也支持 Shell 腳本直接連接。

Server 服務(wù)層

這一層至關(guān)重要，里面還還會(huì)劃分為「連接與安全管理」和「SQL 解析和優(yōu)化」兩大模塊。

服務(wù)層是 MySQL 中的核心組件，負(fù)責(zé)提供各種數(shù)據(jù)庫(kù)操作所需的基本功能，如 SQL 語(yǔ)法處理、事務(wù)管理、鎖管理等。

連接與安全管理

當(dāng)客戶端發(fā)送連接請(qǐng)求時(shí)，MySQL 服務(wù)器會(huì)在連接與安全管理接收請(qǐng)求，分配一個(gè)線程來(lái)處理該連接，隨后進(jìn)行身份驗(yàn)證。具體的功能如下：

當(dāng)客戶端發(fā)起連接請(qǐng)求時(shí)，MySQL 會(huì)創(chuàng)建一個(gè)專用的線程（以操作系統(tǒng)級(jí)別的線程實(shí)現(xiàn)）來(lái)為該客戶端服務(wù)。
MYSQL 對(duì) TCP 傳輸過(guò)來(lái)的賬號(hào)密碼做身份認(rèn)證、權(quán)限獲取（例如，是否允許客戶端對(duì) world 數(shù)據(jù)庫(kù)中的 Country 表執(zhí)行 SELECT 語(yǔ)句）。驗(yàn)證通過(guò)，查詢賬戶擁有的權(quán)限，并緩存起來(lái)。此鏈接是一個(gè)長(zhǎng)鏈接
對(duì)于 TCP 鏈接，MySQL 采用池化技術(shù)，節(jié)省了 TCP 鏈接創(chuàng)建和銷毀的成本。
一個(gè)客戶端請(qǐng)求，必須要分配一個(gè)線程專門與客戶端進(jìn)行交互，所以還有個(gè)線程池，每一個(gè)鏈接從線程池中獲取一個(gè)線程，省去了創(chuàng)建和銷毀線程的開(kāi)銷。把線程池占滿了，再連就報(bào)連接滿了。

SQL 解析和優(yōu)化

SQL Interface(SQL 接口，用來(lái)接受用戶的 SQL 命令，并返回需要的結(jié)果。比如 select from 就是調(diào)用 SQL Interface。

Parse 解析器

MySQL 解析查詢以創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)（解析樹），然后對(duì)其進(jìn)行各種優(yōu)化，包括重寫查詢、決定表的讀取順序，以及選擇合適的索引等。

Optimizer 優(yōu)化器

通過(guò)語(yǔ)法解析,MySQL 知道你的真實(shí)意圖了，但你寫的 SQL 不一定是高效的。

查詢之前會(huì)使用查詢優(yōu)化器確定 SQL 語(yǔ)句的執(zhí)行路徑，生成一個(gè)執(zhí)行計(jì)劃，這個(gè)執(zhí)行計(jì)劃表明使用哪些索引進(jìn)行查詢。

Caches & Buffers 緩沖區(qū)

MySQL 內(nèi)部維持著一些 Cache 和 Buffer，這個(gè)緩存機(jī)制是由一系列小緩存組成的。比如表緩存，記錄緩存，key 緩存，權(quán)限緩存等。這個(gè)查詢緩存可以在不同客戶端之間共享。

查詢緩存：當(dāng)相同的 SQL 查詢被多次執(zhí)行時(shí)，可以從查詢緩存中直接獲取結(jié)果，提高性能。由于 MySQL 8.0 中已移除了查詢緩存功能，使用者需要自行實(shí)現(xiàn)相關(guān)功能，如使用 Redis、Memcached 等中間緩存系統(tǒng)。
表緩存：用于存儲(chǔ)表的元數(shù)據(jù)，如表的結(jié)構(gòu)定義。當(dāng)查詢需要表信息時(shí)，優(yōu)先從表緩存中獲取，避免磁盤操作。
線程緩存：用于復(fù)用服務(wù)器的連接線程。當(dāng)一個(gè)連接關(guān)閉后，它的線程會(huì)被放回線程緩存池中，供新的連接使用。線程池意味著減少了創(chuàng)建和銷毀線程的開(kāi)銷。
緩沖池：主要用于 InnoDB 存儲(chǔ)引擎，緩沖池管理緩存的數(shù)據(jù)頁(yè)，包括數(shù)據(jù)和索引。當(dāng)需要訪問(wèn)這些頁(yè)時(shí)，可以直接從緩沖池讀取，提高訪問(wèn)速度。

buffer 與 cache 的區(qū)別?

簡(jiǎn)單的說(shuō)就是，buffer 是寫緩存，cache 是讀緩存。

存儲(chǔ)引擎層

存儲(chǔ)引擎層負(fù)責(zé)存儲(chǔ)數(shù)據(jù)和執(zhí)行 SQL 語(yǔ)句。MySQL 支持多種存儲(chǔ)引擎，每種引擎各有特點(diǎn)，根據(jù)實(shí)際需求進(jìn)行選用。當(dāng)然，只要沒(méi)有非常明確的特殊需求就不需要更改存儲(chǔ)引擎，因?yàn)?InnoDB 在大部分場(chǎng)景下都比其他引擎更加適用。

InnoDB：InnoDB 是 MySQL 的默認(rèn)存儲(chǔ)引擎，提供了事務(wù)支持、行級(jí)鎖定、外鍵約束等功能，主要用于高并發(fā)、高可靠性的 OLTP 場(chǎng)景。

MyISAM：MyISAM 通常用于只讀數(shù)據(jù)表，適用于簡(jiǎn)單查詢和全文索引。其不支持事務(wù)、行級(jí)鎖等功能，適用于 OLAP 場(chǎng)景。

Memory：Memory 存儲(chǔ)引擎支持哈希和 B 樹索引，它將數(shù)據(jù)存儲(chǔ)在內(nèi)存中，易受到系統(tǒng)斷電或宕機(jī)等影響，具有較高的寫性能但不適用于大規(guī)模數(shù)據(jù)分布。

其他存儲(chǔ)引擎：MySQL 還支持如 Archive、NDB Cluster 等其他存儲(chǔ)引擎，它們分別適用于存檔表、分布式數(shù)據(jù)庫(kù)等不同場(chǎng)景。

我們可以在 SQL 命令行中執(zhí)行 show engines; 來(lái)查看當(dāng)前支持的存儲(chǔ)引擎：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

文件系統(tǒng)層

文件系統(tǒng)由各操作系統(tǒng)提供，MySQL 將其持久化的數(shù)據(jù)物理存儲(chǔ)在磁盤上，持久化保存數(shù)據(jù)、索引、binlog、redo log、undo log、error 日志、慢 sql 等；

總結(jié)

服務(wù)層的連接和安全管理：用戶與 MYSQL 服務(wù)進(jìn)行 TCP 鏈接，校驗(yàn)用戶身份，用戶權(quán)限。
服務(wù)層的 SQL 解析和優(yōu)化：用戶寫的 SQL 語(yǔ)句會(huì)到服務(wù)層進(jìn)行解析，生成語(yǔ)法樹。優(yōu)化 SQL 語(yǔ)句，生成執(zhí)行計(jì)劃。
引擎層：真正與磁盤進(jìn)行交互，對(duì)數(shù)據(jù)進(jìn)行存儲(chǔ)和讀取。

最后，我再附上一張?jiān)谖以瓉?lái)的世界尤其盛行的架構(gòu)圖，與圖 1-1 最大的差別是這次用英文，如圖 1-2 所示。

圖 1-2 圖 1-2

我說(shuō)完之后，眾人紛紛對(duì)我稱贊。CTO 覺(jué)得我的架構(gòu)設(shè)計(jì)考慮的太周全了，立馬從 T3 晉升為 T4，薪資漲了 20%……

故事還沒(méi)結(jié)束，結(jié)構(gòu)圖出來(lái)以后，郝紀(jì)曉說(shuō)道：架構(gòu)圖雖然不錯(cuò)，可是還有很多問(wèn)題并沒(méi)提及，我們是實(shí)干主義，不是寫個(gè)架構(gòu)圖就能忽悠的。

一條查詢、insert、update、delete 語(yǔ)句的執(zhí)行流程是怎樣的？
并發(fā)如何控制？
事務(wù)如何處理？
Server 層與存儲(chǔ)引擎層之間如何保證故障恢復(fù)？
binlog、redo log、undo log 都是什么玩意？
磁盤很慢，如何將讀寫的隨機(jī) I/O 操作變成順序?qū)懕ＷC 數(shù)據(jù)庫(kù)性能？

重生之后面對(duì)的困難也不簡(jiǎn)單，這么多問(wèn)題需要解決，好在我那個(gè)世界鉆研過(guò) MySQL 技術(shù)。我絞盡腦汁的回顧之前學(xué)過(guò)的 MySQL 技術(shù)，希望在這個(gè)世界不要就這么 go dead……，還要迎娶白富美......

責(zé)任編輯：武曉燕來(lái)源：碼哥跳動(dòng)

MySQL 架構(gòu)SQL