偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

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

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

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

MySQL InnoDB 的實現(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ù)據(jù)可能還只在內(nèi)存中修改完，并沒有刷到磁盤上去。內(nèi)存是易失的，如果在數(shù)據(jù)落地前，機器掛了，那么這部分數(shù)據(jù)就丟失了。

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

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

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獨立。

Buffer Pool

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

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

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

InnoDB 的緩沖池頁鏈表分為兩部分：New sublist(默認占5/8緩存池) 和 Old sublist(默認占3/8緩存池，可以通過 innodb_old_blocks_pct修改，默認值為 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輔助索引不同于聚集索引的順序插入，如果每次修改二級索引都直接寫入磁盤，則會有大量頻繁的隨機IO。Change buffer 的主要目的是將對 非唯一 輔助索引頁的操作緩存下來，以此減少輔助索引的隨機IO，并達到操作合并的效果。它會占用部分Buffer Pool 的內(nèi)存空間。

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

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

可以通過 innodb_change_buffering 配置是否緩存輔助索引頁的修改，默認為 all，即緩存 insert/delete-mark/purge 操作(注：MySQL 刪除數(shù)據(jù)通常分為兩步，第一步是delete-mark，即只標記，而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 默認開啟。

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

Log Buffer

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

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

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

          設(shè)置為 1 因為頻繁刷磁盤效率會偏低，但是安全性高，最多丟失 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 可以配置刷新日志緩存到磁盤的頻率，默認是1秒。注意刷磁盤的頻率并不保證就正好是這個時間，可能因為MySQL的一些操作導(dǎo)致推遲或提前。

而這個 “flush” 方法并不是C標準庫的 fflush 方法(fflush是將C標準庫的緩沖寫到內(nèi)核緩沖區(qū)，并不保證刷到磁盤)，它通過 innodb_flush_method 配置的，默認是 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默認打開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的默認字符集 utf8mb4 和校驗方法 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 指定行記錄格式，默認是 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)該不會突破限制，詳細見 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 選項，每個表可以有單獨的表空間數(shù)據(jù)文件(.ibd)，而不是全部放到系統(tǒng)表空間數(shù)據(jù)文件 ibdata1 中。

          在 MySQL5.7 中該選項默認開啟。

  •  其他表空間：

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

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

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

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

  •  段(Segment)分為索引段，數(shù)據(jù)段，回滾段等。其中索引段就是非葉子結(jié)點部分，而數(shù)據(jù)段就是葉子結(jié)點部分，回滾段用于數(shù)據(jù)的回滾和多版本控制。一個段包含256個區(qū)(256M大小)。
  •  區(qū)是頁的集合，一個區(qū)包含64個連續(xù)的頁，默認大小為 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ū)的單獨分配的32個頁。
    •   INDEX 頁：索引頁的葉子結(jié)點的data就是數(shù)據(jù)，如聚集索引存儲的行數(shù)據(jù)，輔助索引存儲的主鍵值。

3.3 InnoDB File-Per-Table表空間

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

  •  優(yōu)點：

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

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

         TRUNCATE TABLE 操作會更快。

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

  •  缺點：

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

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

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

InnoDB 表空間文件 .ibd 初始大小為 96K，而InnoDB默認頁大小為 16K，頁大小也可以通過 innodb_page_size 配置為 4K, 8K…64K 等。在ibd文件中，0-16KB偏移量即為0號數(shù)據(jù)頁，16KB-32KB的為1號數(shù)據(jù)頁，以此類推。頁的頭尾除了一些元信息外，還有Checksum校驗值，這些校驗值在寫入磁盤前計算得到，當從磁盤中讀取時，重新計算校驗值并與數(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ū)和單獨分配的碎片頁信息，包括FULL、FREE、NOT_FULL 等頁列表的基礎(chǔ)結(jié)點信息(基礎(chǔ)結(jié)點信息記錄了列表的起始和結(jié)束頁號和偏移等)，這些結(jié)點指向的是 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é)是上一頁偏移值，因為只有一個數(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ù)，我們只有2條記錄。然后8字節(jié)00…00為修改該頁的最大事務(wù)ID，這個值只在輔助索引中存在，這里為0。接著2字節(jié)00 00為頁在索引樹的層級，0表示葉子結(jié)點。最后8個字節(jié) 00…2f為索引ID 47(索引ID可以在information_schema.INNODB_SYS_INDEXES 中查詢，可以確認 47 正好是表 t 的主索引)。
  •  FSEG Header：這是INDEX頁中的根結(jié)點才有的，非根結(jié)點的為0。前10字節(jié) 00 00 00 1c 00 00 00 02 00 f2 是葉子結(jié)點所在段的segment header，分別記錄了葉子結(jié)點的表空間ID 0x1c，INODE頁的頁號 2 和 INODE項偏移 0xf2。而后10字節(jié) 00 00 00 1c 00 00 00 02 00 32 是非葉子結(jié)點所在段的segment header，偏移分別是0xf2 和 0x32，即INODE頁的前2個Entry，文件段ID分別是1和2。FSEG Header中存儲了該 INDEX 頁的INODE項，INODE項里面則記錄了該頁存儲所在的文件段以及文件段頁的使用情況。對于 File-Per-Table情況下，每個單獨的表空間文件的 FSP_HDR 頁負責(zé)管理頁使用情況。

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

  •  System Records(26字節(jié)): 每個 INDEX 頁都有兩條虛擬記錄 infimum 和 supremum，用于限定記錄的邊界，各占 13 個字節(jié)。其中記錄頭的5個字節(jié)分別標識了擁有記錄的數(shù)目和類型(擁有記錄數(shù)目是即后面頁目錄部分的owned值，當前頁目錄只有兩個槽，infimum擁有記錄數(shù)只有它自己為1，而supremum擁有我們插入的2條記錄和它自己，故為3)、下一條記錄的偏移 0x1c，即位置是 0xc07f，這就是我們實際記錄開始位置。后面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，因為我們記錄中c的值是 abc。然后1字節(jié)記錄的是可為NULL的變量是否是NULL，這里不為 NULL，故為0。接著的5字節(jié)記錄了插入順序2(infimum插入順序固定是0，supremum插入順序是1，其他記錄則是從2開始)，下一個記錄的偏移 0x1b(即下一個記錄開始位置是0xc078+0x1b=0xc093)，刪除標記等。后面就是記錄內(nèi)容。第2條記錄同理。這里的事務(wù)ID可以通過 select * from information_schema.innodb_trx 進行驗證。 
  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+樹頁詳細結(jié)構(gòu)

  •  Page Directory(4字節(jié))：因為頁目錄的slot只有2個，每個slot占2字節(jié)，故頁目錄為 00 70 00 63 這4字節(jié)，存儲的是相對于最初行的位置。其中 0xc063 正好是 infimum 記錄的開始位置，而 0xc070 正好是 supremum 記錄的開始位置。使用頁目錄進行二分查找，可以加速查詢，詳細見后面分析。
  •  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é)一致。

當然，我們也可以通過 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é)點包含了完整的數(shù)據(jù)記錄，輔助索引葉節(jié)點數(shù)據(jù)部分是主鍵的值，除了空間索引外，InnoDB的索引實現(xiàn)基本都是 B+ 樹，如圖所示。

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

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

在InnoDB中根據(jù)輔助索引查詢，如果除了主鍵外還有其他字段，則需要查詢兩遍，先根據(jù)輔助索引查詢主鍵的值，然后再到主索引中查詢得到記錄。此外，因為輔助索引的數(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 文件可驗證：

  •  對比表t，表t2多一個INDEX頁，用于存儲輔助索引的根結(jié)點。
  •  輔助索引的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實現(xiàn)。MySQL的表空間文件 t.ibd 存儲的是記錄最新值，每個記錄都有一個回滾指針(見前面圖中的Roll Ptr)，指向該記錄的最近一條Undo記錄，而每條Undo記錄都會指向它的前一條Undo記錄，如下圖所示。默認情況下 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)當前 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也會刪除。

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

雙寫緩沖

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

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

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

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

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

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

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

  •  Read Uncommitted：

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

  •  Read Committed：

          提交讀。

          它基于的是當前事務(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)幻讀的情況。

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

5、InnoDB 和 ACID 模型

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

Atomicity

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

Autocommit 設(shè)置。

COMMIT 和 ROLLBACK 語句(通過 Undo Log實現(xiàn))。

Consistency

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

InnoDB 的雙寫緩沖區(qū)(doublewrite buffer)。

InnoDB 的故障恢復(fù)機制(crash recovery)。

Isolation

innodb的隔離性也是主要通過事務(wù)機制實現(xiàn)，特別是為事務(wù)提供的多種隔離級別，相關(guān)特性包括：

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

Durability

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

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

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

  •  配置 innodb_flush_log_at_trx_commit。

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

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

          innodb_flush_log_at_timeout 可以配置刷新日志緩存到磁盤的頻率，默認是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)并行

2022-09-06 08:02:40

死鎖順序鎖輪詢鎖

2024-08-13 15:07:20

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ò)代碼

2025-10-27 04:00:00

AI編程傳統(tǒng)軟件

2023-02-16 18:22:44

ChatGPTWolfram語言

2020-11-05 08:14:17

鏈表

2023-10-26 00:37:40

滴滴彈性云公有云

2022-07-11 10:08:34

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

2017-12-07 15:34:57

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

2024-01-02 22:47:47

Nacos注冊中心節(jié)點

2024-07-19 08:34:18

2023-01-06 08:15:58

StreamAPI接口
點贊
收藏

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