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

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

MySQL的基本架构图如下：

一般来说，MySQL可以分为服务器层和存储引擎层。

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

存储引擎层负责存储和检索数据。其架构模型是插件式的，支持InnoDB、MyISAM、Memory等多种存储引擎。现在使用最多的存储引擎是InnoDB，从MySQL 5.5.5版本开始它已经成为默认的存储引擎。

项目推荐：基于SpringBoot2的底层框架包装器。全面管理。引入组件化的思想，实现高一致性、低耦合、高可配置性和可插拔性。严格管理包依赖关系和整体版本控制，以最大程度地减少依赖关系。注重代码规范和注释，非常适合个人学习和商业使用
Github地址：https://github.com/ Plasticene/ Plasticene-boot-starter-parent Gitee - 地址：https://gitee.com/ Plasticene3/ Plasticene-boot-starter-parent

1）连接器

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

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

主要负责用户登录数据库以及用户身份认证，包括账号密码、权限等验证操作。如果用户帐户的密码通过，连接器将查询权限表以获取该用户的所有权限。此连接中的后续权限逻辑评估将依赖于此时读取的权限数据。也就是说，只要以后连接没有断开，即使管理员更改了用户的权限，用户也不会受到影响。

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

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

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

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

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

MySQL 8.0版本之后删除了缓存功能。官方也认为这个功能没有多少实际用例，所以干脆删除了。

3）分析器

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

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

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

完成这2步之后，MySQL就可以开始执行了，但是怎么做，怎样才能得到最好的结果呢？此时，优化器就应该发挥作用了。

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

4）优化器

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

可以说，审查了优化器之后，这条语句的具体执行就确定了。

5）执行器

选择执行计划后，MySQL就准备开始执行了。首先，它会在执行之前检查用户是否有权限。如果没有权限，会返回错误信息。如果允许，它会调用引擎的接口并返回接口执行的结果。开始执行时，首先要判断自己是否有权限查询这张表T，如果没有，会返回无权限错误。

这里就是为什么优化器之前不检查查询表的执行权限呢？

有时这是因为 SQL 语句必须操作的表不仅仅是 SQL 文字中的表。例如，如果有触发，则必须在执行阶段（过程）期间确定。在优化阶段之前您无能为力。

2。如何执行 SQL 更新语句

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

1 ）redo log

首先，redo log是innodb引擎特有的，这也是innodb引擎成为mysql最主流引擎的主要原因。

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

如果有人想要赊账或者还债，店主通常有两种方式：

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

另一种方式 方法是先把账本写在粉红板上，等关门时间后，再拿出账本算。

当生意兴隆，柜台很忙的时候，店主肯定会选择后者，因为前者操作起来太麻烦。首先，找到该人的总信用账户记录。想想看，那是几十页紧密排列的页面。为了找到这个名字，店主可能需要戴上老花镜慢慢寻找。找到之后，他就会拿出算盘算一算，最后把结果写回账本上。这整个过程很难想象。相反，先把它写在粉红色的板上会更容易。想想看，如果店主没有粉板的帮助，他每次记账都得翻账本。效率是不是低得令人难以忍受？

同样，这个问题在MySQL中也存在。如果每次更新操作都要写入磁盘，然后磁盘也要找到相应的记录然后更新，那么整个过程的IO成本和寻道成本将会非常高。为了解决这个问题，MySQL的设计者采用了类似于酒店商家粉红板的思路来提高更新效率。粉红板和账本配合的整个过程，其实就是MySQL中经常提到的WAL技术。 WAL 的全称是预写式日志记录。关键点是先写日志再写Disk，即先写粉板，没有占用的时候再写账本。具体来说，当一条记录需要更新时，InnoDB引擎会首先将该记录写入重做日志（粉色板）并刷新内存。至此，更新完成。同时InnoDB引擎会在适当的时候将操作记录更新到磁盘，而这种更新往往是在系统比较空闲的时候进行的，就像店主打烊后所做的那样。如果今天的赊账不多，店主可以等到打烊的时候再整理货物。但是如果某一天信用账户很多，粉红板满了怎么办？此时，店主只得放下手中的工作，将粉红板上的一些信用记录更新到总账中，然后再将那些条目从粉红板上删除，为新的账户腾出空间。

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

write pos 是当前记录的位置。当您键入时，它会向后移动。写入到3号文件末尾后，返回到0号文件开头，检查点就是当前要删除的位置，同样向前移动，循环。在删除记录之前，必须将该记录更新到数据文件。写入位置和检查点之间的空间是“粉红板”的空白部分，可用于记录新操作。如果文包追上检查点，则说明“粉板”已满。此时，无法执行新的更新。您需要先停止并删除一些条目才能继续到检查点。

有了redolog，InnoDB可以保证即使数据库异常重启，之前提交的记录也不会丢失。此功能称为 碰撞安全。

2）binlog

上面我们讲的粉板重做日志是InnoDB引擎特有的日志，服务器层也有自己的日志，称为binlog（归档日志）。

我猜你想问，为什么有两条日志？

因为MySQL一开始并没有InnoDB引擎。 MySQL自己的引擎是MyISAM，但是MyISAM不具备防崩溃能力，binlog日志只能用于归档。 InnoDB 是由另一家公司作为插件引入 MySQL 的。由于仅仅依靠binlog不具备防崩溃的能力，因此InnoDB使用了不同的日志系统，即redo log来实现防崩溃的能力。

这两个日志之间存在三个差异。

• redo log是InnoDB引擎特有的； binlog是MySQL中服务器层实现的，可以被所有引擎使用。
• 重做日志是一种物理日志，记录“对特定数据页进行了哪些更改”； binlog是逻辑日志，记录了这条语句的原始逻辑，比如“给ID=2的行中的c字段加1”。
• 重复日志是循环写入的，空间永远是用完；binlog可以进一步写入，“添加写入”是指binlog文件达到一定大小后，会切换到下一个，不会覆盖上一个日志。

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

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

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

这条update语句的执行流程图如下：

从上面可以看出，写入redo log分为两个步骤：prepare和commit。这是“两步提交”。

为什么日志需要“两阶段提交”。这里不妨用反证法来解释。由于redo-log和binlog是两个独立的逻辑，如果不采用两步提交，要么先写redo-log再写binlog，要么采用相反的顺序。我们来看看这两种方法都存在哪些问题。

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

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

redo-log用于保证防崩溃功能。当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（读未提交）： 最低的隔离级别，可能会导致 uncom 读、幻读或不可重复读。
• READ-COMMITTED（读已提交）：允许读取并发事务提交的数据，可以防止脏读，但幻读或不可重复读仍然可能发生。
• REPEATABLE-READ（可重复读）：同一个字段的多次读取结果是一致的，除非数据被事务本身改变过，可以防止脏读和不可重复读但幻读读取仍然可能发生。
• SERIALIZABLE（可串行化）： 最高隔离级别，与ACID隔离级别完全兼容。所有交易均按顺序一笔一笔地执行，因此交易之间不存在干扰的可能性。也就是说，这个级别可以防止脏读、不可重复读和幻读。

MySQL InnoDB 存储引擎默认支持的隔离级别是 -可重复读取 ，Oracle 和 SQL Server 的默认隔离级别是 铅提交 。我们可以通过命令SELECT @@tx_isolation;来查看。在 MySQL 8.0 中，该命令更改为 SELECT @@transaction_isolation;

MySQL InnoDB 的 REPEATABLE-READ 不能保证避免幻读。读取需要应用程序使用锁定读取来保证。用于此锁定的机制是下一键锁。

因为隔离级别越低，事务请求的锁就越少，所以大多数数据库系统的隔离级别是READ-COMMITTED（读取已提交的内容）但是你需要知道的是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）

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

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

什么时候不再需要？

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

为什么不尝试使用长事务呢？

长交易意味着系统中会有很旧的交易视图。在执行该事务之前，必须保留回滚记录，这将导致消耗大量的存储空间。此外，长事务还会占用锁资源并可能损坏库。