《MySQL技术内幕：InnoDB存储引擎》读后问题总结

MySQL Innodb特性

插入缓冲的使用条件中为什么辅助索引不能是唯一的？

插入缓冲的设计是面向非聚集索引的插入和更新，由于非聚集索引在磁盘中并不能随主键排列的，所以主键连续增长的插入会导致非聚集索引的大量随机IO，所以使用插入缓冲在内存中进行修改，如果宕机则需要恢复。如何修改的非聚集索引是唯一索引，那么当修改该辅助索引时，还是要进行随机IO，因为必须到磁盘上查找数据，确保当前插入的数据的唯一性。

为什么需要两次写？

MySQL是以页组织数据的，页的结构如下，而Mysql的页是磁盘块的整数倍，在将页写入磁盘时，并不能保证整个页写入的原子性。有可能页写入一半，数据库宕机，则此页的数据是损坏的。而redo.log中记录的是物理日志，即page x offset y data，但是对页只进行数据修改，无法挽回也损坏的局面（比如page header只写入一半，导致page header无法解析）。所以只能通过两次写，为整个页面做一个副本。

Binlog 和 Redolog

Binlog为什么不能用来做crash-safe？

binlog是位于MySQL server层面的，其设计目的只是用来存档，用于主从复制和用于数据库的基于时间点的还原，而不是用来断电、异常恢复的。MyServer层面并不提供事务的crash-safe功能，所以必须借助redolog实现，提交事务的不丢失是依靠重做redo log。

Binlog和redolog的区别

• redo log和binlog的产生方式不同。redo log是在物理存储引擎层产生，而binlog是在MySQL数据库的Server层产生的，并且binlog不仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对数据库的更改都会产生binlog。
• redo log和binlog的记录形式不同。MySQL Server层产生的binlog记录的是一种逻辑日志，即通过SQL语句的方式来记录数据库的修改；而InnoDB层产生的redo log是一种物理格式日志，其记录的是对于磁盘中每一个数据页的修改。
• redo log和binlog记录的时间点不同。binlog只是在事务提交完成后进行一次写入，而redo log则是在事务进行中不断地被写入，redo log并不是随着事务提交的顺序进行写入的，这也就是说在redo log 中针对一个事务会有多个不连续的记录日志。

Binlog和redolog的写入一致性问题

https://blog.csdn.net/shaochenshuo/article/details/73239949

https://blog.csdn.net/huangjw_806/article/details/100927097?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.control

在事务提交时利用两阶段提交(内部XA的两阶段提交)很好地解决了上面提到的binlog和redo log的一致性问题：

• 第一阶段：InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作。

• 第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。此过程中是先写redo log再写binlog的。但需要注意的是，在第一阶段并没有记录完整的redo log（不包含事务的commit标签），而是在第二阶段记录完binlog后再写入redo log的commit 标签。还要注意的是，在这个过程中是以第二阶段中binlog的写入与否作为事务是否成功提交的标志

• Prepare阶段时需要写盘：2PC要求在Prepare时就要将数据持久化，只有这样，恢复时才能提交已经记录了Xid_log_event的事务。

• Commit阶段时不需要写盘：如果Commit阶段不写盘，会造成什么结果呢？已经Cmmit了的事务，在恢复时的状态可能是Prepared。由于恢复时，Prepared的事务可以通过Xid_log_event来提交事务，所以在恢复后事务的状态就是正确的。因此在Commit阶段不需要写盘。

持久性设置推荐

• 每次事务的 redo log 都直接持久化到磁盘:
  推荐: innodb_flush_log_at_trx_commit 这个参数设置成 1。这样可以保证 MySQL 异常重启之后数据不丢失。
• 每次事务的 binlog 都持久化到磁盘
  推荐: sync_binlog 这个参数设置成 1 ,保证 MySQL 异常重启之后 binlog 不丢失。

Binlog和redolog的两阶段写入中为什么要在prepare阶段就落盘呢？

如果binlog写成功之后，将redolog置成commit的时候数据库崩了，如果在commit的时候redolog才落盘，由于事务是否成功以binlog为依据，上面的情况下事务是成功的，但是redolog没有写到磁盘，即使binlog记录了这个事务是成功的，但是没有redolog记录，无法进行重做。恢复之后数据库与binlog就不一致了。如果在prepare阶段落盘，上面的情况下redolog已经写入到文件了（在prepare阶段已经写盘了），恢复的时候不会丢数据。

为什么Mysql默认隔离级别是RR？和binlog的格式有什么关系？

Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！
当binlog为STATEMENT格式，且隔离级别为读已提交(Read Commited)时，有什么bug呢？如下图所示，在主(master)上执行如下事务：

这是因为binlog是以commit的顺序记录sql，而mysql是以sql语句执行的顺序修改数据的。所以在master上执行的顺序为先删后插！而此时binlog为STATEMENT格式，它记录的顺序为先插后删！从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致！
而如果隔离级别是RR，在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住！当然如果将binglog的格式修改为row格式，此时是基于行的复制，自然就不会出现sql执行顺序不一样的问题（Row格式会记录被修改的每一行，而不是直接记录原本的sql语句）！奈何这个格式在mysql5.1版本开始才引入。因此由于历史原因，mysql将默认的隔离级别设为可重复读(Repeatable Read)，保证主从复制不出问题！

数据和索引

数据页中的Page Directory的作用？

数据页中的所有记录使用偏移量组织起来的单链表，查找数据时只能从头向后遍历。InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page_Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：1、通过二分法确定槽；2、通过记录的next_record属性遍历该槽所在的组中的各个记录。

为什么说外键不加索引会导致死锁

不加索引的外键会使得对子表的记录修改走全表扫描，加表级锁。
而外键的修改经常需要同时修改父表和子表的记录，这就可能出现死锁。

mysql主键为什么要是递增的

如果使用递增主键，每次插入记录会顺序添加到当前索引节点的后续位置，当一页写满时，就会自动开辟一个新的页。然而如果是非递增主键，则需要进行随机插入，造成
1、大量随机IO，
2、可能插入页面不在缓存中，需要从磁盘上加载页面
3、如果分页会导致数据频繁移动，索引不紧凑

书签为行标识符（文件号：页号：槽号）的非聚集索引比书签为主键的非聚集索引快吗？

1、 在只读情况下是的
2、 表需要插入、更新、删除，这些可能会改变数据的行标识符，那么所有相关的非聚集索引都需要修改更新。

非聚集索引（书签为行标识符）和聚集索引，哪个好？

1、非聚集索引（书签为行标识符）在只读情况下，查询比非聚集索引（书签为主键）更快，但是对于OLTP应用，数据经常更新导致物理地址的变更。
2、非聚集索引（书签为行标识符）的范围查找包含了大量离散读，因为数据在磁盘中是按照插入顺序排列的，但是这也可以通过数据预读进行改善。

锁算法

MySQL到底是如何加锁的？

MySQL的锁是由存储引擎实现，Innodb的锁是作用于索引之上。
https://yq.aliyun.com/articles/108095?t=t1

* 如果
1. select * from xx where col <比较运算符> M for update
2. M->next-rec: 表示M的下一条记录
3. M->pre-rec: 表示M的前一条记录



* 等值查询M，非唯一索引的加锁逻辑
    (M->pre-rec,M],(M,M->next-rec]

* 等值查询M，唯一键的加锁逻辑
    [M], next-lock 降级为 record locks
    这里大家还记得之前讲过的通用算法吗：
            next-key lock 降级为 record lock的情况：
                如果是唯一索引，且查询条件得到的结果集是1条记录（等值，而不是范围），那么会降级为记录锁

* >= ，加锁逻辑
    (M->pre_rec,M],(M,M->next-rec]....(∞]

* > ,  加锁逻辑
     (M,M->next-rec] ... (∞]

* <= , 加锁逻辑
    (-∞] ... (M,M->next-rec]

* < , 加锁逻辑
     (-∞] ... (M->rec,M]

在RR隔离条件下：

• 如果查询条件是辅助索引，则辅助索引加锁按照上述规则，主键索引为record lock。
• 如果查询条件是主键索引，则主键索引加锁按照上述规则。
• 如果查询条件没有索引，则加表级锁，即为每条记录都加锁。但在实际使用过程当中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的

在RC隔离条件下：

• 如果查询条件是索引，则利用record lock加锁。
• 如果查询条件没有索引，则加表级锁，即为每条记录都加锁。

辅助索引加锁不仅需要考虑辅助索引的值，还需要考虑主键值！！！！！！