mysql之B+ 树索引 (InnoDB 存储引擎)机制

b+树索引机制

- - B+ 树索引 (InnoDB 存储引擎)机制
  - - **引言：**
    - **1. 数据页结构与查找**
    - 2. 索引的引入
    - **3. InnoDB 的 B+ 树索引**
    - **4. InnoDB B+ 树索引的注意事项**
    - **5. MyISAM 的索引方案 (选读，与 InnoDB 做对比)**
    - **6. MySQL 中创建和删除索引的语句**
  - **B+ 树索引的使用**
  - - **1. 索引的代价**
    - **2. B+ 树索引适用的条件**
    - **3. 回表的代价**
    - **4. 覆盖索引（Covering Index）**
    - **5. 如何挑选索引**
    - **6. 索引的类型**
    - **7. 索引的维护**
    - **8. 查询优化器（Query Optimizer）**
    - **9. 索引的限制**
    - **10. 实际应用案例**

B+ 树索引 (InnoDB 存储引擎)机制

引言：

在海量数据中快速定位所需信息，是数据库性能的关键。InnoDB 存储引擎默认使用 B+ 树索引，这是一种为磁盘或其他直接存取辅助设备设计的高效数据结构。理解 B+ 树索引的原理和机制，是掌握 MySQL 性能调优的基石。

1. 数据页结构与查找

InnoDB 将数据划分为若干个页（Page），每个页默认大小为 16KB。数据页是磁盘和内存交互的基本单位。

数据页组成： InnoDB 数据页由 7 个部分组成，包括文件头（File Header）、页头（Page Header）、最大最小记录（Infimum + Supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Trailer）。用户记录按照主键值从小到大顺序排列，形成单向链表。页与页之间通过双向链表连接，形成逻辑上连续的数据存储。
查找方式：
- 单页查找：
  - 主键****查找： InnoDB 在页目录中为每组记录（默认每组 4-8 条）生成一个槽（Slot），槽中存储该组最大记录的地址偏移量。通过二分法快速定位到包含目标记录的槽，然后在槽内遍历记录（因为槽内记录数较少，遍历开销很小）。
  - 非主键查找： 对于没有索引的列，只能从页的第一条记录开始，沿着单向链表逐条遍历，直到找到目标记录或遍历完所有记录。
- 多页查找： 在没有索引的情况下，MySQL 需要扫描所有的数据页，对每一页执行单页查找。这在数据量较大时效率极低，即所谓的“全表扫描”。

2. 索引的引入

为了避免全表扫描，我们需要一种机制来快速定位到目标记录所在的页。索引应运而生。

索引的作用： 索引是一种特殊的数据结构，它以指针的形式“指向”表中的数据，极大地加速了数据检索的速度。可以把索引比作书籍的目录，通过目录我们可以快速找到感兴趣的章节，而无需翻阅整本书。
一个简单的索引方案（作为过渡，帮助理解）：

我们可以为每个数据页建立一个目录项，目录项包含两个关键信息：

页中最小的主键值（key）。
页号（page_no）。

将这些目录项存储在一个数组中，由于数组元素是连续存储的，我们可以利用二分法快速查找。通过比较目标主键值与目录项中的 key 值，就可以迅速确定目标记录所在的页，然后加载该页进行单页查找。

3. InnoDB 的 B+ 树索引

虽然上述简单索引方案能够加速查找，但它存在两个主要问题：

当数据量非常大时，目录项的数量也会变得巨大，即使是二分法查找也可能变得低效。
当发生数据插入、删除和更新时，维护目录项数组的连续性会带来很大的开销（例如，插入一条记录可能需要移动数组中的大量元素）。

为了解决这些问题，InnoDB 采用了 B+ 树索引。

B+ 树结构：
- 叶子节点（Leaf Nodes）： 存储所有的数据记录（或指向数据记录的指针，取决于索引类型），并按照键值（索引列的值）排序。叶子节点之间通过双向链表连接，方便范围查询。
- 非叶子节点（Internal Nodes 或 Index Nodes）： 存储索引键值和指向下一层节点的指针。非叶子节点不存储实际的数据记录，只起到索引的作用，可以看作是目录的目录。
- 多级目录： B+ 树通过多级目录结构实现快速查找。随着数据量的增加，B+ 树的层级也会相应增加，但通常情况下，B+ 树的高度保持在 2-4 层，这意味着即使在海量数据中，查找一条记录也只需要几次磁盘 I/O。
- 示意图：
聚簇索引（Clustered Index）：
- InnoDB 存储引擎会自动为表的主键创建聚簇索引。
- 聚簇索引的叶子节点存储的是完整的用户记录（所有列的数据）。
- 数据存储与索引紧密结合，索引即数据，数据即索引。这意味着通过聚簇索引查找记录可以直接获取到所有列的数据，无需额外的回表操作。
- 一张表只能有一个聚簇索引。
二级索引（Secondary Index 或 Non-Clustered Index）：
- 为非主键列创建的索引。
- 二级索引的叶子节点存储的是索引列的值和主键值。
- 通过二级索引查找记录时，通常需要两步：
  - 在二级索引树中找到对应的叶子节点，获取主键值。
  - 根据主键值回表到聚簇索引中查找完整的记录（这个过程称为回表）。
- 一张表可以有多个二级索引。
联合索引（Composite Index）：
- 为多个列同时建立的索引。
- 联合索引的键值按照创建索引时指定的列顺序排序。例如，创建一个 (col1, col2, col3) 的联合索引，数据会先按照 col1 排序，col1 相同的记录再按照 col2 排序，以此类推。
- 联合索引只建立一棵 B+ 树，而不是为每个列单独建立索引。
- 合理设计联合索引可以提高查询效率，减少存储空间。

4. InnoDB B+ 树索引的注意事项

根页面固定： B+ 树的根节点（Root Node）的页号是固定的，并存储在数据字典中。MySQL 启动时会将根节点加载到内存中，方便快速访问索引。
内节点目录项的唯一性：
- 对于聚簇索引，内节点中的目录项包含主键值和页号。
- 对于二级索引，内节点中的目录项包含索引列的值、主键值和页号。通过包含主键值，确保了二级索引内节点目录项的唯一性。
最少记录数： InnoDB 规定每个数据页（包括叶子节点和非叶子节点）至少存储两条记录（除了根节点）。这是为了避免在数据量较少时出现过多的索引层级，影响查询效率。

5. MyISAM 的索引方案 (选读，与 InnoDB 做对比)

MyISAM 是 MySQL 早期版本中常用的存储引擎，它的索引方案与 InnoDB 有显著不同。

数据文件（.MYD）： MyISAM 将表的数据存储在 .MYD 文件中，数据记录按照插入顺序存储，没有特定的排序。每条记录都有一个行号（Row Number），可以根据行号直接访问记录。
索引文件（.MYI）： MyISAM 将索引信息存储在 .MYI 文件中，索引都是二级索引。
- MyISAM 的 B+ 树索引的叶子节点存储的是索引列的值和行号。
特点：
- 索引与数据分离：MyISAM 的索引和数据是分开存储的。
- 回表操作：通过索引查找记录时，需要根据索引叶子节点中存储的行号到数据文件中定位记录。由于行号是物理地址，这种回表操作通常比 InnoDB 的回表操作更快（因为 InnoDB 的回表是逻辑上的，需要通过聚簇索引树）。
- MyISAM 不支持聚簇索引。

6. MySQL 中创建和删除索引的语句

创建索引：

-- 方法一：在创建表时指定索引
CREATE TABLE table_name (column1 datatype,column2 datatype,...,INDEX index_name (column1, column2, ...)  -- 单列索引或联合索引
);-- 方法二：使用 ALTER TABLE 语句添加索引
ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);
ALTER TABLE table_name ADD UNIQUE index_name (column1, column2, ...); --唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column1); -- 主键，自动创建聚集索引-- 方法三: 使用 CREATE INDEX
CREATE INDEX index_name ON table_name (column_list);

删除索引：

-- 方法一：使用 ALTER TABLE 语句删除索引
ALTER TABLE table_name DROP INDEX index_name;--方法二: 使用 DROP INDEX
DROP INDEX index_name ON table_name;

B+ 树索引的使用

1. 索引的代价

虽然索引可以显著提高查询性能，但它们并非没有代价。

空间代价： 每个索引都对应一棵 B+ 树，会占用额外的存储空间。索引越多，占用的空间越大。
时间代价： 对表进行数据插入、删除和更新操作时，MySQL 需要维护所有相关的索引树。这会增加数据修改操作的时间开销。索引越多，维护开销越大。
- 尤其是对于频繁更新的列，索引的维护成本可能很高。

2. B+ 树索引适用的条件

只有在合适的查询条件下，B+ 树索引才能发挥其优势。

全值匹配（Exact Match）：
- 当查询条件中的列与索引列完全匹配，并且按照索引列的顺序出现时，可以使用索引。
- 例如，对于索引 idx_col1_col2，查询 WHERE col1 = 'a' AND col2 = 'b' 可以使用索引。
- 与索引中列的声明顺序有关，where的条件顺序可以随意，优化器会进行调整
匹配左边的列（Leftmost Prefix Rule）：
- 对于联合索引，查询条件可以只包含索引的左边连续的列。
- 例如，对于索引 idx_col1_col2_col3，以下查询可以使用索引：
  - WHERE col1 = 'a'
  - WHERE col1 = 'a' AND col2 = 'b'
  - WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c'
- 但是，以下查询不能使用索引：
  - WHERE col2 = 'b' （缺少最左边的 col1）
  - WHERE col1 = 'a' AND col3 = 'c' （col2 不连续）
匹配列前缀（Prefix Match）：
- 对于字符串类型的索引列，可以使用 LIKE 操作符进行前缀匹配。
- 例如，对于索引 idx_name，查询 WHERE name LIKE 'abc%' 可以使用索引。
- 但是，WHERE name LIKE '%abc' 或 WHERE name LIKE '%abc%' 不能使用索引（后缀匹配或中间匹配）。
匹配范围值（Range Query）：
- 可以使用索引进行范围查询，但只有最左边的列可以进行范围查询。
- 例如，对于索引 idx_col1_col2，查询 WHERE col1 > 'a' AND col1 < 'z' 可以使用索引。
- 但是，查询 WHERE col1 = 'a' AND col2 > 10 只有 col1 可以使用索引，col2 无法使用索引进行范围过滤。
精确匹配某一列并范围匹配另外一列：
- 这是范围查询的一种特殊情况。如果最左边的列是精确匹配，而后面的列是范围匹配，则索引仍然可以使用。
用于排序（ORDER BY）：
- 如果 ORDER BY 子句中的列与索引列的顺序完全一致，MySQL 可以利用索引直接进行排序，避免额外的排序开销（Using filesort）。
- 例如，对于索引 idx_col1_col2，查询 SELECT * FROM table_name ORDER BY col1, col2 可以使用索引排序。
- 如果 ORDER BY 子句中的列顺序与索引列顺序不同，或者包含非索引列，则无法使用索引排序。
  - ORDER BY 多个列排序要保证顺序和索引列一致（都是ASC或DESC）。
用于分组（GROUP BY）：
- GROUP BY 子句的原理与 ORDER BY 类似。如果 GROUP BY 子句中的列与索引列的顺序完全一致，MySQL 可以利用索引进行分组，提高分组操作的效率。
- 如果联合索引是(a,b),则GROUP BY b无法使用索引。

3. 回表的代价

回表操作： 当使用二级索引进行查询时，如果查询的列不全在二级索引中，MySQL 需要根据二级索引中获取到的主键值，回到聚簇索引中查找完整的记录。这个过程称为回表。
性能影响：
- 回表操作会增加磁盘 I/O 次数。
- 如果二级索引返回的主键值在聚簇索引中是离散分布的，回表操作会导致大量的随机 I/O，性能较差。
- 如果二级索引返回的主键值在聚簇索引中是连续分布的（例如，按照时间范围查询），回表操作可以利用顺序 I/O，性能相对较好。
- 回表的记录数越多，性能越低。

4. 覆盖索引（Covering Index）

定义： 如果一个索引包含了查询所需的所有列，我们就称之为覆盖索引。
优势： 使用覆盖索引可以避免回表操作，直接从索引中获取所需的数据，极大地提高查询性能。
如何使用： 在设计查询语句时，尽量只选择需要的列，并确保这些列都包含在索引中。

5. 如何挑选索引

为表创建合适的索引是数据库优化的关键。以下是一些建议：

只为用于搜索、排序或分组的列创建索引： 不要为查询中不涉及的列创建索引。避免为那些只在 select 列表中，但不在 where、order by 或 group by 子句中使用的列创建索引。
考虑列的基数（Cardinality）： 列的基数是指该列中不同值的数量。基数越高，索引的选择性越好，索引的效果越明显。例如，性别列（男、女）的基数很低，不适合创建索引；而身份证号列的基数很高，非常适合创建索引。
索引列的类型尽量小： 较小的数据类型通常意味着更少的存储空间和更快的比较速度，这可以减少索引占用的空间，提高索引的效率。
索引字符串值的前缀（Prefix Index）： 对于较长的字符串列，可以只对字符串的前几个字符创建索引，而不是对整个字符串创建索引。这可以节省存储空间，并提高索引的效率。
1. 例如：ALTER TABLE table_name ADD INDEX idx_name (name(10)); // 对 name 列的前 10 个字符创建索引
2. 需要仔细权衡前缀的长度，确保前缀的选择性足够好。
让索引列在比较表达式中单独出现： 如果索引列参与了表达式计算或函数调用，MySQL 将无法使用索引。
1. 例如：WHERE col1 * 2 = 10 无法使用索引，而 WHERE col1 = 10 / 2 可以使用索引。
主键插入顺序： 对于 InnoDB 表，建议使用自增列（AUTO_INCREMENT）作为主键。这样可以保证新的数据行被插入到表的末尾，减少页面分裂和碎片，提高插入性能。
避免冗余和重复索引：
1. 冗余索引： 如果已经存在一个联合索引 (col1, col2)，那么再创建一个单列索引 (col1) 就是冗余的，因为联合索引已经可以覆盖单列索引的功能。
2. 重复索引： 不要对同一列创建多个类型相同的索引。

6. 索引的类型

除了 B+ 树索引外，MySQL 还支持其他类型的索引：

哈希索引（Hash Index）：
- 基于哈希表实现，只适用于等值查询（ =，IN），不支持范围查询。
- 哈希索引的查找速度非常快（通常为 O(1)），但不支持排序。
- InnoDB 引擎不支持手动创建哈希索引，它有一个“自适应哈希索引”（Adaptive Hash Index）的功能，会根据查询模式自动创建和管理哈希索引。
- Memory 存储引擎支持显式创建哈希索引。
全文索引（Full-Text Index）：
- 用于全文搜索，可以对文本内容进行分词和匹配，支持模糊查询。
- 适用于 MATCH AGAINST 语法。
- MyISAM 和 InnoDB（5.6 版本及以后）都支持全文索引。
空间索引 (Spatial Index):
- 用于对地理空间数据类型 (如 POINT, LINESTRING, POLYGON) 进行索引,支持空间查询。
- 使用SPATIAL关键字。
- MyISAM 和 InnoDB 都支持。

7. 索引的维护

索引重建： 随着数据的不断插入、删除和更新，索引可能会产生碎片，导致性能下降。定期重建索引可以优化索引结构，提高查询性能。
- 可以使用 ALTER TABLE table_name ENGINE=InnoDB; 或 OPTIMIZE TABLE table_name; 来重建索引。
索引优化： 根据查询需求和数据分布，定期评估和调整索引列、索引类型以及索引的顺序。
索引监控：
- 使用 MySQL 的性能模式（Performance Schema）或慢查询日志（Slow Query Log）监控索引的使用情况。
- 分析查询的执行计划（EXPLAIN 语句），查看是否使用了正确的索引。

8. 查询优化器（Query Optimizer）

查询优化器的作用： MySQL 的查询优化器负责分析 SQL 查询语句，并选择最优的执行计划。这包括选择合适的索引、决定表的连接顺序、选择合适的访问方法等。
影响因素： 查询优化器的决策受到多种因素的影响，包括表结构、索引、数据分布、查询条件、统计信息等。
优化建议：
- 使用 EXPLAIN 语句查看查询的执行计划，了解 MySQL 如何执行查询。
- 根据执行计划的结果，调整查询语句、修改索引或更新统计信息，以帮助优化器选择更优的执行计划。
- 使用查询提示（Query Hints）可以干预优化器的决策（例如，FORCE INDEX 强制使用某个索引）。

9. 索引的限制

索引数量： 虽然可以为一个表创建多个索引，但过多的索引会增加存储空间和维护成本，影响数据修改操作的性能。建议根据实际需求创建索引，避免创建不必要的索引。
索引长度： 索引列的长度越长，索引占用的空间越大，索引的效率也可能降低。对于较长的字符串列，可以考虑使用前缀索引。
索引列的选择： 选择合适的列作为索引列非常重要。一般来说，选择性高（基数大）、经常用于查询条件、排序或分组的列更适合创建索引。避免在频繁更新的列上创建索引。

10. 实际应用案例

电商系统：
- 商品表：为商品名称、商品分类、商品 ID、价格等列创建索引，以提高商品搜索、筛选和排序的性能。
- 订单表：为订单 ID、用户 ID、下单时间等列创建索引，以提高订单查询和统计的性能。
日志系统：
- 日志表：为时间戳、日志级别、错误信息等列创建索引，以提高日志查询、分析和过滤的性能。
社交网络：
- 用户表：为用户 ID、用户名、注册时间等列创建索引，以提高用户查找和登录的性能。
- 关系表：为用户 ID、好友 ID 创建联合索引，以提高好友关系查询的性能。
- 动态表：为用户 ID、发布时间、内容等列创建索引，以提高动态内容检索和推荐的性能。