上一篇我們介紹了 [MySQL 伺服器的體系結構](http://blog.itpub.net/24945919/viewspace-2855656/)，其中外掛式儲存引擎是 MySQL 與其他資料庫管理系統的最大區別。InnoDB 作為 MySQL 預設的儲存引擎應用最為廣泛；因此，本篇我們來介紹一下 InnoDB 儲存引擎。

### InnoDB 概述
InnoDB 是一個具有高可靠性和高效能的通用儲存引擎，也是 MySQL 5.5 之後的預設儲存引擎。因此，如果`CREATE TABLE`語句沒有指定`ENGINE`選項，預設建立的就是 InnoDB 表。

> ?使用`SHOW VARIABLES LIKE 'default_storage_engine';`命令可以檢視預設的儲存引擎。

在進一步討論 InnoDB 體系結構之前，我們先介紹幾個 InnoDB 儲存引擎的關鍵特性：

- InnoDB 表的資料修改操作（DML）具有事務安全性（ACID），支援事務提交、事務回滾以及故障恢復，能夠保障資料的一致性和完整性；
 - InnoDB 採用更細粒度的行級鎖和類似 Oracle 的一致性讀（MVCC），能夠提高併發性和效能。
 - InnoDB 按照主鍵索引（clustered index）的順序組織表中的資料，優化了基於主鍵欄位的查詢。
 - InnoDB 支援外來鍵約束（FOREIGN KEY），能夠維護多個表之間的資料完整性。

當然，InnoDB 儲存引擎提供的功能遠遠不止與此；正是由於這些強大的功能，使得 MySQL 能夠像 Oracle、Microsoft SQL Server 等商業資料庫一樣大量應用在企業系統中。

### InnoDB 系統結構
下圖顯示了 InnoDB 儲存引擎的記憶體結構和磁碟結構。
![InnoDB](https://img-blog.csdnimg.cn/20200212143717765.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70)

記住這張圖可以幫助我們理解 InnoDB 的體系結構，接下來我們分別討論 InnoDB 的記憶體結構和磁碟結構。

### InnoDB 記憶體結構
InnoDB 提供了自己的記憶體元件，主要包括**緩衝池**（Buffer Pool）、**變更緩衝**（Change Buffer）、**日誌緩衝**（Log Buffer）以及**自適應雜湊索引**（Adaptive Hash Index）技術。

#### 緩衝池
緩衝池是 InnoDB 在記憶體中的一個緩衝區域，主要用於快取訪問過的表和索引等資料。緩衝池利用記憶體直接處理資料，避免磁碟操作，從而加快了資料處理的速度。

> ?在專用的 MySQL 伺服器上，通常會給緩衝池分配多達 80% 的實體記憶體。

以下命令顯示了 InnoDB 緩衝池相關的配置：

```sql
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 8388608        |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 8388608        |
+-------------------------------------+----------------+
11 rows in set (0.00 sec)
```

其中，innodb_buffer_pool_chunk_size 表示每個緩衝塊的大小；innodb_buffer_pool_instances 表示緩衝池的例項個數，每個例項由數量相同的緩衝塊組成；innodb_buffer_pool_size 表示總的緩衝池大小，是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍數。

**緩衝池管理演算法**

為了提高大量讀取操作時的效率，緩衝池被劃分為頁（page），每個頁可能包含多行資料。為了提高快取管理的效率，緩衝池被實現為頁組成的連結列表。最終緩衝池使用特定的 LRU（最近最少使用）演算法進行管理，從而將頻繁訪問的資料保留在快取中，將最少使用的快取頁移除。

下圖演示了緩衝池管理的 LRU 演算法。
![buffer pool](https://img-blog.csdnimg.cn/20200212170252797.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70)

InnoDB 使用 LRU 演算法略有改動，快取池被分為兩個部分：頭部的 5/8 是最近被訪問過的一個新的子列表，尾部的 3/8 是最近較少訪問的一箇舊的子列表。這個比例由系統變數 innodb_old_blocks_pct 控制：

當一個新的頁需要被快取時，最近最少使用的頁將被剔除，新頁將被放入快取池的新舊子列表的中間；這種方式被稱為中間點插入策略（midpoint insertion strategy）。使用者提交的操作（例如 SQL 查詢）或者 InnoDB 的預讀（read-ahead）操作都會導致新頁的快取。

一方面，訪問舊子列表中的頁將會使得它被移動到新子列表的頭部，變得更新。如果是使用者操作引起的訪問，該頁將會立即被移動到新的子列表中；如果是預讀操作引起的訪問，不會立即導致移動，也可能根本不會移動。

另一方面，沒有被訪問的快取頁將會逐漸被移動到列表的尾部，變得更舊。新子列表和舊子列表中的頁都會隨著其他頁的前移變得更舊；舊子列表中的頁還會隨著新頁的加入變得更舊，最終到達列表的最尾部並且被剔除。

我們可以輸入`SHOW ENGINE INNODB STATUS`命令，利用 InnoDB 標準監控輸出檢視緩衝池的使用指標，相關資訊顯示在 BUFFER POOL AND MEMORY 部分：

```sql
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Dictionary memory allocated 380485
Buffer pool size   512
Free buffers       249
Database pages     259
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 997, created 142, written 156
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 259, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
```

其中的 Buffer pool size 是緩衝池分配的資料頁數量（512），乘以 innodb_page_size（16384）等於緩衝池的大小（8388608）。

> ?關於 Buffer Pool 的配置和優化我們將會在 MySQL 例項優化的部分進行介紹。

#### 變更緩衝
變更緩衝快取了那些不在緩衝池中的二級索引（secondary index）頁的修改操作。`INSERT`、`UPDATE`或者`DELETE`操作導致的變更將會在此緩衝，隨後再合併（由其他讀取操作引起）到緩衝池中。下圖演示了變更緩衝的作用過程。

![change buffer](https://img-blog.csdnimg.cn/20200213113847911.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70)

與聚集索引（clustered index）不同，二級索引通常是非唯一索引，索引的插入、更新、刪除通常是順序隨機的操作。將變更進行快取，並且在隨後讀入緩衝池時進行合併，能夠避免將輔助索引頁從磁碟讀入緩衝池所需的大量隨機 I/O。

當系統處於空閒狀態或在緩慢關閉期間執行清除操作，定期將更新後的索引頁寫入磁碟。相對於每次將資料即寫入磁碟，這種清除操作可以更有效地寫入多個連續的索引值。

在記憶體中，變更緩衝屬於緩衝池的一部分。在磁碟上，變更緩衝屬於系統表空間的一部分；當資料庫伺服器關閉時，索引變更將會被緩衝到磁碟中。

系統變數 [innodb_change_buffering](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_change_buffering) 決定了何種型別的操作會被緩衝，預設為 ALL。

如果索引中包含降序索引列或主鍵中包含降序索引列，就不會對二級索引進行變更緩衝。

我們同樣可以輸入`SHOW ENGINE INNODB STATUS`命令，利用 InnoDB 標準監控輸出檢視變更緩衝的狀態資訊：

```sql
-------------------------------------
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 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
```

因為變更緩衝最開始只支援插入操作，所以顯示為 INSERT BUFFER AND ADAPTIVE HASH INDEX。

> ?關於 Change Buffer 的配置和優化我們將會在 MySQL 例項優化的部分進行介紹。

#### 日誌緩衝
日誌緩衝是重做日誌（Redo Log）的記憶體緩衝，日誌緩衝的大小由變數 [innodb_log_buffer_size](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_buffer_size) 決定，預設為 16 MB。日誌緩衝的內容會定期重新整理到磁碟檔案。設定一個大的日誌緩衝使得大型事務不必在提交之前將重做日誌資料寫入磁碟。因此，如果存在需要更新、插入或者刪除大量資料的事務，可以通過增加日誌緩衝的大小減少磁碟 I/O。

系統變數 [innodb_flush_log_at_trx_commit](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit) 用於控制日誌緩衝寫入磁碟的方式。預設值為 1，即每次事務提交都會重新整理緩衝到磁碟，滿足 ACID 特性。

系統變數 [innodb_flush_log_at_timeout](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_timeout) 用於控制日誌緩衝重新整理到磁碟的頻率。預設值為 1 秒，即每隔 1 秒重新整理一次。

> ?關於 Log Buffer 的配置和優化我們將會在 MySQL 例項優化的部分進行介紹。

#### 自適應雜湊索引

InnoDB 包含了一個監控索引查詢的機制，當 InnoDB 發現雜湊索引可以提高查詢的效能時會自動建立雜湊索引。雜湊索引基於索引鍵的一個字首部分建立，可能只包含了 B+樹索引中的一些值，通常時頻繁訪問的索引頁。

當一個表能夠差不多完全載入到記憶體中，雜湊索引可以直接定位到所有資料，因此能夠提高查詢效能。自適應雜湊索引特性由變數 [innodb_adaptive_hash_index](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index)  設定，預設為 ON。但是由於它需要佔用緩衝池的記憶體，只能用於等值查詢，而且只在特定的情況下有效，因此 MySQL 5.6 開始建議關閉該選項。

我們可以利用`SHOW ENGINE INNODB STATUS`命令檢視自適應雜湊索引的使用情況，相關的資料也顯示在 INSERT BUFFER AND ADAPTIVE HASH INDEX 部分。

### InnoDB 磁碟結構
InnoDB 提供的磁碟儲存元件主要包括**表空間**（Tablespace）、**表**（Table）、**索引**（Index）、**重做日誌**（Redo Log）、**回滾日誌**（Undo Logs）以及**雙寫緩衝**（Doublewrite Buffer）。
#### 表空間
表空間是一個邏輯上的儲存概念，用於儲存資料表、索引、回滾（Undo）資料等。一個表空間對應作業系統上的一個或者多個檔案。從邏輯概念上來說，表空間又是由段（Segment）組成，段由區間（Extent）組成，區間由頁（Page）組成，頁最終由行（Row）組成。
![tablespace](https://img-blog.csdnimg.cn/20200217094415288.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70 =628x)

一個 InndoDB 表通常對應一個資料段，而區間是磁碟分配的基本單位，頁（預設為 16 KB）是 InndoDB 管理磁碟的最小單位，與作業系統的頁（通常是 4 KB）概念不同。

InnoDB 提供的表空間包括：系統表空間（System Tablespace）、獨立表空間（File-Per-Table Tablespaces）、通用表空間（General Tablespaces）、回滾表空間（Undo Tablespaces）以及臨時表空間（Temporary Tablespaces）。

**系統表空間**

系統表空間用於儲存雙寫緩衝和變更緩衝。如果建立表和索引時不使用獨立表空間或通用表空間，它們也會被儲存到系統表空間；不推薦這種做法。在 MySQL 8.0 之前，系統表空間中還包含了 InnoDB 資料字典資訊；從 MySQL 8.0 開始， InnoDB 使用統一的 MySQL 資料字典儲存後設資料。

系統表空間可以擁有一個或多個資料檔案。預設情況下在資料目錄中建立一個名為 ibdata1 的系統表空間資料檔案。系統表空間資料檔案的大小和數量由系統引數 [innodb_data_file_path](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_data_file_path) 進行控制。

**獨立表空間**

獨立表空間（File-Per-Table Tablespaces）用於儲存單個 InnoDB 表的資料和索引，每個表空間在檔案系統中對應單個資料檔案。舉例來說，如果我們為 test 資料庫建立一個表 t1，MySQL 會在資料目錄下的 test 子目錄中建立一個資料檔案 t1.idb。

InnoDB 預設使用獨立表空間建立表，可以使用系統變數 [innodb_file_per_table](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_file_per_table) 進行控制。如果禁用該引數，InnoDB 將會預設在系統表空間中建立表。

**通用表空間**

通用表空間是一種共享的 InnoDB 表空間，可以供多個表和索引使用。通用表空間比獨立表空間具有更高的記憶體利用率。MySQL 伺服器將會快取表空間的後設資料，包含多個表的通用表空間需要的記憶體比多個獨立表空間更少。

通用表空間可以像獨立表空間一樣在 MySQL 資料目錄內部或者外部建立資料檔案，從而為關鍵的表指定單獨的儲存，例如  RAID 或者 DRBD，提高資料訪問的效能。

通用表空間使用 [CREATE TABLESPACE](https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html) 語句建立。

**回滾表空間**

回滾表空間用於儲存回滾日誌，回滾日誌記錄中包含了撤銷事務對聚集索引記錄所作的最新修改所需的資訊。回滾記錄儲存在回滾日誌段中，回滾日誌段儲存在回滾段中。系統變數 [innodb_rollback_segments](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_segments) 決定了每個回滾表空間分配的回滾段數量。

MySQL 例項初始化時會建立兩個回滾表空間。 預設的回滾表空間在 innodb_undo_directory 引數指定的目錄中建立，如果沒有定義該引數，則在資料目錄中建立。預設回滾表空間的資料檔名為 undo_001 和 undo_002，對應資料字典中的回滾表空間名為 innodb_undo_001 和 innodb_undo_002。

從 MySQL 8.0.14 開始，可以使用 [CREATE UNDO TABLESPACE](https://dev.mysql.com/doc/refman/8.0/en/create-tablespace.html) 增加額外的回滾表空間；一個 MySQL 例項最多可以存在 127 個回滾表空間，包括預設的兩個回滾表空間。

**臨時表空間**

InnoDB 存在兩種臨時表空間：**會話臨時表空間**（session temporary tablespaces）和一個**全域性臨時表空間**（global temporary tablespace）。

會話臨時表空間用於儲存使用者建立的臨時表；當 InnoDB 被設定為磁碟內部臨時表的儲存引擎時，會話臨時表空間也用於優化器建立的內部臨時表。從 MySQL 8.0.16 開始，磁碟內部臨時表的儲存引擎永遠都是 InnoDB；在此之前由引數 internal_tmp_disk_storage_engine 決定 。

系統變數 [innodb_temp_tablespaces_dir](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_temp_tablespaces_dir) 決定了會話臨時表空間的檔案目錄，預設為資料目錄下的 #innodb_temp 子目錄。 表 INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES 儲存了會話臨時表空間的後設資料，表 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 儲存了當前活動的使用者臨時表的後設資料。

全域性臨時表空間（ibtmp1）儲存了使用者臨時表修改資訊的回滾段資料。系統變數 [innodb_temp_data_file_path](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path) 定義了全域性臨時表空間資料檔案的相對路徑、名稱、大小以及屬性。如果沒有指定該引數，預設在 innodb_data_home_dir 目錄中建立一個名為 ibtmp1 的自動擴充套件的資料檔案，初始大小略微大於 12 MB。

#### 表和索引
表是資料庫中儲存資料的主要物件，使用`CREATE TABLE`語句建立。

```sql
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;
```

其中，ENGINE 用於指定表的儲存型別；如果不指定，MySQL 預設使用 InnoDB 儲存引擎。使用以下命令檢視錶的資訊：

```sql
mysql> SHOW TABLE STATUS LIKE 't%' \G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-02-17 14:32:40
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
```

InnoDB 表按照索引的組織方式儲存資料，被稱為聚簇索引（clustered index）。具體來說，

- 如果指定了 PRIMARY KEY，InnoDB 使用主鍵作為聚簇索引。推薦使用生成之後不會更改、不為空且不重複、經常作為查詢條件的欄位作為主鍵，例如各種編號。如果沒有邏輯上唯一且非空的欄位，可以使用自增欄位 AUTO_INCREMENT 作為主鍵。
 - 如果沒有定義 PRIMARY KEY，MySQL 使用第一個非空且唯一的索引欄位作為 InnoDB 表的聚集索引。
 - 如果沒有定義  PRIMARY KEY 也沒有合適的 UNIQUE，InnoDB 會在內部生成一個 行 ID 欄位，並且建立一個隱藏的聚集索引 GEN_CLUST_INDEX 。InnoDB 為表中的每一行生成一個遞增的 ID 值，並且按照該順序儲存資料。

除了聚簇索引之外的索引被稱為二級索引（secondary indexes）。InnoDB 二級索引中的每個索引記錄都包含了主鍵索引列的值，以及二級索引的欄位。InnoDB 使用主鍵值查詢聚集索引中的資料行。因此，如果主鍵欄位很長，二級索引就需要佔用更多的磁碟空間，查詢的效率就會更低。這也就是為什麼 InnoDB 推薦使用簡單的數字作為主鍵。

> ?關於索引和優化我們將會在 MySQL 模式優化的部分進行介紹。

#### 雙寫緩衝
雙寫緩衝是系統表空間中的一個儲存區域；在 InnoDB 將緩衝池重新整理到資料檔案之前，會先將緩衝頁寫入該區域。如果在寫入資料頁的過程中，出現了作業系統、儲存系統或者 mysqld 程式崩潰，InnoDB 可以利用雙寫緩衝儲存的緩衝頁進行故障恢復。

由於 InnoDB 的資料頁大小往往和作業系統資料頁大小不一致，例如 InnoDB 為 16 KB，作業系統為 4 KB；此時 InnoDB 重新整理一個資料頁，作業系統需要重新整理 4 個資料頁，在系統故障時可能只重新整理了部分資料頁。雙寫緩衝會先把緩衝池的資料寫入共享表空間，然後再重新整理資料頁；如果在這個過程中發生系統崩潰，InnoDB 可以從共享表空間獲取到要重新整理的資料，然後重新執行寫入。

雖然資料需要寫入兩次，雙寫緩衝並不會導致兩倍的 I/O 負載或者操作，因為雙寫緩衝只需要寫入一個連續的資料塊，只有一次 fsync() 系統呼叫。

雙寫緩衝由系統變數 [innodb_doublewrite](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_doublewrite) 控制，預設值為 ON。如果檔案系統或者儲存裝置提供了防止部分寫失效的功能，可以禁用雙寫緩衝。

#### 重做日誌
重做日誌用於故障恢復時修復未完成事務的資料，它位於磁碟中，與記憶體中的日誌緩衝相對應。在正常操作過程中，重做日誌記錄了表中的資料修改資訊。當系統出現異常關閉後，重新啟動時自動利用重做日誌恢復未更新到資料檔案中的修改。

預設情況下，重做日誌物理上由兩個檔案 ib_logfile0 和 ib_logfile1 組成。MySQL 使用迴圈的方式寫入重做日誌檔案。

#### 回滾日誌
回滾日誌由一組回滾日誌記錄組成，這些記錄屬於單個讀寫事務。回滾日誌記錄包含了回滾一個事務對聚集索引記錄的最新修改所需的資訊。另外，如果另一個事務需要檢視原始的資料（一致性讀），將會從回滾日誌記錄中返回未修改前的資料。

回滾日誌儲存在回滾日誌段中，後者包含在回滾段中；回滾段儲存在回滾表空間以及全域性臨時表空間中。

下一篇]我們將會討論 MySQL 中的事務管理與併發控制，歡迎關注❤️、評論?、點贊?！

《MySQL 效能優化》之 InnoDB 儲存引擎

相關文章