本篇接上一篇笔记，主要讲了索引和日志相关知识,详细见目录。有问题及时在本博客或在CSDN留言.

普通索引与唯一索引

假设有一个场景，某个表T中的字段k需要是唯一的，我们通过业务代码保证了插入字段的唯一性，那么从性能的角度考虑在数据库层面，我们是该选择普通索引还是唯一索引？

查询的区别

假如查询语句如下：

1
select id from T where k=5;

由于k是辅助索引，其索引树上叶子节点存储的是主键id，对于普通索引和唯一索引的区别是：

  • 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是这点区别对于性能的影响微乎其微：

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。
因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。
但是，我们之前计算过，对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。

更新的区别

change buffer

说明两者在更新方面的区别时，需要先介绍下change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上

merge

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭 （shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率(相比把整个数据页读入内存来说)。

什么条件下可以使用 change buffer

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。
因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

下面看下对于这两种索引，如果想要在在这张表中插 入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行 结束；
  • 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微 小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer使用场景

通过上面的分析，我们了解了使用change buffer对更新过程的加速作用，也清楚了change buffer只限于用在普通索引的场景下，而不适用于唯一索引。

现在有一个问题就是：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

到底用唯一索引还是普通索引

这两类索引在查询 能力上是没差别的，主要考虑的是对更新性能的影响。所以，尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把change buffer尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log

经常有人混淆redo log和change buffer。WAL提升性能的核心机制，也的确是尽量减少随机读写，这两个概念确实容易混淆。所以，这里下面通过同一个流程里来说明，便于区分这两个概念。

假设要执行一条插入语句：

1
insert into t(id,k) values(id1,k1),(id2,k2);

假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如下图所示是带 change buffer 的更新状态图。

分析这条更新语句，你会发现它涉及了四个部分：内存、redolog（ib_log_fileX）、数据表空间（t.ibd）、系统表空间（ibdata1）。
这条更新语句做了如下的操作（按照图中的数字顺序）：

  • Page1在内存中，直接更新内存；
  • Page2没有在内存中，就在内存的change buffer区域，记录下“我要往Page2插入一行”这个信息
  • 将上述两个动作记入redo log中（图中3和4）。

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。
同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间

那在这之后的读请求，要怎么处理呢？
比如，我们现在要执行 select * from t where k in (k1, k2)。这里，下面是这两个读请求的流程图。
如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。所以，图中就没画出这两部分。

从图中可以看到：

  • 读 Page 1 的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL 之后 如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可 以返回？其实是不用的。你可以看一下图 3 的这个状态，虽然磁盘上还是之前的数据， 但是这里直接从内存返回结果，结果是正确的。

  • 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里 面的操作日志，生成一个正确的版本并返回结果。

    可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

    所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的 是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘 的 IO 消耗。

为什么优化器会选错索引

选择索引是优化器的工作，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断

扫描行数是怎么判断的？

区分度：MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。
这个统计信息就是索引的区分度。一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

我们可以使用 show index 方法，看到一个索引的基数。语法为：

show index from 表名

得到结果中的Cardinality就是索引基数。这个基数是mysql采用”采样统计”的方法得到的（如果读取整个表数据来统计代价太大，innodb会默认选择N个数据页，统计这些页上的不同值，得到一个平均数，然后乘以这个索引的页面数，就得到了基数）。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent的值来选择：
设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

假如a是一个表t的普通索引，我们通过如下两条语句查询：

select * from t where a between 100 and 200;

select * from t force index(a) where a between 100 and 200;

其中第二句中a为索引名

我们知道第一条语句有时候并不能如我们意愿去走索引a，可能是下面的情况：

当我们采用explain去查看执行计划的时候，rows（预计扫描行数）这一行并不是很准确，而有时候上面的语句，第一句rows索引优化器评估的预计扫描行数是接近全表，假设有10000条数据，第二句我们强制使用索引可能需要扫描的行数是3000条，但是为什么查询优化器依然没有选择走索引a，而是选择了走预计扫描行数更多的方案1呢。

这是因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
而如果选择扫描 10000行，是直接在主键索引上扫描的，没有额外的代价。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当 然，从执行时间看来，这个选择并不是最优的

如何修正预计扫描行数

analyze table t命令，可以用来重新统计索引信息。在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。

索引选择异常和处理

当优化器选错索引的时候，我们可以采用以下方法：

  • 采用force index 强行选择一个索引。

  • 第二种方法就是，我们可以考虑 修改语句，引导 MySQL 使用我们期望的索引。

    假如表t有a,b两个索引（非联合索引），查询语句条件中有一句“order by b limit 1” 。假如最后优化器没有走我们希望的索引，我们可以把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

    之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已 经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。
    现在 order by b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。 因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。

  • 在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选 择，或删掉误用的索引。

给字符串字段加合理的索引

指定索引前缀长度

假设我们需要在用户表中邮箱这个字段（字符串）加索引，下面有两种方式：

1
2
alter table SUser add index index1(email); 
alter table SUser add index index2(email(6));

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。

由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节 （即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

但，这同时带来的损失是，可能会增加额外的记录扫描次数。

下面再以以下查询语句看下两个索引定义下怎么执行：

1
select id,name,email from SUser where email='zhangssxyz@xxx.com';

当使用index1的时候，执行顺序如下：

  • 从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值；
  • 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
  • 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=’zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

当使用index2的时候，执行顺序如下：

  • 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；

  • 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；

  • 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；

  • 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

    在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

    通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。
    但是，对于这个查询语句来说，如果你定义的index2不是email(6)而是email(7），也就是说取email字段的前7个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到ID2，只扫描一行就结束了。
    也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

如何指定合适的前缀索引长度

我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：

1
select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引， 可以用这个语句：

1
2
3
4
5
6
7
SELECT
	count(DISTINCT LEFT ( email, 4 )） AS L4,
	count(DISTINCT LEFT ( email, 5 )） AS L5,
	count(DISTINCT LEFT ( email, 6 )） AS L6,
	count(DISTINCT LEFT ( email, 7 )） AS L7,
FROM
	SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

前缀索引对覆盖索引的影响

先看下面的sql语句：

1
select id,email from SUser where email='zhangssxyz@xxx.com';

与前面例子中的 SQL 语句相比，这个语句只要求返回 id 和 email 字段。

1
select id,name,email from SUser where email='zhangssxyz@xxx.com';

所以，如果使用index1（即email整个字符串的索引结构）的话，可以利用覆盖索引，从index1查到结果后直接就返回了，不需要回到ID索引再去查一次。而如果使用index2（即email(6)索引结构）的话，就不得不回到ID索引再去判断email字段的值。

即使你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到id索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素

其他方式

假如我们遇到区分度不是很好的字符串字段，比如身份证号，同一个省市县前面的很多位是相同的，这个时候使用前缀索引短了，区分度不好，长了占用索引空间多，同一页能放的索引数据就少了，搜索效率就会降低。这个时候我们考虑采取别的方式来更好应用索引

  • 第一种方式是使用倒序存储

    如果你存储身份证号的时候把它倒过来存，每次查询的时 候，你可以这么写：

    1
    select field_list from t where id_card = reverse('input_id_card_string');

    由于身份证号的最后 6 位没有地址码这样的重复逻辑，所以最后这 6 位很可能就提供了足 够的区分度。当然了，实践中你不要忘记使用 count(distinct) 方法去做个验证。

  • 第二种方式是使用 hash 字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

    1
    alter table t add id_card_crc int unsigned, add index(id_card_crc);

    然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能 是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。这样，索引的长度变成了 4 个字节，比原来小了很多。

使用倒序存储和使用 hash 字段这两种方法的异同点。

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X,ID_Y]的所有市民了。同样地，hash字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

  • 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。

  • 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数， 而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂 度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。

  • 从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

刷盘机制

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志），在更新内存写完 redo log 后，就返回给客户端，本次更新成功。之后在合适的时间，会将内存数据刷到磁盘上。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”不论是脏页还是干净页，都在内存中。

所以对于更新操作来说，平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

什么情况会引发数据库的 flush 过程

  • InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。前面博文中画了一个 redo log 的示意图，这里改成环形，便于大家理解。

checkpoint可不是随便往前修改一下位置就可以的。比如图2中，把checkpoint位置从CP推进到CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都flush到磁盘上。之后，图中从writepos到CP’之间就是可以再写入的redolog的区域。

  • 系统内存不足。需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

    你一定会说，这时候难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？这里其实是从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：一种是内存里存在，内存里就肯定是正确的结果，直接返回；
    另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。

  • MySQL 认为系统“空闲”的时候。即使是忙碌的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

  • MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

上面四种情况对于性能有什么影响呢？

其中，第三种情况是属于MySQL空闲时的操作，这时系统没什么压力，而第四种场景是数据库本来就要关闭了。这两种情况下，你不会太关注“性能”问题。所以这里，我们主要来分析一下前两种场景下的性能问题。
第一种是“redolog写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为0。
第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

  • 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
  • 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略

首先，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。
这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。这个值建议设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试，如果用阿里云数据库可以直接在性能监控页查看到。

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

InnoDB会根据这两个因素先单独算出两个数字。
参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字，计算这个数字的伪代码类似这样：

F1(M) { if M>=innodb_max_dirty_pages_pct then return 100; return 100*M/innodb_max_dirty_pages_pct; }

InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。F2(N)算法比较复杂，你只要知道N越大，算出来的值越大就好了。
然后，根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

下面是计算过程流程图：

InnoDB会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因。
要尽量避免这种情况，你就要合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%。

其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的

刷脏页的连坐策略

一旦一个查询请求需要在执行过程中先flush掉一个脏页时，这个查询就可能要比平时慢了。而MySQL中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在InnoDB中，innodb_flush_neighbors参数就是用来控制这个行为的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO。机械硬盘的随机IOPS一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。

而如果使用的是SSD这类IOPS比较高的设备的话，我就建议你把innodb_flush_neighbors的值设置成0。因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少SQL语句响应时间。

在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了

表空间回收

一个InnoDB表包含两部分，即：表结构定义和数据。在MySQL8.0版本以前，表结构是存在以.frm为后缀的文件里。而MySQL8.0版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

  • 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  • 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以.ibd为后缀的文件中。
  • 从MySQL5.6.6版本开始，它的默认值就是ON了。

建议不论使用MySQL的哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
所以，将innodb_file_per_table设置为ON，是推荐做法，接下来的讨论都是基于这个设置展开的。

表中的数据被删除 了，但是表空间却没有被回收？

数据删除流程

假设我们有张表索引如下图所示：

假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。如果我们删掉了一个数据页上的 所有记录，会怎么样？结果是，整个数据页就可以被复用了

数据页的复用跟记录的复用是不同的。

记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4这条记录被删除后，如果插入一个ID是400的行，可以直接复用这个空间。但如果插入的是一个ID是800的行，就不能复用这个位置了。

而当整个页从B+树里面摘掉以后，可以复用到任何位置。以上图为例，如果将数据页pageA上的所有记录删除以后，pageA会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，pageA是可以被复用的。
如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
如果我们用delete命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但 磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可 以复用，而没有被使用的空间，看起来就像是“空洞”。

数据插入造成空洞

不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。
假设图1中pageA已经满了，这时我要再插入一行数据，如下图所示，会怎样呢？

由于pageA满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面pageB来保存数据了。页分裂完成后，pageA的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。
也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。
而重建表，就可以达到这样的目的。

重建表

假设一个表A已经有很多空洞了，为了去除这些空洞，我们可以新建一个表B与表A结构完全相同，之后将表A数据按照顺序插入到表B，然后用表B替换表A，就会去除这些空洞，alter table A engine=InnoDB这个命令可以帮助我们重建表，流程类似上面，只是我们对于临时表创建无感知。

在这个过程中，花时间多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要 写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。 也就是说，这个 DDL 不是 Online 的。

MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化

引入了Online DDL之后，重建表的流程：

  • 建立一个临时文件，扫描表A主键的所有数据页；
  • 用数据页中表A的记录生成B+树，存储到临时文件中；
  • 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（rowlog）中，对应的是图中state2的状态；
  • 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
  • 用临时文件替换表A的数据文件。

与前述过程不同的地方是，由于日志文件记录和重放操作这个功能的存在， 这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。

上图的流程中，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正 拷贝数据之前就退化成读锁了。
为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。

对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用GitHub开源的gh-ost来做。

Online 和 inplace

你可能注意到了，在非online重建表操作中，我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这 是一个临时表，是在 server 层创建的。
在上图中，根据表 A 重建出来的数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把 数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

这两个逻辑之间的关系是什么的话，可以概括为：

  • DDL过程如果是Online的，就一定是inplace的；
  • 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

optimize table、analyze table和alter table这三种方式重建表的区别。
从MySQL5.6版本开始，alter table t engine=InnoDB（也就是recreate）默认的就是上面图4的流程了；
analyze table t其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
optimize table t等于recreate+analyze

思考题

一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，正常会建议你将redolog设置成4个1GB的文件。
但如果你在配置的时候不慎将redo log设置成了1个100M的文件，会发生什么情况呢？又为什么会出现这样的情况呢？

解答：

每次事务提交都要写redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这个“环”将很快被写满，write pos一直追着CP。

这时候系统不得不停止所有更新，去推进checkpoint。
这时，你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。

什么时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大

  • 这个表，本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。在DDL期间，如果刚好有外部的DML在执行，这期间可能会引入一些新的空洞。

    在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“”紧凑的。

  • 假如是这么一个过程：

    • 将表 t 重建一次；
    • 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；
    • 这种情况下，再重建一次表 t，就可能会出现问题中的现象。

count（*）很慢是为什么

myisam存储引擎会将表行数记录在磁盘上，当我们不带where条件的去查询条数的时候得到结果会很快，而innodb存储引擎则是一行一行读取累计的，因此比较慢。为什么它不像myisam那样记录行数呢？

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因， InnoDB 表“应该返回多少行”也是不确定的。这和InnoDB的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是MVCC来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于count(*)请求来说，InnoDB只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

但是innodb在获取count(*)数据时还是做了一定的优化。

InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count()这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到小的那棵树来遍历。*在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。**

不同的 count 用法

首先你要弄清楚count()的语义。count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。后返回累计值。

所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

  • 对于count(主键id)

    InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

  • 对于count(1)

    InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

  • 对于count(字段)来说：

    如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不 能为 null，按行累加；

    如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

按照效率排序的话，count(字段)<count(主键id)<count(1)≈count()，所以尽量使用count(\)。

日志与索引问题专项

在两阶段提交的不同时刻发生异常重启，如何保证数据完整性

还是以之前讲过的两阶段提交流程为案例来说明：

如果在图中时刻A的地方，也就是写入redolog处于prepare阶段之后、写binlog之前，发生了崩溃（crash），由于此时binlog还没写，redolog也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库。到这里，都可以理解。

容易出现疑问的地方，主要集中在时刻B，也就是binlog写完，redolog还没commit前发生crash，那崩溃恢复的时候MySQL会怎么处理？
我们先来看一下崩溃恢复时的判断规则。
1.如果redolog里面的事务是完整的，也就是已经有了commit标识，则直接提交；2.如果redolog里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：a.如果是，则提交事务；b.否则，回滚事务。
这里，时刻B发生crash对应的就是2(a)的情况，崩溃恢复过程中事务会被提交。
现在，我们继续延展一下这个问题。

MySQL 怎么知道 binlog 是完整的

回答：一个事务的binlog是有完整格式的：

statement 格式的 binlog，后会有 COMMIT；
row 格式的 binlog，后会有一个 XID event。

另外，在MySQL5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的

redo log 和 binlog 是怎么关联起来的

回答：它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

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

处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?

回答：其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻 B，也就是 binlog 写完以后 MySQL 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。
所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

如果这样的话，为什么还要两阶段提交呢？干脆先redolog写完，再写binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？

回答：其实，两阶段提交是经典的分布式系统问题，并不是MySQL独有的。如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。

对于InnoDB引擎来说，如果redolog提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果redolog直接提交，然后binlog写入的时候失败，InnoDB又回滚不了，数据和binlog日志又不一致了。
两阶段提交就是为了给所有人一个机会，当每个人都说“我ok”的时候，再一起提交。

不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩溃恢复，又能支持归档，不就可以了？

只保留 binlog，然后可以把提交流程改成这样：… -> “数据 更新到内存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？

答案是不可以。
如果说历史原因的话，那就是InnoDB并不是MySQL的原生存储引擎。MySQL的原生引擎是MyISAM，设计之初就有没有支持崩溃恢复。
InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。
InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那就用InnoDB原有的redolog好了。
而如果说实现上的原因的话，就有很多了。就按照问题中说的，只用binlog来实现崩溃恢复的流程，如下图，这里就没有redolog了。

这样的流程下，binlog还是不能支持崩溃恢复的。我说一个不支持的点吧：binlog没有能力恢复“数据页”。
如果在图中标的位置，也就是binlog2写完了，但是整个事务还没有commit的时候，MySQL发生了crash。
重启后，引擎内部事务2会回滚，然后应用binlog2可以补回来；但是对于事务1来说，系统已经认为提交完成了，不会再应用一次binlog1。
但是，InnoDB引擎使用的是WAL技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页。
也就是说在图中这个位置发生崩溃的话，事务1也是可能丢失了的，而且是数据页级的丢失。此时，binlog里面并没有记录数据页的更新细节，是补不回来的。

你如果要说，那我优化一下 binlog 的内容，让它来记录数据页的更改可以吗？但，这其 实就是又做了一个 redo log 出来。
所以，至少现在的 binlog 能力，还不能支持崩溃恢复

那能不能反过来，只用 redo log，不要 binlog？

回答：如果只从崩溃恢复的角度来讲是可以的。你可以把 binlog 关掉，这样就没有两阶 段提交了，但系统依然是 crash-safe 的。
但是，如果你了解一下业界各个公司的使用场景的话，就会发现在正式的生产库上， binlog 都是开着的。因为 binlog 有着 redo log 无法替代的功能。
一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保 留，redo log 也就起不到归档的作用。
一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在 了很多地方。其中，MySQL 系统高可用的基础，就是 binlog 复制。
还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。
总之，由于现在包括 MySQL 高可用在内的很多系统机制都依赖于 binlog，所以“鸠占鹊 巢”redo log 还做不到。

redo log 一般设置多大？

回答：redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。
所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

正常运行中的实例，数据写入后的最终落盘，是从redolog更新过来的还是从bufferpool更新过来的呢？

回答：这个问题其实问得非常好。这里涉及到了，“redolog里面到底是什么”的问题。
实际上，redolog并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据终落盘，是由redolog更新过去”的情况。
1.如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redolog毫无关系。2.在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让redolog更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件?

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;

insert into t1 …

insert into t2 …

commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里。
所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。
但是，真正把日志写到redo log文件（文件名是ib_logfile+数字），是在执行commit语句的时候做的。
（这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。

单独执行一个更新语句的时候，InnoDB 会自己启动一个事务，在语句执行完成的时候提 交。过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成。