• Mysql架构

• Mysql架构中SQL执行过程

• InnoDB存储引擎的架构中SQL执行过程

Mysql架构

MySQL驱动是什么？

下面这段maven配置中就引入了一个MySQL驱动。这里的mysql-connector-java就是面向Java语言的MySQL驱动。

数据连接池用来做什么

我们的系统通常会有多个线程来并发的处理多个请求，如果每个线程在每次访问数据库的时候，都基于MySQL驱动去创建一个数据库连接，执行SQL语句，然后频繁的销毁数据库连接，这样的效率十分低下。

所以使用一个数据库连接池，维持多个数据库连接，让多个线程并发执行SQL语句，执行完SQL语句的线程不会销毁这个数据库连接，而是把连接放回池子里，后续还可以继续使用。

因为有很多系统要与MySQL数据库建立很多个连接，那么MySQL也必然要维护与系统之间的多个连接。

Mysql架构中SQL执行过程

线程处理网络连接

通过数据库连接把要执行的SQL语句发送给MySQL数据库，Mysql服务器的线程会监听请求以及读取请求数据，从网络连接中读取和解析SQL语句。

SQL接口处理接收到的SQL语句

MySQL提供了一个组件，就是SQL接口（SQL Interface），他是一套执行SQL语句的接口，专门用于执行发送给MySQL的那些增删改查的SQL语句。

查询解析器解析SQL语句

查询解析器（Parser）就是负责对SQL语句进行解析的。

select id,name,age from users where id=1
->
从“users”表里查询数据
查询“id”字段的值等于1的那行数据
提取该行数据中的“id,name,age”三个字段。

查询优化器选择最优的查询路径

查询优化器（Optimizer）会针对复杂SQL语句生成查询路径树，然后从里面选择一条最优的查询路径出来。

执行器根据执行计划调用存储引擎的接口

执行器就会去根据我们的优化器生成的一套执行计划，按照一定的顺序和步骤，调用存储引擎的各种接口去完成SQL语句的执行计划，达到更新或者提取数据的目的。

调用存储引擎接口执行SQL语句

最终，存储引擎执行SQL语句时，会按照一定的步骤去查询内存缓存数据，更新磁盘数据，查询磁盘数据等。

InnoDB存储引擎的架构中SQL执行过程

系统通过数据库连接将SQL发送到了MySQL上，调用SQL接口，经过解析器解析SQL语句，优化器生成执行计划，接着由执行器负责这个计划的执行，调用InnoDB存储引擎的接口去执行。

通过研究一条更新语句的执行过程探究InndDb存储引擎的架构。

update users set name='xxx' where id=10

缓冲池(Buffer Pool)缓存数据

缓冲池（Buffer Pool）中会缓存很多的数据，用于避免查询时每次都需要读取磁盘。

比如对“id=10”这一行数据进行更新，会先看这行数据是否在缓冲池里，如果不在的话，会直接从磁盘里加载到缓冲池里来，接着对这行记录加独占锁。

undo log记录更新前的回滚数据

假设“id=10”这行数据的name原来是“zhangsan”，现在要更新为“xxx”，那么会先将旧值“zhangsan”写入到undo日志文件中去。在事务提交之前可以对数据进行回滚。

更新buffer pool中的缓存数据

更新的时候，先更新缓冲池中的记录，将内存里的“id=10”这行数据的name字段修改为“xxx”，此时内存数据和磁盘数据是不一样的。

Redo Log Buffer宕机复原

把对内存所做的修改写入到一个Redo Log Buffer里去，用来缓存redo日志的。redo日志是为了避免MySQL宕机时，用来恢复更新后的最新数据的。

此时如果Mysql宕机，内存里的数据都丢失了，由于没提交事务，就代表执行失败，会收到一个数据库的异常，此时磁盘上的数据维持原样。

提交事务将redo日志写入磁盘

提交事务时，会根据策略把redo日志从redo log buffer里刷入到磁盘文件中。

策略通过innodb_flush_log_at_trx_commit
来配置。

当参数值为0，提交事务时不会把redo log buffer里的数据刷入磁盘文件中。相当于提交事务了，结果mysql宕机了，然后此时内存里的数据全部丢失。

当参数值为1，提交事务时会把redo log buffe中数据从内存刷入到磁盘文件redo log里去。只要事务提交成功，那么redo log就在磁盘里了。此时mysql宕机，也可以根据redo日志去恢复之前做过的修改。一般建议redo日志刷盘策略设置为1，保证事务提交之后，数据绝对不能丢失。

当参数值为2，提交事务时会先把redo log buffe中数据写入磁盘文件对应的os cache缓存里去，而不是直接进入磁盘文件，可能1秒后才会把os cache里的数据写入到磁盘文件里去。

这种模式下，提交事务之后，机器宕机可能会丢失1s内的数据，但同时也避免了频繁去磁盘的读写，是一种性能更好的折中策略。

提交事务时同时写入binlog

redo log，偏向物理性质的重做日志，记录的是数据修改之后的值。是属于InnoDB存储引擎的日志文件。

binlog，偏向逻辑性的归档日志，记录的是对数据库更改的SQL语句。是属于mysql server的日志文件。

1、2、3、4几个步骤属于更新语句的阶段。5和6两个步骤属于提交事务的阶段。

binlog日志的刷盘策略

sync_binlog
参数可以控制binlog的刷盘策略，默认值是0，此时先进入os cache内存缓存，每隔1s再写入磁盘。

sync_binlog
参数设置为1时，会强制把binlog直接写入到磁盘文件中，在提交事务之后，磁盘上的binlog不会丢失。

redo log写入commit标记完成事务提交

binlog写入磁盘文件之后，会把本次更新对应的binlog文件名称和这次更新的binlog日志在文件里的位置，写入到redo log日志文件里去，同时在redo log日志文件里写入一个commit标记，完成了事务的提交。

在redo日志中写入commit标记，主要是用来保持redo log日志与binlog日志一致的。

如果在完成567之前mysql宕机了，因为没有redo log中的最终commit标记，事务是提交失败的。

只有redo刷入磁盘、binlog写入磁盘、写入commit标记，5、6、7三个步骤都执行完毕，才算是提交了事务。

后台IO线程随机将内存更新后的脏数据刷回磁盘

更新“update users set name='xxx' where id=10”，提交事务成功后，此时内存里的buffer pool中的缓存数据已经更新，同时磁盘里redo日志和binlog日志，都记录了“id=10”这行数据修改了“name='xxx'”。

但是这个时候磁盘上的数据文件里的“id=10”这行数据的name字段还是等于旧值。所以MySQL有一个后台的IO线程，会在之后某个时间里，随机的把内存buffer pool中的修改后的最新数据给刷回到磁盘上的数据文件里去。

总结

执行更新的时候，每条SQL语句，都会对应修改buffer pool里的缓存数据、写undo日志、写redo log buffer几个步骤。

当你提交事务的时候，一定会把redo log刷入磁盘，binlog刷入磁盘，完成redo log中的事务commit标记。

最后后台的IO线程会随机的把buffer pool里的脏数据刷入磁盘里去。

参考：从零开始带你成为MySQL实战优化高手01-04