MySQL之索引

文章目录

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的数据结构示意图：

我们可以看到，两部分：

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。

红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

动态化操作标准B+树网站,选择Degree==5,插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88

120 268 250 ,然后观察一些数据插入过程中节点的变化情况

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 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7 第三次磁盘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 主键具备唯一性（后面不会有磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中MySQL 索引，在内存中从头遍历比较，10 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7 第三次磁盘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的走第五个指针

动态化操作B树网站,选择Degree==5,插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 然后观察一些数据插入过程中节点的变化情况

插入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%

*基于非唯一性索引的检索

（编辑：财气旺网 - 财气网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!