数据库存储引擎 InnoDB

爱被打了一巴掌 2023-03-02 05:26 31阅读 0赞

## 存储引擎 ##

> 服务器支持不同的存储引擎，不同的表可以设置不同的存储引擎，真实数据在不同存储引擎中存放的格式一般是不同的，有的存储引擎将数据存放在磁盘中，有的存储引擎比如Memory，不用磁盘存储，服务器关闭后数据就消失了。

## InnoDB ##

> InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的

### 页（磁盘和内存的交互） ###

规定，一页至少存放2条记录，一页16KB=16384字节  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70]

### 行格式（4种） ###

> 表的记录在磁盘上的存放方式被称为行格式或者记录格式

ROW_FORMAT=行格式名称

> InnoDB 行格式有：

**1. Compact**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 1]  
变长字段长度列表，NULL值列表 逆序存放  
NULL值列表必须用整数个字节的位表示，高位补零  
记录头信息 用于描述记录，由固定的5个字节组成，也就是40个二进制位，不同的位代表不同的意思  
真实数据列还包含一些隐藏列，由存储引擎自动生成

**2. Redundant**  
mysql5.0之前用的![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 2]  
会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表  
记录头信息占用6字节，48个二进制位

> 对于Compact和Redundant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

![在这里插入图片描述][2020072810241971.png]

**3. Dynamic**  
mysql5.7 默认使用  
**4. Compressed**  
Compressed行格式会采用压缩算法对页面进行压缩。

> Dynamic和Compressed行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

![在这里插入图片描述][20200728102902596.png]

### 页（索引页）结构 ###

> 存放表中记录的页  
> 页被划分为大致7个部分：

*  File Header，表示页的一些通用信息，占固定的38字节。
 *  Page Header，表示数据页专有的一些信息，占固定的56个字节。
 *  Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。自动生成，作为第0,1条记录
 *  User Records：真实存储我们插入的记录的部分，大小不固定。
 *  Free Space：页中尚未使用的部分，大小不确定。 Page
 *  Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
 *  File Trailer：用于检验页是否完整的部分，占用固定的8个字节。为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 3]  
**页与页之间是双向链表连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 4]

#### 记录在页中的存储 ####

一个页存满，就会申请新的页：叶中的记录是单向链表连接  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 5]

#### 页目录（分组） ####

**Page Directory** 页目录，页中的记录会进行分组，每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，就是页目录。

> InnoDB 规定，对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

> 在一个数据页中查找指定主键值的记录的过程分为两步：

1.  通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
2.  通过记录的next\_record属性遍历该槽所在的组中的各个记录。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 6]

## B+ 树索引 ##

### 页和记录关系 ###

各个页在物理结构上可以不连续  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 7]

### 索引 ###

> 为了在大量数据中快速查找到目标，需要建立目录，通过目录可以快速定位（就像书籍的目录一样），目录就是索引。  
> 页中存放目录项的行，称为目录项记录。  
> InnoDB的索引，就像是一个多级目录一样，大目录里嵌套小目录，最小目录里才是实际的数据，这就是B+树结构  
> 一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页）  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 8]

#### 聚簇索引 ####

> 按照记录主键值的大小进行排序  
> 叶子结点存储完整用户数据  
> InnoDB存储引擎会自动的为我们创建聚簇索引  
> 在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

#### 二级索引 ####

> 以其他列值的大小进行排序  
> 叶子结点存储 索引列的值+主键值，如果每个索引叶节点都存储完整的用户记录，会浪费存储空间  
> 需要回表操作，

#### 联合索引 ####

> 按照多个列的值进行排序  
> 排序按照最左原则

## MYISAM ##

> 数据和索引是分开的，所有的索引都需要回表，即相当于都是二级索引  
> 数据文件，按照记录的插入顺序存放，不能二分查找  
> 索引文件，索引的叶子结点存放索引列+该记录在数据文件中的地址偏移量（行号），根据偏移地址回表，速度很快。

## 创建索引语句 ##

普通索引创建

CREATE INDEX indexName ON mytable(username(length));

创建唯一索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

聚簇索引

CREATE CLUSTERED INDEX region_name ON region2(name);

删除索引的语法：

DROP INDEX [indexName] ON mytable;

在创建表的时候建立索引

CREATE TALBE 表名 (
        各种列的信息 ··· , 
        [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
    )

修改表结构的时候添加索引

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

删除索引

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

## B+树索引的使用 ##

> 一个表上的索引建立的越多，就越占用存储空间，在增删改记录的时候，需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护多个索引的节点之间，以及记录之间的顺序，性能就会越差。因此，需要合理适当的建立索引。

### B+树索引 适用的查询条件 ###

**全值匹配**  
如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配  
**匹配左边的列**  
其实在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行，或者包含多个左边的列也行  
**匹配列前缀**  
查询名字以’As’开头的记录  
如果只给出后缀或者中间的某个字符串，MySQL就无法快速定位记录位置了  
可以把后缀查询改写成前缀查询，不过得把表中的数据全部逆序存储一下  
**匹配范围值**  
如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引  
**精确匹配某一列并范围匹配另外一列**  
对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找  
**用于排序**  
如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出  
不可以使用索引进行排序的几种情况  
ASC、DESC混用。也就是说要么各个列都是ASC规则排序，要么都是DESC规则排序。  
排序列包含非同一个索引的列  
排序列使用了复杂的表达式  
用于分组

### 回表的代价 ###

为了彻底告别回表操作带来的性能损耗，最好在查询列表里只包含索引列 ，这种只需要用到索引的查询方式称为索引覆盖

### 如何挑选索引 ###

只为用于搜索、排序或分组的列创建索引  
为列的基数大的列创建索引  
索引列的类型尽量小  
可以只对字符串值的前缀建立索引  
只有索引列在比较表达式中单独出现才可以适用索引  
为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO\_INCREMENT属性。  
定位并删除表中的重复和冗余索引  
尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

### 不适合建索引的情况 ###

*  查询中很少涉及的列，重复值比较多的列不要建立索引。
 *  定义为text、image和bit的数据类型的列不要建立索引。
 *  经常存取的列避免建立索引

### 可以建立索引的情况 ###

*  定义主键的数据列一定要建立索引。
 *  定义有外键的数据列一定要建立索引。
 *  对于经常查询的数据列最好建立索引。
 *  对于需要在指定范围内的快速或频繁查询的数据列;
 *  经常用在WHERE子句中的数据列。
 *  经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70]: /images/20230208/b180ce35304244c1a263dcbedddcfccc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 1]: /images/20230208/8ef887ca38a54cffae491413fefe4ef7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 2]: /images/20230208/3e0f377261c345dcb332a7e2e44b5cd5.png
[2020072810241971.png]: /images/20230208/917439c5616b4eacad03daf32d659bd3.png
[20200728102902596.png]: /images/20230208/7905544f138346e7a73be94fb62fe377.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 3]: /images/20230208/12ee8c2744bb4496885f3f5156ef1abd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 4]: /images/20230208/86beea164b2c450daddae5c347f243bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 5]: /images/20230208/6923756668e14bddbbcf87474eef731f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 6]: /images/20230208/272ae0b018d64409b32e8fbbf61c45f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 7]: /images/20230208/bf9bc15b95f44567a616827194101804.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nbWlubHU_size_16_color_FFFFFF_t_70 8]: /images/20230208/e88561d5491f45d495e7b465a98bb9fe.png