MySQL之SQL执行流程

欧尼熊不懂

已于 2022-10-04 23:46:52 修改

阅读量296

点赞数

文章标签： mysql sql 数据库

于 2022-09-17 01:09:54 首次发布

原文链接：https://blog.csdn.net/fangkang7/article/details/119722112

版权

文章目录

- SQL执行

SQL执行

执行流程

在这里插入图片描述

连接器

通过 mysql -u用户名 -p 指令连接数据库
- 执行命令后输入密码
  - 完成 TCP 握手连接器开始发挥作用
- 密码错误返回错误编码 1045
连接正确根据输入的用户访问权限表获取用户权限
- 登陆成功后被修改权限，当前连接未断开之前权限不会改变
- show variables like 'wait_timeout'：查看当前连接时间
  - 一般默认 8 小时无操作自动断开，单位 s

查询缓存

此模块设计将查询语句作为 key，结果作为 value 进行缓存
- 当表数据有更新时所有缓存都会被清除
MySQL 8.0 之下版本通过参数 query_cache_type = enmand 控制是否使用缓存
- 设置后默认的 select 语句将不会被缓存
被缓存过的的 select 语句在执行时会从结果集直接返回
- 未缓存的会重新查找
MySQL 8.0 取消此模块

分析器

分析器就是分析要执行的 SQL 语句
1. 判断语句类型
  - DML、DQL、DDL、rep、stauts
    - 数据操作、查询、表维护、复制、状态操作
2. 进行词法分析
  - 识别表名、字段
3. 进行语法分析
  - 语法错误返回异常信息
MySQL 8.0 前进入分析器之前需先判断缓存
- 8.0 后连接器验证成功之后直接进入分析器

优化器

MySQL 对执行的操作选择最佳执行方案
优化操作
- 多索引时选择最佳索引
- 多表连接的连接顺序等操作
虽然优化器会对 SQL 优化执行，但写 SQL 时仍要考虑最佳效率写法
- 以小表驱动大表

执行器

全部验证完毕无误才会真正执行 SQL 语句
1. 先进行权限验证
2. 根据表定义的存储引擎使用对应引擎的接口

总结

客户端执行 SQL

连接器
- 失败返回密码错误编码到客户端
- 成功进入下一步
分析器
- 语句异常返回错误码
- 成功执行下一步
优化器
- 选择最优效率的 SQL 执行方式
执行器
1. 调用对应表定义的存储引擎
2. 根据接口获取数据
3. 符合的数据组成结果集返回到客户端
存储引擎接口

日志

InnoDB 存储引擎的 redo log：重做日志
MySQL Servce 层的 binlog：归档日志
MySQL 8.0 中两个日志文件都在 /var/lib/mysql 目录下
- lib_longfile0、lib_longfile1 是重做日志
- undo_001、undo_002 是回滚日志

redo log

作用

实现事务持久性的必备要素
1. 事务提交后并非直接修改数据库数据
  - 首先保证在 redo log 中记录相关操作
  - 数据库根据相应机制将内存中的脏页数据刷新到磁盘中
2. MySQL 先将更新记录到 redo log，空闲后记录到磁盘
InnoDB 存储引擎中 redo log 大小固定
- 两个文件，每个默认大小 48M
  - 文件是闭环的循环写，设定的文件个数和大小不再增加
  - innodb_log_file_size 参数控制单个文件大小
MySQL 5.6.8 及之后版本默认 48M
write pos：记录当前位置，同时后移
- 到 ib-logfile-3 文件末尾后返回 ib-logfile-0 文件开始写
check point：记录当前擦除的位置
- 要使文件循环写必须边写边擦除
- 清除数据的前提是先将记录更新到数据文件
绿色就是可写部分
- write pos 推进是因为正在执行更新操作
  - 此时必须等记录更新到数据文件，chek point 擦除后才能继续更新

刷盘机制

InnoDB 存储引擎内存区域的一部分
- Buffer pool：缓冲池
- redo log buffer：重做日志缓冲区
os cache/buffer：操作系统缓存
redo log file 位于磁盘位置
DML 操作时数据先写入 Buffer pool，再写到 redo log buffer
- redo log buffer 根据刷盘机制进行写入重做日志
刷盘机制设置参数：innodb_flush_log_at_trx_commit
- 取值：0、1、2
- 重做日志写入策略
  - 0
    1. commit 后数据存放到 redo log buffer
    2. 从 redo log buffer 每秒写入到 os cache 且 fulsh 到磁盘
  - 1：MySQL 默认策略
    - commit 后必须把 redo log buffer 从内存刷入磁盘文件
      - 事务提交成功 redo log 必然在磁盘
  - 2：
    1. commit 后将 redo log buffer 写入磁盘文件对应的 os cache
      - 不直接进入磁盘文件
    2. 1 秒后 flush 到磁盘
- 服务器异常停止时事务写入过程
  - 0：前一秒日志保存在内存中的日志缓冲区
    - 机器宕机可能丢失 1 秒的事务数据
  - 1：数据库对 IO 的要求非常高
    - 底层硬件提供的 IOPS 较差时 MySQL 数据库的并发很快会由于硬件 IO 问题无法提升
  - 2：数据直接写入 od cache 缓存，属于系统操作部分
    - 操作系统部分损坏或断电才会丢失一秒内数据
    - 相对于 0 安全许多，且对 IO 要求相对较低
- 小结
  - 性能：0 > 2 > 1
  - 安全：1 > 2 > 0
  - MySQL数据库刷盘策略默认值 1
    - 保证事务提交后数据绝对不会丢失
简单重做日志写入流程

innodb_log_file_size

innodb_log_file_size 设置
1. 设置太小会导致 redo log 文件频繁切换，频繁触及检查点 check point
  - 导致记录更新到数据文件次数增加，影响 IO 性能
  - 若有大事务将所有 redo log 日志写满仍未完成将导致日志无法切换
    - 导致 MySQL 堵死
2. 设置太大虽极大提高 IO 性能，但 MySQL 启动或宕机时恢复时间会因文件太大而过长
  - 这种恢复时间通常无法控制
3. 合理设置 redo log 大小和数量
  - InnoDB 能保证即使数据库发生异常重启之前提交的记录也不会丢失
  - 这点叫做 crash-safe
根据项目情况设置 innodb_log_file_size
- 参数 innodb_log_files_in_group 设置 3 ~ 4 个即可，无需优化
- 对 innodb_log_file_size 的进行大小或优化设置
MySQL 8.0 之前通常计算一段时间内生成的事务日志 red log 大小
- MySQL 日志文件最小应承载一小时业务量，视具体业务情况而定
查看 sequence 一分钟之内的值
- suquence
  - 每个 binlog 生成时该值从 1 开始进行递增
  - 每增加一个事务，sequence number 加 1
- pager 命令：设置 page 显示方式提高效率
  - 执行 pager grep sequence;
    - PAGER set to 'grep sequence'
  - 禁止 pager 设置执行 nopager
    - 不执行该命令则只有下一次启动该命令才会失效
- show engine innodb status\G select sleep (60); show engine innodb status\G;
  - 得到一分钟前后的值进行计算
  - select (后边数据-前面的数据)/1024/1024*60 as MB_per_hour;
    - 随机一分钟计算日志值误差较大
    - 确定几个时间点使用脚本执行记录相应值取平均值

binlog

MySQL 总体分为两层架构
1. server 层
  - 负责功能方面
  - 日志记录：binlog
2. 存储引擎层
  - 负责处理与存储相关操作
  - redo log 是 InnoDB 存储引擎独有的，其他存储引擎不具备

binlog 和 red log

red lod 是 InnoDB 引擎特有
- binlog 是 MySQL server 层特有，所有引擎都可以使用
red log 是物理日志，记录更新操作的修改
- binlog 是逻辑日志，记录更新语句的执行逻辑
red log 是循环写，空间固定
- binlog 是追加写，当前文件写完换下一个文件，不会覆盖之前日志
- 所以有完整的 binlog 文件可以恢复到想要的数据

为什么要两份日志

没有 InnoDB 存储引擎之前 MySQL 默认 MyIsam 引擎
- MyIsam 引擎没有重启恢复的能力
- binlog 日志也只用于归档
InnoDB 起初是另家公司以插件形式引入到 MySQL
- 使用 redo log 实现重启恢复功能
- 导致使用 InnoDB 引擎时会写两份日志

两阶段提交

流程

更新 SQL 会先写 redo log 再写 bining
例如：update user set age = age + 1 where id = 1;
1. 执行器到引擎层先找到 id = 1 数据
  - id 是主键，所以在主键索引树找到这一行
  - 若数据所在数据页在内存中直接返回行数据执行器
    - 否则从磁盘读入内存，再返回行数据
2. 执行器拿到存储引擎返回的 id = 1 行记录后执行 age + 1
  - 调用引擎接口写入新数据
3. 引擎将新数据先更新到内存，同时将更新操作记录到 red log
  - 此时 redo log 处于 prepare 状态
  - 通知执行器执行完成，随时可以提交事务
4. 执行器生成该操作的 binlog，将 binlog 写入到磁盘
5. 执行器调用引擎的提交事务接口
  - 引擎将刚写入的 redo log 改成提交 commit 状态
6. 更新完成

意义

为什么要两段提交
- 让 redo log 和 binlog 两份日志间逻辑一致
- 不使用两阶段提交则原库和用其 binlog 恢复出来的数据是不一致的
先写 redo log 再写 binlog
- 例：更新语句为：aga = age + 1
  1. 数据写入 redo log，此时 MySQL 进程异常重启
    - binlog 还未开始写
    - 系统重启后进行数据恢复，此时 age 值已经被更改
  2. 搭建从库时通过 binlog 进行数据恢复
    - 但此时 age 的更新操作未记录到 binlog
  3. 从库中会缺少此次更新数据
    - 恢复的数据 age 依旧是原值，主、从库数据不一致
先写 binlog 再 redo log
- 例：更新语句为：aga = age + 1
  1. 先写入 binlog，此时 MySQL 进程异常重启
    - 此时 red log 还未记录操做
    - 重启后更新操作对于 redo log 不存在
  2. 重启后数据值没有变化
    - 搭建从库使用 binlog，已经记录了更新操作
  3. 此时从库中数据被更新，主从数据不一致