MySQL(11-15)

11. 怎么给字符串加索引

场景引入
现在，几乎所有的系统都支持邮箱登录，那么如何在邮箱这样的字段上建立合理的索引呢？
假设，你现在维护一个支持邮箱登录的系统，用户表定义如下：

create table Suser (
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb;

由于要使用邮箱登录，所以业务代码中一定会出现类似的语句：

select f1, f2 from Suser where email = 'xxx';

如果email这个字段上没有索引，那么这个资源就只能做全表扫描。
同时，MySQL是支持前缀索引的，也就是说，可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

比如，这两个在email字段上创建索引的语句：

alter table Suser add index index1(email);
alter table Suser add index index2(email(6));

第一个语句创建的index1索引里面，包含了每个记录的整个字符串；而第二个语句创建的index2索引里面，对于每个记录都是只取前6个字节。
两种不同的定义，示意图如下：

图1. email索引结构

图2. email(6) 索引结构

从图中可以看到，由于email(6)这个索引结构中每个邮箱字段都只取前6个字节，所以占用的空间会更小，这就是前缀索引的优势。
但，这同时带来的损失是，可能会增加额外的记录扫描次数。

接下来，看看下面这个索引语句，在这两个索引定义下分别是怎么执行的：

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

使用index1，执行顺序如下：

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

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
使用index2，执行顺序如下：

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
2. 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取index上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行判断，这次值正确，将这行记录加入结果集；
4. 重复上一步，知道在index2上去到的值不是’zhangs’时，循环结束。

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

通过以上对比，很容易发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。
但是，如果定义index2为email(7)，也就是说取email字段的前7个字节来构建索引的话，即满足前缀’zhangss’的就只有一个，也能够直接查到ID2，只扫描一行就结束了。

也就是说使用前缀索引，定义好长度，就可以做到节省空间，又不用额外增加太多的查询成本。
建立索引的时候，关注的是区分度，区分度越高越好。因为区分度越高，重复的键值越好。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

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

select count(distinct email) as l from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看4~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 SUer;

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

前缀索引对覆盖索引的影响
前缀索引可能会增加扫描行数，这会影响到性能。其实，前缀索引的影响不止于此，再看另外一个场景。
看看这个SQL语句：

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

与前面例子中的SQL语句相比，这个语句只要求返回id和email字段。
所以，如果使用index1（email整个字符串的索引结构）的话，可以利用覆盖索引，从index1查到结果后直接就返回了，不需要回到ID索引再去查一次。而如果使用index2（即email(6)的索引结构），就不得不回到ID搜因再去判断email字段的值。
即时你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到id索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。
也就是说使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是在选择使用使用前缀索引时需要考虑的一个因素。

其他方式
对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，我们要怎么办呢？
比如，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。
假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为6的前缀索引的话，这个索引的区分度就非常低了。
按照我们前面的说法，可能需要创建长度为12以上的前缀索引，才能够满足区分度要求。
但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢？既可以占用更小的空间，也能达到相同的查询效率。
答案是有的。

第一种方式是使用倒序存储。如果你存储身份证号的时候把它倒过来存，每次查询的时候，可以这么写：

select field_list fromt where id_card = reverse('input_id_card_string');

由于身份证号的最后6位没有重复逻辑，所以最后这6位很可能就提供了足够的区分度。
第二种方式是使用hash字段。你可在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

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

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

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

这样，索引的长度变成了4个字节，比原来小了很多。

接下来，我们再一起看看使用倒序存储和使用hash字段这两种方法的异同点。
相同点：都不支持范围查询。
不同点：

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

小结
这一节学习了字符串创建索引的场景：

1. 直接创建完整索引，这样可能比较占用空间；
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

小问题
如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是“学号@gmail.com”，而学号的规则是：

• 1-3：所在城市编号
• 4-6：学校编号
• 7-10：入学年份
• 11-15：顺序编号

系统登陆的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登陆验证这个行为的话，你会怎么设计这个登录名的索引呢？
解析：
无论是是正向还是反向的前缀索引，重复度都比较高。因为维护的只是一个学校的，因此前面6位其实是固定的，邮箱后缀都是@gmail.com，因此可以存入年份+顺序编号，它们的长度是9位。
在此基础上，可以用数字类型来存这9位数字。这样只需要占用四个字节。
另一个方面，一个学校几十年也不到200万学生，数据量不大，可以直接使用原来的字符串作为索引。

12. 为什么我的MySQL会抖一下

场景引入
一条SQL语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。
看上去，这就像是数据库抖了一下。

SQL语句为什么变慢了
InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫做redo log（重做日志），也就是《孔乙己》里咸亨酒店掌柜用来记账的粉板，在更新内存写完redo log后，就返回给客户端，本次更新成功。
做下类比的话，掌柜记账的账本的是数据文件，记账用的粉板是日志文件（redo log），掌柜的记忆就是内存。
掌柜总要找时间把账本更新一下，这对应的就是把内存里的数据写入磁盘的过程，术语就是flush。在这个flush操作执行之前，孔乙己的赊账总额，其实跟掌柜手中账本里面的记录是不一致的。因为孔乙己今天的赊账金额还只在粉板上，而账本里的记录是老的，还没把今天的赊账算进去。

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

假设孔乙己已欠账10文，这次又要赊9文，那么赊账的操作过程如下：

图3. “孔乙己赊账”更新和flush过程

回到开头的问题，不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而MySQ偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。
那么，什么情况会引发数据库的flush过程？

继续用咸亨酒店掌柜的这个例子，想一想：掌柜在什么情况下会把粉板上的赊账记录改到账本上？

第一种场景是，粉板满了，记不下了。这时候如果再有人来赊账，掌柜就只得放下手里的活儿，将粉板上的记录擦掉一些，留出空位以便继续记账。当然在擦掉之前，他必须先将正确的账目记录到账本中才行。

图4. redo log状态图

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

第二种场景是，这一天生意太好，要记住的事情太多，掌柜发现自己快记不住了，赶紧找出账本把孔乙己这笔账先加进去。
这种场景，对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
那么，这时候难道不能直接淘汰内存，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？这里其实是从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

• 在内存里存在，内存里就是正确的结果，直接返回。
• 内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。

第三种场景是，生意不忙的时候，或者打烊之后。这时候柜台没事，掌柜闲着也是闲着，不如更新版本。
这种场景，对应的就是MySQL认为系统“空闲”的时候。当然，MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要合理安排时间，即使是“生意好”的时候。也要见缝插针地找时间，只要有机会就刷一点“脏页”。

第四种场景是，年底了咸亨酒店要关门几天，需要把账结清一下。这时候掌柜要把所有账都记到账本上，这样过完年重新开张的时候，就能就这账本明确账目情况了。这种场景，对应的就是MySQL正常关闭的iqngk。这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

四种场景对性能的影响
其中，第三种情况是属于MySQL空闲时的操作，这时系统没什么压力，而第四种场景是数据库本来就要关闭了。这两种情况，你不会太关注“性能”问题。所以这里，我们主要来分析一下前两种场景下的性能问题。

第一种是“redo log写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

1. 还没有使用的；
2. 使用了并且是干净页；
3. 使用了并且是脏页。

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
而当要读入的数据页没有在内存的时候，就必须到缓冲池申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉；如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页，就必须将脏页先刷到磁盘，变成干净页才能复用。

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

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

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

InnoDB刷脏页的控制策略
接下来，聊聊InnoDB脏页的控制策略，以及和这些策略相关的参数。

首先，要明确InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。
这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。这个值我建议你设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试，下面的语句就可以测试磁盘随机读写：

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

其实，因为没能正确地设置innodb_io_capacity参数，而导致的性能问题也比比皆是。
innodb_io_capactity的值设置的比较低，InnoDB会认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。

虽然我们现在已经定义了“全力刷脏页”的行为，但平时总不能一直是全力刷吧？毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。所以接下来，我们就一起看看InnoDB怎么控制引擎按照“全力”的百分比来刷脏页。
如果设计策略控制刷脏页的速度，会参考哪些因素呢？
这个问题可以这么想，如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是redo log写满。
所以，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%来控制刷脏页的速度。

上述计算流程图示如下：

图5. InnoDB刷新页速度策略

现在我们知道了，InnoDB会在后台刷新脏页，而刷脏页的过程是要讲内存写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了 一下的原因。
要尽量避免这种情况，你就要合理设置innodd_io_capacity的值，并且平时要多关注脏页比例，不要让它接近75%。
其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，具体的命令参考如下：

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

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

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

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

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

小结
这一节，延续了2节中的WAL概念，解释了这个机制后续需要的刷脏页操作和执行实际。利用WAL技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。
但是，由此也带来了内存脏页的问题。脏页会被后台线程自动flush，也会有数据页淘汰而出发flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。我们也学习了控制刷脏页的方法和对弈的监控方式。

小问题
一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，正常会建议你将redo log设置成4个1GB的文件。
但如果你在配置的时候不慎将redo log设置成1个100M的文件，会发生什么情况呢？又为什么会出现这样的情况呢？
解析：
每次事务提交都要写redo log，如果设置太小，很快就会被写满，write pos一直追着CP。这时候系统不得不停止所有更新，去推进checkpoint。
这时，能观察到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下降。

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

场景引入
数据库占用空间太大，把最大的一个表删除一半数据，但是表文件的大小还是没变？

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

接下来，先说明为什么简单地删除表数据达不到表空间回收的效果，然后再介绍正确回收空间的方法。

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

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典在一起；
2. 这个参数设置ON表示的是，每个InnoDB表数据存储一个.ibd为后缀的文件中。

从MySQL 5.6.6版本开始，它的默认值就是ON了。

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

我们在删除整个表的时候，可以使用drop table命令回收表空间。但是，我们遇到的更多的删除数据的场景在删除某些行，这时就遇到了本节开头的问题：表中的数据被删除了，但是表空间却没有被回收。

数据删除流程
再看一下InnoDB中的一个索引的示意图：

图6. B+树索引示意图

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

InnoDB的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？
答案是，整个数据页就复用了。
但是，数据页的复用跟记录的复用是不同的。
记录的复用，只限于符合范围条件的数据。比如上面的这个例子，R4这条记录被删除后，如果插入一个ID是400的行，可以直接复用这个空间。但如果插入的是一个ID是800的行，就不能复用这个位置了。
而当整个页从B+树里面摘掉以后，可以复用到任何位置。以上图为例，如果将数据页page A上的所有记录删除以后，page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，page A是可以被复用的。
如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

进一步地，如果我们用delete命令把整个表的数据删除呢？
结果就是，所有的数据页都会被标记为可复用。但是，磁盘上文件不会变小。
delete命令其实只是把记录的位置或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令时不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。
如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

假设上图中page A已经满了，这是再插入一行数据，会怎样呢？

图7. 插入数据导致页分裂

可以看到，由于page A满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面page B来保存数据了。页分裂完成后，page A的末尾就留下了空洞。
另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。
也就是说，经过大量增删改的表，都是可能存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。
而重建表，就可以达到这样的目的。

重建表
试想一下，如果现在有一个表A，需要做空间收缩，为了把表中存在的空洞去掉，可以怎么做？

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

由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。
这里，你可以用alter table A engine=InnoDB来重建表。在MySQL5.5版本前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要自己创建，MySQL会自动完成转存数据、交换表名、删除旧表的操作。

图8. 改锁表DDL

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

而在MySQL5.6版本开始引入的Online DDL，对这个操作流程做了优化。
简单描述一下引入Online DDL之后，重建表的流程：

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

图9. Online DDL

对比图3和图4可以看到，Online DDL与普通DDL的不同之处在于：由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A做增删改操作，这也就是Online DDL名字的来源。

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

而对于一个大表来说，Online DDL最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个DDL过程来说，锁的时间非常短。对业务来说，就可以认为是Online的。
需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用Github开源的gh-ost来做。

Online和inplace
说到Online，还要澄清一下它和另一个DDL有关的、容易混淆的概念inplace的区别。

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

所以，如果有一个1TB的表，现在磁盘空间是1.2TB，能不能做一个inplace的DDL呢？
答案是不能的。因为，tmp_file也是要占用临时空间的。

我们重建表的这个语句alter table engine = InnoDB，其实隐含的意思是：

alter table engine = InnoDB, ALGORITHM = inplace;

跟inplace对应的就是拷贝表的方式了，用法是：

alter table engine = InnoDB, ALGORITHM = copy;

当你使用ALGORITHM = copy的时候，表示的是强制拷贝表，对应的强制拷贝表，对应的流程就是图3的操作流程。

但是，这样让我们觉得，inplace和Online是不是就是一个意思？
其实不是的，只是在重建表这个逻辑中刚好是这样而已。

比如，如果我要给InnoDB表的一个字段加全文索引，写法是：

alter table t add FULLTEXT(field_name);

这个过程是inplace的，但会阻塞增删改操作，是非Online的。

概括Online和inplace这两个逻辑直接的关系，应该这样描述：

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

最后，再延伸一下。
optimize table、analyze table和alter table这三种方式重建表的区别：

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

小结
本节，我们学习了数据库中收缩表空间的方法。
如果要收缩一个表，只是delete掉里面不用的数据的话，表文件的大小是不会变的，你还要通过alter table命令重建表，才能达到表文件变小的目的。Online DDL的方式是可以考虑在业务低峰期使用的，而在MySQL5.5及之前的版本，这个命令是阻塞DML的，这个需要特别小心。

小问题
假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：

1. 一个表t文件大小为1TB；
2. 对这个表执行alter table t engine=InnoDB；
3. 发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB。

你觉得可能是什么原因呢？
解析：
首先，就是这个表，本身可能就已经没有空洞了，即时重建也不会收缩空间。
并且，在DDL期间，如果干好友外部的DML再执行，这期间可能会引入一些新的空洞。
另外，在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

假如是这么一个过程：

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

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

场景引入
在开发系统的时候，你可能经常需要计算一个表的行数，比如一个交易系统的所有变更记录总数。
随着系统中的记录数越来越多，select count(*) from t语句执行得也会越来越慢。

count(*)的实现方式
首先要明确的是，在不同的MySQL引擎中，count(*)有不同的实现方式。

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高；
而InnoDB引擎就麻烦了，它执行count()的时候，需要把数据一行一行地引擎里面读出来，然后累积计数。
这里需要注意的是，这里讨论的是没有过滤条件的count(*)，如果加了where条件的话，MyIASM表也是不能返回得这么快的。

那为什么InnoDB不跟MyISAM一样，也把数字存在来呢？
这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因。
InnoDB表“应该返回多少行”也是不确定的。

假设表t现在有10000条记录，我们设计了三个用户并行的会话。

• 会话A：先启动事务，查询表的总行数；
• 会话B：启动事务，插入一行记录后，查询表的总行数；
• 会话C：先启动一个单独的语句，插入一行记录后，查询表的总行数。

我们假设从上到下是按照时间顺序执行的，同一行一句是在同一时刻执行的。

图10. 会话A、B、C的执行流程

可以看到，在最后一个时刻，三个会话A、B、C会同时查询表t的总行数，但拿到的结果却不同。

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

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

show table status命令的输出结果里面也有一个TABLE_ROWS用于显示这个表当前有多少行，这个命令执行很快，那么这个TABLE_ROWS能代替count(*)吗？

在第10节中说过，索引统计的值是通过采样来估算的。实际上，TABLE_ROWS就是从这个采样估算得来的，因此它也不准。有多不准呢，官方文档说误差可能大袋40%~50%。所以，show table status命令显示的行数也不能直接使用。

小结一下：

• MyISAM表虽然count(*)很快，但是不支持事务；
• show table status命令虽然返回很快，但是不准确；
• InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

如果现在有一个页面经常要显示交易系统的操作记录总数，到底该怎么办呢？
答案是，我们只能自己计数。
接下来，我们讨论一下，看看自己计数有哪些方法，以及每种方法的优缺点。

用缓存系统保存计数
对于更新很频繁的库来说，第一时间很容易想到，用缓存系统来支持。

可以用一个Redis服务来保存这个表的总行数。这个表每被插入一行Redis计数就加1，每被删除一行Redis计数就减1。这种方式下，读和更新操作都很快，但是会存在一个问题：缓存系统可能会丢失更新。
Redis的数据不能永久地留在内存里，所以你会找一个地方把这个值定期地持久化存储起来。但即使这样，仍然可能丢失更新。试想如果刚刚在数据表中插入了一行，Redis中保存的值也加了1，然后Redis异常重启了，重启后你要从存储Redis数据的地方把这个值读回来，而刚刚加1的这个计数操作却丢失了。

当然，这还是有解的。比如，Redis异常重启以后，到数据库里面单独执行一次count(*)获取真实的行数，再把这个值写会到Redis里就可以了。异常重启毕竟不是经常出现的情况，这一次全表扫描的成本，还是可以接受的。

但实际上，将技术保存在缓存系统中的方式，还不只是丢失更新的问题。即使Redis正常工作，这个值还是逻辑上不精确的。

设想一下，要显示操作记录的总数，同时还要线上最近操作的100条记录。那么，这个页面的逻辑就需要先到Redis里面取出计数，再到数据表里面取出数据记录。
我们是这么定义不精确的：

1. 一种是，查到的100行结果里面有最新插入记录，而Redis的计数里还没加1；
2. 另一种是，查到的100行结果里没有最新插入的记录，而Redis的技术已经加了1。

这两种情况，都是逻辑不一致的。

看看这个时序图：

图11. 会话A、B执行时序图

图2中，会话A是一个插入交易记录的逻辑，往数据表里插入一行R，然后Redis技术加1；会话B就是查询页面显示时需要的数据。
在图2的时序里，在T3时刻会话B来查询的时候，会显示出新插入的R这个记录，但是Redis的计数还没加1。这时候，就会出现我们说的数据不一致。

你一定会说，这是我们执行新增逻辑时候，是先写数据表，再改Redis计数。而读的时候是先读Redis，再读数据表，这个顺序是相反的。那么，如果保持顺序一样的话，是不是就没问题了？我们现在把会话A的更新顺序换一下，再看看执行结果。

图12. 调整顺序后，会话A、B的执行时序图

你会发现，这时候反过来了，会话B在T3时刻查询的时候，Redis计数加1了，但还查不到新插入的R这一行，也是数据不一致的情况。

在并发系统里面，我们是无法精确控制不同线程的执行时刻的，因为存在图中的这种操作序列，所以，我们说即时Redis正常操作，这个计数值还是逻辑上不精确的。

在数据库保存计数
根据上面的分析，用缓存系统保存计数有丢失数据和计数不精确的问题。那么，如果我们把这个计数直接放到数据库里单独的一张计数表C中，又会怎么样呢？

首先，这解决了崩溃丢失的问题，InnoDB是支持崩溃恢复不丢数据的。
然后，我们再看看能不能解决计数不精确的问题。
我们利用事务特性，来解决问题。

图13. 会话A、B的执行时序图

我们来看下现在的执行结果。虽然会话B的读操作仍然是在T3执行的，但是因为这时候更新事务还没有提交，所以计数值加1这个操作对会话B还不可见。

因此，会话B看到的结果里，查计数值和“最近100条记录”看到的结果，逻辑上就是一致的。

不同的count用法
那么对于select count(?) from t这样的查询语句里面，count(*)、count(id)、count(field)和count(1)等不同的用法，性能和区别在哪里呢？

分析性能差别的时候，记住以下原则：

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

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

对于count(1)，InnoDB引擎会遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，可以对比出来，count(1)执行得count(id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(field)：

1. 如果这个字段是定义为not null的话，一行行从记录中里面读出这个字段，判断不能为null，按行累加；
2. 如果这个字段定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。
但是count()是例外，并不会把全部字段取出来，而是专门做了优化，不取值。
count()肯定不是null，按行累加。

结论：按照效率的话，count(field) < count(id) < count(1) ≈ count(*)。

小结
这一节学习了获取表行数的两种方法。在不同引擎中count(*)的实现方式是不一样的，也了解到用缓存系统来存储计数值存在的问题。
其实，把计数放在Redis里面，不能够保证计数和MySQL表里数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。而把计数值放在MySQL中，就解决了一致性视图的问题。
InnoDB引擎支持事务，我们利用好事务的原子性和隔离性，就可以简化在业务开发时的逻辑。这也是InnoDB引擎备受青睐的原因之一。

小问题
在上面的讨论中，我们用了事务来确保计数准确。由于事务可以保证中间结果不被别的事务读到，因此修改计数值和插入新记录的顺序是不影响逻辑结果的。但是，从并发系的性能考虑，在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？
解析：
从并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。
因为更新计数表涉及到行锁的竞争，先先插入再更新能最大程度减少事务之间的锁等待，提升了并发度。

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

日志相关问题

回顾两阶段提交，图示如下：

图14. 两阶段提交示意图
在两阶段提交的不同时刻，MySQL异常重启会出现什么现象。

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

那么，对于时刻B，也就是binlog写完，redo log还没commit前发生crash，那崩溃修复的时候MySQL会怎么处理？

我们先来看一下崩溃修复时的判断规则：

1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；
2. 如果redo log里面的事务只有prepare，则判断对应的事务binlog是否存在并完整：
   1. 如果是，则提交事务；
   2. 否则，回滚事务。

这里，时刻B发生crash对应的就是2a的情况，崩溃恢复过程中事务会被提交。

追问1：MySQL怎么知道binlog是完整的？
回答：
一个事务的binlog是有完整格式的：

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

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

追问2：redo log和binlog是怎么关联的？
回答：
它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

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

追问3：处于prepare阶段的redo log加上完整的binlog，重启就能恢复，MySQL为什么要这么设计？
回答：
这个涉及到主备一致性问题。在时刻B，也就是binlog写完以后MySQL发生崩溃，这时候binlog已经写入了，之后就会被从库使用。
所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

追问4：如果这样的话，为什么还要两阶段提交呢？干脆先redo log写完，再写binlog。崩溃修复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？
回答：
其实，两阶段提交时经典的分布式系统问题，并不是MySQL独有的。
对于InnoDB引擎来说，如果redo log提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。而如果redo log直接提交，然后binlog写入的时候，InnoDB又回滚不了，数据和binlog又不一致了。
两阶段提交就是为了给所有人一个机会，当每个人都OK的时候，再一起提交。

追问5：不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩溃修复，又能支持归档，不就可以了？
回答：
只保留binlog，然后提交流程改成这样：数据更新到内存 -> 写binlog -> 提交事务。
是不是这样也可以提供崩溃恢复的能力？答案是不可以的。
如果说历史原因的话，那就是InnoDB并不是MySQL的原生存储引擎。MySQL的原生引擎是MyISAM，设计之初就没有支持崩溃修复。
InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。
InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力只用binlog来实现崩溃恢复的流程。

图15. 只用binlog支持崩溃恢复

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

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

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

追问8：正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？
回答：
实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由redo log更新过去”的情况。

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

追问9：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会自己启动一个事务，在语句执行完成的时候提交。

业务设计问题

问题是这样的

业务上有这样的需求，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步即时使用了排它锁也不行，因为记录不存在，行锁无法生效。 请问这中情况，在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;

在并发场景下，A和B同时关注对方，以时刻顺序表的形式，把这两个事务的执行语句列出来：

图16. 并发“喜欢”逻辑操作顺序

由于一开始A和B之间没有关注关系，所以两个事务里面的select语句查出来的结果都是空。
因此，session1的逻辑就是“既然B没有关注A，那就只插入一个单向关注关系”。session2也同样是这个逻辑。
这个结果对业务来说就是bug了。因为在业务设定里面，这两个逻辑都执行完成以后，是应该在friend表里面插入一行记录的。

解决方案如下：
要给“like”表增加一个字段，比如叫作relation_ship，并设为整型，取值1、2、3。

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

然后，当A关注B的时候，逻辑改成如下。
应用代码里面，比较A和B的大小，如果A<B，就执行下面的逻辑：

begin;
-- A、B唯一索引生效，即已经存在A和B记录，那么执行更新
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
-- 查询A和B的关系
select relation_ship from `like` where user_id=A and liker_id=B;
-- 代码中判断返回的relation_ship
-- 如果是1，事务结束，执行commit
-- 如果是3，则执行下面这个语句再commit
insert ignore into friend(friend_1_id, friend_2_id) values(A, B);

如果A>B，则执行下面的逻辑：

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，则执行下面这个语句再commit;
insert ignore into friend(friend_1_id, friend_2_id) values(B, A);

这个设计里，让“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的这条记录。
这个例子中，按照这个设计，业务根本就是“保证我一定会插入重复数据，数据库一定要有唯一性约束”，这时就没啥好说的了，唯一索引建起来吧。

小问题
我们创建了一个简单的表t，并插入一行，然后对这一行做修改：

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)。假设，我现在要执行：

update t set a = 2 where id = 1;

你会看到这样的结果：

图17. 执行结果

结果显示，匹配了一行，修改了0行。
仅从现象上看，MySQL内部在处理这个命令的时候，可以有以下三种选择：

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

解析：
对于第一个选项，MySQL读出数据，发现值与原来相同，不更新，直接返回，执行结束。
假设，当前表t里的值是（1, 2）。

图18. 锁验证方式

session B的update语句被blocked了，加锁这个动作是InnoDB才能做的，所以排除选项1。

对于第二个选项，MySQL调用了InnoDB引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。
假设，当前表t里的值是(1, 2)。

图19. 可见性验证方式

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

所以，答案是选项3，即InnoDB认真执行了“把这个值修改成了(1, 2)”这个草祖宗，该加锁的加锁，该更新的更新。