MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。

Server层

涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

下面以这条查询语句为例进行各个核心组件的分析。

mysql> select * from T where ID=10;

连接器

在完成经典的 TCP 握手后就进入了连接阶段

作用：负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令：mysql -h$ip -P$port -u$user -p

流程

身份认证
- 1.在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。
- 2.如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。
读取权限表
- 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
  - 这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
建立连接

通过前两步后就会成功建立连接。如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。如下图：

其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。

两种连接形式：
- 长连接（默认方式）

指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

     - 长连接存在的问题：

但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

     -  两种解决方案：
        - 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
        - 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
  - **短连接**

指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

查询缓存

连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。

作用：以 key-value 对的形式，将查询语句与结果缓存到内存中

MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：
mysql> select SQL_CACHE * from T where ID=10；

致命缺陷

查询缓存往往弊大于利，且mysql8.0开始已经彻底将该模块删除。原因如下：
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。作用：解析sql语句

流程：

词法分析：识别sql中字符串各个部分；
语法分析：根据词法分析的结果，以及语法规则判断slq是否满足语法；
判断表和字段是否存在。

优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

作用：决定使用哪个索引以及join时的顺序。

影响索引选择的因素

扫描行数、是否回表、是否使用临时表、是否排序；

是否回表

二级索引需要回表，优化器会评估这个成本；

扫描行数

指Mysql在获取语句执行结果时，需要扫描的数据行数。

  - 区分度

MySQL 在真正开始执行语句之前，并不能精确地知道需要扫描的数据行数，而只能根据统计信息来估算扫描行数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而，一个索引上不同的值的个数，称之为“基数”（cardinality）。基数越大，索引的区分度越好。

  - 采样统计

InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

     - 在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：
        - 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10；
        - 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

执行器

化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段；
MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

作用：执行sql

流程

判断对该表是否有执行权限；

如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。
注意：此处是对“表”的权限校验，而连接器的权限验证是对用户身份的权限验证。

调用对应的执行引擎接口

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

举例说明

mysql> select * from T where ID=10;

  - ID 字段没有索引的执行流程：
     1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
     1. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
     1. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此，这个语句就执行完成了。
  - ID 字段有索引的执行流程：<br />对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。<br />    你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

引擎层

负责数据的存储和提取，不同的存储引擎共用一个 Server 层。
其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

redo log & binlog

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，redo log（重做日志）和 binlog（归档日志）。

为什么会有两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，内置的binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

更新流程

所用的更新操作之前，都必须先查询再更新。
以如下sql为例，将表T中id =2的c字段增加1：mysql> update T set c=c+1 where id=2;

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。
接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

Mysql - 图3

两阶段提交

将 redo log 的写入拆成了两个步骤：prepare 和 commit，在异常情况下也能保证binlog和redolog的数据一致性。这就是”两阶段提交”。

为什么要保证 binlog和redo log的数据一致性？

因为redo log始终与数据库数据一致，如果binlog数据出现了与redo log不一致，那么当使用该binlog恢复数据的时候，会导致恢复后的数据与原库的数据不一致的情况。

两阶段提交是如何保证crash时的数据一致性的呢？

数据库 crash-重启后，会对redo log 进行check，此时分为两种情况：

1. 如果 redo log 已经commit，则视为有效;
1. 如果 redo log prepare 但未commit，则check对应的bin log记录是否记录成功。
  - 2.1. bin log记录成功则将该prepare状态的redo log视为有效，将redo log设置为commit状态;
  - 2.2. bin log记录成功则将该prepare状态的redo log视为无效，删除该 redo log。

总结：以binlog为基准，有binlog就算作已经提交，没有binlog就算作没有提交。

对两阶段提交的一些疑问！
- 我为什么认为两阶段提交并非是保证binlog和redolog的一致性

从上面分析两阶段提交是如何保证crash时数据一致性可知，以binlog为基准，有binlog就算作已经提交，没有binlog就算作没有提交。那么，两阶段提交和先写redolog再写binlog有什么区别呢，反正都是以binlog为准？而且实际上，源码里redolog的第二段提交，也不是写日志，只是给存储引擎返回了一个状态字段，告诉存储引擎，binlog日志已经写完了。为什么要二段提交呢，我觉得因为存储引擎和server层是两个线程，如果binlog写入时出错，需要告诉存储引擎回滚redolog。

两阶段提交既然能保证redo log和binlog的数据一致性，那么binlog为什么就不能保证crash-safe呢？
第二阶段，修改状态为commit，不需要重新去磁盘中查询该 redo log 后再改么？此时不是顺序写，性能没优势了呀。

redo log

redo log 是 InnoDB 引擎特有的日志，用于实现 crash-safe 能力，即 ACID中的持久性。具体来说，更新一条记录，InnoDB 会先把记录写到 redo log 里面，并更新内存，更新就算完成了。之后，InnoDB 引擎会在满足配置的刷盘策略｜redo log写满时，将这条记录更新到磁盘里面。

结构：一个循环数组。从头开始写，写到末尾就又回到开头循环写，如下图所示：

write pos：是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头；
checkpoint：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间则是还空着的部分，可以用来记录新的log。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

写入机制

redo log buffer

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 。

redo log buffer 里面的内容，不需要每次生成后都落盘，因为如果事务执行期间 Mysql 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。
事务还没提交的时候，redo log buffer 中的部分日志是有可能被持久化到磁盘的，刷盘策略参考下方。
- redo log的三种状态

红色：redo log buffer ，物理上是在 MySQL 进程内存中；
黄色：FS page cache，写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面；
绿色：hard disk，持久化到磁盘。

注意：日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

redo log一般设置多大？

redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。
所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

刷盘策略

InnoDB有个关键参数innodb_flush_log_at_trx_commit控制 redo log的刷盘策略，该参数有三个取值：

0（默认）：每秒刷一次磁盘，把Redo Log Buffer中的数据刷到redo log；
1（推荐）：每提交一个事务，就刷一次磁盘（这个最安全）；
2：固定刷盘。根据参数innodb_flush_log_at_timeout设置的值决定刷盘频率。

0：每隔1s自动刷盘

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。

1：事务提交时刷盘
- 可通过innodb_flush_log_at_trx_commit 参数配置，它有三种可能取值：
  - 0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  - 1：表示每次事务提交时都将 redo log 直接持久化到磁盘（默认）；
  - 2：表示每次事务提交时都只是把 redo log 写到 page cache。
- 根据两阶段提交协议，redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。
- 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘

假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候write

注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。

组提交机制

LSN

LSN全称为日志逻辑序列号（log sequence number），它是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。

组提交机制

多个并发事务在prepare阶段，都写完redo log buffer后，按组的形式来执行落盘操作，以此来减少IOPS，提高访问速度的一种机制。
显然，每次组提交的事务越多，节约IOPS的效果越好，为此，Mysql做了如下优化（拖时间）：

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。
- binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
- binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

binlog

binlog（归档日志），属于Server 层，记录的是这个语句的原始逻辑，用于将数据库恢复到一个之前的某个时间点。

binlog的两种模式：

statement格式：记录的就是该条执行更新的sql语句；
row格式：记录行的内容，记两条，更新前和更新后都有。

例如，某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：
- 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
- 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

写入机制

binlog 的写入机制比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。如下图所示：

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件；
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快；
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS；
write 和 fsync 的时机，是由参数 sync_binlog 控制的：
1. sync_binlog=0：表示每次提交事务都只 write，不 fsync；
2. sync_binlog=1：表示每次提交事务都会执行 fsync；
3. sync_binlog=N(N>1) ：表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
注意：
一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

redo log VS binlog

	作用	所属	容量	内容
redo log	崩溃恢复	引擎层	有限	物理日志（数据的物理变化）
binlog	时间线恢复	Server层	无限	逻辑日志（执行的该条sql）

redo log是在数据库突然崩溃的时候用来恢复的，而binlog是用于恢复到一个之前的某个时间点；
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用；
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志；
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
- SQL是面向用户的语义化命令，你可以理解为高级编程语言。高级编程语言最终会被执行去完成磁盘上数据的操作。我理解redo log记录的是磁盘上数据的物理变化，binlog记录的是当时所执行的高级编程语言（可以理解就是这条sql）。（这是个人主观理解，非官方）

为什么redo log 不能取代 binlog？
- 1. redolog只有InnoDB有，别的引擎没有；
- 1. 虽然用redolog恢复数据更快，但redolog是循环写，容量有限，并不太适合做“归档”功能。

个人想法：
我的理解是，binlog和redo log 都具备恢复能力，只是redo log是物理日志，恢复速度相较于 binglog的逻辑日志恢复起来更快，但代价就是能恢复的记录有限，所以redo log用于 crash时的数据丢失恢复，binlog用于恢复到一个之前的某个时间点的恢复。
其实redo 和binlog的存储结构也能印证我的想法，redo log是循环写的，不能存太多log，而binlog是“追加写的”只要内存大，理论上是无限制的，可以记录很长时间的log，也就适合恢复到之前任意时间点的数据库。
同时，我觉得说binlog没有crash-safe能力是不完全正确的，因为binlog显然是可以用于崩溃后的数据恢复（因为更新操作时的两次提交保证了binlog和redo log的数据写入是“同一个事务”），那为啥却说binlog没有crash-safe能力呢？我想可能是历史原因，binlog从一开始就不是用于crash时的数据恢复，所以binlog虽然有crash-safe的能力，但是没有这个功能。
- 随着学习深入，发现上面的个人想法有问题：
  - 其实binlog从实现上来说，也是不支持崩溃恢复的。因为它并未记录数据页的细节，无法从binlog中判断哪些脏数据未能刷盘。虽然说改改底层逻辑，即使让binlog可以做崩溃恢复，显然也没有redo log的数据结构和存储形式优雅高效，所以 redo log + binlog 各司其职，也是目前的最优解。