MySQL 架构设计

MySQL 架构设计

研究MySQL数据库的底层原理和各种实践案例，以及互联网公司的技术方案

一、系统如何与MySQL交互

1.MySQL驱动

访问数据库必须先和数据库建立一个网络连接，这个连接就是由MySQL驱动来完成。

当系统与数据库建立网络连接后，才能基于连接执行各种SQL语句

2. 数据库连接池

Tomcat中每个线程访问数据库时，都基于MySQL驱动去创建一个数据库连接，然后执行SQL语句，执行完后再销毁这个数据库连接。而频繁创建和销毁数据库连接的代价太大，因此必须使用数据库连接池，在数据库连接池中维护多个数据库连接，让多个线程使用里面不同的数据库连接去执行SQL语句，执行完后将连接放回数据库连接池供后续继续使用。常见的数据库连接池有DBCP、C3P0、Druid等。
多个系统与MySQL数据库建立连接，MySQL也必然需要维护与多个系统之间的连接，所以MySQL架构体系中的第一个环节就是连接池。

二、MySQL如何执行SQL语句

1.网络连接必须让线程来处理

代码如下（示例）：思考：假设数据库服务器的连接池中的某个连接接收到了网络请求，谁负责从这个连接中监听网络请求呢？谁负责从网络连接中将这个请求数据读取出来呢？
答案是线程，网络连接必须得分配一个线程去进行处理，由一个线程来监听请求以及读取请求数据：

2.SQL接口：负责处理接收到的SQL语句

MySQL内部提供了一个组件：SQL接口，它是一套执行SQL语句的接口，专门用于执行我们发送的MySQL的增删改查的SQL语句。因此，MySQL的工作线程接收到SQL语句之后，就会转交给SQL接口执行：

3.查询解析器：让MySQL能看懂SQL语句

查询解析器（Parser）：负责对SQL语句进行解析，例如：select id,name,age from users where id=1 查询解析器对这条SQL语句进行拆解，拆解成一下几个部分：
1.从“users”表里查询数据；
2.查询“id”字段等于1的那行数据；
3.对查询出来的那行数据提取“id”，“name”，“age”三个字段；
所谓SQL解析，就是按照既定的SQL语法，对SQL语句进行解析，然后理解这个SQL语句要干什么事情。

4.查询优化器：选择最优的查询路径

查询优化器（Optimizer）：选择一个最优的查询路径

5.存储引擎接口：真正执行SQL语句

存储引擎：其实就是执行SQL语句的，按照一定的步骤去查询内存缓存的数据，更新磁盘数据，查询磁盘数据，等等，执行诸如此类的一系列操作。

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

执行器：根据优化器选择的执行方案，调用存储引擎的接口按照一定的顺序和步骤，执行SQL语句的逻辑。即执行器会根据优化器生成的一套执行计划，不停地调用存储引擎的各种接口来完成SQL语句的执行计划。

三、利用数据更新流程了解InnoDB存储引擎的结构设计

1.InnoDB重要内存结构：缓冲池

缓冲池：缓存数据，减少查询磁盘次数；
例如：update user set name=‘test’ where id = 1;
引擎要执行更新语句时，先查询缓冲池中是否存在id=1的这行数据，如果不存在，则从磁盘里将这行数据加载到缓冲池中，并且对这行数据加独占锁。（因为更新这行数据时，不允许别人同时更新）

2.undo日志文件

执行name=‘test’之前，需先将name的原值和id=1写入undo日志文件中，以便数据的回滚。

3.更新buffer pool中的缓存数据

当把要更新的那行记录从磁盘文件加载到缓冲池，同时对这行数据加锁，并且把更新前的旧值写入undo日志文件之后，即可正式开始更新这行记录了。更新时，先更新缓冲池中的记录，此时这个数据为脏数据（内存与磁盘数据不一致）。

4.Redo Log Buffer

如果内存里的数据进行了修改，但是磁盘上的数据还没修改，此时MySQL所在的机器宕机，将会导致内存里修改过的数据丢失。这时，就必须将内存所做的修改写入到一个Redo Log Buffer文件中，这也是内存里的一个缓冲区，用来存放redo日志的。

这个redo日志是用来在MySQL突然宕机的时候，用来恢复更新过的数据。

5.提交事务的时候将redo日志写入磁盘

提交一个事务时，就会根据一定的策略把redo日志从redo log buffer刷到磁盘文件。
这个策略是通过innodb_flush_log_at_trx_commit来配置的：

1. 当参数值为0时，提交事务时就不会把redo log buffer里的数据刷入磁盘文件，此时可能提交了事务，由于机器宕机，内存数据将会全部丢失；
2. 当参数值为1时，提交事务时，必须把redo log从内存刷入磁盘文件，只要事务提交成功，那么redo log就必然在磁盘里；
   
3. 当参数值为2时，提交事务时，把redo日志写入磁盘文件对应的os cache缓存中，而不是直接进入磁盘文件，可能1秒后才会把os cache里的数据写入到磁盘文件里去。
   
   redo日志刷盘：通常设置为1，提交事务时，redo日志必须是输入磁盘文件里；这样可以严格保证提交事务后，数据不会丢失，因为redo日志在磁盘文件里可以恢复所有的修改。

6.binlog

redo log 属于偏向物理性质的重做日志，它记录的是：对哪些数据页中的什么记录做了什么修改；redo log属于InNoDB存储引擎。
**binlog（归档日志）**记录偏向于逻辑性的日志，类似：对user表中的id=1的一行数据做了更新操作，更新后的值为什么；binlog属于mysql server。

提交事务时，redo log日志会写入磁盘文件中，同时对应的binlog日志也会写入到磁盘文件中，如下图：

执行器：负责和InnoDB进行交互，包括从磁盘里加载数据到Buffer Pool中进行缓存，写入undo日志，更新Buffer Pool 里的数据，以及写入redo log buffer ，redo log刷入磁盘，写binlog等。

1. binlog日志的刷盘策略
   binlog的刷盘策略通过sync_binlog参数控制，默认为0；

• 当sync_binlog为0时，binlog写入磁盘时，并不是直接进入磁盘文件，而是进入os cache内存缓存；如果机器宕机，os cache中的binlog日志会丢失；
• 当sync_binlog为1时，提交事务时，binlog直接写入到磁盘文件；

binlog写入磁盘文件后，接着会完成最终的事务提交，此时会把本次更新对应的binlog文件名称和文件位置都写入到redo log日志文件中，同时在redo log日志文件里写入一个commit标记。
完成这一步后，才算最终完成了事务的提交，如图所示：

commit标记用来保持redo log日志和binlog日志一致

2. 后台IO线程随机将内存更新后的脏数据刷回磁盘
   事务提交完成后，内存里的buffer pool缓存数据已被更新，同时磁盘里有redo log日志和binlog日志，都记录了我们指定的“id=1 这行数据修改了name=test”。此时，磁盘上的数据文件中的数据还是旧值，MySQL会通过一个后台的IO线程，在某个时间里随机将内存buffer pool中修改后的脏数据刷回到磁盘上的数据文件中。如图所示：
   
   总结：
   InnoDB存储引擎主要包含buffer pool、redo log buffer等内存里的缓存数据，同时还包含了一些undo、redo日志文件，以及mysql server自己的binlog日志文件；
   在执行更新时，每条SQL语句都会对应修改buffer pool里的缓存数据、写undo日志、写redo log buffer等步骤；
   当提交事务时，会将redo log、binlog刷入磁盘，完成redo log中的事务commit标记；
   最后，后台的IO线程会随机将buffer pool里的脏数据输入磁盘文件。