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之所以须要生成长期表，并且在长期表上做排序操作，其起因是原来的数据都是无序的。