MySQL的索引原理

最新推荐文章于 2024-09-04 03:07:49 发布

月东坡半

最新推荐文章于 2024-09-04 03:07:49 发布

阅读量373

点赞数 1

分类专栏： JAVA后端 MySQL 文章标签：索引数据库 mysql

本文链接：https://blog.csdn.net/weixin_43415481/article/details/114900268

版权

JAVA后端同时被 2 个专栏收录

18 篇文章 1 订阅

订阅专栏

MySQL

6 篇文章 0 订阅

订阅专栏

JAVA后端开发知识总结（持续更新…）

MySQL的索引

文章目录

MySQL的索引

一、索引概述

1.1 索引基本概念及使用场景

索引（Index）——必须考虑磁盘IO：

帮助MySQL高效获取数据的数据结构，即：索引的本质为数据结构。数据库索引存储在磁盘上，一次磁盘IO对应加载一个磁盘页（节点），因此性能与树高挂钩。使用二叉搜索树可能出现退化的情况，使性能大大降低，因此使用二叉平衡树更好。
索引可以帮助服务器避免排序和临时表，将随机IO变为顺序IO，使得MySQL的检索效率变高，但是降低了更新表的速度，且占用空间高。

在这里插入图片描述

使用场景：

中到大数据量表适合使用索引；
对数据进行频繁查询应该建立索引，如果要频繁更改数据则不建议使用索引。
小数据量表，大部分情况全表扫描效率更高；
特大数据量表，建立和使用索引的代价会随之增大，适合使用分区或分库。

在这里插入图片描述

1.1.1 主键为什么要自增

因为自增的话 B+ 树直接往后面插入就行了，不是自增的话会往中间插入，往中间插入有可能会分裂索引节点，增加开销。
InnoDB引擎表是基于B+树的索引，所有的行记录都存储在B+树的叶子节点。如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。
使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致。
该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引，这时候InnoDB会选择内置的ROWID作为主键。

1.2 索引类型

1.2.1 逻辑分类

单值索引：最基本的索引，没有任何约束限制。

CREATE INDEX 索引名 on 表名(字段名)

唯一索引：和单值索引类似，但是具有唯一性约束，可以有 null。

CREATE UNIQUE 索引名 on 表名(字段名)

主键索引：特殊的唯一索引，不允许有 null，一张表最多一个主键索引。在设定为主键后，数据库会自动建立索引，InnoDB为聚簇索引。

PRIMARY KEY(字段名)

组合索引：多个列组成一个索引，用于进行组合搜索，通常效率大于索引合并。

CREATE INDEX 索引名 on 表名(字段名1，字段名2)

全文索引：对文本的内容进行分词、搜索。
覆盖索引：SELECT的列只需从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

1.2.2 数据结构分类

BTREE

BTREE索引将索引值按一定的算法，存入一个树形数据结构中，每次查询都是从树的入口root开始，依次遍历节点，获取叶子节点上的查询结果。这是MySQL默认和最常用的索引类型，InnoDB使用的即是B+Tree。由于B-Tree是对索引列进行顺序组织，所以很适合范围查找，也可以适用于ORDER BY。

HASH

由于HASH的唯一性及类似键值对的形式，很适合作为索引。目前，只有Memory引擎显式支持。对于索引列，Hash索引会计算出每行的hashCode，然后存放在Hash索引中。其数据结构是键值对，键存放每行的hashCode，值存放指向每行的指针。由于是按照hashCode排序，而非值顺序存储，所以不能用来进行排序。
InnoDB的自适应哈希索引，就是发现某些值被使用非常频繁时，会在B-Tree的基础上再创建哈希索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效仅能在 “=”、“<=>”和“in” 条件下实现，对于范围查询、排序及组合索引仍然效率不高。此外，出现哈希碰撞后，无法避免表扫描。

R-TREE（空间数据索引）

仅支持geometry数据类型，用作地理数据存储，MyISAM、BDb、InnoDb、NDb、Archive支持使用。

FULLTEXT

它查找的是文本的关键词，而不是直接比较索引的值。

二、BTREE

2.1 B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树，m阶B-树具有如下几个特征：

根结点至少有两个子女。
每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m。
每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m。
所有的叶子结点都位于同一层。
每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。
对于非叶子节点，内存中有key、数据和向下的指针。
下图是一个3阶的B-Tree（图片来源于此）：

在这里插入图片描述

初始化介绍：

一颗B-树，白色的块为磁盘块，一个磁盘块会包含几个数据项（紫色）和向下的指针（蓝色）。指针存储的是子节点所在磁盘块的地址，它们是被划分的区域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

查找过程：

在这里插入图片描述

上述过程总共需要3次磁盘I/O操作和3次内存查找操作，而内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。因此，三次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B-Tree的每个节点不仅包含数据的key值，还有data值，由于每一个页的存储空间有限，如果data较大会导致每个节点能存储的key的数量很小，进而导致B-Tree的深度变深，增大查询时的磁盘I/O次数，影响查询效率。

2.2 B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是基于B+Tree。m阶B+树具有如下几个特征：

有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
内存中只有key和向下的指针，没有指向数据的指针。每个节点占用空间变小，每次加载量变大，发生IO的总次数就变少。叶子节点之间也用指针相连。

在这里插入图片描述

2.3 两者异同

在这里插入图片描述

此外，对于范围查询，B-树只能依靠中序遍历，而B+树只需要查询到下限值，然后在叶子节点链表上进行遍历即可。

B+树的优势：

单一节点存储更多的元素，使得查询的IO次数更少。
所有查询都要查找到叶子节点，查询性能稳定。
所有叶子节点形成有序链表，便于范围查询。

B树：

当存储的每个实体数据量不大，并且要求查询速度快，多读的场景用b树。

三、MySQL的BTREE索引

3.1 索引的数据存储方式分类——聚簇索引与非聚簇索引

数据库中的B+Tree索引可以分为聚簇索引和非聚簇索引。聚簇索引的 叶子节点 存放的是整张表的行记录数据。而非聚簇索引与聚簇索引的区别在于非聚簇索引的叶子节点不包含行数据，而是存储指向对应数据块的指针。

对于InnoDB，当通过非聚簇索引来查询数据时，会遍历非聚簇索引找到主键，然后再通过主键在聚簇索引中找到完整的行记录数据。非聚簇索引在InnoDB中，又叫做二级索引或辅助索引。

聚簇索引

目前只有InnoDB支持聚簇索引；
每个MySQL的表只能对应一个聚簇索引，通常是该表的主键；
为了充分发挥聚簇索引的优势，最好使得主键列为有序的ID，而非无序的。

3.2 MySQL索引实现

MySQL索引使用的数据结构主要有BTree索引和哈希索引。MySQL的BTree索引使用的是B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

3.2.1 MyISAM

非聚簇索引：B+Tree叶节点的data域存放的是数据记录的地址指针。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。

MyISAM的主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。下面两图就展示了利用Col1作为主键索引、Col2作为辅助索引的情况（图片及案例来源在此）：

在这里插入图片描述

3.2.2 InnoDB及二级索引回表

聚集索引：数据文件本身就是索引文件，因此表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。该索引的key是数据表的主键，因此InnoDB表数据文件本身就是主键索引。如果没有指定主键，InnoDB 将会自动创建一个 6Byte 的自增主键。

非聚集索引：其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址。因此，根据主索引搜索时，直接找到key所在的节点即可取出数据。但在根据辅助索引查找时，则需要先取出主键的值，然后再查一遍主索引（二级索引回表）。因此，在设计表的时候，不建议使用过长的字段作为主键。