MySQL实战45讲学习笔记（一）

urlyy
  2022-02-26 11:46:27 2022-02-26 11:46:27 创建   2023-07-02 11:53:55 2023-07-02 11:53:55 更新    

查询语句执行过程 （select * from T where ID=10;）

mysql的逻辑架构图

在这里插入图片描述
大体来说，MySQL 可以分为 Server 层和存储引擎层两部分，不同的存储引擎共用一个Server 层。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。不过可以在 create table 语句中使用 engine=xxx来指定引擎

下文涉及到的是select * from T where ID=10；这条SQL运行的过程

1. 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令：

1
2
3
mysql -h$ip -P$port -u$user -p
如
mysql -hlocalhost -uroot -proot

可以只写个-p并换行再输密码，这样安全一些。如果你连的是生产服务器，强烈建议你这么做。
在这里插入图片描述
命令中的mysql是客户端工具，用来跟服务端建立连接。
在输入用户名和密码后，如果有误，则报错Access denied for user 否则在权限表内查出你的权限，你接下来的操作就限于这个权限内了
但是，如果一个用户已经登录了，那即便管理员修改了他的权限，他当前的权限也不会收到影响，除非他重新登录，建立新的连接

连接完成后如果不在这个连接上进行操作，那么这个连接就处于空闲状态，可以在 show processlist 命令中看到它。

客户端超过8h没响应，就会断开这个连接，这个时间是由参数wait_timeout控制的，即默认是8h
此时再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

在使用中肯定要尽量使用长连接，但是长连接也有弊端：MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。这些内存累计起来就可能导致进程被系统杀死，即异常重启
对此有两种解决方案：

  1. 规定一个最长连接时间，连接时间超过这个时间的连接就断开；如果刚刚执行过了一个占内存很大的操作，就断开。
  2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2. 查询缓存

大多数情况下作者不建议使用查询缓存，因为弊大于利
原因：只要你对一张表进行了更新，这张表的查询缓存就会清空，所以除非是很长时间都不会更改的、近似静态或就是静态的表，就推荐用这个查询缓存。
需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

3. 分析器

即解析SQL语句

  1. 词法分析：提取出”select”这个查询操作关键词，”T”这个表名
  2. 语法分析：分析SQL是否符合语法，不符合就会You have an error in your SQL syntax，查错要关注的是紧接use near之后的内容。

4. 优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。有时候几个执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

5. 执行器

先判断用户有没有对表T进行查询操作的权限
如果有权限，就打开表继续执行，执行器就会根据表的引擎定义，去使用这个引擎提供的接口，获得结果集返回给客户端。

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的，但是引擎扫描行数跟 rows_examined 并不是完全相同的。

课后问题：
如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报Unknown column ‘k’ in ‘where clause，这个错误是在我们上面提到的哪个阶段报出来的呢？
答：分析器。分析器会提取出输入的SQL的关键词，并且判断语句是否正确，表是否存在，列是否存在等。

更新操作过程中的日志模块 （update T set c=c+1 where ID=2;）

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，redo log（重做日志）和 binlog（归档日志）。
redo log 是 InnoDB 引擎特有的日志， binlog是server层的日志
在这里插入图片描述

redo log

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

所以有了Mysql的WAL(Write-Ahead Logging)技术，即先写日志，再写磁盘。当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，

当然，redo log也有大小限制，当大小不足时会先停下来把一部分记录更新到磁盘，以空出空间给后面的操作用

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

两个日志模块的比较

有两个日志模块的原因：Mysql自带的MyISAM引擎不能crash-safe，**binlog 日志只能用于归档**，所以那个写了innoDB的公司就在innoDB里写了具有crash-safe能力的redo log

两种日志的不同：

  1. redo log 是InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；`binlog1 是逻辑日志，记录的是这个SQL语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

  4. redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。
    binlog（归档日志）保证了MySQL集群架构的数据一致性。

执行器在用innoDB引擎执行这个update操作的流程

  1. 执行器先从引擎中找到数据，如果在内存则直接返回，如果不在内存查询后返回
  2. 执行器拿到数据后，会先修改数据，然后调用引擎接口重新写入数据
  3. 引擎将数据更新到内存，同时写入redo log，此时处于prepare状态
  4. 执行器生成这个操作的binlog
  5. 执行器调用引擎的事务提交接口，将redo log状态改成commit状态，更新完成

作者的图
在这里插入图片描述

两阶段提交

注意最下面的这三个
发现一个记将录写入redo log的操作，愣是先把先设为prepare状态，、再设置为commit状态，难道不能直接设置吗？
在这里插入图片描述
我们假设不搞两阶段提交，并且举出两种情况

  1. 先写binlog，再写redo log
    当前事务提交后，写入binlog成功，之后主节点崩溃。在主节点重启后，由于没有写入redo log，因此不会恢复该条数据。
    而从节点依据binlog在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致。

  2. 先写redo log，再写binlog
    当前事务提交后，写入redo log成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。

因此，只写一次redo log与binlog，无法保证这两种日志在事务提交后的一致性。也就是无法保证主节点崩溃恢复与从节点本地回放数据的一致性。

不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
即：

  1. redo 还没写入之前崩溃，这时binlog也还没写入，恢复数据不受影响
  2. redo写好了，binlog还没写入之前崩溃，这是redo处于prepare状态，还没有提交，恢复时事务会回滚，binlog也还没有记录，所以不会影响
  3. redo写好了，binlog写好了，但是还没有commit时崩溃了，这时会判断对应事务的binlog是否存在并完整：
    （1）如果存在并完整则提交事务，这时恢复到事务提交之后的状态，因为binlog中有记录，所以恢复成功
    （2）如果binlog不存在或者不完整，这时会恢复到事务提交之前的状态，因为binlog中无记录或者不完整的记录不会生效，所以恢复也成功。
  4. redo已经有了commit标识，则直接提交事务，同时因为binlog有记录，则恢复数据也不受影响

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

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

  • 标题: MySQL实战45讲学习笔记（一）
  • 作者: urlyy
  • 创建于 : 2022-02-26 11:46:27
  • 更新于 : 2023-07-02 11:53:55
  • 链接: https://urlyy.github.io/2022/02/26/MySQL实战45讲学习笔记（一）/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
此页目录
MySQL实战45讲学习笔记（一）
  1. 查询语句执行过程 （select * from T where ID=10;）
    1. mysql的逻辑架构图
    2. 1. 连接器
    3. 2. 查询缓存
    4. 3. 分析器
    5. 4. 优化器
    6. 5. 执行器
  2. 更新操作过程中的日志模块 （update T set c=c+1 where ID=2;）
    1. redo log
    2. 两个日志模块的比较
    3. 执行器在用innoDB引擎执行这个update操作的流程
      1. 两阶段提交