MySQL索引:提升数据库性能的关键利器
今日目标
理解MySQL索引的概念、类型、结构
今日内容介绍,预计花费12分钟,最后有三个Mysql面试题

为了提高数据库的性能和查询效率,MySQL索引是一个不可或缺的工具。本文将深入探讨MySQL索引的概念、类型、结构。
什么是MySQL索引?
MySQL索引是一种数据结构,用于快速查找数据库表中的数据。它类似于一本书的目录,可以快速指引您到目标数据,而无需扫描整本书。索引通过维护数据值和其在表中位置之间的映射关系,加速了数据的检索操作。
1. Mysql索引介绍
索引(index) 是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足 特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构 上实现高级查找算法,这种数据结构就是索引。通过使用索引,数据库可以快速定位和检索所需的数据行,而无需扫描整个表。
1.1. 索引优缺点
索引优点:
-
1.提高检索速度,降低数据库的IO成本 -
2.通过索引进行排序,降低数据排序的成本,降低CPU的消耗
索引缺点:
-
1.索引占用空间 -
2.提高了查询的速度,降低了数据更新(如表进行INSERT、UPDATE、DELETE)的速度
如果您觉得本文不错,欢迎关注,点赞,收藏支持,您的关注是我坚持的动力!
2. Mysql索引结构
2.1. 索引结构概述
MySQL支持多种类型的索引,包括:
-
B-Tree索引:B-Tree(平衡树)索引是最常见的索引类型。它适用于等值查找、范围查找和排序操作。MySQL的InnoDB存储引擎默认使用B-Tree索引。
-
哈希(Hash)索引:哈希索引适用于等值查找,但不支持范围查找或排序。它在某些特定情况下可以提供非常快的查找速度。
-
Full-text(全文索引):是一种通过建立倒排索引,快速匹配文档的方式,用于全文搜索,允许对文本数据进行全文搜索和匹配。MySQL的全文索引通常用于MyISAM存储引擎。
-
R-tree(空间索引):空间索引用于地理数据的查询,支持距离计算和空间查询。MySQL的InnoDB存储引擎支持空间索引。
2.2. 存储引擎支持索引结构对比
不同的存储引擎对于索引结构的支持情况
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B-Tree索引 | 支持 | 支持 | 支持 |
Hash(哈希)索引 | 不支持 | 不支持 | 支持 |
R-tree(空间索引) | 不支持 | 支持 | 不支持 |
Full-text(全文索引) | 5.6版本之后支持 | 支持 | 不支持 |
注意:我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。
2.3. 二叉树
假如说MySQL的索引结构采用二叉树的数据结构,比较理想的结构如下:

在极端情况下,二叉树会形成一个单向链表,结构如下:

2.3.1. 二叉树作为索引结构缺点
-
1.如果数据是有序的,则会顺序插入,形成链表,查询效率就降低 -
2.如果数据量比较大,层级就比较深,检索效率就慢
2.3.2. 红黑树
考虑到二叉树有这些缺点后,我们可能就会考虑使用二叉树进阶版红黑树,红黑树是一个自由平衡二叉树,解决了是顺序插入数据形成单向链表的缺点,最终形成的数据结构也是一颗平衡的二叉树,数据结构如下:

虽然解决单向列表缺点,但是还是存在缺点:
如果数据量比较大,层级就较深,检索效率就慢
索引在Mysql中索引结构,没有使用二叉树或者红黑树,使用的是B+Tree
思考:什么事B+Tree?
在了解什么事B+Tree之前,要先介绍B-Tree
3. B-Tree
B-Tree是多叉路平衡树,和二叉树相比,B-Tree每个节点有多个分支,即多叉,具有一下特点:
-
1.多叉树结构:B-Tree是一种多叉树,每个内部节点可以有多个子节点,使得B-Tree能够有效地存储和管理大量数据。
-
2.平衡性:B-Tree具有平衡性,这意味着从根节点到叶子节点的任何路径的长度几乎相等。这确保了在平均情况下,对树的操作(插入、删除、查找)具有稳定的性能,不会出现严重的性能退化。
-
3.按序存储:B-Tree内的节点和数据通常按照键的顺序存储,这有助于范围查询和排序操作的高效执行。
-
4.分层结构:B-Tree具有分层结构,从根节点到叶子节点有多个级别。
-
5.高度平衡:B-Tree的高度通常是相对较低,这是因为它的平衡性和多叉性质。高度平衡有助于减少查找操作所需的磁盘访问。
3.1. Mysql索引B-Tree
一般来说,数据库的存储引擎都是采用 B 树或者 B+树来实现索引的存储。首先来看 B 树

B-Tree是一种多路平衡树,用这种存储结构来存储大量数据,B-Tree整个高度会相比二叉树来说,会矮很多。
对数据库而言,所有的数据都将会保存到磁盘上,磁盘 I/O 的效率又比较低,特别是在随机磁盘 I/O 的情况下效率更低。
所以 高度决定了磁盘 I/O 的次数,磁盘 I/O 次数越少,对于性能的提升就越大,这也是为什么采用 B 树作为索引存储结构的原因
注意:B-Tree中,非叶子节点和叶子节点都可以存放数据。
4. B+Tree
MySQL 的 InnoDB 存储引擎,它用了一种增强的 B-Tree结构,也就是 B+Tree来作为索引和数据的存储结构。相比较于 B-Tree结构来说,B+Tree做了两个方面的优化,如图所示。

可以看到两部分:
-
1.绿色框部分,索引部分,是非叶子结点,仅仅起到索引数据的作用,不存储数据。 -
2.红色框部分,数据存储部分,是叶子结点,叶子节点中要存储具体的数据。 -
3.MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序
4.1. B-Tree和B+Tree区别
-
1.从磁盘 I/O 效率方面来看:B+树的非叶子节点不存储数据,所以树的每一层就能够存储更多的索引数 量,也就是说,B+树在层高相同的情况下,比 B 树的存储数据量更多,间接会减少磁盘 I/O 的次数。 -
2.从范围查询效率方面来看:在 MySQL 中,范围查询是一个比较常用的操作,而 B+树的所有存储在叶 子节点的数据使用了双向链表来关联,所以 B+树在查询的时候只需查两个节点进行遍历就行,而 B 树需 要获取所有节点,因此,B+树在范围查询上效率更高。 -
3.从全表扫描方面来看:因为,B+树的叶子节点存储所有数据,所以 B+树的全局扫描能力更强一些,因 为它只需要扫描叶子节点。而 B 树需要遍历整个树。 -
4.从自增 ID 方面来看:基于 B+树的这样一种数据结构,如果采用自增的整型数据作为主键,还能更好避免增加数据的时候,带来叶子节点分裂导致的大量运算的问题。
5. Hash索引
MySQL中除了支持B+Tree索引,还支持一种索引类型—Hash索引
5.1. Hash结构
Hash索引:哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在 hash表中,哈希索引适用于等值查找,但不支持范围查找或排序。它在某些特定情况下可以提供非常快的查找速度
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可 以通过链表来解决。

5.2. Hash索引特点
-
1.Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…) -
2.无法利用索引完成排序操作 -
3.查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,Hash索引效率通常要高于B+tree索引
6. 索引分类
MySQL将索引类型分为以下几类:
-
主键索引: 数据列不允许重复,不允许为 NULL,一个表默认自动创建, 只能 有一个。 -
唯一索引: 数据列不允许重复,允许为 NULL 值,一个表允许多个列创建唯一索 引。 -
普通索引: 基本的索引类型,没有唯一性的限制,允许为 NULL 值,一个表允许有多个普通索引。 -
全文索引:是目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索,一个表允许有多个全文索引。 -
覆盖索引:查询列要被所建的索引覆盖,不必读取数据行 -
组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并
7. 聚集索引和二级索引
而在在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(ClusteredIndex ) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级(辅助)索引(SecondaryIndex) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
7.1. 聚集索引选取步骤
-
1.如果存在主键,主键索引就是聚集索引 -
2.如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引 -
3.如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
7.2. 聚集索引和二级索引结构介绍
-
聚集索引的叶子节点下挂的是这一行的数据,聚集索引就是基于主键创建的索引 -
二级索引的叶子节点下挂的是该字段值对应的主键值,除了主键索引以外的其他索引,称 为非聚集索引,也叫做二级索引
7.3. 分析Sql语句查找过程
执行如下的SQL语句
select * from tb_user where name='王五'
查找过程如下:
-
1.由于是根据name字段进行查询,所以先根据name=’王五’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 ‘王五’ 对应的主键值 19 -
2.由于查询返回的数据是 *,所以此时,还需要根据主键值19,到聚集索引中查找19对应的记录,最终找到19对应的行row。 -
3.最终拿到这一行的数据,直接返回即可
回表查询:这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取 数据的方式,就称之为回表查询。
面试题1: 为什么InnoDB存储引擎选择使用B+tree索引结构
-
1.相比较于二叉树,B+Tree层级少,搜索效率高 -
2.相比较于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低 -
3.相比较于Hash索引,B+Tree支持范围查询,Hash索引只能用于对等比较(=,in)
面试题2: 两条SQL语句,那个执行效率高
下面两句sql,谁的执行效率高,为什么?
A select * from tb_user where id = 19 ;
B. select * from tb_user where name = '王五' ;
A的执行效率高于B Sql执行下来:因为A语句之间使用聚簇索引,之间返回数据,B语句要先查询name=’王五’字段的二级查询,然后在使用聚簇索引查询。也就是回表查询
面试题3: InnoDB主键索引的B+tree高度为多高
如图,假设一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空 间,主键即使为bigint,占用字节数为8
B+Tree高度为2:
假设索引(键)的数量为 n
n * 8 + (n + 1) * 6 = 16*1024
算出n约为 1170
指针数= 索引数+1
n+1 就是指针的数量 1171
一个指针对应一页,一页可以有16行数据
1171 * 16 = 18736 行数据,
得出当B+树高度为2的时候可以存储18736 行数据
B+Tree高度为3:
1171 * 1171 * 16 = 21939856
也就是说,如果树的高度为3,则可以存储 2200w 左右的记录。

如果您觉得本文不错,欢迎关注,点赞,收藏支持,您的关注是我坚持的动力!
原创不易,转载请注明出处,感谢支持!如果本文对您有用,欢迎转发分享!
原文始发于微信公众号(springboot葵花宝典):MySQL索引:提升数据库性能的关键利器
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之家整理,本文链接:https://www.bmabk.com/index.php/post/182783.html