MySQL数据库——索引01

最新推荐文章于 2024-10-13 21:08:42 发布

Cerberus冲鸭

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量1.5k

点赞数 1

分类专栏：数据库 MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43595854/article/details/123784620

版权

数据库同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

索引

1.索引的数据结构

1.1 为什么使用索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。索引的作用就相当于目录的作用。目的是为了减少磁盘I/O次数，加快查询速率。

1.2 索引及其优缺点

索引概述：

官方定义：索引是帮助MySQL高效获取数据的数据结构。
索引本质：索引是数据结构，排好序的快速查找数据结构，满足特定查找算法。
索引实现：索引是在存储引擎中实现的，因此每种存储引擎不一定完全相同。

优点：

提升数据检索的效率，降低数据库的IO成本
通过创建唯一索引，可以保证数据库表中每一行的数据的唯一性。（约束）
在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。（外键）
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。（索引有序的）

缺点：

创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

PS：索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

1.3 InnoDB中索引的推演

在一个页中的查找：

以主键为搜索条件

可以在页目录中使用二分法快速定位代对应的槽，然后再便利该槽对应分组中的记录即可快速找到指定的记录。
以其他列作为搜素条件

因为在数据页中并没有对非主键列建立所谓的页目录，所以无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符号搜素条件。很显然，这种查找的效率是非常低的。

在很多页中查找：

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话分为两个步骤：

1.定位到记录所在页。

2.从所在页中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查询方式去查找指定的记录。因为要遍历所有的数据页，所有这种方式显然是超级耗时的。此时索引应运而生。

设计索引：

建一个表：
```
mysql>CREATE TABLE index_demo(
	->	c1 INT,
	->	c2 INT,
	->	c3 CHAR(1),
	->	PRIMARY KEY(c1)
	-> ) ROW_FORMAT = Compact;
```
这个新建的index_demo表中有两个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用Compact行格式来实际存储记录的。简化的index_demo表的行格式示意图：
- record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记录、3表示最大记录、1暂时还没用过。
- next_record：记录头信息的一下属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表示下一条记录是谁。
- 各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
- 其他信息：除了上述3种信息意外的所有信息，包括其他隐藏列的值以及记录的额外信息。
把一些记录放到页里的示意图就是：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q1aM0rK7-1648395593941)(https://cdn.jsdelivr.net/gh/CerberusDong/pictures/image-20220327225554987.png)]
1. 一个简单的索引设计方案
  
  因为各个页中的记录并没有规律，我们并不知道我们的搜素条件匹配哪些页种的记录，所以不得不依次遍历所有的数据页。所有我们为了快速的定位到需要查找记录在哪些数据页中，我们可以为快速定位记录所在的数据页而建立一个目录，建这个目录必须完成下边这些事：
  - 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
    
    假设一个数据页最多存放3条记录，接着向index_demo表插入3条记录：
```
mysql> INSERT INTO index_demo VALUES(1, 4, 'u'),(3, 9, 'd'),(5, 3, 'y');
```
    那么这些记录已经按照主键值的大小串联成一个单向链表了，如图所示：
    
    再插入一条记录：
```
mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
```
    因为页10最多只能放3条数据，所以不得不再分配一个新页：
    
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4KzfYmaX-1648395593942)(https://cdn.jsdelivr.net/gh/CerberusDong/pictures/image-20220327231639486.png)]
    
    新分配的数据页编号可能并不是连续的，逻辑上连续。另外，页10中用户最大主键值是5，而页28中有一条记录的主键值是4，因为5 > 4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以再插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录涌动到页28中，然后再把主键值为4的记录插入到页10中，过程如下：
    
    在进行增删改操作过程中，必须通过一些诸如记录移动的操作来始终保证正状态一直成立：下一个数据页中记录用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程称为页分裂。
  - 给所有的页建立一个目录项。
    
    由于数据页的编号可能是不连续的，所以在向index_demo表中插入许多记录后，可能是这样的效果：
    
    因为这些16KB的页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所造的页，我们需要给它们做个目录，每个目录包括下边两个部分：
    
    页的用户记录中最小的主键值，我们用key来表示。
    
    页号，我们用page_on表示。
    
    目录如图所示：
    
    以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5.我们只需要把几个目录项物理存储器上连续存储，就看可以实现根据主键值快速查找某条记录的功能了。比如，查找主键值为20的记录，具体查找过程分为两步：
```
 1. 先从目录项中根据二分法快速确定主键值为20的记录在目录项3中（因为12 < 20 < 209），它对应的页是页9。
 2. 再根据前边说的再页中查找记录的方式去页9中定位具体的记录。
```
    至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引。