《丁奇-MySQL45讲-12/13》之归纳总结

12 | 为什么我的MySQL会抖一下？

这一篇将的云里雾里的感觉，比如数据页和redo log的脏页到底是一起刷还是分开刷的，还有它们之间的LSN是如何比较的，这部分应该属于很复杂的内容，丁奇老师说后续的篇章会阐述这块的内容，等着吧。

• 内存中的数据页什么情况下会刷盘

1. redo log缓冲池满了，放不下脏页了（在这种情况下个人理解也会触发redo log刷盘）。

2. 系统内存不足，当需要新的数据页而内存不足时，就会淘汰一些数据页，如果这些数据页刚好是脏页的话，就会先将脏页刷盘。

3. 系统空闲的时候。

4. MySQL正常关闭的时候。

• 配置主机的IO能力，可以使用innodb_io_capacity 参数，它会告诉 InnoDB 主机的磁盘能力。这个值可以设置成磁盘的 IOPS，磁盘的 IOPS 可以通过 fio 这个工具来测试。

• 控制刷脏页的速度：通过公式计算得到F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度，如图所示：

• InnoDB的连坐机制：一个脏页要被刷盘的时候，还会看它隔壁的数据页是否也是脏的，如果是的话就会被拖下水，同时还会继续往后蔓延，连着好几个数据页一起刷盘。可以通过innodb_flush_neighbors 参数来控制这个行为，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

• 在崩溃重启时redo log是怎么知道哪些数据页是已经刷到磁盘上的：LSN（日志序列号），数据页有序列号、Checkpoint也有序列号、redo log也有序列号，每次更新数据页的时候都会更新对应的序列号，当刷盘的时候这些序列号也会存储到磁盘上，当使用redo log进行重放的时候，会比较redo log和数据页（磁盘上）的序列号大小，至于是如何的一个比较过程，没能找到一个比较合理的解释。

13 | 为什么表数据库删掉一半，表文件大小不变？

• 在MySQL8.0版本以前，表结构是存放在以.frm为后缀名的文件中，而8.0版本就允许将表结构存放至系统数据表中。

• 表数据可以存放在共享表空间中，即ibdata，也可以单独的文件，即以.ibd为后缀的文件中，具体参数是innodb_file_per_table。不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

• 数据页上的某一条记录被删除后只会打上标记，并没有真正的删除，这条记录的空间可以被符合条件的新纪录复用。对于删除整个数据页的删除也是打上删除标记，只不过该页可以被无条件复用。

• drop会删除表结构、数据，释放空间；truncate不会删除表结构，删除数据，释放空间；delete不会删除表结构，删除数据，不会释放空间。

• 数据页在增删改的过程中可能会出现空洞，简单来说空洞就是已经被标记为删除的空间却没有被复用，这种情况下通常会使用alter table A engine = innodb来重建表，以达到回收空洞的效果。

• 以下几种索引创建的方式：

1. copy：索引创建是通过临时表拷贝的方式实现的。

2. inplace：索引创建直接在原表上进行，不会拷贝临时表，原表是可读的，但不可写。

3. online inplace：在inplace的基础上原表是可以修改的，将这些修改的记录保存到row log中，等到索引创建完毕后重放row log上的记录，在重放过程中会对row log最后一个block加锁，如果不是最后一个的话，那么仍然可以执行dml，该方式减少了锁表时间。