tulip notes
EffectTang
2024-07-14
目录

索引的类型与创建、设计原则

# 索引的类型与创建、设计原则

# 选择使用 B+树的原因

MySQL 的 InnoDB 存储引擎选择使用 B+ 树而不是 B 树来存储数据，主要是因为 B+ 树在数据库系统中具有更好的性能和更高的效率。然而，B 树也有其独特的优势，特别是在某些特定的应用场景下。

你要知道某个事物的优势，一定要了解其结构。就像鸟会飞，是因为翅膀以及发达的胸肌。那B树和B+树各自的结构是什么样子？它们怎么存储数据的呢？

下面给一个简要的示例图：

          [10, 20, 30]
         /    |    |    \
  [5, 7]  [15, 18]  [25, 28]  [35, 38]
1
2
3

这是一个B树的结构，首先它是一颗平衡多路搜索树，每个节点都存储一个完整的列。叶子节点是指向父节点。小的数据在左边的子节点，大的数据在右边的叶子节点。

下列是一个简要的B+树示例图：

          [10, 20, 30]
         /    |    |    \
  [5, 7]  [15, 18]  [25, 28]  [35, 38]
  |____|____|____|____|
  L1  L2  L3  L4  L5
1
2
3
4
5
  • 它的根节点跟其余父节点都只存储某行主键值，只有在最下面的叶子节点，才存储完整的数据。同时叶子节点彼此之间通过双向指针互相连接在一起。

# 各自的优势

下面列举出一些B树的优势，每种结构都应该有其特有的优势。

节点中存储数据：

  • B 树：每个节点都存储键值对（即索引键和相应的数据）。这意味着在查找过程中，一旦找到目标节点，就可以直接获取数据，而不需要再访问叶子节点。
  • B+ 树：所有数据都存储在叶子节点上，非叶子节点只存储索引键。这使得 B+ 树在查找过程中需要两次磁盘 I/O 操作（一次访问索引节点，一次访问数据节点）。

适用于频繁更新的小范围查询：

  • B 树：如果应用中频繁进行小范围的更新操作，并且查询也集中在这些小范围内，B 树可以提供更好的性能，因为它减少了磁盘 I/O 次数。
  • B+ 树：虽然 B+ 树在范围查询和全表扫描方面表现更好，但在单个记录的查找和更新操作中，可能会有额外的开销。

# 为什么 MySQL 选择 B+ 树

优化范围查询：

  • B+ 树：所有的数据都存储在叶子节点上，并且叶子节点通过指针连接成一个链表。这使得 B+ 树非常适合范围查询，因为一旦找到第一个符合条件的记录，后续的记录可以通过链表快速遍历。
  • B 树：B 树的每个节点都存储数据和索引键，这使得范围查询需要在树中进行多次跳跃，效率较低。

支持高效的全表扫描：

  • B+ 树：由于 B+ 树的叶子节点通过指针连接成一个链表，全表扫描可以直接从第一个叶子节点开始，依次遍历所有叶子节点，非常高效。
  • B 树：B 树的全表扫描需要遍历所有节点，效率较低。

具有更低的层级：因为，中间的节点，B+树只存储主键值，所有，可以存储更多的列，树的高度可以更低，遍历的时间可能更少。

# 索引的定义与作用

索引是数据库管理系统中用于提高查询速度的一种数据结构。在MySQL中，索引可以看作是一种特殊的表，其中包含了对数据表中特定列的值及其在数据表中的位置信息。

它就像是一本书的目录或字典，允许我们在很短的时间内找到所需的信息。 在数据库环境中，索引可以大大加快数据检索速度，减少处理时间和磁盘I/O次数。此外，它还可以确保对数据库记录的操作（如INSERT、UPDATE和DELETE）的完整性。因此，在设计数据库应用程序时，应充分考虑并合理利用索引技术。

# 索引的分类

在MySQL中，索引可以根据多个维度进行分类，主要包括索引的实现方式、用途以及存储位置。分类不止上述三种方式，还可以从更多角度进行分类，比如覆盖程度、可见性、存储引擎支持等。

# 按实现方式分类（或者称数据结构）

  • B+树索引：这是最常见和最基本的索引类型，适用于大部分场景，尤其擅长范围查询和排序。在InnoDB存储引擎中，所有索引（包括主键索引和普通索引）都是基于B+树实现的。

  • Hash索引：这种索引使用哈希算法来存储索引项，主要用于快速查找，但在进行范围查询时效率较低。MyISAM和InnoDB存储引擎都支持哈希索引，但使用场景和方式有所不同。

  • R树索引（R-tree index，用于空间数据）：R树索引是一种用于空间数据的索引结构，特别适用于地理信息系统（GIS）和图像数据库系统。它能够高效地处理多维数据，如地图坐标、图像特征向量等，主要用于范围查询和最近邻查询。

  • Fulltext索引（又叫全文索引）：FullText索引是一种专门用于全文搜索的索引类型，它允许用户在大量的文本数据中搜索特定的词语或短语，即使这些词语或短语出现在文档的不同位置。FullText索引在新闻网站、论坛、博客和任何需要搜索大量文本内容的应用中都非常有用。

# 扩展-全文索引

比如一张表staff_table，其中含有一列为name类型为varchar(20)，这时使用LIKE操作符进行模糊查询——比如LIKE '%kangkang%'，如果name列上有全文索引，此时MySQL也不会进行全表扫描，它会利用全文索引进行搜索。否则，会进行全盘扫描。

SELECT * FROM users WHERE name LIKE '%kangkang%';
1

# 按用途分类（常用）

  • 主键索引（Primary Key Index）：用于唯一标识表中的每一行记录，一个表只能有一个主键。在InnoDB中，主键索引还作为聚簇索引，即数据行与主键索引条目存储在一起，提高了数据访问效率。

  • 普通索引（General Index）：也称为辅助索引或非聚集索引，用于加速特定列的查询速度，可以有多个。在InnoDB中，普通索引是二级索引，存储了指向主键的指针，而不是完整的数据行。

    • 通过非聚簇索引查找数据时，需要先找到非聚簇索引的叶子节点，然后通过该节点中的主键值再去聚簇索引中查找完整的数据行，这被称为“回表”操作。

  • 唯一索引（Unique Index）:是一种确保列或列组合的唯一性的索引类型，它可以防止在索引列中出现重复的值。这种索引类型在数据库中非常常见，用于维护数据的完整性。

  • 多列索引（Composite Index）:多列索引是指在一个索引中包含多个列的索引。这种索引类型在查询条件涉及多个列时特别有用，可以显著提高查询性能。多列索引可以加速那些在多个列上都有条件的查询，因为索引可以一次定位到满足所有条件的行。

  • 全文索引（Full-text Index）

# 扩展-前缀索引

索引前缀长度是指在创建索引时，仅使用列值的前N个字符或字节来构建索引，而不是使用完整的列值。这是一个优化策略，尤其适用于包含长字符串的列，通过仅索引每个值的部分前缀，可以显著减小索引的大小，从而提高索引的维护效率和查询性能。它是存储和性能之间平衡的一种结果。

因为它只取部分值，因此myisam跟innodb都对前缀索引有长度限制。在MySQL中，创建前缀索引时可以在索引定义中指定前缀长度，例如：

CREATE INDEX idx_name ON table_name (column_name(length));
1

使用SHOW INDEX FROM table_name命令来查看索引信息时，前缀索引的信息可以通过Sub_part列来识别。如果Sub_part列的值不是NULL，并且小于相应Column_name列的原始列定义的长度，那么这个索引就是一个前缀索引。

# 按存储位置分类

  • 聚簇索引（Clustered Index）：在InnoDB中，主键索引就是聚簇索引，数据行与主键索引条目存储在一起，提高了基于主键的查询效率。
  • 非聚簇索引（Nonclustered Index）：也称为二级索引或辅助索引，存储了指向聚簇索引（通常是主键索引）的指针，而不是直接指向数据行。
  • InnoDB 存储引擎支持聚簇索引和非聚簇索引，这两大类。非聚簇索引还可以继续细分。
    • 从 MySQL 5.7 开始，InnoDB 支持空间索引。
  • MyISAM 存储引擎只支持非聚簇索引。
  • MySQL 还支持其他类型的索引，如唯一索引、全文索引、空间索引等。

# 索引的创建

# 建表时创建

create table users(
    id        int unsigned auto_increment primary key,
    username  varchar(50)  not null,
    password  varchar(11)  not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1
2
3
4
5

在创建表时，在表中定义一个primary key约束时，MySQL会自动为我们创建一个相应的主键索引。这也是所谓的隐式创建，可以通过如下语句来进行查看。

show index from users;
1

image-20231102145644808

Index_type列显示索引的类型。

  • Key_name为PRIMARY表示这是主键索引。
  • Non_unique列为0表示这是一个唯一索引（包括主键索引）。
  • Non_unique列为1表示这是一个普通索引。
  • Index_type可能显示BTREE、HASH、FULLTEXT等，具体取决于索引类型。

# 建表后添加

当然，除了在创建表的时候定义，你也可以在已经定义好的表中进行添加。

create index username_index on users(username);
1

以上语句就为users表又添加了一个普通索引（单列索引）

image-20231102151416370

或者通过alter语句进行索引的创建。

alter table users add index indexName(password);
# ALTER TABLE `tableName` ADD PRIMARY KEY ( `column` ) 
# 添加主键索引
# ALTER TABLE `tableName` ADD UNIQUE (`column`)
# 添加唯一索引
# ALTER TABLE `tableName` ADD INDEX index_name (`column1`,`column2`)
# 添加组合索引
1
2
3
4
5
6
7

以上就又创建了一个名为indexName的普通索引，同时还列举了主键索引跟唯一索引的创建方式。

在创建索引方面，ALTER TABLE 和 CREATE INDEX 都可以达到相同的效果，但它们之间存在一些微妙的区别。

例如，ALTER TABLE 可以同时执行多个更改，而 CREATE INDEX 则只能用于创建单个索引。

# 删除索引

在优化表时，如果遇到需要删除索引时，MySql也是支持的，可通过如下语句实现：

# 删除索引
# DROP INDEX index_name ON table_name;
DROP INDEX username_index ON users;
1
2
3

这里我们就将刚刚创建的单列索引给删除了，只剩下最初建表时创建的主键索引。

image-20231102145644808

# 被动创建

那如果我们在创建表的时候不设置索引，在后续也不给表添加索引，那该表是否就没有索引呢，它的查询效率是否就会很低？

从MySql 8.0.30开始，MySQL支持为没有显式主键的InnoDB表生成不可见的主键。当sql_generate_invisible_primary_key服务器系统变量设置为ON时，MySQL服务器会自动将生成的不可见主键(GIPK)添加到任何这样的表中。

  • 查询对应设置
SELECT @@sql_generate_invisible_primary_key;
1

该属性的默认值为:0。不开启，也就是说默认的情况下，MySql不会为没有创建的表创建主键(索引)。

create table demos(
    username  varchar(50)  not null,
    password  varchar(11)  not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# show index from demos;
1
2
3
4
5

未将sql_generate_invisible_primary_key的值设为on的情况下，是不会为其添加主键的。

接下来我们将其设置为on，再重新创建表看看。

SET sql_generate_invisible_primary_key=ON;
1

| @@sql_generate_invisible_primary_key | ------------------------------------ 0 |

create table demos(
    username  varchar(50)  not null,
    password  varchar(11)  not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# show index from demos;
1
2
3
4
5

此时，就可以看到该表在创建成功后，自动有了一个名为PRIMARY的主键

image-20231102162759335

相关链接：

MySql-官网文档 (opens new window)

# 通过索引定位数据

MySQL常用的InnoDB引擎中，还是使用 B+树索引比较多。以下是MySql使用B+树进行数据存储的一个简略结构，

image-20231102182906743

真实场景中最底层中每个节点(叶子节点)都有若干条数据，并且用其中的最小值作为一个标识符，而中间的节点则是存储的最底层中每个节点的最大值（当然其中的数据也有若干条），并用其中最大值作为标识符。而最上层的节点就是根节点，它存储的就是第二层中的子节点的标识符跟其他一些信息。

# 查找过程

比如我们去找43这个数，

  • 首先在跟节点进行比较，发现应该去最大的那个子节点图中为0033去查找
  • 接着在0033节点中跟内部元素进行比较，发现需要去最大的叶子节点中查找
  • 最后在叶子节点0033-0042中查找，经过一番比较过，没有找到，于是返回结果：没有该元素

以上就是如何通过主键索引查询数据的过程，每个数据节点存储的数据具体有些哪些这里就不展开了。

提示：使用InnoDB引擎创建主键索引，叶子节点存储了我们完整的用户记录。

# 聚簇索引跟非聚簇索引

使用InnoDB 引擎创建的主键索引也叫聚簇索引（Clustered Index）。聚簇索引是指：将整个表的数据都存储在一个 B+树结构中的索引。该索引组织了表中的数据，使它们按照主键值排序并存储在一个 B+树中。聚簇索引的特点：是可以快速访问指定范围内的记录，因为不需要多次查找就可以检索到所需要的数据。此外还有聚簇索引的叶子节点存储的是表中的数据。

此外还有非聚簇索引，在MySql中，非聚簇索引是指在 InnoDB 引擎中除了主键索引以外的所有其他索引类型，如：唯一索引，组合索引...非聚簇索引不同于聚簇索引，它不包含表的实际数据，而是指向数据的指针。当需要从非聚簇索引中检索数据时，MySQL 需要执行一次回表操作，以便访问聚簇索引并将数据返回给客户端。非聚簇索引也叫二级索引，或者叫辅助索引。它叶子结点存储的则是主键值跟索引列。

使用非聚簇索引查找数据的过程一般情况如下：

  1. 首先，MySQL 会在非聚簇索引的 B+ 树上找到匹配的索引项，并读取指向实际数据行的指针；
  2. 然后，MySQL 会根据这些指针从聚簇索引中检索出相关行，然后将其返回给客户端。

从非聚簇索引转到聚簇索引的这个过程就叫做回表。

注意：通过非聚簇索引查询不是一定会发生回表，如果查询完全符合非聚簇索引的条件，则可以直接从非聚簇索引中检索出所需的记录。在这种情况下，无需进一步回表即可完成查询。

# 设计索引的原则

# 选择where后的列

最适合索引的列应该是经常出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。

# 选基数大的列

通常建议选基数大的列。理解它，首先要弄清基数这个概念，基数：即不同值的数量。

基数大的列就是不同值多的列，或者说重复值很少的列。

选择它可以主要是因为基数大的列能帮我们减少搜索空间、减少IO操作，减少回表次数，节省时间。以下是详细解释：

减少搜索空间：基数高的列意味着该列的值分布更广，当进行查询时，如果使用该列作为索引，可以更有效地缩小搜索范围。例如，在一个电话簿中，使用电话号码（基数高）作为索引要比使用名字（可能基数较低，因为有重名）作为索引更高效，因为电话号码更有可能是唯一的。

减少I/O操作：数据库查询通常受限于磁盘I/O速度。基数高的索引可以减少需要从磁盘读取的数据块数量，从而减少I/O操作，加快查询速度。

减少回表次数：在使用二级索引（非聚簇索引）时，如果索引列的基数高，可以减少从索引到主键索引（或聚簇索引）的回表次数，因为每次查询可以定位到更少的行。

当然，选择索引列时也需要权衡。虽然基数高可以带来查询效率的提升，但过长的索引键（例如，如果列中每个值都很长）会增加索引的大小，从而影响插入、更新和删除操作的性能。此外，如果基数大的列更新频率很高，那也不建议使用它作为索引，因为频繁的更新操作也可能降低索引的性能。

# 使用前缀索引

注意：前缀索引主要适用于那些包含长字符串的列，如VARCHAR或TEXT类型，而不是所有数据类型。

对于某个长的字符串列，如果在前5个或8个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前5个或8个字符进行索引能够节省大量索引空间，也可能会使查询更快。

因为较小的索引涉及的磁盘 IO 较少，同时较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块还能容纳更多的键值，因此，如果要对一些字符串类型的列进行索引，有条件就尽量使用前缀索引。

# 善用多列索引

关于多列索引（或者称联合索引），它在进行匹配时，有一个著名的”最左前缀原则“。我们在设计时，应该尽量考虑到业务场景，对于它们之间的顺序，尽可能实现一个联合索引，能作为N个独立使用。

换句话说，一个(A, B, C)的多列索引可以像三个独立的索引一样被使用：(A)、(A, B)和(A, B, C)。

这是因为索引的结构是以列A开始的，然后是列B，最后是列C，所以MySQL可以利用这个索引的不同前缀来匹配行，这样就不必要为每种可能的列组合创建单独的索引。

# 避免过度使用

不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。

因为每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时甚至可能需要重构，因此，索引越多，所花的时间越长。

#MySql
上次更新: 2025/04/23, 16:23:16
建表：选择合适的数据类型
跟Redis的数据一致性保证

← 跟Redis的数据一致性保证→

最近更新
01
面向切面跟自定义注解的结合
05-22
02
时间跟其他数据的序列化
05-19
03
数据加密与安全
05-17
更多文章>
Theme by Vdoing | Copyright © 2023-2025 EffectTang