MySQL

MySQL 基础

关系型数据库

建立在关系模型上的数据库(一对一，一对多，多对多)

MySQL 字段类型

• 数值类型：整型(tinyint, smallint, mediumint, int, bigint)，浮点型(float, double)，定点型(decimal)
• 字符串类型：char, varchar, tinytext, text, mediumtext, longtext, tinyblob, blob, mediumblob, longblob
• 日期类型：year, time, date, datetime, timestamp

char vs varchar

• char 是定长字符串，varchar 是变长字符串。

• char 在存储时会在右边填充空格以达到指定的长度，检索时去掉空格，varchar 在存储时需要额外的 1 或 2 个字节记录字符串的长度，检索时不需要处理

• char 更适合存储长度较短或长度差不多的字符串，varchar 适合存储长度不确定或差异大的字符串

datetime vs timestamp

• datetime 没有时区信息，timestamp 与时区有关，会根据时区改变时间

• datetime 需要 8 个字节，timestamp4 个字节

NULL vs ''

• NULL 代表一个不确定的值, 就算是两个 NULL, 它俩也不一定相等。例如，SELECT NULL=NULL 的结果为 false，但是在我们使用 DISTINCT, GROUP BY, ORDER BY 时, NULL 又被认为是相等的。
• '' 的长度是 0，是不占用空间的，而 NULL 是需要占用空间的。
• NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
• 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而 '' 是可以使用这些比较运算符的

MySQL 基础架构

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存(MySQL 8.0 版本后移除，因为这个功能不太实用)。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行
• 执行器： 执行语句，然后从存储引擎返回数据

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存(MySQL 8.0 版本后移除，因为这个功能不太实用)。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

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

连接器

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

查询缓存(8.0 后移除)

查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

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

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

分析器

分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

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

优化器

化器的作用就是它认为的最优的执行方案去执行(有时候可能也不是最优)

执行器

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

执行流程

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

1. 检查权限，检查缓存(8.0 前)
2. 词法分析，提取 SQL 语句的关键元素，语法分析，是否有语法错误
3. 优化器，选择最优的方案准备执行
4. 权限校验，调用数据库引擎执行结果

update tb_student A set A.age='19' where A.name=' 张三 ';

执行更新要记录日志，日志模块 binlog(归档日志)，InnoDB 引擎还自带 redo log(重做日志)

1. 查询张三的数据
2. 拿到查询的语句，把 age 改为 19，调用引擎写入这行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成，随时可以提交
3. 执行器收到通知后记录 binlog，调用引擎接口，提交 redo log 为提交状态

为什么要两个日志：redo log 来支持事务

为什么 redo log 要引入 prepare 预提交状态：

• 先写 redo log 直接提交，然后写 binlog：写完 redo log 后机器挂了，binlog 日志没写入，重启后，使用 redo log 恢复数据，binlog 没有该记录，后续备份会丢失该记录，主从同步也会丢失
• 先写 binlog，后写 redo log：写完 binlog 后机器异常重启，redo log 不会恢复，而 binlog 有记录，会数据不一致
• 两个阶段，redo log 处于 prepare 状态，写完 binlog 后，若异常重启，MySQL 判断 redo log 是否完整，则提交，如果 redo log 处于 prepare 状态，先判断 binlog 是否完整，完整则提交 redo log，不完整就回滚数据

总结

• MySQL 主要分为 Server 层和引擎层，Server 层主要包括 连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块(binlog)，这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
• 引擎层是插件式的，目前主要包括，MyISAM, InnoDB, Memory 等。
• 查询语句 的执行流程如下：权限校验(如果命中缓存) ---> 查询缓存 ---> 分析器 ---> 优化器 ---> 权限校验 ---> 执行器 ---> 引擎
• 更新语句 执行流程如下：分析器 ---> 权限校验 ---> 执行器 ---> 引擎 ---> redo log(prepare 状态)---> binlog ---> redo log(commit 状态)

MySQL 存储引擎

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MySQL 存储引擎架构

采用插件式架构，支持多种存储引擎，基于表而不是数据库

MyISAM vs InnoDB

• InnoDB 支持 行级别的锁粒度，MyISAM 不支持，只支持 表级别的锁粒度。
• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交和回滚事务的能力，InnoDB 默认使用的 REPEATABLE-READ(可重读)隔离级别是可以解决幻读问题的(基于 MVCC 和 Next-Key Lock)
• MyISAM 不支持外键，而 InnoDB 支持。
• MyISAM 不支持 MVCC，而 InnoDB 支持。
• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样，InnoDB 中数据文件本身就是索引文件，B+树的叶节点保存了完整的数据，而 MyISAM 索引文件和数据文件分离。
• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持，依赖于 redo log。
• InnoDB 的性能比 MyISAM 更强大

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后 以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”，而其余的索引都作为 辅助索引 ，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

InnoDB 的 B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而 MyISAM 的 B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

MySQL 索引

介绍

索引是用于快速查询和检索数据的数据结构，常见的索引结构：B 树，B+树，Hash，红黑树。

InnoDB 和 MyISAM 都使用 B+树作为索引结构

优缺点

• 优点：加快检索速度，创建唯一性索引，保证每一行数据的唯一性
• 缺点：创建和维护索引 耗费时间，索引需要物理文件存储，耗费空间

Hash 索引

使用键值对存储，冲突一般使用链地址法/红黑树，速度快，但不支持范围查询/排序，每次 IO 只能取一个

MySQL 的 InnoDB 存储引擎不直接支持常规的哈希索引，但是，InnoDB 存储引擎中存在一种特殊的“自适应哈希索引”（Adaptive Hash Index），自适应哈希索引并不是传统意义上的纯哈希索引，而是结合了 B+Tree 和哈希索引的特点，以便更好地适应实际应用中的数据访问模式和性能需求。自适应哈希索引的每个哈希桶实际上是一个小型的 B+Tree 结构。这个 B+Tree 结构可以存储多个键值对，而不仅仅是一个键。这有助于减少哈希冲突链的长度，提高了索引的效率。关于 Adaptive Hash Index 的详细介绍，可以查看 MySQL 各种“Buffer”之 Adaptive Hash Index

在 MySQL 运行的过程中，如果 InnoDB 发现，有很多寻路很长（比如 B+树层数太多、回表次数多等情况）的 SQL，并且有很多 SQL 会命中相同的页面（Page）的话，InnoDB 会在自己的内存缓冲区（Buffer Pool）里，开辟一块区域，建立自适应哈希索引（Adaptive Hash Index，AHI），以加速查询。

二叉查找树

• 左节点 < 根节点 < 右节点
• 查询效率可能退化为 O(N)

平衡二叉树(AVL)

• 二叉查找树 + 左右子树高度差不超过 1
• 查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)

B 树 和 B+树

B 树也称 B-树, 全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体

• B 树的所有节点 既存放键(key) 也存放数据(data)，而 B+树只 有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是 独立 的; B+树的叶子节点是 双向链表。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

索引分类

按照底层存储方式角度划分：

• 聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
• 非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

• 主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
• 普通索引：仅加速查询。
• 唯一索引：加速查询 + 列值唯一（可以有 NULL）。
• 覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
• 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。注意，对于 > =、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

MySQL 事务

• 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保 动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）：执行 事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立 的；
• 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务问题

脏读

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

丢失修改

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

不可重复读

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读 vs 幻读

• 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中 某些记录的值被修改；
• 幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现 查到的记录增加了。

并发事务控制

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

事务隔离级别

MySQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读) ：对同一字段的 多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读：由 MVCC 机制来保证不出现幻读。
• 当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

解决幻读：

• 将事务隔离级别调整为 SERIALIZABLE 。

• 在可重复读的事务级别下，给事务操作的这张表添加表锁。

• 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock）

MySQL 锁

行级锁注意

InnoDB 的 行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE 条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

行锁分类

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
• 临键锁（Next-Key Lock）：Record Lock + Gap Lock，锁定一个范围，包含记录本身，主要目的是为了 解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录 ，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁和排它锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

	S 锁	X 锁
S 锁	不冲突	冲突
X 锁	冲突	冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁

如果需要 用到表锁的话，如何判断表中的记录没有行锁 呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

意向锁和共享锁和排它锁互斥（这里指的是 表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

	IS 锁	IX 锁
S 锁	兼容	互斥
X 锁	互斥	互斥

当前读和快照读

快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

• 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
• 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

MySQL 存储 IP 地址

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON()：把 ip 转为无符号整型 (4-8 位)
• INET_NTOA() : 把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

MySQL 日志

• 错误日志（error log） ：对 MySQL 的启动、运行、关闭过程进行了记录。
• 二进制日志（binary log，binlog） ：主要记录的是更改数据库数据的 SQL 语句。
• 一般查询日志（general query log） ：已建立连接的客户端发送给 MySQL 服务器的所有 SQL 记录，因为 SQL 的量比较大，默认是不开启的，也不建议开启。
• 慢查询日志（slow query log） ：执行时间超过 long_query_time秒钟的查询，解决 SQL 慢查询问题的时候会用到。
• 事务日志(redo log 和 undo log) ：redo log 是重做日志，undo log 是回滚日志。
• 中继日志(relay log) ：relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多。不过，relay log 针对的是主从复制中的从库。
• DDL 日志(metadata log) ：DDL 语句执行的元数据操作

慢日志

慢查询日志记录了执行时间超过 long_query_time（默认是 10s，通常设置为1s）的所有查询语句，在解决 SQL 慢查询（SQL 执行时间过长）问题的时候经常会用到。

找到慢 SQL 是优化 SQL 语句性能的第一步，然后再用EXPLAIN 命令可以对慢 SQL 进行分析，获取执行计划的相关信息。

你可以通过 show variables like "slow_query_log";命令来查看慢查询日志是否开启，默认是关闭的。

redo log

redo logo 是 InnoDB 独有的，让 MySQL 有了崩溃恢复能力

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

理想情况，事务一提交就会进行刷盘操作，但实际上，刷盘的时机是根据策略来进行的。

小贴士：每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

刷盘时机

InnoDB 将 redo log 刷到磁盘上有几种情况：

1. 事务提交：当事务提交时，log buffer 里的 redo log 会被刷新到磁盘（可以通过 innodb_flush_log_at_trx_commit 参数控制，后文会提到）。
2. log buffer 空间不足时：log buffer 中缓存的 redo log 已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
3. 事务日志缓冲区满：InnoDB 使用一个事务日志缓冲区（transaction log buffer）来暂时存储事务的重做日志条目。当缓冲区满时，会触发日志的刷新，将日志写入磁盘。
4. Checkpoint（检查点）：InnoDB 定期会执行检查点操作，将内存中的脏数据（已修改但尚未写入磁盘的数据）刷新到磁盘，并且会将相应的重做日志一同刷新，以确保数据的一致性。
5. 后台刷新线程：InnoDB 启动了一个后台线程，负责周期性（每隔 1 秒）地将脏页（已修改但尚未写入磁盘的数据页）刷新到磁盘，并将相关的重做日志一同刷新。
6. 正常关闭服务器：MySQL 关闭的时候，redo log 都会刷入到磁盘里去。

为什么使用 redo log 刷盘，不使用数据页刷盘？

实际上，数据页大小是 16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

binlog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID = 2 这一行的 c 字段加 1”，属于 MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

那 binlog 到底是用来干嘛的？

可以说 MySQL 数据库的 数据备份、主备、主主、主从 都离不开 binlog，需要依靠 binlog 来同步数据，保证数据一致性。

binlog 日志有三种格式，可以通过 binlog_format 参数指定。

• statement： SQL 原文，如 update T set update_time=now() where id=1，可能导致数据不一致 now()
• row：记录具体值和原始值，如 update_time=1627112756247
• mixed：混合，判断这条 SQL 语句是否可能引起数据不一致，如果是，就用 row 格式，否则就用 statement 格式。

写入时机

binlog 的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

因为一个事务的 binlog 不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为 binlog cache。

我们可以通过 binlog_cache_size 参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

binlog 日志刷盘流程如下

两阶段提交

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了 MySQL 集群架构的数据一致性。

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录 redo log 与 binlog 两块日志，以基本的事务为单位，redo log 在事务执行过程中 可以不断写入，而 binlog 只有在 提交事务时 才写入，所以 redo log 与 binlog 的写入时机不一样。

出现问题

我们以 update 语句为例，假设 id=2 的记录，字段 c 值是 0，把字段 c 值更新成 1，SQL 语句为 update T set c=1 where id=2。

假设执行过程中写完 redo log 日志后，binlog 日志写期间发生了异常，会出现什么情况呢？

由于 binlog 没写完就异常，这时候 binlog 里面没有对应的修改记录。因此，之后用 binlog 日志恢复数据时，就会少这一次更新，恢复出来的这一行 c 值是 0，而原库因为 redo log 日志恢复，这一行 c 值是 1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用 两阶段提交 方案。

原理很简单，将 redo log 的写入拆成了两个步骤 prepare 和 commit，这就是 两阶段提交。

使用 两阶段提交 后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log 日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。

再看一个场景，redo log 设置 commit 阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然 redo log 是处于 prepare 阶段，但是能通过事务 id 找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

undo log

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行 回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

总结

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的 持久性，使用 undo log(回滚日志) 来保证事务的 原子性。

MySQL 数据库的 数据备份、主备、主主、主从 都离不开 binlog，需要依靠 binlog 来同步数据，保证数据一致性。

MVCC

MVCC(多版本并发控制)，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性，通过维护多个版本的数据来实现。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

Explain

explain + select 查询语句

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息