文章目录

  • 前言
  • mysql官方架构图
  • SQL执行过程图
  • Server层和存储引擎层
  • 连接器
  • 查询缓存
  • 分析器
  • 优化器
  • 执行器
  • 语句分析
  • 查询语句
  • 日志
  • redo log 和 binlog 区别：
  • 更新语句
  • 操作顺序
  • 总结
  • 参考资料


前言

mysql学习第二期, 让sql在你脑海里也能生成执行计划

mysql官方架构图

怎么查询mysql当前执行的sql有哪些_数据

SQL执行过程图

怎么查询mysql当前执行的sql有哪些_MySQL_02

Server层和存储引擎层

  • Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块
  • 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。InnoDB 作为5.5.5版本默认引擎

连接器

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作。如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据。也就是说，后续只要这个连接不断开，即使管理员修改了该用户的权限，该用户也是不受影响的

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中。Key是查询语句，Value是结果集。如果缓存key被命中，就会直接返回结果给客户端；如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了

设置query_cache_size大小则开启缓存, 0表示不开启
不建议开启: 开启后要保持数据同步, 大大提高了消耗和IO, 所以被删除

分析器

mysql 没有命中缓存，那么就会进入分析器。分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：

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

优化器

优化器的作用就是以它认为的最优的执行方案去执行（虽然有时候也不是最优）。比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等

执行器

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

语句分析

那么究竟一条sql语句是怎么执行的呢？其实sql语句大致上可以分为两种，一种是查询语句，一种是更新语句（增加、更新、删除）

查询语句

select * from tb_student  a where a.age='18' and a.name=' 张三 ';

结合刚刚上面的说明，我们来分析一下这条语句的具体执行流程：

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步
  • 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步
  • 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：
  1. 先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18
  2. 先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生

那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了

  • 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果

日志

binlog（归档日志）是MySQL的Server层有的逻辑日志

主要记录用户对数据库操作的SQL语句

binlog 属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠 binlog 是没有 crash-safe 能力的

binlog 有两种模式，statement 格式的话是记 sql 语句，row 格式会记录行的内容，记两条，更新前和更新后都有

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

正是由于binlog有归档的作用，所以binlog主要用作主从同步和数据库基于时间点的还原

undo log 回滚日志

undo log顾名思义，主要就是提供了回滚的作用，但其还有另一个主要作用，就是多个行版本控制(MVCC)，保证事务的原子性。在数据修改的流程中，会记录一条与当前操作相反的逻辑日志到undo log中（可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录），如果因为某些原因导致事务异常失败了，可以借助该undo log进行回滚，保证事务的完整性，所以undo log也必不可少

redo log （重做日志）是 InnoDB 引擎特有的日志 物理日志

记录的是数据库中每个页的修改，可以用来恢复提交后的物理数据页（恢复数据页，且只能恢复到最后一次提交的位置，因为修改会覆盖之前的）

在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就采用了日志（redo log）来提升更新效率

而日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（redolog buffer）里面，并更新内存（buffer pool），这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候（如系统空闲时），将这个操作记录更新到磁盘里面（刷脏页）

redo log 是 InnoDB 存储引擎层的日志，又称重做日志文件，redo log 是顺序循环写的，相比于更新数据文件的随机写，日志的写入开销更小，能显著提升语句的执行性能，提高并发量

在redo log满了到擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求，所以有可能会导致MySQL卡顿。（所以针对并发量大的系统，适当设置redo log的文件大小非常重要）

redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么日志总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写

redo log 和 binlog 区别：
  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是在某个数据页上做了什么修改，记录了数据；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

tips：crash-safe 指MySQL服务器宕机重启后，能够保证：

  • 所有已经提交的事务的数据仍然存在
  • 所有没有提交的事务的数据自动回滚

更新语句

UPDATE test SET number = 1024 WHERE id = 1;
操作顺序
  1. 查找记录：执行器先找引擎取id=1这一行。ID是主键，引擎直接用树搜索找到这一行。如果id=1这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回；
  2. 执行器拿到引擎返回的行数据，把c+1，得到新的一行数据，再调用引擎接口写入这行新数据；
  3. 引擎将这行新数据更新到内存中，同时将这个更新之后的数据记录到redo log里面，此时redo log处于prepare状态；
  4. 引擎告知执行器，我执行完成了，你随时可以调我的接口提交事务了；
  5. 执行器生成这个操作的binlog，并把binlog写入磁盘。
  6. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成。

怎么查询mysql当前执行的sql有哪些_mysql_03


那么问题来了，为什么redo log要分两步写，中间再穿插写binlog呢？

在上图中我们可以看到redolog分为两个阶段，那为什么要这样呢？如果不这样会发生什么问题？下面我们用反证法进行论述：

  • 先写redolog并提交，然后再写binlog，假设redolog写完后，机器宕机了，这个时候binlog没有写入，机器重新启动之后，由于redo log已经写完了，系统重启后会通过redo log将数据恢复回来，但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的数据与原库的值不同。造成了主从不一致
  • 先写 binlog，然后写 redo log，如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以没有记录到redolog。但是binlog里面已经记录了日志。所以，在之后用binlog来恢复的时候，恢复出来的临时库中的数据就与原库的值不同。

总结

怎么查询mysql当前执行的sql有哪些_sql_04

参考资料

MySQL 的 crash-safe 原理解析MySQL官方中文文档第26章