# MySQL体系结构与存储引擎

> 来源：梦飞idc云平台 | 原文：https://www.mfisp.com/9560.html | 发布：2022-09-01 | 更新：2022-09-01 | 分类：建站教程、投稿分享

### 1 MySQL体系结构

 

#### 1.1 数据库与数据库实例

 

**数据库**：物理操作系统中的文件和其他文件类型的集合，除了硬盘存储的文件，也可以是存放在内存中的文件

 

**数据库实例**：有数据库后台进程、线程以及一个共享内存区域组成，共享内存可以被后台进程/线程所共享，是应用程序，位于用户与操作系统直接的数据管理软件

 

**注意**：不能通过修改二进制文件来更改数据库内容，仅可以通过数据库实例操作数据库。在MySQL中，实例与数据库是一一对应的，但在集群环境下会发生一个数据库被多个实例调用的情况

 

#### 1.2 体系结构

 

![img](https://s2.loli.net/2022/04/21/YJnrB3h8V4oHpWi.png)

 

由图可知，MySQL由以下组件构成：

 
 
- 连接池组件 
- 管理服务与工具组件 
- SQL接口组件 
- 查询分析器组件 
- 优化器组件 
- 缓存组件 
- 插件式存储引擎 
- 物理文件 

 

MySQL的重要特点是其插件式存储引擎，提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是数据库系统本身所必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，此外，存储引擎是基于表的，而不是数据库

 

### 2 常见的存储引擎

 

#### 2.1 InnoDB存储引擎

 

InnoDB存储引擎支持事务，是面向在线事务处理方面的应用，支持行锁、外键，支持类似Oracle的非锁定读，默认情况读取操作不会产生锁，在MySQL5.1版本之后被设定成默认存储引擎，此前是MyISAM

 

InnoDB存储引擎将数据放在一个逻辑表空间，由InnoDB自身进行管理，将每个InnoDB存储的表单独放在一个独立的ibd文件中，同样也可以使用裸设备(row disk)建立表空间

 

InnoDB使用多版本并发控制(MVCC)获得高并发性，实现了SQL标准的4种隔离级别，默认使用的是REPEATABLE READ(可重复读)级别，此外还提供了插入缓冲、预读、二次写、自适应哈希索引、预读等高性能和高可用的功能

 

针对表数据的存储，InnoDB存储引擎采用了聚集(clustered)的方式，每张表的存储按照主键的顺序存放，如果没有显式地指定主键，InnoDB将为每一行生成一个6字节的ROWID作为主键

 

#### 2.2 MyISAM存储引擎

 

特点是不支持事务、表锁和全文索引，针对一些OLAP(在线分析处理)操作速度快，MyISAM存储引擎表由MYD与MYI组成，MYD用于存放压缩数据文件，MYI用于存放索引文件，可使用myisampack工具压缩数据文件，使用哈夫曼编码静态算法压缩，输出结果为只读表。在独占表空间中，每一个表还有一个.frm表描述文件，以及一个.ibd文件(这个文件包括了单独一个表的数据内容以及索引内容)

 

注意：对于MyISAM存储引擎表，MySQL只缓存索引文件，数据文件的缓存由操作系统本身完成，这与其他使用LRU算法缓存数据的大部分数据库大不相同

 

#### 2.3 NDB存储引擎

 

NDB是一种集群存储引擎，类似Oracle的RAC share everything结构，但不同的是其使用share nothing的集群结构，因此能提高更高级别的高可用性。

 

NDB的特点是数据放置在内存中(5.1版本后可将非索引数据放在磁盘中)，主键查找的速度极快，并可以通过添加NDB数据存储节点(Data Node)线性的提高存储性能，可以作为高可用、高性能的集群系统

 

注：NDB存储引擎的连接操作是在MySQL数据库层完成的，而不是在存储引擎层，意味着需要较大的网络开销，因此查询速度较慢

 

#### 2.4 Memory存储引擎

 

Memory将数据存放在内存中，如果数据库重启或崩溃，所有数据将丢失，适合用于存储临时数据的临时表以及数据仓库中的维度表，默认使用哈希索引

 

该引擎存在一些限制，比如只支持行锁，并发性能差，不支持TEXT和BLOB列类型，还有存储变长字段是是按照定长方式进行的，存在内存浪费的问题。若存放结果集时，中间结果集的大小大于Memory存储引擎表的容量设置，或存在TEXT或BLOB字段，MySQL会将其转化为MyISAM存储引擎放在磁盘，因MyISAM不缓存数据文件，因此产生的临时表的性能会有损失

 

#### 2.5 Archive存储引擎

 

Archive存储引擎只支持insert和select操作，从MySQL5.1之后支持索引，使用zlib算法压缩数据行后存储，压缩率可达到1:10，适用于存储归档数据，并使用行锁实现高并发的写入操作

 

#### 2.6 Federated存储引擎

 

Federated存储引擎不存放数据，用于指向一台远程MySQL数据库服务器的表，类似SQL Server的链接服务器与Oracle的透明网关，不同的是Federated存储引擎只支持MySQL数据表，不支持异构数据表

 

#### 2.7 Maria存储引擎

 

Maria存储引擎是新开发的引擎，目标是取代原有的MyISAM存储引擎，成为MySQL的默认存储引擎，特点是缓存数据，索引数据，行锁设计，提供MVCC功能，支持事务和非事务安全的选项支持，以及更好的BLOB字符类型的处理性能

 

#### 2.8 对比

 

![MySQL体系结构与存储引擎](https://s2.loli.net/2022/04/29/JEfUWk8KLxVSo65.jpg)

 

### 3 InnoDB存储引擎

 

#### 3.1 概述

 

InnoDB是事务安全的MySQL存储引擎，一般用于核心应用表的首先存储引擎

 

#### 3.2 体系架构

 

如图，InnoDB拥有多个内存块，组成了一个大的内存池，负责以下工作：

 
 
- 维护所有进程/线程需要访问的多个内部数据结构 
- 缓存磁盘上的数据，方便快速读取，并在对磁盘文件的数据修改之前进行缓存 
- 重做日志缓冲 

 

![image-20220421153228400](https://img-blog.csdnimg.cn/img_convert/7476c78ab71d4b6e6b20d12f3590facf.png)

 

后台线程用于刷新内存池的数据，保证缓冲池中的内存缓存的是最近的数据。此外，将已经修改的数据刷新到磁盘文件，同时保证在数据库发生异常时InnoDB能恢复到正常运行状态

 

##### 3.2.1 后台线程

 
 
- **IO线程**

 

一个insert buffer线程，一个log线程，4个读线程，4个写线程 
- **master线程** 
- **lock监控线程** 
- **错误监控线程** 

 

##### 3.2.2 内存

 

内存由三部分构成，缓冲池(buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool)，分别由配置文件的参数innodb_buffer_pool_size和innodb_log_buffer_size的大小决定

 

![image-20220421161600726](https://img-blog.csdnimg.cn/img_convert/96efb72ea03a08f5ba4c94d230f7dc61.png)

 

使用 `show engine innodb status\G;` 可以查看InnoDB缓冲池的使用情况，buffer pool size表示一共有的缓存帧(buffer frame)，每个缓存帧大小为16k，所以一共分配了512M的缓冲池，free buffers表示当前空闲的缓冲帧，database pages表示以及使用的缓冲帧，modify db pages表示脏页的数量

 

![image-20220421161840399](https://img-blog.csdnimg.cn/img_convert/68871f7c1aa8431d396ebaaf4ee8520c.png)

 

注：show engine innodb status命令显示的不是最新的状态，而是过去某个时间范围的状态，可以从命令前面返回的文本看到

 

![image-20220421162621655](https://img-blog.csdnimg.cn/img_convert/8ba83cec5550768151dd1a6e3a89c2bf.png)

 

缓冲池缓存的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等

 

![image-20220421163026836](https://img-blog.csdnimg.cn/img_convert/43539b9db5f27b4b670ae2da1c982373.png)

 

如上图，日志缓冲将重做日志信息放入缓冲区，然后按照一定的频率刷新到日志文件，只需要保证每秒产生的事务量在这个缓冲区之内就可以

 

额外内存池的值设定也很重要，InnoDB中内存是堆的管理方式，在对一些数据结构本身分配内存时，需要从额外的内存池申请，当该区域不够时，会从缓冲区申请。InnoDB实例会申请缓冲池空间，但每个缓冲池的帧缓冲还有缓冲控制对象，这些对象记录了如LRU、锁、等待等方面的信息，对象的内存需要从额外的内存池中申请，当申请了很大的InnoDB缓冲池时，应释放增加额外内存池的空间

 

#### 3.3 master thread

 

master thread线程的优先级是最高的，内部由几个循环组成，主循环(loop)，后台循环(background loop)，刷新循环(flush loop)，master thread的状态在loop、background loop、flush loop和 suspend loop中切换

 
 
- loop：主循环，通过thread sleep实现

 
 
  - 每秒的操作

 
 
    - 日志缓冲刷新到磁盘，即使事务没有提交（总是执行） 
    - 合并插入缓冲（可能执行）io<5 
    - 最多100个InnoDB的缓冲池中的脏页到磁盘（可能执行）dirty_pages判断阈值>90% 
    - 如果当前没有用户活动，切换到后台循环（可能执行） 
  - 每10秒的操作

 
 
    - 刷新100个脏页到磁盘（可能执行）io<200 
    - 合并至多5个插入缓冲（总是执行） 
    - 将日志缓冲刷新到磁盘（总是执行） 
    - 删除无用的Undo页（总是执行） 
    - 刷新100个或者10个脏页到磁盘（总是执行） 
    - 产生一个检查点（总是执行） 
- background loop：后台循环，当没有用户活动或数据库关闭时，会切换到这个循环

 
 
  - 执行操作 
 
    - 删除无用的redo页（总是执行） 
    - 合并20个插入缓冲（总是执行） 
    - 跳回到主循环（总是执行） 
    - 不断刷新100个页，直到符合条件（可能执行，跳转到flush loop完成） 
- flush loop：刷新循环

 
 
  - 执行操作 
 
    - 。。。 
    - 切换至suspend_loop，将master thread挂起，等待事件发生 

 

**问题**：上述的master线程调度方式存在一定的弊端，尤其是在固态硬盘出现后，极大地影响了磁盘的IO性能，特别是写入性能。因为无论何时，InnoDB引擎最多才会刷新100个脏页到磁盘，合并20个插入缓冲，在密集型应用程序中，每秒产生的脏页或插入缓冲远大于预估值，此时master thread将会出现性能瓶颈，发生宕机需要恢复时，很多数据未刷新会磁盘，导致恢复需要很长时间

 

**对策**：发布修正补丁，动态修改插入缓冲的百分比与刷新脏页的数量，以及其他参数

 

#### 3.4 关键特性

 

一些关键特性：**插入缓冲**、**两次写**、**自适应哈希索引**，这些特性带来了更好的性能与更高的可靠性

 

##### 3.4.1 插入缓冲

 

**缘由**：只存在一个聚集索引的情况下，插入语句执行会很快，但是存在多个非聚集索引的情况，叶子节点的插入顺序不再是顺序执行，由于B+树的特性，插入新功能变低了

 

**解决方案**：提出插入缓冲概念，对于非聚集索引的插入或更新操作，先判断非聚集索引页是否在缓冲池中，在则插入，否则先放入缓冲池，骗过数据库这个索引已经插到叶子节点了，然后以一定的频率执行插入缓冲和非聚集索引叶子节点合并操作，此时将多个插入操作合并到一个操作中，提高了性能

 

需满足**两个条件**：① 索引是辅助索引；② 索引不是唯一的

 

##### 3.4.2 两次写

 

**场景**：数据库宕机时且正在写入页面未完成，称为部分写失效，因重做日志记录的是对页的物理操作，页本身已经损坏，无法进行重做，因此需要一个页的副本，写入失效发生时，通过页的副本还原该页，就是doublewrite

 

![image-20220422092637936](https://img-blog.csdnimg.cn/img_convert/a3a3809430b86335d2fea66346bbc4cf.png)

 

**解决方案**：doublewrite由两部分组成，一部分是内存中的doublewrite buffer，另一部分是物理磁盘共享表空间的连续128个页，大小均为2MB。

 

当缓冲池的脏页刷新时，不会直接写入磁盘，而会通过mencpy函数将脏页拷贝到内存中的doublewrite buffer，doublewrite buffer会分两次写入共享表空间的物理磁盘，随后即刻调用fsync函数同步到磁盘文件

 

##### 3.4.3 自适应哈希索引

 

简介：

 

InnoDB会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度提升，则会建立，称之自适应。自适应哈希索引是根据缓冲池的B+树构造而来的，建立速度很快，而无需将整个表建立索引，InnoDB会自动根据访问频率和模式为某些页建立哈希索引

 

定义：

 

InnoDB存储引擎会监控对表上各索引页的查询。并建立合适的哈希索引，加速数据页的访问

 

特点：

 
 
- 查询消耗O(1) 
- 降低二级索引树的频繁访问资源 
- 自适应 

 

劣势：

 
 
- hash自适应索引会占用innodb buffer pool 
- 自适应hash索引只适合搜索等值的查询，如select * from table where index_col=‘xxx’，而对于其他查找类型，如范围查找，是不能使用的 

 

自适应散列索引（AHI）使InnoDB在系统上执行更像内存数据库，该功能由innodb_adaptive_hash_index 配置启用

 

![img](https://img-blog.csdnimg.cn/img_convert/bd9ac4cea1d77f7059b22a446c38cf6b.png)

 

InnoDB存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，InnoDB就会使用索引键的前缀建立一个哈希索引。将索引值转换为一种指针，便于直接访问，带来速度的提升

 

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快

 

#### 3.5 启动、关闭与恢复

 

**参数innodb_fast_shutdown说明**：

 

取值0：表示关闭时需完成所有的full purge和merge insert buffer操作，需花费较长时间

 

取值1：默认值，无需完成上述的full purge和merge insert buffer操作，但缓冲池的数据脏页仍会刷新到磁盘

 

取值2：仅将日志写入日志文件，在下次启动时会执行恢复操作

 

**参数innodb_force_recovery说明**：默认为0，表示需要恢复时执行所有的恢复操作。当不能有效恢复时，如数据页发生了corruption日志，数据库可能会宕机，并把错误写入错误日志

 

取值1：忽略检查到的corruption页

 

取值2：阻止主线程的运行，如主线程需要执行full purge操作，会导致crash

 

取值3：不执行事务回滚操作

 

取值4：不执行插入缓冲的合并操作

 

取值5：不查看撤销日志，innoDB会将未提交的事务视为已提交

 

取值6：不执行前滚的操作

 

注：设置了该值大于0后，可以对表进行select、create、drop操作，但是insert、update和delete操作是不被允许的

 

### 4 InnoDB内部详解

 

#### 4.1 文件

 

##### 4.1.1 参数文件 my.cnf

 

指定启动初始化参数

 
 
- 动态参数：可在MySQL实例运行中更改 
- 静态参数：在实例生命周期内不得更改 
- 日志文件

 
 
  - 错误日志：记录启动、运行、关闭的过程，以及警告和错误等错误信息 
  - 慢查询日志：设定一个阈值，将超过阈值的查询SQL语句记录到慢查询日志文件中 
  - 查询日志：记录了所有对数据库的请求，可以将日志的记录存放在mysql下的general_log表 
  - 二进制日志(binlog)：不包含select、show等操作，记录执行数据库更改操作的时间和执行时间等信息

 
 
    - 恢复：数据恢复需要二进制文件，当一个数据库全备文件恢复后，可通过二进制日志进行point-in-time的恢复 
    - 复制：通过复制和执行二进制日志文件可以使两台MySQL服务器进行实时同步 
    - binlog_format参数：

 
 
      - STATEMENT：记录日志的逻辑SQL语句 
      - ROW(默认)：记录表的行更改情况，一般搭配事务隔离级别READ COMMITTED 
      - MIXED：该格式默认采用STATEMENT格式记录二进制日志，但在一些情况下使用ROW格式：

 
 
        - 表存储引擎是NDB，进行的DML操作会以ROW记录 
        - 使用UUID() USER() CURRENT_USER() FOUND_ROWS() ROW_COUNT() 等不确定函数 
        - 使用INSERT DELAY语句 
        - 使用用户定义函数(UDF) 
        - 使用临时表 

 

![image-20220427105628451](https://img-blog.csdnimg.cn/img_convert/80c4781e74c9dde7dfbc14b5185b9aea.png) 

 

##### 4.1.2 套接字文件socket

 

用于本地套接字方式连接

 

![image-20220427110819031](https://img-blog.csdnimg.cn/img_convert/6e57685e43ad5a32f46d88106e37ef9e.png)

 

##### 4.1.3 pid文件

 

记录进程id

 

![image-20220427110947341](https://img-blog.csdnimg.cn/img_convert/a380f8251b282c803bec2b4060ec9d95.png)

 

##### 4.1.4 表结构定义frm文件

 

存放MySQL表结构定义，存放在data目录下的每个独立数据库文件夹中

 

##### 4.1.5 InnoDB存储引擎文件

 
 
- 表空间文件：ibdata1 ibdata2

 

![image-20220424172624447](https://s2.loli.net/2022/04/24/FkqNSVGCRp5i3LZ.png)

 

由于是InnoDB存储引擎，且设置了innodb_file_per_table=ON，产生了单独的.ibd表空间，这些单独二表空间仅存储该表的数据、索引和插入缓冲等信息，其余数据还是存放在默认的表空间中 
- 重做日志文件：ib_logfile1 ib_logfile2，也称*redo log file*，记录了对于**InnoDB存储引擎**的事务日志

 

作用：由于主机掉电导致实例失败，InnoDB会重做日志恢复到掉电前的时刻，确保数据的完整性。

 

每个InnoDB存储引擎至少有一个重做日志文件组，每组至少有2个重做日志文件，如ib_logfile0、ib_logfile1。

 

为确保可用性，可设置多个镜像日志组，将不同的文件组放在不同的磁盘上，每个重做日志大小一致，并循环使用

 

![image-20220427114901462](https://img-blog.csdnimg.cn/img_convert/fd0713ef76416891635461e169083fd2.png)

 

重做日志写入：先写入日志缓冲，根据innodb_flush_log_at_trx_commit参数控制在commit时处理操作日志的方式，0表示不将重做日志写入磁盘的日志文件，而是等待主线程每秒的刷新；1表示在commit时将重做日志缓冲写入磁盘，而2表示异步写入，不能保证commit时一定会写入重做日志文件 

 

#### 4.2 表

 

InnoDB规定每张表都需要定义主键，如果存在非空唯一索引则自动设为主键，否则InnoDB自动创建一个6个字节大小的指针作为主键

 

![image-20220427143647065](https://img-blog.csdnimg.cn/img_convert/a542ded7b1b024cdf96e02218b60e41d.png)

 

**InnoDB逻辑存储结构：**

 

表空间(tablespace)：位于InnoDB存储逻辑的最高层，存放所有数据。默认共享表空间ibdata1，若指定innodb_file_per_table则每张表的数据被单独放在一个表空间中

 
 
- 段(segment)：

 

表空间由段组成，常见数据段、索引段、回滚段等

 

InnoDB存储索引表是索引组织的，数据即索引，索引即数据，数据段为B+树的叶子节点，索引段即B+树的非叶子节点 
- 区(extent)：

 

由64个连续的页组成，每个页大小16KB，每个区大小1MB。每个数据段至多申请4个区，以保证数据的顺序性能

 

每个段开始时，有32个页大小的碎片页存放数据，使用完再进行64个连续页的申请

 

```
#page info
Total number of page: 6 #总页数
Freshly Allocated Page: 2 #可用页
Insert Buffer Bitmap: 1 #插入缓存位图页
File Space Header: 1 #插入缓存空闲列表页
B-tree Node: 1 #数据页
File Segment inode: 1 #二进制大对象页，存放溢出行的页，即溢出页
```

 
 
  - 1 
  - 2 
  - 3 
  - 4 
  - 5 
  - 6 
  - 7 

 
 
  - 1 
  - 2 
  - 3 
  - 4 
  - 5 
  - 6 
  - 7 

 

初始大小：16kb * 6(页的总数量) = 96kb 
- 页(page)：

 

页是磁盘管理的最小单位，默认页大小16KB，不可更改

 

常见页类型：

 
 
  - 数据页 
  - Undo页 
  - 系统页 
  - 事务处理页 
  - 插入缓冲位图页 
  - 插入缓冲空闲列表页 
  - 未压缩的二进制大对象页 
  - 压缩的二进制大对象页 
- 行

 

InnoDB存储引擎是面向行的，每页最多存放16KB/2-200(页保留)行的记录，即7992行记录 

 

**InnoDB物理存储结构：**

 

组成：共享表空间、日志文件(redo)组、表结构定义文件

 

.ibd：独立表空间文件

 

.frm：表结构定义文件，与存储引擎无关

 

**InnoDB行记录格式：**

 
 
- Compact行记录格式：

 

![image-20220427155722970](https://img-blog.csdnimg.cn/img_convert/bfef9f3e26b72889d3740edad5d9da22.png)

 
 
  - 变长字段长度列表：逆序存放，列长度小于255字节，用1字节表示，否则使用2字节表示，因此varchar最大长度是65535 
  - null标志位：表示该行数据中是否有null值，用1表示 
  - 记录头信息：固定占用5字节

 

![image-20220427160300104](https://img-blog.csdnimg.cn/img_convert/8eaf3fe9dbb61220fb40233dad670fa7.png)

 

![image-20220427160817720](https://img-blog.csdnimg.cn/img_convert/0f3b2a71aefa5c163bc54e5d867beabd.png) 
  - 实际存储的列数据：

 

null不占用该部分数据，只占有标志位。

 

此外还有两个隐藏列，事务id和回滚指针列，分别为6个字节和7个字节的大小，若未定义主键，还会增加一个6字节的RowID列

 

后面的列数据的就是数据表的实际列数目 
- Redundant行记录格式：MySQL5.0之前的行存储方式

 

![image-20220427165508183](https://img-blog.csdnimg.cn/img_convert/a89b2f16c290863678c1042fcb22efb2.png)

 
 
  - 字段长度偏移列表：按照列顺序逆序放置，列长度小于255字节，用1字节表示；若大于用2字节表示 
  - 记录头信息：固定占用6字节

 

![image-20220427165812027](https://img-blog.csdnimg.cn/img_convert/a0920cea4991c2eedcbd631ab4f0c055.png) 
  - 列数据：varchar的null值不占用空间，而char的null值会占用空间，而在compact中是完全不占用空间的 
- 行溢出数据：

 

varchar类型原则上可存放65535长度数据，实际最多存放65532长度数据 
- Compressed和Dynamic行记录格式：采用了完全的行溢出方式，在数据页存放20个字节的指针，实际数据存放在BLOB Page中

 

Compressed存储的行数据使用zlib算法进行压缩，针对BLOB、TEST、VARCHAR这类长数据类型得到有效的压缩 
- Char的行结构存储

 

CHAR(N) N表示的是字符的长度，而不是之前版本字节长度，不同字符集下，CHAR内部存储的不是定长的数据 

 

**InnoDB数据页格式：**

 

页是InnoDB存储引擎管理数据库的最小单位，页类型是B+树的页，存放具体的行数据

 

**组成：**

 
 
- File Header 文件头 
- Page Header 页头 
- Infimun + Supermum Records 
- User Records 用户记录 
- Free Space 空闲空间 
- Page Directory 页目录 
- File Trailer 文件结尾信息 

 

**Named File Formats：**

 

为解决新的页数据结构与之前版本的页不兼容的问题，从InnoDB Plugin版本开始，InnoDB存储引擎引入Named FileFormats机制解决不同版本页结构的兼容性问题

 

InnoDB将之前版本的文件格式定义为Antelope，将当前版本文件格式定义为Barracuda。Antelope文件格式有Compact和Redudant的行格式，Barracuda文件格式支持Antelope所有格式，此外也新加入了Compossed和Dynamic行记录格式

 

![image-20220428090435290](https://img-blog.csdnimg.cn/img_convert/0a2cd1ed697db405152da05544384979.png)

 

**约束：**

 

数据完整性三种形式

 
 
- 实体完整性：通过定义primary key和unique key约束来保证实体完整性 
- 域完整性：保证数据的值满足特定的条件 
- 参照完整性：保证表之间的关系，InnoDB支持外键，允许用户自定义外键 

 

InnoDB存储引擎几种约束：

 
 
- Primary Key 
- Unique Key 
- Foreign Key 
- Default 
- NOT NULL 

 

**视图：**

 

是一个命名的虚表，由一个查询来定义，视图的数据无物理表现形式

 

**分区表：**

 

从MySQL5.1版本之后支持分区，将一个表或索引分解成多个更小、更加可管理的部分。一个逻辑表由多个物理分区组成，每个分区都是一个独立对象

 

MySQL支持的分区类型是水平分区，不支持垂直分区，数据库分区是局部分区索引，一个分区既存放数据也存放索引

 

支持的分区类型：

 
 
- RANGE分区：行数据基于一个定连续区间的列值放入空间 
- LIST分区：LIST分区面向的是离散的值 
- HASH分区：根据用户自定义的表达式返回值进行分区，返回值不为负数 
- KEY分区：根据MySQL提供的哈希函数进行分区 

 

RANGE分区：

 

使用range分区表时，表上的物理文件不再是由一个ibd文件组成了，而是由建立分区的各个分区ibd文件组成

 

![image-20220428154350793](https://img-blog.csdnimg.cn/img_convert/458da49b56443ebcaf8de2c31726fdc4.png)

 

当插入一个不在分区范围的值时，MySQL将抛出异常，可通过添加一个MAXVALUE值的分区，可理解为正无穷

 

![image-20220428155622989](https://img-blog.csdnimg.cn/img_convert/6b501eb815f106dead3e636260a133c3.png)

 

LIST分区：

 

区别于RANGE分区，LIST分区的值是离散的

 

![image-20220428155542017](https://img-blog.csdnimg.cn/img_convert/da1e660779cea6afcf6d50d2b347bcb8.png)

 

HASH分区：

 

将数据均匀的分布到预定义的各个分区中，保证各分区数量大致都是一样的，大多数工作MySQL将自动完成，仅需将要被哈希的列值指定一个列值或表达式，以及需要分割的数量，默认1

 

![image-20220428160412138](https://img-blog.csdnimg.cn/img_convert/e113edeaa30995be3db5cd40bdf0975e.png)

 

MySQL还支持一种线性哈希的分区，使用更加复杂的算法确定新行插入分区表的位置，语法与hash类似

 

![image-20220428161104741](https://img-blog.csdnimg.cn/img_convert/eeffd67be2ee2f5b2fbdc48b452d73e6.png)

 

![image-20220428161114660](https://img-blog.csdnimg.cn/img_convert/3dd60d3f1dc5ed6a8b163e020966e392.png)

 

优势在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理大量数据的表，缺点是相较于HASH分区得到的数据，各分区分布可能不太均衡

 

KEY分区：

 

与HASH分区类型，不同的是，KEY分区使用MySQL数据库提供的函数进行分区

 

![image-20220428161558209](https://img-blog.csdnimg.cn/img_convert/d6f279c61c63bc539db86878f4c5881a.png)

 

在KEY分区使用LINEAR参数，等同于HASH分区，分区编号通过2的幂算法得到的

 

COLUMNS分区：

 

其他分区都需要分区条件为整型，而COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，无需进行转化，其次，可以对多个值进行分区

 

支持的数据类型：

 
 
- 所有的整数类型：INT、SMALLINT、TINYINT、BIGINT，不支持FLOAT和 DECIMAL 
- 日期类型：DATE和DATETIME 
- 字符串类型：CHAR、VARCHAR、BINARY、VARBINARY 

 

子分区：

 

在分区的基础上继续分区，也成为复合分区，MySQL支持在RANGE和LIST分区上再进行HASH或者是KEY的子分区

 

![image-20220428162603132](https://img-blog.csdnimg.cn/img_convert/e3549d6986d40387a4fa3143ae9b20c9.png)

 

注：分区并不总适用于OLTP应用，应根据应用类型规划分区设计

 

#### 4.3 索引与算法

 

几种二叉树的概念：

 

*二叉查找树：*

 

特点不平衡，在二叉树的基础上需要满足：任意节点的左子树上所有节点值不大于根节点的值，任意节点的右子树上所有节点值不小于根节点的值

 

![image-20220429090248307](https://s2.loli.net/2022/04/29/UPNc2xrAh3SbRqG.png)

 

当需要快速查找时，将数据存储在BST是一种常见的选择，因为此时查询时间取决于树高，平均时间复杂度是O(lgn)。然而，BST可能长歪而变得不平衡，如下图所示，此时BST退化为链表，时间复杂度退化为O(n)

 

![image-20220429090412080](https://s2.loli.net/2022/04/29/xk3Dp6ZEwGldQM7.png)

 

*平衡二叉树(AVL)：*

 

AVL树是严格的平衡二叉树，所有节点的左右子树高度差不能超过1；AVL树查找、插入和删除在平均和最坏情况下都是O(lgn)

 

AVL实现平衡的关键在于旋转操作：插入和删除可能破坏二叉树的平衡，此时需要通过一次或多次树旋转来重新平衡这个树。当插入数据时，最多只需要1次旋转(单旋转或双旋转)；但是当删除数据时，会导致树失衡，AVL需要维护从被删除节点到根节点这条路径上所有节点的平衡，旋转的量级为O(lgn)

 

由于旋转的耗时，AVL树在删除数据时效率很低；在删除操作较多时，维护平衡所需的代价可能高于其带来的好处，因此AVL实际使用并不广泛

 

*红黑树：*

 

与AVL树相比，红黑树并不追求严格的平衡，而是大致的平衡：只是确保从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。从实现来看，红黑树最大的特点是每个节点都属于两种颜色(红色或黑色)之一，且节点颜色的划分需要满足特定的规则(具体规则略)

 

![image-20220429091152733](https://s2.loli.net/2022/04/29/7ZtsEafeIL5MKQ3.png)

 

*B树：*

 

B树也称B-树(其中不是减号)，是为磁盘等辅存设备设计的多路平衡查找树，与二叉树相比，树的每个非叶节点可以有多个子树

 

定义B树最重要的概念是阶数(Order)，对于一颗m阶B树，需要满足以下条件：

 
 
- 每个节点最多包含 m 个子节点。 
- 如果根节点包含子节点，则至少包含 2 个子节点；除根节点外，每个非叶节点至少包含 m/2 个子节点。 
- 拥有 k 个子节点的非叶节点将包含 k - 1 条记录。 
- 所有叶节点都在同一层中。 

 

![image-20220429092836460](https://s2.loli.net/2022/04/29/tsi89c2Fq4N76Zb.png)

 

B树的优势除了树高小，还有对访问局部性原理的利用。所谓局部性原理，是指当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高

 

*B+树：*

 

与B树的区别：

 
 
- B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键。在MySQL中，这里所说的真实数据，可能是行的全部数据（如Innodb的聚簇索引），也可能只行的主键（如Innodb的辅助索引），或者是行所在的地址（如MyIsam的非聚簇索引） 
- B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现——一定会在叶节点出现，也可能在非叶节点重复出现。 
- B+树的叶节点之间通过双向链表链接 
- B树中的非叶节点，记录数比子节点个数少1；而B+树中记录数与子节点个数相同 

 

具有以下优势：

 
 
- **更少的IO次数：** B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高 
- **更适于范围查询：** 在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可 
- **更稳定的查询效率：** B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点 

 

B+树也存在劣势：由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛

 

B+树的两种索引形式：

 
 
- 聚集索引(主键索引)

 

InnoDB存储引擎是索引组织表，表中的数据按照逐渐的顺序存放，聚集索引就是将，每个表的主键构造一棵B+树，且叶子节点存放的是表的行记录数据，因此聚集索引的叶子节点成为数据页，每个数据页通过一个双线链表进行链接

 

实际的数据页只能按照一颗B+树进行排序，因此每张表只有一个聚集索引。查询优化器倾向于聚集索引，能够在索引的叶子节点找到数据 
- 辅助聚集索引

 

叶子节点不包含行的全部数据，除了包含键值外，每个页级别的索引行包含一个书签，用于高速存储引擎哪里可以找到与索引相关的行数据，因此辅助索引的书签就是相应行的聚集索引键 

 

添加/删除B+树索引：

 
 
- ALTER TABLE 
- CREATE / DROP INDEX 

 

MySQL的InnoDB存储引擎使用S锁解决添加删除索引时需要创建临时表与数据导入同步的过程，但仅限于辅助索引，对于主键索引不适用

 

B+树索引的使用原则：高选择，取表中少部分数据

 

**哈希算法：**

 
 
- 哈希表 
- InnoDB哈希算法 

 

​ InnoDB哈希函数：除法散列

 

​ 哈希冲突解决机制：链表

 

​ 散列算法：K = space(表空间号) << 20 + space + offset(偏移量)

 
 
- 自适应哈希索引

 

InnoDB存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升 

 

### 附录

 

**MySQL日志类型**

 
 
- 错误日志：服务运行过程中发生的严重错误日志 
- 二进制日志：也叫**Binlog**，其记录了对数据库所有的更改 
- 查询日志：记录了来自客户端的所有语句 
- 慢查询日志：这里记录了所有响应时间超过阈值的SQL语句，这个阈值我们可以自己设置，参数为`long_query_time`，其默认值为10s，且默认是**关闭**的状态，需要手动打开 

 

MySQL中的**Binlog**日志是重要日志，可以给其他类型存储引擎使用，记录了所有对数据库的修改

 
 
- statement：记录所有会修改数据的SQL，只记录sql，不记录SQL影响的所有行，减少了日志数量 
- row：只保存被修改的记录，会存在大量的日志 
- mixedlevel：属于statement与row的混用 

 

**InnoDB的中两个重要日志：**

 
 
- Redo log：重做日志，记录事务操作后的值，无论事务是否提交。更新数据时，会将更新记录写到redo log中，再更新缓存页的数据，最后根据更新策略，将数据回写到磁盘 
- Undo log：记录了事务开始之前的记录，可用于事务失败后的回滚

---
本页由 梦飞idc云平台（https://www.mfisp.com）提供。站点导航见 https://www.mfisp.com/llms.txt