和大多数数据库一样，MySQL是一种C/S架构的程序，服务器端负责提供数据服务器，客户端负责和服务器端打交道，传递客户的请求和返回请求的结果。和其他数据库不同的是，MySQL 是一种采用插件式存储引擎的数据库，用户可以根据不同的使用场景选择或定制存储引擎。例如，事务类场景可以选择 InnoDB，非事务类场景可以选择MyISAM。这些存储引擎究竟是怎样工作的呢？它们在MySQL中究竟扮演怎样的角色呢？本章主要介绍MySQL的体系结构。

MySQL的结构

MySQL 是一种存储引擎可插拔的关系型数据库，用户可以根据自己的需求选择不同的存储引擎。对于不同的存储引擎，Server层会抽象出相同的API，在切换存储引擎时无须进行大量的编码或流程更改即可正常使用，从而降低了使用和学习的成本。

如图所示，MySQL的结构大致分为两层，即Server层和存储引擎层。其中，Server层又由连接层和SQL层两部分组成。

连接层的主要功能包括提供通信协议、用户密码认证、权限获取和线程分配。协议方面支持Socket协议和TCP/IP协议。

SQL层的主要功能包括SQL解析、SQL预处理、选择最优执行计划、权限验证、数据页缓存。

存储引擎层主要包含InnoDB、MyISAM、NDB Cluster等插件式存储引擎，用户可以根据自己的需求自行选择。MySQL 8.0默认采用的存储引擎是InnoDB。

存储引擎

存储引擎作为MySQL的重要组件，用于处理不同的SQL请求。其中，InnoDB是默认的存储引擎，也是使用最广泛的存储引擎。MySQL 8.0将mysql库也改为InnoDB。

开发人员把存储引擎设计成可插拔式插件，这样就可以对正在运行的MySQL服务执行安装/卸载存储引擎的操作。

查询MySQL数据库支持哪些存储引擎很简单，只要执行一条SQL语句即可：

常见的存储引擎的主要特征和使用场景如表所示。

InnoDB的体系结构

InnoDB是MySQL 8.0默认采用的存储引擎，也是使用最广泛的存储引擎。InnoDB的体系结构如图所示。

内存结构

缓冲池

缓冲池主要用于缓存数据页（Data Page）和索引页（Index Page）。缓冲池的存在大大提高了访问InnoDB的速度。为了提高缓冲池的利用率，防止热数据被冲洗，InnoDB采用LRU算法管理缓冲池。下面详细介绍LRU算法。LRU算法的实现逻辑如图所示。

如图所示，缓冲池中的数据页以链表的形式存储。热数据（New Sublist）被存储在链表的头部，冷数据（OldSublist）被存储在链表的尾部。当向缓冲池中添加新页面时，最少使用的数据将被逐出链表，同时将新页面添加到链表的中点。

默认的LRU算法的规则包括以下几点。

• 3/8的缓冲池部分用于存储冷数据。
• 冷数据和热数据相交的边界称为链表的中点。
• 当数据页首次被读入缓冲池时，它将被插入链表的中点（冷数据的头部）。
• 当链表中的冷数据被再次读取时，它将被移到热数据的头部。
• 随着热数据不断增多，缓冲池中未被访问的数据会向链表的尾部移动而被逐步淘汰。
  

  更改缓冲区

更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池时，它会缓存二级索引更改。更改的类型包括insert、update、delete等DML操作。更改缓冲区会缓存多次二级索引更改操作，并在合适的时间写入磁盘，如图所示。

和聚集索引不同，二级索引可能是非唯一的，并且以相对随机的方式插入索引树。同样，delete或update也可能会影响索引树中相邻的索引页。更改缓冲区的存在，可以减少大量的随机访问I/O。

Q：为什么更改缓冲区的存在可以减少随机访问I/O呢？

A：这主要是因为二级索引数据的写入以页为基本单位，多次操作可能位于同一页面，将同一页面上的多次更改操作合并后再写入磁盘，可以将多次磁盘写入转换为一次磁盘写入。

如果索引包含降序索引列或主键包含降序索引列，则二级索引不支持更改缓冲。

自适应哈希索引

自适应哈希索引以哈希表的方式实现，由数据库自身实现并管理，使用者不能对其进行干预。需要注意的是，对于等值查找，自适应哈希索引可以提高查找效率。但范围查找或模糊查找是不能使用自适应哈希索引的。可以通过show engine innodb status语句查看自适应哈希索引的使用情况：

通过hash searches/s指标和non-hash searches/s指标可以大概了解哈希索引的使用率。对于无法从自适应哈希索引中受益的场景，将自适应哈希索引关闭可以减少不必要的性能开销。自适应哈希索引是分区的。每个索引都绑定到一个特定的分区，每个分区都由一个单独的闩锁保护。分区由innodb_adaptive_hash_index_parts 变量控制。innodb_adaptive_hash_index_parts变量的默认值为8，最大值为512。

日志缓冲区

日志缓冲区是用来临时存储Redo Log的。日志缓冲区的大小由innodb_log_buffer_size变量定义，默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘，如果日志缓冲区设置得太小，那么在大事务情况下可能会导致日志缓冲区频繁刷新。所以，对于更新、插入或删除频繁的场景，建议增大innodb_log_buffer_size变量的取值，减少磁盘I/O消耗。除了innodb_log_buffer_size变量会影响日志缓冲区的刷新频率，日志缓冲区的刷新频率默认由 innodb_flush_log_at_trx_commit 变量和innodb_flush_log_at_timeout 变量控制。所以，对于更新、插入或删除频繁的场景，建议增大innodb_log_buffer_size变量的取值，设置合理的刷新频率可以减少磁盘I/O消耗。

磁盘结构

nnoDB的磁盘结构主要包含各种表空间文件、Redo Log文件、双写缓冲区文件等。

表空间

表空间包含系统表空间、独立表空间、通用表空间、撤销表空间、临时表空间。

系统表空间

系统表空间主要用于存储更改缓冲。如果用户未使用独立表空间或通用表空间，那么它可能还包含用户表数据和索引数据。在之前的版本中，系统表空间还包含 InnoDB的字典表。在MySQL 8.0中，InnoDB将字典表存储在mysql库中。在之前的版本中，系统表空间还包含双写缓冲区。从MySQL 8.0.20开始，双写缓冲区存储在单独的双写文件中。

独立表空间

独立表空间的特征是每个InnoDB表的数据和索引存储在单独的数据文件中。与共享表空间相比，独立表空间具有以下几点优势。

• 表删除后，占用的空间将返还操作系统，共享表空间不会缩小，只会置为空闲空间等待下一次的使用。
• alter table的影子表所占用的空间不会返还操作系统。
• truncate table在独立表空间中的性能更好。
• 独立表空间可以使用多个文件设备，可以用于I/O优化、空间管理或备份。
• 独立表空间可以使用表空间迁移。
• 独立表空间中的表支持的行格式为dynamic与compressed。
• 当发生数据损坏且备份文件不可用时，使用独立表空间可以提高恢复概率。
• 独立表空间可以通过表空间文件大小计算或监控表的大小。
• 通用的Linux系统不允许并发写入同一文件，独立表空间或许可以提高并发性能。
• 表的大小受表空间大小的限制。独立表空间中每张表的大小最高可以达到64 TB。

独立表空间具有以下几点劣势。

• 可能会导致更多的空间碎片。
• fsync（刷盘）操作无法合并多张表的写操作。
• 需要更多的文件句柄。mysqld进程必须为每个表空间保留一个打开的文件句柄，独立表空间过多可能会影响性能。
• 需要更多的文件描述符号。
• 删除表时会扫描缓冲池，业务高峰期可能会导致系统不稳定，出现“毛刺”。
• 不能关闭表空间自增。
  

  通用表空间

通用表空间是指通过create tablespace语句创建的共享表空间。通用表空间支持以下功能。

• 与系统表空间类似，可以存储多张表。
• 表空间中的元数据消耗的内存更少。
• 支持独立磁盘。
• 支持所有的行格式（redundant、compact、dynamic、compressed）。
• 支持表空间移动。

通用表空间的限制包括以下几点。

• 已经存在的表空间不能更改为通用表空间。- 不支持创建临时通用表空间。
• 不支持临时表。
• 表删除后空间不支持回收，只能重用。
• alter table…discard tablespace语句、alter table…import tablespace语句不支持使用通用表空间中的表。
• 不支持分区表。
• 如果MySQL主从在同一主机上，则不支持add datafile语句。如果省略add datafile语句，则在数据目录中创建的文件名是唯一的。
• 从MySQL 8.0.21开始，不能在撤销表空间目录创建通用表空间。
  

  撤销表空间

撤销表空间既包含撤销日志，又包含事务回滚记录。在初始化MySQL实例时会创建两个默认的撤销表空间，默认位置由 innodb_undo_directory 变量定义。如果未定义innodb_undo_directory 变量，则在数据目录中创建。数据文件命名为 undo_001 和undo_002。数据字典中定义的对应名称是innodb_undo_001和innodb_undo_002。从MySQL 8.0.14开始，可以在运行时使用SQL语句创建额外的撤销表空间。

临时表空间

临时表空间分为会话临时表空间和全局临时表空间。

会话临时表空间存储用户创建的临时表。从MySQL 8.0.16开始，用于磁盘内部临时表的存储引擎是InnoDB。

MySQL服务在启动时会创建一个包含10个临时表空间的池。池的大小永远不会缩小，并且表空间会根据需要自动添加到池中。在正常关闭或初始化时会删除临时表空间池。会话临时表空间在第一次请求创建临时表时，会从临时表空间池中分配给会话。一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。分配给会话的临时表空间用于会话创建的所有临时表。当会话断开连接时，其临时表空间被截断并释放回临时表空间池中。

有40万个table ID用于会话临时表。因为每次启动MySQL服务时都会重新创建会话临时表空间池，所以当关闭MySQL服务时不会保留会话临时表空间的空间ID，并且空间ID可能会被重用。

全局临时表空间（ibtmp1）用于存储用户修改临时表数据产生的回滚段。全局临时表空间在正常关闭或初始化时被删除，并在每次启动MySQL服务时重新创建。全局临时表空间在创建时会动态生成一个表空间 ID。如果无法创建全局临时表空间，那么MySQL 服务拒绝启动。如果 MySQL 服务意外停止，那么不会删除全局临时表空间。在这种情况下，数据库管理员可以手动删除全局临时表空间或重新启动MySQL服务。重新启动MySQL服务会自动删除并重新创建全局临时表空间。

表

使用create table语句就可以很轻松地创建一张InnoDB表，默认不需要指定engine=innodb子句：

行格式

InnoDB支持4种行格式，即redundant、compact、dynamic和compressed。

innodb_default_row_format变量定义了默认行格式。

主键

• 查询语句最常使用。
• 不可为空。
• 不能重复。
• 很少更改。
• 最好是无符号整数。
  

  查看表属性

执行一条show table status语句：
除此之外，还可以通过information_schema.innodb_tables表查看：

### 索引

索引分为聚集索引、二级索引、排序索引和全文索引。索引的原理可以参考前面的文章，此外不再赘述。

聚集索引和二级索引

• 聚集索引用于存储数据，一个好的聚集索引可以加快查询速度。聚集索引具有以下特征。
• 一张表只能有一个聚集索引。
• 默认在主键上定义聚集索引。
• 如果表上没有主键，那么表上的第一个唯一索引会被定义为聚集索引。
• 如果表上既没有主键也没有唯一索引，那么自动生成一个隐藏的唯一ROW ID列来构造聚集索引。
  

  索引的物理结构

除空间索引外，InnoDB 索引都是 B 树（B-tree）数据结构。空间索引使用的是 R树（R-tree）数据结构。索引记录存储在树的叶子节点中。索引页的默认大小为16KB，大小由innodb_page_size变量在实例初始化时设置。

当有新的记录插入聚集索引时，InnoDB 会保留1/16 的页面空间用于后续的insert操作和update操作。

排序索引

排序索引的构建分为3个阶段。第一阶段，扫描聚集索引，生成索引条目并添加到排序缓冲区中。当排序缓冲区变满时，将排序后的条目写入临时文件，此过程也称为run。第二阶段，将临时文件中的所有条目执行合并排序。第三阶段，将合并排序后的索引条目插入B树索引。

全文索引

全文索引是基于文本列（char 列、varchar 列或 text 列）创建的，用于加快行的查询或修改操作。全文搜索使用match（）…against语句。

双写缓冲区

双写缓冲区是一个存储区域。在InnoDB将数据页写入InnoDB数据文件前，先将数据页写入双写缓冲区中。这是为了在 MySQL 意外崩溃或宕机后，InnoDB 在崩溃恢复期间可以在双写缓冲区中找到完整的数据页副本。虽然数据页会被写两次，但双写缓冲并不需要两倍的I/O开销。双写缓冲区的写入是一个连续的操作，只需要调用一次fsync操作，在MySQL 8.0.20之前，双写缓冲区位于系统表空间中。从MySQL 8.0.20开始，双写缓冲区存储在单独的双写缓冲文件中。