11、讲怎么给字符串字段加索引

 
mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));
 
1、创立的 index1 索引外面，蕴含了每个记录的整个字符串；
 
2、创立的 index2 索引外面，对于每个记录都是只取前 6 个字节 (前缀索引)
 
​       应用前缀索引就用不上笼罩索引对查问性能的优化了，这也是你在抉择是否应用前缀索引时须要思考的一个因素
 
3、其余形式
 
​     第一种形式是应用倒序存储。如果你存储身份证号的时候把它倒过去存
 
mysql> select field_list from t where id_card = reverse(‘input_id_card_string’);
 
​     第二种形式是应用 hash 字段。你能够在表上再创立一个整数字段，来保留身份证的校验码，同时在这个字段上创立索引
 
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
 
​       首先，它们的相同点是，都不反对范畴查问。倒序存储的字段上创立的索引是依照倒序字符串的形式排序的，曾经没有方法利用索引形式查出身份证号码在[ID_X, ID_Y] 的所有市民了。同样地，hash 字段的形式也只能反对等值查问。
 
它们的区别，次要体现在以下三个方面：
 

1. 从占用的额定空间来看，倒序存储形式在主键索引上，不会耗费额定的存储空间，而 hash 字段办法须要减少一个字段。当然，倒序存储形式应用 4 个字节的前缀长度应该是不够的，如果再长一点，这个耗费跟额定这个 hash 字段也差不多对消了。

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

1. 从查问效率上看，应用 hash 字段形式的查问性能绝对更稳固一些。因为 crc32 算进去的值尽管有抵触的概率，然而概率十分小，能够认为每次查问的均匀扫描行数靠近 1。而倒序存储形式毕竟还是用的前缀索引的形式，也就是说还是会减少扫描行数。

 
   # 小结
 
   在明天这篇文章中，我跟你聊了聊字符串字段创立索引的场景。咱们来回顾一下，你能够应用的形式有：
 
   1. 间接创立残缺索引，这样可能比拟占用空间；
   2. 创立前缀索引，节俭空间，但会减少查问扫描次数，并且不能应用笼罩索引；
   3. 倒序存储，再创立前缀索引，用于绕过字符串自身前缀的区分度不够的问题；
   4. 创立 hash 字段索引，查问性能稳固，有额定的存储和计算耗费，跟第三种形式一样，都不反对范畴扫描。
 

12 讲为什么我的 MySQL 会“抖”一下

 
​      我为你介绍了 WAL 机制。当初你晓得了，InnoDB 在解决更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志），也就是《孔乙己》里咸亨酒店掌柜用来记账的粉板，在更新内存写完 redo log 后，就返回给客户端，本次更新胜利。
 
 当内存数据页跟磁盘数据页内容不统一的时候，咱们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就统一了，称为“洁净页”
 

 
回到文章结尾的问题，你不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶然“抖”一下的那个霎时，可能就是在刷脏页（flush）。
 
那么，什么状况会引发数据库的 flush 过程呢
 
咱们还是持续用咸亨酒店掌柜的这个例子，想一想：掌柜在什么状况下会把粉板上的赊账记录改到账本上？
 

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

  这个场景，对应的就是 InnoDB 的 redo log 写满了。这时候零碎会进行所有更新操作，把 checkpoint 往前推动，redo log 留出空间能够持续写。我在第二讲画了一个 redo log 的示意图，这里我改成环形，便于大家了解。
 

 
图 2 redo log 状态图
 
checkpoint 可不是轻易往前批改一下地位就能够的。比方图 2 中，把 checkpoint 地位从 CP 推动到 CP’，就须要将两个点之间的日志（浅绿色局部），对应的所有脏页都 flush 到磁盘上。之后，图中从 write pos 到 CP’之间就是能够再写入的 redo log 的区域。
 

• 第二种场景是，这一天生意太好，要记住的事件太多，掌柜发现自己快记不住了，连忙找出账本把孔乙己这笔账先加进去。

  这种场景，对应的就是零碎内存不足。当须要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页应用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
  你肯定会说，这时候难道不能间接把内存淘汰掉，下次须要申请的时候，从磁盘读入数据页，而后拿 redo log 进去利用不就行了？这里其实是从性能思考的。如果刷脏页肯定会写盘，就保障了每个数据页有两种状态：
  – 一种是内存里存在，内存里就必定是正确的后果，间接返回；
  – 另一种是内存里没有数据，就能够必定数据文件上是正确的后果，读入内存后返回。
    这样的效率最高。

• 第三种场景是，生意不忙的时候，或者打烊之后。这时候柜台没事，掌柜闲着也是闲着，不如更新账本。

  这种场景，对应的就是 MySQL 认为零碎“闲暇”的时候。当然，MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要正当地安顿工夫，即便是“生意好”的时候，也要见缝插针地找工夫，只有有机会就刷一点“脏页”。

• 第四种场景是，年底了咸亨酒店要关门几天，须要把账结清一下。这时候掌柜要把所有账都记到账本上，这样过完年从新倒闭的时候，就能就着账本明确账目状况了。

  这种场景，对应的就是 MySQL 失常敞开的状况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就能够间接从磁盘上读数据，启动速度会很快。
 
你能够剖析一下下面四种场景对性能的影响。
 
其中，第三种状况是属于 MySQL 闲暇时的操作，这时零碎没什么压力，而第四种场景是数据库原本就要敞开了。这两种状况下，你不会太关注“性能”问题。所以这里，咱们次要来剖析一下前两种场景下的性能问题。
 
第一种是“redo log 写满了，要 flush 脏页”，这种状况是 InnoDB 要尽量避免的。因为呈现这种状况的时候，整个零碎就不能再承受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
 
第二种是“内存不够用了，要先将脏页写到磁盘”，这种状况其实是常态。InnoDB 用缓冲池（buffer pool）治理内存，缓冲池中的内存页有三种状态：
 

• 第一种是，还没有应用的；
• 第二种是，应用了并且是洁净页；
• 第三种是，应用了并且是脏页。

 
InnoDB 的策略是尽量应用内存，因而对于一个长时间运行的库来说，未被应用的页面很少。
 
而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不应用的数据页从内存中淘汰掉：如果要淘汰的是一个洁净页，就间接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成洁净页后能力复用。
 
所以，刷脏页尽管是常态，然而呈现以下这两种状况，都是会显著影响性能的：
 

1. 一个查问要淘汰的脏页个数太多，会导致查问的响应工夫显著变长；
2. 日志写满，更新全副堵住，写性能跌为 0，这种状况对敏感业务来说，是不能承受的。

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

参数 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，是举荐做法，咱们接下来的探讨都是基于这个设置开展的。
 

数据删除流程

 
咱们先再来看一下 InnoDB 中一个索引的示意图。在后面第 4 和第 5 篇文章中，我和你介绍索引时已经提到过，InnoDB 里的数据都是用 B + 树的构造组织的。
 

 
图 1 B+ 树索引示意图
 
假如，咱们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个地位。然而，磁盘文件的大小并不会放大。
 
当初，你曾经晓得了 InnoDB 的数据是按页存储的，那么如果咱们删掉了一个数据页上的所有记录，会怎么样？
 
答案是，整个数据页就能够被复用了。
 
数据页的复用跟记录的复用是不同的。
 
记录的复用，只限于合乎范畴条件的数据。比方下面的这个例子，R4 这条记录被删除后，如果插入一个 ID 是 400 的行，能够间接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个地位了。
 
而当整个页从 B + 树外面摘掉当前，能够复用到任何地位。以图 1 为例，如果将数据页 page A 上的所有记录删除当前，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录须要应用新页的时候，page A 是能够被复用的。
 
如果相邻的两个数据页利用率都很小，零碎就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。
 
进一步地，如果咱们用 delete 命令把整个表的数据删除呢？后果就是，所有的数据页都会被标记为可复用。然而磁盘上，文件不会变小。
 
你当初晓得了，delete 命令其实只是把记录的地位，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些能够复用，而没有被应用的空间，看起来就像是“空洞”。
 

重建表

 
试想一下，如果你当初有一个表 A，须要做空间膨胀，为了把表中存在的空洞去掉，你能够怎么做呢？
 
你能够新建一个与表 A 构造雷同的表 B，而后依照主键 ID 递增的程序，把数据一行一行地从表 A 里读出来再插入到表 B 中。
 
因为表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果咱们把表 B 作为长期表，数据从表 A 导入表 B 的操作实现后，用表 B 替换 A，从成果上看，就起到了膨胀表 A 空间的作用。
 
这里，你能够应用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟咱们后面形容的差不多，区别只是这个长期表 B 不须要你本人创立，MySQL 会主动实现转存数据、替换表名、删除旧表的操作。
 

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 t engine=InnoDB，其实隐含的意思是：
 

alter table t engine=innodb,ALGORITHM=inplace;

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

alter table t engine=innodb,ALGORITHM=copy;

 
当你应用 ALGORITHM=copy 的时候，示意的是强制拷贝表，对应的流程就是图 3 的操作过程。
 
但我这样说你可能会感觉，inplace 跟 Online 是不是就是一个意思？
 
其实不是的，只是在重建表这个逻辑中刚好是这样而已。
 
比方，如果我要给 InnoDB 表的一个字段加全文索引，写法是：
 

alter table t add FULLTEXT(field_name);

 
这个过程是 inplace 的，但会阻塞增删改操作，是非 Online 的。
 
如果说这两个逻辑之间的关系是什么的话，能够概括为：
 

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

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

 
 
 
在这篇文章的评论区外面，大家都提到了一个点，就是这个表，自身就曾经没有空洞的了，
 
比如说刚刚做过一次重建表操作。
 
在 DDL 期间，如果刚好有内部的 DML 在执行，这期间可能会引入一些新的空洞。
 
@翱翔 提到了一个更粗浅的机制，是咱们在文章中没说的。在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1 /16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。
 
如果是这么一个过程：
 

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

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

count(*)的实现形式

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

• MyISAM 引擎把一个表的总行数存在了磁盘上，因而执行 count(*)的时候会间接返回这个数，效率很高；
• 而 InnoDB 引擎就麻烦了，它执行 count(*)的时候，须要把数据一行一行地从引擎外面读出来，而后累积计数。

 
那 为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？
 
这是因为即便是在同一个时刻的多个查问，因为多版本并发管制（MVCC）的起因，InnoDB 表“应该返回多少行”也是不确定的。这里，我用一个算 count(*)的例子来为你解释一下。
 
假如表 t 中当初有 10000 条记录，咱们设计了三个用户并行的会话。
 

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

 
咱们假如从上到下是依照工夫程序执行的，同一行语句是在同一时刻执行的。
 

 
图 1 会话 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 篇文章《MySQL 为什么有时候会选错索引？》中我提到过，索引统计的值是通过采样来估算的。实际上，TABLE_ROWS 就是从这个采样估算得来的，因而它也很不准。有多不准呢，官网文档说误差可能达到 40% 到 50%。所以，show table status 命令显示的行数也不能间接应用。
 

 用缓存零碎保留计数

 
将计数保留在缓存零碎中的形式，会呈现失落更新的问题。即便 Redis 失常工作，这个值还是逻辑上不准确的
 

在数据库保留计数

 
首先，这解决了解体失落的问题，InnoDB 是反对解体复原不丢数据的。
 
咱们这篇文章要解决的问题，都是因为 InnoDB 要反对事务，从而导致 InnoDB 表不能把 count(*)间接存起来，而后查问的时候间接返回造成的。
 
所谓以子之矛攻子之盾，当初咱们就利用“事务”这个个性，把问题解决掉。
 

 
图 4 会话 A、B 的执行时序图
 
咱们来看下当初的执行后果。尽管会话 B 的读操作依然是在 T3 执行的，然而因为这时候更新事务还没有提交，所以计数值加 1 这个操作对会话 B 还不可见。
 
因而，会话 B 看到的后果里，查计数值和“最近 100 条记录”看到的后果，逻辑上就是统一的。
 

不同的 count 用法

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

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

 
也就是后面的第一条准则，server 层要什么字段，InnoDB 就返回什么字段。
 
然而 count()是例外 ，并不会把全副字段取出来，而是专门做了优化，不取值。count() 必定不是 null，按行累加。
 
所以论断是：依照效率排序的话，count(字段)<count(主键 id)<count(1)≈count()，所以我倡议你，尽量应用 count()。
 

15 日志相干问题

 
我在第 2 篇文章《日志零碎：一条 SQL 更新语句是如何执行的？》中，和你讲到 binlog（归档日志）和 redo log（重做日志）配合解体复原的时候，用的是反证法，阐明了如果没有两阶段提交，会导致 MySQL 呈现主备数据不统一等问题。
 
在这篇文章上面，很多同学在问，在两阶段提交的不同霎时，MySQL 如果产生异样重启，是怎么保障数据完整性的？
 
当初，咱们就从这个问题开始吧。
 
我再放一次两阶段提交的图，不便你学习上面的内容。
 

 
图 1 两阶段提交示意图
 
这里，我要先和你解释一个误会式的问题。有同学在评论区问到，这个图不是一个 update 语句的执行流程吗，怎么还会调用 commit 语句？
 
他产生这个疑难的起因，是把 两个“commit”的概念 混同了：
 

• 他说的“commit 语句”，是指 MySQL 语法中，用于提交一个事务的命令。个别跟 begin/start transaction 配对应用。
• 而咱们图中用到的这个“commit 步骤”，指的是事务提交过程中的一个小步骤，也是最初一步。当这个步骤执行实现后，这个事务就提交实现了。
• “commit 语句”执行的时候，会蕴含“commit 步骤”。

 
而咱们这个例子外面，没有显式地开启事务，因而这个 update 语句本人就是一个事务，在执行实现后提交事务时，就会用到这个“commit 步骤“。
 
接下来，咱们就一起剖析一下 在两阶段提交的不同时刻，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 是否存在并残缺：

   a. 如果是，则提交事务；
   b. 否则，回滚事务。
 
这里，时刻 B 产生 crash 对应的就是 2(a)的状况，解体复原过程中事务会被提交。
 
当初，咱们持续延展一下这个问题。
 

诘问 1：MySQL 怎么晓得 binlog 是残缺的?

 
答复：一个事务的 binlog 是有残缺格局的：
 

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

 
另外，在 MySQL 5.6.2 版本当前，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志因为磁盘起因，可能会在日志两头出错的状况，MySQL 能够通过校验 checksum 的后果来发现。所以，MySQL 还是有方法验证事务 binlog 的完整性的。
 

诘问 2：redo log 和 binlog 是怎么关联起来的?

 
答复：它们有一个独特的数据字段，叫 XID。解体复原的时候，会按程序扫描 redo log：
 

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

诘问 3：处于 prepare 阶段的 redo log 加上残缺 binlog，重启就能复原，MySQL 为什么要这么设计?

 
答复：其实，这个问题还是跟咱们在反证法中说到的数据与备份的一致性无关。在时刻 B，也就是 binlog 写完当前 MySQL 产生解体，这时候 binlog 曾经写入了，之后就会被从库（或者用这个 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 没有解体复原的能力，那就用 InnoDB 原有的 redo log 好了。
 
而如果说 实现上的起因 的话，就有很多了。就依照问题中说的，只用 binlog 来实现解体复原的流程，我画了一张示意图，这里就没有 redo log 了。
 

 
图 2 只用 binlog 反对解体复原
 
这样的流程下，binlog 还是不能反对解体复原的。我说一个不反对的点吧：binlog 没有能力复原“数据页”。
 
如果在图中标的地位，也就是 binlog2 写完了，然而整个事务还没有 commit 的时候，MySQL 产生了 crash。
 
重启后，引擎内部事务 2 会回滚，而后利用 binlog2 能够补回来；然而对于事务 1 来说，零碎曾经认为提交实现了，不会再利用一次 binlog1。
 
然而，InnoDB 引擎应用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算实现了。如果之后解体，要依赖于日志来复原数据页。
 
也就是说在图中这个地位产生解体的话，事务 1 也是可能失落了的，而且是数据页级的失落。此时，binlog 外面并没有记录数据页的更新细节，是补不回来的。
 
你如果要说，那我优化一下 binlog 的内容，让它来记录数据页的更改能够吗？但，这其实就是又做了一个 redo log 进去。
 
所以，至多当初的 binlog 能力，还不能反对解体复原。
 

诘问 6：那能不能反过来，只用 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 还做不到。你看，倒退生态是如许重要。
 

诘问 7：redo log 个别设置多大？

 
答复：redo log 太小的话，会导致很快就被写满，而后不得不强行刷 redo log，这样 WAL 机制的能力就施展不进去了。
 
所以，如果是当初常见的几个 TB 的磁盘的话，就不要太小气了，间接将 redo log 设置为 4 个文件、每个文件 1GB 吧。
 

诘问 8：失常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过去的还是从 buffer pool 更新过去的呢？

 
答复：这个问题其实问得十分好。这里波及到了，“redo log 外面到底是什么”的问题。
 
实际上，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 耗费。然而可能会呈现“被动写入磁盘”，比方内存不够、其余事务提交等状况。这个问题咱们会在前面第 22 篇文章《MySQL 有哪些“饮鸩止渴”的进步性能的办法？》中再具体开展）。
 
独自执行一个更新语句的时候，InnoDB 会本人启动一个事务，在语句执行实现的时候提交。过程跟下面是一样的，只不过是“压缩”到了一个语句外面实现。
 
以上这些问题，就是把大家提过的对于 redo log 和 binlog 的问题串起来，做的一次集中答复。如果你还有问题，能够在评论区持续留言补充。
 

业务设计问题

 
接下来，我再和你分享 @ithunter 同学在第 8 篇文章《事务到底是隔离的还是不隔离的？》的评论区提到的跟索引相干的一个问题。我感觉这个问题挺乏味、也挺实用的，其他同学也可能会碰上这样的场景，在这里解答和分享一下。
 
问题是这样的（我文字上略微做了点批改，不便大家了解）：
 

业务上有这样的需要，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 锁层面有没有方法解决？
 
首先，我要先赞一下这样的发问形式。尽管极客工夫当初的评论区还不能追加评论，但如果大家可能一次留言就把问题讲清楚的话，其实影响也不大。所以，我心愿你在留言发问的时候，也能借鉴这种形式。
 
接下来，我把 @ithunter 同学说的表模仿进去，不便咱们探讨。
 

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;

 
尽管这个题干中，并没有说到 friend 表的索引构造。但我猜想 friend_1_id 和 friend_2_id 也有索引，为便于形容，我给加上惟一索引。
 
顺便阐明一下，“like”是关键字，我个别不倡议应用关键字作为库名、表名、字段名或索引名。
 
我把他的疑难翻译一下，在并发场景下，同时有两个人，设置为关注对方，就可能导致无奈胜利加为敌人关系。
 
当初，我用你曾经相熟的时刻程序表的模式，把这两个事务的执行语句列出来：

 
图 3 并发“喜爱”逻辑操作程序
 
因为一开始 A 和 B 之间没有关注关系，所以两个事务外面的 select 语句查出来的后果都是空。
 
因而，session 1 的逻辑就是“既然 B 没有关注 A，那就只插入一个单向关注关系”。session 2 也同样是这个逻辑。
 
这个后果对业务来说就是 bug 了。因为在业务设定外面，这两个逻辑都执行实现当前，是应该在 friend 表外面插入一行记录的。
 
如发问外面说的，“第 1 步即便应用了排他锁也不行，因为记录不存在，行锁无奈失效”。不过，我想到了另外一个办法，来解决这个问题。
 
首先，要给“like”表减少一个字段，比方叫作 relation_ship，并设为整型，取值 1、2、3。
 

值是 1 的时候，示意 user_id 关注 liker_id;
值是 2 的时候，示意 liker_id 关注 user_id;
值是 3 的时候，示意相互关注。
 
而后，当 A 关注 B 的时候，逻辑改成如下所示的样子：
 
利用代码外面，比拟 A 和 B 的大小，如果 A <B，就执行上面的逻辑
 

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 的这条记录。
 
不晓得你会不会吐槽：之前明明还说尽量不要应用惟一索引，后果这个例子一上来我就创立了两个。这里我要再和你阐明一下，之前文章咱们探讨的，是在“业务开发保障不会插入重复记录”的状况下，着重要解决性能问题的时候，才倡议尽量应用一般索引。
 
而像这个例子里，依照这个设计，业务根本就是保障“我肯定会插入反复数据，数据库肯定要要有唯一性束缚”，这时就没啥好说的了，惟一索引建起来吧。
 
 
 

16 讲“orderby”是怎么工作的

全字段排序

 
后面咱们介绍过索引，所以你当初就很分明了，为防止全表扫描，咱们须要在 city 字段加上索引。
 
在 city 字段上创立索引之后，咱们用 explain 命令来看看这个语句的执行状况。
 

 
图 1 应用 explain 命令查看语句的执行状况
 
Extra 这个字段中的“Using filesort”示意的就是须要排序，MySQL 会给每个线程调配一块内存用于排序，称为 sort_buffer。
 
为了阐明这个 SQL 查问语句的执行过程，咱们先来看一下 city 这个索引的示意图。
 

 
图 2 city 字段的索引示意图
 
从图中能够看到，满足 city=’ 杭州’条件的行，是从 ID_X 到 ID_(X+N)的这些记录。
 
通常状况下，这个语句执行流程如下所示：
 

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city=’ 杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 反复步骤 3、4 直到 city 的值不满足查问条件为止，对应的主键 id 也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据依照字段 name 做疾速排序；
7. 依照排序后果取前 1000 行返回给客户端。

 
咱们暂且把这个排序过程，称为全字段排序，执行流程的示意图如下所示，下一篇文章中咱们还会用到这个排序。
 

 
图 3 全字段排序
 
图中“按 name 排序”这个动作，可能在内存中实现，也可能须要应用内部排序，这取决于排序所需的内存和参数 sort_buffer_size。
 
sort_buffer_size，就是 MySQL 为排序开拓的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中实现。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
 

全字段排序 VS rowid 排序

 
咱们来剖析一下，从这两个执行流程里，还能得出什么论断。
 
如果 MySQL 切实是放心排序内存太小，会影响排序效率，才会采纳 rowid 排序算法，这样排序过程中一次能够排序更多行，然而须要再回到原表去取数据。
 
如果 MySQL 认为内存足够大，会优先选择全字段排序，把须要的字段都放到 sort_buffer 中，这样排序后就会间接从内存外面返回查问后果了，不必再回到原表去取数据。
 
这也就体现了 MySQL 的一个设计思维：如果内存够，就要多利用内存，尽量减少磁盘拜访。
 
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因而不会被优先选择。
 
这个论断看上去有点废话的感觉，然而你要记住它，下一篇文章咱们就会用到。
 
看到这里，你就理解了，MySQL 做排序是一个老本比拟高的操作。那么你会问，是不是所有的 order by 都须要排序操作呢？如果不排序就能失去正确的后果，那对系统的耗费会小很多，语句的执行工夫也会变得更短。
 
其实，并不是所有的 order by 语句，都须要排序操作的。从下面剖析的执行过程，咱们能够看到，MySQL 之所以须要生成长期表，并且在长期表上做排序操作，其起因是原来的数据都是无序的。