前言

整理了一些Mysql數(shù)據(jù)庫(kù)相關(guān)流程圖/原理圖，做一下筆記，大家一起學(xué)習(xí)。

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

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

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

• 主數(shù)據(jù)庫(kù)有個(gè)bin-log二進(jìn)制文件，紀(jì)錄了所有增刪改Sql語(yǔ)句。（binlog線程）
• 從數(shù)據(jù)庫(kù)把主數(shù)據(jù)庫(kù)的bin-log文件的sql語(yǔ)句復(fù)制過(guò)來(lái)。（io線程）
• 從數(shù)據(jù)庫(kù)的relay-log重做日志文件中再執(zhí)行一次這些sql語(yǔ)句。（Sql執(zhí)行線程）

如下圖所示：

image

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

步驟一：主庫(kù)的更新事件(update、insert、delete)被寫(xiě)到binlog

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

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

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

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

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

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

image

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

1) 第一層負(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)證，通過(guò)用戶名和密碼認(rèn)證，也可以通過(guò)SSL證書(shū)進(jìn)行認(rèn)證。
• 一旦客戶端連接成功，服務(wù)器會(huì)繼續(xù)驗(yàn)證客戶端是否具有執(zhí)行某個(gè)特定查詢的權(quán)限。

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

• 這一層包括查詢解析，分析，優(yōu)化，緩存以及所有的的內(nèi)置函數(shù)。
• 對(duì)于SELECT語(yǔ)句，在解析查詢前，服務(wù)器會(huì)先檢查查詢緩存，如果能在其中找到對(duì)應(yīng)的查詢結(jié)果，則無(wú)需再進(jìn)行查詢解析、優(yōu)化等過(guò)程，直接返回查詢結(jié)果。
• 所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn):存儲(chǔ)過(guò)程，觸發(fā)器，視圖。

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

• 存儲(chǔ)引擎負(fù)責(zé)在MySQL中存儲(chǔ)數(shù)據(jù)、提取數(shù)據(jù)。
• 存儲(chǔ)引擎通過(guò)API與上層進(jìn)行通信，這些API屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對(duì)上層查詢過(guò)程透明。
• 存儲(chǔ)引擎不會(huì)去解析SQL，不同存儲(chǔ)引擎之間也不會(huì)相互通信，而只是簡(jiǎn)單地響應(yīng)上層服務(wù)器的請(qǐng)求。

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

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

image

表空間（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)信息、插入緩沖檢索頁(yè)、系統(tǒng)事物信息，二次寫(xiě)緩沖等還是放在原來(lái)的共享表內(nèi)的。

段（segment）

• 表空間由段組成，常見(jiàn)的段有數(shù)據(jù)段、索引段、回滾段等。
• InnoDB存儲(chǔ)引擎表是索引組織的，因此數(shù)據(jù)即索引，索引即數(shù)據(jù)。數(shù)據(jù)段即為B+樹(shù)的葉子結(jié)點(diǎn)，索引段即為B+樹(shù)的非索引結(jié)點(diǎn)。
• 在InnoDB存儲(chǔ)引擎中對(duì)段的管理都是由引擎自身所完成，DBA不能也沒(méi)必要對(duì)其進(jìn)行控制。

區(qū)（extent）

• 區(qū)是由連續(xù)頁(yè)組成的空間，在任何情況下每個(gè)區(qū)的大小都為1MB。
• 為了保證區(qū)中頁(yè)的連續(xù)性，InnoDB存儲(chǔ)引擎一次從磁盤申請(qǐng)4~5個(gè)區(qū)。
• 默認(rèn)情況下，InnoDB存儲(chǔ)引擎頁(yè)的大小為16KB，一個(gè)區(qū)中一共64個(gè)連續(xù)的區(qū)。

頁(yè)（page）

• 頁(yè)是InnoDB磁盤管理的最小單位。
• 在InnoDB存儲(chǔ)引擎中，默認(rèn)每個(gè)頁(yè)的大小為16KB。
• 從InnoDB1.2.x版本開(kāi)始，可以通過(guò)參數(shù)innodb_page_size將頁(yè)的大小設(shè)置為4K，8K，16K。
• InnoDB存儲(chǔ)引擎中，常見(jiàn)的頁(yè)類型有：數(shù)據(jù)頁(yè)，undo頁(yè)，系統(tǒng)頁(yè)，事務(wù)數(shù)據(jù)頁(yè)，插入緩沖位圖頁(yè)，插入緩沖空閑列表頁(yè)等。

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

Innodb頁(yè)結(jié)構(gòu)單體圖

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

image

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

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

image

記錄在頁(yè)中的存儲(chǔ)流程圖

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

image

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

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

image

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

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

image

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

image

• 聚集索引就是以主鍵創(chuàng)建的索引
• 聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是表中的數(shù)據(jù)

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

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

image

• 非聚集索引就是以非主鍵創(chuàng)建的索引
• 非聚集索引在葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和索引列
• 使用非聚集索引查詢出數(shù)據(jù)時(shí)，拿到葉子上的主鍵再去查到想要查找的數(shù)據(jù)。(拿到主鍵再查找這個(gè)過(guò)程叫做回表)
• 假設(shè)所查詢的列，剛好都是索引對(duì)應(yīng)的列，不用再回表查，那么這個(gè)索引列，就叫覆蓋索引。

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

image

加鎖機(jī)制

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

樂(lè)觀鎖

• 每次去取數(shù)據(jù)，都很樂(lè)觀，覺(jué)得不會(huì)出現(xiàn)并發(fā)問(wèn)題。
• 因此，訪問(wèn)、處理數(shù)據(jù)每次都不上鎖。
• 但是在更新的時(shí)候，再根據(jù)版本號(hào)或時(shí)間戳判斷是否有沖突，有則處理，無(wú)則提交事務(wù)。

悲觀鎖

• 每次去取數(shù)據(jù)，很悲觀，都覺(jué)得會(huì)被別人修改，會(huì)有并發(fā)問(wèn)題。
• 因此，訪問(wèn)、處理數(shù)據(jù)前就加排他鎖。
• 在整個(gè)數(shù)據(jù)處理過(guò)程中鎖定數(shù)據(jù)，事務(wù)提交或回滾后才釋放鎖.

鎖粒度

• 表鎖： 開(kāi)銷小，加鎖快；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低;不會(huì)出現(xiàn)死鎖。
• 行鎖： 開(kāi)銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高。
• 頁(yè)鎖： 開(kāi)銷和加鎖速度介于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般

兼容性

共享鎖：

• 又稱讀鎖（S鎖）。
• 一個(gè)事務(wù)獲取了共享鎖，其他事務(wù)可以獲取共享鎖，不能獲取排他鎖，其他事務(wù)可以進(jìn)行讀操作，不能進(jìn)行寫(xiě)操作。
• SELECT ... LOCK IN SHARE MODE 顯示加共享鎖。

排他鎖：

• 又稱寫(xiě)鎖（X鎖）。
• 如果事務(wù)T對(duì)數(shù)據(jù)A加上排他鎖后，則其他事務(wù)不能再對(duì)A加任任何類型的封鎖。獲準(zhǔn)排他鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。
• SELECT ... FOR UPDATE 顯示添加排他鎖。

鎖模式

• 記錄鎖： 在行相應(yīng)的索引記錄上的鎖，鎖定一個(gè)行記錄
• gap鎖： 是在索引記錄間歇上的鎖,鎖定一個(gè)區(qū)間
• next-key鎖： 是記錄鎖和在此索引記錄之前的gap上的鎖的結(jié)合，鎖定行記錄+區(qū)間。
• 意向鎖 是為了支持多種粒度鎖同時(shí)存在；

參考與感謝

• 《MySQL技術(shù)內(nèi)幕》
• 《高性能MySql》
• MySQL InnoDB鎖機(jī)制全面解析分享 https://segmentfault.com/a/1190000014133576
• 數(shù)據(jù)庫(kù)兩大神器【索引和鎖】 https://juejin.im/post/5b55b842f265da0f9e589e79#heading-2
• InnoDB的邏輯存儲(chǔ)結(jié)構(gòu)學(xué)習(xí) https://blog.csdn.net/m0_37752084/article/details/80496490
• MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理 http://blog.codinglabs.org/articles/theory-of-mysql-index.html