慢查询

解决慢SQL的路径：

MySQL 有一个慢查询日志，会帮我们记录执行耗时超过n秒的 SQL 语句，可以通过这个慢查询日志，发现慢SQL.

慢查询日志默认是关闭状态，可以通过以下命令查看慢查询日志的开关状态和慢查询日志路径：

可以将参数设置为on，或者修改配置文件my.cnf来开启慢查询日志，如：

[mysqld]
slow_query_log= ON #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/my-slow.log  #慢查询日志的目录和文件名信息
long_query_time=2 #设置慢査询的阈值为2秒，超出此设定值的SQL即被记录到慢查询日志

在我们想排查接口响应慢的原因的时候，可以考虑把慢査询日志打开，看下接口响应慢的原因是不是慢SQL导致的，如果是的话，就可以针对日志中SQL进行分析和调优了。

接下来，为了分析查询执行慢的原因，需要使用explain来查看执行计划。

逐列解释：

table

table 显示的是这一行的数据是关于哪张表的，上述内容中显示的表名就是 user。

type

这是重要的列，显示查询扫描方式使用了何种类型，类型还是蛮多的，常见扫描类型的执行效率从高到低的顺序为：

const (结果只有一条的主键或唯一索引扫描)：使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。
eq_ref (唯一索引扫描)：使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
ref (非唯一索引扫描)：表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
range (索引范围扫描)：表示采用了索引范围扫描，一般在 where 子句中使用 <、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
index (全索引扫描)：对二级索引进行全扫描，性能跟全表扫描差不多。
All (全表扫描)：全表数据扫描，性能最差，应该尽量避免，上面的示例就进行了全表扫描。

key

key 表示实际使用的索引。如果为 Null，则没有使用索引，这种情况也是尤其需要注意的。

rows

rows 表明 SQL 返回请求数据的行数，这一行非常重要，返回的内容中 SQL 遍历了 9975686 行，其实也证明了这条 SQL 遍历了一张表。

extra

关于 extra，我列两个需要注意的状态，因为这样的状态是会对性能产生不良的影响，意味着查询需要优化了。

Using filesort: 表示SQL 需要进行额外的步骤来发现如何对返回的行排序。它会根据连接类型、存储排序键值和匹配条件的全部行记录进行排序。
Using temporary: 表示MySQL 需要创建一个临时表来存储结果，非常消耗性能。

还可以使用 mysqldumpslow 这种自带的解析工具进行对慢查询的各种操作，比如：

-s ORDER : 按照指定的方式排序结果
- t: 按查询时间排序
- l: 按锁定时间排序
- r: 按返回的行数排序
- c: 按执行次数排序
- at: 按平均查询时间排序
- al: 按平均锁定时间排序
- ar: 按平均返回行数排序
-t N: 只显示前 N 条查询
-g PATTERN: 只显示包含特定模式的查询
-a: 不要将数字抽象为 N，字符串抽象为 S
-n N: 抽象数字，保留小数点后 N 位
-r: 反转排序顺序（默认是降序，使用 -r 变为升序）

例如说，显示执行时间最长的 10 条查询就是这样的：

1	mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

至于如何优化sql，那就要放到优化篇去讲了。

undolog

事务回滚
ReadView + undo log 实现 MVCC（多版本并发控制）

在进行增删改操作时，MySQL会隐式开启事务执行这些语句。因此，如果我们每次在事务执行过程中记录下回滚时需要的信息到一个日志里，那么在事务执行中途发生了MySQL 崩溃后，就不用担心无法回滚到事务之前的数据，我们可以通过这个日志回滚到事务之前的数据。实现这一机制就是 undo log（回滚日志），它保证了事务的 ACID 特性中的原子性（Atomicity）。

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。如下图:

回滚具体如何操作呢？也很简单，把插入回滚成删除，删除回滚成插入，更新回滚为更新为旧值即可。

然而，针对 delete 操作和 update 操作会有一些特殊的处理：

delete操作实际上不会立即直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
update分为两种情况:update的列是否是主键列：

·如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。

·如果是主键列，update分两部执行:先删除该行，再插入一行目标行。

另外，undo log 还有一个作用，通过 ReadView+ undo log 实现 MVCC(多版本并发控制)。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id:

通过 trx_id 可以知道该记录是被哪个事务修改的;
通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链;

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读(普通 select 语句)是通过 ReadView + undo log 来实现的，它们的区别在于创建 Read View 的时机不同:

[读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同-条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
[可重复读|隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列(trx_id 和roll_pointer)」的比对，如果不满足可见性，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC(多版本并发控制)。

redolog

Why redolog？

crash-safe
将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能

我们知道为了提高数据库的IO性能，在存储引擎层有Buffer Pool这么个玩意儿作为缓存：

它的作用是：

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页(该页的内存数据和磁盘上的数据已经不一致)，为了减少磁盘I/0，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool缓存页存储的内容有哪些呢？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

那么问题来了，Buffer Pool 是基于内存的，而内存总是不可靠。万一断电重启，还没来得及落盘的脏页数据就会丢失，包括undo页。因此，buffer pool 中对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。

于是当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存(同时标记为脏页)，然后将本次对这个页的修改以redo log 的形式记录下来，这个时候更新就算完成了；

后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool的脏页刷新到磁盘里，这就是WAL(Write-Ahead Logging)技术：MySQL的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

什么是redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 Y 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘，节省时间和性能。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据redo log 的内容，将所有数据恢复到最新的状态。

redo log还有一个关于写入性能提升的优势：

Redo vs Undo

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

redo log 记录了此次事务「修改后」的数据状态，记录的是更新之后的值，主要用于事务崩溃恢复，保证事务的持久性。
undo log 记录了此次事务「修改前」的数据状态，记录的是更新之前的值，:主要用于事务回滚，保证事务的原子性。

事务提交之前发生了崩溃(这里的崩溃不是宕机崩溃，而是事务执行错误)，重启后会通过 undo log 回滚事务。

事务提交之后发生了崩溃(这里的崩溃是宕机崩溃)，重启后会通过 redo log 恢复事务，如下图：

How redo log？

redolog是直接写入磁盘的嘛？不是的，实际上还是要先写入到自己的缓存redo log buffer，后续再进行持久化。

那么，redo log 什么时候进行刷盘呢？

主要有下面几个时机:

MySQL正常关闭时;
当 redo log buffer 中记录的写入量大于其内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1秒，将redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘(这个策略可由innodb_flush_log_at_trx_commit 参数控制)

innodb_flush_log_at_trx_commit ？

单独执行一个更新语句的时候，InnoDB 引擎会自己启动一个事务，在执行更新语句的过程中，生成的redo log 先写入到 redo log bufer 中，然后等事务提交的时候，再将缓存在 redo log buffer 中的 redolog 按组的方式「顺序写」到磁盘。

上面这种 redo log 刷盘时机是在事务提交的时候，这个默认的行为。

除此之外，InnoDB 还提供了另外两种策略，由参数innodb_flush_log_at_trx_commit 参数控制，可取的值有:0、1、2，默认值为 1，这三个值分别代表的策略如下:

当设置该参数为0时，表示每次事务提交时，还是将redo log 留在 redo log buffer 中，该模式下在事务提交时不会主动触发写入磁盘的操作。

当设置该参数为1时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL异常重启之后数据不会丢失。

当设置该参数为2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redolog 文件，注意写入到「redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个Page Cache(如果你想了解 Page Cache，可以看这篇区)，Page Cache 是专门用来缓存文件数据的，所以写入「redo log文件」意味着写入到了操作系统的文件缓存。

那么0和2的持久化时机是什么？

针对参数0:会把缓存在 redo log buffer 中的 redo log ，通过调用 write()写到操作系统的 PageCache，然后调用 fsync()持久化到磁盘。所以参数为0的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失；

针对参数 2:调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为2 的策略，较取值为0情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

说白了就是，012三个人跑步，路线是redo log buffer—-操作系统的pagecache—-磁盘。1直接一步到位速通，2要顿一下再到终点，0一开始直接不动。

因此从安全性上讲，1>2>0;

从性能上讲，0>2>1.

redo log写满了怎么办？

redo log实际上由重做日志文件组组成，采用循环写的方法记录操作。

我们知道 redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，那么如果随着系统运行，Buffer Poo的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们擦除这些旧记录，以腾出空间记录新的更新操作。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示 redo log 当前记录写到的位置用 checkpoint 表示当前要擦除的位置，如下图:

图中的:

write pos 和 checkpoint 的移动都是顺时针方向;
write pos ~ checkpoint 之间的部分(图中的红色部分)，用来记录新的更新操作;
check point ~ write pos 之间的部分(图中蓝色部分):待落盘的脏数据页记录;

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL不能再执行新的更新操也就是说 MySQL 会被阻塞(因此针对并发量大的系统，适当设置redo log 的文件大小非常重要)，此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动(图中顺时针)，然后 MySQL恢复正常运行继续执行新的更新操作。

所以，一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

1、顺序写代表，redolog 刷盘是用顺序 io 刷盘的

2、循环写，是说 redolog 有 2个文件，2个文件满了，会从第一个文件开始写，循环一直写

binlog

不同于 undo 和 redo，binlog由Server层生成。binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT和 SHOW 操作。

三种格式

binlog 有 3种格式类型，分别是 STATEMENT(默认格式)、ROW、MIXED，区别如下：

STATEMENT:每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现。

缺陷:STATEMENT 有动态函数的问题，比如用了 uuid 或者 now 这些函数，在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致。

ROW:记录行数据最终被修改成什么样了，不会出现 STATEMENT 下动态函数的问题。

缺陷:但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句。

MIXED:包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式。

Redo vs Bin

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存了所有对数据库的更新操作，可以用来恢复数据库某个时刻的数据或者全量恢复数据库数据。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存的是 Innodb 存储引擎对数据页所做的修改操作，用来恢复因中途 MySQL 断电丢失的脏页数据。

崩溃恢复时就使用redo log，因为其可以在innodb层中记录哪些脏页没有被刷盘，相比于binlog粒度更细不用重新执行sql；

重建数据库时使用binlog，因为redo log采用的是循环写的方式，会覆盖以前的日志，而binlog不会。

两阶段提交

为什么需要两阶段提交？

两阶段提交是为了保证 redo log 和 binlog 逻辑一致，从而保证主从复制的时候不会出现数据不一致的问题。

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，比如在主从复制的场景下，如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入磁盘，这时候主库是最新的数据，而从库是旧数据，这样就造成两份日志之间的逻辑不一致。

Binlog 是在最后事务提交阶段才写入的，是一次性的。而 redo log 是在事务过程中不断写。redo log 写磁盘是在 binlog 写入磁盘之前完成的。也就是说，当 binlog 写磁盘时，已完成 redo log 写。

两阶段提交过程？

两阶段提交把事务的提交拆分成了2个阶段，分别是准备阶段和提交阶段。

准备阶段会将 redo log 状态设置为 prepare 状态，然后将 redo log 刷入磁盘（参数1）;
提交阶段会将 binlog 刷入磁盘，然后设置 redo log 设置为 commit 状态，到这里两阶段就已经完成了。

在两阶段提交中，是以 binlog 刷入磁盘时机作为事务提交成功的标志的:

如果 binlog 还没刷入磁盘的时候，MySQL 就发生了崩溃，MySQL 重启的时候就需要回滚事务;
如果 binlog 刷入磁盘，即使 redo log 没有设置 commit 状态，MySQL 就发生了崩溃，MySQL 重启的时候就会提交事务。

数据库备份

为什么需要备份

备份是防范灾难的重要手段，无论是恶意删库、员工误操作、还是系统故障，一个完善的备份系统能够确保数据安全。高可用设计保证业务连续性，而备份则是最后的数据安全保障线。

全量备份

全量备份是指备份当前时间点数据库中的所有数据，作为恢复数据的基础。根据不同需求，可以选择以下两种方式：

1. 逻辑备份

逻辑备份将数据库内容转换为SQL语句形式保存，适合以下场景：

需要恢复特定表或数据时
在不同版本的MySQL之间迁移数据
需要对备份内容进行检查或修改时

工具选择：

mysqldump：单线程备份，简单易用
1
mysqldump -A --single-transaction > backup.sql
必须使用--single-transaction参数确保一致性备份，否则备份文件可能不一致，失去意义。
mysqlpump：当备份速度成为瓶颈时可考虑，但需注意：
1
mysqlpump -A --single-transaction --default-parallelism=8 > backup.sql
多线程时无法保证一致性，且对单个大表仍是单线程处理。
mydumper（推荐）：当数据库规模较大且需要高效备份时的首选
1
mydumper -o /bak -r 100000 --trx-consistency-only -t 8
同时支持一致性备份、记录级分片和多线程操作，备份恢复灵活性高。

2. 物理备份

当逻辑备份速度无法满足要求时，物理备份是更高效的选择：

直接复制数据文件，速度比逻辑备份快很多
适合大型数据库的备份与恢复
恢复速度更快，减少业务中断时间

工具选择：

Clone Plugin（MySQL 8.0.17+）：官方认证，可靠性高
1
CLONE LOCAL DATA DIRECTORY = '/path/to/clone_dir';
适合MySQL 8.0以上版本，支持本地和远程备份。
Xtrabackup：当使用MySQL 8.0之前版本时的替代选择

物理备份的局限性在于只能整个实例恢复，不能像逻辑备份那样选择性恢复特定表。