【MySQL】8.0 索引

索引

索引概述

介绍

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
在这里插入图片描述

特点

优势劣势
提高数据检索的效率,降低数据库的IO成本索引列也是要占用空间的。
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。

索引结构

概述

MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:

索引结构描述
B+Tree索引最常见的索引类型,大部分引擎都支持 B+ 树索引
Hash索引底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引)是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

上述是MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持情况。

索引InnoDBMyISAMMemory
B+tree索引支持支持支持
Hash 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

注意: 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。

二叉树

假如说MySQL的索引结构采用二叉树的数据结构,比较理想的结构如下:
在这里插入图片描述
如果主键是顺序插入的,则会形成一个单向链表,结构如下:
在这里插入图片描述
所以,如果选择二叉树作为索引结构,会存在以下缺点:

  • 顺序插入时,会形成一个链表,查询性能大大降低。
  • 大数据量情况下,层级较深,检索速度慢。

此时大家可能会想到,我们可以选择红黑树,红黑树是一颗自平衡二叉树,那这样即使是顺序插入数据,最终形成的数据结构也是一颗平衡的二叉树,结构如下:
在这里插入图片描述
但是,即使如此,由于红黑树也是一颗二叉树,所以也会存在一个缺点:

  • 大数据量情况下,层级较深,检索速度慢。

所以,在MySQL的索引结构中,并没有选择二叉树或者红黑树,而选择的是B+Tree,那么什么是
B+Tree呢?在详解B+Tree之前,先来介绍一个B-Tree。

B-Tree

B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。
以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key,5
个指针:
在这里插入图片描述

知识: 树的度数指的是一个节点的子节点个数。

B+Tree

B+Tree是B-Tree的变种,我们以一颗最大度数(max-degree)为4(4阶)的b+tree为例,来看一
下其结构示意图:
在这里插入图片描述
我们可以看到,两部分:

  • 绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
  • 红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。

最终我们看到,B+Tree 与 B-Tree相比,主要有以下三点区别:

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。

上述我们所看到的结构是标准的B+Tree的数据结构,接下来,我们再来看看MySQL中优化之后的
B+Tree。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
在这里插入图片描述

Hash

MySQL中除了支持B+Tree索引,还支持一种索引类型—Hash索引。

1). 结构

哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中

如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。

2). 特点

  • Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…)
  • 无法利用索引完成排序操作
  • 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引

3). 存储引擎支持

在MySQL中,支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能,hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

思考题: 为什么InnoDB存储引擎选择使用B+tree索引结构?

A. 相对于二叉树,层级更少,搜索效率高;
B. 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储> 的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
C. 相对Hash索引,B+tree支持范围匹配及排序操作;

索引分类

索引分类

在MySQL数据库,将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建, 只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词,而不是比较索引中的值可以有多个FULLTEXT

聚集索引&二级索引

而在在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:

分类含义特点
聚集索引(ClusteredIndex)将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据必须有,而且只有一个
二级索引(SecondaryIndex)将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键可以存在多个

聚集索引选取规则:

  • 如果存在主键,主键索引就是聚集索引。
  • 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
  • 如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索 引。

聚集索引和二级索引的具体结构如下:
在这里插入图片描述

  • 聚集索引的叶子节点下挂的是这一行的数据 。
  • 二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来,我们来分析一下,当我们执行如下的SQL语句时,具体的查找过程是什么样子的。
在这里插入图片描述
具体过程如下:

①. 由于是根据name字段进行查询,所以先根据name='Arm’到name字段的二级索引中进行匹配查
找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最终找到10对应的行row。

③. 最终拿到这一行的数据,直接返回即可。

回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取 数据的方式,就称之为回表查询。

思考题:
以下两条SQL语句,那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = ‘Arm’ ;
备注: id为主键,name字段创建的有索引;

解答:
A 语句的执行性能要高于B 语句。

因为A语句直接走聚集索引,直接返回数据。 而B语句需要先查询name字段的二级索引,然
后再查询聚集索引,也就是需要进行回表查询。

思考题:
InnoDB主键索引的B+tree高度为多高呢?
在这里插入图片描述

假设:
一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空 间,主键即使为bigint,占用字节数为8。

高度为2:
n * 8 + (n + 1) * 6 = 161024 , 算出n约为 1170
1171
16 = 18736
也就是说,如果树的高度为2,则可以存储 18000 多条记录。
高度为3:
1171 * 1171 * 16 = 21939856
也就是说,如果树的高度为3,则可以存储 2200w 左右的记录。

索引语法

1). 创建索引

create [ UNIQUE | FULLTEXT ] index index_name on table_name (
index_col_name,... ) ;

2). 查看索引

show index from table_name ;

3). 删除索引

drop index index_name on table_name ;
  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值