Mysql大师之路：索引结构详解

最新推荐文章于 2024-10-02 06:14:43 发布

饭叮叮的学习日记

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量541

点赞数 25

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_55839103/article/details/141705794

版权

InnoDB 引擎的索引数据结构

InnoDB 存储引擎主要采用 B+树 作为索引的数据结构。此外，它也支持 FULLTEXT（全文索引），但在多数情况下，B+树索引更为常用。以下是关于 InnoDB 中 B+树索引的详细描述：

B+树索引

数据组织形式:
- B+树结构: 在 InnoDB 中，B+树是一种平衡树结构，其非叶子节点（内部节点）只存储索引键值和指向子节点的指针，而不存储实际的数据行。这些非叶子节点用于快速定位数据的位置。
- 聚簇索引（Clustered Index）: InnoDB 的主键索引是一个聚簇索引。聚簇索引的叶子节点不仅包含索引键值，还存储了实际的行数据。这意味着通过主键查找数据时，叶子节点直接返回实际的行数据，而无需进行额外的查找操作。
- 二级索引（Secondary Index）: 二级索引的叶子节点存储的是索引键值和相应行的主键值。查找数据时需要先通过二级索引找到对应的主键值，再通过聚簇索引找到实际的数据行。
叶子节点链表:
- 双向链表结构: B+树的叶子节点通过指针相连，形成一个双向链表。这样的设计允许对数据进行快速的顺序扫描和范围查询。比如，进行 BETWEEN 操作时，只需从链表的起点节点开始，顺序遍历链表即可。
- 顺序访问效率高: 由于叶子节点按顺序连接，支持按序和按范围查找数据。这种结构在需要排序的数据访问模式下效率更高。
平衡树结构:
- 树的高度平衡: B+树是一种高度平衡的树结构，所有叶子节点在树的同一层上。这意味着无论是查找、插入、删除还是更新操作，任何数据记录的路径长度（从根节点到叶子节点的距离）都是相同的，通常树的高度较小（例如，高度为 2 或 3）。
- 稳定性和性能: 因为树的高度平衡，查找、插入、删除、更新的时间复杂度较低，通常为 O(log⁡N)O(\log N)O(logN)。这使得 InnoDB 能够高效地处理大量数据，并保持一致的性能。

FULLTEXT（全文索引）

全文索引（FULLTEXT Index） 是 InnoDB 和 MyISAM 存储引擎中支持的一种特殊索引类型，专门用于全文搜索，主要用于查找文本中的关键词。

1. 什么是全文索引？

全文索引 是一种对文本数据进行索引的方法，用于加速复杂的文本搜索操作，例如在大量文本中查找包含特定词语或短语的文档。
与普通索引不同，全文索引并不适用于精确匹配查询，而是针对自然语言处理的需求而设计的。

2. 全文索引的特点

适用场景:
- 主要用于处理大量文本数据的表，比如博客文章、产品描述、用户评论等字段。
- 适用于文本中词语的查找、匹配、以及查询中包含的词语出现的频率和位置等复杂查询。
数据类型:
- 全文索引一般应用于 CHAR、VARCHAR 或 TEXT 类型的字段。
存储引擎支持:
- InnoDB 和 MyISAM 引擎都支持全文索引，但它们的实现机制有一些差异。自 MySQL 5.6 起，InnoDB 开始支持全文索引。
- MyISAM 是 MySQL 中第一个支持全文索引的存储引擎。

InnoDB 引擎的 B+ 树索引结构

InnoDB 引擎采用 B+树 作为索引的数据结构，B+树是数据库系统中常用的一种平衡树结构，特别适合磁盘存储和高效的数据库查询操作。以下是 B+树索引的两个主要特点：

B+树的两个主要特点

叶子节点存储索引和数据:
- 在 B+树中，只有叶子节点存储了实际的索引键值和行数据。中间节点（非叶子节点）不存储实际数据，它们仅存储索引键值以及指向子节点的指针。这种设计使得树的中间节点可以容纳更多的索引键，减少了树的高度，从而提高了查询性能。
- 例如，在 InnoDB 中，主键索引是一个聚簇索引，叶子节点存储了主键值和对应的整行数据，非叶子节点则仅存储主键值和指向下一级节点的指针。这种结构使得通过主键进行的查询和数据检索非常高效。
叶子节点之间使用双向链表连接:
- B+树的所有叶子节点通过双向指针串联在一起，形成一个双向链表。这种结构允许在叶子节点之间快速进行顺序扫描和范围查询，支持更高效的区间查询操作（如 BETWEEN）。
- 这种设计的优点在于，使用范围查询时，只需找到查询范围的起点，然后沿着链表遍历相应的范围即可，无需重复从根节点进行树形查找，极大地提升了范围查询的性能。

聚簇索引和非聚簇索引的区别

聚簇索引（Clustered Index） 和 非聚簇索引（Non-Clustered Index）（也称为二级索引）在数据库系统中是两种主要的索引类型，它们的区别主要体现在 B+树叶子节点存储内容的不同。以下是它们的详细区别：

1. 聚簇索引（Clustered Index）

叶子节点存储: 聚簇索引的 B+树的叶子节点存放的是主键值和完整的记录数据。这意味着聚簇索引的叶子节点本身就是数据行，因此使用聚簇索引可以直接从索引中获取到完整的数据行。
数据存储: 数据库表的物理存储顺序与索引顺序相同，因此一个表只能有一个聚簇索引，通常聚簇索引是基于主键创建的。
优势: 当根据主键进行查询时，可以直接从叶子节点读取数据，而不需要额外的 I/O 操作。

2. 非聚簇索引（Non-Clustered Index, Secondary Index）

叶子节点存储: 非聚簇索引的 B+树的叶子节点存放的是索引键值和对应的主键值。它的叶子节点不包含实际数据行，而是指向数据行的指针（在 InnoDB 中，这个指针是主键值）。
数据存储: 非聚簇索引存储在一个独立于数据的 B+树中，数据行和索引行的存储是分离的。一个表可以有多个非聚簇索引，每个索引的 B+树都独立于其他索引。
优势与劣势: 非聚簇索引在根据索引键值查找数据时效率较高，但如果查询的目标数据不在索引中，而在数据行中，则需要通过索引找到主键值，然后再通过主键值回到聚簇索引查找数据行，这个过程被称为回表。回表操作增加了额外的 I/O 开销。

3. 覆盖索引（Covering Index）

定义: 当一个查询可以完全从索引中获取所需数据，而不需要回表获取数据行时，使用的索引称为覆盖索引。
工作原理: 对于非聚簇索引，如果查询的列能够在索引的叶子节点中全部找到（比如查找的字段是索引键或主键），则不需要再去聚簇索引中查找数据行。

B+ 树结构中插入操作的影响

在 MySQL 的 InnoDB 存储引擎中，B+树 是主要的索引数据结构。当进行 INSERT 操作时，B+树结构可能会发生以下变化，这些变化会影响数据库性能，特别是当主键值是随机分布而非顺序递增时。

1. 页分裂问题

页分裂（Page Split） 是在 B+树中插入数据时可能发生的一种现象。当一个数据页（B+树的叶子节点）已满，新的数据需要插入到该页的中间位置时，页分裂就会发生。
如何发生:
- 如果数据页的空间已满，且插入的记录需要插入到当前页的某个位置（例如在 1, 3, 5, 9 之间插入 7），当前页无法容纳新数据时，数据库会将该页分裂成两个页：
  - 原始页将部分数据保留，部分数据移动到新的页中。
  - 数据页的分裂会涉及大量的内存操作，包括重新分配页、复制数据等。
影响:
- 页分裂会导致性能下降，因为它引起了额外的 I/O 操作和内存拷贝。
- 页分裂还会导致页内碎片的产生，索引结构变得不紧凑，从而进一步影响查询效率。

2. 主键的顺序插入 vs. 随机插入

顺序插入（自增主键）:
- 如果插入操作是按照顺序递增的主键（例如自增 ID），每次新数据的插入都会追加到当前最大数据页的末尾。这种情况下，页分裂的发生几率很低。
- 索引的维护代价较低，因为不需要重新平衡树结构，也不会频繁发生页分裂。
随机插入（非自增主键，例如 UUID）:
- 使用随机主键值（如 UUID）时，插入操作的目标页可能是 B+树的任何位置。这种情况下，每次插入都可能需要在 B+树的中间节点进行操作，导致页分裂更频繁。
- 页分裂的频繁发生会造成内存碎片，索引的查找路径变长，查询效率下降。

为什么数据库索引使用 B+ 树，而不是 B 树、哈希表或红黑树？

数据库系统（如 MySQL）通常使用 B+树 作为索引结构，而不是 B 树、哈希表、红黑树等其他数据结构，这是因为 B+树在处理大量数据的磁盘 I/O 操作时有着显著的优势。以下是使用 B+树的原因及其他数据结构的缺点：

1. B+ 树的优势

磁盘 I/O 友好:
- 顺序存储和读写优化: B+树的所有叶子节点形成一个有序的链表，这种顺序存储的方式非常适合磁盘读取。因为硬盘的顺序读取速度远远大于随机读取速度，当进行范围查询或顺序扫描时，B+树可以充分利用磁盘的顺序读取特性，大大提高查询效率。
- 减少磁盘 I/O: B+树的内部节点不存储实际数据，只存储键和子节点指针，因此每个节点可以存储更多的索引键。相较于 B 树，B+树的高度更低，从而减少了磁盘 I/O 次数。通常，B+树的高度为 3-4 层，这意味着在最坏的情况下，仅需 3-4 次磁盘访问即可检索到数据。
范围查询和区间查找高效:
- 叶子节点链表: B+树的叶子节点通过双向链表连接，支持快速的顺序访问和范围查询。对于需要返回多个结果的数据查询（例如 BETWEEN 查询），只需沿着链表依次遍历，不必重新从根节点进行多次查找。
- 高效区间操作: 对于区间查询，B+树可以直接从叶子节点链表的起始位置开始，顺序扫描到结束位置，而不需要重新从树的根节点进行查找。
稳定的查询性能:
- 层次平衡: B+树是一种平衡树，所有叶子节点位于同一层。无论查找哪条记录，路径长度（从根节点到叶子节点的距离）都是相同的，因此查找操作具有稳定的性能。
- 批量插入和删除效率高: 由于 B+树的节点通常为满的插入和删除操作较少触发重平衡（即树的分裂或合并），并且由于它们的节点存储了更多数据，I/O 次数也减少。

2. 为什么不使用 B 树？

中间节点存储数据: B 树的中间节点不仅存储索引键，还存储实际数据。当进行顺序扫描时，每个中间节点的数据都必须被访问，导致每次读取的磁盘块中包含不连续的数据块。这种设计增加了磁盘 I/O，特别是范围查询效率低。
占用更多内存: 由于每个节点要存储实际数据，B 树的每个节点能容纳的索引数量更少，因此树的高度通常更高，增加了访问路径长度和磁盘访问次数。

3. 为什么不使用哈希表？

不支持范围查询: 哈希表的结构使得它无法高效支持范围查询，因为哈希表只能通过精确的键来进行查找，无法像 B+树那样进行顺序访问。
数据无序存储: 哈希表中的数据是无序的，无法实现顺序访问和排序操作。这在需要进行有序数据访问或区间查询的场景中表现不佳。
哈希冲突和存储效率: 哈希表需要处理哈希冲突，且数据分布不均匀可能导致空间浪费和性能下降。扩展哈希表大小也涉及到重新哈希和搬移数据，这在处理大数据集时非常昂贵。