【数据库】3 存储结构、页、聚簇索引

最新推荐文章于 2023-09-06 21:29:47 发布

hancoder

最新推荐文章于 2023-09-06 21:29:47 发布

阅读量655

点赞数

分类专栏：数据库文章标签：聚簇索引存储结构页数据库

blog.csdn.net/hancoder 可以打赏后索要离线md文件，打赏码在谷粒笔记的文尾。博主目前就职于阿里云，有需要可内推

本文链接：https://blog.csdn.net/hancoder/article/details/105773153

版权

数据库专栏收录该内容

16 篇文章 5 订阅

订阅专栏

数据库相关文章：

数据库①：基础、事务、锁：https://blog.csdn.net/hancoder/article/details/105773038
数据库②：索引、调优、explain（尚硅谷笔记）https://blog.csdn.net/hancoder/article/details/105773095
数据库③：存储结构、页、聚簇索引 https://blog.csdn.net/hancoder/article/details/105773153

在文件系统中，Mysql将每个数据库（也可以称之为schema）保存在数据目录下的一个子目录。创建表时，mysql会在数据库子目录下创建一个和表同名的.frm文件（如user.frm）保存表的定义。

可以使用SHOW TABLE STATUS目录显示表的相关信息，会显示如下的内容

name：表名
engine（Type）：存储引擎。
row_format：行格式。对于MyISAM表，可选的值为Dynamic、Fixed或者Compressed。
- Dynamic：行长度可变，一般包含可变长度的字段，如VARCHAR和BLOB
- Fixed：行长度固定，只包含固定长度的列，如CHAR和INTEGER
- Compressed：只在亚索表中存在
Rows：表中的行数。MyISAM是准确值，InnoDB是估计值。
AvgRowLength：平均每行包含的数据数
dataLength：表数据大小，字节为单位
maxDataLength：表数据的最大容量，该值和存储引擎有关
indexLength：索引的大小，字节为单位
dataFree：对于MyISAM代表已分配单没有使用的空间，这部分空间包括了之前删除的行以及可以被INSERT利用到的空间
autoIncrement：下一个AUTO_INCREMENT的值
createTime：创建时间
updateTime：表数据的最后更改时间。
checkTime：使用CHECK TABLE命令或者myisamchk工具最后一次检查表的时间
collation：表的默认字符集和字符列排序规则
checksum：如果启用，保存的是整个表的实时校验和
createOptions：创建表时的其他选项
Comment：对应MyISAM表，保存的是注释。对于InnoDB，保存的是InnoDB表空间的剩余空间信息。如果是一个视图，则该列包含"VIEW"的文本字样

1

MySQL数据库储存引擎Inoodb一–记录储存结构

我们可能有很多熟悉的数据库储存引擎，比如说Inoodb，MyISAM，Memory。每一种储存引擎对于数据的持久化可能是不同的，比如说我们的Memory储存引擎的数据都是不会写进磁盘的，所有的数据是保存在内存中的，也就意味着如果我们的服务器进行重启以后，数据是不会被进行保存的。当然，因为MySQL数据库默认的储存引擎是使用的Inoodb，所以我们在这里是需要重点介绍这个储存引擎的。

简介：

Inoodb储存引擎是把数据储存在磁盘里面的储存引擎，它在内存和磁盘的交互中使用的是页这个数据单位。我们都知道一个事情就是我们在对磁盘进行访问的时候速度是非常慢的，所以我们肯定是不能接受一条数据一条数据的进行取用。所有数据划分为若干页，一个数据页是可以保存16kb的数据，也就是说我们每次在进行数据访问的时候是一次性的16kb数据。

逻辑页依赖于不太的存储引擎，对于InnoDB为16KB

数据文件的组织形式：

散列文件：直接存取文件或哈希文件，利用哈希函数，将具有相同搜索码值的记录散列到外存（磁盘）的同地址范围中。
- 随机存放，插入删除方便，存取速度快，不需要索引去
- 只支持按搜索码的随机查询，不支持范围查询。哈希函数确定比较困难，可能会造成桶的偏斜。
聚簇文件：每块存储多个有关联的关系。
- 支持高效率的多表连接查询
- 降低单表查询的效率
按列存储：将一个大表拆分成多个小表

一、行格式：

知道了数据库中我们数据的大概储存方式，那么接下来我们需要做的就是学习一条数据在我们的数据库中是什么样的一个结构存在。我们把数据库储存数据的格式称之为行格式或者是记录格式，我们现在所使用的行格式有Compact，Redundant，Dynamic，Compressed。当然随着时间的迁移我们是会有更多的行格式出现。

设置行格式语法：在创建表时，在表外指定

ROW_FORMAT=行格式名称

1.1 Compact：

我们对编码格式进行了设置为ASCII，这个编码格式只能是储存空格，字母，标点符号，不可见字符，数字。所以汉字是不可以储存进来的！

create table test(
    C1 varchar(10),
    C2 varchar(10) not null,
    C3 char(10),
    C4 varchar(10)
)charset=ASCII row_format=COMPACT; -- 设置行格式

insert into test values('aaaa','bbb','cc','d'),('aaa','bb',NULL,NULL); -- 插入两行数据

结构示意图：如下图所示我们可以看到的是分为两个大部分，第一部分就是记录额外信息，第二部分就是记录真实数据，接下来我们对这两个部分进行详细的描述：

记录的额外信息：

①变长字段长度列表：

这个部分记录的是可变长字段的信息，比如说VARCHAR,VARBINARY,TEXT，BLOB数据类型。我们都知道这些可变长的数据类型是分为两部分的，第一种就是它们可以储存的数据最大值，第二种就是储存数据的真实大小。MySQL数据库也不知我们到底储存了多少内容，所以我们在变长字段长度列表里面是需要指出的。

我们以第一条数据为例，我们知道C1，C2，C4是可变长的varchar数据类型，C3(char)不是，所以我们在这里是需要记录三列的储存情况。因为我们采用的是ASCII字符集，所以一个字符我们需要一个字节进行编码。那么我来看看储存数据对应的长度表示：

注意：我们在变长字段长度列表里面进行长度保存的时候是要根据列对应的逆序记性保存，并且只保存值为非NULL的列，所以我们这条数据在变长字段长度列表的表示如下图所示：

CHAR类型数据储存：

我们在前面使用Compact行格式的提到过，比如我们的第一条数据的C3列因为是CHAR的数据类型，所以在变长数据长度列表里面是不进行储存的。但是在最后需要提出一点就是在那里我们采用的是定长的字符集ASCII，但是如果我们把字符集换成UTF8的话这一列数据还是会在变长数据长度列表里面进行储存的。

因为数据都较短，所以在变长字段长度列表里面我们使用一个字节对这些信息进行保存，所以我们会产生疑问，在这里保存每一列的信息的时候怎么判断是使用的一个字节还是两个字节呢？三个因素：

1>字符集储存一个字符使用字节M（我们采用的ASCII是1，UTF-8是3，GBK是2）

2>可以储存的位数W(VARCHAR(W))

3>真实储存的位数L

规则：

（1）当M * W < 256使用一个字节

（2）当M * W > 256：L小于128使用一个字节，L大于128使用两个字节。

②NULL值列表：

顾名思义这是用来储存除了NOT NULL、主键等关键字修饰的列的信息。因为我们的空值列如果进行储存的话也是需要消耗内存的，所以我们在这里进行记录，后面的真实数据就是不用进行保存的了。

我们用第二条数据为例：因为C2是不能为空的，所以我们需要记录的是C1，C2，C3的信息：

我们就来看看这个06数据到底是怎么一回事儿，这个值到底是怎么得到的：

1>如果对应列值为空，那么我们用1进行表示，如果不为空那么我们就用0进行表示，每个列对应一个二进制位。

2>和变长数据长度列表规则一致，我们必须是要使用列的逆序进行表示。

3>如果使用的二进制位不是字节的整数倍，那么我们是需要在高位进行补零操作的。

所以综合上述三条规则，我们是可以轻易的写出第二条数据在NULL列表里面的二进制位表示：0000 0110–>对应的十六进制就是：0x06，所以我们到这里就可以知道上面图片中的06是怎么得到的了。

③记录头信息：

记录头信息。这部分是5B×8bit=40个二进制位组成的，那么这40个二进制位分别代表了什么内容：

预留位1 一：没有进行使用

预留位2 一：没有进行使用

delete_mask 一：是否被删除

min_rec_mask 一：该记录是否为B+树中非叶子节点的最小记录

n_owned 四：当前嘈管理的记录数

heap_no 十三：当前数据在记录堆的位置

record type 三：0表示普通记录，1表示B+树非节点记录，2表示最小记录，3表示最大记录

next_record 十六：下一条记录的相对位置

上图就是我们两条记录对应的记录头信息，如果我们在这里记不住头信息的这些概念信息或者是看不懂上图，没关系，我们看一下就OK。后边会继续详细的讲解。

记录的真实数据：

④隐藏列

记录的真实数据除了我们插入的那些数据列之外，MySQL数据库还会帮我们自动生成三个列，也称之为隐藏列。隐藏列是MVCC的关键

注意：行id不是必须有的，是在我们没有进行主键指定的时候才生成的。我们的事务id和回滚指针才是每一条数据都会帮助我们进行添加的。我们不需要关心这三个列的数据添加，因为是MySQL自动帮我们进行添加的。

⑤个人列

我们看看加上真实数据以后，我们添加的两条记录的储存完整格式是什么情况：

注意：

1>在第一条数据中的C3列虽然真实储存的是 ‘cc’，但是我们定义的数据类型是char，所以需要进行完整的表示它定义的十个字符空间，剩下的八个用空格字符进行填充。

2>我们在第二条数据中看到的是C3，C4两个列是没有在真实数据中进行保存的，因为它们已经在NULL列表里面已经进行了储存声明，所以是不需要重复进行储存的。

3>上边的数据储存因为我们采用的是ASCII字符集，当然如果采用其他的字符集是会不一致的。

1.2 Redundant：

这个行格式是MySQL5.0之前的版本使用的行格式，非常古老，但是我们还是介绍一下。直接进行和Compact行格式比较：

结构示意图：

从结构示意图我们可以看出以下区别：

1>变长字段长度列表变成了–>字段长度偏移列表

2>少了NULL值列表

数据完整信息：　　　　

区别：

1>Redundant会把所有列都在字段长度偏移列表进行储存，包括隐藏列，当然顺序依然是逆序。

2>Redundant采用偏移量也就是相邻两列的差值进行储存。

第一列：row_id 六个字节 0x06
　　　　　　第二列：transaction_id 六个字节 0x0c - 0x06 = 0x06
　　　　　　第三列：roll_pointer 七个字节 0x13 - 0x0c = 0x07
　　　　　　。。。。。。。

以此类推我们就可以获取完整的储存信息

3>我们在第二条数据可以看到，在真实数据的位置我们是对空值的列进行了相应的用00进行替代保存。在Compact行格式里面我们是不会进行保存的。

记录头信息：

预留位1 一：没有进行使用
预留位2 一：没有进行使用
delete_mask  一：是否被删除
min_rec_mask 一：该记录是否为B+树中非叶子节点的最小记录
n_owned 四： 当前嘈管理的记录数
heap_no 十三：当前数据在记录堆的位置
n_field   十： 表示记录中列的数量
1byte_offs_flag 一：标记字段长度偏移列表中的偏移量是使用1字节还是2字节表示的
next_record 十六：下一条记录的相对位置

区别：

1>少了record_type这个属性

2>多了n_field和1byte_offs_flag这两个属性

行溢出数据：

我们在前面提到过一个问题，那就是我们MySQL数据库是采用页作为磁盘和内存的中间交换，一页可以储存16k的数据，那么如果我们的一条数据超过了这个内存大小，又会发生什么样的情况呢？其实在这里我觉得是没有必要关心多大的数据会发生行溢出的，如果有兴趣可以自行百度。在Compact行格式和Redundant中，对于这种特别大的数据的处理方式就是在真实数据的储存地方留下20个字节的内存用来储存指向下一页的地址。意思就是用几页进行储存，这些页之间的联系是使用20个字节内存进行维护。

1.3 Dynamic和Compressed：

这两个行格式和Compact是非常相似的，它们的区别就在于对于上面提出的行溢出的处理。Dynamic是使用所有的真实数据储存空间进行储存其它页面的地址，把所有的真实数据都储存在其它页面中。

相比于Dynamic来说，Compressed行格式的处理仅仅是加上了压缩算法进行压缩，节省空间。

上一篇博客回顾：

1：数据库拥有众多的储存引擎，现在主要使用的是Inoodb，这个储存引擎有Compact，Redundant，Dynamic，Compressed四种行格式

2：Compact行格式的结构分为变长数据长度列表，NULL值列表，记录头信息，真是数据储存

3：变长数据长度列表储存的是变长数据类型数据的字节数逆顺序，空值列不储存，NULL值列表储存非主键和没有被NOT NULL 修饰的列，二进制位逆顺序进行储存。

4：记录头信息包括了偏移量，槽数量，本组数据量，是否被删除，数据类型，是不是B+树子节点等等信息。

5：真实数据会有三个三个虚拟列，ROW_ID(没有主键的时候自动生成)，ROLL_POINTER,TRANSACTION_ID(事务管理ID)

5：行溢出数据的处理Compact行格式是使用最后记录下一页地址的方式，然而Redundant和Compressed是采用整页记录数据页地址的方式，后两者的Compressed采用压缩算法。

6：对于char相似类型的数据来说，如果我们采用可变的字符集进行操作也是会在可变长度数据列表里面进行储存的。

7：对于可变数据长度列表储存的占用字节为1或者2，NULL值用二进制位，记录头信息在Compact行格式占用5个字节，Redundant占用6字节。一页空间16kb。

二、页储存结构：

我们都知道的是数据库一个页的储存空间是16kb，那么这16kb的储存空间是怎么进行分配，数据在这个储存空间里是怎么样的一个格式呢？对于这些数据数据库都进行了什么操作？这就是我们今天需要进行学习的内容。

可能按照顺序来说的话不是太方便进行讲诉，而且看起来可能也不会效果太好，所以我们一点一点根据功能的划分进行区分。

User Records：

③记录头信息详解

这个区域对我们插入的数据进行保存，需要说明的是原本这一块是不存在的，当我们插入数据的时候这块区域才会被划分出来。并且是从Free Space进行的划分。当我们的Free Space区域所有的空间都变成了UserRecords，那么这时候就是需要重新开辟一个储存页的时间了。

那么当我们把数据放在这个区域里面的时候，是不是就是说没有一点规则，随便进行摆放，其实想一想就知道了，当我们数据过于庞大的时候，我们随便摆放，查找会是多么的痛苦。所以接下来在我们知道数据保存在那个位置以后我们需要弄清楚的是数据在User Records里的情况。在这里我们假设插入了四条我们自己的记录：

两个虚拟的数据：我们可以看到下图所示，我们插入了四条记录的时候，但是在我们这个页中存在的是六条记录，也就是两条我们说的每个页中都会存在的虚拟记录：最大记录和最小记录。他们都存infumum_supremum里面，因为不是我们自己插入的记录所以是不在User_Record里面.最小记录默认在开始，最大记录在最末尾结束。

下图蓝色的为头信息，橙色的为记录信息。

接下来我们再根据这六条记录描述一些问题：

1：Record_Type：我们在每条数据的记录头里面提到的Record_Type标记的是这条数据的类型，当时我们说的是有0普通数据，1叶子节点数据，2最小数据，3最大数据。我们可以看到的是上边最大和最小数据分别是3和2，我们自己插入数据的记录头信息在Record_type这里都是0

2：delete_mask：我们在记录头信息里面还可以看到的是delete_mask这个数据，表示的是数据是否被删除，0表示没有，1表示已经被删除，所以上边的数据都是0

3：heap_no：标记该数据在页中的位置，我们可以看到插入数据分别是2，3，4，5。那么0和1去哪了，别着急，请看看最小记录和最大记录的该数据，是不是分别为0和1。我们插入的数据都会从2开始计数，虚拟数据会占用默认的0和1的位置。

4：插入数据的排列是否就是数据插入的顺序，那显然是不可能的，你没想错，数据会根据大小进行排列，那么数据用什么进行大小的排列？显然就是主键进行比较。

5：next_record：记录的就是相对于本条数据，下一条数据的地址偏移量，就是通过这条数据往下查找这么多字节就可以找到下一条数据，没错。他就是使用的链表进行链接的。如下图：

删除

6：如果一条数据被删除，也就是它的delete_mask被标记为了1，那么这个会怎么进行改变？就是和链表一致，进行链接的切断就可以了。

分组：

7：我们在进行数据查找的时候就是这么一条接一条的进行查找么？从最小记录开始根据next_record查找？那必然是耗时的一个活，显然是不可能的，所以在就出现了分组这个概念。

我们可以看到的是六条数据分成了两组，首先是最小的虚拟数据独自一组，然后剩下的五个数据再分成一组。在这里需要知道的就是MySql数据库在每个页中进行数据分组的时候默认的最小数据是第一组，它拥有一条数据，就是最小数据，不能在插入其他数据。最大数据是第二组，我们在进行数据插入的时候都是先插入最大数据组，当最大数据组满足的时候进行分裂，形成普通的分组，然后再进来的数据又插入最大数据组，如此循环往复，完成数据的分组。

槽：

我们还可以看到的是在分组的图里面出现了两个奇奇怪怪的东西，槽，我们上一篇文章页诉说过这个玩意儿。每个分组最后一条数据的相对于页的地址偏移量就是一个槽数据，一个分组有一个槽，槽存在的位置就是页信息的**Page Directory**。在这里我需要强调的是在记录头信息中有个地址偏移量next_record，这个偏移量是本条数据相对于下一条数据位置，然后槽中的偏移量是分组最后一条数据相对于页的偏移量。

寻找：

有了分组以后，我们在进行数据查找的时候就是根据二分法确定对应数据所在的槽位置，然后在使用记录头信息的next_record一条条进行查找。有主键索引的时候才能采样二分法查找，否则只能遍历查找。

n_owned：

这个数据我们在记录头信息中一直看到，其实在这里就可以结束这个数据了。它表示的是该分组有多少条数据，存在于分组的最后一条信息中。我们可以看到的是每个分组的前面的数据 n_owned都是0，只有在最后一条数据上它才有值。

page_header

上边我们通过数据的方式介绍了User_Records,infumum_supremum,page_directory,Free spce这四块空间的使用情况，接下来需要进行解释的就是page_header,file_header,file tailer这三块空间。

首先说的就是page_header，这个地方储存的就是数据的一些信息：

`PAGE_N_DIR_SLOTS`	`2`字节	在页目录中的槽数量
`PAGE_HEAP_TOP`	`2`字节	第一个记录的地址
`PAGE_N_HEAP`	`2`字节	本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
`PAGE_FREE`	`2`字节	指向可重用空间的地址（就是标记为删除的记录地址）
`PAGE_GARBAGE`	`2`字节	已删除的字节数，行记录结构中`delete_flag`为1的记录大小总数
`PAGE_LAST_INSERT`	`2`字节	最后插入记录的位置
`PAGE_DIRECTION`	`2`字节	最后插入的方向
`PAGE_N_DIRECTION`	`2`字节	一个方向连续插入的记录数量
`PAGE_N_RECS`	`2`字节	该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
`PAGE_MAX_TRX_ID`	`8`字节	修改当前页的最大事务ID，该值仅在二级索引中定义
`PAGE_LEVEL`	`2`字节	当前页在索引树中的位置，高度
`PAGE_INDEX_ID`	`8`字节	索引ID，表示当前页属于哪个索引
`PAGE_BTR`	`10`字节	非叶节点所在段的segment header，仅在B+树的Root页定义
`PAGE_LEVEL`	`10`字节	B+树所在段的segment header，仅在B+树的Root页定义

在上边我们需要说的就是PAGE_DIRECTION和PAGE_N_RECS这两个数据第一个指的是最后插入的方向，相对于上一条数据来说，我们新插入的比他大，就在右边，反之则在左边，这就是方向。当我们插入数据连续的都在右边或者是都在左边的时候就会记录下数量。当然如果改变方向的话这个数据会被清空从零开始计数。

File_Header:

上边讲的page_header就是对页储存记录的描述，那么这里的File_Header就是对页信息的描述：

名称	占用空间大小	描述
`FIL_PAGE_SPACE_OR_CHKSUM`	`4`字节	页的校验和（checksum值）
`FIL_PAGE_OFFSET`	`4`字节	页号
`FIL_PAGE_PREV`	`4`字节	上一个页的页号
`FIL_PAGE_NEXT`	`4`字节	下一个页的页号
`FIL_PAGE_LSN`	`8`字节	最后被修改的日志序列位置（英文名是：Log Sequence Number）
`FIL_PAGE_TYPE`	`2`字节	该页的类型
`FIL_PAGE_FILE_FLUSH_LSN`	`8`字节	仅在系统表空间的一个页中定义，代表文件至少被更新到了该LSN值，独立表空间中都是0
`FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID`	`4`字节	页属于哪个表空间

我们可以看到三个值，一个记录的是这个页的页号，上一个页，下一个页。针对上面我们讲的一个页中的数据是采用单向链表的形式进行连接，那么我们可以想到的是数据库中的页是采用的双向链表。

我们在上面还可以看到的是FIL_PAGE_TYPE这个值，描述的是这个页的类型，显然我们数据库不可能就只有一种数据页，上面我们讲的储存真实数据页就是数据页。FIL_PAGE_INDEX，也就是我们提到的　B+树叶子节点。

名称	十六进制	描述
`FIL_PAGE_ALLOCATED`	0x0000	最新分配，还没使用
`FIL_PAGE_UNDO_LOG`	0x0002	Undo Log页
`FIL_PAGE_INODE`	0x0003	段信息的节点
`FIL_PAGE_IBUUF_FRE_LIST`	0x0004	Insert Buffer空闲列表
`FIL_PAGE_IBUF_BITMAP`	0x0005	Insert Buffer位图
`FIL_PAGE_TYPE_SYS`	0x0006	系统页
`FIL_PAGE_TYPE_TRX_SYS`	0x0007	事务系统数据
`FIL_PAGE_TYPE_FSP_HDR`	0x0008	File Space Header
`FIL_PAGE_TYPE_XDES`	0x0009	扩展描述页
`FIL_PAGE_TYPE_BLOB`	0x000A	BLOB页
`FIL_PAGE_INDEX`	0x45BF	B+树的子节点

File Trailer

这玩意需要和上面File header中的FIL_PAGE_SPACE_OR_CHKSUM这个属性系统校验和一起说。我们都知道的是页是一块16kb的储存空间，不管是内存刷新到数据库取用都是一次操作16kb。那么如果在中途产生停电等不可抗拒的因素，这时候这里就起作用了。File Header位于页的开始，它会计算一个校验和，这个校验和你可以这么理解，当我们需要一个很复杂的字符串的时候，往往会将它按照一定的算法进行计算出一个整数值，当和其它字符串进行比较的时候就用这个值。所以校验和也是这个道理，File Trailer是位于页尾部的，他也会储存一个校验和。如果数据不完整，那么两个校验和不可能一致，那么就可以判定这个数据页是损坏的。

MySQL数据库索引（上）

上一篇回顾：

1.数据页由七部分组成，包括File Header(描述页的信息)、Page Header（描述数据的信息）、Infimum + Supremum（页中的虚拟数据最大值和最小值）、User Records（用户真实数据储存的部分）、Free Space（真实数据增加划分的部分空间）、Page Directory（页中记录相对位置，槽储存的位置）、File Trailer（检验16kb大小的数据页是否完整）。

2.每一个页中的数据都是单向链表，由数据的记录头信息next_record进行维护，记录的是相对于本数据,下一条数据的字节数距离。内存中的页是双向链表的数据结构，由页信息的本页号码，上一页号码，下一页号码进行维护。

3.页中的数据都会进行分组，虚拟最小数据是一组，最大数据组最多存在8条数据，满额是二分成普通分组数据。每个分组的最后一条数据相对于页的偏移量就是槽的数据。本页中数据的查找采用的就是二分法，通过槽确定数据所在的分组，然后在进行搜索。

没有索引的查找：

在一个页中：

如果数据在一个页中，采用的是以主键为条件，那么我们就可以采用二分法的方式进行寻找，我们通过上一篇文章可以知道的是每一个页中的数据都会进行分组，然后在页信息的Page Diractory部分进行储存槽的信息，通过槽定位到数据所在的分组，然后在开始进行数据的搜索。

如果我们采用的不是主键作为搜索的条件，那么我们通过二分法寻找的方法必然是行不通的了，这时候我们只能很苦逼的一条一条数据慢慢的检索。

在很多数据页中：

如果我们要在很多数据页中进行没有索引的数据寻找那就更是不幸运了，只有一条数据的挨着进行搜索，假如我们数据库里有上亿的数据那么就是很苦逼而且不现实的一种方式。

索引的实现：

1：首先我们先创建一个今天需要使用到的表格：

create table index_demo(
    c1 int,
    c2 varchar(10),
    c3 char,
    primary key(c1)
)row_format=compact engine=myisam;

insert into index_demo values (1,4,'u'),(3,9,'d'),(5,3,'y');

2：我们用简化的图示展示一下这些数据储存的状态信息：

我们在上一篇文章中提到过一个问题，那就是数据的有序性。我们可以从上图看出一点端倪，那就是所有的数据都是以主键的大小值进行排列的。

3：假设我们一页只能储存下三条数据，这是假设，一页可以储存很多条数据，但是我们在这里为了方便演示就假设只能储存三条数据，然后我们在新加入一数据：

我们可以看到的是我们新插入的数据分了新的一页，但是很奇怪的一点就是上一个页的号码是10，为什么下一页就是28了？所以我们必须要清楚一点，页号码是不连续的，我们页的双向链表才是维护页秩序的连接，在前面我们也提到过这个问题，使用的是File Header进行储存页码，上一页码，下一页码。我们在前面提到过一个问题那就是数据具有一定的顺序，需要根据主键的大小进行排序，所以应该如下图所示：

4：我们现在搞清楚了数据的排布，那我们在连续插入多条数据在直观的看一下数据：

是不是发现了问题，我们如果数据库里还有很多这样的数据，就算是每一页都是有槽的存在，可以帮助我们快速在同一页中定位到数据所在。那么，如果不在一页我们是不是只有按照页的双向链表进行遍历？答案很显然不是，所以我们需要对每个页创建新的东西。

5：目录项：

我们可以看到的是我们在图上侧是不是创建了四个新的东西，这是啥？这就是目录项，一个页对应一个目录项，目录项里面储存的是页码+当前页主键最小的值。到这里是不是就有想法了？这玩意儿和我们自己的数据是不是着实很像。还记得我们在前面提过在数据的记录头信息的record_type这个属性么，0表示普通数据，1表示非叶子节点数据，2表示虚拟最小数据，3表示虚拟最大数据。所以，我们自己的数据和这个目录项数据的区分就是靠着这个属性区分的。除了这个我们的目录项是没有数据库自动添加的三个列的。所以，我们这时候是需要给这些目录项分配一个页，让他们进行储存：

7：到了这里的时候我们大概就能很清楚索引的结构了吧，那么问题又来了，我们的目录项页多了以后怎么办？那还能怎么办，继续再建造上一层目录项页呗。使用当前目录项的页码和当前页最小目录项的主键。

8：索引的查找就是通过一层一层的定位来实现的，最上层的页我们称之为根节点，中间的我们称之为内节点，最底层的我们称之为叶子节点。我们就是通过页中的槽二分法快速的定位数据所在页或者组中，我们在进行遍历查找。最后说一点这玩意儿就是我们说的B+树，也没多大点东西。

总结：

上面我们讲的就是Innodb储存引擎为每个表都会创建的用主键进行构造的聚簇索引，聚簇索引就是所有数据都在叶子节点的索引。

二级索引(非聚簇索引)：

讲完聚簇索引我们就来讲一下什么叫二级索引，二级索引顾名思义就是我们自己创建的索引。有时候我们的业务所需要，我们要根据某个或者是某些字段进行查找，排序，分组等，这时候我们为了加快速度，那么最好的方案就是创建这些列的索引：

我们首先看叶子节点，它的构成就是通过的我们需要使用的列+主键列构成，我们以需要使用的列作为排序的依据。然后我们再看目录项，是用我们需要使用的列+页码组成。以此类推，我们可以得出的结论就是：

1.二级索引和聚簇索引的区别就是叶子节点不包括完整的数据

2.二级索引储存的只是我们需要使用到的列和主键，如果要其它列的数据怎么办？回表：就是通过二级索引获取到的主键然后到聚簇索引里面去进行查找。

联合索引：

上面我们看了一下聚簇索引和二级索引，接下来我们再看一下什么叫联合索引，其实我们可以看出来就是联合指的就是多列进行组合：我们用c2和c3创建

我们可以看到的是联合索引就是用多个字段进行创建索引，然后根据对应列顺序进行排序。比如上图我们使用的就是c2和c3两个列，所以我们就是现根据c2进行排序，如果c2相同的情况下我们再根据c3进行排序。在这里提前说一个注意点：联合索引的使用务必要从最左边也就是最先开始排序的列开始使用。下一篇文章我们再详细讲。

Myisam储存引擎的索引：

我们本来主要使用的储存引擎就是Innodb，但是本着知识的完整性，我们介绍一下myisam储存引擎的索引，其实一句话就说清楚了。myisam的索引的叶子节点是没有保存真实数据的，只保存了主键的值。够清楚了吧，这货就是一个二级索引而已。

索引的创建和删除语法：

1：在建表的时候创建索引：index和key二选其一即可

create tabel 表名(列信息) index|key 索引名(创建索引使用的列)

2：在修改表结构的时候我们创建索引：

alter table 表名 add key|index 索引名(索引使用的列)

3：修改表结构删除索引：

alter table 表名 drop key|index 索引名；

MySQL数据库索引（中）

上一篇回顾：

1.一个索引对应一颗B+树，所有的真实记录都是存在叶子节点里面的，所有的项目录都存在内节点或者说根节点上。

2.innodb会为我们的表格主键添加一个聚簇索引，如果没有主键的话数据库是会为我们自动添加row_id这一列的。聚簇索引的叶子节点包含完整的用户记录。

3.我们是可以为自己感兴趣的列添加二级索引的，二级索引的叶子节点没有用户完整的信息，只是拥有对应列和主键的信息，如果想要拥有完整的信息是需要进行回表操作用二级索引找到的主键去聚簇索引寻找完整信息。

4.B+树的每一层节点都是按照索引列的大小信息进行排序而组成的双向链表，每个页里里面的记录也是按照索引列大小信息组成的单向链表。如果是联合索引的话，先按照前面的列进行排序，如果是相同的情况下再根据其他的列进行排序。

5.每个索引的搜索都是从根节点进行的，由于每个页面都按照索引列的值建立了Page Directory，所以在确定了具体页面信息的情况下是可以根据二分法进行快速的定位的。

索引的代价：

1.空间上的代价：每一个索引对应的都是一颗B+树，B+树的每一个节点都对应的是一个16kb大小的数据页，如果是一个很大的数据库的话那么占用的内存还是很大的。

2.时间上的代价：我们在上面讲过，每层节点都是按照数据的大小顺序进行排列的单向链表，每个页也是按照大小排列的双向链表。那么我们在对数据进行操作的时候必然避免不了的就是数据的迁移，数据页的删除，回收，分裂等等，如果我们创建的索引过多的话那么对应的问题就是频繁的需要对这些东西进行操作。那就是浪费时间，给性能拖后腿。

B+树适用的范围：

1：创建一个我们这篇文章需要用到的数据表：

create table person_info(
	name char,
    birthday Data,
    phone_num char,
    country char, -- 没有这个索引
    key psi_name_birth_phone(
        name,
        birthday,
        phone_num
    )
)row_format compact
charset ascii
engine innodb;

我们创建好表格以后需要注意的问题：

1>我们是没有主键的，那么是由数据库给我们生成主键，然后再根据主键创建聚簇索引；

2>我们自己创建的索引是没有包含country这个列的，所以我们索引的叶子节点只包含name，birthday，phone_num的值以及数据库帮助我们创建的主键row_id;

下面我们给出的就是这个索引的示意图：我们用颜色对内节点以及叶子节点进行了区分，而且我们必须要注意的就是这是根据name先排序，然后再根据birthday、phone

全值匹配：

如果是我们进行查询的数据列和我们索引所有列的顺序都是一样的话，那么我们称之为全值匹配，如下所示的查询：

select * from person_info where name='asiz' and birthday='1997-1-12' and phone_num='123456';

我们就可以利用索引进行快速的确定name=asiz的位置，然后如果有相同数据的话再根据这个信息进行birthday和phone_num的匹配。因为我们的索引是现根据name进行排序，再根据birthday和phone_num进行的排序。

但是，如果我们要是改变了这个顺序，首先使用birthday进行查询的话，那么就是不能使用这个索引，只能全文检索了。因为我们的索引都是先根据name进行排序的。

所以我们在使用联合索引的时候必须要严格按照顺序，至于里面具体的规则我们下面在讲。

匹配最左边的列：

1>只包含最左边的一个列：如下图所示，这样也是可以使用到我们的联合索引的

select * from person_info where name='asiz';

2>包含左边的多个列：如下图所示，这样操作也是没问题的

select * from person_info where name='asiz' and phone_num='123456';

3>如果我们在查询的时候没有使用到最左边的name列，如下图所示，这是不能使用索引的，只能进行全文的检索

select * from person_info where phone_num='123456';

注意：

所以我们在使用联合索引的时候，务必需要记住的就是一定要使用到第一个列，因为我们的索引就是按照第一个列最先开始排序的，如果不按照这个规则进行，那么我们是不能使用到索引的。而且，就如我们最后一条查询而言，我们在进行完成name的索引以后，在相同情况下进行phone_num的查询的时候是不能使用索引的，因为name完成以后是根据birthday进行的索引排序，所以一定要严格按照索引定义的顺序进行查找。

匹配值前缀：

1>如果我们在进行字符串的搜素的时候是没有必要输入完整的字符串的，就好像我们的模糊查询，我们只需要输入字符串的前面字母即可得到筛选的结果，因为B+树是现根据name进行排序的，我们只使用前面的部分字符也是可以进行二分查找迅速定位。

select * from person_info where name like 'as%';

2>如果我们给定的字符是位于字符串中间，那么这样是不可行的，是不能使用索引的，只能进行全文的检索，如下图所示：

select * from person_info where name like '%as%';

范围匹配:

1>我们的索引也是可以应用在范围查询里面的，如下图所示，因为我们的数据都是在页内按照单向链表进行排列，页之间是按照双向链表进行排列，所以是可以很快速获取到我们需要的数据：

select * from person_info where name > '%asiz%' and name<'sasa';

2>但是我们在使用多个列的范围查找的时候我们只能使用到的是第一列的索引，但是其他列的索引我们是使用不到的，因为我们是根据查询出来的结果在不同的name里面在进行birthday的筛选，索引是根据相同name的条件下才对birthday进行排序的，如下图所示:

select * from person_info where name > '%asiz%' and name<'sasa' and birthday>'1992-12-23';

精确匹配某一列并范围匹配某一列：

对于同一个索引来说，我们使用多个列的范围查询的时候，只能使用最左边列的B+树，其他列是不能使用的。但是我们左边使用的是精确查询，右边使用的是范围查询，那么，我们的右边也是可以使用到B+树的，如下图所示：

select * from person_info where name='%asiz%' and birthday>'1992-12-23' and phone_num>'212';

我们分析一下上图：

1>第一部分的name进行的精确匹配当然是可以使用到索引的

2>因为我们name是一样的，和索引的排序规则一致，所以birthday的范围搜索也是可以使用到B+树的

3>因为birthday的范围进行不同查找的结果，所以我们在进行phone_num的查找的时候是不能使用B+树的。

用于排序：

我们在使用排序比如说Order by的时候也是可以使用到索引的，如下图所示，具体的规则和我们进行查询的时候是一样的，因为我们索引就是按照顺序已经进行好排序的，所以如果我们的排序的顺序和索引的顺序是一致的，那么完全没问题可以直接取用数据，但是就是一直强调的问题，如果我们列的顺序改变了们就不能在使用B+树了。

select * from person_info order by name,birthday,phone_num;

用于分组：

如下所示，我们在使用group by的时候需要进行分组，这个过程包含了三个部分，第一个是先对name一致的进行分组，第二个在着基础上在对birthday一致的进行分组，然后最后在基础上对phone_num一致的进行分组。这就正好和我们的索引是一致的，所以是可以使用到B+树的，和上面一样，我们的顺序问题是坚决的不能乱的。

select name,count(*) from person_info group by name,birthday,phone_num;

索引的挑选：

1>必须条件：只为我们使用到的查询条件，分组，排序列创建索引。查询列表里面的列我们没有必要建立索引。

2>基数考虑：如果一个列的差异数据不是很多，我们称之为基数小的列。也就是说所有数据的这个列的数据大部分都相同，那么就是基数小，这种列没必要创建索引。

3>数据类型：我们知道的是索引列可以有很多的数据类型，比如说整形数据我们就有TINYINT、MEDIUMINT、INT、BIGINT，它们所占用的空间内存肯定是不一样的，所以我们挑选数据类型小的类型作为索引列的数据类型，可以有效的节约空间，储存更多的数据，那么我们在进行数据取用的时候一次可以加载更多的数据进入内存，减小IO损耗，同时在CPU层次来说，数据类型越小，查询处理的速度是越快的。

4>索引字符串的前缀：这个问题我们在上面其实提到过，我们在使用索引的时候是可以的，那么在创建索引的时候当然也是可以的，这样可以减少很多的内存空间，e而且我们在做字符串比较的时候如果我们使用的是前缀那么比较的时间也是可以大大进行缩短的。具体的语法如下:

alter table person_info add key psi_name(name(20),phone_num);

5>尽量使用联合索引：因为我们的每一个索引对应的都是一颗B+树，需要使用时间和空间进行维护的，我们文章开始就说了索引需要付出的代价。我们使用联合索引，是可以满足很多字段的索引条件的。

6>主键插入的顺序：记不记得我们在上边说的，索引的一个目录项对应的是一个页，我们的数据都是有序的进行单向链表的维护，那么如果我们的主键在后期插入中间的话就涉及到了位置的移动，目录项的修改，页面分裂，数据迁移等等问题。所以我们建议的是让数据库给主键进行自增生成。

7>避免冗余重复的索引：不要为一个列重复的添加多个索引，这样是不好的，他对效率的提升没有半点的帮助，但是对空间的消耗确实实打实的。

8>覆盖索引：比如我们开始创建的索引是没有包含country这个列的，如果我们如下图所示进行查询，我们本来是可以在索引直接得到三个列的数据，但是差一个列，这时候就必须用主键去聚簇索引进行回表操作了。所以我们查询的列最好都是我们索引的列，也就是说我们是鼓励把需要查询的列明确进行书写的。

select * from person_info;

参考：https://www.cnblogs.com/zslli/category/1199669.html