MySQL基础架构(二) MySQL索引简介

目录

1 MySQL索引简介

1.1 什么是数据库索引？

1.2 MySQL 为什么需要索引？

2. B+树：MySQL的主要索引选择

2.1 B树

2.2. B+树

3 B+树在MySQL中是如何使用的

3.1 聚簇索引

3.2 非聚簇索引（二级索引、普通索引）

3.3 联合索引

1 MySQL索引简介

1.1 什么是数据库索引？

数据库索引是一个持久化的数据结构，它减少了查询所需的I/O操作，提高了数据检索速度。可以把它想象为一本书的目录：没有索引，你就必须一页一页地浏览书籍来找到您需要的信息；有了目录，你可以快速找到所需信息的位置。

1.2 MySQL 为什么需要索引？

上节提到了数据页的结构，我们知道数据页内数据是按主键顺序排列，并且分成了多个组，每个组的最大值形成了页目录。保证了在数据页内的快速检索。

但在这种结构下，还有两个问题需要解决：

1. 一个数据页内只有主键有页目录，如果用非主键查询怎么办？
2. 一个表有很多数据页，如何从大量数据页中定位到所需的数据页？

MySQL 索引正是为了解决上面说的两个问题。

2. B+树：MySQL的主要索引选择

2.1 B树

B+树是B树的一个变种，我们介绍B+树的时候顺便介绍以下B树。

首先看B树的一个示例：

B树（Balanced Tree）是一个自平衡的多路搜索树，它的结构和存放顺序确保了数据的高效检索、插入和删除。B树中数据的存放顺序基于以下几点：

1. 键值排序：在B树的每个节点中，键值是按升序排列的。例如，对于一个节点，如果它包含三个键值：K1, K2, K3，K4,那么这些键值满足 K1 < K2 < K3 < K4。
2. 子节点指针：每个节点中的键值将子节点指针分隔开，每一个子节点指针指向的子树中的所有键值都在相邻的键值之间。例如，对于上面的节点，有以下四个子节点指针：P0, P1, P2, P3，P4，它们的键值范围满足：

• P0指向的子树的所有键值 < K1
• P1指向的子树的所有键值在 K1 和 K2 之间
• P2指向的子树的所有键值在 K2 和 K3 之间
• P3指向的子树的所有键值在 K3 和 K4 之间
• P4指向的子树的所有键值 >= K4

1. 平衡性：B树的所有叶子节点具有相同的深度，这确保了从树的根到任何叶子节点的路径长度相同，从而实现了数据访问的平衡性。
2. 节点填充：B树具有特定的填充因子，通常表示为节点包含的最小和最大键数。这意味着除了根节点外，每个节点必须有至少一定数量的键（通常是节点最大键数的一半）。这个特性确保树不会太“稀疏”。
3. 分裂与合并：当插入一个键导致节点的键数量超过最大值时，该节点会分裂为两个节点。相反，当删除一个键导致节点的键数量少于最小值时，该节点可能会与相邻的兄弟节点合并或从其兄弟节点“借”一个键。

2.2. B+树

B+树是B树的一个变种，大致结构和B树相同但具有一些特殊的特点：

• 所有键值都出现在叶子节点：与B树不同的是，B+树的所有键值都位于叶子节点。内部节点只包含关键字和指向其子节点的指针。
• 叶子节点的连续性：B+树的叶子节点是连续的，它们通过指针相互连接。这种结构使范围查询变得非常高效，因为可以简单地遍历叶子节点来获取一系列的值。

示例如下：

下面对B树和B+树做一下简单对比：

1. 数据访问：B树的精准查询速度可能更快，B+树的数据访问可能更均匀。因为所有的数据查询都至少要到叶子节点，而B树在内部节点可能就已经结束。
2. 范围查询：B+树更适合范围查询，因为它的叶子节点是相互连接的，可以快速地遍历整个数据范围。
3. 高度：对于存储同样数量的键，B+树通常比B树低，因为数据值只存储在叶子节点，这允许B+树有更多的分支因子。
4. 稳定性：B+树可能更稳定，因为所有的数据都在叶子节点，并且当数据增加或减少时，树的结构变动较小。

3 B+树在MySQL中是如何使用的

3.1 聚簇索引

当新建一张表时，MySQL InnoDB存储引擎会帮我们自动创建一个索引，这个索引成为聚簇索引。这个索引以主键组织，包含了所有的表数据，这也就是MySQL所谓的"数据即索引"。聚簇索引可以加速通过主键的检索。

一个聚簇索引的例子：

聚簇索引有以下特点：

1. 数据的物理存储：在InnoDB中，聚簇索引实际上决定了表中数据行的物理存储顺序。这意味着表的数据是按照聚簇索引的键值顺序存储的。
2. 主键：在InnoDB中，每张表只能有一个聚簇索引。默认情况下，聚簇索引是表的主键（PRIMARY KEY）。因此，选择一个好的主键对于数据的查询和存储性能至关重要。
3. 非主键的情况：如果表中没有定义主键，MySQL会尝试选择一个合适的唯一索引来作为聚簇索引。如果这都不可行，MySQL会为每一行生成一个6字节的隐式聚簇索引。
4. 与非聚簇索引的关系：InnoDB的非聚簇索引（Secondary Index）项包含了相应聚簇索引项的值。这意味着在通过非聚簇索引进行查找时，一旦找到了索引项，MySQL会使用该索引项中的聚簇索引值来查找实际的数据行。
5. 性能：由于数据是按照聚簇索引的顺序存储的，范围查询和按顺序访问数据时，聚簇索引通常能提供更好的性能。但同时，插入数据时，为了保持数据的物理存储顺序，可能需要进行数据移动，这可能导致插入操作相对较慢。

3.2 非聚簇索引（二级索引、普通索引）

非聚簇索引是用户手动创建的，基于指定的列组织的索引。该索引存放指定的列与主键值，可以加速指定列的查询速度。

一个非聚簇索引的示例图如下：

具体特点如下：

1. 独立的数据结构：非聚簇索引是独立于数据的索引结构。它保存了索引列的值及对应的聚簇索引键值（通常是主键值）。
2. 查找方式：当通过非聚簇索引查询数据时，查询过程通常是两阶段的：首先，根据非聚簇索引查找到对应的聚簇索引键值；然后，使用这个键值在聚簇索引中查找实际的数据行。上述过程中由聚簇索引查找实际数据行的过程称为回表。
3. 存储：非聚簇索引并不改变或决定数据的物理存储顺序。它只是一个对表数据的引用。
4. 数量：与聚簇索引不同，一张表可以有多个非聚簇索引。
5. 性能影响：使用非聚簇索引查询数据可能会有额外的性能开销，因为可能涉及到两次查找：一次在非聚簇索引中，一次在聚簇索引中。但在某些情况下，如果查询只需要索引中的数据，则可以避免额外的回表查找，此种情况称为覆盖索引。

3.3 联合索引

联合索引是由两个或多个列组成的索引，用来加速使用多列的查询。

一个联合索引的结构图如下：

它允许数据库管理系统基于多个列的组合来加速查询。联合索引的关键点和用途如下：

1. 索引列的顺序：在联合索引中，列的顺序很重要。对于索引(col1, col2, col3)，你可以有效地查询col1，或者col1与col2的组合，或者col1、col2和col3的组合。但如果只查询col2或col3，这个联合索引可能不会被使用。
2. 最左前缀原则：在使用联合索引时，必须遵循最左前缀原则。这意味着查询条件必须使用索引的最左边的列。例如，对于索引(col1, col2, col3)，你可以基于col1或col1和col2进行查询，但如果没有使用col1，那么此索引可能不会被考虑。
3. 性能考虑：联合索引可以减少所需的磁盘I/O，并帮助数据库更快地检索数据，尤其是当查询条件包含索引中的多个列时。
4. 存储开销：虽然联合索引可以提高查询性能，但它也增加了存储开销，并可能对INSERT、UPDATE和DELETE操作的速度产生影响，因为有更多的索引结构需要被维护。
5. 选择性：索引的选择性是指索引列中唯一值的数量与表中行数的比率。高选择性的索引通常更有效，因为它们能更精确地定位数据。当组合多个列创建联合索引时，这些列的组合选择性可能会比单独的列更高。
6. 覆盖查询：如果一个查询只需要使用联合索引中的列，那么数据库可以只读取索引，而不需要访问实际的数据行。这称为覆盖查询，它通常非常快。举个例子，表t有a、b、c三列，a是主键，现在一条SQL是select c from t where b = 'x';如果你只建立的b字段的索引，那么还是需要回表（不清楚回表概念可以看下3.2的普通索引查询过程描述），如果建立了b、c字段的联合索引，不需要回表。可以减少io次数。