极客时间 mysql四十五讲学习笔记(一)

vlambda
2022-02-13

极客时间 mysql四十五讲学习笔记(一)

首先通过《一条查询SQL 是如何执行的》来了解mysql的整体架构

架构图如下

软件编程中有一条至理名言，"高效解决复杂问题的最好方式就是分层，如果还没解决那就继续分层"，mysql也不例外，先分了server层与存储引擎层，其实server层又包括：

连接器（处理客户端连接的问题），

查询缓存（8.0版本默认关闭这个功能），

分析器（分析执行sql的语法是否正确），

优化器（生成mysql的执行计划，比如有多个表进行join 查询的时候，决定哪个表是主表，哪些表是副表），

执行器（连接存储引擎，查询数据返回）

存储引擎层是mysql存储数据核心逻辑都在这里，不同的存储引擎的数据结构，设计理念可能都不一样，比如myisam 中索引文件（myI）与数据文件（myd）是分开的，但是innodb中是索引文件与数据文件都保存在idb文件中。

第二章通过《一条更新sql的执行逻辑》来分析数据库中常用的log文件（redo log ,bin log）

redo log

Redo log 的执行逻辑如下

Mysql 中常说的WAL（write-ahead logging）技术,它的关键技术点就是先写日志文件，再写磁盘，具体讲就是先将数据写到redo log 中，并更新内存，然后在适当的时候将数据更新到磁盘中(什么时候才能称之为合适的时候，以什么样的方式，什么频率将数据更新到磁盘中)。

一般 redo log 是固定大小的，比如上图，将redo log 配置成四个文件，其中write pos是当前记录的位置，一边写一边往后移，写到3号文件的末尾就回到0号文件开头了，checkpoint 是当前要擦除的位置，也是不断往后推移的，在擦除前，会把记录更新到数据文件中

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

想一想，如果write pos 赶上了checkpoint ，这个时候数据没有写库，线程会暂停，然后发生了crash,是不是会发生数据丢失的问题， mysql是通过什么机制来解决这个问题的

顺便看看redo log 是什么样子的

正是因为有了redo log, innodb就拥有了crash-safe的能力，即便服务器突然宕机了，也不会造成数据丢失的问题

Bin log

Bin log是server 层的日志，我们常常称之为归档日志，也就是说它只用于归档，不能实现crash-safe 功能

Redo log 是物理日志，记录的是“在某个数据页上做了什么修改，”binlog 是逻辑日志，记录的是这个语句的原始逻辑；

redo log 是循环写的，空间是固定的，会用完； bin log 是追加写的，binlog 文件写到一定大小，就会切换到下一个文件，并不会被覆盖

Binlog 与redo log 的执行逻辑如下图

其中prepare 与最后的commit 操作，我们称之为两阶段提交（什么是两阶段提交）

查看是否开启了binlog

  
    
    
  
   
     
     
   mysql> show binlog events; #只查看第一个binlog文件的内容
   
     
     
   mysql> show binlog events in 'mysql-bin.000002';#查看指定binlog文件的内容
   
     
     
   mysql> show binary logs; #获取binlog文件列表
   
     
     
   mysql> show master status；#查看当前正在写入的binlog文件

这里吐槽一句，mysql的标准化做得其实也一般，有时候叫binlog，有时候又是log_bin

通过binlog工具查看数据

 
   
   
 
  
    
    
   
    
     
      
     
    mysqlbinlog --no-defaults -vvv DESKTOP-HU0U4FD-bin.000023 
   

注：上图的binlog 格式为row, 记录的是每条sql 的实际数据

通过sql 命令看

 
   
   
 
  
    
    
   
    
     
      
     
    show binlog events in 'DESKTOP-HU0U4FD-bin.000005';

binlog 被称之为归档日志，在mysql 分布式环境中常常用作主从负责的重要依据，有些项目中需要把mysql 数据同步到其他的存储库（es,mongo等）中的时候，也会用到binlog

Bin log 一般有三种格式，row ,statement 和mixed，其中mixed是前两种格式的混合。

format	定义	优点	缺点
statement	记录的是修改sql 语句	日志文件小，节约IO,提高性能	准确性差，对一些系统函数不能准确复制或不能复制
row	记录的是每行实际数据的变更，	准确性强，能准确复制数据的变更，	日志文件大，较大的网络IO,和磁盘IO
mixed	statement 和row模式的混合	准确性强，文件大小适中	有可能发生主从不一致的问题

更新binlog 格式方法

  
    
    
  
   
     
     
   set global binlog_format=ROW

现在一般项目会使用row 作为默认的binlog格式，虽然它所占的空间大，但是它准确，statement 格式下，binlog 保存的就是sql 语句，可能在不同时间段执行的数据不一致

Undo log (回滚日志)

前面说的，redo log 提供了crash-safe 功能；bin 提供了数据存档的功能，课程中其实没有对undo log 做过多的讲解，，那undo log 提供了什么功能呢？

回滚日志可以看这个

https://cloud.tencent.com/developer/article/1497335

https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html#auto_id_13

https://cloud.tencent.com/developer/article/1801920

vlambda博客
学习文章列表