携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第1天，点击查看活动详情

本次学习总结基于大版本5.7的MySQL，学习重点为InnoDB引擎。

一、MySQL执行SQL语句的流程

1.1 连接层：管理控制连接

不管通过什么方式和MySQL进行交互，首先都需要建立客户端与服务端连接，连接层就是负责控制连接和验证用户身份的

通信类型：同步/异步

连接方式：长连接/短连接

协议：TCP/Unix Socket(在linux本机客户端连本机服务端用的是这样一个文件)

因为Mysql是单进程多线程的模式工作的，客户端每产生一个连接服务端就会产生一个线程来处理这个连接;

怎么察看客户端维持了多少个跟客户端的连接？

show global status like 'Thread%';

5.7版本的MySQL服务默认允许的最大连接数（并发数）为151个，最大可设置为100000

show variables like 'max_connections';

1.2 缓存（没啥用的玩意）

MySQL自己的缓存默认不开启，局限性太大，sql变了一点就识别不出来，一般项目中结合Mybatis处理缓存。

1.3 解析器

包含解析器和预处理器

解析器：

• 高情商： 解析器负责对输入的语句基于SQL语法进行词法的分析和语义的解析，解析通过后生成解析树（select_lex）。

• 低情商： MySQL先给你检查下你的SQL语法写的对不对啊，“select”是不是写成了"sleect"，几个关键字的顺序用的对不对啊之类的。

预处理器：

• 高情商： 接到解析器生成的解析树后，继续解析解析器无法解析的语义，预处理后得到一个新的解析树。

• 低情商： 接着检查你写的表名、列名是否有歧义，写没写错，是否有歧义？

1.4 优化器

根据解析树生成不同的执行计划，然后选择最优的（开销小的）计划

一条SQL可能会有多种执行方式，优化器的目的就是根据解析树生成不同的执行计划，然后选择最优的（开销小的）计划

可以通过在SQL前加EXPLAN来查看执行计划

如果想看详细的情况，在EXPLAN后边加上FORMAT=JSON，或者开启optimizer trace。

1.5 存储引擎

创建表的时候通过ENGINE关键字指定，后期可修改，5.5.5之前默认是MyISAM，5.5.5之后默认为InnoDB。

1.6 查询SQL与更新SQL

查询没啥好说的，按照流程一步步走下去，将结果返回就完了；

更新SQL找到要操作的数据和查询基本一致，区别在于拿到数据之后的处理操作；

简化版更新过程：

1、事务开始，从内存(buffer pool)或磁盘(data file)取到包含这条数据的数据页,返回给Server的执行器;
2、Server 的执行器修改数据页的这一-行数据的值为 penyuyan;
3、记录name=qingshan到undo log;
4、记录name=penyuyan到redo log; .
5、调用存储引擎接口，记录数据页到Buffer Pool (修改name=penyuyan) 
6、事务提交。

名词解释：

1、缓冲池：Buffer Pool（为了提升读写的效率）

对于InnoDB存储引擎来讲，数据都是放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里才能操作，但是由于和磁盘进行IO操作要经过寻址能一系列操作，导致效率很慢，所以就不能需要多少数据就拿多少数据——预读取机制（一次读一页，操作系统的一页大小一般是4k，InnoDB里，默认最小16KB）

修改数据的时候也是先写入到Buffer Pool里，当内存里的数据页和磁盘数据不一致的时候，叫脏页，InnoDB中有专门的后台线程来完成数据同步，这个动作叫刷脏。

2、Redo Log（重做日志）（为了解决在BufferPool中的数据还没刷入磁盘中，服务器/数据库就断电或宕机导致数据丢失——ACID-D-事务的持久性）

为啥加它对性能影响比较小？数据直接写入磁盘文件需要有一个寻址的过程，所以慢，而RedoLog记录日志不需要一次次寻址，直接写就行（连续写入）（顺序IO和随机IO区别）

3、Undo Log（撤销日志/回滚日志）（记录了事务发生之前的数据状态，分为Insert undolog和update underlog）如果修改数据时出现了异常，可以用undolog来实现回滚操作-事务的原子性）

记录的是反向操作，insert会记录delete，update会记录update原来的值，

redo Log 和undo Log与事务密切相关，统称为事务日志。

二、InnoDB架构分析

2.1 内存结构

2.1.1 BufferPool

缓冲池介绍在上边名词解释中有，不再赘述。

缓冲池缓存的是页面信息，包括数据页、索引页，默认大小128M，强烈建议调大。能尽可能的提升数据库运行效率。

缓冲池知识点附加：LRU算法

缓冲池中的数据页并不都是在访问过之后才缓存到缓冲池中的，InnoDB有一个预读机制（设计者认为你访问某个page之后可能会接着访问相邻的page，所以就把相邻的page也放进来了）

缓冲池那么好用，但是总归有尽头，这时候就需要有一个淘汰机制——LRU算法； InnoDB在传统LRU的结构基础上（Map+链表），将链表部分分为new区和old区（冷热分离）。

默认情况下热区占5/8，冷区占3/8；

规则：

1.所有新数据加入到buffer pool的时候，一律先放到old区的head，不管是预读的还是普通读的；

2.old区的数据被访问到，会将其移动到new区的head，new区原有数据如果长时间没有被访问，会被先移动到old区中的head，直到慢慢从tail淘汰；

3.为了防止很多只用一次的数据大批量进入new区，导致原有的热点数据被挤跑，有一个默认1秒的时间窗口，只有进入到old区1秒中后又被访问的才从old移动到new区的head；

4.为了防止new区过于频繁的移动增加不必要的开销，如果一个页处于new区的前1/4区域，那么当访问这个页的时候，就不用将其移动到new区的head，只有当页位于new区的后3/4区域中时，被访问的时候才会将其移动到new区的head；

2.1.2 Log Buffer

Log Buffer是redo log的写缓冲，默认16m。

数据库中的数据是从Buffer Pool中写入磁盘文件，Redo Log通过Log Buffer写入特定磁盘文件。

Log Buffer写入时机通过"innodb_flush_log_at_trx_commit"来控制，默认参数1

2.2 磁盘结构

2.2.1 系统表空间

包含InnoDB数据字典、双写缓冲区、ChangeBuffer和UndoLogs，如果没有指定file-per-table，系统表空间中也包含用户创建的表和索引数据

双写缓冲区

InnoDB的页和操作系统的页大小不一致, InnoDB页大小一般为16K,操作系统页大小为4K, InnoDB的页写入到磁盘时，一个页需要分4次写。如果存储引擎写到一页中的一部分时系统宕机了，可能会导致数据丢失。

正常情况下的崩溃恢复可以使用redo log，doublewrite就是保证了只写入部分页情况下的崩溃恢复。

InnoDB通过redo log和 doublewrite共同实现事务的持久性。

2.2.2 Binlog

以事件的形式记录了所有的DDL和DML语句(因为它记录的是操作而不是 数据值，属于逻辑日志)，可以用来做主从复制和数据恢复。

在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句,把所有的操作重放一遍，来实现数据的恢复。

开启binlog后，更新操作变为二段式提交

例如一条语句: 
update teacher set name= '盆鱼宴' where id=1;
1、先查询到这条数据，如果有缓存，也会用到缓存。
2、把name改成盆鱼宴,然后调用引擎的API接口，写入这一行数据到内存,同时记录redo log。这时redo log进入prepare状态，然后告诉执行器，执行完成了，可以随时提交。
3、执行器收到通知后记录binlog,然后调用存储引擎接口，设置redo log为commit状态。
4、更新完成。

在崩溃恢复时，判断事务是否需要提交:

1、binlog无记录，redolog 无记录:在redolog写之前crash,恢复操作:回滚事务

2、binlog 无记录，redolog 状态prepare:在binlog写完之前的crash,恢复操作:回滚事务

3、binlog 有记录，redolog 状态prepare:在binlog写完提交事务之前的crash,恢复操作:提交事务

4、binlog 有记录, redolog 状态commit:正常完成的事务，不需要恢复