自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

為什么別人能用好MySQL？萬字詳解其復(fù)雜原理

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

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

MySQL InnoDB 的實(shí)現(xiàn)非常復(fù)雜，本文只是總結(jié)了一些皮毛，希望以后能夠研究的更加深入些。

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

InnoDB 通過 redo 日志來保證數(shù)據(jù)的一致性。如果保存所有的重做日志，顯然可以在系統(tǒng)崩潰時根據(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)存會分配給 Buffer Pool。

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

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

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

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

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

Change Buffer

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

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

如果輔助索引頁已經(jīng)在緩沖區(qū)了，則直接修改即可；如果不在，則先將修改保存到 Change Buffer。Change Buffer的數(shù)據(jù)在對應(yīng)輔助索引頁讀取到緩沖區(qū)時合并到真正的輔助索引頁中。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 命令。更多信息見 

  1. -------------------------------------  
  2. INSERT BUFFER AND ADAPTIVE HASH INDEX  
  3. -------------------------------------  
  4. Ibuf: size 1, free list len 0, seg size 2, 0 merges  
  5. merged operations:  
  6.  insert 0, delete mark 0, delete 0  
  7. discarded operations:  
  8.  insert 0, delete mark 0, delete 0  
  9. Hash table size 34673, node heap has 0 buffer(s)  
  10. Hash table size 34673, node heap has 0 buffer(s)  
  11. Hash table size 34673, node heap has 0 buffer(s)  
  12. Hash table size 34673, node heap has 0 buffer(s)  
  13. Hash table size 34673, node heap has 0 buffer(s)  
  14. Hash table size 34673, node heap has 0 buffer(s)  
  15. Hash table size 34673, node heap has 0 buffer(s)  
  16. Hash table size 34673, node heap has 0 buffer(s) 

Adaptive Hash Index

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

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

Log Buffer

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

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

  •  默認(rèn)為1，表示每次事務(wù)提交都會將 Log Buffer 寫入操作系統(tǒng)緩存，并調(diào)用配置的 “flush” 方法將數(shù)據(jù)寫到磁盤。

          設(shè)置為 1 因?yàn)轭l繁刷磁盤效率會偏低，但是安全性高，最多丟失 1個 事務(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秒。注意刷磁盤的頻率并不保證就正好是這個時間，可能因?yàn)镸ySQL的一些操作導(dǎo)致推遲或提前。

而這個 “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 基本每秒都會將 Log buffer落盤。而InnoDB中使用的 redo log 和 undo log，它們是分開存儲的。

redo log在內(nèi)存中有l(wèi)og buffer，在磁盤對應(yīng)ib_logfile文件。而undo log是記錄在表空間ibd文件中的，InnoDB為undo log會生成undo頁，對undo log本身的操作（比如向undo log插入一條記錄），也會記錄redo log，因此undo log并不需要馬上落盤。而 redo log 則通常會分配一塊連續(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 文件)，臨時表空間，常規(guī)表空間，Undo 表空間以及 file-per-table 表空間(MySQL5.7默認(rèn)打開file_per_table 配置）。

          系統(tǒng)表空間又包括了InnoDB數(shù)據(jù)字典，雙寫緩沖區(qū)(Doublewrite Buffer)，修改緩存(Change Buffer），Undo日志等。

  •  Redo日志：

         存儲的就是 Log Buffer 刷到磁盤的數(shù)據(jù)。

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

  1. mysql> create database test;  
  2. mysql> use test;  
  3. mysql> create table t (id int auto_increment primary key, ch varchar(5000));  
  4. mysql> insert into t (ch) values('abc');  
  5. mysql> insert into t (ch) values('defgh'); 

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

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

InnoDB 與 MyISAM 不同，它在系統(tǒng)表空間存儲數(shù)據(jù)字典信息，因此它的表不能像 MyISAM 那樣直接拷貝數(shù)據(jù)表文件移動。MySQL5.7 采用的文件格式是 Barracuda，它支持 COMPACT 和 DYNAMIC 這兩種新的行記錄格式。創(chuàng)建表時可以通過 ROW_FORMAT 指定行記錄格式，默認(rèn)是 DYNAMIC?？梢酝ㄟ^命令 SHOW TABLE STATUS 查看表信息，此外，也可使用 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t' 查看。 

  1. mysql> SHOW TABLE STATUS FROM test LIKE 't' \G  
  2. *************************** 1. row ***************************  
  3.            Name: t  
  4.          Engine: InnoDB  
  5.         Version: 10  
  6.      Row_format: Dynamic  
  7.            Rows: 2  
  8.  Avg_row_length: 8192  
  9.     Data_length: 16384  
  10. Max_data_length: 0  
  11.    Index_length: 0  
  12.       Data_free: 0  
  13.  Auto_increment: 3  
  14.     Create_time: 2019-01-13 02:24:52  
  15.     Update_time: 2019-01-13 02:28:16  
  16.      Check_time: NULL  
  17.       Collation: utf8mb4_general_ci  
  18.        Checksum: NULL  
  19.  Create_options:   
  20.         Comment:   
  21. 1 row in set (0.00 sec) 

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

3.2 InnoDB 表空間概述

表空間根據(jù)類型可以分為系統(tǒng)表空間，F(xiàn)ile-Per-Table 表空間，常規(guī)表空間，Undo表空間，臨時表空間等。本節(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ī)表空間，多個表可共享一個常規(guī)表空間，也可以修改表的表空間。

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

  1. CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;  
  2. CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;  
  3. CREATE TABLE t2 (c2 INT PRIMARY KEY) TABLESPACE ts1;  
  4. ALTER TABLE t2 TABLESPACE=innodb_file_per_table;  
  5. DROP TABLE t1;  
  6. DROP TABLESPACE ts1; 
  •  File-Per-Table表空間：

          MySQL InnoDB新版本提供了 innodb_file_per_table 選項，每個表可以有單獨(dú)的表空間數(shù)據(jù)文件(.ibd)，而不是全部放到系統(tǒng)表空間數(shù)據(jù)文件 ibdata1 中。

          在 MySQL5.7 中該選項默認(rèn)開啟。

  •  其他表空間：

          其他表空間中Undo表空間存儲的是Undo日志。

           除了存儲在系統(tǒng)表空間外，Undo日志也可以存儲在單獨(dú)的Undo表空間中。

          臨時表空間則是非壓縮的臨時表的存儲空間，默認(rèn)是數(shù)據(jù)目錄的 ibtmp1 文件，所有臨時表共享，壓縮的臨時表用的是 File-Per-Table 表空間。

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

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

3.3 InnoDB File-Per-Table表空間

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

  •  優(yōu)點(diǎn)：

         可以方便回收刪除表所占的磁盤空間。

         如果使用系統(tǒng)表空間的話，刪除表后空閑空間只能被 InnoDB 數(shù)據(jù)使用。

         TRUNCATE TABLE 操作會更快。

         可以單獨(dú)拷貝表空間數(shù)據(jù)到其他數(shù)據(jù)庫(使用 transportable tablespace 特性)，可以更方便的觀測每個表空間數(shù)據(jù)的大小。

  •  缺點(diǎn)：

          fsync 操作需要作用的多個表空間文件，比只對系統(tǒng)表空間這一個文件進(jìn)行fsync操作會多一些 IO 操作。

         此外，mysqld需要維護(hù)更多的文件描述符。

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

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

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

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

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

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

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

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

索引頁分析

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

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

  1. # hexdump -C t.ibd  
  2. 0000c000  95 45 82 8a 00 00 00 03  ff ff ff ff ff ff ff ff  |.E..............|  
  3. 0000c010  00 00 00 00 00 28 85 7c  45 bf 00 00 00 00 00 00  |.....(.|E.......|  
  4. 0000c020  00 00 00 00 00 1c 00 02  00 b0 80 04 00 00 00 00  |................|  
  5. 0000c030  00 9a 00 02 00 01 00 02  00 00 00 00 00 00 00 00  |................|  
  6. 0000c040  00 00 00 00 00 00 00 00  00 2f 00 00 00 1c 00 00  |........./......|  
  7. 0000c050  00 02 00 f2 00 00 00 1c  00 00 00 02 00 32 01 00  |.............2..|  
  8. 0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|  
  9. 0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1b 80  |supremum........|  
  10. 0000c080  00 00 01 00 00 00 00 05  68 d1 00 00 01 54 01 10  |........h....T..|  
  11. 0000c090  61 62 63 05 00 00 00 18  ff d6 80 00 00 02 00 00  |abc.............|  
  12. 0000c0a0  00 00 05 69 d2 00 00 01  55 01 10 64 65 66 67 68  |...i....U..defgh|  
  13. 0000c0b0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|  
  14. *  
  15. 0000fff0  00 00 00 00 00 70 00 63  95 45 82 8a 00 28 85 7c  |.....p.c.E...(.||  
  16. 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)橹挥幸粋€數(shù)據(jù)頁，所以這里為 ff ff ff ff，接著 4 字節(jié)是下一頁偏移值 ff ff ff ff。然后 8 字節(jié) 00 00 00 00 00 28 85 7c 是日志序列號 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 是頁中第一個記錄的指針。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 指一個方向連續(xù)插入的數(shù)量，這里為1。接著的00 02是 INDEX 頁中的真實(shí)記錄數(shù)，我們只有2條記錄。然后8字節(jié)00…00為修改該頁的最大事務(wù)ID，這個值只在輔助索引中存在，這里為0。接著2字節(jié)00 00為頁在索引樹的層級，0表示葉子結(jié)點(diǎn)。最后8個字節(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頁的頁號 2 和 INODE項偏移 0xf2。而后10字節(jié) 00 00 00 1c 00 00 00 02 00 32 是非葉子結(jié)點(diǎn)所在段的segment header，偏移分別是0xf2 和 0x32，即INODE頁的前2個Entry，文件段ID分別是1和2。FSEG Header中存儲了該 INDEX 頁的INODE項，INODE項里面則記錄了該頁存儲所在的文件段以及文件段頁的使用情況。對于 File-Per-Table情況下，每個單獨(dú)的表空間文件的 FSP_HDR 頁負(fù)責(zé)管理頁使用情況。

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

  •  System Records(26字節(jié)): 每個 INDEX 頁都有兩條虛擬記錄 infimum 和 supremum，用于限定記錄的邊界，各占 13 個字節(jié)。其中記錄頭的5個字節(jié)分別標(biāo)識了擁有記錄的數(shù)目和類型(擁有記錄數(shù)目是即后面頁目錄部分的owned值，當(dāng)前頁目錄只有兩個槽，infimum擁有記錄數(shù)只有它自己為1，而supremum擁有我們插入的2條記錄和它自己，故為3)、下一條記錄的偏移 0x1c，即位置是 0xc07f，這就是我們實(shí)際記錄開始位置。后面8個字節(jié)為 infimum + 空值，supremum類似，只是它下一條記錄偏移為0。 
  1. 01 00 02 00 1c 69 6e 66 69 6d 75 6d 00  # infimum  
  2.  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開始)，下一個記錄的偏移 0x1b(即下一個記錄開始位置是0xc078+0x1b=0xc093)，刪除標(biāo)記等。后面就是記錄內(nèi)容。第2條記錄同理。這里的事務(wù)ID可以通過 select * from information_schema.innodb_trx 進(jìn)行驗(yàn)證。 
  1. 03 00 00 00 10 00 1b # 記錄頭  
  2. 80 00 00 01          # 主鍵值1  
  3. 00 00 00 00 05 68    # 事務(wù)ID  
  4. d1 00 00 01 54 01 10 # 回滾指針  
  5. 61 62 63             # ch的值 abc  
  6. 05 00 00 00 18 ff d6 # 第2條記錄頭  
  7. 80 00 00 02          # 主鍵值2  
  8. 00 00 00 00 05 69    # 事務(wù)ID  
  9. d2 00 00 01 55 01 10 # 回滾指針  
  10. 64 65 66 67 68       # ch的值 defgh 

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

  •  Page Directory(4字節(jié))：因?yàn)轫撃夸浀膕lot只有2個，每個slot占2字節(jié)，故頁目錄為 00 70 00 63 這4字節(jié)，存儲的是相對于最初行的位置。其中 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個字節(jié)一致。

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

  1. root@stretch:/home/vagrant# innodb_space -s /var/lib/mysql/ibdata1 -T test/t space-page-type-regions  
  2. start       end         count       type                  
  3. 0           0           1           FSP_HDR              
  4. 1           1           1           IBUF_BITMAP           
  5. 2           2           1           INODE                 
  6. 3           3           1           INDEX                 
  7. 4           5           2           FREE (ALLOCATED)      
  8. root@stretch:/home/vagrant# innodb_space -s /var/lib/mysql/ibdata1 -T test/t -p 3 page-records  
  9. Record 127: (id=1) → (ch="abc")  
  10. 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)存儲的是子頁的最小的鍵值和子頁的頁號，葉子結(jié)點(diǎn)存儲的是數(shù)據(jù)，數(shù)據(jù)按照索引鍵排序。同一層的頁之間用雙向鏈表連接(前面提到的FIL Header中PREV PAGE 和 NEXT PAGE)，同一頁內(nèi)的記錄用單向鏈表連接(Record Header中記錄了下一條記錄的偏移)。每一頁設(shè)置了兩個虛擬記錄Infimum和Supremum用于標(biāo)識頁的開始和結(jié)束。

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

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

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

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

  •  對比表t，表t2多一個INDEX頁，用于存儲輔助索引的根結(jié)點(diǎn)。
  •  輔助索引的INDEX頁也有兩個系統(tǒng)記錄 infimum 和 supremum。

          而用戶記錄內(nèi)容格式跟前面分析基本一致，內(nèi)容為輔助索引 ch 列的值 ab 和 主鍵值1。

頁目錄

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

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

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

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

  1. slot    offset  type          owned   key  
  2. 0       99      infimum       1         
  3. 1       207     conventional  4       (i=4)  
  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 頁存儲的是Undo log，SYS 頁存儲的是數(shù)據(jù)字典、回滾段、修改緩存等信息，INDEX 是索引頁，TRX_SYS 頁用于InnoDB的事務(wù)系統(tǒng)。數(shù)據(jù)字典就是數(shù)據(jù)表的元信息，修改緩沖前面提到是為了提高IO性能也不再贅述，這里主要分析下 Undo 日志和雙寫緩沖。 

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

Undo 日志

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

Undo Log示意圖 

  1. CREATE TABLE `t3` (  
  2.   `id` int(11) NOT NULL,  
  3.   `a` varchar(100) DEFAULT NULL,  
  4.   PRIMARY KEY (`id`)  
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  
  6. insert into t3 values(1, 'A');  
  7. update t3 set a='B' where id=1;  
  8. update t3 set a='C' where id=1; 

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

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

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

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

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

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

雙寫緩沖

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

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

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

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

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

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

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

  •  Read Uncommitted：

    未提交讀也稱為臟讀，它讀取的是當(dāng)前最新修改的記錄，即便這個修改最后并未生效。

  •  Read Committed：

          提交讀。

          它基于的是當(dāng)前事務(wù)內(nèi)的語句開始執(zhí)行時的最大的事務(wù)ID。

          如果其他事務(wù)修改同一個記錄，在沒有提交前，則該語句讀取的記錄還是不會變。

          但是這種情況會產(chǎn)生不可重復(fù)讀，即一個事務(wù)內(nèi)多次讀取同一條記錄可能得到不同的結(jié)果(該記錄被其他事務(wù)修改并提交了)。

  •  Repeatable Read：

          可重復(fù)讀。

          它基于的是事務(wù)開始時的讀視圖，直到事務(wù)結(jié)束。

          不讀取其他新的事務(wù)對該記錄的修改，保證同一個事務(wù)內(nèi)的可重復(fù)讀取。

          InnoDB提供了 next-key lock來解決幻讀問題，不過在一些特殊場景下，可重復(fù)讀還是可能出現(xiàn)幻讀的情況。

          在實(shí)際開發(fā)中影響不大，就不贅述了。

5、InnoDB 和 ACID 模型

事務(wù)有 ACID 四個屬性， 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ù)提供的多種隔離級別，相關(guān)特性包括：

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

Durability

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

  •     Redo log。
  •     雙寫緩沖功能。

    可以通過配置項 innodb_doublewrite 開啟或者關(guān)閉。

  •  配置 innodb_flush_log_at_trx_commit。

          用于配置innodb如何寫入和刷新 redo 日志緩存到磁盤。

          默認(rèn)為1，表示每次事務(wù)提交都會將日志緩存寫入并刷到磁盤。

          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è)備和備份策略等。 

 

責(zé)任編輯：龐桂玉 來源： 數(shù)據(jù)庫開發(fā)
MySQL數(shù)據(jù)庫架構(gòu)
相關(guān)推薦

2024-12-31 00:00:01

驅(qū)動設(shè)計應(yīng)用場景業(yè)務(wù)邏輯

2024-08-30 10:29:21

2023-10-31 12:58:00

TypeScriptJavaScript

2021-03-16 08:21:29

Spark系統(tǒng)并行

2024-08-13 15:07:20

2022-09-06 08:02:40

死鎖順序鎖輪詢鎖

2021-11-11 09:27:02

技術(shù)RedisMySQL

2024-09-26 13:33:12

2021-03-18 10:04:46

數(shù)據(jù)倉庫體系

2020-01-15 09:53:59

MySQL緩存索引

2022-05-18 08:45:25

Nginx網(wǎng)絡(luò)代碼

2022-07-11 10:08:34

大數(shù)據(jù)平臺機(jī)房

2023-10-26 00:37:40

滴滴彈性云公有云

2020-11-05 08:14:17

鏈表

2023-02-16 18:22:44

ChatGPTWolfram語言

2017-12-07 15:34:57

數(shù)據(jù)庫MySQL優(yōu)化原理

2024-01-02 22:47:47

Nacos注冊中心節(jié)點(diǎn)

2024-07-19 08:34:18

2023-01-06 08:15:58

StreamAPI接口

2021-10-18 11:58:56

負(fù)載均衡虛擬機(jī)
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號