《MySQL实战45讲》实践篇 9-15讲 学习笔记

图片和内容来自于极客时间，如有版权问题，请联系我删除。

09 | 普通索引和唯一索引，应该怎么选择？

查询

select id from T where k=5
查询到叶子节点，唯一索引在找到第一个之后就会不再搜索，而普通索引会继续检索直到遇到第一个不满足条件的
二者性能差别微乎其微 ，因为InnoDB是按页加载数据的，只是多执行一次指针寻找和一次计算,并不耗时

更新

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

唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用
change buffer适合于写多读少的场景，如果读多写少，一个业务在写入之后马上会做查询，那么就不适合使用change buffer

普通索引和唯一索引:建议使用普通索引–区别在于更新的效率上

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

补充:
首先，业务正确性优先。这里的比较前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。

然后，在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引

课后问题: 如果某次写入使用了 change buffer 机制，之后主机异常重启，是否会丢失 change buffer 和数据。
是不会丢失.
虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

merge 的过程是否会把数据直接写回磁盘?
merge 的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

10 | MySQL为什么有时候会选错索引？

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)

选择索引是优化器的工作

优化器的逻辑

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

我们可以使用 show index 方法，看到一个索引的基数。虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。

MySQL 是通过采样统计得到索引的基数
采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数

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

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

如果show index的统计信息不对，那就修正。analyze table t 命令，可以用来重新统计索引信息

除了考虑扫描行数，MySQL还会考虑回表造成的额外开销

索引选择异常和处理

一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。
缺点:使用 force index 最主要的问题还是变更的及时性。
因为选错索引的情况还是比较少出现的，所以开发的时候通常不会先写上 force index。

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

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

这样的一个SQL,分析应该是使用a作为索引扫描行数更小，但是优化器选择了b(可能是由于这里使用了b进行排序)
改成 order by b,a limit 1;就可以引导MySQL去使用索引a

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

课后问题:为什么经过这个操作序列，explain的结果就不对了？
delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。
但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。

这样，索引 a 上的数据其实就有两份。然后你会说，不对啊，主键上的数据也不能删，那没有使用 force index 的语句，使用 explain 命令看到的扫描行数为什么还是 100000 左右？（潜台词，如果这个也翻倍，也许优化器还会认为选字段 a 作为索引更合适）

是的，不过这个是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。

11 | 怎么给字符串字段加索引？

可以针对整个字符串创建索引，也可以使用前缀索引
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
可以通过区分度找到一个比较合适的长度

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

如果前缀索引的区分度不高，怎么办?比方说身份证，一个县的前6位基本都是一致的
一种方式是使用倒序存储

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

二种方式是使用 hash 字段
在表上再创建一个整数字段，来保存身份证的校验码(就是对身份证做hash)，同时在这个字段上创建索引

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

缺点:两种都不支持范围查询
区别:
1.从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，
而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。(倒序存储后还需要创建前缀索引，需要额外的存储空间)
2.在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
3.从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数

课后问题: 如何给一个学生学号创建索引
前三位是所在城市编号、第四到第六位是学校编号,接下来是入学年份+排序号+邮箱后缀(@gamil.com)
因为维护的只是一个学校的，因此前面 6 位，可以只存入学年份加顺序编号，它们的长度是 9 位。

在此基础上，可以用数字类型来存这 9 位数字。比如 201100001，这样只需要占 4 个字节。其实这个就是一种 hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。

12 | 为什么我的MySQL会“抖”一下？— 突发性SQL执行变慢

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

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

flush时间:
1.redo log满了 check point向前移动
2.系统内存不足，需要淘汰一些数据页以加载新的数据页，如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3.MySQL 认为系统“空闲”的时候
4.MySQL 正常关闭的情况

分析下前两种对性能的影响:
1.redo log满了flush 这种情形需要尽可能避免。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0
2.“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页

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

InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况

InnoDB 刷脏页的控制策略

首先需要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。(innodb_io_capacity)

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。
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。根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N),N越大，算出来的值越大。

引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。
而 MySQL 会在刷脏时去判断相邻的数据页是否是脏页，是的话就一起flush,并且会不断蔓延
在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
而如果使用的是 SSD 这类 IOPS 比较高的设备的话，建议把 innodb_flush_neighbors 的值设置成 0。

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

课后问题: 如果一个高配的机器，redo log设置太小，会发生什么情况。

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

这时候系统不得不停止所有更新，去推进checkpoint。

这时，你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。

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

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。

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

ON : 每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。 推荐

OFF : 表的数据放在系统共享表空间，也就是跟数据字典放在一起

一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

数据删除流程

假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小

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

如果我们用 delete 命令把整个表的数据删除，那么，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”

这些可以复用，而没有被使用的空间，看起来就像是“空洞”。实际上，插入数据也会造成空洞(发生页分裂的时候)。

经过大量增删改的表，都是可能是存在空洞的。(因为页分裂导致的索引不紧凑)
重建表可以帮助我们减少不必要的空间浪费

新建与表 A 结构相同的表 B，按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中

可以使用 alter table A engine=InnoDB 完成重建表
MySQL 5.5之前要求这个DDL期间 表A不能有数据更新 通过创建临时表来完成

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

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5. 用临时文件替换表 A 的数据文件
   
   上图的流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。(为了实现Online，MDL读锁不会阻塞增删改操作)

那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。

而对于一个大表来说，Online DDL最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个DDL过程来说，锁的时间非常短。对业务来说，就可以认为是Online的。

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用GitHub开源的gh-ost来做。

Online 和 inplace

在图4中，根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

所以，我现在问你，如果你有一个1TB的表，现在磁盘间是1.2TB，能不能做一个inplace的DDL呢？

答案是不能。因为，tmp_file也是要占用临时空间的。

我们重建表的这个语句alter table t engine=InnoDB，其实隐含的意思是：
alter table t engine=innodb,ALGORITHM=inplace;
跟inplace对应的就是拷贝表的方式了，用法是：

alter table t engine=innodb,ALGORITHM=copy;
当你使用ALGORITHM=copy的时候，表示的是强制拷贝表，对应的流程就是图3的操作过程。

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

optimize table、analyze table 和 alter table 三种方式区别

从 MySQL 5.6 版本开始，
1.alter table t engine = InnoDB（也就是 recreate）默认的就是上面的流程了；
2.analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
3.optimize table t 等于 recreate+analyze。

课后问题: 什么时候使用 alter table t engine=InnoDB 会让一个表占用的空间反而变大。
如果这个表，本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。
在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞。
在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。
假如是这么一个过程：
将表 t 重建一次；
插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；(此时的预留空间不足1/16))
这种情况下，再重建一次表 t，就可能会出现问题中的现象(因为需要预留空间1/16，导致所需的空间更大))。

14 | count(*)这么慢，我该怎么办？

count的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 count 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。 所以InnoDB表没办法向MyISAM一样在磁盘上保存对应的表的行数

对于 count 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，*MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
*
MyISAM 表虽然 count很快，但是不支持事务；

show table status 命令的输出结果里面也有一个TABLE_ROWS用于显示这个表当前有多少行，但是TABLE_ROWS是从索引的统计值估算来的(而索引的统计值是通过采样来估算的)，因此它也很不准, 官方文档说误差可能达到40%到50%。

InnoDB 表直接 count会遍历全表，虽然结果准确，但会导致性能问题。

所以当遇到count的性能问题时，我们只能自己去实现计数的方式

实现计数的方式

用缓存系统保存计数
问题:
1.缓存系统可能会丢失更新。redis的持久化可能会有数据丢失（取决于你持久化的配置） 可以在重启后执行一次count拿到最新的行数
2.有可能会和数据库的结果不完全一致 (缓存一致性问题)

在数据库保存计数
通过事务可以解决上述的计数不一致问题

不同count()的用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

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

原则:

1. server 层要什么就给什么；
2. InnoDB 只给必要的值；
3. 现在的优化器只优化了 count(星号) 的语义为“取行数”，其他“显而易见”的优化并没有做。

对于 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(星号) 肯定不是 null，按行累加。

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

课后问题: 计数放在Redis里面，不能够保证计数和MySQL表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。而把计数值也放在MySQL中，就解决了一致性视图的问题。

我们用了事务来确保计数准确。由于事务可以保证中间结果不被别的事务读到，因此修改计数值和插入新记录的顺序是不影响逻辑结果的。但是，从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？

并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。
知识点在《行锁功过：怎么减少行锁对性能的影响？》
因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少事务之间的锁等待，提升并发度。

15 | 答疑文章（一）：日志和索引相关问题

问题1: 两阶段期间发生crash

A时刻发生crash会回滚。在B时刻发生异常:
如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
a. 如果是，则提交事务；
b. 否则，回滚事务

问题2：MySQL 怎么知道 binlog 是完整的?

MySQL一个事务的bin log是有完整格式的：
statement 格式的 binlog，最后会有 COMMIT；
row 格式的 binlog，最后会有一个 XID event

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

问题3: redo log 和 binlog 是怎么关联起来的?

它们有一个共同的数据字段，叫 XID。
崩溃恢复的时候，会按顺序扫描 redo log：
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

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

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

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

历史原因:
InnoDB并不是MySQL的原生存储引擎。MySQL的原生引擎是MyISAM，设计之初就有没有支持崩溃恢复。
InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那就用InnoDB原有的redo log好了。

问题在于binlog 没有能力恢复“数据页”。
如果在图中标的位置，也就是 binlog2 写完了，但是整个事务还没有 commit 的时候，MySQL 发生了 crash。
重启后，引擎内部事务 2 会回滚，然后应用 binlog2 可以补回来；
但是对于事务 1 来说，系统已经认为提交完成了，不会再应用一次 binlog1。

但是，InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。
如果之后崩溃，要依赖于日志来恢复数据页。也就是说在图中这个位置发生崩溃的话，事务 1 也是可能丢失了的，而且是数据页级的丢失。此时，binlog 里面并没有记录数据页的更新细节，是补不回来的。

如果要说，那我优化一下binlog的内容，让它来记录数据页的更改可以吗？可以但是这其实就是又做了一个redo log出来。

所以，至少现在的binlog能力，还不能支持崩溃恢复。

我的理解:这跟InnoDB的设计有关，它使用了WAL技术，而redo log里记录的是已经更新到缓存但是还没有更新到磁盘的数据修改。但是bin log文件是记录了所有的操作，如果只使用bin log，那么就没办法知道哪些日志是需要用来恢复数据的，如果把之前已经提交的事务的bin log又拿过来执行，就又跟bin log设计的初衷违背了

问题6：那能不能反过来，只用redo log，不要binlog？

只从崩溃恢复的角度来讲是可以的，没有binlog关掉，系统依然是crash-safe的。

但是bin log尤其重要作用
一个是归档。redo log是循环写，起不到归档的作用。
一个就是MySQL系统依赖于binlog。MySQL系统高可用的基础，就是binlog复制。
还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费MySQL的binlog来更新自己的数据。关掉binlog的话，这些下游系统就没法输入了。

问题7: redo log一般设置多大？

redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log。如果是现在常见的几个TB的磁盘的话，可以直接将redo log设置为4个文件、每个文件1GB吧。

问题8: 正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。
在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

问题9: redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

redo log buffer 就是一块内存，用来先存 redo 日志的。
真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit 语句的时候做的
在事务执行过程中执行的生成的日志是需要先保存到redo log buffer的

业务设计问题

业务上有这样的需求，A、B 两个用户，如果互相关注，则成为好友。
设计上是有两张表，一个是 like 表，一个是 friend 表，like 表有 user_id、liker_id 两个字段，我设置为复合唯一索引即 uk_user_id_liker_id。
语句执行逻辑是这样的：
以 A 关注 B 为例：
第一步，先查询对方有没有关注自己（B 有没有关注 A）
select * from like where user_id = B and liker_id = A;
如果有，则成为好友insert into friend;没有，则只是单向关注关系insert into like;

但是如果 A、B 同时关注对方，会出现不会成为好友的情况。因为上面第 1 步，双方都没关注对方。第 1 步即使使用了排他锁也不行，因为记录不存在，行锁无法生效。

请问这种情况，在 MySQL 锁层面有没有办法处理？

CREATE TABLE `like` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`liker_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;

CREATE TABLE `friend` (
id` int(11) NOT NULL AUTO_INCREMENT,
`friend_1_id` int(11) NOT NULL,
`firned_2_id` int(11) NOT NULL,
UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_2_id`)
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

上述问题就是如下场景:

首先，要给“like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值 1、2、3

值是 1 的时候，表示 user_id 关注 liker_id;
值是 2 的时候，表示 liker_id 关注 user_id;
值是 3 的时候，表示互相关注。

当 A 关注 B 的时候，逻辑改成如下所示的样子：
应用代码里面，比较 A 和 B 的大小，如果 A<B，就执行下面的逻辑
(这里的on duplicate key update就是如果插入失败就更新的意思)

mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/*代码中判断返回的 relation_ship，
  如果是1，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
  */
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;

如果A>B,则执行如下:

mysql> begin; /*启动事务*/
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/*代码中判断返回的 relation_ship，
  如果是2，事务结束，执行 commit
  如果是3，则执行下面这两个语句：
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;

让“like”表里的数据保证 user_id < liker_id，这样不论是 A 关注 B，还是 B 关注 A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突

然后，insert … on duplicate 语句，确保了在事务内部，执行了这个 SQL 语句后，就强行占住了这个行锁，之后的 select 判断 relation_ship 这个逻辑时就确保了是在行锁保护下的读操作。

操作符 “|” 是按位或，连同最后一句 insert 语句里的 ignore，是为了保证重复调用时的幂等性。

这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是 like 表里面有一条关于 A 和 B 的记录，而且 relation_ship 的值是 3， 并且 friend 表里面也有了 A 和 B 的这条记录。

我的理解:
通过添加relation_ship字段，将A,B之间相互关注的操作都集中在同一记录里(前面因为是在操作不同的记录导致可以并发操作)，这样就可以利用InnoDB的行锁来解决同时关注的问题。
使用了"|" 按位或来简化代码操作，如果插入时的relation_ship和自己想要插入的不一致，那么就说明是相互关注，"|"运算后就得到3（相互关注的关系）

之所以要根据A,B的大小判断也是为了让A,B之间的关注操作的都是同一记录

课后问题:

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL primary key auto_increment,
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB;
insert into t values(1,2);

表t里有唯一的一行数据(1,2)。假设，我现在要执行：

mysql> update t set a=2 where id=1;

你会看到这样的结果：

仅从现象上看，MySQL内部在处理这个命令的时候，可以有以下三种选择：

1. 更新都是先读后写的，MySQL读出数据，发现a的值本来就是2，不更新，直接返回，执行结束；
2. MySQL调用了InnoDB引擎提供的“修改为(1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回；
3. InnoDB认真执行了“把这个值修改成(1,2)"这个操作，该加锁的加锁，该更新的更新。

你觉得实际情况会是以上哪种呢？你可否用构造实验的方式，来证明你的结论？进一步地，可以思考一下，MySQL为什么要选择这种策略呢？

第一个选项是，MySQL 读出数据，发现值与原来相同，不更新，直接返回，执行结束。这里我们可以用一个锁实验来确认。假设，当前表 t 里的值是 (1,2)。

session B 的 update 语句被 blocked 了，加锁这个动作是 InnoDB 才能做的，而读取操作不会锁记录，所以排除选项 1。

第二个选项是，MySQL 调用了 InnoDB 引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。这里用一个可见性实验来确认

session A 的第二个 select 语句是一致性读（快照读)，读取的数据是session A快照生成时的数据，session A的快照读按理是不能看见 session B 的更新的。现在它返回的是 (1,3)，表示它看见了某个新的版本，那么这个版本只能是 session A 自己的 update 语句做更新的时候生成。

答案应该是选项 3，即：InnoDB 认真执行了“把这个值修改成 (1,2)"这个操作，该加锁的加锁，该更新的更新。
然后你会说，MySQL 怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费 InnoDB 操作，多去更新一次了？
其实 MySQL 是确认了的。只是在这个语句里面，MySQL 认为读出来的值，只有一个确定的 (id=1), 而要写的是 (a=3)，只从这两个信息是看不出来“不需要修改”的。

我觉得还可以反向思维 : 如果在执行的时候不进行加锁和更新操作，那么此时其他会话就是有可能去操作这个数据的，这样一来数据就可能会产生不一致的问题

session A	session B
begin;
update t set a=2 where id=1;
	update t set a=5 where id=1; //blocked
update t set a=a+1 where id=1;
commit;

实际操作后的数据是(1,3)

说明在session A执行update t set a=2 where id=1;的时候就已经锁住了对应的记录，否则就会被session B的操作修改该记录