文章目录
MySQL之索引
注:本文基于Linux系统上MySQL v8.0.26进行讲解
1.索引概念
索引是存储引擎用于提高数据库表的访问速度的一种数据结构
索引是一个文件,它是要占据物理空间的,索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针;
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据,索引的实现通常使用 B 树及其变种 B+树;
更通俗的说,索引就相当于目录,为了方便查找书中的内容,通过对内容建立索引形成目录;
注:在数据库中除了要保存原始数据外,数据据还需要去维护索引这种结构,通过索引这种数据结构来指向原始数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
并且索引不是越多越好。合理的索引可以提高存储引擎对数据的查询效率。
形象一点来说呢,索引跟书本的目录一样,能否快速的查找到你需要的信息,取决于你设计的目录是否合理
2.索引优缺点
索引的优点
*可以大大加快数据的检索速度,降低数据库的IO成本,这也是创建索引的最主要的原因;
*通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能
*为用来排序或者是分组的字段添加索引,索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗可以加快分组和排序的速度
*加快表与表之间的连接
索引的缺点
*时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;即索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。
*空间方面:建立索引需要占用物理空间
3.索引作用
数据是存储在磁盘上的,查询数据时,如果没有索引,会加载所有的数据到内存,依次进行检索,读取磁盘次数较多。有了索引,就不需要加载所有数据,因为B+树的高度一般在2-4层,最多只需要读取2-4次磁盘,查询速度大大提升。
4.索引数据结构分类
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,下述是MySQL中所支持的所有的索引结构
B+Tree索引:
最常见的索引类型,大部分引擎都支持 B+ 树索引
Hash索引:
1.底层数据结构是用哈希表实现的, 性能很高,查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引
2.Hash索引只能用于对等比较(=,in),即只有精确匹配索引列的查询才有效, 不支持范围查询(between,>,< ,…)
3.无法利用索引完成排序操作
4.在MySQL中,支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能,hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
R-tree(空间索引):
空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引):
是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES
5.无索引
在无索引情况下,就需要从第一行开始扫描,一直扫描到最后一行,我们称之为 全表扫描,性能很低。
6.二叉树索引(假如)
选择二叉树作为索引结构,会存在以下缺点:
*顺序插入时,会形成一个链表,查询性能大大降低。
*二叉树只能有两个节点,大数据量情况下,层级较深,检索速度慢。
在主键不是顺序插入的二叉树中,假如我们要找17.则要查找4次,在第四次时找到
在主键不是顺序插入的二叉树中,假如我们要找17.则要查找4次,要找很多次,相当于遍历链表
如果我们针对于这张表建立了索引,假设索引结构就是二叉树,那么也就意味着,会对age这个字段建
立一个二叉树的索引结构,此时我们在进行查询时,只需要扫描三次就可以找到数据了,极大的提高的查询的效率。
备注: 这里我们只是假设索引的结构是二叉树,介绍一下索引的大概原理,只是一个示意图,并不是索引的真实结构
7.红黑树索引(假如)
即使解决了"顺序插入形成链表的问题",由于红黑树也是一颗二叉树,所以也会存在一个缺点:
大数据量情况下,层级较深,检索速度慢。
8.B+树索引
8.1标准B+Tree
B+Tree是B-Tree的变种,我们以一颗最大度数(max-degree)为3(3阶)的b+tree为例
B+Tree 与 B-Tree相比,主要有以下三点区别:
*B+Tree 所有的数据都会出现在叶子节点。
*B+Tree 叶子节点形成一个单向链表。
*B+Tree 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的
来看一下标准的B+Tree的数据结构示意图:
我们可以看到,两部分:
绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。
900
900在向上裂变后,在叶子节点也有,而且叶子节点形成了一个单向列表
556
780
35
215
1200
234
888
158
90
1000
88
120
268
250
8.2优化B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化,:
1)在原B+Tree的基础上,叶子节点增加了顺序访问指针,即增加一个指向相邻叶子节点的链表指针,每个叶子节点都指向相邻的叶子节点的地址,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序,叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表
2)每一个节点都是存储在数据块当中的,数据块也叫"页"
所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
所有的非终端结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字。
B+ 树中,数据对象的插入和删除仅在叶节点上进行。
B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点。
进行范围查找时只需要查找两个节点,进行遍历即可。而B-Tree需要获取所有节点,相比之下B+Tree效率更高。
B+树的磁盘读写代价更低:B+树的内部节点并没有指向关键字具体信息的指针。
由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引;
B+树的最底层叶子节点包含了所有的索引项。从图上可以看到,B+树在查找数据的时候,由于数据都存放在最底层的叶子节点上,所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系,相对于B树来说,B+树的树高理论上情况下是比B树要矮的。
可以看到B+树可以保证等值和范围查询的快速查找,MySQL的索引就采用了B+树的数据结构。
MySQL中优化之后的B+Tree如下,如下一个小黄框代表一个页
等值查询:
假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。
第一次磁盘IO:将磁盘块1加载到内存中,在内存中从头遍历比较,9<15,走左路,到磁盘寻址磁盘块2。
第二次磁盘IO:将磁盘块2加载到内存中,在内存中从头遍历比较,7<9<12,到磁盘中寻址定位到磁盘块6。
第三次磁盘IO:将磁盘块6加载到内存中,在内存中从头遍历比较,在第三个索引中找到9,取出data,如果data存储的行记录,取出data,查询结束。如果存储的是磁盘地址,还需要根据磁盘地址到磁盘中取出数据,查询终止。(这里需要区分的是在InnoDB中Data存储的为行数据,而MyIsam中存储的是磁盘地址。
过程如图:
范围查询:
假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。
首先查找值等于9的数据,将值等于9的数据缓存到结果集,这一步和前面等值查询流程一样,发生了三次磁盘IO。
我们从磁盘块6,键值9开始向后遍历筛选所有符合筛选条件的数据。
第四次磁盘IO:根据磁盘6后继指针到磁盘中寻址定位到磁盘块7,将磁盘7加载到内存中,在内存中从头遍历比较,9<25<26,9<26<=26,将data缓存到结果集。
主键具备唯一性(后面不会有<=26的数据),不需再向后查找,查询终止。将结果集返回给用户。
9.哈希索引
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在
hash表中;
对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),并且Hash索引将所有的哈希码存储在索引中,同时在索引表中保存指向每个数据行的指针
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可
以通过链表来解决。
哈希索引虽然可以快速定位,但是没有顺序,IO复杂度高;
基于Hash表实现,只有Memory存储引擎显式支持哈希索引 ;
适合等值查询,如=、in()、<=>,不支持范围查询,即使范围查询的话只能扫描全表方式 ;
因为不是按照索引值顺序存储的,就不能像B+Tree索引一样利用索引完成排序 ;
Hash索引在查询等值时非常快;
因为Hash索引始终索引的所有列的全部内容,所以不支持部分索引列的匹配查找 ;
如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题 。
10.空间索引
MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
11.不同引擎支持的索引结构
在 InnoDB 引擎中有三种索引:
B-Tree 索引
哈希索引
全文索引(5.6b版本之后支持)
InnoDB引擎默认的索引类型为B+树索引
注意:
有的说InnoDB支持哈希索引,有的说不支持,到底哪个是正确的呢?
对于InnoDB的哈希索引,确切的应该这么说:
(1)InnoDB用户无法手动创建哈希索引,这一层上说,InnoDB确实不支持哈希索引;
(2)InnoDB会自调优(self-tuning),如果判定建立自适应哈希索引(Adaptive Hash Index, AHI),能够提升查询效率,InnoDB自己会建立相关哈希索引,这一层上说,InnoDB又是支持哈希索引的;
12.B树(B-树)(多路平衡查找树)
12.1介绍
假如key为bigint=8字节,每个节点有两个指针,每个指针为4个字节,一个节点占用的空间16个字节(8+4*2=16)。
因为在MySQL的InnoDB存储引擎一次IO会读取的一页(默认一页16K)的数据量,而二叉树一次IO有效数据量只有16字节(为了方便讲解,忽略key所在的节点中key所对应的数据的占用字节),空间利用率极低。
为了最大化利用一次IO空间,一个简单的想法是在每个节点存储多个元素,在每个节点尽可能多的存储数据。这样就将二叉树改造成了多叉树,通过增加树的叉树,将树从高瘦变为矮胖。
B-Tree能加快数据的访问速度,因为存储引擎不再需要进行全表扫描来获取数据,数据分布在各个节点之中
B-Tree,B树是一种多叉路衡查找树,多路是指可以有多个节点,相对于二叉树,B树每个节点可以有多个分支,即多叉。
B树等值查询:
假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。
第一次磁盘IO:将磁盘块1加载到内存中,在内存中从头遍历比较,10<15,走左路,到磁盘寻址磁盘块2。
第二次磁盘IO:将磁盘块2加载到内存中,在内存中从头遍历比较,7<10,到磁盘中寻址定位到磁盘块5。
第三次磁盘IO:将磁盘块5加载到内存中,在内存中从头遍历比较,10=10,找到10,取出data,如果data存储的行记录,取出data,查询结束。如果存储的是磁盘地址,还需要根据磁盘地址到磁盘中取出数据,查询终止。
相比红黑树,在整个查找过程中,虽然数据的比较次数并没有明显减少,但是磁盘IO次数会大大减少。B树的高度一般2至3层就能满足大部分的应用场景,所以使用B树构建索引可以很好的提升查询的效率。
但是B树也有缺点,B树不支持范围查询的快速查找,你想想这么一个情况如果我们想要查找10和35之间的数据,查找到15之后,需要回到根节点重新遍历查找,需要从根节点进行多次遍历,查询效率有待提高。
如果data存储的是行记录,行的大小随着列数的增多,所占空间会变大。这时,一个页中可存储的数据量就会变少,树相应就会变高,磁盘IO次数就会变大。
过程如下图:
以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树最多有5个节点,每个节点最多存储4个key,5个指针(4个key对应5个指针),这5个指针分别指向的是它下面的子节点:
注意:树的度数指的是一个节点的子节点个数。
特点::
*5阶的B树,每一个节点最多存储4个key,对应5个指针(指针数由key数决定,指针=key数量+1)。
*一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。
*在B树中,非叶子节点和叶子节点都可以存放数据。
小于20走第一个指针,20到30之间走第2个指针,30-62之间走第三个指针,62到98之间走第四个指针,大于89的走第五个指针
插入900
插入556
插入780
插入35
插入25
插入1200
插入234
插入888
插入158
插入90
插入1000
插入88
插入120
插入268
插入250
12.2定义
为什么会有m/2这个最低限制
因为要保证它是一棵“树” 。 如果没有这个最低限制,比如只有一个子节点,它就是链表,链表又分为 单链表、双端链表、双向链表、无序链表。 如果只有两个子节点,它就是二叉树,二叉树里有经典的有平衡二叉对,红黑树等。即便是树,也有B-树,B+树,B*树。不同的数据结构要采取不同的方式来处理。
键是每个节点最多可以存储的值 的个数
12.3搜索
12.4插入
12.5删除
13.为什么InnoDB存储引擎选择使用B+tree索引结构
为什么InnoDB存储引擎选择使用B+tree索引结构,而没有选则二叉树、红黑树、 B树、哈希索引?
A. 二叉树、红黑树:
二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。
红黑树:树的高度随着数据量增加而增加,IO代价高。
相对于二叉树与红黑树,层级更少,搜索效率高;
B. B树:
一个节点最终是通过磁盘块或者以页来存放的,一个节点存储在一个页上,一页得大小是固定的,是16K。
对于B+树来说只有叶子节点才会数据,然后上面的节点仅仅起到索引的作用,它不存放数据,那么这一页中可以存放的key与指针就会增多了,key与指针增多了,那么最终相同数据量的情况下它的层级将会更少;
如果对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,相同数据量得情况下层级更深,要同样保存大量数据,只能增加树的高度,导致性能降低;
而且如果采用的是B+树结构,不管查找的是哪个数据,都要到叶子节点中才能找到对应的数据,此时搜索效率稳定,而且B+树中叶子节点形成了一个双向链表,那么这是方便范围搜索与排序的;
C. Hash索引:
Hash索引虽然可以快速定位,但是没有顺序,IO复杂度高;
B+tree索引相对Hash索引,B+tree支持范围匹配及排序操作;
不使用平衡二叉树的原因如下:
最大原因:深度太大(因为一个节点最多只有2个子节点),一次查询需要的I/O复杂度为O(lgN),而b+tree只需要O(logmN),而其出度m非常大,其深度一般不会超过4
平衡二叉树逻辑上很近的父子节点,物理上可能很远,无法充分发挥磁盘顺序读和预读的高效特性。
14.查看索引命令
1.Table: 表名
2.Non_unique: 如果索引不能包括重复值则为0,如果可以则为1。也就是平时所说的唯一索引。
3.Key_name 索引名称,如果名字相同则表明是同一个索引,而并不是重复,比如上图中的第四、五条数据,索引名称都是name,其实是一个联合索引。
4.Seq_in_index 索引中的列序列号,从1开始。上图中的四、五条数据,Seq_in_index一个是1一个是2,就是表明在联合索引中的顺序,我们就能推断出联合索引中索引的前后顺序。
5.Column_name 索引的列名。
6.Collation指的是列以什么方式存储在索引中,大概意思就是字符序。
7.Cardinality 是基数的意思,表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引,所以我们一般都是根据Cardinality来判断索引是否具有高选择性,如果这个值非常小,那就需要重新评估这个字段是否适合建立索引。
8.Sub_part 前置索引的意思,如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。
9.Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。
10.Null 如果列含有NULL,则含有YES。
11.Index_type表示索引类型,Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。
12.Comment Index_comment 注释的意思。
加上\G后看起来会更方便
SHOW INDEX FROM table_name ;
SHOW INDEX FROM table_name\G;
15.MyISAM 索引与 InnoDB 索引的区别
*InnoDB 索引是聚簇索引,MyISAM 索引是非聚簇索引
*InnoDB 的主键索引的叶子节点存储着行数据,因此主键索引非常高效
*MyISAM 索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据
*InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效
16.使用索引查询一定能提高查询的性能吗
通常,通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的 INSERT,DELETE,UPDATE
将为此多付出 4,5 次的磁盘 I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
*基于一个范围的检索,一般查询返回结果集小于表中记录数的 30%
*基于非唯一性索引的检索