MySQL各种日志详解

MySQL各种日志详解

一：undo-log：撤销日志

Undo即撤销的意思，但咱们通常也习惯称它为回滚日志，在日常开发过程中，如果代码敲错了，一般会习惯性的按下Ctrl+Z撤销，而Undo-log的作用也是如此，但它是用来给MySQL撤销SQL操作的。

当一条写入类型的SQL执行时，都会记录Undo-log日志，会生成相应的反SQL放入到Undo-log中，例如：

• 如果目前是insert插入操作，则生成一个对应的delete操作。
• 如果目前是delete删除操作，InnoDB中会修改隐藏字段deleted_bit=1，则生成改为0的语句。
• 如果目前的update修改操作，比如将姓名从张三改成了李四，那就生成一个从李四改回张三的操作。

当事务中某条SQL执行失败时，MySQL就需要回滚事务中其他执行成功的SQL，此时就会找到这个事务在Undo-log中生成的反SQL，然后将库中的数据改回事务发生前的样子。

然而这些都是方便理解描述的，实际上并不会生成反向的SQL的文件进行储存，因为没有xxx-undo.log文件生成

实际上，InnoDB默认是将Undo-log存储在xx.ibdata共享表数据文件当中，默认采用段的形式存储。

也就是当一个事务尝试写某行表数据时，首先会将旧数据拷贝到xx.ibdata文件中，将表中行数据的隐藏字段：roll_ptr回滚指针会指向xx.ibdata文件中的旧数据，然后再写表上的数据。

那Undo-log究竟在xx.ibdata文件中怎么存储呢？

在共享表数据文件中，有一块区域名为Rollback Segment回滚段，每个回滚段中有1024个Undo-log Segment

每个Undo段可存储一条旧数据，而执行写SQL时，Undo-log就是写入到这些段中。

不过在MySQL5.5版本前，默认只有一个Rollback Segment，而在MySQL5.5版本后，默认有128个回滚段，即支持128*1024条Undo记录同时存在。

1：事务回滚的真正原理

当一个事务需要回滚时，本质上并不会以执行反SQL的模式还原数据，而是直接将roll_ptr回滚指针指向的Undo记录，从xx.ibdata共享表数据文件中拷贝到xx.ibd表数据文件，覆盖掉原本改动过的数据。

即当需要回滚事务时，直接用Undo旧记录覆盖表中修改过的新记录即可

如果是insert操作，由于插入之前这条数据都不存在，那么就不会产生Undo记录，此时回滚时如何删除这条记录呢？

因为插入操作不会产生Undo旧记录，因此隐藏字段中的roll_ptr=null，因此直接用null覆盖插入的新记录即可，这样也就实现了删除数据的效果

2：Undo_log版本链实现的MVCC

Undo-log中记录的旧数据并不仅仅只有一条，一条相同的行数据可能存在多条不同版本的Undo记录，内部会通过roll_ptr回滚指针，组成一个单向链表，而这个链表则被称之为Undo版本链

-- 事务T1：trx_id=1（两次修改同一条数据）
UPDATE zz_users SET user_name = "竹子" WHERE user_id = 1;
UPDATE zz_users SET user_sex = "男" WHERE user_id = 1;

这个前面MVCC都说过，不再赘述

3：Undo_log的内存缓冲区

InnoDB在MySQL启动时，会在内存中构建一个BufferPool，而这个缓冲池主要存放两类东西：

• 一类是数据相关的缓冲，如索引、锁、表数据等
• 一类则是各种日志的缓冲，如Undo、Bin、Redo…等日志。

而当一条写SQL执行时，不会直接去往磁盘中的xx.ibdata文件写数据，而是会写在undo_log_buffer缓冲区中

因为工作线程直接去写磁盘太影响效率了，写进缓冲区后会由后台线程去刷写磁盘。

undo-log & purger线程

当一个事务提交时，Undo的旧记录会不会立马被删除呢？虽然似乎用不上Undo旧记录了，但不会立马删除Undo记录，对于旧记录的删除工作，InnoDB中会有专门的purger线程负责，purger线程内部会维护一个ReadView，它会以此作为判断依据，来决定何时移除Undo记录。

为什么不是事务提交后立马删除Undo记录呢？

因为可能会有其他事务在通过快照，读Undo版本链中的旧数据，直接移除可能会导致其他事务读不到数据，因此删除的工作就交给了purger线程。

3：Undo_log相关参数

3.1：参数说明

3.2：如何使用

修改配置文件（永久生效）

这种方式适合在数据库启动前就确定好参数值，修改后重启数据库，参数设置会永久生效。
找到配置文件：MySQL 的配置文件通常是 my.cnf 或 my.ini，不同操作系统的存放位置可能不同。
Linux：常见位置为 /etc/my.cnf 或 /etc/mysql/my.cnf。
Windows：一般在 MySQL 安装目录下，如 C:\ProgramData\MySQL\MySQL Server X.Y\my.ini（X.Y 是具体版本号）。
编辑配置文件：使用文本编辑器打开配置文件，在 [mysqld] 部分添加或修改相应的 undo_log 参数。
以下是一个示例，展示如何设置 innodb_undo_logs、innodb_undo_tablespaces、innodb_max_undo_log_size 和 innodb_undo_log_truncate 参数：

[mysqld]
# 设置回滚段数量
innodb_undo_logs = 64
# 设置撤销表空间数量
innodb_undo_tablespaces = 3
# 设置单个 undo_log 文件的最大大小为 2GB
innodb_max_undo_log_size = 2147483648
# 允许对 undo_log 进行截断操作
innodb_undo_log_truncate = ON

保存并重启 MySQL 服务
Linux：使用以下命令重启 MySQL 服务。

sudo systemctl restart mysql

Windows：在服务管理器中找到 MySQL 服务，选择 “重启”。

动态修改参数（临时生效）

在数据库运行过程中，可以使用 SQL 语句动态修改某些参数，但这种修改在数据库重启后会失效。

不是所有的参数都支持动态修改，需要查看 MySQL 官方文档确认。

-- 设置回滚段数量
SET GLOBAL innodb_undo_logs = 64;
-- 设置撤销表空间数量（部分版本不支持动态修改）
-- SET GLOBAL innodb_undo_tablespaces = 3; 
-- 设置单个 undo_log 文件的最大大小为 2GB
SET GLOBAL innodb_max_undo_log_size = 2147483648;
-- 允许对 undo_log 进行截断操作
SET GLOBAL innodb_undo_log_truncate = ON;

注意事项

• 使用 SET GLOBAL 语句修改参数时，需要具有 SUPER 权限。
• 对于不支持动态修改的参数，如 innodb_undo_tablespaces，使用 SET GLOBAL 语句会报错。这种情况下只能通过修改配置文件来调整参数。

验证参数设置

可以使用以下 SQL 语句来验证参数是否已经成功设置：

SHOW VARIABLES LIKE 'innodb_undo_logs';
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
SHOW VARIABLES LIKE 'innodb_max_undo_log_size';
SHOW VARIABLES LIKE 'innodb_undo_log_truncate';

执行上述语句后，会显示每个参数的当前值。
通过以上方法，你可以根据数据库的实际情况和业务需求来灵活调整 undo_log 相关参数。

二：redo_log 重做日志

redo-log和undo-log是一对孪生兄弟，因为二者都是Innodb引擎独有的

前面说过多条事务的事务机制和事务恢复机制：

bufferPool刷入磁盘的时候宕机了怎么办

对于这个问题呢实际上并不需要担心，因为前面聊到过redo-log是一种预写式日志，会先记录日志再去更新缓冲区中的数据

所以就算缓冲区的数据未被刷写到磁盘，在MySQL重启时，依旧可以通过redo-log日志重新恢复未落盘的数据，从而确保数据的持久化特性。

记录日志的时候宕机了怎么办？

先明确一点：只要进入bufferPool，就说明已经执行成功了，redo-log就有记录，此时会返回写入成功的提示，即使这时候数据库宕机了也没事，此时只是没有落盘而言，所以MySQL重启后只需要再次落盘即可。

如果在记录日志的时候MySQL宕机了，这代表着SQL都没执行成功，SQL没执行成功的话，MySQL也不会向客户端返回任何信息

因为MySQL一直没返回执行结果，因此会导致客户端连接超时，而一般客户端都会有超时补偿机制的，比如会超时后重试

如果MySQL做了热备/灾备，这个重试的时间足够MySQL重启完成了，因此用户的操作依旧不会丢失（对于补偿机制，在各大数据库连接池中是有实现的）。

没做热备/灾备，这时候记录日志的时候宕机了怎么办？

如果是这样的情况，那就只能说是寄了！！！毕竟MySQL挂了一直不重启，不仅仅当前的SQL会丢失，后续平台上所有的用户操作都会无响应

这属于系统崩溃级别的灾难了，因此只能靠完善系统架构来解决。

1：为何需要redo-log日志

MySQL绝大部分引擎都是是基于磁盘存储数据的，但如若每次读写数据都走磁盘，其效率必然十分低下

因此InnoDB引擎在设计时，当MySQL启动后就会在内存中创建一个BufferPool

运行过程中会将大量操作汇集在内存中进行，比如写入数据时，先写到内存中，然后由后台线程再刷写到磁盘。

虽然使用BufferPool提升了MySQL整体的读写性能，但它是基于内存的，也就意味着随着机器的宕机、重启，其中保存的数据会消失

当一个事务向内存中写入数据后，MySQL突然宕机了，这条未刷写到磁盘的数据会丢失，因此Redo-log应运而生！

因为数据写到内存后有丢失风险，这明显违背了事务ACID原则中的持久性，所以Redo-log的出现就是为了解决该问题

Redo-log是一种预写式日志，即在向内存写入数据前，会先写日志，当后续数据未被刷写到磁盘、MySQL崩溃时，就可以通过日志来恢复数据，确保所有提交的事务都会被持久化。

⚠️ 工作线程执行SQL前，写的Redo-log日志，也是写在了内存中的redo_log_buffer缓冲区。

既然Redo-log日志也是先写内存，那Redo-log有没有丢失的风险呢？这跟Redo-log的刷盘策略有关。

2：redo-log 的刷盘策略

这个前面也提到过了，redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制

innodb_flush_log_at_trx_commit有如下三个选择：

• 0：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。
• 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。
• 2：每当事务提交时，把日志记录放到内核缓冲区，刷写的时机交给OS控制（性能最佳）。

简单来说就是刷盘的时机由innodb_flush_log_at_trx_commit参数来控制，默认是处于第二个级别

也就是每次提交事务时都会刷盘，这也就意味着一个事务执行成功后，相应的Redo-log日志绝对会被刷写到磁盘中，因此无需担心会出现丢失风险。

3：redo-log为何多次一举

先刷写一次Redo-log日志到磁盘，后台线程再根据Redo-log日志把数据落盘，这个动作看似多余，但实际上这样做好处很大：

• 日志比数据先落入磁盘，因此就算MySQL崩溃也可以通过日志恢复数据。
• 写日志时是以追加形式写到末尾，而写数据时则是计算数据位置，随机插入。【顺序写入比随机写入快的多】

因为写日志会比写数据落盘快，因此日志落盘后返回，比数据落盘后返回要快，对于客户端而言，响应时间会更短~

4：redo-log相关参数

为啥redo日志的磁盘文件个数默认是两个？

因为MySQL通过来回写这两个文件的形式记录Redo-log日志，用两个日志文件组成一个“环形”

• write pos：这根指针用来表示当前Redo-log文件写到了哪个位置。
• check point：这根指针表示目前哪些Redo-log记录已经失效且可以被擦除（覆盖）。

其中：

• head -> tail部分表示：日志落盘但数据还未落盘的记录
• tail -> head部分表示：可写部分【写入日志记录的可用空间】

check-point刷盘

当write pos指针追上check point指针时，红色区域就会消失，也就代表Redo-log文件满了，再当MySQL执行写操作时就会被阻塞

因为无法再写入redo-log日志了，所以会触发checkpoint刷盘机制

将redo-log记录对应的事务数据，全部刷写到磁盘中的表数据文件后，阻塞的写事务才能继续执行。

触发checkpoint刷盘机制后，随着数据的落盘，check point指针也会不断的向后移动，红色区域也会不断增长，因此阻塞的写事务才能继续执行。

三：Bin-log：变更日志

Bin-log日志也被称之为二进制日志，作用与Redo-log类似，主要是记录所有对数据库表结构变更和表数据修改的操作

对于select、show这类读操作并不会记录

bin-log是MySQL-Server级别的日志，也就是所有引擎都能用的日志，而redo-log、undo-log都是InnoDB引擎专享的，无法跨引擎生效。

bin-log也由内存日志缓冲区+本地磁盘文件两部分组成，所以写bin-log日志时，也会先写缓冲区，然后由后台线程去刷盘。

1：bin-log的缓冲区

bin-log的缓冲区跟redo-log、undo-log的缓冲区并不同，那两个都位于InnoDB创建的共享BufferPool中

而bin_log_buffer是位于每条线程中的

也就是说，MySQL-Server会给每一条工作线程，都分配一个bin_log_buffer，而并不是放在共享缓冲区中，有两点原因：

1. MySQL设计时要兼容所有引擎，直接将bin-log的缓冲区，设计在线程的工作内存中，这样就能够让所有引擎通用
2. 不同线程/事务之间，由于写的都是自己工作内存中的bin-log缓冲，因此并发执行时也不会冲突！

bin-log的刷盘策略前面也已经说过了：

bin-log日志的刷盘策略则可以通过sync_binlog参数控制，有如下两个选择：

• 0：同上述innodb_flush_log_at_trx_commit参数的2，交给OS控制（默认）。
• 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交事务都会刷盘。

2：文件的外部格式

bin-log的本地日志文件，采用的是追加写的模式，也就是一直向文件末尾写入新的日志记录，当一个日志文件写满后，会创建一个新的bin-log日志文件

每个日志文件的命名为mysql-bin.000001、mysql-bin.000002、mysql-bin.00000x....

show binary logs;

3：文件的内部格式

在bin-log的本地文件中，其中存储的日志记录共有Statment、Row、Mixed三种格式

3.1：statment格式

每一条会对数据库产生变更的SQL语句都会记录到bin-log中。

-- 查询一次用户表数据，如下：
SELECT * FROM zz_users;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 张三      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 李四      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 王五      | 男       | 4321     | 2022-09-16 07:42:21 |
|       4 | 张三      | 女       | 8888     | 2022-09-27 17:22:59 |
|       9 | 李四      | 男       | 9999     | 2022-09-28 22:31:44 |
+---------+-----------+----------+----------+---------------------+

-- 将用户表中所有 ID>3的密码重置
update zz_users set password = "1111" where user_id > 3;

比如上述这个事务执行时，MySQL会将第二条update语句记录在bin-log日志中

但对于select语句则不会记录（在记录SQL时，还会记录一下SQL的上下文信息，如执行时间、事务ID、日志量…）。

这种方式的优势很明显，由于只记录对数据库产生变更操作的SQL，所以不会产生太大的日志量，节约空间

恢复数据时因为数据量小，所以磁盘IO次数少，因此性能会比较不错。

同时做主备等高可用架构时，数据同步也会较小，因此比较节省带宽。

⚠️

但虽然优势不小，但缺点也很明显，即恢复数据、主从同步数据时，有时会出现数据不一致的情况

如SQL中使用了sysdate()、now()这类函数

insert into zz_users values(11,"赵六","男","3333",sysdate());

-- 比如这条插入语句，由于对用户表产生了变更操作，所以会被记录到bin-log中
-- 但当主从架构之间做数据同步时，假设将这条SQL同步到从机上执行，此时问题就来了，sysdate()函数会获取机器的当前时间
-- 但主机和从机执行这条SQL显然不是同一时间
-- 因此就会导致ID=11的这条数据，在主机和从机的用户表中，注册时间会出现不一致。

3.2：Row格式

Row模式中不再记录每条造成变更的SQL语句，而是记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了。

还是以前面的重置密码的例子来说：

-- 将用户表中所有 ID>3的密码重置（ID=4、9的两条数据会被重置）
update zz_users set password = "1111" where user_id > 3;

在这种模式下，就不会记录这条update语句，而是记录发生改变的行数据，即ID=4、9的两条用户数据，会将其更改后的值记录到bin-log日志中。

这种方式因为不记录SQL，而是记录修改后的值，因此有个很大的好处是：当主从同步数据时，复制的是主机上的数据，因此不会出现主从数据不一致的情况。

但缺陷同样很明显，记录的数据量将会大大增加，其磁盘IO、网络带宽开销会很高。

update xxx set password = '111' where id > 3;

# 假设我表里面有1000w条数据，但是我想更新所有的id > 3的数据

# 如果采用的是statment的方式，将会只记录一条数据
update xxx set password = '111' where id > 3;

# 如果采用row格式，将会一下记录很多条内容，将所有满足条件的各记录一条

3.3：Mixed格式

混合模式，即Statment、Row的结合版

因为Statment模式会导致数据出现不一致，而Row模式数据量又会很大，因此Mixed模式结合了两者的优劣势

对于可以复制的SQL采用Statment模式记录，对于无法复制的SQL采用Row记录。

这样即保留了Statment模式的数据量小，又具备Row模式的数据精准性

🎉 Redis的RDB、AOF持久化模式，正好对应MySQL的Statment、Row模式，Redis4.0引入了混合持久化机制，MySQL5.1版本也引入了混合日志模式

4：为啥既有redo-log又有bin-log

Redo-log、Bin-log都是记录更新数据库的操作，但为什么会同时设计两个呢？

这其实跟InnoDB有关，MySQL自己的官方引擎实际上最初是MyISAM，InnoDB是Innobase-Oy公司开发的一款可拔插式引擎

由于InnoDB被MySQL支持后使用频率越来越高，后面MySQL官方才用InnoDB替换了MyISAM作为默认引擎。

MySQL-Server、MyISAM是出自于官方的产品，因此MyISAM中并未设计记录变更操作的日志，记录变更操作由MySQL-Server来通过Bin-log完成。

但因为MyISAM不支持事务，所以MySQL-Server设计的Bin-log无法用于灾难恢复

因此InnoDB在设计时，又重新设计出Redo-log日志，可以利用该日志实现crash-safe灾难恢复能力，确保任何事务提交后数据都不会丢失。

5：redo-log和bin-log的区别

生效范围不同，Redo-log是InnoDB专享的，Bin-log是所有引擎通用的。

写入方式不同，Redo-log是用两个文件循环写，而Bin-log是不断创建新文件追加写。

文件格式不同，Redo-log中记录的都是变更后的数据，而Bin-log会记录变更SQL语句。

使用场景不同，Redo-log主要实现故障情况下的数据恢复，Bin-log则用于数据灾备、同步。

6：删库跑路和数据恢复

6.1：删库后跑路会不会被人发现

如果你在线上真的删库了，基本上是跑不掉的，因为bin-log日志中会记录执行SQL的连接会话信息

同时如果企业搭建了完善的监控系统，会监控服务的网络连接，因此当你删库后，可以顺着bin-log → session → network-connection这条线确定执行删库SQL的IP

如果你还未断开连接，直接通过MySQL的命令就能定位到删库的IP，因此基本上删库了，是可以定位到责任人的！

只有下面这种情况才有可能跑路成功：

项目配备的监控系统不够完善 && 你的连接已经断开 && 你的电脑换了一个局域网 && 时间来到了三天以后

6.2：删库后的数据恢复

跑路肯定是跑不掉的了，误删了数据就要想办法恢复，咋恢复呢？通过日志恢复，那bin-log & redo-log用哪个比较好呢？ bin-log

因为Redo-log采用循环写的方式，一边写会一边擦，里面无法得到完整的数据

而Bin-log是追加写的模式，你不去主动删除磁盘的日志文件，并且磁盘的空间还足够，一般Bin-log日志文件都会在本地，因此当你删库后，可以直接去本地找Bin-log的日志文件，然后拷贝出来一份，再打开最后一个文件，把里面删库的记录手动移除；最后再利用mysqlbinlog工具导出xx.SQL文件，最后执行该SQL文件即可恢复删库前的数据。

7：bin-log相关参数

• log_bin：是否开启bin-log日志，默认ON开启，表示会记录变更DB的操作。
• log_bin_basename：设置bin-log日志的存储目录和文件名前缀，默认为./bin.0000x。
• log_bin_index：设置bin-log索引文件的存储位置，因为本地有多个日志文件，需要用索引来确定目前该操作的日志文件。
• binlog_format：指定bin-log日志记录的存储方式，可选Statment、Row、Mixed。
• max_binlog_size：设置bin-log本地单个文件的最大限制，最多只能调整到1GB。
• binlog_cache_size：设置为每条线程的工作内存，分配多大的bin-log缓冲区。
• sync_binlog：控制bin-log日志的刷盘频率。
• binlog_do_db：设置后，只会收集指定库的bin-log日志，默认所有库都会记录。

8：redo-log的两阶段提交都是因为你

上图可以看出，在⑤，⑩两个阶段会分别写两次redo-log，写一次不行吗？为什么要分成prepare写和commit写

在思考这个问题之前，先思考下如果只写一次的话，那到底先写bin-log还是redo-log呢？

• 先写bin-log，再写redo-log：当事务提交后，先写bin-log成功，结果在写redo-log时断电宕机了，再重启后由于redo-log中没有该事务的日志记录，因此不会恢复该事务提交的数据。但要注意，主从架构中同步数据是使用bin-log来实现的，而宕机前bin-log写入成功了，就代表这个事务提交的数据会被同步到从机，也就意味着从机会比主机多出一条数据。

• 先写redo-log，再写bin-log：当事务提交后，先写redo-log成功，但在写bin-log时宕机了，主节点重启后，会根据redo-log恢复数据，但从机依旧是依赖bin-log来同步数据的，因此从机无法将这个事务提交的数据同步过去，毕竟bin-log中没有撒，最终从机会比主机少一条数据。

🎉 如果redo-log只写一次，那不管谁先写，都有可能造成主从同步数据时的不一致问题出现

为了解决该问题，redo-log就被设计成了两阶段提交模式，设置成两阶段提交后，整个执行过程有三处崩溃点：

• redo-log(prepare)：在写入准备状态的redo记录时宕机，事务还未提交，不会影响一致性。

• bin-log：在写bin记录时崩溃，重启后会根据redo记录中的事务ID，回滚前面已写入的数据。

• redo-log(commit)：在bin-log写入成功后，写redo(commit)记录时崩溃，因为bin-log中已经写入成功了，所以从机也可以同步数据，因此重启时直接再次提交事务，写入一条redo(commit)记录即可。

通过这种两阶段提交的方案，就能够确保redo-log、bin-log两者的日志数据是相同的

bin-log中有的主机再恢复，如果bin-log没有则直接回滚主机上写入的数据，确保整个数据库系统的数据一致性。

为什么bin-log又被叫做二进制日志呢？

因为记录日志时，MySQL写入的是二进制数据，而并非字符数据，也就意味着直接用cat/vim这类工具是无法打开的

必须要通过MySQL提供的mysqlbinlog工具解析查看。

四：Error-log：错误日志

从名字都能得知它是用于记录MySQL报错信息的

其中涵盖了MySQL-Server的启动、停止运行的时间，以及报错的诊断信息，也包括了错误、警告和提示等多个级别的日志详情。

通过错误日志，一方面可以用来监控MySQL的运行状态，便于预防故障、发现故障

同时也可以在出现问题时，用来辅助排查问题、修复故障，因为MySQL-Server的错误日志是默认开启的，并且无法手动关闭！

一般来说，error-log日志文件默认是在MySQL安装目录下的data文件夹中，

如果你想要改变位置，哪也可以通过log-error这个参数，来手动指定保存的位置与文件名。

🎉 如果你不清楚错误日志的位置，也可以通过SHOW VARIABLES LIKE ‘log_error’;命令来查看。

在MySQL故障的情况下，打开error-log文件，然后搜索Error、Waiting级别的日志记录，然后参考诊断信息即可。

五：Slow-log：慢查询日志

当一条SQL执行的时间超过规定的阈值后，那么这些耗时的SQL就会被记录在慢查询日志中

当线下出现响应缓慢的问题时，可以直接通过查看慢查询日志定位问题

定位到产生问题的SQL后，再用explain这类工具去生成SQL的执行计划

然后根据生成的执行计划来判断为什么耗时长，是由于没走索引，还是索引失效等情况导致的。

不过对于慢查询SQL的监控，MySQL默认是关闭的，也就是说MySQL默认不会记录慢查询日志

因为为了后续线上问题好排查，项目上线前一定要记得开启！

• slow_query_log：设置是否开启慢查询日志，默认OFF关闭。
• slow_query_log_file：指定慢查询日志的存储目录及文件名。

可以通过这两个参数来开启慢查询日志，如果不设置存储目录，默认放在MySQL的具体库的目录下。

一般来说，MySQL慢查询日志可以放置在MySQL的data目录下。

默认情况下，MySQL的data目录位于MySQL安装目录的data文件夹中。

在该目录下创建一个名为slow_query.log的文件，并将慢查询日志写入此文件中。

当开启慢查询日志的监控后，可以通过设置long_query_time参数，来指定查询SQL的阈值：

-- 其默认单位是秒，因此如果要指定更细粒度的时间，可以通过0.01这种形式设置，0.01表示10ms。
-- 默认是10s, 即执行时间超过10s的查询SQL才会记录到慢查询日志中。
set global long_query_time = 1;

对于阈值的设置，并不是随咱们率性而为，这个参数一定要设置合理！

如果大量业务SQL执行时都会超出该阈值时长，那最终会导致MySQL十分频繁的往慢查询日志中写数据。

⚠️ 慢查询日志在内存中是没有缓冲区的，也就意味着每次记录慢查询SQL，都必须触发磁盘IO来完成

因此阈值设的太小，容易使得MySQL性能下降；如果设的太大，又会导致无法检测到问题SQL，因此该值一定要设置一个合理值。

这个值设成多大合理呢？可以先开启general log，观察后实际的业务情况后再决定。

general log即查询日志，MySQL会向其中写入所有收到的查询命令，如select、show等，同时要注意：无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。对于该日志可以通过下述参数开启：

• general_log：是否开启查询日志，默认OFF关闭。
• general_log_file：指定查询日志的存储路径和文件名（默认在库的目录下，主机名+.log）。

1. 项目测试阶段，可以先开启查询日志，然后压测所有业务
2. 紧接着再分析日志中SQL的平均耗时
3. 再根据正常的SQL执行时间，设置一个偏大的慢查询阈值即可

🎉 如果项目规模较大，直接设置一个大概值，然后上灰度发布，走正式的运营场景效果会更佳

六：Relay-log：中继日志

relay log在单库中是见不到的，该类型的日志仅存在主从架构中的从机上

主从架构中的从机，其数据基本上都是复制主机bin-log日志同步过来的，而从主机复制过来的bin-log数据放在哪儿呢？也就是放在relay-log日志中

中继日志的作用就跟它的名字一样，仅仅只是作为主从同步数据的“中转站”。

当主机的增量数据被复制到中继日志后，从机的线程会不断从relay-log日志中读取数据并更新自身的数据

relay-log的结构和bin-log一模一样，同样存在一个xx-relaybin.index索引文件，以及多个xx-relaybin.00001....数据文件。