SQL的查询和更新流程详解

Myth丶恋晨 2022-12-08 14:06 240阅读 0赞

### SQL的查询和更新执行流程 ###

MySQL 的基本架构示意图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center]

从中可以看出：MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

并且可以看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分。

#### 查询流程 ####

*  **连接器**：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
    
        mysql -h$ip -P$port -u$user -p
    
    输入该命令并且输入密码后，开始建立连接，之后连接器进行权限查询，即连接建立以后，权限就确定下来。如果发生变化，需要下次重新连接时生效。
    
    通过`show processist`可以查看当前的mysql连接状态。
 *  **查询缓存**：之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中，key 是查询的语句，value 是查询的结果。如果之后的查询语句能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。【查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，因此，MySQL 8.0 版本直接将查询缓存的整块功能删掉了】
 *  **分析器**：分析器对SQL语句进行解析。包括进行词法分析、关键词识别、语法分析等，如果有错，将会产生报错提醒
 *  **优化器**：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
 *  **执行器**：执行器是执行SQL语句的部分。通常首先会判断对该表是否有相应的权限，如果没有则返回错误，查询缓存也会进行权限查询。如果有权限，就打开表继续执行，并调用引擎的接口去完成执行。然后依次查询每一行，查看是否符合条件，符合的话存到结果中，然后继续执行。

如果查询一个没有的列，会在哪个阶段报错？

分析器。在分析阶段判断语句是否正确，表是否存在，列是否存在等，如果有error则会报错。

#### 更新流程 ####

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

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

##### redo log #####

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高，因此引入了redo log的方式提高效率。

也就是MYSQL的WAL技术。WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。如果redo log写的比较满了，那么这时候就会不再执行新的更新操作，会先处理一部分redo log中的更新操作写到磁盘里，然后再继续采用WAL技术。

redo log不仅提高了更新效率，并且为数据库提供了一个crash-safe的能力，即使数据库发生异常重启，之前提交的日志也不会丢失。

*  redo log 是 InnoDB 引擎特有的
 *  redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
 *  redo log 是循环写的，空间固定会用完

innodb\_flush\_log\_at\_trx\_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不丢失。

##### binlog #####

*  binlog日志用于归档，是 MySQL 的 Server 层实现的，所有引擎都可以使用。
 *  binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1"。
 *  binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

sync\_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘，可以保证 MySQL 异常重启之后 binlog 不丢失。

因此，对于一条执行语句 `update T set c=c+1 where ID=2;` ，也就是把ID=2的行的c值+1.

执行流程：

*  执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回
 *  执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据
 *  引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
 *  执行器生成这个操作的 binlog，并把 binlog 写入磁盘
 *  执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

redo log的写入是两阶段提交：prepare 和 commit

采用两阶段提交的关键在于要维持redo log 和 binlog的数据一致性，不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。实际上是把这个提交改成了一个事件，要么都完成，要么不做。否则不管是redo log还是binlog现写完提交然后再去写另一个都可能导致在这期间的MYSQL故障导致数据不一致。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RpbmdkaW5nZG9kbw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/d3b4e8e0da354aa1b130bd34ccbe0b96.png