目录

前言

MySQL架构图

连接层​编辑

服务层

连接池

缓存

解析器

优化器

执行器

存储引擎层

MyISAM 与 InnoDB 引擎的区别

如何在两种存储引擎中进行选择？

系统文件存储层

数据文件

日志文件（错误日志，binglog和慢查询日志）

redo log文件：

undo log文件：

配置文件

一条SQL执行流程

更新数据SQL的执行流程

总结

---

前言

从互联网发展至今20几年，mysql这个数据库系统也一直伴随着我们一起发展。mysql是在我们开发过程中常用的数据库利器，也是最让人耳熟能详的，大中小型的业务项目几乎都能胜任，用的多，用的广，大厂面试也常问。

自从2000年mysql开放源代码以来，数据库行业蓬勃发展，特别是nosql的发展更是百家齐放，百家争鸣。这里面多多少少要归因于mysql AB公司的开放胸怀。2000年之后的数据库行业的发展在技术上多多少少都会参考一些mysql的实现思路，以及由mysql的实现思路基础上而激发出新的创新技术来应对不同的数据管理需求的场景。

所以，如果你想成为一名合格或者优秀的架构师，数据库这块的知识可以先从深入学习mysql开始。

今天我们先来聊聊MySQL的架构体系

MySQL架构图

如下是mysql的分层架构图，下面我们对如下的架构图进行拆分，并做简要的说明

连接层

与客户端打交道，上面已经写明了能支持的的语言。客户端的链接支持的协议很多，比如我们在 Java 开发中的 JDBC。

服务层

连接池

主要是负责存储和管理客户端与数据库的链接，一个线程负责管理一个连接。自从引入了连接池以后，官方报道：当数据库的连接数达到128后，使用连接池与没有连接池的性能是提升了n倍（反正就是性能大大的提升了）。

连接建立完成后，就可以执行select语句了。执行逻辑就会先来到缓存模块。

缓存

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果会以key-value对的形式存储在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key（命中），那么这个value就会被直接返回给客户端。

如果在缓存中未命中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。这里可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的某一条数据更新，这个表上所有的查询缓存都会被清空。

因此可能很费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。

比如：一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。

「注意」：MySQL 8.0版本直接将查询缓存的整块功能删掉了，标志着MySQL8.0开始彻底没有缓存这个功能了。

解析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

做完了词法分析以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

如果我们在拼写SQL时候，少了或者写错了某个字母，，就会收到“You have an error in your SQL syntax”的错误提醒。

比如下面这个案例：

错误在于WHERE关键字中差了一个E。

同样，我们使用的SQL如果某个字段不存在。

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容，仅供参考，有时候这个提示也不是非常靠谱。

经过分析器对SQL进行了分析，并且没有报错。那么此时就进入优化器中，对SQL进行优化。

优化器

优化器主要是在我们的数据库表中，如果存在多个多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序 。

比如说：

SELECT a.id, b.id FROM t_user a join t_user_detail b WHERE a.id=b.user_id and a.user_name='田维常' and b.id=10001

它会在条件查询上进行优化处理。

优化器处理完成过后，此时就已经确定了SQL的执行方案。然后继续进入执行器中。

执行器

首先，肯定是要判断权限，就是有没有权限执行这条SQL。工作中可能会对某些客户端进行权限控制。

比如说：生产环境中，对于大部分开发人员都只开查询权限，没有增删改权限（部分小公司除外）。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎层

存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念 。

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(Table Type，即存储和操作此表的类型)。

• MySQL5.5版本(mysql 版本 < 5.5版本) 以前，默认使用的存储引擎是MyISAM 。
• MySQL5.5版本(mysql 版本 >= 5.5版本) 以后，默认使用的存储引擎是InnoDB 。

下面对部分相对使用多的引擎进行一个对比：

在实际项目中，大多数使用InnoDB，然后是MyISAM，至于其他存储引擎使用的非常至少。

MyISAM 与 InnoDB 引擎的区别

Mysql5.5 版本之前默认的存储引擎就是 MyISAM 存储引擎，MySQL 中比较多的系统表使用 MyISAM 存储引擎，系统临时表也会用到 MyISAM 存储引擎，但是在 Mysql5.5 之后默认的存储引擎就是 InnoDB 存储引擎了。

两个主要原因：

• 第一个原因是MyISAM 是表级锁定，限制了数据库读/写的性能；
• 另外一个原因是MyISAM 不支持事务，基于以上两点，InnoDB 引擎可以锁到行。

如何在两种存储引擎中进行选择？

• 是否有事务操作？有，InnoDB。
• 是否存储并发修改？有，InnoDB。
• 是否追求快速查询，且数据修改较少？是，MyISAM。
• 是否使用全文索引？如果不引用第三方框架，可以选择MyISAM，但是可以选用第三方框架和InnDB效率会更高。

系统文件存储层

系统文件存储层主要是负责将数据库的数据和日志存储在系统的文件中，同时完成与存储引擎的之间的打交道，是文件的物理存储层。

比如：数据文件、日志文件、pid文件、配置文件等。

数据文件

「db.opt文件」：记录这个数据库的默认使用的字符集和校验规则。

「frm文件」：存储于边相关的元数据信息，包含表结构的定义信息等，每一张表都会有一个frm文件与之对应。

「MYD文件」：MyISAM存储引擎专用的文件，存储MyISAM表的数据信息，每一张MyISAM表都有有一个.MYD文件。

「MYI文件」：也是MyISAM存储引擎专用的文件，存放MyISAM表的索引相关信息，每一张MyISAM表都有对应的.MYI文件。

「ibd文件和ibdata文件」：存放InnoDB的数据文件（包括索引）。InnoDB存储引擎有两种表空间方式：独立表空间和共享表空间。

• 独享表空间使用ibd文件来存放数据，并且每一张InnoDB表存在与之对应的.ibd文件。
• 共享表空间使用ibdata文件，所有表共同使用一个或者多个.ibdata文件。

「ibdata1文件」：系统表空间数据文件，存储表元数据、Undo日志等。

「ib_logfile0、ib_logfile0文件」：Redo log日志文件。

日志文件（错误日志，binglog和慢查询日志）

错误日志：默认是开启状态，可以通过命令查看：

show variables like '%log_error%';

二进制日志binary log：记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行耗时；但是不记录查询select、show等不修改数据的SQL。主要用于数据库恢复和数据库主从复制。也是大家常说的binlog日志。

show variables like '%log_log%';//查看是否开启binlog日志记录。show variables like '%binllog%';//查看参数show binary logs;//查看日志文件

慢查询日志：记录查询数据库超时的所有SQL，默认是10秒。

show variables like '%slow_query%'；//查看是否开启慢查询日志记录。show variables '%long_query_time%'；//查看时长

通用查询日志：记录一般查询语句；

show variables like '%general%'；

redo log文件：

redo log顾名思义，就是重做日志，每次数据库的SQL操作导致的数据变化它都会记录一下，具体来说，redo log是物理日志，记录的是数据库页的物理修改操作。如果数据发生了丢失，数据库可以根据redo log进行数据恢复。

InnoDB通过Force Log at Commit机制实现事务的持久性，即当事务COMMIT时，必须先将该事务的所有日志都写入到redo log文件进行持久化之后，COMMIT操作才算完成。

当事务的各种SQL操作执行时，即会在缓冲区中修改数据，也会将对应的redo log写入它所属的缓存。当事务执行COMMIT时，与该事务相关的redo log缓冲必须都全部刷新到磁盘中之后COMMIT才算执行成功。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，InnoDB存储引擎会使用redo log恢复到掉宕机前的时刻，以此来保证数据的完整性

InnoDB的redo log 是固定大小，即记录满了以后就从头循环写

undo log文件：

在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作

undo log的存储不同于redo log，它存放在数据库内部的一个特殊的段(segment)中，这个段称为回滚段。回滚段位于共享表空间中。undo段中的以undo page为更小的组织单位。undo page和存储数据库数据和索引的页类似（undlog存的就是事务提交前的真实数据）。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undo log（也看成数据库数据）的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

大家常熟悉的MVCC能力就是通过undolog来实现的。

配置文件

用于存放MySQL所有的配置信息的文件，比如：my.cnf、my.ini等。

「pid文件」

pid文件是mysqld应用程序在Linux或者Unix操作系统下的一个进程文件，和许多其他Linux或者Unix服务端程序一样，该文件放着自己的进程id。

「socket文件」

socket文件也是Linux和Unix操作系统下才有的，用户在Linux和Unix操作系统下客户端连接可以不通过TCP/IP网络而直接使用Unix socket来连接MySQL数据库。

一条SQL执行流程

客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回

sql执行流程

更新数据SQL的执行流程

取数据行: 执行器先找引擎取 ID=2 这一行（ID 是主键，引擎直接通过B+树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。)

写入undolog：获取ID=2 这一行的数据写入到对应的undolog

更新数据： 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

更新内存：引擎将这行新数据更新到内存中，

更新 redo log：同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

写入binlog：执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

提交事务：执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，触发redolog刷盘，然后事务标记为提交成功。

总结

文章的重点还是让大家对Mysql的整体架构以及sql执行流程有个比较清晰的认识，便于后续学习Mysql其他方面的知识。