mysql-SQL语句执行过程

mysql系统结构组成

mysql 执行sql时主要分为两个模块：客户端与服务端，服务端包括serve层与存储引擎层。

客户端

各种连接mysql工具，如navicat、SQLyog、JDBC等，主要用来发送执行SQL的请求

服务端

服务端分为server层与存储引擎层：

server层主要负责数据库连接，权限管理、安全认证、SQL语句解析、分析、SQL优化，提供各种函数支持存储过程。并且server层具有专属的binlog归档日志，记录所有修改数据库数据的SQL操作，可以用来进行数据恢复与主从架构。server层包括连接器、查询缓存、分析器、优化器、执行器等模块。

存储引擎层负责数据存储与检索。支持多种存储引擎 ：innodb、myisam、memory。innodb支持事务、行锁、具有自己的日志系统 undoLog、redoLog。

一条SQL查询语句是如何执行

mysql>select*fromuserwhereid=10;

客户端发送执行SQL请求后经历以下步骤:

步骤1：连接器 验证用户名与密码查询相应权限，维护连接状态 步骤2：查询缓存（mysql8.0已经移除） 以SQL语句作为key查询缓存，命中后直接返回,返回前要进行权限验证。 移除原因：缓存失效平凡，利大于弊 步骤3：分析器（词法分析/语法分析） 词法分析:分析SQL语句每个单词含义 语法分析:检查SQL语句是否满足mysql规则 步骤4: 优化器 对SQL语句进行优化,决定如何执行查询.如:表中存在多个索引时决定使用哪个索引,多表关联时表连接顺序.生成执行计划,通过explain查看. 步骤5: 执行器 再次确认是否存在表操作权限(可能存在只有运行时才能确定的表),调用存储引擎接口. 步骤6: 返回结果 - 无索引:每次取出表中一条记录,将满足条件的存入结果集,重复相同的判断逻辑，直到取到这个表的最后一行,执行器将所有满足条件的行组成结果集作为结果集返回. - 有索引 :调用引擎的索引接口，直接定位到满足条件的行 ,引擎内部使用 B+Tree 查找，不需要逐行扫描 - 索引 + 回表 : 调用引擎接口，使用索引定位 ,引擎在索引树上找到满足条件的主键 id,对每个主键，回表查询完整行数据 - 索引下推: 备注: 1:分析器进行语法分析时也会对SQL语句中 要操作的表作初步权限检查preCheck. 2:存储引擎层没有表达式计算能力：InnoDB 只负责数据存取，不负责 WHERE 条件的复杂计算（如 age*2 > 100）索引条件下推（ICP）是个例外

一条SQL更新语句是如何执行的？

mysql>createtableT(IDintprimarykey,cint);mysql>updateTsetc=c+1whereID=2;

执行过程:**

1.连接器进行数据库连接 2.清空与表相关的所有缓存 3:分析器进行词法/语法分析 4:优化器决定使用哪个索引 5:执行器进行权限验证,通过后调用存储引擎 6:存储引擎通过主键索引定位到id=2的数据,如果buffer pool中存在则直接返回,不存在将磁盘一页数据读入内存,在返回 7:执行器拿到数进行逻辑计算,得到新数据 8:执行器调用存储引擎将新数据写入buffer pool中,同时记录redolog文件,此时redolog文件为prepare状态,通知执行器 9:执行器生成此次操作binlog日志,写入磁盘 10:执行器调用提交事务接口,将redolog日志状态改为commit提交状态

redoLog重做日志 (物理日志)

作用:记录事务对数据库的增删改操作 功能:保证事务持久性,实现系统崩溃恢复

Redo Log 循环写机制:

作用:通过固定大小文件实现高效可循环的文件写入.(文件可多个). 为什么循环写效率高: 1:将随机写文件变成顺序写文件,顺序写文件磁盘i/o比随机写磁盘i/o效率指数级增加. 2:数据存储位置未知,如果事务中存在多个update,必须通过主键确定数据位置,无法预计下一个update修改哪一页数据.

WAL 技术（预写式日志）

先写日志，后写数据

binlog归档日志 (逻辑日志)

作用:详细记录数据库更改操作的日志文件，以二进制形式存储. 功能:数据恢复,主从复制

两阶段提交(2PC):

方式: 将redoLog文件写入分为PrePare与commit两阶段.

第一阶段: prepre阶段 1. 写入redoLog ---PrePare状态 2. 写入binLog, 第二阶段:commit阶段 4. 将redoLog状态改为Commit状态 5. 提交事务

作用:保证redoLog 与 binLog一致性

崩溃恢复情况

情况1：redo log 无 prepare 标记 → 事务未开始或未完成，直接回滚 情况2：redo log 有 prepare，但 binlog 无对应记录 → 事务在 prepare 后、binlog 前崩溃 → 回滚事务（数据不一致，需要舍弃） 情况3：redo log 有 prepare，binlog 也有完整记录 → 事务在 binlog 后崩溃 → 提交事务（重放 redo log） 情况4：redo log 有 commit → 事务已完成，直接提交

问题:

场景1: 假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启. 场景2: 先写 binlog 后写 redo log 场景3: binlog 写完，redo log 还没 commit前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？ redo log 和 binlog 是怎么关联起来的? redo log buffer 是什么？是先修改内存，还是先写 redo log文件？