MySQL底層數據結構詳解

一、B+樹索引

1、B+樹是一種平衡樹，它是一種多路查找樹，每個節點可以存儲多個索引值和相應數據的地址。MySQL使用B+樹作為索引結構，B+樹的優勢在於磁盤I/O瓶頸的優化，它的每個非葉子節點都存儲有下一層葉子節點的地址，可以按照層級進行I/O優化。

2、B+樹在MySQL中的實現採用了InnoDB存儲引擎，它的B+樹索引結構中，在非葉子節點存儲的是索引值，數據都記錄在葉子節點，可以通過葉子節點的鏈表進行順序掃描查詢。葉子節點的數據存儲按照主鍵的順序存儲，所以在進行範圍查詢時，可以通過順序掃描最小值和最大值之間的葉子節點進行查詢。

示例代碼：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

二、聚簇索引

1、聚簇索引是一種特殊的索引，它與數據行存儲在一起，即索引和數據在同一個物理區域內存儲。從而在使用聚簇索引進行主鍵查詢時，可以減少I/O的次數，提高查詢效率，並且避免了非聚簇索引的回表操作。

2、在MySQL中，InnoDB存儲引擎默認採用的就是聚簇索引，所以在進行主鍵查詢時，可以通過B+樹索引直接定位到數據行所在的物理地址，從而避免了回表操作。

示例代碼：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

三、自適應哈希索引

1、自適應哈希索引是一種動態調整的索引結構，它可以根據數據的讀取頻率自動調整哈希索引表的大小，並且可以根據數據的訪問情況進行動態的哈希函數調整。在數據分布不均勻的情況下，自適應哈希索引比B+樹索引具有更好的查詢性能。

2、在MySQL中，自適應哈希索引被用於InnoDB存儲引擎中，用於加速緩存中的數據查詢。它在緩存中可以替代B+樹索引的一部分功能，從而減少B+樹的節點數，提高查詢效率。

示例代碼：

ALTER TABLE `users` ADD PRIMARY KEY USING HASH (id);

四、頁式存儲結構

1、頁式存儲結構是指MySQL的數據存儲結構是按照頁為單位進行管理，每個頁的大小是固定的。在InnoDB存儲引擎中，每個頁的大小默認為16KB，而MyISAM存儲引擎中每個頁的大小默認為1KB。

2、MySQL在存儲記錄時，使用了基於B+樹的頁式存儲結構，通過將數據按照頁的大小劃分成多個塊，分別存儲在不同的頁內，從而提高數據存儲和查詢的效率。同時，MySQL在管理頁的使用中也採用了緩存機制，常用的頁會被緩存到內存中，從而減少I/O次數。

示例代碼：

ALTER TABLE `users` ENGINE=InnoDB;

五、undo日誌和redo日誌

1、undo日誌是在事務執行過程中記錄數據修改前的狀態的日誌文件，在事務回滾時可以用來還原修改前的狀態，以保證數據的一致性。

2、redo日誌是記錄數據修改後的狀態的日誌文件，在MySQL的InnoDB存儲引擎中，redo日誌的作用是在崩潰恢復或者主從複製的過程中，恢複數據的一致性。

示例代碼：

1、開啟undo日誌

SET GLOBAL innodb_undo_logs=4;
SET GLOBAL innodb_undo_tablespaces=2;

2、開啟redo日誌

SET GLOBAL innodb_flush_log_at_trx_commit=1;
SET GLOBAL innodb_file_per_table=1;