【MySQL】索引

最新推荐文章于 2025-06-13 23:20:28 发布

schdut

最新推荐文章于 2025-06-13 23:20:28 发布

阅读量1.5k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/shengchaohua163/article/details/128757643

版权

MySQL 专栏收录该内容

4 篇文章

订阅专栏

文章详细介绍了MySQL中的索引类型，包括B+树结构及其在数据库中的应用。讨论了索引的优点和缺点，以及何时适合创建索引。提到了不同类型的索引，如聚簇索引、非聚簇索引、联合索引，强调了索引设计的最佳实践和性能优化策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

MySQL索引——分类、何时使用、何时不使用、何时失效
 数据库两大神器【索引和锁】

索引（Index）是帮助MySQL高效获取记录的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。通常的索引结果如下图所示：

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

索引的优点有：

减少检索数据的数量，提高检索数据的效率，降低数据库的IO。
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
把随机IO变为顺序IO，加速表和表之间的连接。

索引的缺点有：

占用一定空间，如果索引过多，容易造成空间浪费。
降低插入、更新和删除操作的速度，不仅要保存数据，还要更新索引。

适合建索引的条件：

表比较大，查询操作占多数的表适合建索引。
经常查询的字段适合建索引。
经常作为where查询条件的字段适合建索引。
经常需要排序的字段适合建索引。
与其他表关联的字段，比如外键，适合建索引。

不适合建索引的条件：

表记录少的。
增删改操作占多数的表或者字段不应该建立索引。
where条件里用不到的字段不应该创建索引。
过滤性不好的不适合建索引。

索引语法

索引可以在创建表的时候创建，也可以在建好的表上增加新的索引。

创建索引：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

查看索引：

SHOW INDEX FROM table_name[\G];

删除索引：

DROP INDEX index_name ON tbl_name;

用ALTER命令来添加索引：

# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
AlTER TABLE tb_name add primary key(column_list);
# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
AlTER TABLE tb_name add unique index_name(column_list);
# 添加普通索引， 索引值可以出现多次。
AlTER TABLE tb_name add index index_name(column_list);
# 该语句指定了索引为FULLTEXT，用于全文索引
AlTER TABLE tb_name add fulltext index_name(column_list);

索引分类

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

B-Tree索引：最常见的索引类型，一般使用 B+ 树来实现。
Hash索引：只有Memory引擎支持，使用场景简单。
R-Tree索引：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-Text：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引。另外，InnoDB存储引擎从MySQL5.6版本开始支持全文索引。

比较常用的索引就是B-Tree索引和Hash索引。Hash索引相对简单，只有Memory/Head存储引擎支持Hash索引。

1）B-Tree索引

B-Tree索引是最常见的索引，其构造类似一棵树，能根据键值进行快速查找需要的行。需要注意的是，B-Tree索引中的B不是代表二叉树（Binary Tree），二是代表平衡树（Balanced Tree）。

这种索引可以分为几类：

普通索引：主要目的是为了加快查询数据，一张表允许建多个普通索引，允许数据重复和NULL。
唯一索引：类似普通索引，索引列的值必须唯一，可以为NULL。唯一索引可以保证该属性列的数据的唯一性，也可以提高查询效率。
主键索引：特殊的唯一索引，只能有一个，不允许为NULL，不允许重复，一般是在建表时指定。对于InnoDB存储引擎，如果没有显式指定主键，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个自增主键。
联合索引（复合索引）：在多个字段上创建索引，可以同时创建多个索引，遵循最左匹配原则。

2）哈希索引

哈希索引使用了哈希算法，通过键值计算出一个哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。但是哈希索引也有一些局限：

不支持范围查询。
没办法利用索引完成排序。
在有大量重复键值情况下，哈希索引的效率也是极低的（哈希碰撞问题）。

索引结构

https://www.cnblogs.com/itwxe/p/15783164.html

B树

B树（B Tree）又叫多路平衡搜索树，一颗m叉的B树特性如下：

树中每个节点最多包含m个孩子。
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点，则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以3叉B树为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1，所以 1<= n <=2。如下图所示，
在这里插入图片描述

和二叉树相比，B树的查询数据的效率更高。因为对于相同的数据量来说，B树的层级结构比二叉树小，因此搜索速度快。

B+树

B+树是B树的变种，两者的主要区别在于：

B树的非叶子结点存放了N个关键字和N+1个指针，而B+树的非叶子结点的关键字与指针个数相同。
B树的非叶子结点同时存放了关键字和数据，而B+树的非叶子结点只存放关键字，所有的数据都存在叶子结点。
B树的检索过程可能没有到达叶子结点就结束了，而B+树的检索都是从根结点到叶结点。
B树非叶子节点是独立的，B+树为所有叶子结点增加一个链指针。

3叉B+树的结构如下所示：
在这里插入图片描述

和B树相比，B+树的查询效率更加稳定。因为B+树只有叶子节点保存key信息，查询任何关键字都要从根节点走到叶子。

MySQL中的B+树

MySQL的基本存储结构是页，页的大小一般是16KB（一个磁盘块是4KB），页中存储了数据记录、页目录等信息，其中页目录就是索引，可以指向其他的页。

InnoDB存储引擎的页结构如下图所示：
在这里插入图片描述

通过索引来查找数据时，先读取根节点的页，使用二分查找搜索其中合适的索引，找到下一层的页，进而找到叶子节点，在叶子节点的数据中查找目标数据。

不通过索引查找数据时，只能通过叶子节点的双向链表遍历所有的页，查找每一个页中的每一条记录。

聚簇索引

MyISAM存储引擎的数据文件和索引文件是分开储存的。索引底层结构的B+树的叶子节点的data域存放的是数据记录的地址，非叶节点存放的是索引。在检索数据的时候，首先按照B+树搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的记录。这被称为非聚簇索引或非聚集索引，即索引结构和数据分开存放的索引。

InnoDB存储引擎的主键索引是聚簇索引，因为数据文件和主键索引是一起存储的，而且数据文件本身就是按主键索引底层的B+树进行组织的，树的叶节点data域保存了完整的数据记录，非叶节点存放的是索引（主键key）。

InnoDB存储引擎的非主键索引是非聚簇索引。非主键索引底层也是B+树，叶子节点存放的是主键值和索引列（和MyISAM不同），非叶子结点存放的是索引。非聚集索引的优点是不存储完整的数据记录，更新时或排序时代价较小。

对于InnoDB存储引擎：

在根据主索引查询时，直接找到key所在的结点即可取出数据；
在根据辅助索引查找时，需要先取出主键的值，再走一遍主键索引，这称为二次回表。
建议使用比较短的主键，比如自增主键，因为过长的字段会导致辅助索引过大；
建议使用有序的主键，比如自增主键，因为没有顺序的主键在插入时需要进行排序，会造成主索引频繁分裂。

联合索引

如果用多个非主键列建立索引，该索引称为联合索引，并且可以一次性建立多个索引。比如在(a,b,c)三个字段上建立索引，则会同时建立(a)、(a,b)、(a,b,c)三个索引。

联合索引的好处是在一棵B+树上存储多个索引，减少了索引的空间。如果对多个字段建立单列索引，会建立多棵B+树，占用磁盘空间。

联合索引遵循最左匹配原则。最左匹配原则是指，按联合索引建立时列的顺序进行连续匹配，遇到范围查询时（>、<、between、like）停止匹配。举例，对于联合索引(a,b,c)，当查询条件是a=1 and b<10 and c=3，根据最左匹配原则可以使用(a,b)索引，无法使用(a,b,c)索引。

如果查询条件的顺序和联合索引的顺序不同，MySQL会自动优化为相同的顺序，再进行最左匹配。举例，对于联合索引（a,b,c），当查询条件是a=1 and c>3 and b=10，MySQL会自动优化为a=1 and b=10 and c>3，因为可以使用完整的(a,b,c)索引。

联合索引中有一种特殊的索引，覆盖索引，就是包含了所有查询字段的索引。覆盖索引的好处是可以避免二次回表。对于Innodb存储引擎来说，二级索引在叶子节点中所保存的是主键值和索引列，如果使用非主键索引查询不在该索引中的数据的话，在查找到相应的键值后，还要通过主键索引进行二次查询才能获取真正需要的数据。而对于覆盖索引来说，二级索引的索引列中已经包含了所需的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

其他

其他使用索引的注意事项：

尽量选择区分度高的列作为索引，区分度的公式是COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。在联合索引，把区分度高的列放在左边。
避免在where子句中对字段施加函数，这会造成无法命中索引。
删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。
避免冗余索引。冗余索引指的是索引的功能相同，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
考虑在字符串类型的字段上使用前缀索引代替普通索引，前缀索引仅限于字符串类型，较普通索引会占用更小的空间。