【MySQL面渣逆袭 | 第一篇】MySQL的存储引擎和日志

1.说一下MySQL的基础架构

MySQL的逻辑架构图主要分三层：

客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端或者服务都有类似的结构，比如连接处理、授权认证等等。

Server层：MySQL大多数核心服务都在Server层，包括查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能都在这部分实现：存储过程、触发器、视图等等。

存储引擎层：存储引擎负责MySQL数据的存储和提取。Server层通过API与存储引擎层通信，这些接口屏蔽了不同存储引擎之间的差异。

2.一条SQL查询语句在MySQL中是如何执行的？ 

1.先判断该语句是否有权限，没有权限直接返回错误信息，有权限就会先去查询缓存。

2.如果没有缓存，分析器进行语法分析，提取SQL中的select等关键字，然后判断sql是否有语法错误，比如关键字是否正确等等。

3.语法解析后，MySQL服务器会对查询语句进行优化，确定执行方案。

4.完成查询优化后，按照执行计划调用数据库引擎接口，返回执行结果。

3.InnoDB和MyISAM的区别

1.事务支持：InnoDB支持完整的 ACID 事务（原子性、一致性、隔离性、持久性），适合需要高可靠性和数据一致性的场景（如支付系统），MyISAM不支持事务，无法回滚操作，仅保证基本的数据完整性。

2.锁机制：InnoDB支持行级锁，仅锁定需要修改的行，适合高并发写入场景，MyISAM仅支持表级锁，任何写操作都会锁定表，读操作也会阻塞写操作，适合读多写少的场景。

3.外键约束：InnoDB支持 外键约束（Foreign Key），通过外键维护表间的关联关系，确保数据完整性，MyISAM不支持外键，表间关联需在应用层实现。

 4.InnoDB具有 崩溃安全恢复（Crash-Safe Recovery） 能力，通过 redo log 和 undo log 保证数据一致性，意外宕机后能自动恢复，MyISAM崩溃后可能数据损坏，需手动修复。

4.MySQL日志有哪些，分别介绍一下作用

错误⽇志（error log）：错误⽇志⽂件对 MySQL 的启动、运⾏、关闭过程进⾏了记录，能帮助 定位 MySQL 问题。

慢查询⽇志（slow query log）：慢查询⽇志是⽤来记录执⾏时间超过 long_query_time 这个变 量定义的时长的查询语句。通过慢查询⽇志，可以查找出哪些查询语句的执⾏效率很低，以便进⾏ 优化。

⼀般查询⽇志（general log）：⼀般查询⽇志记录了所有对 MySQL 数据库请求的信息，⽆论请 求是否正确执⾏。

⼆进制⽇志（bin log）：关于⼆进制⽇志，它记录了数据库所有执⾏的 DDL 和 DML 语句（除 了数据查询语句 select、show 等），以事件形式记录并保存在⼆进制⽂件中。

还有两个 InnoDB 存储引擎特有的⽇志⽂件：

重做⽇志（redo log）：重做⽇志⾄关重要，因为它们记录了对于 InnoDB 存储引擎的事务⽇志。

回滚⽇志（undo log）：回滚⽇志同样也是 InnoDB 引擎提供的⽇志，顾名思义，回滚⽇志的作 ⽤就是对数据进⾏回滚。当事务对数据库进⾏修改，InnoDB 引擎不仅会记录 redo log，还会生成对应的 undo log ⽇志；如果事务执⾏失败或调⽤了 rollback，导致事务需要回滚，就可以用 undo log 中的信息将数据回滚到修改之前的样⼦。

5.bin log与redo log有什么区别？

1.bin log会记录所有与数据库相关的日志记录，包括InnoDB、MyISAM等存储引擎，redo log只记录InnoDB存储引擎的日志。

2.记录的内容不同，bin log记录的是关于一个事务的具体操作内容，即逻辑日志，而redo log记录的是对每一页修改的物理情况。

3.写入方式不同，redo log是循环写入和擦除，bin log是追加写入，不会覆盖。

如果对数据库日志感兴趣，可以看一下我之前写的一篇博客：数据库日志（binlog、redo log、undo log）-CSDN博客

6.一条更新语句怎么执行了解吗？

一条更新语句是Server层和引擎层配合完成的，数据除了要写入表中，还要记录相应的日志。

1. 执⾏器先找引擎获取 ID=2 这⼀⾏。ID 是主键，存储引擎检索数据，找到这⼀⾏。如果 ID=2 这⼀⾏所在的数据页本来就在内存中，就直接返回给执⾏器；否则，需要先从磁盘读⼊内存，然 后再返回。

2. 执⾏器拿到引擎给的⾏数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的⼀⾏数 据，再调⽤引擎接⼜写⼊这⾏新数据。

3. 引擎将这⾏新数据更新到内存中，同时将这个更新操作记录到 redo log ⾥⾯，此时 redo log 处 于 prepare 状态。然后告知执⾏器执⾏完成了，随时可以提交事务。

4. 执⾏器⽣成这个操作的 binlog，并把 binlog 写⼊磁盘。

5. 执⾏器调⽤引擎的提交事务接⼜，引擎把刚刚写⼊的 redo log 改成提交（commit）状态，更新 完成 

从上图可以看出，MySQL 在执⾏更新语句的时候，在服务层进⾏语句的解析和执⾏，在引擎层进⾏数 据的提取和存储；同时在服务层对 binlog 进⾏写⼊，在 InnoDB 内进⾏ redo log 的写⼊。

7.redo log怎么刷入磁盘的知道吗？

redo log的写入不是直接落在磁盘上的，而是在内存中设置了一片名为 redo log buffer的连续内存空间，也就是redo日志缓冲区。

什么时候会刷入磁盘？

1.log buffer空间不足时

log buffer大小是有限的，如果不停的往这个有限⼤⼩的 log buffer ⾥塞⼊⽇志，很快它就会被填 满。如果当前写⼊ log buffer 的 redo ⽇志量已经占满了 log buffer 总容量的⼤约⼀半左右，就需要 把这些⽇志刷新到磁盘上。

2.事务提交时

在事务提交时，为了保证持久性，会把 log buffer 中的⽇志全部刷到磁盘。注意，这时候，除了本事 务的，可能还会刷⼊其它事务的⽇志。

3.后台线程输入

有⼀个后台线程，⼤约每秒都会刷新⼀次 log buffer 中的 正常关闭服务器时 触发 checkpoint 规则 redo log 到磁盘。

4.正常关闭服务器时

5.触发checkpoint规则时

 redo log的写⼊方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置： write pos 是当前记录的位置，⼀边写⼀边后移，写到第 3 号⽂件末尾后就回到 0 号⽂件开 头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。

当write_pos追上 checkpoint 时，表⽰ redo log ⽇志已经写满。这时候就不能接着往⾥写数据了， 需要执⾏ checkpoint 规则腾出可写空间。

所谓的checkpoint 规则，就是 checkpoint 触发后，将 buffer 中⽇志页都刷到磁盘。