MySQL进阶知识点：SQL语句的执行流程

1.查询的SQL语句是如何执行的

MySQL架构的基本图如下：

一般来说，MySQL可以分为Server层和Storage引擎层两部分。

服务器层包括连接器、查询缓存、解析器、优化器、触发器等，涵盖了大部分基本的MySQL服务功能，以及所有内置函数（如日期、时间、数学和加密函数）等），在这一层实现了所有缓存功能，如存储过程、触发器、视图等。

数据存储层负责存储和检索数据。它的架构模型是插件式的，支持多个存储库，例如 InnoDB、MyISAM、Memory。现在最常用的存储库是 InnoDB，从 MySQL 5.5.5 开始它已成为默认存储库。

1) 连接器

连接器负责与客户端建立连接、获取授权、维护和管理连接。一般情况下，使用数据库管理工具（如：Navicat）或直接在安装mysql的服务器上输入以下命令：

root@bac8f643c3e9:/# mysql -h10.10.0.18 -p3306 -uroot -p

主要负责用户登录数据库和用户身份验证，包括账号密码验证、授权等其他操作。如果用户帐户密码已通过，连接器将查询权限表以获取所有用户权限。该连接后续的授权逻辑判断将取决于当前加载的授权数据。也就是说，如果以后连接没有断开，即使管理员更改了用户权限，用户也不会受到影响。

2）查询缓存（MySQL 8.0之后删除）

查询缓存主要用来存储我们执行的SELECT语句以及该语句的结果集。

建立连接后，执行查询命令时，会先查询缓存。 MySQL首先验证该sql是否已经执行，并以键值的形式缓存起来。 Key 是查询估计，Value 是结果集。如果找到缓存key，则直接返回给客户端。如果没有干预，将执行以下操作。完成后，结果会被缓存起来，方便下次调用。 当然，实际执行缓存查询时，仍然会检查用户的权限，看看表的查询条件是否存在。

不建议使用MySQL查询缓存，因为查询缓存失败在实际业务场景中非常常见。如果更新表，该表中的所有查询缓存都将被清除。对于不经常更新的数据仍然可以使用缓存。

因此，一般来说，在大多数情况下，我们不建议使用查询缓存。

MySQL 8.0版本后删除了缓存功能。官方也认为该功能实际应用场景很少，干脆删除了。

3）解析器

MySQL没有命中缓存，则进入解析器。解析器主要用来分析SQL语句的用途。分析器也会分为几个步骤：

一步，词法分析，SQL语句由多个字符串组成。首先需要提取select、查询表、字段名、查询条件等关键字，完成这些操作后，进入第二步。

第二步，语法分析，主要是判断输入的sql是否正确，是否符合MySQL语法。

完成这2步之后，MySQL就可以运行了，但是如何运行它，如何得到最好的结果呢？这个时候，优化器就必须发挥作用了。

大多数SQL执行错误发生在解析器步骤

4）优化器

优化器的作用是执行它认为是最优的执行计划（有时可能不是最优解）。比如有多个索引时如何选择索引、查询多表时如何选择关联顺序等等。

可以说，经过优化器之后，这条语句的具体实现就已经确定了。

5）执行器

选择执行计划后，MySQL就准备开始执行了。它在运行之前首先检查用户是否有权限。如果该权限不存在，则会显示错误消息。如果存在权限，则调用引擎接口并返回接口执行结果。开始执行时，首先要判断自己是否有权限对该表T进行查询，如果没有，会返回无权限错误。

为什么优化器之前不检查查询表的执行权限？

这是因为有时 SQL 语句需要使用的表并不是 SQL 文字中唯一的表。例如，如果有触发，则必须在执行（处理）阶段确定。在优化阶段之前您无能为力。

2。 SQL更新语句是如何执行的

首先可以肯定地说，更新语句将经历与查询语句相同的过程。与查询过程不同的是，更新语句包括事务，需要保证事务的四个主要特性：ACID，所以更新过程包括两个重要的日志模板：重做日志（redo log）和binlog（归档日志）

1）重做日志

首先，重做日志是innodb引擎特有的，这也是innodb引擎成为mysql最常用引擎的主要原因。

不知道大家还记不记得《孔乙己》这篇文章里，酒店经理有一块粉色的板子，专门用来记录客人的信用记录。如果还款的人不多，那么他可以把客户的名字和账户写在黑板上。但如果拥有信用账户的人太多，粉丝板总会有无法追踪到他们的情况。此时，商家必须有一个专门用于记录信用账户的账本。

如果有人想要贷记或者还债，商家通常有两种方式：

一种方式是直接写出账本，进行增减贷项；

第二种方法 方法是先把账写在粉红板上，然后在关门时间后取出账本进行计算。

当生意兴隆，柜台非常繁忙时，商家肯定会选择后者，因为前者操作要求太高。首先，您需要找到该人的总信用评分记录。想想看，密密麻麻的页面有几十个。为了找到这个名字，商人可能得戴上老花镜慢慢寻找。找到后，他取出计数器进行计算，最后将结果写回到账本中。整个过程想想都是有问题的。相比之下，先写在粉红板上比较容易。想一想，如果一个商人没有粉板，他每次记账都得翻账本。效率是不是低得令人难以忍受？

同样，MySQL也存在这个问题。如果每次更新操作都需要写入磁盘，磁盘也要找到对应的记录然后更新，那么整个过程的输入和查找成本会非常高。为了解决这个问题，MySQL设计者采用了类似于酒店推销员粉红板的思路来提高更新的效率。粉红板和账本配合的整个过程其实就是MySQL中经常提到的WAL技术。 WAL 的全称是预写式日志记录。关键点是先写日志再写Disk，即先写粉板，不忙的时候再写账本。具体来说，当需要更新一条记录时，InnoDB引擎首先将该记录写入重做日志（粉色板）并更新内存。至此，更新完成。同时，InnoDB引擎会在适当的时候将操作的记录更新到磁盘，而这种更新往往是在系统相对空闲的时候进行的，类似于交易者在平仓后所做的事情。如果今天的信用账户不多，商家可能会等到关门时间才对商品进行排序。但如果某一天信用账户很多，粉红板满了怎么办？这时，商家不得不暂停他的工作，将粉红板上的一些信用条目更新到分类账中，然后从粉红板上删除这些条目，为新账户腾出空间。

同样，InnoDB的重做日志也有固定的大小。例如，可以配置为一组 4 个文件，每个文件大小为 1 GB。那么这个“粉红板”总共可以记录4GB的操作。从头开始写，然后回到开头循环写，如下图所示。

写入位置是当前记录的位置。打字时向后移动。写入到文件#3的末尾后，返回到文件#0的开头，检查点就是当前要擦除的位置，同样向前移动，循环。在删除记录之前，必须将该记录更新到数据文件。写入位置和检查点之间的空间是“粉红板”的空白部分，可用于记录新操作。如果写入位置赶上检查点，则说明“粉板”已满。目前无法进行新的更新。首先，你需要停下来并清除一些记录才能前进到检查点。

有了重做日志，InnoDB可以保证即使数据库异常重启，之前发送的记录也不会丢失。这种能力被称为碰撞安全。

2）Binlog

上面我们讲的pinkboard redo log是InnoDB引擎特有的日志，Server层也有自己的日志，称为binlog（归档日志）。

我猜你会问为什么有两种协议？

因为MySQL一开始并没有InnoDB引擎。 MySQL的原生引擎是MyISAM，但MyISAM不具备故障安全功能，并且binlog只能用于归档。 InnoDB是由另一家公司以插件的形式引入MySQL的。由于仅依赖binlog不具备故障安全能力，因此InnoDB使用不同的日志系统，即重做日志来实现故障安全能力。

两种协议之间存在三个差异。

• redo协议是InnoDB引擎独有的； binlog由MySQL服务器层实现，可供所有引擎使用。
• 重做日志是一种物理日志，记录“对特定数据页进行了哪些编辑”； binlog是逻辑日志，记录了这条命令的原始逻辑，比如“put array c row with ID=2” plus 1 ”。
• 重做日志是循环写入的，空间会一直用完； binlog可以另外写。 “attach write”是指binlog文件达到一定大小后，会切换到下一个，不会覆盖之前的日志。

接下来我们看一下执行SQL更新的整体流程：

mysql> update T set c=c+1 where ID=2;

1. 执行器首先搜索引擎得到ID=2的行。 ID 是主键，引擎直接使用树搜索来查找该行。如果row ID=2所在的数据页已经在内存中，则直接返回给执行器；否则需要从磁盘加载到内存中然后返回。
2. 执行器获取引擎输入的行数据，将该值加1，例如原来是N，现在是N+1，获取新的一行数据，然后调用引擎接口写入这一新行数据的。
3. 引擎将这行新数据更新到内存中，并将更新操作记录在重做日志中。此时，重做日志处于就绪状态。然后通知执行者执行完成，可以随时提交交易。
4. 执行器生成本次操作的binlog，并将binlog写入磁盘。
5. 执行器调用引擎的事务接口，引擎将刚刚写入的重做日志更改为提交状态，更新完成。

执行此更新命令的流程图如下：

从上面可以看出，重做日志写入分为两个步骤：准备和确认。这就是“两阶段承诺”。

为什么协议需要“两阶段确认”。这里我们还可以用反证法来解释。由于redo log和binlog是两个独立的逻辑，如果不使用biphasic发送，必须先写redo log再写binlog，或者必须接收相反的顺序。我们来看看这两种方法存在什么问题。

1. 先写redo log，再写binlog。假设MySQL进程异常重启是在redo log写入时，binlog写入之前。之前我们说过，重做日志写入后，即使系统崩溃，数据仍然可以恢复，所以恢复后这一行c的值为1。但是，由于binlog还没完成就崩溃了，所以这条语句此时没有记录在binlog中。因此，后续备份日志时，保存的binlog中不会包含该命令。然后你会发现如果需要使用这个binlog来恢复临时库，因为这个命令的binlog丢失了，所以这次临时库不会更新。恢复后的行中c的值为0，与原库的值相同。不同的。
2. 先写binlog，然后再写log。如果写binlog后出现错误，因为redo log还没有写完，崩溃恢复后事务就会失效，所以这行c的值为0。但是日志“Changing c from 0 to 1”是记录在binlog中。所以，后面用binlog恢复的时候，就会多出来一笔事务。恢复后的行中c的值为1，与原始数据库中的值不同。

两阶段确认是分布式事务一致性的解决方案。

重做日志用于确保安全功能。当innodb_flush_log_at_trx_commit参数设置为1时，表示将每个事务的重做日志直接保存到磁盘。这样可以保证MySQL异常重启后数据不丢失。

sync_binlog 如果该参数设置为1，则表示每个事务的binlog都存储在磁盘上。这样可以保证MySQL异常重启后binlog不会丢失。

3。事务并发和隔离级别问题

简单地说，事务旨在确保一组数据库操作要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎级别实现的。正如您现在所知，MySQL是一个多引擎系统，但并非所有引擎都支持事务。例如MySQL原生的MyISAM核心不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。

1）事务的四大特性（ACID）

原子性： 事务是最小的执行单元，不可分割。事务的原子性确保一个动作要么完全完成，要么根本没有效果；

一致性：交易前后数据保持一致。例如，在一笔转账中，无论交易是否成功，转让方和接收方的详细信息是： 总金额应保持不变；

隔离性： 并发访问数据库时，用户的事务不会被其他事务打断，数据库在并发事务之间是独立的；

持续时间： 确认交易后。他对数据库中数据的更改是永久性的，即使数据库发生故障，也不应该对其产生任何影响

2）事务并发问题

• 脏读：当事务访问的数据已修改且修改尚未发送至数据库。此时，另一个事务也访问并使用了该数据。因为这些数据还没有提交，所以其他事务读取到的数据就是“脏数据”，基于“脏数据”的操作可能会不正确。
• 丢失修改： 指的是当一个事务读取了一条数据时，另一个事务也访问了该数据，并且第一个事务修改了数据后，第二个事务也修改了该数据。这样，第一个事务中的修改结果就丢失了，所以称为丢失修改。例如：事务1读取表中数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果为A=19，事务修改1迷路了。
• 不可重复读取：表示在一次事务内多次读取相同数据。在该事务终止之前，另一个事务也在访问数据。那么，在第一事务的两次读取数据之间，第一事务两次读取的数据可能会因为第二事务的修改而不同。当一个事务内两次读取的数据不同时就会出现这种情况，因此称为不可重复读取。
• 幻读： 幻读类似于不可重复读取。当一个事务 (T1) 读取几行数据，然后另一个并发事务 (T2) 插入一些数据时，就会发生这种情况。在后续的查询中，第一个事务（T1）发现了一些原本不存在的额外记录，就像出现幻觉一样，因此称为幻读。

不可重复读与幻读的区别：

不可重复读侧重于修改，目标数据为多行。幻读侧重于添加或删除，并针对多行数据。

3）事务隔离级别

SQL标准定义了四种隔离级别：

• READ-UNCOMMITTED（读未提交）： 最低级别的隔离‼可能读到的数据可能会改变数据y读、幻读或不可重复阅读。
• READ-COMMITTED（读已提交）：允许读取并发事务已提交的数据，可以防止脏读，但幻读或不可重复读仍然可能发生。
• REPEATABLE-READ：同一字段的多次读取结果是一致的，除非数据被事务本身修改，可以防止脏读和不可重复读，但仍然会出现幻读。
• SERIALIZABLE（可串行化）： 最高隔离级别，完全符合ACID隔离级别。所有交易都是按顺序依次执行的，因此交易之间不存在干扰的可能性。也就是说，这个级别可以防止脏读、不可重复读和幻读。

MySQL InnoDB 存储默认支持的隔离级别是 REPEATABLE-READ Oracle 和 sql server 默认支持的隔离级别是 **READ-COMMITTED。我们可以使用命令 SELECT @@tx_isolation; 来检查这一点。在 MySQL 8.0 中，该命令更改为 SELECT @@transaction_isolation;

MySQL InnoDB REPEATABLE-READ（可重复读取）不保证避免幻象。读取要求应用程序使用读锁来确保安全。用于此锁定的机制是下一键锁。

因为隔离级别越低，一个事务需要的锁就越少，所以大多数数据库系统的隔离级别是READ-CONFIRMED（读取提交的内容），但是你需要知道的是InnoDB引擎存储默认使用 REPEATABLE-READ（可重复读取） 不会造成性能损失

1. 如果隔离级别为“读未提交”，则 V1 的值为 2。此时，即使事务 B 还没有尚未发送，A已经看到结果了，所以V2和V3都是2。
2. 如果隔离级别是“已提交读”，则V1为1，V2为2。事务B的更新只能提交后可以被事务 A 看到。因此，V3 的值也为 2。
3. 如果隔离级别为“可重复读”，则 V1 和 V2 为 1，V3 为 2。之所以 V2 始终为 1，是为了符合这个要求：数据事务在执行过程中看到的前后必须一致。
4. 如果隔离级别是“序列化”，则事务B在进行“1改2”时会被锁定。在交易 A 确认之前，交易 B 无法继续进行。所以从视图A来看，V1和V2的值为1，V3的值为2

4）事务隔离的实现

从实现上来看，会在数据库中创建一个视图，访问时视图的逻辑结果将优先。在“可重复读”隔离级别下，该视图在事务启动时创建，并在整个事务中使用。在“读提交”隔离级别下，该视图在每个 SQL 语句的开头创建。这里注意，在“未提交读”隔离级别下，直接返回记录中的最后一个值，没有视图的概念；而在“串行化”隔离级别以下，则直接使用锁定来防止并行访问。

每条记录更新时，都会同时记录一次回滚操作。系统中同一条记录可以存在多个版本。这是一个多版本并发控制（MVCC）数据库

回滚日志什么时候会被删除？

系统会判断当没有事务需要使用这些回滚日志时，就会删除这些回滚日志。

什么时候不再需要？

当系统中存在比该回滚日志更早的读取视图时。

为什么尽量不要使用长事务？

长事务意味着系统中将会存在非常旧的事务视图。在这笔交易被确认之前，必须维护退货记录，这会导致大量的存储空间被占用。此外，长事务还会消耗锁资源并可能破坏库。