老生常談:MySQL的體系結(jié)構(gòu)

現(xiàn)在要進(jìn)廠，每個(gè)面試官都要問你熟不熟悉mysql,作為開發(fā)者咋們也應(yīng)該清楚mysql的基礎(chǔ)體系架構(gòu)

從Mysql官網(wǎng)提供的體系結(jié)構(gòu)圖中可以看出來出來，整個(gè)mysql分為以下幾部分

1. 連接池
2. SQL接口組件
3. 查詢分析器組件
4. 優(yōu)化器組件
5. 緩存組件
6. 存儲(chǔ)引擎(插件)
7. 物理文件

mysql比較有特色的一點(diǎn)就是，它的存儲(chǔ)引擎是以插件方式提供的， 而每個(gè)存儲(chǔ)引擎決定了我們的數(shù)據(jù)是如何存放的。

雖然存儲(chǔ)引擎有很多種，但是我們常用的不太多。比如我就只使用過 InnoDB,MyISAM,Memory,InfoBright這4種。

MyISAM VS InnoDB

1. MyISAM不支持事務(wù)(不支持ACID),InnoDB支持事務(wù)(支持ACID)
2. 使用DML語句時(shí)，MyISAM鎖級(jí)別是表鎖，同一時(shí)間只支持一個(gè)session修改表,而InnoDB是行鎖,粒度更細(xì)，并發(fā)更高
3. MyISAM不支持外鍵,InnoDB支持
4. MyISAM寫入速度慢,查詢速度快，InnoDB寫入快，查詢慢(相對(duì))
5. InnoDB因?yàn)橹С质聞?wù)，所以更可靠，MyISAM無法保證數(shù)據(jù)完整性
6. MyISAM只存儲(chǔ)索引，不緩存數(shù)據(jù)，支持全文索引,InnoDB緩存索引以及數(shù)據(jù),不支持全文索引(5.6之前)。

所以在選擇哪個(gè)引擎的時(shí)候要根據(jù)實(shí)際業(yè)務(wù)選擇，比如某個(gè)業(yè)務(wù)只會(huì)插入一次數(shù)據(jù)，后面的都只有查詢,那么我就建議使用MyISAM，如果需要支持事務(wù)那就用InnoDB。

InnoDB引擎

由于我們平時(shí)使用最多的還是InnoDB引擎，所以這里主要剖析下InnoDB的存儲(chǔ)結(jié)構(gòu)(MyISAM和InnoDB在存儲(chǔ)結(jié)構(gòu)上是類似的)

5.7和8.0有些許不一致，主要是系統(tǒng)表空間的一些內(nèi)容會(huì)移動(dòng)到單獨(dú)的表空間中去

為了保證數(shù)據(jù)不丟失,我們的數(shù)據(jù)必須要落盤,但是如果每次增刪改查都去操作磁盤，那效率太低了，所以mysql會(huì)在內(nèi)存中抽象出一份數(shù)據(jù)結(jié)構(gòu)和磁盤一一對(duì)應(yīng)，這樣我們每次的增刪改查就會(huì)優(yōu)先操作內(nèi)存中的數(shù)據(jù)。

咱們插入的數(shù)據(jù)是存儲(chǔ)在磁盤的,而平時(shí)我們通過Navicate或者shell查詢出來的數(shù)據(jù)，展現(xiàn)形式為表格，并不代表我們的數(shù)據(jù)也是這樣直接這樣存儲(chǔ)在磁盤上的，磁盤上的數(shù)據(jù)有自己的格式。

比如我們將數(shù)據(jù)存在在文件是這樣存儲(chǔ)的

user.id=1
user.name=think123
user.age=18

在我們代碼中，我們會(huì)將其映射為一個(gè)java對(duì)象

public?class?User?{
?private?Long?id;
?private?String?name;
?private?Integer?age;
}

輸出顯示的時(shí)候又是這樣

{
?"id":?1,
?"name":?"think123",
?"age":?18
}

內(nèi)存結(jié)構(gòu)

將內(nèi)存中數(shù)據(jù)刷新到磁盤并不是一條數(shù)據(jù)一條數(shù)據(jù)從內(nèi)存刷新到磁盤的，為了效率，InnoDB將數(shù)據(jù)劃分為若干個(gè)頁，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的???般為 16KB,和磁盤上默認(rèn)的頁大小一致。

也就是在?般情況下，?次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中，?次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。

而內(nèi)存數(shù)據(jù)結(jié)構(gòu)主要分為 Buffer Pool(緩沖池) 和 Log Buffer(日志緩沖)，它們管理的都是頁，而頁中存儲(chǔ)的是數(shù)據(jù)。

Buffer Pool(緩沖池)

Buffer Pool中存儲(chǔ)的是頁，頁中會(huì)存儲(chǔ)很多條數(shù)據(jù)。當(dāng)我們修改數(shù)據(jù)的時(shí)候會(huì)先修改內(nèi)存中的數(shù)據(jù)，修改后的頁面并不立即同步到磁盤，而是作為臟頁繼續(xù)呆在內(nèi)存中，然后會(huì)有后臺(tái)線程將內(nèi)存中的數(shù)據(jù)刷盤到文件，保證數(shù)據(jù)一致。

我們可以通過下面的命令查看緩沖池大小

show?VARIABLES?like?'innodb_buffer_pool_size'

如果要修改，則通過修改配置完成

[server]
innodb_buffer_pool_size?=?268435456

緩沖池中緩存的數(shù)據(jù)頁類型有:索引頁、數(shù)據(jù)頁、undo頁、修改緩沖(change buffer)、自適應(yīng)哈希索引(adaptive hash index)、InnoDB中鎖信息、數(shù)據(jù)字典信息等。

除了這些，InnoDB引擎中內(nèi)存的結(jié)構(gòu)除了緩沖池還有其他結(jié)構(gòu)，如圖所示

圖中插入緩沖的部分現(xiàn)在叫做修改緩沖(change buffer),也就是說以前只對(duì)插入有用，現(xiàn)在對(duì)insert update delete都起作用，后面會(huì)分析下什么是修改緩沖

多線程環(huán)境下，訪問緩沖池(buffer pool)中的各種page是需要加鎖的，不然會(huì)有并發(fā)問題，而且在Buffer Pool特別??且多線程并發(fā)訪問特別 ?的情況下，單?的Buffer Pool可能會(huì)影響請(qǐng)求的處理速度。

所以在Buffer Pool特別?的時(shí)候，我們可以把它們拆分成多個(gè)?的Buffer Pool，每個(gè)Buffer Pool都稱為?個(gè)實(shí)例，它們都是獨(dú)?的，獨(dú) ?的去申請(qǐng)內(nèi)存空間，這樣可以增加數(shù)據(jù)庫的并發(fā)處理，減少資源競爭，我們可以通過修改配置的方式來配置緩沖池個(gè)數(shù)

[server]
innodb_buffer_pool_instances?=?2

每個(gè)buffer pool占用內(nèi)存空間就是

innodb_buffer_pool_size/innodb_buffer_pool_instances

自適應(yīng)Hash索引

Mysql中B+樹的索引高度一般為3-4層，因此需要3-4次的查詢，InnoDB會(huì)監(jiān)控表上各索引頁的查詢，如果建立hash索引可以帶來性能提升，則會(huì)建立hash索引，稱之為自適應(yīng)哈希索引(Adaptive Hash Index, AHI),AHI通過緩沖池的B+樹頁構(gòu)造而來，而且并不需要對(duì)整張表建立hash索引，只是對(duì)熱點(diǎn)頁建立hash索引。

AHI要求對(duì)某個(gè)頁的連續(xù)訪問模式一樣，比如對(duì)于（a,b)這樣的聯(lián)合索引頁，存在下面兩種情況

where?a?=?xxx;
where?a?=?xxx?and?b=xxx

如果每次都查詢條件一樣，但是交替出現(xiàn)上面兩種查詢，那么InnoDB不會(huì)建立Hash索引，如果只出現(xiàn)一個(gè)查詢，然后再滿足下面的要求，InnoDB就會(huì)建立自適應(yīng)Hash索引

1. 以該模式(同樣的等值查詢條件)訪問了100次
2. 頁通過該模式訪問了N次，N = 頁記錄/16

這種索引屬于數(shù)據(jù)庫的自優(yōu)化，無需DBA進(jìn)行認(rèn)為調(diào)整。我們可以通過下面的命令查看當(dāng)前自適應(yīng)hash索引的使用情況。

show?engine?innodb?status;

其本質(zhì)是將頻繁訪問數(shù)據(jù)頁的索引鍵值以“Key”放在緩存中，“Value”為該索引鍵值匹配完整記錄所在頁面（Page）的位置，通過縮短尋路路徑（Search Path）從而提升MySQL查詢性能的一種方式

物理結(jié)構(gòu)

數(shù)據(jù)最終會(huì)落盤到磁盤的文件中，而實(shí)際mysql管理的數(shù)據(jù)以及額外信息等等都會(huì)寫入到文件中。

系統(tǒng)表空間

InnoDB采用將存儲(chǔ)的數(shù)據(jù)按照表空間(tablespace)進(jìn)行存放設(shè)計(jì)，默認(rèn)配置下會(huì)有一個(gè)初始大小為10M，名為 ibdata1的文件，這個(gè)文件就是系統(tǒng)表空間文件，我們可以通過參數(shù)在配置文件中進(jìn)行設(shè)置，比如:

innodb_data_file_path?=?ibdata1:10M:autoextend

表示文件初始大小為10MB,用完了這10M,該文件可以自動(dòng)增長（autoextend)。

設(shè)置 innodb_data_file_path 參數(shù)后，所有基于InnoDB存儲(chǔ)引擎的表的數(shù)都會(huì)記錄到該共享表空間中。

系統(tǒng)表空間中除了雙寫緩沖區(qū)(doublewrite buffer)、change buffer、undo logs之外,還會(huì)記錄一些有關(guān)整個(gè)系統(tǒng)的信息。

再次提一下，從mysql 8.0開始doublewrite buffer會(huì)有單獨(dú)的文件, undo log也會(huì)放到自己單獨(dú)的表空間中 https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

當(dāng)我們插入數(shù)據(jù)到表里面的時(shí)候，我們需要知道很多信息，比如

1. 某個(gè)表屬于哪個(gè)表空間，表里面有多少列
2. 表對(duì)應(yīng)的每一個(gè)列類型是什么
3. 該表有多少索引，每個(gè)索引對(duì)應(yīng)哪些字段，該索引對(duì)應(yīng)的根頁面在哪個(gè)表空間的哪個(gè)頁面
4. 該表又哪些外鍵，外鍵對(duì)應(yīng)哪個(gè)表的哪些列
5. 某個(gè)表空間對(duì)應(yīng)文件系統(tǒng)上文件路徑是什么

等等。

上面提到的這些數(shù)據(jù)實(shí)際和我們插入數(shù)據(jù)無關(guān)，但是為了更好管理我們的用戶數(shù)據(jù)而不得不引入的額外數(shù)據(jù)被稱為元數(shù)據(jù)。

InnoDB特意定義了一系列的內(nèi)部系統(tǒng)表用來記錄這些數(shù)據(jù)，不過咱們是不能直接訪問InnoDB的這些內(nèi)部系統(tǒng)表的，除?直接去解析系統(tǒng)表空間對(duì)應(yīng)?件系統(tǒng)上的?件。

好在我們可以在infomation_schema數(shù)據(jù)庫中看到這些表數(shù)據(jù)

1. INNODB_SYS_COLUMNS: 整個(gè)InnoDB存儲(chǔ)引擎中所有的列的信息
2. INNODB_SYS_DATAFILES: 整個(gè)InnoDB存儲(chǔ)引擎中所有的表空間對(duì)應(yīng)?件系統(tǒng)的?件路徑信息
3. INNODB_SYS_FIELDS: 整個(gè)InnoDB存儲(chǔ)引擎中所有的索引對(duì)應(yīng)的列的信息
4. INNODB_SYS_FOREIGN: 整個(gè)InnoDB存儲(chǔ)引擎中所有的外鍵的信息
5. INNODB_SYS_FOREIGN_COLS: 整個(gè)InnoDB存儲(chǔ)引擎中所有的外鍵對(duì)應(yīng)列的信息
6. INNODB_SYS_INDEXES: 整個(gè)InnoDB存儲(chǔ)引擎中所有的索引的信息
7. INNODB_SYS_TABLES: 整個(gè)InnoDB存儲(chǔ)引擎中所有的表的信息
8. INNODB_SYS_TABLESPACES: 整個(gè)InnoDB存儲(chǔ)引擎中所有的表空間信息
9. INNODB_SYS_TABLESTATS: 整個(gè)InnoDB存儲(chǔ)引擎中所有表統(tǒng)計(jì)信息
10. INNODB_SYS_VIRTUAL: 整個(gè)InnoDB存儲(chǔ)引擎中所有的虛擬?成列的信息

information_schema數(shù)據(jù)庫中的這些以INNODB_SYS開頭的表并不是真正的內(nèi)部系統(tǒng)表，?是在存儲(chǔ)引擎啟動(dòng)時(shí)讀取真正的系統(tǒng)表，然后 填充到這些以INNODB_SYS開頭的表中。

獨(dú)立表空間

當(dāng)我們?cè)跀?shù)據(jù)庫新建一個(gè)表test的時(shí)候(如果未做特殊說明，表引擎都是InnoDB),會(huì)產(chǎn)生兩個(gè)文件，一個(gè)是 test.frm文件，一個(gè)是test.ibd文件。

frm文件中存儲(chǔ)的是表結(jié)構(gòu)(任何存儲(chǔ)引擎都會(huì)產(chǎn)生這個(gè)文件)，ibd文件存儲(chǔ)的是索引以及數(shù)據(jù)。

如果設(shè)置了 innodb_file_per_table ，則每個(gè)基于InnodDB存儲(chǔ)引擎產(chǎn)生的表都會(huì)產(chǎn)生一個(gè)獨(dú)立表空間，其命名規(guī)則為 表名.ibd。

表空間是?個(gè)抽象的概念，對(duì)于系統(tǒng)表空間來說，對(duì)應(yīng)著?件系統(tǒng)中?個(gè)或多個(gè)實(shí)際?件；對(duì)于每個(gè)獨(dú)?表空間來說，對(duì)應(yīng)著?件系統(tǒng)中?個(gè)名為 表名.ibd 的實(shí)際?件

innodb_file_per_table 也是推薦的方式(我使用的5.7默認(rèn)是開啟的)，可以通過以下命令查看設(shè)置情況

show?VARIABLES?like?'innodb_file_per_table'

需要注意的是單獨(dú)的.ibd獨(dú)立表空間文件僅僅存儲(chǔ)該表的數(shù)據(jù)、索引和插入緩沖等信息，其余信息還是存放在默認(rèn)的系統(tǒng)表空間中的。

通用表空間(general tablesapce)

通用表空間是使用 CREATE TABLESPACE 語法創(chuàng)建的共享 InnoDB 表空間，我們除了像上面那樣說的讓每個(gè)表都有自己的獨(dú)立表空間之外，還可以讓多個(gè)表在同一個(gè)表空間

//?創(chuàng)建表空間,文件默認(rèn)是在data目錄下
CREATE?TABLESPACE?`ts1`?ADD?DATAFILE?'ts1.ibd'?Engine=InnoDB;

//?可以放到data目錄外
CREATE?TABLESPACE?`ts1`?ADD?DATAFILE?'/my/tablespace/directory/ts1.ibd'?Engine=InnoDB;

//?指定表在某個(gè)表空間
CREATE?TABLE?t1?(c1?INT?PRIMARY?KEY)?TABLESPACE?ts1;

這個(gè)讓多個(gè)表在同一個(gè)表空間有啥用呢？按照官方文檔的說法，其主要特殊點(diǎn)在于兩個(gè)地方

1. 相比獨(dú)立的表空間，通用表空間因?yàn)槭嵌鄠€(gè)表放到同一個(gè)表空間中，所以其元數(shù)據(jù)占用內(nèi)存更少
2. 通過表空間可以將數(shù)據(jù)文件放在MySQL數(shù)據(jù)目錄之外，因此我們可以為這些特定的表設(shè)置RAID或DRBD，或?qū)⒈斫壎ǖ教囟ǖ拇疟P上

臨時(shí)表空間(temporary tablespace)

臨時(shí)表空間分為會(huì)話(session)臨時(shí)表空間和一個(gè)全局(global)臨時(shí)表空間。

全局臨時(shí)表空間在正常關(guān)機(jī)或初始化中止時(shí)被移除，并在每次服務(wù)器啟動(dòng)時(shí)重新創(chuàng)建。全局臨時(shí)表空間在創(chuàng)建時(shí)收到一個(gè)動(dòng)態(tài)生成的空間ID。

如果全局臨時(shí)表空間不能被創(chuàng)建，則拒絕啟動(dòng)。如果服務(wù)器意外停止，全局臨時(shí)表空間不會(huì)被刪除。在這種情況下，數(shù)據(jù)庫管理員可以手動(dòng)刪除全局臨時(shí)表空間或重新啟動(dòng)MySQL服務(wù)器。重啟MySQL服務(wù)器會(huì)自動(dòng)刪除并重新創(chuàng)建全局臨時(shí)表空間。

會(huì)話臨時(shí)表空間在第一次請(qǐng)求創(chuàng)建磁盤臨時(shí)表時(shí)從臨時(shí)表空間池中分配給會(huì)話。一個(gè)會(huì)話最多分配兩個(gè)表空間，一個(gè)用于用戶創(chuàng)建的臨時(shí)表，另一個(gè)用于優(yōu)化器創(chuàng)建的內(nèi)部臨時(shí)表。

分配給會(huì)話的臨時(shí)表空間用于會(huì)話創(chuàng)建的所有磁盤臨時(shí)表。當(dāng)會(huì)話斷開連接時(shí)，其臨時(shí)表空間將被截?cái)嗖⑨尫呕爻刂小?/span>

當(dāng)我們創(chuàng)建臨時(shí)表的時(shí)候數(shù)據(jù)會(huì)被放到臨時(shí)表空間

create?temporary?table

session的臨時(shí)表數(shù)據(jù)會(huì)存儲(chǔ)在ibt文件中，而global session的臨時(shí)表數(shù)據(jù)會(huì)存儲(chǔ)在ibtmp1文件中

臨時(shí)表可以使用各種引擎類型，如果使用的是InnoDB或者M(jìn)yISAM引擎的臨時(shí)表，寫數(shù)據(jù)的時(shí)候是寫到磁盤上的，當(dāng)然臨時(shí)表也可以使用Memory引擎。

臨時(shí)表只針對(duì)當(dāng)前會(huì)話有用，當(dāng)會(huì)話(session)被關(guān)閉的時(shí)候，這個(gè)臨時(shí)表也會(huì)被刪除。臨時(shí)表具有以下特性

1. 臨時(shí)表只能被創(chuàng)建它的session訪問，對(duì)其他線程不可見。
2. 臨時(shí)表可以于普通表同名
3. session A中有同名的臨時(shí)表和普通標(biāo)表的時(shí)候，show create 語句，以及增刪改查語句訪問的是臨時(shí)表
4. show tables不顯示臨時(shí)表

redo log

我們之前說過修改數(shù)據(jù)也是先修改buffer pool中頁的數(shù)據(jù)，如果事務(wù)提交后發(fā)生了故障，導(dǎo)致內(nèi)存中的數(shù)據(jù)都丟失了(此時(shí)還未刷盤),那提交了的事務(wù)對(duì)數(shù)據(jù)庫所做的更改也跟著丟失了，這是不能忍受的。

為了保證事務(wù)持久性(對(duì)于一個(gè)已經(jīng)提交的事務(wù)，在事務(wù)提交后，即使系統(tǒng)發(fā)生了崩潰，這個(gè)事務(wù)對(duì)數(shù)據(jù)庫中所做的更改也不能丟失)，我們必須要有一個(gè)機(jī)制來記錄事務(wù)到底修改了什么內(nèi)容。

如果每次修改了buffer pool中數(shù)據(jù)就刷盤到磁盤的話，是不可取的，原因有2個(gè)。

1. 刷新一個(gè)完整的數(shù)據(jù)也太浪費(fèi)了: 可能只更新了一個(gè)頁的幾個(gè)字節(jié)，但是確要刷整個(gè)頁
2. 隨機(jī)IO比較慢: 一個(gè)事務(wù)可能更新多個(gè)頁，如果這些頁不連續(xù)，就會(huì)產(chǎn)生很多隨機(jī)IO,隨機(jī)IO比順序IO要慢。

因此我們使用redo log記錄更新了什么內(nèi)容，當(dāng)事務(wù)提交時(shí)，我們只需要把redo log內(nèi)容刷到磁盤中，即使系統(tǒng)崩潰了，我們也能根據(jù)文件內(nèi)容恢復(fù)。使用redo log有以下優(yōu)勢

1. redo日志占用空間非常小
2. redo日志是順序?qū)懭氪疟P的: 每次執(zhí)行一條語句就可能產(chǎn)生多條redo日志，這些日志是按照產(chǎn)生順序?qū)懭氲?/span>

默認(rèn)情況下，InnoDB存儲(chǔ)引擎的數(shù)據(jù)目錄下會(huì)有兩個(gè)名為 ib_logfile0 和 ib_logfile1 的文件，這個(gè)文件是redo log file,它們記錄了InnoDB存儲(chǔ)引擎的redo日志。

當(dāng)mysql發(fā)生問題時(shí)，比如斷電了，InnoDB引擎會(huì)使用redo log恢復(fù)到掉電前時(shí)刻，以此來保證數(shù)據(jù)完整性。

寫入redo log并不是直接寫，而是先寫入redo log buffer中，然后再寫入到日志文件中，寫入方式是循環(huán)寫入，InnoDB先寫重寫ib_logfile0,當(dāng)文件1達(dá)到文件最后時(shí)，會(huì)切換至ib_logfile1,當(dāng)文件2也被寫滿時(shí)，會(huì)在切換到文件1，如此循環(huán)往復(fù)。

同樣的這里有兩個(gè)參數(shù)可以控制redo log

#查看相關(guān)配置
show?VARIABLES?like?'innodb%log%'

#指定日志文件大小
innodb_log_file_size=5M

#指定文件組個(gè)數(shù)(默認(rèn)值是2,所以是ib_logfile0,ib_logfile1兩個(gè)文件)
innodb_log_files_in_group=2

看到這里是不是會(huì)疑惑，先寫入redo log buffer,在刷盤，如果在刷盤的時(shí)候系統(tǒng)也崩潰了，不是也沒辦法回復(fù)數(shù)據(jù)嗎？那再加一層來解決？這不就是俄羅斯套娃了么

還好mysql沒有這么干，它讓我們可以選擇修改?個(gè)稱為 innodb_flush_log_at_trx_commit 的系統(tǒng)變量的值，該變量有3個(gè)可選的值

• 0：當(dāng)該系統(tǒng)變量值為0時(shí)，表?在事務(wù)提交時(shí)不?即向磁盤中同步redo?志，這個(gè)任務(wù)是交給后臺(tái)線程做的。這樣很明顯會(huì)加快請(qǐng)求處理速度，但是如果事務(wù)提交后服務(wù)器掛了，后臺(tái)線程沒有及時(shí)將redo?志刷新到磁盤，那么該事務(wù)對(duì)頁?的修改會(huì)丟失。
• 1：當(dāng)該系統(tǒng)變量值為1時(shí)，表?在事務(wù)提交時(shí)需要將redo?志同步到磁盤，可以保證事務(wù)的持久性。1也是innodb_flush_log_at_trx_commit的默認(rèn)值。
• 2：當(dāng)該系統(tǒng)變量值為2時(shí)，表?在事務(wù)提交時(shí)需要將redo?志寫到操作系統(tǒng)的緩沖區(qū)中，但并不需要保證將?志真正的刷新到磁盤。這種情況下如果數(shù)據(jù)庫掛了，操作系統(tǒng)沒掛的話，事務(wù)的持久性還是可以保證的，但是操作系統(tǒng)也掛了的話，那就不能保證持久性了。

參考資料

1. Mysql官方文檔
2. <>
3. <>