MySQL 索引分类

原创已于 2024-07-25 15:35:21 修改 · 482 阅读

CC 4.0 BY-SA版权

文章标签：

于 2022-06-22 10:37:02 首次发布

MySQL官方对索引的定义是：索引（Index）是帮助MySQL 高效获取数据的数据结构。索引最形象的比喻就是图书的目录。注意，只有在大量数据中查询时索引才显得有意义。在MySQL中索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

1. MySQL 索引分类

1.1. 数据结构角度

在这里插入图片描述
注：InnoDB/MyISAM实际上也支持Hash索引，但是属于是“自适应Hash索引”，它的创建过程由存储引擎引擎自动优化创建，不能人为干预是否为表创建“Hash索引”。

1.1.1. B+Tree 索引

“B+Tree索引”包含“数据节点”和“索引节点”，“数据节点”通常就是“B+Tree索引”的“叶子节点”，而“索引节点”则通常是“根节点”或“中间节点”。通常在“B+Tree索引”上有两个指针，一个指向“根节点”，另一个指向关键字最小的“叶子节点”，而且所有“叶子节点”之间是一种链式环状结构。因此可以对“B+Tree索引”进行两种检索运算：一种是对于主键的范围查找和分页查找，另一种是从“根节点”开始进行随机查找。
在这里插入图片描述
优点：主要体现在read方面，数据检索效率稳定、效率高、磁盘IO次数少。
缺点：主要体现在write方面，数据写入产生大量的随机IO（如果一个“数据节点”已经写入磁盘，后面树形结构发生变化，“数据节点”需要数据分裂成2个“数据节点”，那么就要删除原来的磁盘块，同时新增2个新的磁盘块，这样反复的操作，就会造成很多的随机IO）。

1.1.2. Hash 索引

“hash索引”通过hash函数计算每条数据的key对应的hash码，然后找到对应的bucket桶的位置，同时在bucket桶中保存指向每个entry的指针（如果出现hash冲突，就使用拉链法来解决）。
在这里插入图片描述
优点：hash冲突低的时候，效率比T+Tree效率高（比如“=”、“in”操作符，最快一次磁盘IO/检索可获取到数据）。
缺点：hash冲突高的时候，检索效率代价大（比如“=”、“in”操作符，需要一一比较entries链表中的数据）；排序、范围查询等会全表扫描。

1.1.3. 空间索引

B-Tree的搜索本质上是一维区间的划分过程，每次搜索节点所找到的子节点其实就是一个子区间。“空间索引”R-Tree采用了B-Tree分割空间的思想，把B-Tree的思想扩展到了多维空间，R-Tree是一棵用来存储高维数据的平衡树。
理论上，不同的点的组合得到的MBR（最小边界矩形）不一样，最后得到的R-Tree的结构也不一样。一般组合的原则就是最小化每个MBR矩形，这样查询的时候发生的相交情况会越少，查询的分支就越少，查询效率越高。
在这里插入图片描述

1.1.4. 全文索引

在“B+Tree索引”中，如果执行“SELECT * FROM 表名 WHERE 字段 LIKE %xxxx%”语句时，那么索引会失效，这个时候可以用“全文索引”解决这个问题（注意：“全文检索”需要使用MATCH AGAINST关键字）。“全文索引”采用“倒排索引”设计，使用分词器（可自定义）将一个文档内容进行拆分成多个关键字，索引中的每个关键字都指向出现该单词的文档内容。

1.2. 物理存储角度

按叶子节点和数据是否在一起可分为：“聚簇索引”（“聚集索引”）、“非聚簇索引”（“非聚集索引”）。

1.2.1. 聚簇索引

索引结构上的叶子节点和数据一起存储的叫做“聚簇索引”（“聚集索引”）。可以发现，索引存储顺序和数据存储顺序是一致的。
在这里插入图片描述

1.2.2. 非聚簇索引

索引结构上的叶子节点和数据分开存储的叫做“非聚簇索引”（“非聚集索引”）。可以发现，索引存储顺序和数据存储顺序不一定一致，且索引和数据分开独立存储的。
在这里插入图片描述

1.3. 是否主键角度

1.3.1. 主键索引

每张表都必须有且仅有一个“主键索引”，如果建表时没有指定“主键索引”，则会自动生成一个隐藏的字段作为“主键索引”。
在这里插入图片描述

1.3.2. 辅助索引

所有的非“主键索引”都叫做“辅助索引”（“二级索引”）。

InnoDB引擎“辅助索引”上的叶子节点存储的则是主键值，通过“辅助索引”先检索到主键值，然后再到“主键索引”上去回表检索到对应的数据。

注意：如果“组合索引”的索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么InnoDB里针对使用辅助索引的查询场景做了检索优化即“覆盖索引”检索（“覆盖索引”是一种检索方式，而不是索引类型）。
MyISAM引擎“辅助索引”上的叶子节点存储的则是指针地址，通过“辅助索引”先检索到指针地址，然后再根据指针地址直接检索到对应的数据。