文末领取【医疗行业数据盘点报告】

MySQL 在整体架构上分为 Server 层和存储引擎层。其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

连接器

负责和客户端建立连接，获取用户权限以及维持和管理连接。

通过show processlist来查询连接的状态。在用户建立连接后，即使管理员改变连接用户的权限，也不会影响到已连接的用户。默认连接时长为 8 小时，超过时间后将会被断开。

简单说下长连接：

1. 优势：在连接时间内，客户端一直使用同一连接，避免多次连接的资源消耗。

2. 劣势：在MySQL执行时，使用的内存被连接对象管理，由于长时间没有被释放，会导致系统内存溢出，被系统kill. 所以需要定期断开长连接，或执行大查询后，断开连接。MySQL 5.7 后，可以通过mysql_rest_connection初始化连接资源，不需要重连或者做权限验证。

查询缓存

当接受到查询请求时，会现在查询缓存中查询（key/value保存），是否执行过。没有的话，再走正常的执行流程。

但在实际情况下，查询缓存一般没有必要设置。因为在查询涉及到的表被更新时，缓存就会被清空。所以适用于静态表。在MySQL8.0后，查询缓存被废除。

分析器

1. 词法分析：如识别select，表名，列名，判断其是否存在等。

2. 语法分析：判断语句是否符合MySQL语法。

优化器

确定索引的使用，join表的连接顺序等，选择最优化的方案。

执行器

在具体执行语句前，会先进行权限的检查，通过后使用数据引擎提供的接口，进行查询。如果设置了慢查询，会在对应日志中看到rows_examined来表示扫描的行数。在一些场景下（索引），执行器调用一次，但在数据引擎中扫描了多行，所以引擎扫描的行数和rows_examined并不完全相同。

不预先检查权限的原因：如像触发器等情况，需要在执行器阶段才能确定权限，在优化器阶段无法验证。

MySQL 日志模块

如前面所说，MySQL整体分为Server层和数据引擎层，而每层也对应了自己的日志文件。如果选用的是InnoDB引擎，对应的是redo log文件。Server层则对应了binlog文件。至于为什么存在了两种日志系统，咱们往下看。

1. redo log

redo log是InnoDB特有日志，为什么要引入redo log呢，想象这样一个场景，MySQL为了保证持久性是需要把数据写入磁盘文件的。我们知道，在写入磁盘时，会进行文件的 IO，查找操作，如果每次更新操作都这样的话，整体的效率就会特别低，根本没法使用。

既然直接写入磁盘不行，解决方法就是先写进内存，在系统空闲时再更新到磁盘就可以了。但光更新内存不行，假如系统出现异常宕机和重启，内存中没有被写入磁盘的数据就会被丢掉，数据的一致性就出现问题了。

这时redo log就发挥了作用，在更新操作发生时，InnoDb会先写入redo log日志（记录了数据发生了怎么样的改变），然后更新内存，最后在适当的时间再写入磁盘。先写日志，在写磁盘的操作，就是常说到的WAL（Write-Ahead- Logging）技术。

redo log的出现，除了在效率上有了很大的改善，还保证了MySQL具有了crash-safe的能力，在发生异常情况下，不会丢失数据。

在具体实现上redo log的大小是固定的，可配置一组为 4 个文件，每个文件1GB，更新时对四个文件进行循环写入。

write pos记录当前写入的位置，写完就后移，当第写入第4个文件的末尾时，从第0号位置重新写入。

check point表示当前可以擦除的位置，当数据更新到磁盘时，check point就向后移动。

write pos和check point之间的位置，就是可以记录更新操作的空间。当write pos追上check point ，不在能执行新的操作，先让check point去写入一些数据。

可以将innodb_flush_log_at_trx_commit设置成1，开启redo log持久化的能力。

2. binlog

binlog则是Server层的日志，主要用于归档，在备份，主备同步，恢复数据时发挥作用，常见的日志格式有row, mixed, statement三种。

可以通过sync_binlog=1开启binlog写入磁盘。

这里对binlog和 redo进行下区分：

所有者不同：binlog是 Server层，所有引擎都可使用。redo log是 InnoDB特有的。

类型不同：binlog是逻辑日志，记录的是语句的原始逻辑（比 statement）。redo log是物理日志，记录某个数据页被做了怎样的修改。

数据写入的方式不同：binog日志会一直追加，而redo log是循环写入。

功能不同：binlog用于归档，而redo log用于保证crash-safe。

3. 两阶段提交

一条更新语句，在InnoDB引擎下的更新过程如下。在更新内存后，将写入redolog和写入 binlog放在一起成为一个事务最后一起写入redo log和 binlog的过程就是常说的两阶段提交。用于保证当有意外情况发生时，数据的一致性。

这里假设下，如果不采用两阶段提交会发生什么？

先写redo log后写binlog假设在写入redo log后，MySQL发生异常重启，此时binlog没有写入。在重启后，由于redolog已经写入，此时数据库的内容是没有问题的。但此时，如果想要拿binlog进行备份或恢复，发现会少了最后一条的更新逻辑，导致数据不一致。

先写binlog和redo log. binlog写入后，MySQL异常重启，redo log没有写入。此时重启后，发现redo log没有成功写入，认为这个事务无效，而此时binlog却多了一条更新语句，拿去恢复后自然数据也是不一致的。

再分析下两阶段提交的过程：

在写redo log prepare阶段奔溃，重启后，发现redo log没写入，回滚此次事务。

如果在写binlog时奔溃，重启后，发现binlog未被写入，回滚操作

如果在写入redo log和binlog后崩溃，重启后，发现没提交，则进行commit。

总结

在文章开始部分，说明了MySQL的整体架构分为Server层和引擎层，并简要说明了一条语句的执行过程。接着MySQL在5.5后选用InnoDB作为默认的引擎，就是因为比原生的MyISAM多了事务以及crash-safe的能力。

而crash-safe就是由redo log实现的。与redo log类似的日志文件还有binlog，是Server引擎的日志，用于归档和备份数据。

最后提到了，为了保证数据的一致性，将redo log和binlog放入相同的事务中，也就是常提到的两阶段提交操作。

End.

作者：以终为始

来源：博客园

本文为转载分享，如侵权请联系后台删除

长按下方海报领取【医疗行业数据盘点报告】

MySQL查询性能优化详解 | 推荐收藏

9个Excel技巧，让你的工作效率飞升！

引爆用户增长必备的5大要素！