當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

后悔！我早该把这1W字详解的 InnoDB 原理给你！

發(fā)布時(shí)間：2024/4/11 编程问答 43 豆豆

生活随笔收集整理的這篇文章主要介紹了后悔！我早该把这1W字详解的 InnoDB 原理给你！小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

點(diǎn)擊上方“朱小廝的博客”，選擇“設(shè)為星標(biāo)”

當(dāng)當(dāng)滿200-40元優(yōu)惠碼「?FV5YSU?」

來源：r6d.cn/Q8tM

MySQL InnoDB 引擎現(xiàn)在廣為使用，它提供了事務(wù)，行鎖，日志等一系列特性，本文分析下 InnoDB 的內(nèi)部實(shí)現(xiàn)機(jī)制，MySQL 版本為 5.7.24，操作系統(tǒng)為 Debian 9。

1、InnoDB 架構(gòu)

Innodb 架構(gòu)圖

InnoDB 的架構(gòu)分為兩塊：內(nèi)存中的結(jié)構(gòu)和磁盤上的結(jié)構(gòu)。InnoDB 使用日志先行策略，將數(shù)據(jù)修改先在內(nèi)存中完成，并且將事務(wù)記錄成重做日志(Redo Log)，轉(zhuǎn)換為順序IO高效的提交事務(wù)。

這里日志先行，說的是日志記錄到數(shù)據(jù)庫以后，對(duì)應(yīng)的事務(wù)就可以返回給用戶，表示事務(wù)完成。但是實(shí)際上，這個(gè)數(shù)據(jù)可能還只在內(nèi)存中修改完，并沒有刷到磁盤上去。內(nèi)存是易失的，如果在數(shù)據(jù)落地前，機(jī)器掛了，那么這部分?jǐn)?shù)據(jù)就丟失了。

InnoDB 通過 redo 日志來保證數(shù)據(jù)的一致性。如果保存所有的重做日志，顯然可以在系統(tǒng)崩潰時(shí)根據(jù)日志重建數(shù)據(jù)。

當(dāng)然記錄所有的重做日志不太現(xiàn)實(shí)，所以 InnoDB 引入了檢查點(diǎn)機(jī)制。即定期檢查，保證檢查點(diǎn)之前的日志都已經(jīng)寫到磁盤，則下次恢復(fù)只需要從檢查點(diǎn)開始。

2、InnoDB 內(nèi)存中的結(jié)構(gòu)

內(nèi)存中的結(jié)構(gòu)主要包括 Buffer Pool，Change Buffer、Adaptive Hash Index以及 Log Buffer 四部分。

如果從內(nèi)存上來看，Change Buffer 和 Adaptive Hash Index 占用的內(nèi)存都屬于 Buffer Pool，Log Buffer占用的內(nèi)存與 Buffer Pool獨(dú)立。

Buffer Pool

緩沖池緩存的數(shù)據(jù)包括Page Cache、Change Buffer、Data Dictionary Cache等，通常 MySQL 服務(wù)器的 80% 的物理內(nèi)存會(huì)分配給 Buffer Pool。

基于效率考慮，InnoDB中數(shù)據(jù)管理的最小單位為頁，默認(rèn)每頁大小為16KB，每頁包含若干行數(shù)據(jù)。

為了提高緩存管理效率，InnoDB的緩存池通過一個(gè)頁鏈表實(shí)現(xiàn)，很少訪問的頁會(huì)通過緩存池的 LRU 算法淘汰出去。

InnoDB 的緩沖池頁鏈表分為兩部分：New sublist(默認(rèn)占5/8緩存池) 和 Old sublist(默認(rèn)占3/8緩存池，可以通過 innodb_old_blocks_pct修改，默認(rèn)值為 37)，其中新讀取的頁會(huì)加入到 Old sublist的頭部，而 Old sublist中的頁如果被訪問，則會(huì)移到 New sublist的頭部。

緩沖池的使用情況可以通過?show engine innodb status?命令查看。其中一些主要信息如下：

---------------------- BUFFER?POOL?AND?MEMORY ---------------------- Total?large?memory?allocated?137428992?#?分配給InnoDB緩存池的內(nèi)存(字節(jié)) Dictionary?memory?allocated?102398??#?分配給InnoDB數(shù)據(jù)字典的內(nèi)存(字節(jié)) Buffer?pool?size???8191?#?緩存池的頁數(shù)目 Free?buffers???????7893?#?緩存池空閑鏈表的頁數(shù)目 Database?pages?????298??#?緩存池LRU鏈表的頁數(shù)目 Modified?db?pages??0????#?修改過的頁數(shù)目 ......

Change Buffer

通常來說，InnoDB輔助索引不同于聚集索引的順序插入，如果每次修改二級(jí)索引都直接寫入磁盤，則會(huì)有大量頻繁的隨機(jī)IO。Change buffer 的主要目的是將對(duì) 非唯一輔助索引頁的操作緩存下來，以此減少輔助索引的隨機(jī)IO，并達(dá)到操作合并的效果。它會(huì)占用部分Buffer Pool 的內(nèi)存空間。

在 MySQL5.5 之前 Change Buffer其實(shí)叫 Insert Buffer，最初只支持 insert 操作的緩存，隨著支持操作類型的增加，改名為 Change Buffer。

如果輔助索引頁已經(jīng)在緩沖區(qū)了，則直接修改即可；如果不在，則先將修改保存到 Change Buffer。Change Buffer的數(shù)據(jù)在對(duì)應(yīng)輔助索引頁讀取到緩沖區(qū)時(shí)合并到真正的輔助索引頁中。Change Buffer 內(nèi)部實(shí)現(xiàn)也是使用的 B+ 樹。

可以通過?innodb_change_buffering?配置是否緩存輔助索引頁的修改，默認(rèn)為 all，即緩存 insert/delete-mark/purge 操作(注：MySQL 刪除數(shù)據(jù)通常分為兩步，第一步是delete-mark，即只標(biāo)記，而purge才是真正的刪除數(shù)據(jù))。

查看Change Buffer 信息也可以通過?show engine innodb status?命令。更多信息見

------------------------------------- INSERT?BUFFER?AND?ADAPTIVE?HASH?INDEX ------------------------------------- Ibuf:?size?1,?free?list?len?0,?seg?size?2,?0?merges merged?operations:insert?0,?delete?mark?0,?delete?0 discarded?operations:insert?0,?delete?mark?0,?delete?0 Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s) Hash?table?size?34673,?node?heap?has?0?buffer(s)

Adaptive Hash Index

自適應(yīng)哈希索引(AHI)查詢非常快，一般時(shí)間復(fù)雜度為 O(1)，相比 B+ 樹通常要查詢 3~4次，效率會(huì)有很大提升。innodb 通過觀察索引頁上的查詢次數(shù)，如果發(fā)現(xiàn)建立哈希索引可以提升查詢效率，則會(huì)自動(dòng)建立哈希索引，稱之為自適應(yīng)哈希索引，不需要人工干預(yù)，可以通過?innodb_adaptive_hash_index?開啟，MySQL5.7 默認(rèn)開啟。

考慮到不同系統(tǒng)的差異，有些系統(tǒng)開啟自適應(yīng)哈希索引可能會(huì)導(dǎo)致性能提升不明顯，而且為監(jiān)控索引頁查詢次數(shù)增加了多余的性能損耗， MySQL5.7 更改了 AHI 實(shí)現(xiàn)機(jī)制，每個(gè) AHI 都分配了專門分區(qū)，通過?innodb_adaptive_hash_index_parts配置分區(qū)數(shù)目，默認(rèn)是8個(gè)，如前一節(jié)命令列出所示。

Log Buffer

Log Buffer是重做日志在內(nèi)存中的緩沖區(qū)，大小由?innodb_log_buffer_size?定義，默認(rèn)是 16M。一個(gè)大的 Log Buffer可以讓大事務(wù)在提交前不必將日志中途刷到磁盤，可以提高效率。如果你的系統(tǒng)有很多修改很多行記錄的大事務(wù)，可以增大該值。

配置項(xiàng)?innodb_flush_log_at_trx_commit?用于控制 Log Buffer 如何寫入和刷到磁盤。注意，除了 MySQL 的緩沖區(qū)，操作系統(tǒng)本身也有內(nèi)核緩沖區(qū)。

默認(rèn)為1，表示每次事務(wù)提交都會(huì)將 Log Buffer 寫入操作系統(tǒng)緩存，并調(diào)用配置的 “flush” 方法將數(shù)據(jù)寫到磁盤。
設(shè)置為 1 因?yàn)轭l繁刷磁盤效率會(huì)偏低，但是安全性高，最多丟失 1個(gè) 事務(wù)數(shù)據(jù)。
而設(shè)置為 0 和 2 則可能丟失 1秒以上的事務(wù)數(shù)據(jù)。
為 0 則表示每秒才將 Log Buffer 寫入內(nèi)核緩沖區(qū)并調(diào)用 “flush” 方法將數(shù)據(jù)寫到磁盤。
為 2 則是每次事務(wù)提交都將 Log Buffer寫入內(nèi)核緩沖區(qū)，但是每秒才調(diào)用 “flush” 將內(nèi)核緩沖區(qū)的數(shù)據(jù)刷到磁盤。

innodb_flush_log_at_timeout?可以配置刷新日志緩存到磁盤的頻率，默認(rèn)是1秒。注意刷磁盤的頻率并不保證就正好是這個(gè)時(shí)間，可能因?yàn)镸ySQL的一些操作導(dǎo)致推遲或提前。

而這個(gè) “flush” 方法并不是C標(biāo)準(zhǔn)庫的 fflush 方法(fflush是將C標(biāo)準(zhǔn)庫的緩沖寫到內(nèi)核緩沖區(qū)，并不保證刷到磁盤)，它通過 innodb_flush_method 配置的，默認(rèn)是 fsync，即日志和數(shù)據(jù)都通過 fsync 系統(tǒng)調(diào)用刷到磁盤。

可以發(fā)現(xiàn)，InnoDB 基本每秒都會(huì)將 Log buffer落盤。而InnoDB中使用的 redo log 和 undo log，它們是分開存儲(chǔ)的。

redo log在內(nèi)存中有l(wèi)og buffer，在磁盤對(duì)應(yīng)ib_logfile文件。而undo log是記錄在表空間ibd文件中的，InnoDB為undo log會(huì)生成undo頁，對(duì)undo log本身的操作（比如向undo log插入一條記錄），也會(huì)記錄redo log，因此undo log并不需要馬上落盤。而 redo log 則通常會(huì)分配一塊連續(xù)的磁盤空間，然后先寫到log buffer，并每秒刷一次磁盤。

redo log 必須在數(shù)據(jù)落盤前先落盤(Write Ahead Log)，從而保證數(shù)據(jù)持久性和一致性。而數(shù)據(jù)本身的修改可以先駐留在內(nèi)存緩沖池中，再根據(jù)特定的策略定期刷到磁盤。

3、InnoDB 磁盤上的結(jié)構(gòu)

表空間：
分為系統(tǒng)表空間(MySQL 目錄的 ibdata1 文件)，臨時(shí)表空間，常規(guī)表空間，Undo 表空間以及 file-per-table 表空間(MySQL5.7默認(rèn)打開file_per_table 配置）。
系統(tǒng)表空間又包括了InnoDB數(shù)據(jù)字典，雙寫緩沖區(qū)(Doublewrite Buffer)，修改緩存(Change Buffer），Undo日志等。
Redo日志：
存儲(chǔ)的就是 Log Buffer 刷到磁盤的數(shù)據(jù)。

為了后面測試方便，我們先建立一個(gè)測試數(shù)據(jù)庫 test，然后建立一個(gè)測試表 t。

mysql>?create?database?test; mysql>?use?test; mysql>?create?table?t?(id?int?auto_increment?primary?key,?ch?varchar(5000)); mysql>?insert?into?t?(ch)?values('abc'); mysql>?insert?into?t?(ch)?values('defgh');

建立完成后，可以在 MySQL 目錄中看到 test 數(shù)據(jù)庫目錄，然后里面有 db.opt， t.frm 和 t.ibd 3個(gè)文件。其中 db.opt 保存了數(shù)據(jù)庫test的默認(rèn)字符集 utf8mb4 和校驗(yàn)方法 utf8mb4_general_ci，t.frm 是表的數(shù)據(jù)字典信息(InnoDB數(shù)據(jù)字典信息主要是存儲(chǔ)在系統(tǒng)表空間ibdata1文件中，由于歷史原因才在 t.frm 多保留了一份)，t.ibd是表的數(shù)據(jù)和索引。

3.1 InnoDB 表結(jié)構(gòu)

InnoDB 與 MyISAM 不同，它在系統(tǒng)表空間存儲(chǔ)數(shù)據(jù)字典信息，因此它的表不能像 MyISAM 那樣直接拷貝數(shù)據(jù)表文件移動(dòng)。MySQL5.7 采用的文件格式是 Barracuda，它支持 COMPACT 和 DYNAMIC 這兩種新的行記錄格式。

創(chuàng)建表時(shí)可以通過?ROW_FORMAT?指定行記錄格式，默認(rèn)是 DYNAMIC。可以通過命令?SHOW TABLE STATUS?查看表信息，此外，也可使用?SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t'?查看。

mysql>?SHOW?TABLE?STATUS?FROM?test?LIKE?'t'?\G ***************************?1.?row?***************************Name:?tEngine:?InnoDBVersion:?10Row_format:?DynamicRows:?2Avg_row_length:?8192Data_length:?16384 Max_data_length:?0Index_length:?0Data_free:?0Auto_increment:?3Create_time:?2019-01-13?02:24:52Update_time:?2019-01-13?02:28:16Check_time:?NULLCollation:?utf8mb4_general_ciChecksum:?NULLCreate_options:?Comment:? 1?row?in?set?(0.00?sec)

InnoDB表使用上有一些限制，如一個(gè)表最多只能有64個(gè)輔助索引，一行大小不能超過65535等，組合索引不能超過16個(gè)字段等，一般應(yīng)該不會(huì)突破限制，詳細(xì)見 innodb-restrictions。

3.2 InnoDB 表空間概述

表空間根據(jù)類型可以分為系統(tǒng)表空間，File-Per-Table 表空間，常規(guī)表空間，Undo表空間，臨時(shí)表空間等。本節(jié)分析 File-Per-Table 表空間。

系統(tǒng)表空間：
包含內(nèi)容有數(shù)據(jù)字典，雙寫緩沖，修改緩沖以及undo日志，以及在系統(tǒng)表空間創(chuàng)建的表的數(shù)據(jù)和索引。
常規(guī)表空間：
類似系統(tǒng)表空間，也是一種共享的表空間，可以通過?CREATE TABLESPACE?創(chuàng)建常規(guī)表空間，多個(gè)表可共享一個(gè)常規(guī)表空間，也可以修改表的表空間。

注意：必須刪除常規(guī)表空間中的表后才能刪除常規(guī)表空間。

CREATE?TABLESPACE?`ts1`?ADD?DATAFILE?'ts1.ibd'?Engine=InnoDB; CREATE?TABLE?t1?(c1?INT?PRIMARY?KEY)?TABLESPACE?ts1; CREATE?TABLE?t2?(c2?INT?PRIMARY?KEY)?TABLESPACE?ts1; ALTER?TABLE?t2?TABLESPACE=innodb_file_per_table;DROP?TABLE?t1; DROP?TABLESPACE?ts1;

File-Per-Table表空間：
MySQL InnoDB新版本提供了 innodb_file_per_table 選項(xiàng)，每個(gè)表可以有單獨(dú)的表空間數(shù)據(jù)文件(.ibd)，而不是全部放到系統(tǒng)表空間數(shù)據(jù)文件 ibdata1 中。
在 MySQL5.7 中該選項(xiàng)默認(rèn)開啟。
其他表空間：
其他表空間中Undo表空間存儲(chǔ)的是Undo日志。
除了存儲(chǔ)在系統(tǒng)表空間外，Undo日志也可以存儲(chǔ)在單獨(dú)的Undo表空間中。
臨時(shí)表空間則是非壓縮的臨時(shí)表的存儲(chǔ)空間，默認(rèn)是數(shù)據(jù)目錄的 ibtmp1 文件，所有臨時(shí)表共享，壓縮的臨時(shí)表用的是 File-Per-Table 表空間。

表空間文件結(jié)構(gòu)上分為段、區(qū)、頁。

段(Segment)分為索引段，數(shù)據(jù)段，回滾段等。其中索引段就是非葉子結(jié)點(diǎn)部分，而數(shù)據(jù)段就是葉子結(jié)點(diǎn)部分，回滾段用于數(shù)據(jù)的回滾和多版本控制。一個(gè)段包含256個(gè)區(qū)(256M大小)。
區(qū)是頁的集合，一個(gè)區(qū)包含64個(gè)連續(xù)的頁，默認(rèn)大小為 1MB (64*16K)。
頁是 InnoDB 管理的最小單位，常見的有 FSP_HDR，INODE, INDEX 等類型。所有頁的結(jié)構(gòu)都是一樣的，分為文件頭(前38字節(jié))，頁數(shù)據(jù)和文件尾(后8字節(jié))。頁數(shù)據(jù)根據(jù)頁的類型不同而不一樣。
- FILE_SPACE_HEADER 頁：用于存儲(chǔ)區(qū)的元信息。ibd文件的第一頁 FSP_HDR 頁通常就用于存儲(chǔ)區(qū)的元信息，里面的256個(gè) XDES(extent descriptors) 項(xiàng)存儲(chǔ)了256個(gè)區(qū)的元信息，包括區(qū)的使用情況和區(qū)里面頁的使用情況。
- IBUF_BITMAP 頁：用于記錄 change buffer的使用情況。
- INODE 頁：用于記錄文件段(FSEG)的信息，每頁有85個(gè)INODE entry，每個(gè)INODE entry占用192字節(jié)，用于描述一個(gè)文件段。每個(gè)INODE entry包括文件段ID、屬于該段的區(qū)的信息以及碎片頁數(shù)組。區(qū)信息包括 FREE(完全空閑的區(qū)), NOT_FULL(至少使用了一個(gè)頁的區(qū)), FULL(沒空閑頁的區(qū))三種類型的區(qū)的List Base Node(包含鏈表長度和頭尾頁號(hào)和偏移的結(jié)構(gòu)體)。碎片頁數(shù)組則是不同于分配整個(gè)區(qū)的單獨(dú)分配的32個(gè)頁。
- INDEX 頁：索引頁的葉子結(jié)點(diǎn)的data就是數(shù)據(jù)，如聚集索引存儲(chǔ)的行數(shù)據(jù)，輔助索引存儲(chǔ)的主鍵值。

3.3 InnoDB File-Per-Table表空間

采用 File-Per-Table 的優(yōu)缺點(diǎn)如下：

優(yōu)點(diǎn)：
可以方便回收刪除表所占的磁盤空間。
如果使用系統(tǒng)表空間的話，刪除表后空閑空間只能被 InnoDB 數(shù)據(jù)使用。
TRUNCATE TABLE 操作會(huì)更快。
可以單獨(dú)拷貝表空間數(shù)據(jù)到其他數(shù)據(jù)庫(使用 transportable tablespace 特性)，可以更方便的觀測每個(gè)表空間數(shù)據(jù)的大小。
缺點(diǎn)：
fsync 操作需要作用的多個(gè)表空間文件，比只對(duì)系統(tǒng)表空間這一個(gè)文件進(jìn)行fsync操作會(huì)多一些 IO 操作。
此外，mysqld需要維護(hù)更多的文件描述符。

表空間文件結(jié)構(gòu)

InnoDB 表空間文件 .ibd 初始大小為 96K，而InnoDB默認(rèn)頁大小為 16K，頁大小也可以通過?innodb_page_size?配置為 4K, 8K…64K 等。在ibd文件中，0-16KB偏移量即為0號(hào)數(shù)據(jù)頁，16KB-32KB的為1號(hào)數(shù)據(jù)頁，以此類推。頁的頭尾除了一些元信息外，還有Checksum校驗(yàn)值，這些校驗(yàn)值在寫入磁盤前計(jì)算得到，當(dāng)從磁盤中讀取時(shí)，重新計(jì)算校驗(yàn)值并與數(shù)據(jù)頁中存儲(chǔ)的對(duì)比，如果發(fā)現(xiàn)不同，則會(huì)導(dǎo)致 MySQL 崩潰。

ibd文件存儲(chǔ)結(jié)構(gòu)如下所示：

ibd文件存儲(chǔ)結(jié)構(gòu)

InnoDB頁分為INDEX頁、Undo頁、系統(tǒng)頁，IBUF_BITMAP頁, INODE頁等多種。

第0頁是 FSP_HDR 頁，主要用于跟蹤表空間，空閑鏈表、碎片頁以及區(qū)等信息。
第1頁是 IBUF_BITMAP 頁，保存Change Buffer的位圖。
第2頁是 INODE 頁，用于存儲(chǔ)區(qū)和單獨(dú)分配的碎片頁信息，包括FULL、FREE、NOT_FULL 等頁列表的基礎(chǔ)結(jié)點(diǎn)信息(基礎(chǔ)結(jié)點(diǎn)信息記錄了列表的起始和結(jié)束頁號(hào)和偏移等)，這些結(jié)點(diǎn)指向的是 FSP_HDR 頁中的項(xiàng)，用于記錄頁的使用情況，它們之間關(guān)系如下圖所示。
第3頁開始是索引頁 INDEX(B-tree node)，從 0xc000(每頁16K) 開始，后面還有些分配的未使用的頁。

可以在?innodb_sys_tables?表中查到表t的表空間ID為28，然后可以在 innodb_buffer_page查到所有頁信息，一共4個(gè)頁。分別是 FSP_HDR, IBUF_BITMAP, INODE, INDEX。

select?*?from?information_schema.innodb_sys_tables?where?name='test/t'; select?*?from?information_schema.innodb_buffer_page?where?SPACE=28;

索引頁分析

InnoDB引擎索引頁的結(jié)構(gòu)如下圖，可以用 hexdump查看 t.ibd 文件，然后對(duì)照InnoDB頁的結(jié)構(gòu)分析下各個(gè)頁的字段。

索引頁結(jié)構(gòu)

#?hexdump?-C?t.ibd 0000c000??95?45?82?8a?00?00?00?03??ff?ff?ff?ff?ff?ff?ff?ff??|.E..............| 0000c010??00?00?00?00?00?28?85?7c??45?bf?00?00?00?00?00?00??|.....(.|E.......| 0000c020??00?00?00?00?00?1c?00?02??00?b0?80?04?00?00?00?00??|................| 0000c030??00?9a?00?02?00?01?00?02??00?00?00?00?00?00?00?00??|................| 0000c040??00?00?00?00?00?00?00?00??00?2f?00?00?00?1c?00?00??|........./......| 0000c050??00?02?00?f2?00?00?00?1c??00?00?00?02?00?32?01?00??|.............2..| 0000c060??02?00?1c?69?6e?66?69?6d??75?6d?00?03?00?0b?00?00??|...infimum......| 0000c070??73?75?70?72?65?6d?75?6d??03?00?00?00?10?00?1b?80??|supremum........| 0000c080??00?00?01?00?00?00?00?05??68?d1?00?00?01?54?01?10??|........h....T..| 0000c090??61?62?63?05?00?00?00?18??ff?d6?80?00?00?02?00?00??|abc.............| 0000c0a0??00?00?05?69?d2?00?00?01??55?01?10?64?65?66?67?68??|...i....U..defgh| 0000c0b0??00?00?00?00?00?00?00?00??00?00?00?00?00?00?00?00??|................| * 0000fff0??00?00?00?00?00?70?00?63??95?45?82?8a?00?28?85?7c??|.....p.c.E...(.|| 00010000??00?00?00?00?00?00?00?00??00?00?00?00?00?00?00?00??|................|

FIL Header（38字節(jié)): 記錄文件頭信息。前4字節(jié)?95 45 82 8a?是 checksum，接著?00 00 00 03?是頁偏移值 3，即這是第3頁。接著 4 字節(jié)是上一頁偏移值，因?yàn)橹挥幸粋€(gè)數(shù)據(jù)頁，所以這里為?ff ff ff ff，接著 4 字節(jié)是下一頁偏移值?ff ff ff ff。然后 8 字節(jié)?00 00 00 00 00 28 85 7c 是日志序列號(hào) LSN。隨后的 2 字節(jié)45 bf是頁類型，代表是 INDEX 頁。接著 8 字節(jié)00 00 00 00 00 00 00 00表示被更新到的LSN，在 File-Per-Table 表空間中都是0。然后 4 字節(jié)00 00 00 1c` 表示該數(shù)據(jù)頁屬于的表t的表空間ID是 0x1c(28)。
INDEX Header（36字節(jié)): 記錄的是 INDEX 頁的狀態(tài)信息。前2字節(jié) 00 02 表示頁目錄的 slot 數(shù)目為2；接著2字節(jié) 00 b0 是頁中第一個(gè)記錄的指針。80 04是這頁的格式為DYNAMIC和記錄數(shù)4(包括2條System Records我們插入的2條記錄)。接著 00 00是可重用空間首指針，再后面2字節(jié)00 00是已刪除記錄數(shù)；00 9a是最后插入記錄的位置偏移，即最后插入位置是 0xc09a，即第2條記錄開始地址。00 02 是最后插入的方向，2 表示 PAGE_DIRECTION_RIGHT，即自增長方式插入。00 01 指一個(gè)方向連續(xù)插入的數(shù)量，這里為1。接著的00 02是 INDEX 頁中的真實(shí)記錄數(shù)，我們只有2條記錄。然后8字節(jié)00…00為修改該頁的最大事務(wù)ID，這個(gè)值只在輔助索引中存在，這里為0。接著2字節(jié)00 00為頁在索引樹的層級(jí)，0表示葉子結(jié)點(diǎn)。最后8個(gè)字節(jié) 00…2f為索引ID 47(索引ID可以在information_schema.INNODB_SYS_INDEXES 中查詢，可以確認(rèn) 47 正好是表 t 的主索引)。
FSEG Header：這是INDEX頁中的根結(jié)點(diǎn)才有的，非根結(jié)點(diǎn)的為0。前10字節(jié) 00 00 00 1c 00 00 00 02 00 f2 是葉子結(jié)點(diǎn)所在段的segment header，分別記錄了葉子結(jié)點(diǎn)的表空間ID 0x1c，INODE頁的頁號(hào) 2 和 INODE項(xiàng)偏移 0xf2。而后10字節(jié) 00 00 00 1c 00 00 00 02 00 32 是非葉子結(jié)點(diǎn)所在段的segment header，偏移分別是0xf2 和 0x32，即INODE頁的前2個(gè)Entry，文件段ID分別是1和2。FSEG Header中存儲(chǔ)了該 INDEX 頁的INODE項(xiàng)，INODE項(xiàng)里面則記錄了該頁存儲(chǔ)所在的文件段以及文件段頁的使用情況。對(duì)于 File-Per-Table情況下，每個(gè)單獨(dú)的表空間文件的 FSP_HDR 頁負(fù)責(zé)管理頁使用情況。

FSEG結(jié)構(gòu)關(guān)系圖

System Records(26字節(jié)): 每個(gè) INDEX 頁都有兩條虛擬記錄 infimum 和 supremum，用于限定記錄的邊界，各占 13 個(gè)字節(jié)。其中記錄頭的5個(gè)字節(jié)分別標(biāo)識(shí)了擁有記錄的數(shù)目和類型(擁有記錄數(shù)目是即后面頁目錄部分的owned值，當(dāng)前頁目錄只有兩個(gè)槽，infimum擁有記錄數(shù)只有它自己為1，而supremum擁有我們插入的2條記錄和它自己，故為3)、下一條記錄的偏移 0x1c，即位置是 0xc07f，這就是我們實(shí)際記錄開始位置。后面8個(gè)字節(jié)為 infimum + 空值，supremum類似，只是它下一條記錄偏移為0。
01?00?02?00?1c?69?6e?66?69?6d?75?6d?00??#?infimum 03?00?0b?00?00?73?75?70?72?65?6d?75?6d??#?supermum
User Records: 接下來是2條我們插入的記錄。第1條記錄前面7字節(jié)是記錄頭(Record Header)，其中前面的 1字節(jié)記錄的是可變變量的長度03，因?yàn)槲覀冇涗浿衏的值是 abc。然后1字節(jié)記錄的是可為NULL的變量是否是NULL，這里不為 NULL，故為0。接著的5字節(jié)記錄了插入順序2(infimum插入順序固定是0，supremum插入順序是1，其他記錄則是從2開始)，下一個(gè)記錄的偏移 0x1b(即下一個(gè)記錄開始位置是0xc078+0x1b=0xc093)，刪除標(biāo)記等。后面就是記錄內(nèi)容。第2條記錄同理。這里的事務(wù)ID可以通過 select * from information_schema.innodb_trx 進(jìn)行驗(yàn)證。

?03?00?00?00?10?00?1b?#?記錄頭80?00?00?01??????????#?主鍵值100?00?00?00?05?68????#?事務(wù)IDd1?00?00?01?54?01?10?#?回滾指針61?62?63?????????????#?ch的值?abc05?00?00?00?18?ff?d6?#?第2條記錄頭80?00?00?02??????????#?主鍵值200?00?00?00?05?69????#?事務(wù)IDd2?00?00?01?55?01?10?#?回滾指針64?65?66?67?68???????#?ch的值?defgh

B+樹頁詳細(xì)結(jié)構(gòu)

Page Directory(4字節(jié))：因?yàn)轫撃夸浀膕lot只有2個(gè)，每個(gè)slot占2字節(jié)，故頁目錄為 00 70 00 63 這4字節(jié)，存儲(chǔ)的是相對(duì)于最初行的位置。其中 0xc063 正好是 infimum 記錄的開始位置，而 0xc070 正好是 supremum 記錄的開始位置。使用頁目錄進(jìn)行二分查找，可以加速查詢，詳細(xì)見后面分析。
FIL Tail (8字節(jié)): 最后8字節(jié)為 95 45 82 8a 00 28 85 7c，其中 95 45 82 8a 為 checknum，跟 FIL Header的checksum一樣。后4字節(jié)00 28 85 7c 與 FIL Header的LSN的后4個(gè)字節(jié)一致。

當(dāng)然，我們也可以通過 innodb_ruby 工具來分析表空間文件。

root@stretch:/home/vagrant#?innodb_space?-s?/var/lib/mysql/ibdata1?-T?test/t?space-page-type-regions start???????end?????????count???????type???????????????? 0???????????0???????????1???????????FSP_HDR????????????? 1???????????1???????????1???????????IBUF_BITMAP????????? 2???????????2???????????1???????????INODE??????????????? 3???????????3???????????1???????????INDEX??????????????? 4???????????5???????????2???????????FREE?(ALLOCATED)???? root@stretch:/home/vagrant#?innodb_space?-s?/var/lib/mysql/ibdata1?-T?test/t?-p?3?page-records Record?127:?(id=1)?→?(ch="abc")Record?154:?(id=2)?→?(ch="defgh")

索引結(jié)構(gòu)

InnoDB數(shù)據(jù)文件本身就是索引文件，其索引分聚集索引和輔助索引，聚集索引的葉節(jié)點(diǎn)包含了完整的數(shù)據(jù)記錄，輔助索引葉節(jié)點(diǎn)數(shù)據(jù)部分是主鍵的值，除了空間索引外，InnoDB的索引實(shí)現(xiàn)基本都是 B+ 樹，如圖所示。

其中非葉子結(jié)點(diǎn)存儲(chǔ)的是子頁的最小的鍵值和子頁的頁號(hào)，葉子結(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)，數(shù)據(jù)按照索引鍵排序。同一層的頁之間用雙向鏈表連接(前面提到的FIL Header中PREV PAGE 和 NEXT PAGE)，同一頁內(nèi)的記錄用單向鏈表連接(Record Header中記錄了下一條記錄的偏移)。每一頁設(shè)置了兩個(gè)虛擬記錄Infimum和Supremum用于標(biāo)識(shí)頁的開始和結(jié)束。

索引結(jié)構(gòu)

在InnoDB中根據(jù)輔助索引查詢，如果除了主鍵外還有其他字段，則需要查詢兩遍，先根據(jù)輔助索引查詢主鍵的值，然后再到主索引中查詢得到記錄。此外，因?yàn)檩o助索引的數(shù)據(jù)部分是主鍵值，主鍵不能過大，否則會(huì)導(dǎo)致輔助索引占用空間變大，用自增ID做主鍵是個(gè)不錯(cuò)的選擇。

mysql>?create?table?t2(id?int?auto_increment?primary?key,?ch?varchar(10),?key(ch)); mysql>?insert?into?t2(ch)?values('ab');

創(chuàng)建一個(gè)新的測試表 t2，有主索引 id 和輔助索引 ch，分析 t2.ibd 文件可驗(yàn)證：

對(duì)比表t，表t2多一個(gè)INDEX頁，用于存儲(chǔ)輔助索引的根結(jié)點(diǎn)。
輔助索引的INDEX頁也有兩個(gè)系統(tǒng)記錄 infimum 和 supremum。
而用戶記錄內(nèi)容格式跟前面分析基本一致，內(nèi)容為輔助索引 ch 列的值 ab 和主鍵值1。

頁目錄

前面提到INDEX頁內(nèi)的記錄是通過單向鏈表連接在一起的，遍歷列表性能會(huì)比較差，而INDEX頁的頁目錄就是為了加速記錄搜索。表 t2 中的頁目錄只有兩項(xiàng)，分別是 0x63 和 0x70，即 99 和 112。

下面的ownedkey為這個(gè)頁目錄槽擁有的小于等于它的記錄數(shù)目，顯然 infimum 的ownedkey為 1，即只有它自己，沒有key會(huì)比infimum小。而 supremum 的owned是3，分別是我們插入的兩條記錄和它自己。

slot????offset??type??????????owned??key 0???????99??????infimum???????1??????? 1???????112?????supremum??????3?

每個(gè)頁目錄槽最少要包含4個(gè)記錄，最多包含8個(gè)記錄(包括它自己)。如果我們?cè)诒?t2 中另外插入 7 條記錄，則會(huì)增加一個(gè)新的slot，即 id 為 4 的記錄，如下：

slot????offset??type??????????owned???key 0???????99??????infimum???????1??????? 1???????207?????conventional??4???????(i=4) 2???????112?????supremum??????5??

下圖是頁目錄結(jié)構(gòu)圖，可以通過頁目錄的二分查找提高頁內(nèi)數(shù)據(jù)的查詢性能。

頁目錄結(jié)構(gòu)

3.4 InnoDB 系統(tǒng)表空間

系統(tǒng)表空間包含內(nèi)容有：數(shù)據(jù)字典，雙寫緩沖，修改緩沖，undo日志，以及在系統(tǒng)表空間創(chuàng)建的表的數(shù)據(jù)和索引。可以看到，除了分配未使用的頁外， UNDO_LOG，SYS, INDEX 頁占據(jù)了不少的空間。

UNDO_LOG 頁存儲(chǔ)的是Undo log，SYS 頁存儲(chǔ)的是數(shù)據(jù)字典、回滾段、修改緩存等信息，INDEX 是索引頁，TRX_SYS 頁用于InnoDB的事務(wù)系統(tǒng)。數(shù)據(jù)字典就是數(shù)據(jù)表的元信息，修改緩沖前面提到是為了提高IO性能也不再贅述，這里主要分析下 Undo 日志和雙寫緩沖。

root@stretch:/home/vagrant#?innodb_space?-s?/var/lib/mysql/ibdata1?space-page-type-summary type????????????????count???????percent?????description????????? ALLOCATED???????????427?????????55.60???????Freshly?allocated??? UNDO_LOG????????????125?????????16.28???????Undo?log???????????? SYS?????????????????110?????????14.32???????System?internal????? INDEX???????????????71??????????9.24????????B+Tree?index???????? INODE???????????????11??????????1.43????????File?segment?inode?? FSP_HDR?????????????9???????????1.17????????File?space?header??? IBUF_BITMAP?????????8???????????1.04????????Insert?buffer?bitmap BLOB????????????????5???????????0.65????????Uncompressed?BLOB??? TRX_SYS?????????????2???????????0.26????????Transaction?system?header

Undo 日志

MySQL的MVCC(多版本并發(fā)控制)依賴Undo Log實(shí)現(xiàn)。MySQL的表空間文件 t.ibd 存儲(chǔ)的是記錄最新值，每個(gè)記錄都有一個(gè)回滾指針(見前面圖中的Roll Ptr)，指向該記錄的最近一條Undo記錄，而每條Undo記錄都會(huì)指向它的前一條Undo記錄，如下圖所示。默認(rèn)情況下 undo log存儲(chǔ)在系統(tǒng)表空間 ibdata1 中。

Undo Log示意圖

CREATE?TABLE?`t3`?(`id`?int(11)?NOT?NULL,`a`?varchar(100)?DEFAULT?NULL,PRIMARY?KEY?(`id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4;insert?into?t3?values(1,?'A');update?t3?set?a='B'?where?id=1; update?t3?set?a='C'?where?id=1;

插入一條數(shù)據(jù)后，可以發(fā)現(xiàn)當(dāng)前 t3.ibd 文件中的記錄是 (1, ‘A’)，而 Undo Log此時(shí)有一條 insert 的記錄。如下：

root@stretch:/var/lib/mysql#?innodb_space?-s?ibdata1?-T?test/t3?-p?3?-R?127?record-history Transaction???Type????????????????Undo?record (n/a)?????????insert??????????????(id=1)?→?()

執(zhí)行后面的update語句，可以看到 undo log如下：

root@stretch:/var/lib/mysql#?innodb_space?-s?ibdata1?-T?test/t3?-p?3?-R?127?record-history Transaction???Type????????????????Undo?record 2333??????????update_existing?????(id=1)?→?(a="B") 2330??????????update_existing?????(id=1)?→?(a="A") (n/a)?????????insert??????????????(id=1)?→?()

需要注意的是，Undo Log 在事務(wù)執(zhí)行過程中就會(huì)產(chǎn)生，事務(wù)提交后才會(huì)持久化，如果事務(wù)回滾了則Undo Log也會(huì)刪除。

另外，刪除記錄并不會(huì)立即在表空間中刪除該記錄，而只是做個(gè)標(biāo)記(delete-mark)，真正的刪除則是等由后臺(tái)運(yùn)行的 purge 進(jìn)程處理。除了每條記錄有Undo Log的列表外，整個(gè)數(shù)據(jù)庫也會(huì)有一個(gè)歷史列表，purge 進(jìn)程會(huì)根據(jù)該歷史列表真正刪除已經(jīng)沒有再被其他事務(wù)使用的 delete-mark 的記錄。purge 進(jìn)程會(huì)刪除該記錄以及該記錄的 Undo Log。

雙寫緩沖

先回顧下InnoDB的記錄更新流程：先在Buffer Pool中更新，并將更新記錄到 Redo Log 文件中，Buffer Pool中的記錄會(huì)標(biāo)記為臟數(shù)據(jù)并定期刷到磁盤。由于InnoDB默認(rèn)Page大小是16KB，而磁盤通常以扇區(qū)為單位寫入，每次默認(rèn)只能寫入512個(gè)字節(jié)，無法保證16K數(shù)據(jù)可以原子的寫入。

如果寫入過程發(fā)生故障(比如機(jī)器掉電或者操作系統(tǒng)崩潰)，會(huì)出現(xiàn)頁的部分寫入(partial page writes)，導(dǎo)致難以恢復(fù)。因?yàn)?MySQL 的重做日志采用的是物理邏輯日志，即頁間是物理信息，而頁內(nèi)是邏輯信息，在發(fā)生頁部分寫入時(shí)，無法確認(rèn)數(shù)據(jù)頁的具體修改而導(dǎo)致難以恢復(fù)。

MySQL 的數(shù)據(jù)頁在真正寫入到表空間文件前，會(huì)先寫到系統(tǒng)表空間文件的一段連續(xù)區(qū)域雙寫緩沖(Double-Write Buffer，默認(rèn)大小為 2MB，128個(gè)頁)并 fsync 落盤，等雙寫緩沖寫入成功后才會(huì)將數(shù)據(jù)頁寫到實(shí)際表空間的位置。

因?yàn)殡p寫緩沖和數(shù)據(jù)頁的寫入時(shí)機(jī)不一致，如果在寫入雙寫緩沖出錯(cuò)，可以直接丟棄該緩沖頁，而如果是寫入數(shù)據(jù)頁時(shí)出錯(cuò)，則可以根據(jù)雙寫緩沖區(qū)數(shù)據(jù)恢復(fù)表空間文件。

4、InnoDB 事務(wù)隔離級(jí)別

InnoDB的多版本并發(fā)控制是基于事務(wù)隔離級(jí)別實(shí)現(xiàn)的，而事務(wù)隔離級(jí)別則是依托前面提到的 Undo Log 實(shí)現(xiàn)的。當(dāng)讀取一個(gè)數(shù)據(jù)記錄時(shí)，每個(gè)事務(wù)會(huì)使用一個(gè)讀視圖(Read View)，讀視圖用于控制事務(wù)能讀取到的記錄的版本。

InnoDB的事務(wù)隔離級(jí)別分為：Read UnCommitted，Read Committed，Repeatable Read以及Serializable。其中Serializable是基于鎖實(shí)現(xiàn)的串行化方式，嚴(yán)格來說不是事務(wù)可見性范疇。

Read Uncommitted：
未提交讀也稱為臟讀，它讀取的是當(dāng)前最新修改的記錄，即便這個(gè)修改最后并未生效。
Read Committed：
提交讀。
它基于的是當(dāng)前事務(wù)內(nèi)的語句開始執(zhí)行時(shí)的最大的事務(wù)ID。
如果其他事務(wù)修改同一個(gè)記錄，在沒有提交前，則該語句讀取的記錄還是不會(huì)變。
但是這種情況會(huì)產(chǎn)生不可重復(fù)讀，即一個(gè)事務(wù)內(nèi)多次讀取同一條記錄可能得到不同的結(jié)果(該記錄被其他事務(wù)修改并提交了)。
Repeatable Read：
可重復(fù)讀。
它基于的是事務(wù)開始時(shí)的讀視圖，直到事務(wù)結(jié)束。
不讀取其他新的事務(wù)對(duì)該記錄的修改，保證同一個(gè)事務(wù)內(nèi)的可重復(fù)讀取。
InnoDB提供了 next-key lock來解決幻讀問題，不過在一些特殊場景下，可重復(fù)讀還是可能出現(xiàn)幻讀的情況。
在實(shí)際開發(fā)中影響不大，就不贅述了。

5、InnoDB 和 ACID 模型

事務(wù)有 ACID 四個(gè)屬性， InnoDB 是支持事務(wù)的，它實(shí)現(xiàn) ACID 的機(jī)制如下：

Atomicity

innodb的原子性主要是通過提供的事務(wù)機(jī)制實(shí)現(xiàn)，與原子性相關(guān)的特性有：

Autocommit 設(shè)置。
COMMIT 和 ROLLBACK 語句(通過 Undo Log實(shí)現(xiàn))。

Consistency

innodb的一致性主要是指保護(hù)數(shù)據(jù)不受系統(tǒng)崩潰影響，相關(guān)特性包括：

InnoDB 的雙寫緩沖區(qū)(doublewrite buffer)。
InnoDB 的故障恢復(fù)機(jī)制(crash recovery)。
Isolation
innodb的隔離性也是主要通過事務(wù)機(jī)制實(shí)現(xiàn)，特別是為事務(wù)提供的多種隔離級(jí)別，相關(guān)特性包括：

Autocommit設(shè)置。
SET ISOLATION LEVEL 語句。
InnoDB 鎖機(jī)制。

Durability

innodb的持久性相關(guān)特性：

Redo log。
雙寫緩沖功能。
可以通過配置項(xiàng) innodb_doublewrite 開啟或者關(guān)閉。
配置 innodb_flush_log_at_trx_commit。
用于配置innodb如何寫入和刷新 redo 日志緩存到磁盤。
默認(rèn)為1，表示每次事務(wù)提交都會(huì)將日志緩存寫入并刷到磁盤。
innodb_flush_log_at_timeout 可以配置刷新日志緩存到磁盤的頻率，默認(rèn)是1秒。
配置 sync_binlog。
用于設(shè)置同步 binlog 到磁盤的頻率，為0表示禁止MySQL同步binlog到磁盤，binlog刷到磁盤的頻率由操作系統(tǒng)決定，性能最好但是最不安全。
為1表示每次事務(wù)提交前同步到磁盤，性能最差但是最安全。
MySQL文檔推薦是 sync_binlog 和 innodb_flush_log_at_trx_commit 都設(shè)置為 1。
操作系統(tǒng)的 fsync 系統(tǒng)調(diào)用。
UPS設(shè)備和備份策略等。

MySQL InnoDB 的實(shí)現(xiàn)非常復(fù)雜，本文只是總結(jié)了一些皮毛。有什么問題，留言一起交流吧。。。

想知道更多？掃描下面的二維碼關(guān)注我

當(dāng)當(dāng)雙12圖書優(yōu)惠活動(dòng)，活動(dòng)時(shí)間是12月7~13號(hào)，力度是5折封頂。

這里有一個(gè)滿200-40元優(yōu)惠碼「?FV5YSU?」

后臺(tái)回復(fù)"技術(shù)"，加入技術(shù)群

點(diǎn)個(gè)贊+在看，少個(gè) bug?????

總結(jié)

以上是生活随笔為你收集整理的后悔！我早该把这1W字详解的 InnoDB 原理给你！的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇：不是keys，记一次因 redis 使用
下一篇：推荐一款日志切割神器，好用到爆！！