前言

在我們樂字節(jié)的公開課上，講了一些關(guān)于一些 MySQL 數(shù)據(jù)庫相關(guān)流程圖/原理圖的重點(diǎn)。我整理出來了，做一下筆記，大家一起學(xué)習(xí)。

1. MySQL 主從復(fù)制原理圖

MySQL?主從復(fù)制原理是大廠后端的高頻面試題，了解MySQL?主從復(fù)制原理非常有必要。

主從復(fù)制原理簡言之，就三步曲，如下：

主數(shù)據(jù)庫有個(gè) bin-log 二進(jìn)制文件，記錄了所有增刪改 SQL 語句（binlog線程）；

從數(shù)據(jù)庫把主數(shù)據(jù)庫的 bin-log 文件的SQL?語句復(fù)制過來（I/O線程）；

從數(shù)據(jù)庫的 relay-log 重做日志文件中再執(zhí)行一次這些SQL?語句（SQL 執(zhí)行線程）。

如下圖所示：

上圖主從復(fù)制分了五個(gè)步驟進(jìn)行：

步驟一：主庫的更新事件（update、insert、delete）被寫到 binlog；

步驟二：從庫發(fā)起連接，連接到主庫；

步驟三：此時(shí)主庫創(chuàng)建一個(gè) binlog dump thread，把 binlog 的內(nèi)容發(fā)送到從庫；

步驟四：從庫啟動(dòng)之后，創(chuàng)建一個(gè) I/O 線程，讀取主庫傳過來的 binlog 內(nèi)容并寫入到 relay log；

步驟五：還會(huì)創(chuàng)建一個(gè) SQL 線程，從 relay log 里面讀取內(nèi)容，從 Exec_Master_Log_Pos 位置開始執(zhí)行讀取到的更新事件，將更新內(nèi)容寫入到 slave 的 DB。

2.?MySQL?邏輯架構(gòu)圖

如果能在腦海中構(gòu)建出MySQL?各組件之間如何協(xié)同工作的架構(gòu)圖，就會(huì)有助于深入理解MySQL?服務(wù)器。

MySQL?邏輯架構(gòu)圖主要分三層：

第一層負(fù)責(zé)連接處理，授權(quán)認(rèn)證，安全等。

每個(gè)客戶端連接都會(huì)在服務(wù)器進(jìn)程中擁有一個(gè)線程，服務(wù)器維護(hù)了一個(gè)線程池，因此不需要為每一個(gè)新建的連接創(chuàng)建或者銷毀線程；

當(dāng)客戶端連接到Mysql服務(wù)器時(shí)，服務(wù)器對(duì)其進(jìn)行認(rèn)證，通過用戶名和密碼認(rèn)證，也可以通過SSL證書進(jìn)行認(rèn)證；

一旦客戶端連接成功，服務(wù)器會(huì)繼續(xù)驗(yàn)證客戶端是否具有執(zhí)行某個(gè)特定查詢的權(quán)限。

第二層負(fù)責(zé)編譯并優(yōu)化SQL。

這一層包括查詢解析，分析，優(yōu)化，緩存以及所有的的內(nèi)置函數(shù)；

對(duì)于SELECT語句，在解析查詢前，服務(wù)器會(huì)先檢查查詢緩存，如果能在其中找到對(duì)應(yīng)的查詢結(jié)果，則無需再進(jìn)行查詢解析、優(yōu)化等過程，直接返回查詢結(jié)果；

所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn):存儲(chǔ)過程，觸發(fā)器，視圖。

第三層是存儲(chǔ)引擎。

存儲(chǔ)引擎負(fù)責(zé)在 MySQL 中存儲(chǔ)數(shù)據(jù)、提取數(shù)據(jù)；

存儲(chǔ)引擎通過 API 與上層進(jìn)行通信，這些 API 屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層查詢過程透明；

存儲(chǔ)引擎不會(huì)去解析 SQL，不同存儲(chǔ)引擎之間也不會(huì)相互通信，而只是簡單地響應(yīng)上層服務(wù)器的請(qǐng)求。

3. InnoDB?邏輯存儲(chǔ)結(jié)構(gòu)圖

從 InnoDB 存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)看，所有數(shù)據(jù)都被邏輯地存放在一個(gè)空間中，稱之為表空間（tablespace）。表空間又由段（segment）、區(qū)（extent）、頁（page）組成。頁在一些文檔中有時(shí)候也稱為塊（block）。

InnoDB 邏輯存儲(chǔ)結(jié)構(gòu)圖如下：?

表空間（tablespace）

表空間是 Innodb 存儲(chǔ)引擎邏輯的最高層，所有的數(shù)據(jù)都存放在表空間中；

默認(rèn)情況下 Innodb 存儲(chǔ)引擎有一個(gè)共享表空間 ibdata1，即所有數(shù)據(jù)都存放在這個(gè)表空間中內(nèi)；

如果啟用了 innodb_file_per_table 參數(shù)，需要注意的是每張表的表空間內(nèi)存放的只是數(shù)據(jù)、索引、和插入緩沖 Bitmap，其他類的數(shù)據(jù)，比如回滾（undo）信息、插入緩沖檢索頁、系統(tǒng)事物信息，二次寫緩沖等還是放在原來的共享表內(nèi)的。

段（segment）

表空間由段組成，常見的段有數(shù)據(jù)段、索引段、回滾段等；

InnoDB 存儲(chǔ)引擎表是索引組織的，因此數(shù)據(jù)即索引，索引即數(shù)據(jù)。數(shù)據(jù)段即為 B+ 樹的葉子結(jié)點(diǎn)，索引段即為 B+ 樹的非索引結(jié)點(diǎn)；

在 InnoDB 存儲(chǔ)引擎中對(duì)段的管理都是由引擎自身所完成，DBA 不能也沒必要對(duì)其進(jìn)行控制。

區(qū)（extent）

區(qū)是由連續(xù)頁組成的空間，在任何情況下每個(gè)區(qū)的大小都為 1MB；

為了保證區(qū)中頁的連續(xù)性，InnoDB 存儲(chǔ)引擎一次從磁盤申請(qǐng) 4~5 個(gè)區(qū)

默認(rèn)情況下，InnoDB 存儲(chǔ)引擎頁的大小為 16KB，一個(gè)區(qū)中一共 64 個(gè)連續(xù)的區(qū)。

頁（page）

頁是 InnoDB 磁盤管理的最小單位；

在 InnoDB 存儲(chǔ)引擎中，默認(rèn)每個(gè)頁的大小為 16KB；

從 InnoDB1.2.x 版本開始，可以通過參數(shù) innodb_page_size 將頁的大小設(shè)置為 4K、8K、16K；

InnoDB 存儲(chǔ)引擎中，常見的頁類型有：數(shù)據(jù)頁、undo頁、系統(tǒng)頁、事務(wù)數(shù)據(jù)頁、插入緩沖位圖頁、插入緩沖空閑列表頁等。

4. InnoDB頁結(jié)構(gòu)相關(guān)示意圖

4.1 InnoDB 頁結(jié)構(gòu)單體圖

InnoDB 數(shù)據(jù)頁由以下 7 部分組成，如圖所示：

其中 File Header、Page Header、File Trailer 的大小是固定的，分別為 38、56、8 字節(jié)，這些空間用來標(biāo)記該頁的一些信息，如 Checksum，數(shù)據(jù)頁所在 B+ 樹索引的層數(shù)等。User Records、Free Space、Page Directory 這些部分為實(shí)際的行記錄存儲(chǔ)空間，因此大小是動(dòng)態(tài)的。

下邊我們用表格的方式來大致描述一下這 7 個(gè)部分：

4.2?記錄在頁中的存儲(chǔ)流程圖

每當(dāng)我們插入一條記錄，都會(huì)從 Free Space 部分，也就是尚未使用的存儲(chǔ)空間中申請(qǐng)一個(gè)記錄大小的空間劃分到 User Records 部分，當(dāng) Free Space 部分的空間全部被 User Records 部分替代掉之后，也就意味著這個(gè)頁使用完了，如果還有新的記錄插入的話，就需要去申請(qǐng)新的頁了，這個(gè)過程的圖示如下：

4.3?不同Innodb頁構(gòu)成的數(shù)據(jù)結(jié)構(gòu)圖

一張表中可以有成千上萬條記錄，一個(gè)頁只有 16KB，所以可能需要好多頁來存放數(shù)據(jù)。不同頁其實(shí)構(gòu)成了一條雙向鏈表，F(xiàn)ile Header 是 InnoDB 頁的第一部分，它的 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 就分別代表本頁的上一個(gè)和下一個(gè)頁的頁號(hào)，即鏈表的上一個(gè)以及下一個(gè)節(jié)點(diǎn)指針。

5. Innodb索引結(jié)構(gòu)圖

我們先看一份數(shù)據(jù)表樣本，假設(shè) Col1 是主鍵，如下：

5.1 B+ 樹聚集索引結(jié)構(gòu)圖

聚集索引就是以主鍵創(chuàng)建的索引；

聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù)。

5.2 非聚集索引結(jié)構(gòu)圖

假設(shè)索引列為 Col3，索引結(jié)構(gòu)圖如下：?

非聚集索引就是以非主鍵創(chuàng)建的索引；

非聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列；

使用非聚集索引查詢出數(shù)據(jù)時(shí)，拿到葉子上的主鍵再去查到想要查找的數(shù)據(jù)。(拿到主鍵再查找這個(gè)過程叫做回表)；

假設(shè)所查詢的列，剛好都是索引對(duì)應(yīng)的列，不用再回表查，那么這個(gè)索引列，就叫覆蓋索引。

5.3 InnoDB 鎖類型思維導(dǎo)圖

5.4 加鎖機(jī)制

樂觀鎖與悲觀鎖是兩種并發(fā)控制的思想，可用于解決丟失更新問題。

樂觀鎖

每次去取數(shù)據(jù)，都很樂觀，覺得不會(huì)出現(xiàn)并發(fā)問題；

因此，訪問、處理數(shù)據(jù)每次都不上鎖；

但是在更新的時(shí)候，再根據(jù)版本號(hào)或時(shí)間戳判斷是否有沖突，有則處理，無則提交事務(wù)。

悲觀鎖

每次去取數(shù)據(jù)，很悲觀，都覺得會(huì)被別人修改，會(huì)有并發(fā)問題；

因此，訪問、處理數(shù)據(jù)前就加排他鎖；

在整個(gè)數(shù)據(jù)處理過程中鎖定數(shù)據(jù)，事務(wù)提交或回滾后才釋放鎖。

5.5 鎖粒度

表鎖：?開銷小，加鎖快；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低;不會(huì)出現(xiàn)死鎖；

行鎖：?開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高；

頁鎖：?開銷和加鎖速度介于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般。

5.6 兼容性

共享鎖

又稱讀鎖（S 鎖）；

一個(gè)事務(wù)獲取了共享鎖，其他事務(wù)可以獲取共享鎖，不能獲取排他鎖，其他事務(wù)可以進(jìn)行讀操作，不能進(jìn)行寫操作；

SELECT ... LOCK IN SHARE MODE 顯示加共享鎖。

排他鎖

又稱寫鎖（X 鎖）。

如果事務(wù) T 對(duì)數(shù)據(jù) A 加上排他鎖后，則其他事務(wù)不能再對(duì) A 加任何類型的封鎖。獲準(zhǔn)排他鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。

SELECT ... FOR UPDATE 顯示添加排他鎖。

5.7 鎖模式

記錄鎖：?在行相應(yīng)的索引記錄上的鎖，鎖定一個(gè)行記錄；

gap 鎖：?是在索引記錄間歇上的鎖，鎖定一個(gè)區(qū)間；

next-key 鎖：?是記錄鎖和在此索引記錄之前的 gap 上的鎖的結(jié)合，鎖定行記錄+區(qū)間；

意向鎖：是為了支持多種粒度鎖同時(shí)存在。

? ? ? ? ? ? ? 文章轉(zhuǎn)載自樂字節(jié)