MYSQL索引-优快云博客

本文链接：https://blog.youkuaiyun.com/liuhangzho/article/details/146188450

一、概念:

一种排好序，能够提升查询性能的数据结构

二、分类

聚簇（集）索引（主键索引）

定义：聚簇索引决定了数据在物理磁盘上的存储顺序。数据行和索引一起存储在同一个B树（或B+树）结构的叶子节点中。
特点：
- 数据顺序：数据按照聚簇索引的键值顺序存储。
- 唯一性：通常，聚簇索引是唯一的，因为数据表中的每一行都需要有一个唯一的物理位置。
- 主键与聚簇索引：在大多数数据库系统中，主键默认创建为聚簇索引。如果表中没有定义主键，某些数据库允许选择一个唯一索引作为聚簇索引，或者自动创建一个隐藏的聚簇索引。
- 查询性能：由于数据按索引顺序存储，范围查询（如BETWEEN或ORDER BY）和联合查询（涉及多个列）通常比非聚簇索引更快。

非聚簇（集）索引（非主键索引）

定义：非聚簇索引的索引结构与数据行分开存储。索引的叶子节点存储的是指向实际数据行的指针（通常是主键值或行的物理地址）。
特点：
- 数据顺序：数据在磁盘上的物理存储顺序与索引顺序无关。
- 多个非聚簇索引：一个表可以有多个非聚簇索引，每个索引都可以有不同的键值。
- 查询性能：对于精确匹配查询（如=），非聚簇索引通常表现良好，但对于范围查询，性能可能不如聚簇索引，因为需要额外的步骤来查找数据行。
- 占用空间：由于索引和数据分开存储，非聚簇索引会占用额外的磁盘空间。

实际应用中的选择

聚簇索引通常用于主键或那些频繁进行范围查询的列。
非聚簇索引则适用于那些需要快速访问但不需要物理排序的列，或者当表中有多个查询模式需要不同的索引时。

三、索引底层采用的是B+树

1、为什么不用hash

不支持范围查询：
- 哈希索引无法进行范围查询（如<、>、BETWEEN等），因为哈希算法无法维护数据的排序关系，无法有效地执行范围查找。
- 在需要范围查询的场景中，哈希索引需要遍历所有的哈希桶（bucket），效率低下。
无法处理排序操作：
- 哈希索引没有维护元素的顺序，因此无法直接支持ORDER BY或GROUP BY等排序操作。
- 进行排序操作需要对所有的数据进行全表扫描，然后再进行排序，这会大大降低查询效率。
插入和删除操作效率低：
- 插入操作需要计算哈希值并将数据放入相应的哈希桶中，如果哈希桶已经满了，则需要进行扩展或重新哈希，这会带来额外的开销。
- 删除操作也需要找到对应的哈希桶并移除数据，如果哈希桶中的数据较多，这个过程也会变得复杂。
不能进行部分匹配查询：
- 哈希索引由于其映射特性，无法支持部分匹配查询（如模糊查询）。
- 例如，查询一个字符串中包含某个子串的记录，哈希索引无法直接通过哈希值来定位这些记录。
内存占用和维护成本高：
- 在使用哈希索引时，需要为每个哈希桶分配内存，而随着数据量的增大，哈希桶的数量也会随之增加，导致内存占用显著增加。
- 哈希索引的维护成本较高，特别是在数据频繁更新的情况下，需要不断地进行哈希计算和哈希桶调整。
数据分布和负载均衡问题：
- 哈希索引的数据分布是无序的，无法保证负载均衡。
- 数据分布不均衡会导致某些哈希桶负载过重，影响查询和更新操作的效率。

相比之下，B树（B-Tree）及其变种B+树等索引结构能够高效地支持范围查询、排序操作，并且在插入、删除和更新操作上也具有较高的效率。此外，它们还能够通过树的层级结构实现数据的均匀分布，保证负载均衡。因此，在大多数数据库系统中，B树及其变种被广泛用作索引结构。

2、B树

B树的基本结构

节点类型：
- 非叶子节点：存储索引值，用于指引查找方向。
- 叶子节点：存储实际的数据或指向数据的指针。
- 父节点：位于某个节点之上的节点，通过索引值与子节点相连。
节点存储内容：
- 每个节点可以存储多个索引值和指向子节点的指针。
- 在某些实现中，非叶子节点仅存储索引值，而叶子节点存储索引值和对应的数据或数据指针。
存储效率：
- B树通过增加每个节点的存储容量来减少树的高度，从而降低查询时的磁盘I/O操作次数。
- 在MySQL等数据库系统中，数据通常以页（page）为单位进行存储，一页的大小通常为16KB（16 * 1024B）。
查询效率：
- 查询时，从根节点开始，根据索引值逐层向下查找，直到找到目标数据所在的叶子节点。
- B树的高度较低时，查询路径较短，效率较高。但随着数据量的增加，树的高度可能会增加，影响查询效率。
节点存储大小计算：
- 您提到的索引大小为8B，行数据大小为1KB，节点总大小为1032B（这里可能存在误解，因为通常节点大小会考虑更多的因素，如指针大小、元数据等，但为简化分析，我们采用您的数据）。
- 在一页16KB的情况下，如果仅考虑索引和行数据的存储，一页大约可以存储15个这样的节点（假设每个节点只存储一个索引值和一个行数据，且不考虑其他开销）。
树的高度与查询效率：
- 如您所述，随着数据量的增加，为了存储所有数据，B树的高度可能会增加。
- 树的高度增加会导致查询路径变长，需要访问更多的节点才能找到目标数据，从而增加磁盘I/O操作次数和查询时间。
节点存储优化：
- 在实际应用中，B树的节点通常会存储多个索引值和对应的数据指针（对于非叶子节点）或数据（对于叶子节点）。
- 这意味着一页可以存储更多的索引值或数据，从而降低树的高度。
数据库优化：
- 数据库系统通常会对B树索引进行优化，如使用B+树（B树的变种）来提高范围查询的效率。
- B+树将所有实际数据存储在叶子节点，并通过链表将叶子节点连接起来，便于范围查询。
- 数据库系统还会利用缓存、预取等技术来减少磁盘I/O操作次数，提高查询效率。

3、非叶子节点

存储内容：非叶子节点存储索引值和指向子节点的指针。
索引值大小：8B（您给出的数据）。
指针大小：6B（您给出的数据，这通常取决于系统架构和指针的实现）。
节点总大小：14B（索引值 + 指针）。

4、叶子节点

存储内容：叶子节点存储索引值和对应的数据或指向数据的指针（在数据库系统中，通常是数据的物理地址或页号）。
索引值大小：8B（与非叶子节点相同）。
数据/指针大小：您之前提到的是1KB，但这里我们更关心的是节点能够存储的索引值数量，因此这个大小对于计算非叶子节点的存储能力不是直接相关的。然而，在计算叶子节点时，我们需要考虑整个节点的大小（包括索引值和数据/指针）。

5、节点存储能力计算

非叶子节点：一页（16KB）可以存储大约1170个索引值和指针对（16*1024B / 14B ≈ 1170）。
叶子节点：如果每个叶子节点除了索引值外还存储1KB的数据（或指向数据的指针），则一页可以存储的索引值和数据对数量会减少。但您的计算中只考虑了索引值和数据/指针的总大小（1032B），并得出了一页可以存储15个这样的对。然而，这个计算没有考虑到节点内部的元数据（如节点类型、分裂信息等）和其他可能的开销。在实际实现中，一页可能存储的索引值和数据对数量会少于15。

6、B树的存储能力

一层存储：1170个索引值（非叶子节点）。
二层存储：如果每个非叶子节点都有1170个子节点（实际上，由于B树的平衡性质，这不太可能完全发生，但可以作为理论上的上限），则二层可以索引的索引值数量为1170^2。然而，这同样是一个理论上的上限，实际中由于B树的填充因子（即节点中实际存储的索引值数量与节点最大容量的比例）和分裂策略，这个数量会小一些。
三层（叶子节点）：如果第三层是叶子节点，并且我们按照您的计算（15个索引值和数据/指针对每页），则三层可以存储的数据量会受到前面两层索引结构的限制。但实际上，叶子节点的存储能力也会受到数据大小和节点内部结构的影响。

7、注意事项

B树的平衡性：B树是一种自平衡的树结构，它会在插入和删除操作后保持平衡，以确保树的高度尽可能低。这意味着，尽管理论上可能存在很深的树，但在实际使用中，B树的高度会被控制在相对较小的范围内。
填充因子：在实际应用中，B树的节点通常不会完全填满，而是会保留一定的空间以应对未来的插入操作。这有助于提高B树的动态性能。
数据库系统的优化：数据库系统通常会对B树索引进行各种优化，以提高查询性能。这些优化可能包括使用B+树（在叶子节点之间建立链表以提高范围查询的效率）、利用缓存减少磁盘I/O等。
8、为什么选B书
非叶子节点和叶子节点存储的数据不一样，可以使用尽量深度低的树存储大量的数据，树的深度越低，查询的次数就越少，性能就越高
叶子节点是一个双向链表，支持范围查询，也能够提升访问效

四、主键索引

1、InnoDB表与主键

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index Organized Table，IOT）。每张InnoDB表都有一个主键（Primary Key）：

如果在创建表时没有显式地定义主键，InnoDB存储引擎会选择表中符合条件的列去创建主键。首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。如果不符合上述条件，InnoDB存储引擎会自动创建一个6字节大小的指针作为主键。
当表中存在多个非空的唯一索引时，InnoDB存储引擎会根据建表时所创建的第一个非空唯一索引作为主键。

2、主键索引的结构

InnoDB使用B+树索引结构来实现数据的索引，B+树索引结构的特点包括：

B+树是一种平衡树结构，所有叶子节点通过指针连接成一个链表，支持高效的范围查询。
每个叶子节点都包含一个指向对应行数据的物理地址（也称作聚集索引）。
使用B+树作为索引结构，可以保证查询操作的平衡性、有序性、支持范围查询和高并发。

在InnoDB的主键索引中：

非叶子节点存储的是主键值。
叶子节点存储的是行数据（聚簇索引）。

因此，通过主键可以直接在索引树上找到数据，无需查表，查询速度非常快。

3、自增主键的推荐

推荐使用自增主键的理由如下：

插入性能高：自增主键每次插入数据时，都会将新数据放在索引树的最后面，这样可以快速地找到插入的位置，无需做额外的开销，如移动数据的位置、旋转树等。
索引结构稳定：使用自增主键可以避免频繁的索引树旋转和数据移动，从而保持索引结构的稳定性。

如果主键不是自增的，例如使用身份证号或学号等，由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这会带来不必要的开销，同时频繁的移动、分页操作会造成大量的碎片，得到不够紧凑的索引结构。

综上所述，InnoDB引擎的表确实需要主键来组织数据的存储和索引，而自增主键由于具有插入性能高和索引结构稳定等优点，因此是推荐的主键选择。

五、非主键索引

1. 数据冗余与存储效率

如果普通索引（非主键索引）的叶子节点中直接存储完整的数据行，那么会导致数据冗余。因为每个索引都会包含一份数据的副本，这会占用大量的存储空间。而存储主键值则更为高效，因为主键通常较短且唯一，能够节省存储空间并降低索引的维护成本。

2. 数据一致性与维护成本

当数据行发生变化时（如更新或删除），如果每个索引都存储了完整的数据行，那么需要同时更新所有相关索引中的数据，这会导致额外的维护开销。而存储主键值则只需更新主键索引和对应的非主键索引中的主键值，降低了维护成本并保证了数据的一致性。

3. 查询效率与索引结构

非主键索引的主要作用是加速查询。通过存储主键值，非主键索引可以快速定位到对应的数据行，然后再通过主键索引获取完整的数据。这种设计使得查询过程更加高效，因为只需要在索引结构中遍历一次，即可找到所需的数据。

4. 索引类型与适用场景

唯一索引：用于确保列中的值唯一。如果尝试插入重复的值，数据库会报错。
联合索引（组合索引）：用于对多个列进行索引。在查询时，如果使用了联合索引中的列作为查询条件，数据库会利用联合索引来加速查询。
全文索引：主要用于文本数据的全文搜索。Elasticsearch等搜索引擎就支持全文索引，能够高效地处理大规模的文本数据查询。

综上所述，非主键索引的叶子节点存储主键值是为了提高存储效率、降低维护成本、保证数据一致性以及提高查询效率。这种设计使得数据库系统能够更高效地处理数据查询和更新操作。

六、创建索引的要求

单表索引不超过5个：
- 索引虽然能提高查询性能，但也会增加插入、删除和更新操作的开销。因此，建议每张表的索引数量不要过多，以避免对性能产生负面影响。具体数量可能因表的大小、查询模式和数据库性能等因素而异，但5个通常被认为是一个合理的上限。
联合索引的字段不超过5个：
- 联合索引（组合索引）涉及多个字段，因此其维护成本较高。过多的字段会导致索引变得庞大且难以维护。一般来说，联合索引中的字段数量不应超过5个，以确保索引的有效性和性能。
经常增删改的字段不适合创建索引：
- 频繁进行增删改操作的字段上创建索引会导致索引的频繁更新，从而增加数据库的维护开销。因此，对于这类字段，应谨慎考虑是否创建索引。
枚举值字段不适合创建索引：
- 枚举值字段通常包含有限数量的唯一值。由于索引的主要目的是加速不同值的查询，因此在枚举值字段上创建索引可能无法带来显著的性能提升，反而会增加存储和维护开销。
不经常修改，经常查询的字段适合创建索引：
- 这类字段是创建索引的理想选择。由于它们很少被修改，索引的维护开销较低；同时，由于它们经常被查询，索引能够显著提高查询性能。
大长度的字段，可以设置前缀索引：
- 对于长度较大的字段，创建完整索引可能会占用大量存储空间并降低查询性能。此时，可以考虑使用前缀索引，即为字段的前几个字符创建索引。这种索引方式能够在节省存储空间的同时保持较好的查询性能。
- 您提供的SQL示例展示了如何在users表的username字段上创建前缀索引：
  
  sql
  
  CREATE TABLE users (
      user_id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(255),
      email VARCHAR(255)
  );
  
  -- 创建前缀索引
  
  CREATE INDEX idx_username_prefix ON users (username(10));
  
  在这个示例中，username字段被定义为一个长度可达255个字符的VARCHAR类型。为了节省存储空间并提高查询性能，我们在该字段的前10个字符上创建了索引。这意味着，当执行涉及username字段的查询时，数据库将使用这个前缀索引来加速查询过程。需要注意的是，前缀索引的选择应根据实际查询模式和字段值的分布情况来确定，以确保索引的有效性和性能。