MySQL整体架构解析

mysql架构

首先，开局一张图，看一下mysql结构：

表面上看mysql的架构很简单，就是由SQL层和引擎层组成的，但是它们都还有很多的小模块，mysql不仅自带了许多存储引擎，而且它支持第三方插件式引擎，可以根据自己的业务需求使用不同的存储引擎，正如上图中看到的innodb、Myiseam等。我们着重注意SQL层的执行流程：

以下是对相关模块的简单介绍：

• 初始化模块：它在mysql服务启动时，对整个系统进行相关的初始化操作，比如内存空间的申请、系统变量的初始化、存储引擎的初始化等。

• 网络交互模块：底层网络交互抽象出的接口API，实现了底层网络数据的接收与发送。

• Client-Server模块：实现了客户端与mysql交互过程中的所有协议，比如TCP/IP和Unix Socket。

• 连接管理模块：负责监听对mysql服务的各种请求，并转发连接到线程管理模块，每一个连接上mysql的客户端请求都会被分配一个连接线程来单独服务。

• 线程管理模块：管理维护连接线程，包括线程的创建、回收、缓存等。

• 用户模块：对用户的登录连接的权限控制和用户的授权管理。

• 命令分发模块：对请求的命令按照不同的情况分发到下属的处理模块。

• 查询缓存模块：将客户端提交的select请求的返回结果缓存到内存中欧冠，通过与该query语句进行一个hash进行映射。当该query获取数据的表发生变化后，mysql会自动使该语句的缓存失效。

• 日志记录模块：负责记录系统级别的逻辑层的日志，包括error log、binary log、slow query log等，关于mysql的日志记录，后面会进行介绍。

• query解析模块：对query语句进行语义和语法分析，按照操作类型分发到不同的处理模块。

  在mysql中，将客户端请求分为了两种类型：一种是query，需要调用解析器也就是query解析和转发模块的解析才能够执行的请求；一种是command，不需要调用解析器就可以直接执行的请求。

• 查询优化模块：针对select请求，优化客户请求的query。

• 表变更模块：针对一些DML/DDL请求，对表进行修改。

• 表维护模块：针对一些DDL请求，对表的进行状态检查、错误修复以及SQL语句的分析优化等工作。

• 复制模块：复制模块可以分为Master和Slave模块两部分：

  • Master：主要负责在Replication环境中读取Master端的binary日志，以及与Slave端的I/O线程交互等工作。
  • Slave：在系统中主要体现在两个线程上面。一个是负责Master请求和接受binary日志，并写入本地relay log中的I/O线程。另外一个是负责从relay log中读取相关日志事件，然后解析成可以在Slave端正确执行并得到Master端完全相同的结果的命令并再交给Slave`执行的SQL线程。

• 状态模块：针对status请求，在客户端请求系统状态的时候，将各种状态数据返给用户，像各种show status命令，show variables命令等。

• 访问控制模块：根据用户模块中各用户的授权信息，以及数据库自身特有的各种约束，来控制用户对数据的访问。

• 表管理模块：维护表的定义文件.frm、各个表结构信息的缓存，以及表级别的锁管理。

• 存储引擎接口模块：这个模块实际上是一个抽象类，将不同存储引擎的数据处理高度抽象化，实现底层数据存储引擎的插件式管理。

mysql执行流程：

1. 当执行启动mysql命令之后，mysql的初始化模块就从系统配置文件中读取系统参数和命令行参数，并按照参数来初始化整个系统，如申请并分配缓冲，初始化全局变量，以及各种结构等。同时各个存储引擎也被启动，并进行各自的初始化工作。当整个系统初始化结束后，由连接管理模块接手。连接管理模块会启动处理客户端连接请求的监听程序，包括TCP/IP的网络监听。这样，MySQL Server就基本启动完成，准备好接受客户端请求了。
2. 当连接管理模块监听到客户端的连接请求（借助网络交互模块的相关功能），双方通过Client & Server 交互协议模块所定义的协议建立连接后之后，连接管理模块就会将连接请求转发给线程管理模块，去请求一个连接线程。
3. 线程管理模块马上又会将控制交给连接线程模块，连接线程模块在接到连接请求后，首先会检查当前连接线程池中是否有被缓存的空闲连接线程，如果有，就取出一个和客户端请求连接上，如果没有空闲的连接线程，则建立一个新的连接线程与客户端请求连接。连接线程模块在收到连接请求后，首先通过调用用户模块进行授权检查，只有客户端请求通过了授权检查后，它才会将客户端请求和负责请求的连接线程连上。
4. 当客户端请求和连接线程通过协议连接之后，连接线程就开始处理客户端请求发送过来的各种命令（或者query），接受相关请求。它将收到的query语句转给查询解析和转发模块，query解析器先对query进行基本的语义和语法解析，然后根据命令类型的不同，有些会直接处理，有些会分发给其他模块来处理。
5. 如果是一个query类型的请求，会将控制权交给query解析器。query解析器首先分析
   看是不是一个select类型的query，如果是，则调用查询缓存模块，让它检查该query在缓存队列中是否已经存在。如果有，则直接将缓存中的数据返回给连接线程模块，然后通过与客户端的连接的线程将数据传输给客户端。如果不是一个可以被缓存的query类型，或者缓存中没有该query的数据，那么query将被继续传回query解析器，让query解析器进行相应处理，再通过query分发器分发给相关处理模块。
6. 如果解析器解析结果是一条未被缓存的select 语句，则将控制权交给查询优化器，
   如果是DML或者是DDL语句，则会交给表变更管理模块，如果是一些更新统计信息、检测、修复和整理类的query则会交给表维护模块去处理，复制相关的query则转交给复制模块去进行相应的处理，请求状态的query则转交给了状态收集报告模块。
7. 在各个模块收到query解析与分发模块分发过来的请求后，首先会通过访问控制模块检查连接用户是否有访问目标表以及目标字段的权限，如果有，就会调用表管理模块请求相应的表，并获取对应的锁。表管理模块首先会查看该表是否已经存在于缓存表中，如果已经打开则直接进行锁相关的处理，如果没有在缓存中，则需要再打开表文件获取锁，然后将打开的表交给表变更管理模块。
8. 当表变更管理模块获取打开的表之后，就会根据该表的相关meta信息，判断表的
   存储引擎类型和其他相关信息。根据表的存储引擎类型，提交请求给存储引擎接口模块，调用对应的存储引擎实现模块，进行相应处理。
9. 当一条query或者一个command 处理完成（成功或者失败）之后，控制权都会交还给连接线程模块。如果处理成功，则将处理结果（可能是一个结果集，也可能是成功或者失败的标识）通过连接线程反馈给客户端。如果处理过程中发生错误，也会将相应的错误信息发送给客户端，然后连接线程模块会进行相应的清理工作，并继续等待后面的请求，重复上面提到的过程，或者完成客户端断开连接的请求。

以上是mysql语句的执行流程，下面我们将看看mysql的数据存储是怎么样的。

mysql物理文件

日志文件

对于日志的输出格式，可以通过SQL语句进行查询。默认情况下是文件，我们可以设置为表格或者其他属性：

mysql的日志文件主要包括以下四种：

• 错误日志：error log

  记录mysql每次启动和关闭的信息 ，以及运行过程中的警告和错误信息。默认情况下，系统记录错误日志的功能是开启的，错误信息会被输出到指定目录，也可以自定义设置指定的存放路径：

  可以使用flush logs命令来让mysql备份旧日志文件并生成新的日志文件 ，备份文件名以.old结尾。

• 二进制日志：binary log & binary log index

  它记录的是对数据进行修改的query，每一项包括query语句、执行时间、消耗的资源、相关的事务信息。默认情况下它是关闭的：

• 查询日志：query log

  查询日志记录了mysql中的所有query，它默认是关闭的：

• 慢查询日志：slow query log

  慢查询日志记录的是执行时间较长的query，它默认是关闭的，对于执行时间较长的定义可以通过时间阈值来确定：

数据文件

在mysql中，每一个数据库在数据目录下都会有一个文件夹，里面存储这个数据库的表信息以及数据文件。不同的存储引擎有不同的定义数据文件的方式，它们的扩展名也不一样。比如MyISAM用.MYD作为扩展名，Innodb用.ibd，Archive用.arc等。

我们可以通过SQL语句查询数据目录的位置：

我们进入某一个数据文件夹，查看它下面的数据文件：

发现它有三种文件：.frm、.opt、.idb，下面对这三种文件进行简单的说明：

.frm：不管什么存储引擎，每一个表都会有一个以表名命名的.frm文件。该文件存储与表相关的元数据，包括表结构的定义信息。

.idb：.idb是innodb存储引擎的独享式存储数据生成的文件，每一个表有一个.idb文件。

Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个）ibdata文件。ibdata文件可以通过innodb_data_home_dir和innodb_data_file_path两个参数共同配置组成，innodb_data_home_dir配置数据存放的总目录， 而innodb_data_file_path配置每一个文件的名称。

innodb_data_file_path中可以一次配置多个ibdata文件。文件可以是指定大小，也可以是自动扩展的，但是Innodb限制了仅仅只有最后一个ibdata 文件能够配置成自
动扩展类型。当我们需要添加新的ibdata 文件的时候，只能添加innodb_data_file_path配置的最后，而且必须重启mysql才能完成ibdata的添加工作。

不同的存储引擎的数据文件不同，比如MyISAM存储引擎有两种数据文件：.MYD和.MYI，.MYD文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个.MYD文件与之对应，同样存放于所属数据库的文件夹下，和.frm文件在一起。

.MYI主要存放MyISAM 表的索引相关信息。对于MyISAM存储来说，可以被缓存的内容主要就是来源于.MYI文件中。每一个MyISAM表对应一个.MYI文件。

.opt：db.opt是mysql在创建数据库时自动生成的文件，用来记录数据库创建时的指定属性，比如字符集和排序规则。

mysql存储引擎

mysql使用插件式存储引擎体系结构，让存储引擎层和SQL层各自更加独立，甚至可以做到在线加载其它的存储引擎。MySQL 的插件式存储引擎主要包括MyISAM，Innodb，NDB Cluster，Maria，Falcon，Memory，Archive，Merge，Federated 等，其中最著名而且使用最为广泛的MyISAM和Innodb两种存储引擎。我们可以看到mysql默认的存储引擎为Innodb：

接下来将选取MyISAM和Innodb存储引擎进行说明。

Innodb

InnoDB是一种兼顾了高可靠性和高性能的通用存储引擎，InnoDB是默认的MySQL存储引擎。Innodb存储引擎是现在最常用的存储引擎，接下来我们将通过它的整体架构来看看它（图来自mysql官方文档）：

Innodb的功能有：

内存结构

内存结构由Buffer Pool（缓冲池）、Change Buffer（更改缓冲池）、Adaptive Hash Index（自适应哈希索引）、Log Buffer（日志缓冲区）构成。

缓冲池：在InnoDB访问表和索引数据时会在缓冲池中进行缓存，它使用的是LRU算法，默认情况下，该算法的执行流程如下：

• 3/8的缓冲池用于存储旧列表。
• 列表的中点是新列表的尾部与旧列表的头相交的边界。
• 当InnoDB将页面读入缓冲池时，它首先将其插入中点（旧子列表的头部）。
• 当访问旧子列表中的页面，会将其移至新列表的开头。当该页是因为预读操作访问的，则第一次访问不会更新，如果是因为用户操作而读取的，则更新到新列表。

预读取是mysql I/O优化的一种策略，预读机制就是发起一个I/O请求，根据顺序访问的缓冲池中的页面来预测很快可能需要哪些页面。

当第一次向数据库请求数据时，会将读请求放入请求队列中，然后进程管理器取出请求，进行SQL解析、分发，取出数据，放入响应队列中，最后数据库从响应队列中将数据取走，这就完成一次数据读操作过程。

接着进程继续处理请求，它会判断后面几个数据读请求的数据是否相邻，如果相邻，那么它会取出一部分连续的页面，这样当在执行SQL时不需要去总页面查找，只需要从取出的部分页面中查找就可以了。

缓冲池的示例如下：

缓冲池的相关配置如下：

更改缓冲区：更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存这些页的更改。缓冲可能是由DML引起的，后面当其他读取操作将页加载到缓冲池中时，将合并这些缓冲。它的主要作用就是将二级索引的数据操作缓存下来，以此减少二级索引的随机I/O。

它的结构如下：

自适应哈希索引：自适应哈希索引是Innodb通过观察建立哈希索引是否可以提高性能，如果可以的话就自动创建索引，所以被称为自适应。

哈希索引和B+索引不一样：

• 它们只适用于=、<=>（此操作符和=操作符一样，还可以用来与null进行比较）操作符（速度很快），不能用于其它操作符。
• 优化器无法使用哈希索引来加快ORDER BY操作速度。

日志缓冲区：日志缓冲区是用于保存要写入磁盘上的日志文件的数据的存储区域。

磁盘结构

Innodb的磁盘结构用于将缓冲区的内容文件来进行持久化，它主要分为：表文件、索引文件、日志文件、双写缓冲区。

对于表文件，在Innodb中，存在一个表空间的概念，它被分为两种形式：共享表空间（所有表和索引数据被存放在同一个表空间中）和独享表空间（每个表的数据和索引被存放在一个单独的.ibd文件中）。

注意：共享表空间是必须存在的，因为Innodb的undo信息和其他一些元数据信息都是存放在共享表空间里面的。

共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式的，自动扩展形式的文件可以设置文件的最大大小和每次扩展量。在创建自动扩展的数据文件的时
候，加上最大尺寸的属性，不仅可以限制文件的增长，而且便于自身的维护。

Innodb的表空间有以下几种：

• The System Tablespace（系统表空间）：存储更改缓冲区。
• File-Per-Table Tablespaces（独立表空间）：存储单个Innodb表的数据和索引。
• General Tablespaces（通用表空间）：使用CREATE TABLESPACE创建的共享表。
• Undo Tablespaces（undo表空间）：存储undo日志。
• Temporary Tablespaces（临时表空间）：临时表包括会话临时表和全局临时表。

双写缓冲区：双写缓冲区是一个存储区域，在该区域中，在 InnoDB将页面写到InnoDB数据文件中的适当位置之前，先从缓冲池中刷新页面 。如果在页面写入过程中发生mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到页面的原副本。

MyISAM

MyISAM是基于传统的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）类型，它支持以下三种类型的索引：

• B-Tree索引：所有的索引节点都按照balance tree的数据结构来存储，所有的索引数据节点都在叶节点。
• R-Tree索引：主要设计用于为存储空间和多维数据的字段做索引。
• Full-text索引：即全文索引，存储结构也是b-tree。主要是为了解决在需要用模糊查询的低效问题。

MyISAM的功能如下如所示：（图自mysql官方文档）

在早期，MyISAM可以对数据进行分区，但是在mysql8.0中，该引擎不提供分区的支持了。

MyISAM的数据存放格式是分为静态（FIXED）固定长度、动态（DYNAMIC）可变长度以及压缩（COMPRESSED）这三种格式。可以在创建表的时候通过ROW_FORMAT来指定{COMPRESSED | DEFAULT}，默认是不压缩的。在非压缩的情况下，是静态还是动态，就和表中个字段的定义相关了。只要表中有可变长度类型的字段存在，那么该表就是DYNAMIC格式的，如果没有任何可变长度的字段，则为FIXED格式，可以通过ALTER TABLE命令，强行将一个带有VARCHAR类型字段的DYNAMIC的表转换为FIXED，但是所带来的结果是原VARCHAR字段类型会被自动转换成CHAR类型。相反如果将FIXED转换为DYNAMIC，也会将CHAR类型字段转换为VARCHAR类型。

在前面讲到MyISAM的数据文件由.MYD和.MYI组成，在每个索引文件的标头都有一个计数器，用于检查表是否已经关闭。计数器的工作方式如下：

1. 在MySQL中第一次更新表时，索引文件标题中的计数器增加。
2. 计数器在后面更新期间不会更改。
3. 当关闭表的最后一个实例时（由于执行了FLUSH TABLES操作或因为表高速缓存中没有空间）更新了表，计数器都会递减。
4. 当修理或检查表时，如果发现表没有问题，则计数器将重置为零。
5. 如果计数器为零，则在关闭时计数器不会递减。

如果计数器不同步，可能会报以下错误：

clients are using or haven't closed the table properly

有关MyISAM存储引擎的详细信息，可查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

Innodb与MyISAM区别

1. InnoDB支持事务，而MyISAM不支持。

2. InnoDB 中不保存表的具体行数，而MyISAM保存。

3. Innodb一般情况下使用行锁（除非是对整个表进行操作），MyISAM使用表锁。

4. 删除表时，InnoDB是一行一行的删除，MyISAM是直接删除整个表。

5. MyISAM存储引擎的某个表文件出错之后，仅影响到该表，同时可以使用check table来尝试校验它，并使用repair table来尝试进行修改。

6. InnoDB表必须有主键（用户没有指定的话会自动创建一个主键），而Myisam可以没有。

7. MyISAM读性能比Innodb强。

8. MyISAM的索引和数据是分开的，并且索引是有压缩的，能加载更多索引，而nnodb是索引和数据是一起的，没有使用压缩。