MySQL索引详解

我不是欧拉_

已于 2022-08-23 10:34:35 修改

阅读量93

点赞数

文章标签： mysql

于 2022-08-17 14:17:20 首次发布

本文链接：https://blog.csdn.net/m0_38033199/article/details/126347904

版权

官方定义

索引是存储引擎用于快速找到记录的一种数据结构

1. 索引的本质：索引是数据结构。

2. 可以理解为排好序的快速查找数据结构。数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

3. 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。

索引的优缺点

优点：

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

缺点：

索引占用磁盘或者内存空间
减慢了插入更新操作的速度

索引分类

1. 从逻辑角度

普通索引：最基本的索引，没有任何限制。

唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引：特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

2. 从数据结构角度

B/B+Tree索引：如果不熟悉B/B+树，参考一文搞懂B树、B-树、B+树 - 漂流小王子 - 博客园。

Hash索引：基于hash表结构实现的索引，只支持精确查找，不支持范围查找，不支持排序。mysql中只有MEMORY/HEAP和NDB存储引擎支持。

FULLTEXT索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用。目前MyISAM和InnoDB引擎都支持了FULLTEXT索引。

R-Tree索引：空间索引。用于对GIS数据类型创建SPATIAL索引。

3. 从存储结构角度

聚簇索引：

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。即索引顺序与数据行顺序一致。

在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键（Primary Key）进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

InnoDB索引实现

InnoDB 使用B+树作为索引结构，其中叶节点的data域存放的是数据记录本身。

主键索引原理图

由上图可以看出：InnoDB的数据文件本身就是索引文件，即它的叶子节点的data域包含了完整的数据记录。该索引的key是数据表的主键。

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

辅助索引原理图

上图为辅助索引图，即非主键列作为索引key，但是这里需注意的是辅助索引的叶子节点的data域存放的是该数据记录的主键值而不是数据记录本身，因为使用辅助索引查询的时候需要进行二次查询（第一次查询到数据记录的主键值，第二次根据主索引查询到实际的数据记录）。

非聚簇索引：

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

MyISAM存储引擎采用的是非聚簇索引。如果涉及到大数据量的排序、全表扫描、count之类的操作的话，MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

MyISAM索引实现

MyISAM存储引擎使用B+树作为索引结构，其中叶节点的data域存放的是数据记录的地址。

主索引原理图

上图所示是MyISAM存储引擎主索引样例图，右下角为一张表的三个列col1、col2和col3，其中col1位主键，该图以主键为索引，其中0x07为十六进制，表示表中某一列数据的存放地址。由此看出叶子节点的data域存放的是数据块的引用（即地址），并非存储真实数据。所以它的索引的顺序与实际数据记录的位置没有必然的联系，即顺序不一定一致。

辅助索引原理图