MySQL高級10-InnoDB引擎存儲架構

一、邏輯存儲結構

　　表空間(Tablespace)：一個mysql實例，及一個資料庫實例，可以對應多個表空間(ibd文件)，用於存儲記錄，索引等數據。

　　段(Segment)：分為數據段（Leaf node segment）、索引段（Non-leaf node segment）、回滾段（Rollback segment）,InnoDB是索引組織表，數據段就是B+樹的葉子節點(Leaf node segment)，索引段即為B+樹的非葉子節點(Non-leaf node segment)。段用來管理多個Extent(區)。

　　區(Extent)：表空間的單元結構，每個區的大小為1M, 預設情況下，InnoDB存儲引擎頁大小為16k，即一個區中一共有64個連續的頁

　　頁(Page)：頁是InnoDB存儲引擎磁碟管理的最小單元，每個頁大小預設為16K,為了保證頁的連續，InnoDB存儲引擎每次從磁碟申請4-5個區

　　行(Row)：InnoDB存儲引擎數據是按行進行存放的，Trx_id：每次對某條記錄進行改動時，都會把對應的事務id賦值給這個trx_id隱藏列。Roll_pointer：每次對某條記錄進行改動時，都會把舊的版本寫入到undo日誌中，然後這個隱藏列就相當於一個指針，通過它可以找到該記錄修改前的信息

二、整體架構

　　MySQL5.5 版本開始，預設使用 InnoDB 存儲引擎，它擅長事務處理，具有崩潰恢復特性，在日常開發中使用非常廣泛，下麵是 InnoDB 架構圖，左側為記憶體架構，右側為磁碟架構。　

三、記憶體結構

　　記憶體架構中主要分為：Buffer Poll(記憶體緩衝池)、Change Buffer()、LogBuffer()、Adaptive Hash Index()四個區。

　　3.1 Buffer Pool

　　　　緩衝池是主記憶體中的一個區域，裡面可以緩存磁碟上經常操作的真實數據，在執行增刪改查操作時，先操作緩衝池中的數據，若緩衝池沒有數據，則從磁碟載入並緩存，然後再以一定頻率刷新到磁碟，從而減少磁碟IO，加快出來速度。緩衝池以Page頁為單位，底層採用鏈表數據結構管理Page，根據狀態可以將Page分為三種類型。

• • free page：空閑Page，未被使用。
  • clean page：被使用page，數據沒有被修改過。
  • dirty page：臟頁，被使用page，數據被修改過，其中數據與磁碟上的數據產生了不一致。

　　3.2 Change Buffer

　　　　更改緩衝區，針對與非唯一二級索引頁，在執行DML語句時，如果這些數據Page沒有在Buffer Pool中，不會直接操作磁碟，而是將數據變更存在更改緩衝區Change Buffer中，在未來數據被讀取時，再將數據合併恢復到Buffer Pool中，再講合併後的數據刷新到磁碟中。

　　　　Change Buffer的意義：與集聚索引不同，二級索引通常是非唯一的，並且以相對隨機的順序插入二級索引，同樣，刪除和更新都可能會影響索引樹中不相鄰的二級索引頁，如果每一次都操作磁碟，會造成大量的磁碟IO，有了Change Buffer之後，我們可以在緩衝池中進行合併處理，減少磁碟IO

　　3.3 Adaptive Hash index

　　　　自適應hash索引，InnoDB預設是不支持hash索引的，預設支持的是B+樹的索引。因為hash索引不支持範圍查找，僅可以用來做值匹配查找。但是自適應hash索引，用於優化對Buffer Pool數據的查詢。InnoDB存儲引擎會監控對錶上各項索引頁的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引。

　　　　自適應哈希索引，無需人工干預，是系統根據情況自動完成。通過 innodb_adaptive_hash_index 參數可以配置自適應hash索引的開啟和關閉。 

mysql> show variables like "%innodb_adaptive_hash_index%";
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.00 sec)

　　3.4 Log Buffer

　　　　日誌緩衝區，用來保存要寫入到磁碟中的log日誌（redolog，undolog），預設大小為16M, 日誌緩衝區的日誌會定期刷新到磁碟中，如果需要更新、插入或者刪除許多行的事務，增加日誌緩衝區的大小可以節省磁碟i/o

　　　　通過 innodb_log_buffer_size 參數可以查看緩衝區大小

　　　　通過 innodb_flush_log_at_trx_commit 參數可以查看刷新到磁碟時機。這裡有0,1,2三個值：

• • • 0：每秒將日誌寫入並刷新到磁碟一次。
    • 1：日誌在每次事務提交時寫入並刷新到磁碟。
    • 2：日誌在每次事務提交後寫入，並每秒刷新到磁碟一次。

mysql> show variables like "innodb_log_buffer_size";
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

mysql>   show variables like "%flush_log%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

四、磁碟架構　　

　　4.1 System Tablespace　　

　　　　在MySQL中，System Tablespace（系統表空間）是一個用於存儲系統表和一些特殊表的預設表空間。系統表空間包含了以下幾個重要的系統表：

• • mysql.user：存儲了MySQL中的用戶和許可權信息。
  • mysql.db：存儲了所有資料庫的信息。
  • mysql.host：存儲了允許連接到MySQL伺服器的主機信息。
  • mysql.tables_priv：存儲了表級別的許可權信息。
  • mysql.columns_priv：存儲了列級別的許可權信息。

　　　　系統表空間還包含其他系統表，用於存儲MySQL伺服器的配置和元數據信息。
　　　　系統表空間預設存儲在名為`ibdata1`的共用文件中。這個文件通常位於MySQL的數據目錄下。

　　　　要註意的是，在InnoDB存儲引擎中，除了系統表空間（System Tablespace），還存在一個叫做表空間文件（Tablespaces）的概念。表空間文件用於存儲用戶創建的表和索引。每個InnoDB表都會有一個對應的表空間文件。
　　　　需要註意的是，有時候系統表空間的大小會超過預期，導致空間不足或性能問題。在這種情況下，可以考慮調整系統表空間的大小或進行其他優化措施以解決問題。
　　　　總結：系統表空間是MySQL中用於存儲系統表和特殊表的預設表空間，包含了一些重要的系統表，通常存儲在名為`ibdata1`的共用文件中。

　　4.2 File-Per-Table Tablespace

　　　　File-Per-Table Tablespace（每個表一個表空間）是MySQL中的一個存儲配置選項，它允許每個InnoDB表使用單獨的表空間文件來存儲數據和索引。這與預設情況下的系統表空間不同。

　　　　在預設情況下，所有的InnoDB表共用一個系統表空間，即存儲在ibdata1文件中。而使用File-Per-Table Tablespace選項，每個InnoDB表都會有一個獨立的表空間文件，位於數據目錄下。

　　　　File-Per-Table Tablespace的優點包括：

• • 管理：每個表有自己的表空間文件，方便管理和維護。可以更方便地備份、恢復或遷移單個表。
  • 性能：每個表具有獨立的表空間文件，可以在磁碟上更好地分佈和組織數據，提高性能和併發訪問的能力。
  • 存儲空間：使用File-Per-Table Tablespace可以更有效地使用存儲空間。當有大量小表或經常進行刪除和重建表時，每個表的表空間文件可以更好地管理空間，避免系統表空間的不斷增長。

　　　　File-Per-Table Tablespace的缺點和註意事項包括：

• • 管理複雜性：每個表都會有一個獨立的表空間文件，這可能增加了管理的複雜性，包括備份和維護的管理操作。
  • 存儲和文件系統：使用File-Per-Table Tablespace可能涉及更多的磁碟IO操作，並可能增加文件系統的碎片化問題。

　　　　使用File-Per-Table Tablespace可以在創建表時進行配置或在現有表上進行更改。要在創建新表時啟用File-Per-Table Tablespace，可以在創建表的DDL語句中加上ENGINE=InnoDB選項。要在已有表上啟用File-Per-Table Tablespace，可以使用MySQL的ALTER TABLE語句並設置innodb_file_per_table參數為ON。

　　　　總結：File-Per-Table Tablespace是MySQL InnoDB存儲引擎的一個選項，允許每個表使用單獨的表空間文件存儲數據和索引。它提供了更靈活的管理、更好的性能和更高效的存儲空間利用率

　　4.3 General Tablespaces

　　　　General Tablespaces（通用表空間）是MySQL 5.7版本引入的一個功能，在InnoDB存儲引擎中提供了更靈活和更高級的表空間管理選項。 

　　　　通用表空間允許將多個InnoDB表存儲在一個或多個共用表空間文件中，而不是每個表都有自己的獨立表空間文件（如File-Per-Table Tablespace中）。這些共用表空間文件可以在運行時動態添加或刪除新的表。

　　　　通用表空間的優點包括：

• • 簡化管理：使用通用表空間可以更方便地管理和維護多個表。可以將相關的表組織在同一個共用表空間中，便於備份、恢復和遷移。
  • 空間效率：通用表空間可以更有效地使用存儲空間。多個表可以共用一個表空間文件，避免了為每個表都創建單獨的表空間文件的開銷。
  • 高性能：由於多個表共用表空間文件，可以提高磁碟IO操作的效率。此外，共用表空間可以利用預讀機制（預讀相鄰頁）來提高查詢性能。

 　　　　使用通用表空間時，可以在創建表時指定TABLESPACE子句來為表分配到指定的共用表空間，也可以使用ALTER TABLE語句將現有表移動到共用表空間中。

 　　　　創建通用表空間示例：

mysql> create tablespace my_test add datafile "my_test.ibd" engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

 　　　　創建使用通用表空間的表示例：

mysql> create table tablespace_test(id int) tablespace my_test;
Query OK, 0 rows affected (0.03 sec)

　　　　說明1：此時MySQL就不會再給tablespace_test表創建單獨的表空間了，而是使用通用表空間my_test.ibd空間

　　　　將現有表移動到通用表空間語法示例：

ALTER TABLE table_name TABLESPACE tablespace_name;

　　　　說明2：`table_name`是要移動的表的名稱，`tablespace_name`是要移動到的表空間的名稱。　　　　

　　　　刪除通用表空間語法示例

ALTER TABLESPACE tablespace_name DROP DATAFILE '<path>/tablespace_file.ibd';

　　　　說明3：`tablespace_name`是要刪除的表空間的名稱，`<path>/tablespace_file.ibd`是要刪除的表空間文件的路徑和文件名。

　　4.4 undo Tablespace

　　　　在MySQL中，"undo tablespace"（撤銷表空間）是用於存儲撤銷日誌數據的一種特殊類型的表空間。

　　　　撤銷日誌是 MySQL 中的一項重要功能，用於回滾或撤銷事務中所做的更改。當事務執行 UPDATE、DELETE 或 INSERT 操作時，撤銷日誌記錄了被修改或刪除的非聚集索引的舊值，以及 INSERT 操作插入的新記錄。這些撤銷日誌記錄存儲在名為 "undo log" 的數據結構中。

　　　　為了高效地管理和存儲撤銷日誌數據，MySQL引入了 "undo tablespace" 的概念。撤銷表空間是一個獨立於數據表空間的區域，用於存儲撤銷日誌數據。它可以包含一個或多個文件，這些文件具有固定大小（通常是小於等於1GB）和特定的命名約定，預設的是undo_001和undo_002

　　　　撤銷表空間的主要作用有以下幾個方面：

• • 提供事務的回滾能力：如果一個事務需要回滾，MySQL可以使用撤銷表空間中的撤銷日誌來還原事務執行前的數據狀態。
  • 支持併發事務：撤銷表空間使得多個事務可以同時進行，並提供了事務隔離級別的支持。
  • 回收空間：當事務完成時，撤銷表空間中的撤銷日誌可以被清除，空間可以被重覆使用。

 　　　　撤銷表空間在MySQL的配置文件（my.cnf或my.ini）中通過innodb_undo_directory和innodb_undo_tablespaces配置項進行設置。innodb_undo_directory定義了撤銷表空間文件的存儲目錄，而innodb_undo_tablespaces指定了要使用的撤銷表空間文件的數量。

　　　　總結：撤銷表空間是MySQL中用於存儲撤銷日誌數據的表空間，支持事務的回滾、併發事務和空間回收。

　　4.5 Temporary Tablespace

　　　　在MySQL中，臨時表空間（Temporary Tablespace）是用於存儲臨時表數據和臨時結果集的一種特殊類型的表空間。臨時表空間的作用是存儲臨時表的數據，這些臨時表通常是在查詢過程中創建的。這些臨時表可能包括臨時表名、中間結果集或者用於排序和聚合的臨時數據。

　　　　預設情況下，MySQL使用系統表空間（system tablespace）來存儲臨時表數據。但是，在高併發環境下，使用單個系統表空間可能會導致性能瓶頸。為了提高性能並優化系統資源的使用，MySQL引入了臨時表空間的概念。通過為臨時表數據分配獨立的臨時表空間，MySQL可以更好地管理和優化臨時表的創建和使用。臨時表空間可以在獨立的表空間文件中存儲臨時表數據，這些文件可以位於不同的存儲設備上，從而分散了IO負載。

　　　　可以通過以下配置項來設置臨時表空間：既可以寫在MySQL配置文件中也可以在MySQL交互界面上使用set 指令設置

　　　　-`tmp_table_size`：用於設置每個臨時表的記憶體大小。如果臨時表大小超過此值，則會將其存儲到臨時表空間中。

　　　　-`max_heap_table_size`：用於設置只在記憶體中存儲的臨時表的最大大小。

　　　　-`tmpdir`：用於設置臨時表空間的目錄。

　　　　使用臨時表空間可以提升查詢性能，減少對系統表空間的負載，並提供更好的系統擴展性和可維護性。

　　　　總結：臨時表空間是MySQL中用於存儲臨時表數據和臨時結果集的表空間。它可以提高查詢性能，並分散IO負載，提供更好的系統資源利用和擴展性。

　　4.6 Doublewrite Buffer Files

　　4.7 Redo Log

　　　　在MySQL中，Redo Log（重做日誌）是用於實現事務的持久性和恢復能力的關鍵組件之一。它記錄了發生在資料庫中的數據更改操作，以確保在系統崩潰或斷電時，能夠將未完成的事務重新應用到資料庫中，以保持數據的一致性。

　　　　Redo Log是迴圈寫入的，意味著當日誌文件寫滿後，會重新從開頭開始覆蓋之前的日誌。所有的修改操作都會先寫入到Redo Log，然後非同步地刷新到磁碟上的數據文件。這樣即使在寫操作還未刷新到磁碟上的數據文件時發生崩潰，通過Redo Log的回放可以重新執行未完成的事務，確保數據的持久性。

　　　　Redo Log是以邏輯方式記錄的，而不是物理方式。它記錄了事務引起的數據修改，而不是實際的數據變化。通過記錄這些邏輯操作，MySQL可以在恢復時重新執行所需的操作。

　　　　在MySQL中，Redo Log由兩個文件組成，通常為`ib_logfile0`和`ib_logfile1`。這些文件的大小由配置參數`innodb_log_file_size`控制，預設情況下為`48MB`。可以在MySQL配置文件中進行修改。

　　　　使用Redo Log的一個重要註意事項是，寫入Redo Log會引起磁碟IO操作，因此對於事務密集型負載，合理調整Redo Log的大小和I/O性能是很重要的。過小的Redo Log可能導致頻繁的刷新和IO延遲，而過大的Redo Log可能對記憶體和磁碟空間帶來負擔。

　　　　總結：Redo Log是MySQL中用於實現事務的持久性和恢復的關鍵組件，它記錄了數據的修改操作，保證在系統崩潰或斷電後，能夠重新應用未完成的事務。Redo Log由兩個文件組成，通過迴圈寫入的方式記錄數據修改。合理調整Redo Log的大小和I/O性能對於資料庫性能和持久性是重要的。