为什么要用日志

日志有三方面的作用，分别是WAL机制、备份归档以及crash-safe能力。一个个来说说它的好处：

WAL机制

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志【log file on disk】，再写磁盘【Page on disk】。看定义貌似跑不掉写磁盘这一步，因为日志文件也存储在磁盘中。为啥WAL的写磁盘就快呢？

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快，到达一定时机会将日志中涉及的数据写到磁盘中的数据库。如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高
• 组提交机制，积累一些buffer后一次性刷盘。可以大幅度降低磁盘的 IOPS 消耗（IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数),是衡量磁盘性能的主要指标之一。IOPS是指单位时间内系统能处理的I/O请求数量），这样其实写日志【log file on disk】的次数也会少，可能一次写好几个事务。

其中IOPS (Input/Output Per Second)即每秒的输入输出量(或读写次数)，是衡量磁盘性能的主要指标之一，数值越小，说明IO占用越低。

crash-safe能力

crash-safe能力表示，即使数据库发生异常重启【可能是数据库进程掉电，也可能是主机掉电】，之前提交的记录，都不会丢失，也即已经提交（commit）成功的事务不会被丢失，可以通过持久化到磁盘的日志【log file on disk】+持久化到磁盘的数据【Page on disk】来恢复，

begin;
insert into t1 ...
insert into t2 ...
commit;

举个例子，如果仅仅只有数据库，掉电的时候以上的第一个insert执行完中断，数据库中的数据就不是我们预想的了，满足不了事务的ACID特性。

备份归档

数据库的备份和恢复依赖于日志和数据库的全量备份的，因为我们不能频繁的进行数据库的实时的全量备份，成本太大而且不易实现，所以定期的整库备份+日志才是实现数据库安全稳定的王道

日志设计策略

Redolog和Binlog可以满足以上提到的三个日志需要的功能。

Redolog和Binlog实现WAL

其实无论是Redolog还是Binlog，都有WAL机制，也就是更新记录都会先落到磁盘上的日志文件上，并且更新内存中的数据页，等到合适的时机再刷数据落盘。具体而言有如下三个步骤：

• 创建阶段：事务创建一条日志，并添加到日志缓存
• 日志刷盘【log file on disk】：日志缓存经过文件系统缓存后写入到磁盘上的日志文件，并更新记录到内存中的数据页，使其变为脏页
• 数据刷盘【Page on disk】：日志对应的脏页数据写入到磁盘上的数据文件
• 更新checkpoint【Redo log特有】，RedoLog在数据刷盘之后还有一个步骤就是，也就是说redolog中的checkpoint前进方向区域一定是数据页还没落盘的提交，这样我们就能确定哪些数据即使事务commit也就是日志刷盘已经成功的状态下其实数据还没有刷盘

commit主要就是在日志刷盘阶段，我们主要讨论下事务从产生到日志落盘为止的过程。

Redolog写入机制

以下这几个步骤对应于整个事务数据落盘的前两步，也就是日志刷盘为止。

redo log 可能存在三种状态，这三种状态是依次写入的：

1. 事务先写到 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；日志写到 redo log buffer 是很快的
2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分【redo log file on FS cache】，wirte 到 page cache 也很快
3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分【binlog file on disk】，fsync到磁盘的速度就慢多了

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ，不管是主机掉电还是MySQL异常重启，都有丢数据的风险，风险高
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache，写入文件系统的page cache，主机掉电后会丢数据，但是MySQL异常重启不会丢数据，风险较低，写入比较快
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘，非常安全，但慢。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。可以理解为innodb_flush_log_at_trx_commit三个设置对应每次事务写入到不同级别，而每隔一秒就会从最不稳定状态直接刷到最稳定状态

Binlog写入机制

事务执行过程中，**先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件【文件系统缓存page cache】**中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

1. 事务先写到每个线程自己的 binlog cache，但是共用同一份 binlog 文件。
2. 然后write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快【binlog file on FS cache】。
3. 最后fsync，才是将数据持久化到磁盘的操作【binlog file on disk】

一般情况下，我们认为 fsync 才占磁盘的 IOPS。write 和 fsync 的时机，是由参数 sync_binlog 控制的：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志，主机都crash了，文件系统缓存里的日志当然也丢了，没法落盘

可以理解为sync_binlog等于几表明每几个事务发生一次fsync，因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。

Redolog实现crash-safe

为什么Redolog可以实现crash-safe而binlog不可以，因为RedoLog的特殊数据结构，RedoLog是循环写的，有特有的checkpoint。

binglog是顺序写的，binlog 没有能力恢复“数据页”。如果MySQL 发生了 crash需要恢复（恢复的过程就是将未刷盘的内存中的数据脏页恢复到内存中）

• binlog由于没有checkpoint所以可能虽然有N个事务已经commit，但数据页还没有落盘，依据binlog没有办法判断哪些事务的commit涉及的数据没有落盘，也就不知道该从哪里恢复
• redo log是循环写的，有checkpoint，通过checkpoint的移动位置可以确定哪些事务指向的数据脏页确实被刷盘了，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了

如果真想使用binlog来恢复的话，那么就要在每个commit之前，将更改的内存记录刷盘。刷盘之后再将这个事务改为commit状态。 这样崩溃恢复就可以在事务级去做了，而不用在数据页级去做了，但是这样显然违背WAL技术【累计多个事务修改的数据页存储内存，一次刷盘来减少IOPS】的初衷

binlog实现备份归档

redo log 是循环写的，空间固定会用完，用完后会之前的记录会被覆盖；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志，所以归档使用追加写的binlog。

Redo log和Binglog存在意义

除了上述二者各自不可替代的功能外，他们还有一些必须存在的历史原因

• Binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog
• Redo log是 InnoDB 引擎特有的，可以实现crash-safe，因为这个能力，后续被当做了MySQL的默认引擎

所以因为有了Redo log的crash-safe，所以没必要再去做一个出来，而又因为Binlog比较早期，除了归档还在很多生态中被广泛使用，所以二者都不能被取代。只能并存，那么并存就有一个问题，我们需要保证这两份并存的日志是一致的！

两阶段提交机制

两阶段提交的顺序如下图所示，那么为什么要两阶段提交呢？前面我们讨论了两个日志各自存在的必要性，表明我们不能只用其中一种日志，而要结合使用，结合使用就一定要保证它两是一致的。

如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。

• 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。binlog丢，无法备份
• 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于 redo log 还没写，崩溃恢复以后这个事务无效。本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。redolog丢，无法crash-safe

所以如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。采用这种方式再验证一下：

• 写redo log 预提交过程中，机器挂了，则回滚事务【时刻A】
• 写完redo log 预提交，并且写binglog过程中，机器挂了，则判断【时刻B】

• 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交【说明写完binlog了】
• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务

这样就解决了数据一致性的问题，那么redo log 和 binlog 是怎么关联起来的?它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，binlog完整就提交，不完整就回滚

这样就能知道这条事务binlog到什么阶段了

组提交策略

三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160

从图中可以看到：

1. trx1 是第一个到达的，会被选为这组的 leader；
2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；这时候 trx2 和 trx3 就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好

提升MySQL的IO性能

结合以上的日志写入策略和延迟刷盘尽量组里事务多一些来谈谈MySQL的IO性能

日志落盘参数配置

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？针对这个问题，可以考虑以下三种方法：

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
• 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）【事务都写到文件系统缓存】。这样做的风险是，主机crash掉电时会丢 binlog 日志。
• 将 innodb_flush_log_at_trx_commit 设置为 2【事务都写到文件系统缓存】。这样做的风险是，主机掉电的时候会丢数据。

不建议把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小

MySQL的延迟刷盘策略

为了让一次 fsync 带的组员更多，MySQL 有一个优化策略：拖时间。了解了两种日志的写入过程后，我们可以把这个过程更加的细化

这样做有两个好处，对redolog和binglog都加了组提交优化：

• redolog可以更好的组提交。把 redo log 做 fsync 的时间拖到了步骤 2之后，这样redo log做fsync 和 write的中间隔了一个binlog的write，所以会累计更多的事务在文件缓存中一起刷盘
• binlog 也可以组提交了。在执行第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好

这就是MySQL的拖时间组提交策略，需注意其实组提交处理的是并发的场景，单线程的话在双1设置下都是每次事务完成单个刷盘的。

MySQL数据库索引

本部分回答以下几个问题，如果能回答正确，则证明本部分掌握好了。

索引建在哪些字段上比较合适，应用场景是什么

聚簇索引和非聚簇索引的区别

索引的常见模型有哪几种

InnoDB的索引模型是什么样的

索引的几种策略，覆盖索引和回表机制

联合索引的最佳使用方式、最左前缀原则

索引下推机制

接下来我们看这部分的内容。

索引的使用场景

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下是比较难找的。同样，对于数据库的表而言，索引其实就是它的目录。数据库中如果不使用索引，大概率会非常慢，所以一定需要创建索引。索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列 上创建索引，例如：

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

总之就是在进行一些常用的查询和排序的过程中使用索引的场景比较多。因为如果不建立索引的话只能全表扫描了。

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是mysql数据库中两种主要的索引方式

• 聚簇索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚簇索引与字典相同，字典按字母顺序排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引，我们把这种正文内容本身就是一种按照一定规则排列的目录称为聚集索引
• 非聚簇索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为非聚集索引。

二者的详细对比如下，我们可以这么理解：聚簇索引索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过节点上存储一个指向对应的数据块的指针

索引常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多，这里介绍三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树

哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。以下是一个按照身份证号查名字的示例：

需要注意的是，图中四个 ID_card_n 的值并不是递增的，而且同一个链表上的User2也不会与User4比较就直接追加到链表尾部，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。所以哈希表这种结构适用于只有等值查询的场景

这种模型使用场景较少，其优点和应用场景如下：

• 优点：Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B+Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于B+Tree 索引。时间复杂度是O(1)
• 应用场合：依赖于这种单值查找的系统被称为 键-值存储；对于这种系统，尽可能地使用 hash 索引

缺点不言而喻，应用场合太小了，只有做等值查询时候才有意义，区间查询时效率极差。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证号查名字的例子，如果我们使用有序数组来实现的话，示意图如下所示

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。

• 等值查询，这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))
• 区间查询，要查身份证号在[ID_card_X, ID_card_Y]区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高（需要O(n)的时间复杂度），所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据

搜索树

二叉搜索树也是经典数据结构了，这里简单回顾下。Binary Sort Tree，二叉搜索树或者是一棵空树，或者是具有下列性质的二叉树：

• 若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
• 若它的右子树不空，则右子树上所有结点的值均大于或等于它的根结点的值；
• 它的左、右子树也分别为二叉排序树。
• 没有键值相等的节点。

二叉搜索树是按照关键升序排列，对每一个节点来说，比它节点值高的节点是它的中序后继，所以对二叉查找树进行中序遍历（左根右），即可得到有序的数列。它和二分查找一样，插入和查找的时间复杂度均为O(logn)，但是在最坏的情况下仍然会有O(n)的时间复杂度。原因在于插入和删除元素的时候，树没有保持平衡，所以我们这里也需要数是平衡的，也就是二叉搜索平衡树。