MySQL存储引擎内部之：`InnoDB`的`B-Tree`索引：其在`聚簇索引`和`二级索引`中的物理存储。

MySQL InnoDB存储引擎：B-Tree索引的物理存储与聚簇/二级索引差异

大家好，今天我们来深入探讨MySQL InnoDB存储引擎中B-Tree索引的物理存储，重点关注它在聚簇索引和二级索引中的差异。理解这一点对于优化查询性能至关重要。

一、B-Tree索引基础回顾

B-Tree（Balanced Tree）是一种自平衡的树数据结构，广泛应用于数据库索引。InnoDB的B-Tree索引具有以下关键特性：

• 平衡性： 所有叶子节点都在同一层级，保证查询效率稳定。
• 多路搜索： 每个节点可以包含多个子节点（扇出性），减少了树的高度，从而减少了磁盘I/O操作。
• 有序性： 节点内的键是有序排列的，方便进行范围查询。

在InnoDB中，B-Tree的每个节点对应磁盘上的一个页（通常为16KB），这决定了索引的物理存储特性。

二、聚簇索引（Clustered Index）

聚簇索引是一种特殊的索引，它决定了表中数据的物理存储顺序。在InnoDB中，如果表定义了主键，则InnoDB会使用主键作为聚簇索引。如果没有定义主键，InnoDB会选择一个非空的唯一索引作为聚簇索引。如果两者都没有，InnoDB会隐式地创建一个隐藏的聚簇索引。

2.1 聚簇索引的物理存储

聚簇索引的叶子节点存储了完整的行数据。这意味着表中的数据实际上是按照聚簇索引的键值顺序存储在磁盘上的。

可以用一个简单的表格来展示聚簇索引的结构：

索引类型	叶子节点存储内容	决定数据存储顺序
聚簇索引	完整行数据	是

假设我们有一个 users 表，主键为 id，表结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    age INT
);

INSERT INTO users (id, name, email, age) VALUES
(1, 'Alice', '[email protected]', 30),
(2, 'Bob', '[email protected]', 25),
(3, 'Charlie', '[email protected]', 35),
(4, 'David', '[email protected]', 28);

在物理存储上，users 表的数据会按照 id 的顺序排列。聚簇索引的B-Tree结构可能如下所示（简化示意）：

• 根节点: 指向包含 id 值为 1 和 3 的中间节点。
• 中间节点 (包含 1 和 3):
  • 指向叶子节点，包含 id 为 1 和 2 的数据行。
  • 指向叶子节点，包含 id 为 3 和 4 的数据行。
• 叶子节点 (包含 1 和 2): 存储 id 为 1 的完整行数据和 id 为 2 的完整行数据。
• 叶子节点 (包含 3 和 4): 存储 id 为 3 的完整行数据和 id 为 4 的完整行数据。

2.2 代码示例：验证聚簇索引的存在

虽然我们无法直接观察InnoDB的物理存储细节，但可以通过查询来验证聚簇索引的存在以及它对数据存储的影响。例如，我们可以按照非主键字段进行排序，然后观察查询执行计划。如果查询使用了聚簇索引，那么通常情况下查询效率会更高。

-- 查询所有用户，按照年龄排序
EXPLAIN SELECT * FROM users ORDER BY age;

查看 EXPLAIN 输出的 Extra 列，如果包含 Using index 并且 key 列显示使用了主键索引 (即聚簇索引)，则说明MySQL尝试利用聚簇索引的物理顺序来优化排序操作。但这并不意味着总是使用聚簇索引进行排序，MySQL会根据成本选择最佳方案。

2.3 聚簇索引的优势与劣势

• 优势：

  • 数据访问速度快： 由于数据和索引存储在一起，通过聚簇索引可以直接获取到完整的行数据，避免了回表操作。
  • 范围查询性能好： 由于数据按照索引顺序存储，范围查询可以顺序读取磁盘，减少了随机I/O。

• 劣势：

  • 插入速度慢： 插入数据时，需要维护数据的物理顺序，可能导致页分裂和重组，影响插入性能。
  • 更新代价高： 如果更新了聚簇索引的键值，可能需要移动整行数据，代价较高。
  • 二级索引需要回表： 二级索引只存储索引列的值和聚簇索引的键值，如果需要查询其他列，需要回表查询。

三、二级索引（Secondary Index）

二级索引（也称为非聚簇索引或辅助索引）是建立在表上的其他列上的索引。一个表可以有多个二级索引。

3.1 二级索引的物理存储

二级索引的叶子节点存储的是索引列的值和聚簇索引的键值。这意味着，通过二级索引只能找到对应行的聚簇索引键值，如果需要查询其他列的数据，还需要根据聚簇索引键值进行回表操作。

索引类型	叶子节点存储内容	决定数据存储顺序	是否回表
聚簇索引	完整行数据	是	否
二级索引	索引列的值 + 聚簇索引键值	否	是

假设我们在 users 表的 name 列上创建一个二级索引：

CREATE INDEX idx_name ON users (name);

这个二级索引的B-Tree结构可能如下所示（简化示意）：

• 根节点: 指向包含 ‘Alice’ 和 ‘Charlie’ 的中间节点。
• 中间节点 (包含 ‘Alice’ 和 ‘Charlie’):
  • 指向叶子节点，包含 ‘Alice’ 和 ‘Bob’ 的索引条目。
  • 指向叶子节点，包含 ‘Charlie’ 和 ‘David’ 的索引条目。
• 叶子节点 (包含 ‘Alice’ 和 ‘Bob’):
  • 存储 name 为 ‘Alice’，id 为 1 的索引条目。
  • 存储 name 为 ‘Bob’，id 为 2 的索引条目。
• 叶子节点 (包含 ‘Charlie’ 和 ‘David’):
  • 存储 name 为 ‘Charlie’，id 为 3 的索引条目。
  • 存储 name 为 ‘David’，id 为 4 的索引条目。

注意，这里的叶子节点存储的是 name 的值和对应的 id 值（聚簇索引键值）。

3.2 代码示例：演示二级索引的回表操作

-- 查询 name 为 'Alice' 的用户的年龄
EXPLAIN SELECT age FROM users WHERE name = 'Alice';

查看 EXPLAIN 输出。如果 type 列显示 ref (表示使用了索引)，key 列显示 idx_name，但 Extra 列显示 Using index condition; Using where; Using MRR，则表明 MySQL 使用了二级索引 idx_name，并且进行了回表操作才能获取 age 的值。

如果查询只需要 name 和 id 列，则可以避免回表，此时 Extra 列可能显示 Using index，表示使用了覆盖索引（Covering Index）。

-- 查询 name 为 'Alice' 的用户的 id 和 name
EXPLAIN SELECT id, name FROM users WHERE name = 'Alice';

3.3 二级索引的优势与劣势

• 优势：

  • 查询速度快（特定场景）： 在只需要索引列和聚簇索引键值的情况下，可以避免回表，提高查询速度。
  • 可以建立多个索引： 可以根据不同的查询需求建立多个二级索引，提高查询的灵活性。

• 劣势：

  • 需要额外的存储空间： 二级索引需要存储索引列的值和聚簇索引键值，占用额外的存储空间。
  • 需要维护索引： 插入、更新、删除数据时，需要维护二级索引，增加操作的开销。
  • 回表操作： 如果需要查询二级索引没有包含的列，需要回表查询，增加I/O操作。

四、InnoDB的B-Tree索引优化策略

了解了InnoDB的B-Tree索引的物理存储和聚簇/二级索引的差异后，我们可以采取一些策略来优化索引的使用：

1. 选择合适的主键： 选择一个短小、自增的列作为主键，可以减少索引的大小，提高插入性能，并减少页分裂。
2. 避免过长的索引： 索引越长，占用的存储空间越大，查询性能越低。
3. 合理创建二级索引： 只为经常用于查询的列创建索引，避免创建过多的索引，增加维护成本。
4. 利用覆盖索引： 尽量让查询只需要访问索引，避免回表操作。
5. 定期维护索引： 定期使用 OPTIMIZE TABLE 命令重建表，可以整理碎片，提高索引的效率。
6. 联合索引的使用: 对于多列查询，可以创建联合索引。联合索引的顺序很重要，应该将选择性高的列放在前面。

五、代码案例：通过索引优化查询性能

假设我们有一个 orders 表，包含以下字段：

• order_id (INT, PRIMARY KEY)
• customer_id (INT)
• order_date (DATE)
• total_amount (DECIMAL)

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 插入一些示例数据
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 101, '2023-01-01', 100.00),
(2, 102, '2023-01-05', 200.00),
(3, 101, '2023-01-10', 150.00),
(4, 103, '2023-01-15', 250.00),
(5, 102, '2023-01-20', 300.00);

现在，我们有一个查询，需要查询某个客户在特定日期范围内的订单：

-- 查询 customer_id 为 101，order_date 在 '2023-01-01' 到 '2023-01-10' 之间的订单
SELECT * FROM orders WHERE customer_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-10';

如果没有索引，这个查询会进行全表扫描。为了优化查询性能，我们可以创建一个联合索引：

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

这个联合索引会先按照 customer_id 排序，然后在每个 customer_id 内部按照 order_date 排序。这样，查询就可以利用索引快速定位到符合条件的订单，避免全表扫描。

EXPLAIN SELECT * FROM orders WHERE customer_id = 101 AND order_date BETWEEN '2023-01-01' AND '2023-01-10';

查看 EXPLAIN 输出，确认查询使用了 idx_customer_date 索引。

六、深入理解InnoDB的页分裂

InnoDB的B-Tree索引是基于页的结构，每个节点对应一个磁盘页。当插入数据导致某个页的数据量超过上限时，会发生页分裂。页分裂会将一个页分成两个页，并将数据重新分配到这两个页中。

页分裂会带来以下问题：

• 性能下降： 页分裂需要进行磁盘I/O操作，会降低插入性能。
• 空间浪费： 页分裂会导致页的利用率降低，浪费存储空间。

为了减少页分裂，可以采取以下措施：

• 使用自增主键： 自增主键可以保证插入的数据始终在索引的末尾，减少页分裂的概率。
• 预留空间： 可以通过调整 innodb_fill_factor 参数来预留一定的空间，减少页分裂的频率。

七、其他需要注意的点

• Cardinality： Cardinality 是指索引列中唯一值的数量。Cardinality 越高，索引的选择性越好，查询性能越高。可以使用 ANALYZE TABLE 命令更新索引的统计信息，以便优化器选择最佳的执行计划。
• Index Merge： MySQL 5.0 及更高版本支持 Index Merge，可以在某些情况下合并多个索引的结果。但 Index Merge 通常不如单列索引或联合索引效率高，应该尽量避免使用。

记住的关键点

InnoDB的B-Tree索引在聚簇索引和二级索引中的物理存储方式不同，聚簇索引存储完整的行数据，二级索引存储索引列的值和聚簇索引键值。了解这些差异对于优化查询性能至关重要。合理选择主键、创建索引、利用覆盖索引和避免回表操作是提高查询效率的关键。