MySQL中查询语句和更新语句的执行流程_mysql 查询更新-优快云博客

本文链接：https://blog.youkuaiyun.com/liiilbb/article/details/143744151

参考：MySQL缓存失效分析与解决：避免性能瓶颈，保障数据库稳定运行 - 优快云文库

（1）更新语句的执行流程中出现两个日志模块：MySQL 自带的日志模块是 binlog（归档日志），所有的存储引擎都可以使用， InnoDB 引擎还自带了一个日志模块 redo log（重做日志），So为什么要两个日志模块？

话不多说，直接放出

查询语句的执行流程如下：权限校验（如果命中缓存）--->查询缓存--->分析器--->优化器--->权限校验--->执行器--->引擎

更新语句执行流程如下：分析器---->权限校验---->执行器--->引擎---redo log(prepare 状态)--->binlog--->redo log(commit 状态)

一、查询语句和更新语句

我们的 SQL 可以分为两种，一种是查询，一种是更新（增加，修改，删除）。

二、MySQL 基本架构

MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。

存储引擎层：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redo log 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。

   连接器： 身份认证和权限相关

负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作)。

如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，只要这个连接不断开，即使管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存： 连接建立后，执行查询语句的时候，会先查询缓存，

MySQL 会先校验这个 SQL 是否执行过，以 Key-Value（Key 是查询语句，Value 是结果集）的形式缓存在内存中，如果缓存 key 被命中，就会直接返回给客户端。

如果没有命中，跳过并执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。

注：MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。所以，MySQL 8.0 版本后删除了缓存的功能

分析器： 没有命中缓存的话，SQL 语句就会经过分析器。

词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
语法分析，主要就是判断你输入的 SQL 是否正确，是否符合 MySQL 的语法。

优化器： 按照 MySQL 认为最优的方案去执行（有时候可能也不是最优）。

                      比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器： 执行语句，然后从存储引擎返回数据。

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

三、例子

select * from tb_student  A where A.age='18' and A.name='冰箱里的金鱼 ';

结合上面的说明，我们分析下这个查询语句SELECT的执行流程：

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

通过分析器进行词法分析，提取 SQL 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student，需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

接下来就是优化器进行确定执行方案，上面的 SQL 语句，可以有两种执行方案：a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

update tb_student A set A.age='19' where A.name=' 冰箱里的金鱼 ';

结合上面的说明，我们分析下这个更新语句UPDATA的执行流程：

先查询到张三这一条数据，不会走查询缓存，因为更新语句会导致与该表相关的查询缓存失效。
然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
更新完成。

四、问题

（1）更新语句的执行流程中出现两个日志模块：MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用， InnoDB 引擎还自带了一个日志模块 redo log（重做日志），So为什么要两个日志模块？

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

写完 binlog 后，然后再提交 redo log 就会防止出现数据丢失，从而保证了数据的一致性。

但是也会有极端的情况，假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

判断 redo log 是否完整，如果判断是完整的，就立即提交。

如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

（2）从文中知道更新语句会导致与该表相关的查询缓存失效。So缓存失效的原因，有什么优化策略？

首先，MySQL缓存机制是通过在内存中存储经常访问的数据，以减少磁盘IO操作，从而提高数据库查询性能的重要技术。它包括查询缓存、键值缓存和缓冲池三种主要类型。

查询缓存将最近执行的查询及其结果存储在内存中，当后续查询与缓存中的查询匹配时，直接从缓存中返回结果，避免了对数据库的访问。
键值缓存将数据键值对存储在内存中，当需要访问数据时，直接从缓存中获取，而无需查询数据库。
缓冲池将经常访问的数据库页加载到内存中，当需要访问这些页时，直接从缓冲池中读取，避免了对磁盘的访问。

缓存失效是指缓存中的数据与数据库中的实际数据不一致的情况。这会导致查询返回过时或不准确的数据。缓存失效的原因主要有：

数据更新：当数据库中的数据发生更新时，缓存中的数据将不再是最新的。
缓存过期：缓存中的数据具有有限的生存期，当超过生存期时，数据将被清除。
手动清除：可以通过命令或操作手动清除缓存中的数据。
系统故障：当MySQL服务器发生故障时，缓存中的数据可能会丢失。

优化策略

1、 优化查询语句

缓存失效的一个常见原因是查询语句不合理，导致缓存中的数据与数据库中的数据不一致。优化查询语句可以有效减少缓存失效的发生。

优化策略：

使用索引：为查询中涉及的字段创建索引，可以显著提高查询速度，减少缓存失效的可能性。
使用缓存友好的查询：避免使用 SELECT * 查询，只选择需要的列，减少返回结果集的大小，从而提高缓存命中率。
合理使用缓存键：查询语句生成合理的缓存键，确保相同查询语句始终返回相同的缓存结果，避免因缓存键不同而导致缓存失效。

代码示例：

-- 未优化查询
SELECT * FROM table_name;

-- 优化查询
SELECT id, name, age FROM table_name WHERE id = 1;

2、调整缓存配置参数

MySQL 提供了多种缓存配置参数，通过调整这些参数可以优化缓存性能，减少缓存失效的发生。

主要参数：

**innodb_buffer_pool_size：**设置缓冲池大小，缓冲池是 MySQL 缓存数据的地方，增大缓冲池可以减少缓存失效的可能性。
**innodb_buffer_pool_instances：**设置缓冲池实例数，多个实例可以并行处理查询，提高缓存命中率。
**innodb_flush_log_at_trx_commit：**控制事务日志的刷新频率，较低的刷新频率可以提高性能，但也会增加缓存失效的风险。
**innodb_flush_method：**设置刷新方法，O_DIRECT 方法可以绕过文件系统缓存，直接将数据写入磁盘，减少缓存失效的可能性。

代码示例：

-- 调整缓冲池大小
SET GLOBAL innodb_buffer_pool_size = 1G;

-- 设置缓冲池实例数
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 设置事务日志刷新频率
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 设置刷新方法
SET GLOBAL innodb_flush_method = O_DIRECT;

3、使用持久化存储

对于一些关键数据，可以使用持久化存储来避免缓存失效。持久化存储将数据存储在非易失性介质中，即使服务器重启或发生故障，数据也不会丢失。

持久化方式：

Redis：一种内存数据库，支持持久化功能，可以将数据持久化到磁盘上。
Memcached：一种分布式缓存系统，支持持久化功能，可以将数据持久化到磁盘上。
文件系统：将数据写入文件系统，确保数据在服务器重启或发生故障后仍然存在。

代码示例：

// 使用 Redis 持久化数据
$redis->set('key', 'value');
$redis->save();

// 使用 Memcached 持久化数据
$memcached->set('key', 'value', 0, 0);
$memcached->quit();

// 使用文件系统持久化数据
file_put_contents('data.txt', 'value');