MySQL Server体系结构

一条SQL的执行过程

系统是如何与MySQL交互的

1. MySQL驱动、数据库连接池

我们的系统在和 MySQL 数据库进行通信的时候，不是平白无故的就能接收和发送请求。就是这个MySQL驱动在底层帮我们做了对数据库的连接，只有建立了连接了，才能够有后面的交互。看下图：

在系统和 MySQL 进行交互之前，MySQL 驱动会帮我们建立好连接，然后我们只需要发送 SQL 语句就可以执行 CRUD 了。一次 SQL 请求就会建立一个连接，多个请求就会建立多个连接，那么问题来了，我们系统肯定不是一个人在使用的，换句话说肯定是存在多个请求同时去争抢连接的情况。
我们的 web 系统一般都是部署在 tomcat 容器中的，而 tomcat 是可以并发处理多个请求的，这就会导致多个请求会去建立多个连接，然后使用完再都去关闭，这样会有什么问题呢？如下图：

java 系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，所以如果每个请求都是新建连接和销毁连接，那这样势必会造成不必要的浪费和性能的下降，也就说上面的多线程请求的时候频繁的创建和销毁连接显然是不合理的，必然会大大降低我们系统的性能。
但是如果能提供一些固定的用来连接的线程，这样是不是不需要反复的创建和销毁连接了呢？这就是数据库连接池。

数据库连接池，维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。

2. MySQL连接池

业务系统是并发的，而 MySQL 接受请求的线程呢，只有一个吗？
其实 MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连接池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。

MySQL在接收到系统发送的sql语句时做了哪些事情

3. MySQL线程调用SQL接口

网络中的连接都是由线程来处理的，所谓网络连接说白了就是一次请求，每次请求都会有相应的线程去处理的。
也就是说对于 SQL 语句的请求在 MySQL 中是由一个个的线程去处理的。

那这些线程会怎么去处理这些请求？会做哪些事情？
MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。

4. 解析器

假设现在有这样一个SQL：

1
SELECT stuName,age,sex FROM students WHERE id=1;

但是这个 SQL 是写给我们人看的，机器哪里知道你在说什么？这个时候解析器就上场了。他会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言。具体可以看MySQL - MySQL中SQL是如何解析的。

现在 SQL 已经被解析成 MySQL 认识的样子的，那下一步是不是就是执行吗？理论上是这样子的，但是 MySQL 的强大远不止于此，它还会帮我们选择最优的查询路径。
什么叫最优查询路径？就是 MySQL 会按照自己认为的效率最高的方式去执行查询。
具体是怎么做到的呢？这就要说到 MySQL 的查询优化器了。

5. 优化器

查询优化器内部具体怎么实现的我们不需要关心，我们需要知道的是 MySQL 会帮我们去使用它自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本。

  • IO成本
    即从磁盘把数据加载到内存的成本。
    默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关。
  • CPU成本
    将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

MySQL 优化器 会计算「IO 成本 + CPU成本」最小的那个索引来执行。

优化器执行选出最优索引等步骤后，会去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句。

6. 执行器、存储引擎

执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。
执行器最终根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行。

总结

MySQL中一条SQL是如何执行的

大致过程如下：
SQL ==> 查询缓存 ==> 解析器 ==> 优化器 ==> 执行器

  • 客户端SQL发送到MySQL服务端，首先会去查询缓存里面查找，如果多次执行同一个SQL是会命中缓存中，此时直接通过缓存来取数据
  • 若没有命中查询缓存，则将SQL发送给解析器，解析器会对SQL进行语法、语义分析，同时也会对SQL合法性进行校验
  • SQL语义解析出来后，优化器会对SQL语义进行优化，比如，是走索引还是全表扫描，优化器会根据实际情况来确定一个最优的执行方案（当然，不一定是最优的，只是给出MySQL认为是最优的执行方案）
  • 优化完毕后，就给到执行器（调用具体某个存储引擎，例如innodb）进行执行SQL，取出SQL执行结果
  • 最后将执行结果返回给客户端

准备更新一条数据到事务的提交的流程描述

从准备更新一条数据到事务的提交的流程描述：

  • 首先执行器根据 MySQL 的执行计划来查询数据，MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（BufferPool）中
  • 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
  • 更新的动作是在 BufferPool 中完成的，同时会将更新后的数据记录到 redo log buffer 中
  • 完成以后就可以提交事务，在提交的同时会做以下三件事
    • 将redo log buffer中的数据刷入到 redo log 文件中
    • 将本次操作记录写入到 bin log文件中
    • 将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
  • 至此表示整个更新事务已经完成

如果在数据被写入到bin log文件的时候，刚写完，数据库宕机了，数据会丢失吗？
首先可以确定的是，只要redo log最后没有 commit 标记，说明本次的事务一定是失败的。但是数据是没有丢失了，因为已经被记录到redo log的磁盘文件中了。在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到Buffer Pool中。

这个时候被更新记录仅仅是在内存中执行的，哪怕是宕机又恢复了也仅仅是将更新后的记录加载到Buffer Pool中，这个时候 MySQL 数据库中的这条记录依旧是旧值，也就是说内存中的数据在我们看来依旧是脏数据，那这个时候怎么办呢？
其实 MySQL InnoDB存储引擎会有一些后台线程，它会在某个时机将我们Buffer Pool中的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。

MySQL中SQL是如何解析的

在一条SQL是如何在数据库中执行的过程中，其中有一个阶段是SQL的解析。这个阶段对于更全面的SQL优化功能、多维度的慢查询分析、辅助故障分析等有很大帮助。

背景

SQL解析的原理

SQL解析与优化是属于编译器范畴，和C等其他语言的解析没有本质的区别。其中分为，词法分析、语法和语义分析、优化、执行代码生成。
对应到MySQL的部分，如下图：

词法分析

语法分析

MySQL日志

MySQL Server层日志

1. 二进制日志（binlog）

记录所有更改数据（insert、update、delete等）的语句，还用于复制。

作用
  • 主从复制
  • 数据恢复
STATMENT、ROW、MIXED
  • STATMENT
    基于 SQL 语句的复制(statement-based replication, SBR)，每一条修改数据的 SQL 语句都会记录到 bin log 中
    • 优点 不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能
    • 缺点 在某些情况下会导致主从数据不一致，比如执行sysdate()、sleep()等
  • ROW
    基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了
    • 优点 不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题
    • 缺点 会产生大量的日志，尤其是 alter table 的时候会让日志暴涨
  • MIXED
    基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log。
    当 MySQL 判断可能数据不一致时，就用 row 格式，否则就用 statement 格式。

2. 错误日志（errorlog）

记录启动、运行或停止mysqld时出现的问题。

3. 慢查询日志（slow query log）

记录所有执行时间超过long_query_time秒的所有查询或者不适用索引的查询。

4. 一般查询日志（general log）

记录建立的客户端连接和执行语句。

InnoDB存储引擎事务相关日志

1. 重做日志（redo log）

redo log 记录的是数据被事务操作后的样子。
redo log是InnoDB存储引擎层实现的（也就是说是 Innodb 存储引擎独有的），用于保障事务的持久性，主要用于掉电等故障恢复。

redo log记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。
redo log是用来恢复数据的，用于保障已提交事务的持久化特性。

2. 回滚日志（undo log）

Undo log 记录的是数据被事务操作前的样子。
undo log是InnoDB存储引擎层实现的（也就是说是 Innodb 存储引擎独有的），用于保障事务的原子性，主要用于事务回滚和MVCC。

因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到被修改前的状态。
undo log是用来回滚数据的，用于保障未提交事务的原子性。

由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo log。
undo log主要分为3种：

  • Insert undo log ：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
  • Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。
  • Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
    • 删除操作都只是设置一下老记录的DELETED_BIT，并不真正将过时的记录删除。
    • 为了节省磁盘空间，InnoDB有专门的purge线程来清理DELETED_BIT为true的记录。
      为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）。
      如果某个记录的DELETED_BIT为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

主从同步从数据库

中继日志（relay log）

一般情况下它在MySQL主从同步读写分离集群的从节点才开启，主节点不需要这个日志。