问题解析

都说InnoDB好，那还要不要使用Memory引擎？我在上一篇文章末尾留给你的问题是：两个group by 语句都用了order by null，为什么使用内存临时表得到的语句结果里，0这个值在最后一行；而使用磁盘临时表得到的结果里，0这个值在第一行？今天我们就来看看，出现这个问题的原因吧。

内存表的数据组织结构为了便于分析，我来把这个问题简化一下，假设有以下的两张表t1 和 t2，其中表t1使用Memory引擎， 表t2使用InnoDB引擎。

然后，我分别执行select * from t1和select * from t2。

create table t1(id int primary key, c int) engine=Memory;create table t2(id int primary key, c int) engine=innodb;insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);图1 两个查询结果-0的位置可以看到，内存表t1的返回结果里面0在最后一行，而InnoDB表t2的返回结果里0在第一行。

出现这个区别的原因，要从这两个引擎的主键索引的组织方式说起。

表t2用的是InnoDB引擎，它的主键索引id的组织方式，你已经很熟悉了：InnoDB表的数据就放在主键索引树上，主键索引是B+树。

所以表t2的数据组织方式如下图所示：

图2 表t2的数据组织主键索引上的值是有序存储的。

在执行select *的时候，就会按照叶子节点从左到右扫描，所以得到的结果里，0就出现在第一行。

与InnoDB引擎不同，Memory引擎的数据和索引是分开的。

我们来看一下表t1中的数据内容。

图3 表t1 的数据组织可以看到，内存表的数据部分以数组的方式单独存放，而主键id索引里，存的是每个数据的位置。

主键id是hash索引，可以看到索引上的key并不是有序的。

在内存表t1中，当我执行select *的时候，走的是全表扫描，也就是顺序扫描这个数组。

因此，0就是最后一个被读到，并放入结果集的数据。

可见，InnoDB和Memory引擎的数据组织方式是不同的：

InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id。

这种方式，我们称之为索引组织表（Index Organizied Table）。

而Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

从中我们可以看出，这两个引擎的一些典型不同：

1. InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；

2. 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；

3. 数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；

4. InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。

而内存表没有这个区别，所有索引的“地位”都是相同的。

5. InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

由于内存表的这些特性，每个数据行被删除以后，空出的这个位置都可以被接下来要插入的数据复用。

比如，如果要在表t1中执行：

就会看到返回结果里，id=10这一行出现在id=4之后，也就是原来id=5这行数据的位置。

需要指出的是，表t1的这个主键索引是哈希索引，因此如果执行范围查询，比如是用不上主键索引的，需要走全表扫描。

你可以借此再回顾下第4篇文章的内容。

那如果要让内存表支持范围扫描，应该怎么办呢 ？hash索引和B-Tree索引实际上，内存表也是支B-Tree索引的。

在id列上创建一个B-Tree索引，SQL语句可以这么写：

这时，表t1的数据组织形式就变成了这样：

delete from t1 where id=5;insert into t1 values(10,10);select * from t1;select * from t1 where id<5;alter table t1 add index a_btree_index using btree (id);图4 表t1的数据组织–增加B-Tree索引新增的这个B-Tree索引你看着就眼熟了，这跟InnoDB的b+树索引组织形式类似。

作为对比，你可以看一下这下面这两个语句的输出：

图5 使用B-Tree和hash索引查询返回结果对比可以看到，执行select * from t1 where id<5的时候，优化器会选择B-Tree索引，所以返回结果是0到4。

使用force index强行使用主键id这个索引，id=0这一行就在结果集的最末尾了。

其实，一般在我们的印象中，内存表的优势是速度快，其中的一个原因就是Memory引擎支持hash索引。

当然，更重要的原因是，内存表的所有数据都保存在内存，而内存的读写速度总是比磁盘快。

但是，接下来我要跟你说明，为什么我不建议你在生产环境上使用内存表。

这里的原因主要包括两个方面：

1. 锁粒度问题；

2. 数据持久化问题。

内存表的锁我们先来说说内存表的锁粒度问题。

内存表不支持行锁，只支持表锁。

因此，一张表只要有更新，就会堵住其他所有在这个表上的读写操作。

需要注意的是，这里的表锁跟之前我们介绍过的MDL锁不同，但都是表级的锁。

接下来，我通过下面这个场景，跟你模拟一下内存表的表级锁。

图6 内存表的表锁–复现步骤在这个执行序列里，session A的update语句要执行50秒，在这个语句执行期间session B的查询会进入锁等待状态。

session C的show processlist 结果输出如下：

图7 内存表的表锁–结果跟行锁比起来，表锁对并发访问的支持不够好。

所以，内存表的锁粒度问题，决定了它在处理并发事务的时候，性能也不会太好。

数据持久性问题接下来，我们再看看数据持久性的问题。

数据放在内存中，是内存表的优势，但也是一个劣势。

因为，数据库重启的时候，所有的内存表都会被清空。

你可能会说，如果数据库异常重启，内存表被清空也就清空了，不会有什么问题啊。

但是，在高可用架构下，内存表的这个特点简直可以当做bug来看待了。

为什么这么说呢？我们先看看M-S架构下，使用内存表存在的问题。

图8 M-S基本架构我们来看一下下面这个时序：

1. 业务正常访问主库；

2. 备库硬件升级，备库重启，内存表t1内容被清空；

3. 备库重启后，客户端发送一条update语句，修改表t1的数据行，这时备库应用线程就会报错“找不到要更新的行”。

这样就会导致主备同步停止。

当然，如果这时候发生主备切换的话，客户端会看到，表t1的数据“丢失”了。

在图8中这种有proxy的架构里，大家默认主备切换的逻辑是由数据库系统自己维护的。

这样对客户端来说，就是“网络断开，重连之后，发现内存表数据丢失了”。

你可能说这还好啊，毕竟主备发生切换，连接会断开，业务端能够感知到异常。

但是，接下来内存表的这个特性就会让使用现象显得更“诡异”了。

由于MySQL知道重启之后，内存表的数据会丢失。

所以，担心主库重启之后，出现主备不一致，MySQL在实现上做了这样一件事儿：在数据库重启之后，往binlog里面写入一行DELETE FROM t1。

如果你使用是如图9所示的双M结构的话：

图9 双M结构在备库重启的时候，备库binlog里的delete语句就会传到主库，然后把主库内存表的内容删除。

这样你在使用的时候就会发现，主库的内存表数据突然被清空了。

基于上面的分析，你可以看到，内存表并不适合在生产环境上作为普通数据表使用。

有同学会说，但是内存表执行速度快呀。

这个问题，其实你可以这么分析：

1. 如果你的表更新量大，那么并发度是一个很重要的参考指标，InnoDB支持行锁，并发度比内存表好；

2. 能放到内存表的数据量都不大。

如果你考虑的是读的性能，一个读QPS很高并且数据量不大的表，即使是使用InnoDB，数据也是都会缓存在InnoDB Buffer Pool里的。

因此，使用InnoDB表的读性能也不会差。

所以，我建议你把普通内存表都用InnoDB表来代替。

但是，有一个场景却是例外的。

这个场景就是，我们在第35和36篇说到的用户临时表。

在数据量可控，不会耗费过多内存的情况下，你可以考虑使用内存表。

内存临时表刚好可以无视内存表的两个不足，主要是下面的三个原因：

1. 临时表不会被其他线程访问，没有并发性的问题；

2. 临时表重启后也是需要删除的，清空数据这个问题不存在；

3. 备库的临时表也不会影响主库的用户线程。

现在，我们回过头再看一下第35篇join语句优化的例子，当时我建议的是创建一个InnoDB临时表，使用的语句序列是：

了解了内存表的特性，你就知道了， 其实这里使用内存临时表的效果更好，原因有三个：

1. 相比于InnoDB表，使用内存表不需要写磁盘，往表temp_t的写数据的速度更快；

2. 索引b使用hash索引，查找的速度比B-Tree索引快；

3. 临时表数据只有2000行，占用的内存有限。

因此，你可以对第35篇文章的语句序列做一个改写，将临时表t1改成内存临时表，并且在字段b上创建一个hash索引。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);图10 使用内存临时表的执行效果可以看到，不论是导入数据的时间，还是执行join的时间，使用内存临时表的速度都比使用InnoDB临时表要更快一些。

小结今天这篇文章，我从“要不要使用内存表”这个问题展开，和你介绍了Memory引擎的几个特性。

可以看到，由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双M架构，还可能导致主库的内存表数据被删掉。

因此，在生产上，我不建议你使用普通内存表。

如果你是DBA，可以在建表的审核系统中增加这类规则，要求业务改用InnoDB表。

我们在文中也分析了，其实InnoDB表性能还不错，而且数据安全也有保障。

而内存表由于不支持行锁，更新语句会阻塞查询，性能也未必就如想象中那么好。

基于内存表的特性，我们还分析了它的一个适用场景，就是内存临时表。

内存表支持hash索引，这个特性利用起来，对复杂查询的加速效果还是很不错的。

最后，我给你留一个问题吧。

假设你刚刚接手的一个数据库上，真的发现了一个内存表。

备库重启之后肯定是会导致备库的内存表数据被清空，进而导致主备同步停止。

这时，最好的做法是将它修改成InnoDB引擎表。

假设当时的业务场景暂时不允许你修改引擎，你可以加上什么自动化逻辑，来避免主备同步停止呢？你可以把你的思考和分析写在评论区，我会在下一篇文章的末尾跟你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间今天文章的正文内容，已经回答了我们上期的问题，这里就不再赘述了。

问题解析

什么时候会使用内部临时表？今天是大年初二，在开始我们今天的学习之前，我要先和你道一声春节快乐！在第16和第34篇文章中，我分别和你介绍了sort buffer、内存临时表和join buffer。

这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行的。

其中，我们在排序的时候用到了sort buffer，在使用join语句的时候用到了join buffer。

然后，你可能会有这样的疑问，MySQL什么时候会使用内部临时表呢？今天这篇文章，我就先给你举两个需要用到内部临时表的例子，来看看内部临时表是怎么工作的。

然后，我们再来分析，什么情况下会使用内部临时表。

union 执行流程为了便于量化分析，我用下面的表t1来举例。

然后，我们执行下面这条语句：

这条语句用到了union，它的语义是，取这两个子查询结果的并集。

并集的意思就是这两个集合加起来，重复的行只保留一行。

下图是这个语句的explain结果。

图1 union语句explain 结果可以看到：

第二行的key=PRIMARY，说明第二个子句用到了索引id。

第三行的Extra字段，表示在对子查询的结果集做union的时候，使用了临时表(Usingtemporary)。

这个语句的执行流程是这样的：

1. 创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。

create table t1(id int primary key, a int, b int, index(a));delimiter ;;create procedure idata()begin declare i int; set i=1; while(i<=1000)do insert into t1 values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();(select 1000 as f) union (select id from t1 order by id desc limit 2);2. 执行第一个子查询，得到1000这个值，并存入临时表中。

3. 执行第二个子查询：

拿到第一行id=1000，试图插入临时表中。

但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；

取到第二行id=999，插入临时表成功。

4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

这个过程的流程图如下所示：

图 2 union 执行流程可以看到，这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一性约束，实现了union的语义。

顺便提一下，如果把上面这个语句中的union改成union all的话，就没有了“去重”的语义。

这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。

因此也就不需要临时表了。

图3 union all的explain结果可以看到，第二行的Extra字段显示的是Using index，表示只使用了覆盖索引，没有用临时表了。

group by 执行流程另外一个常见的使用临时表的例子是group by，我们来看一下这个语句：

这个语句的逻辑是把表t1里的数据，按照 id%10 进行分组统计，并按照m的结果排序后输出。

它的explain结果如下：

图4 group by 的explain结果在Extra字段里面，我们可以看到三个信息：

Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；

Using temporary，表示使用了临时表；

Using filesort，表示需要排序。

这个语句的执行流程是这样的：

1. 创建内存临时表，表里有两个字段m和c，主键是m；

2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

如果临时表中没有主键为x的行，就插入一个记录(x,1);如果表中有主键为x的行，就将x这一行的c值加1；

3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

这个流程的执行图如下：

select id%10 as m, count(*) as c from t1 group by m;图5 group by执行流程图中最后一步，对内存临时表的排序，在第17篇文章中已经有过介绍，我把图贴过来，方便你回顾。

图6 内存临时表排序流程其中，临时表的排序过程就是图6中虚线框内的过程。

接下来，我们再看一下这条语句的执行结果：

图 7 group by执行结果如果你的需求并不需要对结果进行排序，那你可以在SQL语句末尾增加order by null，也就是改成：

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。

返回的结果如图8所示。

图8 group + order by null 的结果（内存临时表）由于表t1中的id值是从1开始的，因此返回的结果集中第一行是id=1；扫描到id=10的时候才插入m=0这一行，因此结果集里最后一行才是m=0。

这个例子里由于临时表只有10行，内存可以放得下，因此全程只使用了内存临时表。

但是，内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。

如果我执行下面这个语句序列：

把内存临时表的大小限制为最大1024字节，并把语句改成id % 100，这样返回结果里有100行数据。

但是，这时的内存临时表大小不够存下这100行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024字节）。

那么，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB。

这select id%10 as m, count() as c from t1 group by m order by null;set tmp_table_size=1024;select id%100 as m, count() as c from t1 group by m order by null limit 10;时，返回的结果如图9所示。

图9 group + order by null 的结果（磁盘临时表）如果这个表t1的数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

group by 优化方法 –索引可以看到，不论是使用内存临时表还是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。

如果表的数据量比较大，上面这个group by语句执行起来就会很慢，我们有什么优化的方法呢？要解决group by语句的优化问题，你可以先想一下这个问题：执行group by语句为什么需要临时表？group by的语义逻辑，是统计不同的值出现的个数。

但是，由于每一行的id%100的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

那么，如果扫描过程中可以保证出现的数据是有序的，是不是就简单了呢？假设，现在有一个类似图10的这么一个数据结构，我们来看看group by可以怎么做。

图10 group by算法优化-有序输入可以看到，如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右，顺序扫描，依次累加。

也就是下面这个过程：

当碰到第一个1的时候，已经知道累积了X个0，结果集里的第一行就是(0,X);当碰到第一个2的时候，已经知道累积了Y个1，结果集里的第一行就是(1,Y);按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到group by的结果，不需要临时表，也不需要再额外排序。

你一定想到了，InnoDB的索引，就可以满足这个输入有序的条件。

在MySQL 5.7版本支持了generated column机制，用来实现列数据的关联更新。

你可以用下面的方法创建一个列z，然后在z列上创建一个索引（如果是MySQL 5.6及之前的版本，你也可以创建普通列和索引，来解决这个问题）。

这样，索引z上的数据就是类似图10这样有序的了。

上面的group by语句就可以改成：

alter table t1 add column z int generated always as(id % 100), add index(z);优化后的group by语句的explain结果，如下图所示：

图11 group by 优化的explain结果从Extra字段可以看到，这个语句的执行不再需要临时表，也不需要排序了。

group by优化方法 –直接排序所以，如果可以通过加索引来完成group by逻辑就再好不过了。

但是，如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。

那么，这时候的group by要怎么优化呢？如果我们明明知道，一个group by语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。

那么，我们就会想了，MySQL有没有让我们直接走磁盘临时表的方法呢？答案是，有的。

在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。

所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

因此，下面这个语句的执行流程就是这样的：

1. 初始化sort_buffer，确定放入一个整型字段，记为m；

2. 扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；

3. 扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序）；

select z, count() as c from t1 group by z;select SQL_BIG_RESULT id%100 as m, count() as c from t1 group by m;4. 排序完成后，就得到了一个有序数组。

根据有序数组，得到数组里面的不同值，以及每个值的出现次数。

这一步的逻辑，你已经从前面的图10中了解过了。

下面两张图分别是执行流程图和执行explain命令得到的结果。

图12 使用 SQL_BIG_RESULT的执行流程图图13 使用 SQL_BIG_RESULT的explain 结果从Extra字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。

基于上面的union、union all和group by语句的执行过程的分析，我们来回答文章开头的问题：

MySQL什么时候会使用内部临时表？1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

2. join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；

3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。

比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

小结通过今天这篇文章，我重点和你讲了group by的几种实现算法，从中可以总结一些使用的指导原则：

1. 如果对group by语句的结果没有排序要求，要在语句后面加 order by null；

2. 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Usingfilesort；

3. 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

4. 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

最后，我给你留下一个思考题吧。

文章中图8和图9都是order by null，为什么图8的返回结果里面，0是在结果集的最后一行，而图9的结果里面，0是在结果集的第一行？你可以把你的分析写在留言区里，我会在下一篇文章和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期的问题是：为什么不能用rename修改临时表的改名。

在实现上，执行rename table语句的时候，要求按照“库名/表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的frm文件是放在tmpdir目录下的，并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”，因此会报“找不到文件名”的错误。

问题解析

为什么临时表可以重名？在上一篇文章中，我们在优化join查询的时候使用到了临时表。

当时，我们是这么用的：
你可能会有疑问，为什么要用临时表呢？直接用普通表是不是也可以呢？今天我们就从这个问题说起：临时表有哪些特征，为什么它适合这个场景？这里，我需要先帮你厘清一个容易误解的问题：有的人可能会认为，临时表就是内存表。

但是，这两个概念可是完全不同的。

内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。

这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。

除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。

而临时表，可以使用各种引擎类型 。

如果是使用InnoDB引擎或者MyISAM引擎的临时表，写create temporary table temp_t like t1;alter table temp_t add index(b);insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);数据的时候是写到磁盘上的。

当然，临时表也可以使用Memory引擎。

弄清楚了内存表和临时表的区别以后，我们再来看看临时表有哪些特征。

临时表的特性为了便于理解，我们来看下下面这个操作序列：
图1 临时表特性示例可以看到，临时表在使用上有以下几个特点：

1. 建表语法是create temporary table …。

2. 一个临时表只能被创建它的session访问，对其他线程不可见。

所以，图中session A创建的临时表t，对于session B就是不可见的。

3. 临时表可以与普通表同名。

4. session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。

5. show tables命令不显示临时表。

由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。

也正是由于这个特性，临时表就特别适合我们文章开头的join优化这种场景。

为什么呢？原因主要包括以下两个方面：

1. 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。

2. 不需要担心数据删除问题。

如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。

而临时表由于会自动回收，所以不需要这个额外的操作。

临时表的应用由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。

其中，分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。

比如。

将一个大表ht，按照字段f，拆分成1024个分表，然后分布到32个数据库实例上。

如下图所示：
图2 分库分表简图一般情况下，这种分库分表系统都有一个中间层proxy。

不过，也有一些方案会让客户端直接连接数据库，也就是没有proxy这一层。

在这个架构中，分区key的选择是以“减少跨库和跨表查询”为依据的。

如果大部分的语句都会包含f的等值条件，那么就要用f做分区键。

这样，在proxy这一层解析完SQL语句以后，就能确定将这条语句路由到哪个分表做查询。

比如下面这条语句：
这时，我们就可以通过分表规则（比如，N%1024)来确认需要的数据被放在了哪个分表上。

这种语句只需要访问一个分表，是分库分表方案最欢迎的语句形式了。

但是，如果这个表上还有另外一个索引k，并且查询语句是这样的：
这时候，由于查询条件里面没有用到分区字段f，只能到所有的分区中去查找满足条件的所有行，然后统一做order by 的操作。

这种情况下，有两种比较常用的思路。

第一种思路是，在proxy层的进程代码中实现排序。

这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。

不过，这个方案的缺点也比较明显：

1. 需要的开发工作量比较大。

我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如group by，甚至join这样的操作，对中间层的开发能力要求比较高；
2. 对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

比如上面这条语句，执行流程可以类似这样：
在汇总库上创建一个临时表temp_ht，表里包含三个字段v、k、t_modified；
在各个分库上执行select v from ht where f=N;select v from ht where k >= M order by t_modified desc limit 100;select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;把分库执行的结果插入到temp_ht表中；
执行得到结果。

这个过程对应的流程图如下所示：
图3 跨库查询流程示意图在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某一个上。

这时的查询逻辑与图3类似，你可以自己再思考一下具体的流程。

为什么临时表可以重名？你可能会问，不同线程可以创建同名的临时表，这是怎么做到的呢？接下来，我们就看一下这个问题。

我们在执行select v from temp_ht order by t_modified desc limit 100; 这个语句的时候，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_{线程id}_序列号”。

你可以使用select @@tmpdir命令，来显示实例的临时文件目录。

而关于表中数据的存放方式，在不同的MySQL版本中有着不同的处理方式：
在5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件，用来存放数据文件；
而从 5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。

因此，我们就不需要再创建ibd文件了。

从文件名的前缀规则，我们可以看到，其实创建一个叫作t1的InnoDB临时表，MySQL在存储上认为我们创建的表名跟普通表t1是不同的，因此同一个库下面已经有普通表t1的情况下，还是可以再创建一个临时表t1的。

为了便于后面讨论，我先来举一个例子。

图4 临时表的表名这个进程的进程号是1234，session A的线程id是4，session B的线程id是5。

所以你看到了，session A和session B创建的临时表，在磁盘上的文件不会重名。

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。

一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。

而对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

create temporary table temp_t(id int primary key)engine=innodb;也就是说，session A和sessionB创建的两个临时表t1，它们的table_def_key不同，磁盘文件名也不同，因此可以并存。

在实现上，每个线程都维护了自己的临时表链表。

这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表里的每个临时表，执行 “DROP TEMPORARY TABLE +表名”操作。

这时候你会发现，binlog中也记录了DROP TEMPORARY TABLE这条命令。

你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到binlog里面？这，就需要说到主备复制了。

临时表和主备复制既然写binlog，就意味着备库需要。

你可以设想一下，在主库上执行下面这个语句序列：
如果关于临时表的操作都不记录，那么在备库就只有create table t_normal表和insert intot_normal select * from temp_t这两个语句的binlog日志，备库在执行到insert into t_normal的时候，就会报错“表temp_t不存在”。

你可能会说，如果把binlog设置为row格式就好了吧？因为binlog是row格式时，在记录insert intot_normal的binlog时，记录的是这个操作的数据，即：write_row event里面记录的逻辑是“插入一行数据（1,1)”。

确实是这样。

如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里。

也就是说，只在binlog_format=statment/mixed 的时候，binlog中才会记录临时表的操作。

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。

主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。

所以，这时候我们就需要在主库上再写一个DROP TEMPORARY TABLE传给备库执行。

之前有人问过我一个有趣的问题：MySQL在记录binlog的时候，不论是create table还是altertable语句，都是原样记录，甚至于连空格都不变。

但是如果执行drop table t_normal，系统记录binlog就会写成：
create table t_normal(id int primary key, c int)engine=innodb;/Q1/create temporary table temp_t like t_normal;/Q2/insert into temp_t values(1,1);/Q3/insert into t_normal select * from temp_t;/Q4/也就是改成了标准的格式。

为什么要这么做呢 ？现在你知道原因了，那就是：drop table命令是可以一次删除多个表的。

比如，在上面的例子中，设置binlog_format=row，如果主库上执行 “drop table t_normal, temp_t”这个命令，那么binlog中就只能记录：
因为备库上并没有表temp_t，将这个命令重写后再传到备库执行，才不会导致备库同步线程停止。

所以，drop table命令记录binlog的时候，就必须对语句做改写。

“/* generated by server */”说明了这是一个被服务端改写过的命令。

说到主备复制，还有另外一个问题需要解决：主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？现在，我给你举个例子，下面的序列中实例S是M的备库。

图5 主备关系中的临时表操作主库M上的两个session创建了同名的临时表t1，这两个create temporary table t1 语句都会被传到备库S上。

但是，备库的应用日志线程是共用的，也就是说要在应用线程里面先后执行这个create 语句两次。

（即使开了多线程复制，也可能被分配到从库的同一个worker中执行）。

那么，这会不会导致同步线程报错 ？显然是不会的，否则临时表就是一个bug了。

也就是说，备库线程在执行的时候，要把这两个t1DROP TABLE t̀_normal ̀/* generated by server /DROP TABLE t̀_normal ̀/ generated by server */表当做两个不同的临时表来处理。

这，又是怎么实现的呢？MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中。

这样，在备库的应用线程就能够知道执行每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_key：

1. session A的临时表t1，在备库的table_def_key就是：库名+t1+“M的serverid”+“session A的thread_id”;2. session B的临时表t1，在备库的table_def_key就是 ：库名+t1+“M的serverid”+“session B的thread_id”。

由于table_def_key不同，所以这两个表在备库的应用线程里面是不会冲突的。

小结今天这篇文章，我和你介绍了临时表的用法和特性。

在实际应用中，临时表一般用于处理比较复杂的计算逻辑。

由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。

在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。

在binlog_format=’row’的时候，临时表的操作不记录到binlog中，也省去了不少麻烦，这也可以成为你选择binlog_format时的一个考虑因素。

需要注意的是，我们上面说到的这种临时表，是用户自己创建的 ，也可以称为用户临时表。

与它相对应的，就是内部临时表，在第17篇文章中我已经和你介绍过。

最后，我给你留下一个思考题吧。

下面的语句序列是创建一个临时表，并将其改名：
图6 关于临时表改名的思考题可以看到，我们可以使用alter table语法修改临时表的表名，而不能使用rename语法。

你知道这是什么原因吗？你可以把你的分析写在留言区，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期的问题是，对于下面这个三个表的join语句，如果改写成straight_join，要怎么指定连接顺序，以及怎么给三个表创建索引。

第一原则是要尽量使用BKA算法。

需要注意的是，使用BKA算法的时候，并不是“先计算两个表join的结果，再跟第三个表join”，而是直接嵌套查询的。

具体实现是：在t1.c>=X、t2.c>=Y、t3.c>=Z这三个条件里，选择一个经过过滤以后，数据最少的那个表，作为第一个驱动表。

此时，可能会出现如下两种情况。

第一种情况，如果选出来是表t1或者t3，那剩下的部分就固定了。

1. 如果驱动表是t1，则连接顺序是t1->t2->t3，要在被驱动表字段创建上索引，也就是t2.a 和t3.b上创建索引；
2. 如果驱动表是t3，则连接顺序是t3->t2->t1，需要在t2.b 和 t1.a上创建索引。

同时，我们还需要在第一个驱动表的字段c上创建索引。

第二种情况是，如果选出来的第一个驱动表是表t2的话，则需要评估另外两个条件的过滤效果。

总之，整体的思路就是，尽量让每一次参与join的驱动表的数据集，越小越好，因为这样我们的驱动表就会越小。

问题解析

join语句怎么优化？

join语句的两种算法，分别是Index Nested-Loop Join(NLJ)和 Block Nested-Loop Join(BNL)。

我们发现在使用NLJ算法的时候，其实效果还是不错的，比通过应用层拆分成多个语句然后再拼接查询结果更方便，而且性能也不会差。

但是，BNL算法在大表join的时候性能就差多了，比较次数等于两个表参与join的行数的乘积，很消耗CPU资源。

当然了，这两个算法都还有继续优化的空间，我们今天就来聊聊这个话题。

为了便于分析，我还是创建两个表t1、t2来和你展开今天的问题。

为了便于后面量化说明，我在表t1里，插入了1000行数据，每一行的a=1001-id的值。

也就是说，表t1中字段a是逆序的。

同时，我在表t2中插入了100万行数据。

Multi-Range Read优化在介绍join语句的优化方案之前，我需要先和你介绍一个知识点，即：Multi-Range Read优化(MRR)。

这个优化的主要目的是尽量使用顺序读盘。

在第4篇文章中，我和你介绍InnoDB的索引结构时，提到了“回表”的概念。

我们先来回顾一下这个概念。

回表是指，InnoDB在普通索引a上查到主键id的值后，再根据一个个主键id的值到主键索引上去查整行数据的过程。

然后，有同学在留言区问到，回表过程是一行行地查数据，还是批量地查数据？我们先来看看这个问题。

假设，我执行这个语句：
create table t1(id int primary key, a int, b int, index(a));create table t2 like t1;drop procedure idata;delimiter ;;create procedure idata()begin declare i int; set i=1; while(i<=1000)do insert into t1 values(i, 1001-i, i); set i=i+1; end while; set i=1; while(i<=1000000)do insert into t2 values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。

因此，回表肯定是一行行搜索主键索引的。

如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差。

虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是MRR优化的设计思路。

此时，语句的执行流程变成了这样：

1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;2. 将read_rnd_buffer中的id进行递增排序；
2. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

select * from t1 where a>=1 and a<=100;这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。

read_rnd_buffer放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。

之后继续找索引a的下个记录，并继续循环。

另外需要说明的是，如果你想要稳定地使用MRR优化的话，需要设置set optimizer_switch=”mrr_cost_based=off”。

（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了。

）下面两幅图就是使用了MRR优化后的执行流程和explain结果。

从explain结果中，我们可以看到Extra字段多了Using MRR，表示的是用上了MRR优化。

而且，由于我们在read_rnd_buffer中按照id做了排序，所以最后得到的结果集也是按照主键id递增顺序的，也就是与图1结果集中行的顺序相反。

到这里，我们小结一下。

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。

这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access理解了MRR性能提升的原理，我们就能理解MySQL在5.6版本后开始引入的Batched KeyAcess(BKA)算法了。

这个BKA算法，其实就是对NLJ算法的优化。

我们再来看看上一篇文章中用到的NLJ算法的流程图：
图4 Index Nested-Loop Join流程图NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。

也就是说，对于表t2来说，每次都是匹配一个值。

这时，MRR的优势就用不上了。

那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些行出来，一起传给表t2。

既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。

这个临时内存不是别人，就是join_buffer。

通过上一篇文章，我们知道join_buffer 在BNL算法里的作用，是暂存驱动表的数据。

但是在NLJ算法里并没有用。

那么，我们刚好就可以复用join_buffer到BKA算法中。

如图5所示，是上面的NLJ算法优化后的BKA算法的流程。

图5 Batched Key Acess流程图中，我在join_buffer中放入的数据是P1~P100，表示的是只会取查询需要的字段。

当然，如果join buffer放不下P1~P100的所有数据，就会把这100行数据分成多段执行上图的流程。

那么，这个BKA算法到底要怎么启用呢？如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置其中，前两个参数的作用是要启用MRR。

这么做的原因是，BKA算法的优化要依赖于MRR。

set optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;BNL算法的性能问题说完了NLJ算法的优化，我们再来看BNL算法的优化。

我在上一篇文章末尾，给你留下的思考题是，使用Block Nested-Loop Join(BNL)算法时，可能会对被驱动表做多次扫描。

如果这个被驱动表是一个大的冷数据表，除了会导致IO压力大以外，还会对系统有什么影响呢？在第33篇文章中，我们说到InnoDB的LRU算法的时候提到，由于InnoDB对Bufffer Pool的LRU算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在old区域。

如果1秒之后这个数据页不再被访问了，就不会被移动到LRU链表头部，这样对Buffer Pool的命中率影响就不大。

但是，如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过1秒，就会在再次扫描冷表的时候，把冷表的数据页移到LRU链表头部。

这种情况对应的，是冷表的数据量小于整个Buffer Pool的3/8，能够完全放入old区域的情况。

如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入young区域。

由于优化机制的存在，一个正常访问的数据页，要进入young区域，需要隔1秒后再次被访问到。

但是，由于我们的join语句在循环读磁盘和淘汰内存页，进入old区域的数据页，很可能在1秒之内就被淘汰了。

这样，就会导致这个MySQL实例的Buffer Pool在这段时间内，young区域的数据页没有被合理地淘汰。

也就是说，这两种情况都会影响Buffer Pool的正常运作。

大表join操作虽然对IO有影响，但是在语句执行结束后，对IO的影响也就结束了。

但是，对Buffer Pool的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数。

也就是说，BNL算法对系统的影响主要包括三个方面：

1. 可能会多次扫描被驱动表，占用磁盘IO资源；
2. 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
3. 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。

我们执行语句之前，需要通过理论分析和查看explain结果的方式，确认是否要使用BNL算法。

如果确认优化器会使用BNL算法，就需要做优化。

优化的常见做法是，给被驱动表的join字段加上索引，把BNL算法转成BKA算法。

接下来，我们就具体看看，这个优化怎么做？BNL转BKA一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成BKA算法了。

但是，有时候你确实会碰到一些不适合在被驱动表上建索引的情况。

比如下面这个语句：
我们在文章开始的时候，在表t2中插入了100万行数据，但是经过where条件过滤后，需要参与join的只有2000行数据。

如果这条语句同时是一个低频的SQL语句，那么再为这个语句在表t2的字段b上创建一个索引就很浪费了。

但是，如果使用BNL算法来join的话，这个语句的执行流程是这样的：

1. 把表t1的所有字段取出来，存入join_buffer中。

这个表只有1000行，join_buffer_size默认值是256k，可以完全存入。

2. 扫描表t2，取出每一行数据跟join_buffer中的数据进行对比，如果不满足t1.b=t2.b，则跳过；
   如果满足t1.b=t2.b, 再判断其他条件，也就是是否满足t2.b处于[1,2000]的条件，如果是，就作为结果集的一部分返回，否则跳过。

我在上一篇文章中说过，对于表t2的每一行，判断join是否满足的时候，都需要遍历join_buffer中的所有行。

因此判断等值条件的次数是1000*100万=10亿次，这个判断的工作量很大。

图6 explain结果图7 语句执行时间可以看到，explain结果里Extra字段显示使用了BNL算法。

在我的测试环境里，这条语句需要执select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;行1分11秒。

在表t2的字段b上创建索引会浪费资源，但是不创建索引的话这个语句的等值条件要判断10亿次，想想也是浪费。

那么，有没有两全其美的办法呢？这时候，我们可以考虑使用临时表。

使用临时表的大致思路是：

1. 把表t2中满足条件的数据放在临时表tmp_t中；
2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
3. 让表t1和tmp_t做join操作。

此时，对应的SQL语句的写法如下：
图8就是这个语句序列的执行效果。

图8 使用临时表的执行效果可以看到，整个过程3个语句执行时间的总和还不到1秒，相比于前面的1分11秒，性能得到了大幅提升。

接下来，我们一起看一下这个过程的消耗：

1. 执行insert语句构造temp_t表并插入数据的过程中，对表t2做了全表扫描，这里扫描行数是100万。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);2. 之后的join语句，扫描表t1，这里的扫描行数是1000；join比较过程中，做了1000次带索引的查询。

相比于优化前的join语句需要做10亿次条件判断来说，这个优化效果还是很明显的。

总体来看，不论是在原表上加索引，还是用有索引的临时表，我们的思路都是让join语句能够用上被驱动表上的索引，来触发BKA算法，提升查询性能。

扩展-hash join看到这里你可能发现了，其实上面计算10亿次那个操作，看上去有点儿傻。

如果join_buffer里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是10亿次判断，而是100万次hash查找。

这样的话，整条语句的执行速度就快多了吧？确实如此。

这，也正是MySQL的优化器和执行器一直被诟病的一个原因：不支持哈希join。

并且，MySQL官方的roadmap，也是迟迟没有把这个优化排上议程。

实际上，这个优化思路，我们可以自己实现在业务端。

实现流程大致如下：

1. select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构，比如C++里的set、PHP的dict这样的数据结构。

2. select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。

3. 把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据。

满足匹配的条件的这行数据，就作为结果集的一行。

理论上，这个过程会比临时表方案的执行速度还要快一些。

如果你感兴趣的话，可以自己验证一下。

小结今天，我和你分享了Index Nested-Loop Join（NLJ）和Block Nested-Loop Join（BNL）的优化方法。

在这些优化方法中：

1. BKA优化是MySQL已经内置支持的，建议你默认使用；
2. BNL算法效率低，建议你都尽量转成BKA算法。

优化的方向就是给被驱动表的关联字段加上索引；
3. 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；
4. MySQL目前的版本还不支持hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

最后，我给你留下一道思考题吧。

我们在讲join语句的这两篇文章中，都只涉及到了两个表的join。

那么，现在有一个三个表join的需求，假设这三个表的表结构如下：
语句的需求实现如下的join逻辑：
现在为了得到最快的执行速度，如果让你来设计表t1、t2、t3上的索引，来支持这个join语句，你会加哪些索引呢？同时，如果我希望你用straight_join来重写这个语句，配合你创建的索引，你就需要安排连接顺序，你主要考虑的因素是什么呢？

问题解析

到底可不可以使用join？在实际生产中，关于join语句使用的问题，一般会集中在以下两类：

1. 我们DBA不让使用join，使用join有什么问题呢？2. 如果有两个大小不同的表做join，应该用哪个表做驱动表呢？今天这篇文章，我就先跟你说说join语句到底是怎么执行的，然后再来回答这两个问题。

为了便于量化分析，我还是创建两个表t1和t2来和你说明。

可以看到，这两个表都有一个主键索引id和一个索引a，字段b上无索引。

存储过程idata()往表t2里插入了1000行数据，在表t1里插入的是100行数据。

Index Nested-Loop Join我们来看一下这个语句：

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响我们分析SQL语句的执行过程。

所以，为了便于分析执行过程中的性能问题，我改用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。

在这个语句CREATE TABLE t̀2 ̀( ìd ̀int(11) NOT NULL, a ̀int(11) DEFAULT NULL, b ̀int(11) DEFAULT NULL, PRIMARY KEY (̀ id )̀, KEY `a ̀(̀ a )̀) ENGINE=InnoDB;drop procedure idata;delimiter ;;create procedure idata()begin declare i int; set i=1; while(i<=1000)do insert into t2 values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();create table t1 like t2;insert into t1 (select * from t2 where id<=100)select * from t1 straight_join t2 on (t1.a=t2.a);里，t1 是驱动表，t2是被驱动表。

现在，我们来看一下这条语句的explain结果。

图1 使用索引字段join的 explain结果可以看到，在这条语句里，被驱动表t2的字段a上有索引，join过程用上了这个索引，因此这个语句的执行流程是这样的：

1. 从表t1中读入一行数据 R；

2. 从数据行R中，取出a字段到表t2里去查找；

3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；

4. 重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。

在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ。

它对应的流程图如下所示：

图2 Index Nested-Loop Join算法的执行流程在这个流程里：

1. 对驱动表t1做了全表扫描，这个过程需要扫描100行；

2. 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。

由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；

3. 所以，整个执行流程，总扫描行数是200。

现在我们知道了这个过程，再试着回答一下文章开头的两个问题。

先看第一个问题：能不能使用join?假设不使用join，那我们就只能用单表查询。

我们看看上面这条语句的需求，用单表查询怎么实现。

1. 执行select * from t1，查出表t1的所有数据，这里有100行；

2. 循环遍历这100行数据：

从每一行R取出字段a的值$R.a；

执行select * from t2 where a=$R.a；

把返回的结果和R构成结果集的一行。

可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join多了100次交互。

除此之外，客户端还要自己拼接SQL语句和结果。

显然，这么做还不如直接join好。

我们再来看看第二个问题：怎么选择驱动表？在这个join语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

假设被驱动表的行数是M。

每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。

每次搜索一棵树近似复杂度是以2为底的M的对数，记为log M，所以在被驱动表上查一行的时间复杂度是 2*log M。

假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是 N + N2log M。

显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。

到这里小结一下，通过上面的分析我们得到了两个结论：

1. 使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；

2. 如果使用join语句的话，需要让小表做驱动表。

但是，你需要注意，这个结论的前提是“可以使用被驱动表的索引”。

接下来，我们再看看被驱动表用不上索引的情况。

Simple Nested-Loop Join现在，我们把SQL语句改成这样：

由于表t2的字段b上没有索引，因此再用图2的执行流程时，每次到t2去匹配的时候，就要做一次222如果你没觉得这个影响有那么“显然”， 可以这么理解：N扩大1000倍的话，扫描行数就会扩大1000倍；而M扩大1000倍，扫描行数扩大不到10倍。

select * from t1 straight_join t2 on (t1.a=t2.b);全表扫描。

你可以先设想一下这个问题，继续使用图2的算法，是不是可以得到正确的结果呢？如果只看结果的话，这个算法是正确的，而且这个算法也有一个名字，叫做“Simple Nested-Loop Join”。

但是，这样算来，这个SQL请求就要扫描表t2多达100次，总共扫描100*1000=10万行。

这还只是两个小表，如果t1和t2都是10万行的表（当然了，这也还是属于小表的范围），就要扫描100亿行，这个算法看上去太“笨重”了。

当然，MySQL也没有使用这个Simple Nested-Loop Join算法，而是使用了另一个叫作“BlockNested-Loop Join”的算法，简称BNL。

Block Nested-Loop Join这时候，被驱动表上没有可用的索引，算法的流程是这样的：

1. 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；

2. 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

这个过程的流程图如下：

图3 Block Nested-Loop Join 算法的执行流程对应地，这条SQL语句的explain结果如下所示：

图4 不使用索引字段join的 explain结果可以看到，在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。

由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

前面我们说过，如果使用Simple Nested-Loop Join算法进行查询，扫描行数也是10万行。

因此，从时间复杂度上来说，这两个算法是一样的。

但是，Block Nested-Loop Join算法的这10万次判断是内存操作，速度上会快很多，性能也更好。

接下来，我们来看一下，在这种情况下，应该选择哪个表做驱动表。

假设小表的行数是N，大表的行数是M，那么在这个算法里：

1. 两个表都做一次全表扫描，所以总的扫描行数是M+N；

2. 内存中的判断次数是M*N。

可以看到，调换这两个算式中的M和N没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。

然后，你可能马上就会问了，这个例子里表t1才100行，要是表t1是一个大表，join_buffer放不下怎么办呢？join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。

如果放不下表t1的所有数据话，策略很简单，就是分段放。

我把join_buffer_size改成1200，再执行：

执行过程就变成了：

1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；

2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；

3. 清空join_buffer；

4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

执行流程图也就变成这样：

select * from t1 straight_join t2 on (t1.a=t2.b);图5 Block Nested-Loop Join – 两段图中的步骤4和5，表示清空join_buffer再复用。

这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去join”。

可以看到，这时候由于表t1被分成了两次放入join_buffer中，导致表t2会被扫描两次。

虽然分成两次放入join_buffer，但是判断等值条件的次数还是不变的，依然是(88+12)*1000=10万次。

我们再来看下，在这种情况下驱动表的选择问题。

假设，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。

注意，这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。

所以，在这个算法的执行过程中：

1. 扫描行数是 N+λNM；

2. 内存判断 N*M次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。

而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小。

所以结论是，应该让小表当驱动表。

当然，你会发现，在N+λNM这个式子里，λ才是影响扫描行数的关键因素，这个值越小越好。

刚刚我们说了N越大，分段数K越大。

那么，N固定的时候，什么参数会影响K的大小呢？（也就是λ的大小）答案是join_buffer_size。

join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。

这就是为什么，你可能会看到一些建议告诉你，如果你的join语句很慢，就把join_buffer_size改大。

理解了MySQL执行join的两种算法，现在我们再来试着回答文章开头的两个问题。

第一个问题：能不能使用join语句？1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。

尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。

所以这种join尽量不要用。

所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“BlockNested Loop”字样。

第二个问题是：如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；

2. 如果是Block Nested-Loop Join算法：

在join_buffer_size足够大的时候，是一样的；

在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

当然了，这里我需要说明下，什么叫作“小表”。

我们前面的例子是没有加条件的。

如果我在语句的where条件加上 t2.id<=50这个限定条件，再来看下这两条语句：

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;注意，为了让两条语句的被驱动表都用不上索引，所以join字段都使用了没有索引的字段b。

但如果是用第二个语句的话，join_buffer只需要放入t2的前50行，显然是更好的。

所以这里，“t2的前50行”是那个相对小的表，也就是“小表”。

我们再来看另外一组例子：

这个例子里，表t1 和 t2都是只有100行参加join。

但是，这两条语句每次查询放入join_buffer中的数据是不一样的：

表t1只查字段b，因此如果把t1放到join_buffer中，则join_buffer中只需要放入b的值；

表t2需要查所有的字段，因此如果把表t2放到join_buffer中的话，就需要放入三个字段id、a和b。

这里，我们应该选择表t1作为驱动表。

也就是说在这个例子里，“只需要一列参与join的表t1”是那个相对小的表。

所以，更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

小结今天，我和你介绍了MySQL执行join语句的两种可能算法，这两种算法是由能否使用被驱动表的索引决定的。

而能否用上被驱动表的索引，对join语句的性能影响很大。

通过对Index Nested-Loop Join和Block Nested-Loop Join两个算法执行过程的分析，我们也得到了文章开头两个问题的答案：

1. 如果可以使用被驱动表的索引，join语句还是有其优势的；

2. 不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；

3. 在使用join的时候，应该让小表做驱动表。

最后，又到了今天的问题时间。

我们在上文说到，使用Block Nested-Loop Join算法，可能会因为join_buffer不够大，需要对被select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;驱动表做多次全表扫描。

我的问题是，如果被驱动表是一个大表，并且是一个冷数据表，除了查询过程中可能会导致IO压力大以外，你觉得对这个MySQL服务还有什么更严重的影响吗？（这个问题需要结合上一篇文章的知识点）你可以把你的结论和分析写在留言区，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上一篇文章最后留下的问题是，如果客户端由于压力过大，迟迟不能接收数据，会对服务端造成什么严重的影响。

这个问题的核心是，造成了“长事务”。

至于长事务的影响，就要结合我们前面文章中提到的锁、MVCC的知识点了。

如果前面的语句有更新，意味着它们在占用着行锁，会导致别的语句更新被锁住；

当然读的事务也有问题，就是会导致undo log不能被回收，导致回滚段空间膨胀。

问题解析

我查这么多数据，会不会把数据库内存打爆？我经常会被问到这样一个问题：我的主机内存只有100G，现在要对一个200G的大表做全表扫描，会不会把数据库主机的内存用光了？这个问题确实值得担心，被系统OOM（out of memory）可不是闹着玩的。

但是，反过来想想，逻辑备份的时候，可不就是做整库扫描吗？如果这样就会把内存吃光，逻辑备份不是早就挂了？所以说，对大表做全表扫描，看来应该是没问题的。

但是，这个流程到底是怎么样的呢？全表扫描对server层的影响假设，我们现在要对一个200G的InnoDB表db1. t，执行一个全表扫描。

当然，你要把扫描结果保存在客户端，会使用类似这样的命令：

你已经知道了，InnoDB的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表t的主键索引。

这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。

那么，这个“结果集”存在哪里呢？mysql -h$host -P$port -u$user -p$pwd -e “select * from db1.t” > $target_file实际上，服务端并不需要保存一个完整的结果集。

取数据和发数据的流程是这样的：

1. 获取一行，写到net_buffer中。

这块内存的大小是由参数net_buffer_length定义的，默认是16k。

2. 重复获取行，直到net_buffer写满，调用网络接口发出去。

3. 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。

4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket sendbuffer）写满了，进入等待。

直到网络栈重新可写，再继续发送。

这个过程对应的流程图如下所示。

图1 查询结果发送流程从这个流程中，你可以看到：

1. 一个查询在发送过程中，占用的MySQL内部的内存最大就是net_buffer_length这么大，并不会达到200G；

2. socket send buffer 也不可能达到200G（默认定义/proc/sys/net/core/wmem_default），如果socket send buffer被写满，就会暂停读数据的流程。

也就是说，MySQL是“边读边发的”，这个概念很重要。

这就意味着，如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。

比如下面这个状态，就是我故意让客户端不去读socket receive buffer中的内容，然后在服务端show processlist看到的结果。

图2 服务端发送阻塞如果你看到State的值一直处于“Sending to client”，就表示服务器端的网络栈写满了。

我在上一篇文章中曾提到，如果客户端使用–quick参数，会使用mysql_use_result方法。

这个方法是读一行处理一行。

你可以想象一下，假设有一个业务的逻辑比较复杂，每读一行数据以后要处理的逻辑如果很慢，就会导致客户端要过很久才会去取下一行数据，可能就会出现如图2所示的这种情况。

因此，对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用mysql_store_result这个接口，直接把查询结果保存到本地内存。

当然前提是查询返回结果不多。

在第30篇文章评论区，有同学说到自己因为执行了一个大查询导致客户端占用内存近20G，这种情况下就需要改用mysql_use_result接口了。

另一方面，如果你在自己负责维护的MySQL里看到很多个线程都处于“Sending to client”这个状态，就意味着你要让业务开发同学优化查询结果，并评估这么多的返回结果是否合理。

而如果要快速减少处于这个状态的线程的话，将net_buffer_length参数设置为一个更大的值是一个可选方案。

与“Sending to client”长相很类似的一个状态是“Sending data”，这是一个经常被误会的问题。

有同学问我说，在自己维护的实例上看到很多查询语句的状态是“Sending data”，但查看网络也没什么问题啊，为什么Sending data要这么久？实际上，一个查询语句的状态变化是这样的（注意：这里，我略去了其他无关的状态）：

MySQL查询语句进入执行阶段后，首先把状态设置成“Sending data”；

然后，发送执行结果的列相关的信息（meta data) 给客户端；

再继续执行语句的流程；

执行完成后，把状态设置成空字符串。

也就是说，“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。

比如，你可以构造一个锁等待的场景，就能看到Sending data状态。

图3 读全表被锁图 4 Sending data状态可以看到，session B明显是在等锁，状态显示为Sending data。

也就是说，仅当一个线程处于“等待客户端接收结果”的状态，才会显示”Sending to client”；而如果显示成“Sending data”，它的意思只是“正在执行”。

现在你知道了，查询的结果是分段发给客户端的，因此扫描全表，查询返回大量的数据，并不会把内存打爆。

在server层的处理逻辑我们都清楚了，在InnoDB引擎里面又是怎么处理的呢？ 扫描全表会不会对引擎系统造成影响呢？全表扫描对InnoDB的影响在第2和第15篇文章中，我介绍WAL机制的时候，和你分析了InnoDB内存的一个作用，是保存更新的结果，再配合redo log，就避免了随机写盘。

内存的数据页是在Buffer Pool (BP)中管理的，在WAL里Buffer Pool 起到了加速更新的作用。

而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。

在第2篇文章的评论区有同学问道，由于有WAL机制，当事务提交的时候，磁盘上的数据页是旧的，那如果这时候马上有一个查询要来读这个数据页，是不是要马上把redo log应用到数据页呢？答案是不需要。

因为这时候内存数据页的结果是最新的，直接读内存页就可以了。

你看，这时候查询根本不需要读磁盘，直接从内存拿结果，速度是很快的。

所以说，Buffer Pool还有加速查询的作用。

而Buffer Pool对查询的加速效果，依赖于一个重要的指标，即：内存命中率。

你可以在show engine innodb status结果中，查看一个系统当前的BP命中率。

一般情况下，一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要在99%以上。

执行show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。

比如图5这个命中率，就是99.0%。

图5 show engine innodb status显示内存命中率如果所有查询需要的数据页都能够直接从内存得到，那是最好的，对应的命中率就是100%。

但，这在实际生产上是很难做到的。

InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%。

在大约十年前，单机的数据量是上百个G，而物理内存是几个G；现在虽然很多服务器都能有128G甚至更高的内存，但是单机的数据量却达到了T级别。

所以，innodb_buffer_pool_size小于磁盘的数据量是很常见的。

如果一个 Buffer Pool满了，而又要从磁盘读入一个数据页，那肯定是要淘汰一个旧数据页的。

InnoDB内存管理用的是最近最少使用 (Least Recently Used, LRU)算法，这个算法的核心就是淘汰最久未使用的数据。

下图是一个LRU算法的基本模型。

图6 基本LRU算法InnoDB管理Buffer Pool的LRU算法，是用链表来实现的。

1. 在图6的状态1里，链表头部是P1，表示P1是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页；

2. 这时候有一个读请求访问P3，因此变成状态2，P3被移到最前面；

3. 状态3表示，这次访问的数据页是不存在于链表中的，所以需要在Buffer Pool中新申请一个数据页Px，加到链表头部。

但是由于内存已经满了，不能申请新的内存。

于是，会清空链表末尾Pm这个数据页的内存，存入Px的内容，然后放到链表头部。

4. 从效果上看，就是最久没有被访问的数据页Pm，被淘汰了。

这个算法乍一看上去没什么问题，但是如果考虑到要做一个全表扫描，会不会有问题呢？假设按照这个算法，我们要扫描一个200G的表，而这个表是一个历史数据表，平时没有业务访问它。

那么，按照这个算法扫描的话，就会把当前的Buffer Pool里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。

也就是说Buffer Pool里面主要放的是这个历史数据表的数据。

对于一个正在做业务服务的库，这可不妙。

你会看到，Buffer Pool的内存命中率急剧下降，磁盘压力增加，SQL语句响应变慢。

所以，InnoDB不能直接使用这个LRU算法。

实际上，InnoDB对LRU算法做了改进。

图 7 改进的LRU算法在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。

图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处。

也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。

改进后的LRU算法执行流程变成了下面这样。

1. 图7中状态1，要访问数据页P3，由于P3在young区域，因此和优化前的LRU算法一样，将其移到链表头部，变成状态2。

2. 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页Pm，但是新插入的数据页Px，是放在LRU_old处。

3. 处于old区域的数据页，每次被访问的时候都要做下面这个判断：

若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部；

如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。

1秒这个时间，是由参数innodb_old_blocks_time控制的。

其默认值是1000，单位毫秒。

这个策略，就是为了处理类似全表扫描的操作量身定制的。

还是以刚刚的扫描200G的历史数据表为例，我们看看改进后的LRU算法的操作逻辑：

1. 扫描过程中，需要新插入的数据页，都被放到old区域;2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；

2. 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。

可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了Buffer Pool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率。

小结今天，我用“大查询会不会把内存用光”这个问题，和你介绍了MySQL的查询结果，发送给客户端的过程。

由于MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集。

所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。

而对于InnoDB引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。

并且，由于InnoDB对LRU算法做了改进，冷数据的全表扫描，对Buffer Pool的影响也能做到可控。

当然，我们前面文章有说过，全表扫描还是比较耗费IO资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

最后，我给你留一个思考题吧。

我在文章中说到，如果由于客户端压力太大，迟迟不能接收结果，会导致MySQL无法发送结果而影响语句执行。

但，这还不是最糟糕的情况。

你可以设想出由于客户端的性能问题，对数据库影响更严重的例子吗？或者你是否经历过这样的场景？你又是怎么优化的？你可以把你的经验和分析写在留言区，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期的问题是，如果一个事务被kill之后，持续处于回滚状态，从恢复速度的角度看，你是应该重启等它执行结束，还是应该强行重启整个MySQL进程。

因为重启之后该做的回滚动作还是不能少的，所以从恢复速度的角度来说，应该让它自己结束。

当然，如果这个语句可能会占用别的锁，或者由于占用IO资源过多，从而影响到了别的语句执行的话，就需要先做主备切换，切到新主库提供服务。

切换之后别的线程都断开了连接，自动停止执行。

接下来还是等它自己执行完成。

这个操作属于我们在文章中说到的，减少系统压力，加速终止逻辑。

问题解析

为什么还有kill不掉的语句？在MySQL中有两个kill命令：一个是kill query +线程id，表示终止这个线程中正在执行的语句；一个是kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

不知道你在使用MySQL的时候，有没有遇到过这样的现象：使用了kill命令，却没能断开这个连接。

再执行show processlist命令，看到这条语句的Command列显示的是Killed。

你一定会奇怪，显示为Killed是什么意思，不是应该直接在show processlist的结果里看不到这个线程了吗？今天，我们就来讨论一下这个问题。

其实大多数情况下，kill query/connection命令是有效的。

比如，执行一个查询的过程中，发现执行时间太久，要放弃继续查询，这时我们就可以用kill query命令，终止这条查询语句。

还有一种情况是，语句处于锁等待的时候，直接使用kill命令也是有效的。

我们一起来看下这个例子：

图1 kill query 成功的例子可以看到，session C 执行kill query以后，session B几乎同时就提示了语句被中断。

这，就是我们预期的结果。

收到kill以后，线程做什么？但是，这里你要停下来想一下：session B是直接终止掉线程，什么都不管就直接退出吗？显然，这是不行的。

我在第6篇文章中讲过，当对一个表做增删改查操作时，会在表上加MDL读锁。

所以，session B虽然处于blocked状态，但还是拿着一个MDL读锁的。

如果线程被kill的时候，就直接终止，那之后这个MDL读锁就没机会被释放了。

这样看来，kill并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”。

实现上，当用户执行kill query thread_id_B时，MySQL里处理kill命令的线程做了两件事：

1. 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；

2. 给session B的执行线程发一个信号。

为什么要发信号呢？因为像图1的我们例子里面，session B处于锁等待状态，如果只是把session B的线程状态设置THD::KILL_QUERY，线程B并不知道这个状态变化，还是会继续等待。

发一个信号的目的，就是让session B退出等待，来处理这个THD::KILL_QUERY状态。

其实，这跟Linux的kill命令类似，kill -N pid并不是让进程直接停止，而是给进程发一个信号，然后进程处理这个信号，进入终止逻辑。

只是对于MySQL的kill命令来说，不需要传信号量参数，就只有“停止”这个命令。

上面的分析中，隐含了这么三层意思：

1. 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是THD::KILL_QUERY，才开始进入语句终止逻辑；

2. 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；

3. 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

到这里你就知道了，原来不是“说停就停的”。

接下来，我们再看一个kill不掉的例子，也就是我们在前面第29篇文章中提到的innodb_thread_concurrency 不够用的例子。

首先，执行set global innodb_thread_concurrency=2，将InnoDB的并发线程上限数设置为2；然后，执行下面的序列：

图2 kill query 无效的例子可以看到：

1. sesssion C执行的时候被堵住了；

2. 但是session D执行的kill query C命令却没什么效果，3. 直到session E执行了kill connection命令，才断开了session C的连接，提示“Lostconnection to MySQL server during query”，4. 但是这时候，如果在session E中执行show processlist，你就能看到下面这个图。

图3 kill connection之后的效果这时候，id=12这个线程的Commnad列显示的是Killed。

也就是说，客户端虽然断开了连接，但实际上服务端上这条语句还在执行过程中。

为什么在执行kill query命令时，这条语句不像第一个例子的update语句一样退出呢？在实现上，等行锁时，使用的是pthread_cond_timedwait函数，这个等待状态可以被唤醒。

但是，在这个例子里，12号线程的等待逻辑是这样的：每10毫秒判断一下是否可以进入InnoDB执行，如果不行，就调用nanosleep函数进入sleep状态。

也就是说，虽然12号线程的状态已经被设置成了KILL_QUERY，但是在这个等待进入InnoDB的循环过程中，并没有去判断线程的状态，因此根本不会进入终止逻辑阶段。

而当session E执行kill connection 命令时，是这么做的，1. 把12号线程状态设置为KILL_CONNECTION；

2. 关掉12号线程的网络连接。

因为有这个操作，所以你会看到，这时候session C收到了断开连接的提示。

那为什么执行show processlist的时候，会看到Command列显示为killed呢？其实，这就是因为在执行show processlist的时候，有一个特别的逻辑：

所以其实，即使是客户端退出了，这个线程的状态仍然是在等待中。

那这个线程什么时候会退出呢？答案是，只有等到满足进入InnoDB的条件后，session C的查询语句继续执行，然后才有可能判断到线程状态已经变成了KILL_QUERY或者KILL_CONNECTION，再进入终止逻辑阶段。

到这里，我们来小结一下。

这个例子是kill无效的第一类情况，即：线程没有执行到判断线程状态的逻辑。

跟这种情况相同的，还有由于IO压力过大，读写IO的函数一直无法返回，导致不能及时判断线程的状态。

如果一个线程的状态是KILL_CONNECTION，就把Command列显示成Killed。

另一类情况是，终止逻辑耗时较长。

这时候，从show processlist结果上看也是Command=Killed，需要等到终止逻辑完成，语句才算真正完成。

这类情况，比较常见的场景有以下几种：

1. 超大事务执行期间被kill。

这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。

2. 大查询回滚。

如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待IO资源，导致耗时较长。

3. DDL命令执行到最后阶段，如果被kill，需要删除中间过程的临时文件，也可能受IO资源影响耗时较久。

之前有人问过我，如果直接在客户端通过Ctrl+C命令，是不是就可以直接终止线程呢？答案是，不可以。

这里有一个误解，其实在客户端的操作只能操作到客户端的线程，客户端和服务端只能通过网络交互，是不可能直接操作服务端线程的。

而由于MySQL是停等协议，所以这个线程执行的语句还没有返回的时候，再往这个连接里面继续发命令也是没有用的。

实际上，执行Ctrl+C的时候，是MySQL客户端另外启动一个连接，然后发送一个kill query 命令。

所以，你可别以为在客户端执行完Ctrl+C就万事大吉了。

因为，要kill掉一个线程，还涉及到后端的很多操作。

另外两个关于客户端的误解在实际使用中，我也经常会碰到一些同学对客户端的使用有误解。

接下来，我们就来看看两个最常见的误解。

第一个误解是：如果库里面的表特别多，连接就会很慢。

有些线上的库，会包含很多表（我见过最多的一个库里有6万个表）。

这时候，你就会发现，每次用客户端连接都会卡在下面这个界面上。

图4 连接等待而如果db1这个库里表很少的话，连接起来就会很快，可以很快进入输入命令的状态。

因此，有同学会认为是表的数目影响了连接性能。

从第一篇文章你就知道，每个客户端在和服务端建立连接的时候，需要做的事情就是TCP握手、用户校验、获取权限。

但这几个操作，显然跟库里面表的个数无关。

但实际上，正如图中的文字提示所说的，当使用默认参数连接的时候，MySQL客户端会提供一个本地库名和表名补全的功能。

为了实现这个功能，客户端在连接成功后，需要多做一些操作：

1. 执行show databases；

2. 切到db1库，执行show tables；

3. 把这两个命令的结果用于构建一个本地的哈希表。

在这些操作中，最花时间的就是第三步在本地构建哈希表的操作。

所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。

也就是说，我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。

图中的提示也说了，如果在连接命令中加上-A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。

这里自动补全的效果就是，你在输入库名或者表名的时候，输入前缀，可以使用Tab键自动补全表名或者显示提示。

实际使用中，如果你自动补全功能用得并不多，我建议你每次使用的时候都默认加-A。

其实提示里面没有说，除了加-A以外，加–quick(或者简写为-q)参数，也可以跳过这个阶段。

但是，这个–quick是一个更容易引起误会的参数，也是关于客户端常见的一个误解。

你看到这个参数，是不是觉得这应该是一个让服务端加速的参数？但实际上恰恰相反，设置了这个参数可能会降低服务端的性能。

为什么这么说呢？MySQL客户端发送请求后，接收服务端返回结果的方式有两种：

1. 一种是本地缓存，也就是在本地开一片内存，先把结果存起来。

如果你用API开发，对应的就是mysql_store_result 方法。

2. 另一种是不缓存，读一个处理一个。

如果你用API开发，对应的就是mysql_use_result方法。

MySQL客户端默认采用第一种方式，而如果加上–quick参数，就会使用第二种不缓存的方式。

采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。

关于服务端的具体行为，我会在下一篇文章再和你展开说明。

那你会说，既然这样，为什么要给这个参数取名叫作quick呢？这是因为使用这个参数可以达到以下三点效果：

第一点，就是前面提到的，跳过表名自动补全功能。

第二点，mysql_store_result需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能；

第三点，是不会把执行命令记录到本地的命令历史文件。

所以你看到了，–quick参数的意思，是让客户端变得更快。

小结在今天这篇文章中，我首先和你介绍了MySQL中，有些语句和连接“kill不掉”的情况。

这些“kill不掉”的情况，其实是因为发送kill命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。

而被kill的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。

并且，终止逻辑本身也是需要耗费时间的。

所以，如果你发现一个线程处于Killed状态，你可以做的事情就是，通过影响系统环境，让这个Killed状态尽快结束。

比如，如果是第一个例子里InnoDB并发度的问题，你就可以临时调大innodb_thread_concurrency的值，或者停掉别的线程，让出位子给这个线程执行。

而如果是回滚逻辑由于受到IO资源限制执行得比较慢，就通过减少系统压力让它加速。

做完这些操作后，其实你已经没有办法再对它做什么了，只能等待流程自己完成。

最后，我给你留下一个思考题吧。

如果你碰到一个被killed的事务一直处于回滚状态，你认为是应该直接把MySQL进程强行重启，还是应该让它自己执行完成呢？为什么呢？你可以把你的结论和分析写在留言区，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上一篇文章末尾，给你留下的问题是，希望你分享一下误删数据的处理经验。

@苍茫 同学提到了一个例子，我觉得值得跟大家分享一下。

运维的同学直接拷贝文本去执行，SQL语句截断，导致数据库执行出错。

从浏览器拷贝文本执行，是一个非常不规范的操作。

除了这个例子里面说的SQL语句截断问题，还可能存在乱码问题。

一般这种操作，如果脚本的开发和执行不是同一个人，需要开发同学把脚本放到git上，然后把git地址，以及文件的md5发给运维同学。

这样就要求运维同学在执行命令之前，确认要执行的文件的md5，跟之前开发同学提供的md5相同才能继续执行。

另外，我要特别点赞一下@苍茫 同学复现问题的思路和追查问题的态度。

@linhui0705 同学提到的“四个脚本”的方法，我非常推崇。

这四个脚本分别是：备份脚本、执行脚本、验证脚本和回滚脚本。

如果能够坚持做到，即使出现问题，也是可以很快恢复的，一定能降低出现故障的概率。

不过，这个方案最大的敌人是这样的思想：这是个小操作，不需要这么严格。

@Knight²º¹ 给了一个保护文件的方法，我之前没有用过这种方法，不过这确实是一个不错的思路。

为了数据安全和服务稳定，多做点预防方案的设计讨论，总好过故障处理和事后复盘。

方案设计讨论会和故障复盘会，这两种会议的会议室气氛完全不一样。

经历过的同学一定懂的。

Leon  2kill connection本质上只是把客户端的sql连接断开，后面的执行流程还是要走kill query的，是这样理解吧2019-01-30 作者回复这个理解非常到位额外的一个不同就是show processlist的时候，kill connection会显示“killed”这两句加起来可以用来替换我们文中的描述2019-01-30Mr.sylar  2老师，我想问下这些原理的”渔”的方法除了看源码，还有别的建议吗2019-01-25 作者回复不同的知识点不太一样哈，有些可以看文档；

有些可以自己验证；

还有就是看其他人文章，加验证；（就是我们这个专栏的方法^_^）2019-01-25夹心面包  2对于结尾的问题,我觉得肯定是等待,即便是mysql重启,也是需要对未提交的事务进行回滚操作的,保证数据库的一致性2019-01-25Ryoma  1想得简单点：既然事务处于回滚状态了，重启MySQL这部分事务还是需要回滚。

私以为让它执行完成比较好。

2019-01-25斜面镜子 Bill  0“采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢” 这个怎么理解？2019-01-28 作者回复堵住了不就变慢了2019-01-28700  0精选留言 0老师，您好。

客户端版本如下：

mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using EditLine wrapper老师，再请教另一个问题。

并非所有的 DDL 操作都可以通过主从切换来实现吧？不适用的场景有哪些呢？2019-01-27 作者回复对，其实只有 改索引、 加最后一列、删最后一列其他的大多数不行，比如删除中间一列这种2019-01-28千年孤独  0可能不是本章讨论的问题，我想请问老师“MySQL使用自增ID和UUID作为主键的优劣”，基于什么样的业务场景用哪种好?2019-01-27 作者回复后面会有文章会提到这个问题哈：）2019-01-27Geek_a67865  0老师好，我猜发条橙子的问题 因为很多日志监控会统计error日志，这样并不很优雅，觉得他是想有什么办法规避这种并发引起的问题，2019-01-26 作者回复嗯嗯 不过我也确实没有想到更好的方法毕竟两个线程要同时发起一个insert操作，这个服务端也拦不住呀2019-01-26路过  0老师，kill语法是：

KILL [CONNECTION | QUERY] processlist_idprocesslist_id是conn_id，不是thd_id.通过对比sys.processlist表中的信息就可以知道了。

通过查询官方文档也说明了：

thd_id：The thread ID.conn_id：The connection ID.所以，这篇文章开头的：

在 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id感觉有点不对。

请老师指正。

谢谢！2019-01-26 作者回复这两个是一样的吧？都是对应show processlist这个命令结果里的第一列2019-01-26HuaMax  0课后题。

我认为需要看当时的业务场景。

重启会导致其他的连接也断开，返回给其他业务连接丢失的错误。

如果有很多事务在等待该事务的锁，则应该重启，让其他事务快速重试获取锁。

另外如果是RR的事务隔离级别，长事务会因为数据可见性的问题，对于多版本的数据需要找到正确的版本，对读性能是不是也会有影响，这时候重启也更好。

个人理解，请老师指正。

2019-01-26 作者回复有考虑到对其他线程的影响，这个其实这种时候往往是要先考虑切换（当然重启也是切换的）如果只看恢复时间的话，等待会更快 2019-01-26Geek_a67865  0也遇到@发条橙子一样的问题，例如队列两个消息同时查询库存，发现都不存在，然后就都执行插入语句，一条成功，一条报唯一索引异常，这样程序日志会一直显示一个唯一索引报错，然后重试执行更新，我暂时是强制查主库2019-01-26 作者回复“我暂时是强制查主库” 从这就看你是因为读是读的备库，才出现这个问题的是吧。

发条橙子的问题是，他都是操作主库。

其实如果索引有唯一键，就直接上insert。

然后碰到违反唯一键约束就报错，这个应该就是唯一键约束正常的用法吧2019-01-26gaohueric  0老师您好，一个表中 1个主键，2个唯一索引，1个普通索引 4个普通字段，当插入一条全部字段不为空的数据时，此时假设有4个索引文件，分别对应 主键 唯一性索引，普通索引，假设内存中没有这个数据页，那么server是直接调用innodb的接口，然后依次校验 （读取磁盘数据，验证唯一性）主键，唯一性索引，然后确认无误A时刻之后，吧主键和唯一性索引的写入内存，再把普通索引写入change buffer？那普通数据呢，是不是跟着主键一块写入内存了？2019-01-26 作者回复1. 是的，如果普通索引上的数据页这时候没有在内存中，就会使用change buffer2. “那普通数据呢，是不是跟着主键一块写入内存了？” 你说的是无索引的字段是吧，这些数据就在主键索引上，其实改的就是主键索引。

2019-01-26700  0老师，您好。

我继续接着我上条留言。

关于2），因为是测试机，我是直接 tail -0f 观察 general log 输出的。

确实没看到 KILL QUERY 等字眼。

数据库版本是 MySQL 5.7.24。

关于4），文中您不是这样说的吗？2.但是 session D 执行的 kill query C 命令却没什么效果， 3.直到 session E 执行了 kill connection 命令，才断开了 session C 的连接，提示“Lost connection to MySQL server during query”， 感谢您的解答。

2019-01-26 作者回复1. 你的客户端版本是什么 mysql –version 看看3. 嗯，是的，连接会断开，但是这个语句在server端还是会继续执行 （如果kill query 无效的话）2019-01-26700  0老师，请教。

1）文中开头说“当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的”。

我个人在平时使用中就是按默认的执行，不管这个线程有无正在执行语句。

不知这样会有什么潜在问题？2）文中说“实际上，执行 Ctrl+C 的时候，是 MySQL 客户端另外启动一个连接，然后发送一个 kill query 命令“。

这个怎么解释呢？我开启 general log 的时候执行 Ctrl+C 或 Ctrl+D 并没有看到有另外启动一个连接，也没有看到 kill query 命令。

general log 中仅看到对应线程 id 和 Quit。

3）MySQL 为什么要同时存在 kill query 和 kill connection，既然 kill query 有无效的场景，干嘛不直接存在一个 kill connection 命令就好了？那它俩分别对应的适用场景是什么，什么时候考虑 kill query，什么时候考虑 kill connection？我个人觉得连接如果直接被 kill 掉大不了再重连一次好了。

也没啥损失。

4）小小一个总结，不知对否？kill query - 会出现无法 kill 掉的情况，只能再次执行 kill connection。

kill connection - 会出现 Command 列显示成 Killed 的情况。

2019-01-25 作者回复1. 一般你执行kill就是要停止正在执行的语句，所以问题不大2. 不应该呀， KILL QUERY 是大写哦，你再grep一下日志；

3. 多提供一种方法嘛。

kill query是指你只是想停止这个语句，但是事务不会回滚。

一般kill query是发生在客户端执行ctrl+c的时候啦。

平时紧急处理确实直接用kill + thread_id。

好问题4. 对，另外，在kill query无效的时候，其实kill connection也是无效的2019-01-26Justin  0想咨询一个问题 如果走索引找寻比如age=11的人的时候是只会锁age=10到age=12吗 如果那个索引页包含了从5到13的数据 是只会锁离11最近的还是说二分查找时候每一个访问到的都会锁呢2019-01-25 作者回复只会锁左右。

2019-01-26往事随风，顺其自然  012 号线程的等待逻辑是这样的：每 10 毫秒判断一下是否可以进入 InnoDB 执行，如果不行，如果不行，就调用 nanosleep 函数进入 sleep状态。

这里为什么是10毫秒判断一下？怎么查看和设置这个参数？2019-01-25发条橙子 。

 0老师我这里问一下唯一索引的问题 ，希望老师能给点思路背景 ： 一张商品库存表 ， 如果表里没这个商品则插入 ，如果已经存在就更新库存 。

同步这个库存表是异步的 ，每次添加商品库存成功后会发消息 ， 收到消息后会去表里新增/更新库存问题 ： 商品库存表会有一个 商品的唯一索引。

当我们批量添加同一商品库存后会批量发消息 ，消息同时生效后去处理就有了并发的问题 。

这时候两个消息都判断表里没有该商品记录， 但是插入的时候就会有一个消息插入成功，另一个消息执行失败报唯一索引的错误， 之后消息重试走更新的逻辑。

这个这样做对业务没有影响 ，但是现在批量添加的需求量上来了 ，线上一直报这种错误日志也不是个办法， 我能想到的除了 catch 掉这个异常就没什么其他思路了。

老师能给一些其他的思路么2019-01-25 作者回复有唯一索引了，就直接插入，然后出现唯一性约束就放弃，这个逻辑的问题是啥，我感觉挺好的呀是不是我没有get到问题的点2019-01-25AI杜嘉嘉  0我想请问下老师，一个事务执行很长时间，我去kill。

那么，执行这个事务过程中的数据会不会回滚？2019-01-25 作者回复这个事务执行过程中新生成的数据吗？ 会回滚的2019-01-25曾剑  0曾剑 0今天的问题，我觉得得让他自己执行完成后自动恢复。

因为强制重启后该做的回滚还是会继续做。

2019-01-25Dkey  0老师，请教一个 第八章 的问题。

关于可见性判断，文中都是说事务id大于高水位都不可见。

如果等于是不是也不可见。

还有一个，readview中是否不包含当前事务id。

谢谢老师2019-01-25 作者回复代码实现上，事务生成trxid后，trxid的分配器会+1，以这个加1以后的数作为高水位，所以“等于”是不算的。

其实有没有包含是一样的，实现上没有包含。

2019-01-25```

问题解析

误删数据后除了跑路，还能怎么办？今天我要和你讨论的是一个沉重的话题：误删数据。

在前面几篇文章中，我们介绍了MySQL的高可用架构。

当然，传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

虽然我们之前遇到的大多数的数据被删，都是运维同学或者DBA背锅的。

但实际上，只要有数据操作权限的同学，都有可能踩到误删数据这条线。

今天我们就来聊聊误删数据前后，我们可以做些什么，减少误删数据的风险，和由误删数据带来的损失。

为了找到解决误删数据的更高效的方法，我们需要先对和MySQL相关的误删数据，做下分类：

1. 使用delete语句误删数据行；

2. 使用drop table或者truncate table语句误删数据表；

3. 使用drop database语句误删数据库；

4. 使用rm命令误删整个MySQL实例。

误删行在第24篇文章中，我们提到如果是使用delete语句误删了数据行，可以用Flashback工具通过闪回把数据恢复回来。

Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。

而能够使用这个方案的前提是，需要确保binlog_format=row 和 binlog_row_image=FULL。

具体恢复数据时，对单个事务做如下处理：

1. 对于insert语句，对应的binlog event类型是Write_rows event，把它改成Delete_rows event即可；

2. 同理，对于delete语句，也是将Delete_rows event改为Write_rows event；

3. 而如果是Update_rows的话，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

如果误操作不是一个，而是多个，会怎么样呢？比如下面三个事务：

现在要把数据库恢复回这三个事务操作之前的状态，用Flashback工具解析binlog后，写回主库的命令是：

也就是说，如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

需要说明的是，我不建议你直接在主库上执行这些操作。

恢复数据比较安全的做法，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。

为什么要这么做呢？这是因为，一个在执行线上逻辑的主库，数据状态的变更往往是有关联的。

可能由于发现数据问题的时间晚了一点儿，就导致已经在之前误操作的基础上，业务代码逻辑又继续修改了其他数据。

所以，如果这时候单独恢复这几行数据，而又未经确认的话，就可能会出现对数据的二次破(A)delete …(B)insert …(C)update …(reverse C)update …(reverse B)delete …(reverse A)insert …坏。

当然，我们不止要说误删数据的事后处理办法，更重要是要做到事前预防。

我有以下两个建议：

1. 把sql_safe_updates参数设置为on。

这样一来，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。

2. 代码上线前，必须经过SQL审计。

你可能会说，设置了sql_safe_updates=on，如果我真的要把一个小表的数据全部删掉，应该怎么办呢？如果你确定这个删除操作没问题的话，可以在delete语句中加上where条件，比如where id>=0。

但是，delete全表是很慢的，需要生成回滚日志、写redo、写binlog。

所以，从性能角度考虑，你应该优先考虑使用truncate table或者drop table命令。

使用delete命令删除的数据，你还可以用Flashback来恢复。

而使用truncate /drop table和dropdatabase命令删除的数据，就没办法通过Flashback来恢复了。

为什么呢？这是因为，即使我们配置了binlog_format=row，执行这三个命令时，记录的binlog还是statement格式。

binlog里面就只有一个truncate/drop 语句，这些信息是恢复不出数据的。

那么，如果我们真的是使用这几条命令误删数据了，又该怎么办呢？误删库/表这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。

这个方案要求线上有定期的全量备份，并且实时备份binlog。

在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点；

2. 用备份恢复出一个临时库；

3. 从日志备份里面，取出凌晨0点之后的日志；

4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

这个流程的示意图如下所示：

图1 数据恢复流程-mysqlbinlog方法关于这个过程，我需要和你说明如下几点：

1. 为了加速数据恢复，如果这个临时库上有多个数据库，你可以在使用mysqlbinlog命令时，加上一个–database参数，用来指定误删表所在的库。

这样，就避免了在恢复数据时还要应用其他库日志的情况。

2. 在应用日志的时候，需要跳过12点误操作的那个语句的binlog：

如果原实例没有使用GTID模式，只能在应用到包含12点的binlog文件的时候，先用–stop-position参数执行到误操作之前的日志，然后再用–start-position从误操作之后的日志继续执行；

如果实例使用了GTID模式，就方便多了。

假设误操作命令的GTID是gtid1，那么只需要执行set gtid_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合，之后按顺序执行binlog的时候，就会自动跳过误操作的语句。

不过，即使这样，使用mysqlbinlog方法恢复数据还是不够快，主要原因有两个：

1. 如果是误删表，最好就是只恢复出这张表，也就是只重放这张表的操作，但是mysqlbinlog工具并不能指定只解析一个表的日志；

2. 用mysqlbinlog解析出日志应用，应用日志的过程就只能是单线程。

我们在第26篇文章中介绍的那些并行复制的方法，在这里都用不上。

一种加速的方法是，在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，这样：

1. 在start slave之前，先通过执行change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；

2. 这样做也可以用上并行复制技术，来加速整个数据恢复过程。

这个过程的示意图如下所示。

图2 数据恢复流程-master-slave方法可以看到，图中binlog备份系统到线上备库有一条虚线，是指如果由于时间太久，备库上已经删除了临时实例需要的binlog的话，我们可以从binlog备份系统中找到需要的binlog，再放回备库中。

假设，我们发现当前临时实例需要的binlog是从master.000005开始的，但是在备库上执行showbinlogs 显示的最小的binlog文件是master.000007，意味着少了两个binlog文件。

这时，我们就需要去binlog备份系统中找到这两个文件。

把之前删掉的binlog放回备库的操作步骤，是这样的：

1. 从备份系统下载master.000005和master.000006这两个文件，放到备库的日志目录下；

2. 打开日志目录下的master.index文件，在文件开头加入两行，内容分别是“./master.000005”和“./master.000006”;3. 重启备库，目的是要让备库重新识别这两个日志文件；

3. 现在这个备库上就有了临时库需要的所有binlog了，建立主备关系，就可以正常同步了。

不论是把mysqlbinlog工具解析出的binlog文件应用到临时库，还是把临时库接到备库上，这两个方案的共同点是：误删库或者表后，恢复数据的思路主要就是通过备份，再加上应用binlog的方式。

也就是说，这两个方案都要求备份系统定期备份全量日志，而且需要确保binlog在被从本地删除之前已经做了备份。

但是，一个系统不可能备份无限的日志，你还需要根据成本和磁盘空间资源，设定一个日志保留的天数。

如果你的DBA团队告诉你，可以保证把某个实例恢复到半个月内的任意时间点，这就表示备份系统保留的日志时间就至少是半个月。

另外，我建议你不论使用上述哪种方式，都要把这个数据恢复功能做成自动化工具，并且经常拿出来演练。

为什么这么说呢？这里的原因，主要包括两个方面：

1. 虽然“发生这种事，大家都不想的”，但是万一出现了误删事件，能够快速恢复数据，将损失降到最小，也应该不用跑路了。

2. 而如果临时再手忙脚乱地手动操作，最后又误操作了，对业务造成了二次伤害，那就说不过去了。

延迟复制备库虽然我们可以通过利用并行复制来加速恢复数据的过程，但是这个方案仍然存在“恢复时间不可控”的问题。

如果一个库的备份特别大，或者误操作的时间距离上一个全量备份的时间较长，比如一周一备的实例，在备份之后的第6天发生误操作，那就需要恢复6天的日志，这个恢复时间可能是要按天来计算的。

那么，我们有什么方法可以缩短恢复数据需要的时间呢？如果有非常核心的业务，不允许太长的恢复时间，我们可以考虑搭建延迟复制的备库。

这个功能是MySQL 5.6版本引入的。

一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库持续保持跟主库有N秒的延迟。

比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。

这时候到这个备库上执行stopslave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

这样的话，你就随时可以得到一个，只需要最多再追1小时，就可以恢复出数据的临时实例，也就缩短了整个数据恢复需要的时间。

预防误删库/表的方法虽然常在河边走，很难不湿鞋，但终究还是可以找到一些方法来避免的。

所以这里，我也会给你一些减少误删操作风险的建议。

第一条建议是，账号分离。

这样做的目的是，避免写错命令。

比如：

我们只给业务开发同学DML权限，而不给truncate/drop权限。

而如果业务开发人员有DDL需求的话，也可以通过开发管理系统得到支持。

即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。

第二条建议是，制定操作规范。

这样做的目的，是避免写错要删除的表名。

比如：

在删除数据表之前，必须先对表做改名操作。

然后，观察一段时间，确保对业务无影响以后再删除这张表。

改表名的时候，要求给表名加固定的后缀（比如加_to_be_deleted)，然后删除表的动作必须通过管理系统执行。

并且，管理系删除表的时候，只能删除固定后缀的表。

rm删除数据其实，对于一个有高可用机制的MySQL集群来说，最不怕的就是rm删除数据了。

只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。

这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。

当然了，现在不止是DBA有自动化系统，SA（系统管理员）也有自动化系统，所以也许一个批量下线机器的操作，会让你整个MySQL集群的所有节点都全军覆没。

应对这种情况，我的建议只能是说尽量把你的备份跨机房，或者最好是跨城市保存。

小结今天，我和你讨论了误删数据的几种可能，以及误删后的处理方法。

但，我要强调的是，预防远比处理的意义来得大。

另外，在MySQL的集群方案中，会时不时地用到备份来恢复实例，因此定期检查备份的有效性也很有必要。

如果你是业务开发同学，你可以用show grants命令查看账户的权限，如果权限过大，可以建议DBA同学给你分配权限低一些的账号；你也可以评估业务的重要性，和DBA商量备份的周期、是否有必要创建延迟复制的备库等等。

数据和服务的可靠性不止是运维团队的工作，最终是各个环节一起保障的结果。

今天的课后话题是，回忆下你亲身经历过的误删数据事件吧，你用了什么方法来恢复数据呢？你在这个过程中得到的经验又是什么呢？你可以把你的经历和经验写在留言区，我会在下一篇文章的末尾选取有趣的评论和你一起讨论。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上一篇文章给你留的问题，是关于空表的间隙的定义。

一个空表就只有一个间隙。

比如，在空表上执行：

这个查询语句加锁的范围就是next-key lock (-∞, supremum]。

验证方法的话，你可以使用下面的操作序列。

你可以在图4中看到显示的结果。

begin;select * from t where id>1 for update;图3 复现空表的next-key lock图4 show engine innodb status

问题解析

答疑文章（二）：用动态的观点看加锁
在第20和21篇文章中，我和你介绍了InnoDB的间隙锁、next-key lock，以及加锁规则。

在这两篇文章的评论区，出现了很多高质量的留言。

我觉得通过分析这些问题，可以帮助你加深对加锁规则的理解。

所以，我就从中挑选了几个有代表性的问题，构成了今天这篇答疑文章的主题，即：用动态的观点看加锁。

为了方便你理解，我们再一起复习一下加锁规则。

这个规则中，包含了两个“原则”、两个“优化”和一个“bug”：

原则1：加锁的基本单位是next-key lock。

希望你还记得，next-key lock是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

接下来，我们的讨论还是基于下面这个表t：

不等号条件里的等值查询有同学对“等值查询”提出了疑问：等值查询和“遍历”有什么区别？为什么我们文章的例子里面，where条件是不等号，这个过程里也有等值查询？我们一起来看下这个例子，分析一下这条查询语句的加锁范围：

利用上面的加锁规则，我们知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和(10, 15)。

也就是说，id=15这一行，并没有被加上行锁。

为什么呢？我们说加锁单位是next-key lock，都是前开后闭区间，但是这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁 (10, 15)。

但是，我们的查询语句中where条件是大于号和小于号，这里的“等值查询”又是从哪里来的呢？要知道，加锁动作是发生在语句执行过程中的，所以你在分析加锁行为的时候，要从索引上的数据结构开始。

这里，我再把这个过程拆解一下。

如图1所示，是这个表的索引id的示意图。

CREATE TABLE t̀ ̀( ìd ̀int(11) NOT NULL, c ̀int(11) DEFAULT NULL, d ̀int(11) DEFAULT NULL, PRIMARY KEY (̀ id )̀, KEY `c ̀(̀ c )̀) ENGINE=InnoDB;insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);begin;select * from t where id>9 and id<12 order by id desc for update;图1 索引id示意图1. 首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个id<12的值”。

2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙。

3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]。

也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。

等值查询的过程与上面这个例子对应的，是@发条橙子同学提出的问题：下面这个语句的加锁范围是什么？这条查询语句里用的是in，我们先来看这条语句的explain结果。

begin;select id from t where c in(5,20,10) lock in share mode;图2 in语句的explain结果可以看到，这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的。

在查找c=5的时候，先锁住了(0,5]。

但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。

同样的，执行c=10这个逻辑的时候，加锁的范围是(5,10] 和 (10,15)；执行c=20这个逻辑的时候，加锁的范围是(15,20] 和 (20,25)。

通过这个分析，我们可以知道，这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁。

你可能会说，这个加锁范围，不就是从(5,25)中去掉c=15的行锁吗？为什么这么麻烦地分段说呢？因为我要跟你强调这个过程：这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。

理解了这个加锁过程之后，我们就可以来分析下面例子中的死锁问题了。

如果同时有另外一个语句，是这么写的：

此时的加锁范围，又是什么呢？我们现在都知道间隙锁是不互锁的，但是这两条语句都会在索引c上的c=5、10、20这三行记录上加记录锁。

这里你需要注意一下，由于语句里面是order by c desc， 这三个记录锁的加锁顺序，是先锁c=20，然后c=10，最后是c=5。

也就是说，这两条语句要加锁相同的资源，但是加锁顺序相反。

当这两条语句并发执行的时候，就可能出现死锁。

关于死锁的信息，MySQL只保留了最后一个死锁的现场，但这个现场还是不完备的。

有同学在评论区留言到，希望我能展开一下怎么看死锁。

现在，我就来简单分析一下上面这个例子的死锁现场。

select id from t where c in(5,20,10) order by c desc for update;怎么看死锁？图3是在出现死锁后，执行show engine innodb status命令得到的部分输出。

这个命令会输出很多信息，有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。

图3 死锁现场我们来看看这图中的几个关键信息。

1. 这个结果分成三部分：

(1) TRANSACTION，是第一个事务的信息；

(2) TRANSACTION，是第二个事务的信息；

WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。

2. 第一个事务的信息中：

WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；

index c of table t̀est .̀̀ t`，说明在等的是表t的索引c上面的锁；

lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；

Record lock说明这是一个记录锁；

n_fields 2表示这个记录是两列，也就是字段c和主键字段id；

0: len 4; hex 0000000a; asc ;;是第一个字段，也就是c。

值是十六进制a，也就是10；

1: len 4; hex 0000000a; asc ;;是第二个字段，也就是主键id，值也是10；

这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格。

第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁。

当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。

别着急，我们从第二个事务的信息中推导出来。

3. 第二个事务显示的信息要多一些：

“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；

index c of table t̀est .̀̀ t ̀表示锁是在表t的索引c上；

hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁；

WAITING FOR THIS LOCK TO BE GRANTED，表示在等(c=5,id=5)这个记录锁。

从上面这些信息中，我们就知道：

1. “lock in share mode”的这条语句，持有c=5的记录锁，在等c=10的锁；

2. “for update”这个语句，持有c=20和c=10的记录锁，在等c=5的记录锁。

因此导致了死锁。

这里，我们可以得到两个结论：

1. 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；

2. 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句，来回滚。

怎么看锁等待？看完死锁，我们再来看一个锁等待的例子。

在第21篇文章的评论区，@Geek_9ca34e 同学做了一个有趣验证，我把复现步骤列出来：

图4 delete导致间隙变化可以看到，由于session A并没有锁住c=10这个记录，所以session B删除id=10这一行是可以的。

但是之后，session B再想insert id=10这一行回去就不行了。

现在我们一起看一下此时show engine innodb status的结果，看看能不能给我们一些提示。

锁信息是在这个命令输出结果的TRANSACTIONS这一节。

你可以在文稿中看到这张图片图 5 锁等待信息我们来看几个关键信息。

1. index PRIMARY of table t̀est .̀̀ t ̀，表示这个语句被锁住是因为表t主键上的某个锁。

2. lock_mode X locks gap before rec insert intention waiting 这里有几个信息：

insert intention表示当前线程准备插入一个记录，这是一个插入意向锁。

为了便于理解，你可以认为它就是这个插入动作本身。

gap before rec 表示这是一个间隙锁，而不是记录锁。

3. 那么这个gap是在哪个记录之前的呢？接下来的0~4这5行的内容就是这个记录的信息。

4. n_fields 5也表示了，这一个记录有5列：

0: len 4; hex 0000000f; asc ;;第一列是主键id字段，十六进制f就是id=15。

所以，这时我们就知道了，这个间隙就是id=15之前的，因为id=10已经不存在了，它表示的就是(5,15)。

1: len 6; hex 000000000513; asc ;;第二列是长度为6字节的事务id，表示最后修改这一行的是trx id为1299的事务。

2: len 7; hex b0000001250134; asc % 4;; 第三列长度为7字节的回滚段信息。

可以看到，这里的acs后面有显示内容(%和4)，这是因为刚好这个字节是可打印字符。

后面两列是c和d的值，都是15。

因此，我们就知道了，由于delete操作把id=10这一行删掉了，原来的两个间隙(5,10)、(10,15）变成了一个(5,15)。

说到这里，你可以联合起来再思考一下这两个现象之间的关联：

1. session A执行完select语句后，什么都没做，但它加锁的范围突然“变大”了；

2. 第21篇文章的课后思考题，当我们执行select * from t where c>=15 and c<=20 order by cdesc lock in share mode; 向左扫描到c=10的时候，要把(5, 10]锁起来。

也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。

update的例子看过了insert和delete的加锁例子，我们再来看一个update语句的案例。

在留言区中@信信 同学做了这个试验：

图 6 update 的例子你可以自己分析一下，session A的加锁范围是索引c上的 (5,10]、(10,15]、(15,20]、(20,25]和(25,supremum]。

之后session B的第一个update语句，要把c=5改成c=1，你可以理解为两步：

1. 插入(c=1, id=5)这个记录；

2. 删除(c=5, id=5)这个记录。

按照我们上一节说的，索引c上(5,10)间隙是由这个间隙右边的记录，也就是c=10定义的。

所以通过这个操作，session A的加锁范围变成了图7所示的样子：

注意：根据c>5查到的第一个记录是c=10，因此不会加(0,5]这个next-key lock。

图 7 session B修改后， session A的加锁范围好，接下来session B要执行 update t set c = 5 where c = 1这个语句了，一样地可以拆成两步：

1. 插入(c=5, id=5)这个记录；

2. 删除(c=1, id=5)这个记录。

第一步试图在已经加了间隙锁的(1,10)中插入数据，所以就被堵住了。

小结今天这篇文章，我用前面第20和第21篇文章评论区的几个问题，再次跟你复习了加锁规则。

并且，我和你重点说明了，分析加锁范围时，一定要配合语句执行逻辑来进行。

在我看来，每个想认真了解MySQL原理的同学，应该都要能够做到：通过explain的结果，就能够脑补出一个SQL语句的执行流程。

达到这样的程度，才算是对索引组织表、索引、锁的概念有了比较清晰的认识。

你同样也可以用这个方法，来验证自己对这些知识点的掌握程度。

在分析这些加锁规则的过程中，我也顺便跟你介绍了怎么看show engine innodb status输出结果中的事务信息和死锁信息，希望这些内容对你以后分析现场能有所帮助。

老规矩，即便是答疑文章，我也还是要留一个课后问题给你的。

上面我们提到一个很重要的点：所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。

那么，一个空表有间隙吗？这个间隙是由谁定义的？你怎么验证这个结论呢？你可以把你关于分析和验证方法写在留言区，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上一篇文章最后留给的问题，是分享一下你关于业务监控的处理经验。

在这篇文章的评论区，很多同学都分享了不错的经验。

这里，我就选择几个比较典型的留言，和你分享吧：

@老杨同志 回答得很详细。

他的主要思路就是关于服务状态和服务质量的监控。

其中，服务状态的监控，一般都可以用外部系统来实现；而服务的质量的监控，就要通过接口的响应时间来统计。

@Ryoma 同学，提到服务中使用了healthCheck来检测，其实跟我们文中提到的select 1的模式类似。

@强哥 同学，按照监控的对象，将监控分成了基础监控、服务监控和业务监控，并分享了每种监控需要关注的对象。

这些都是很好的经验，你也可以根据具体的业务场景借鉴适合自己的方案。

令狐少侠  2有个问题想确认下，在死锁日志里，lock_mode X waiting是间隙锁+行锁，lock_mode X locks rec but not gap这种加but not gap才是行锁？老师你后面能说下group by的原理吗，我看目录里面没有2019-01-22 作者回复对， 好问题lock_mode X waiting表示next-key lock；

lock_mode X locks rec but not gap是只有行锁；

还有一种 “locks gap before rec”，就是只有间隙锁；

2019-01-23Ryoma  2删除数据，导致锁扩大的描述：“因此，我们就知道了，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。

”我觉得这个提到的(5, 10) 和 (10, 15)两个间隙会让人有点误解，实际上在删除之前间隙锁只有一个(10, 15)，删除了数据之后，导致间隙锁左侧扩张成了5，间隙锁成为了(5, 15)。

2019-01-22 作者回复嗯 所以我这里特别小心地没有写“锁“这个字。

间隙 (5,10)、(10,15）是客观存在的。

你提得也很对，“锁”是执行过程中才加的，是一个动态的概念。

这个问题也能够让大家更了解我们标题的意思，置顶了哈 2019-01-22  1老师好：

select * from t where c>=15 and c<=20 order by c desc for update;为什么这种c=20就是用来查数据的就不是向右遍历select * from t where c>=15 and c<=20 这种就是向右遍历怎么去判断合适是查找数据，何时又是遍历呢，是因为第一个有order by desc，然后反向向左遍历了吗？所以只需要[20,25)来判断已经是最后一个20就可以了是吧2019-01-22 作者回复索引搜索就是 “找到第一个值，然后向左或向右遍历”，order by desc 就是要用最大的值来找第一个；

精选留言order by就是要用做小的值来找第一个；

“所以只需要[20,25)来判断已经是最后一个20就可以了是吧”，你描述的意思是对的，但是在MySQL里面不建议写这样的前闭后开区间哈，容易造成误解。

可以描述为：

“取第一个id=20后，向右遍历(25,25)这个间隙”^_^2019-01-22老杨同志  1先说结论：空表锁 (-supernum，supernum],老师提到过mysql的正无穷是supernum，在没有数据的情况下，next-key lock 应该是supernum前面的间隙加 supernum的行锁。

但是前开后闭的区间，前面的值是什么我也不知道，就写了一个-supernum。

稍微验证一下session 1）begin;select * from t where id>9 for update;session 2）begin;insert into t values(0,0,0),(5,5,5);（block）2019-01-21 作者回复赞show engine innodb status 有惊喜2019-01-21Long  0感觉这篇文章以及前面加锁的文章，提升了自己的认知。

还有，谢谢老师讲解了日志的对应细节……还愿了2019-01-28 作者回复 2019-01-28滔滔  0老师，有个疑问，select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到 c=10 的时候，为什么要把 (5, 10] 锁起来？不锁也不会出现幻读或者逻辑上的不一致吧2019-01-23 作者回复会加锁，insert into t values (6,6,6) 被堵住了2019-01-23尘封  0尘封  0老师，咨询个问题，本来想在后面分区表的文章问，发现大纲里没有分区表这一讲。

1，timestamp类型为什么不支持分区？2，前面的文章讲过分区不要太多，这个多了会怎么样？比如一个表一千多个分区谢谢2019-01-23 作者回复会讲的哈新春快乐2019-02-04长杰  0老师，还是select * from t where c>=15 and c<=20 order by c desc in share mode与select * from t where id>10 and id<=15 for update的问题，为何select * from t where id>10 and id<=15 for update不能解释为：根据id=15来查数据，加锁(15, 20]的时候，可以使用优化2，这个等值查询是根据什么规则来定的？ 如果select * from t where id>10 and id<=15 for update加上order by id desc是否可以按照id=15等值查询，利用优化2？多谢指教。

2019-01-22 作者回复1. 代码实现上，传入的就是id>10里面的这个102. 可以的，不过因为id是主键，而且id=15这一行存在，我觉得用优化1解释更好哦2019-01-23堕落天使  0老师，您好：

我执行“explain select id from t where c in(5,20,10) lock in share mode;” 时，显示的rows对应的值是4。

为什么啊？我的mysql版本是：5.7.23-0ubuntu0.16.04.1，具体sql语句如下：

mysql> select * from t;+—-+——+——+| id | c | d |+—-+——+——+| 0 | 0 | 0 || 5 | 5 | 5 || 10 | 10 | 10 || 15 | 15 | 15 || 20 | 20 | 20 || 25 | 25 | 25 || 30 | 10 | 30 |+—-+——+——+7 rows in set (0.00 sec)mysql> explain select id from t where c in(5,20,10) lock in share mode;+—-+————-+——-+————+——-+—————+——+———+——+——+———-+————————–+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+——-+————+——-+—————+——+———+——+——+———-+————————–+| 1 | SIMPLE | t | NULL | range | c | c | 5 | NULL | 4 | 100.00 | Using where; Using index |+—-+————-+——-+————+——-+—————+——+———+——+——+———-+————————–+1 row in set, 1 warning (0.00 sec)2019-01-22 作者回复你这个例子里面有两行c=102019-01-23Ivan  0Jan 17 23:52:27 prod-mysql-01 kernel: [ pid ] uid tgid total_vm rss cpu oom_adj oom_score_adjnameJan 17 23:52:27 prod-mysql-01 kernel: [125254] 0 125254 27087 5 0 0 0 mysqld_safeJan 17 23:52:27 prod-mysql-01 kernel: [126004] 498 126004 24974389 22439356 5 0 0 mysqldJan 17 23:52:27 prod-mysql-01 kernel: [ 5733] 0 5733 7606586 6077037 7 0 0 mysql—————————系统日志——————————————————————————–老师你好，请教一个问题 ，我在mysql服务器上本地登录，执行了一个SQL（select b.id,b.status from rb_bak b where id not in (select id from rb );该语句问了找不同数据， rb和 rb_bak 数据量均为500万左右），SQL很慢，30分钟也没结果；

在SQL语句执行期间，发生了OOM，mysql服务被kill。

查看系统日志发现 mysqld 占用内存基本没有变，但是本机连接mysql的客户端进程（5733）却占用了内存近20G，这很让人费解，SQL没有执行完，客户端怎么会占用这么多内存？用其他SQL查询查询不同数据，也就十几条数据，更不可能占用这么多内存呀。

还请老师帮忙分析一下，谢谢。

2019-01-22 作者回复好问题，第33篇会说到哈你可以在mysql客户端参数增加 –quick 再试试2019-01-23PengfeiWang  0老师，您好：

对文中以下语句感到有困惑：

我们说加锁单位是 next-key lock，都是前开后闭区，但是这里用到了优化 2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以 next-key lock 退化为了间隙锁 (10, 15)。

SQL语句中条件中使用的是id字段（唯一索引），那么根据加锁规则这里不应该用的是优化 2，而是优化 1，因为优化1中明确指出给唯一索引加锁，从而优化 2的字面意思来理解，它适用于普通索引。

不知道是不是我理解的不到位？2019-01-22 作者回复主要是这里这一行不存在。

。

如果能够明确找到一行锁住的话，使用优化1就更准确些2019-01-23Justin  0想咨询一下 普通索引 如果索引中包括的元素都相同 在索引中顺序是怎么排解的呢 是按主键排列的吗 比如(name ,age ) 索引 name age都一样 那索引中会按照主键排序吗？2019-01-22 作者回复会的2019-01-23ServerCoder  0林老师我有个问题想请教一下，描述如下，望给予指点，先谢谢了！环境：虚拟机，CPU 4核，内存8G，系统CentOS7.4，MySQL版本5.6.40数据库配置：

bulk_insert_buffer_size = 256Msql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESsecure_file_priv=’’default-storage-engine=MYISAM测试场景修改过的参数(以下这些参数得调整对加载效率没有实质的提升)：

myisam_repair_threads=3myisam_sort_buffer_size=256Mnet_buffer_length=1Mmyisam_use_mmap=ONkey_buffer_size=256M测试场景：测试程序多线程，通过客户端API，执行load data infile语句加载数据文件三个线程，三个文件(每个文件100万条数据、150MB)，三张表(表结构相同，字段类型均为整形，没有定义主键，有一个字段加了非唯一索引)，一一对应进行数据加载，数据库没有使用多核，而是把一个核心的利用率均分给了三个线程。

单个线程加载一个文件大约耗时3秒单线程加载三个文件到三张表大约耗时9秒三个线程分别加载三个文件到三张表，则每个线程均耗时大约9秒。

从这个效果看，单线程顺序加载和三线程并发加载耗时相同，没有提升效果。

三线程加载过程中查看processlist发现时间主要耗费在了网络读取上。

问题：为啥这种场景下MySQL不利用多核？这种并行加载的情况要如何才能让其利用多核，提升加载速度2019-01-22 作者回复可以用到多核呀，你是怎么得到 “时间主要耗费在了网络读取上。

”这个结论的？另外，把这三个文件先拷贝到数据库本地，然后本地执行load看看什么效果？2019-01-23慕塔  0是这样的 假设只有一主一从 1)是集群只有一个sysbench实例，产生的数据流通过中间件，主机分全部写，和30%的读，另外70%的读全部分给从机。

2)有两个sysbench，一个读写加压到主机，另一个只有加压到从机。

主从复制之间通过binlog。

问题在1)的QPS累加与2)QPS累加 意义一样吗 1)的一条事务有读写，而2)的情况，主机与1)一样，从机的读事务与主机里的读不一样吧2019-01-22 作者回复我觉得这两个对比不太公平^_^1）的测试可能会出现中间件瓶颈，a)网络环节中间增加了一跳；

b) 如果是小查询，可能proxy先打到瓶颈2)的测试结论一般会比1）好些但是有这个架构，你肯定是从中间件访问数据库的，所以应该以1的测试结果为准2019-01-23Jason_鹏  0最后一个update的例子，为没有加（0，5）的间隙呢？我理解应该是先拿c＝5去b+树搜索，按照间隙索最右原则，应该会加（0，5]的间隙，然后c＝5不满足大于5条件，根据优化2原则退化成（0，5）的间隙索，我是这样理解的2019-01-22 作者回复根据c>5查到的第一个记录是c=10，因此不会加(0,5]这个next-key lock。

你提醒得对，我应该多说明这句， 我加到文稿中啦2019-01-22长杰  0老师，之前讲这个例子时，select * from t where c>=15 and c<=20 order by c desc in share mode;最右边加的是 (20, 25)的间隙锁，而这个例子select * from t where id>10 and id<=15 for update中，最右边加的是(15,20]的next-key锁，这两个查询为何最后边一个加的gap锁，一个加的next-key锁，他们都是<=的等值范围查询，区别在哪里？2019-01-22 作者回复select * from t where c>=15 and c<=20 order by c desc in share mode;这个语句是根据 c=20 来查数据的，所以加锁(20,25]的时候，可以使用优化2；

select * from t where id>10 and id<=15 for update；

这里的id=20，是用“向右遍历”的方式得到的，没有优化，按照“以next-key lock”为加锁单位来执行2019-01-22库淘淘  0对于问题 我理解是这样 session 1：

delete from t;begin; select * from t for update;session 2:insert into t values(1,1,1);发生等待show engine innodb status\G; …..——- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 75 page no 3 n bits 72 index PRIMARY of table test.t trx id 752090 lock_mode X insert intention waitingRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;其中申请插入意向锁与间隙锁 冲突，supremum这个能否理解为 间隙右边的那个记录2019-01-21 作者回复发现了2019-01-22慕塔  0大佬 请教下一主多从集群性能测试性能计算问题 如果使用基准测试工具sysbench。

数据流有两种1)sysbench—mycat—mysql主机(读写) TPS QPS1| |binlogmysql从机(只读)QPS2那性能指标 TPS QPS=QPS1+QPS22)sysbench—mysql主机(读写) TPS QPS1| binlogsysbench—mysql从机(只读)TPS QPS2集群性能指标TPS QPS=QPS1+QPS2这两种哪种严谨些啊？mycat的损失忽略。

生产中的集群性能怎么算的呢？？？(还是学生 谢谢！)2019-01-21 作者回复TPS就看主库的写入QPS就看所有从库的读能力加和不过没看懂你问题中1）和2）的区别2019-01-22HuaMax  0删除导致锁范围扩大那个例子，id>10 and id<=15，锁范围为什么没有10呢？不是应该（5，10]吗？2019-01-21 作者回复不是的，要找id>10的，并没有命中id=10哦，你可以理解成就是查到了(10,15)这个间隙2019-01-21llx  0回复@往事随风，顺其自然前面有解释为什么，这篇文章有更详细的解释。

Gap lock 由右值指定的，由于 c 不是唯一键，需要到10，遍历到10的时候，就把 5-10 锁了2019-01-21 作者回复2019-01-21```

问题解析

如何判断一个数据库是不是出问题了？我在第25和27篇文章中，和你介绍了主备切换流程。

通过这些内容的讲解，你应该已经很清楚了：在一主一备的双M架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。

主备切换有两种场景，一种是主动切换，一种是被动切换。

而其中被动切换，往往是因为主库出问题了，由HA系统发起的。

这也就引出了我们今天要讨论的问题：怎么判断一个主库出问题了？你一定会说，这很简单啊，连上MySQL，执行个select 1就好了。

但是select 1成功返回了，就表示主库没问题吗？select 1判断实际上，select 1成功返回，只能说明这个库的进程还在，并不能说明主库没问题。

现在，我们来看一下这个场景。

图1 查询blocked我们设置innodb_thread_concurrency参数的目的是，控制InnoDB的并发线程上限。

也就是说，一旦并发线程数达到这个值，InnoDB在接收到新请求的时候，就会进入等待状态，直到有线程退出。

这里，我把innodb_thread_concurrency设置成3，表示InnoDB只允许3个线程并行执行。

而在我们的例子中，前三个session 中的sleep(100)，使得这三个语句都处于“执行”状态，以此来模拟大查询。

你看到了， session D里面，select 1是能执行成功的，但是查询表t的语句会被堵住。

也就是说，如果这时候我们用select 1来检测实例是否正常的话，是检测不出问题的。

在InnoDB中，innodb_thread_concurrency这个参数的默认值是0，表示不限制并发线程数量。

但是，不限制并发线程数肯定是不行的。

因为，一个机器的CPU核数有限，线程全冲进来，上下文切换的成本就会太高。

所以，通常情况下，我们建议把innodb_thread_concurrency设置为64~128之间的值。

这时，你一定会有疑问，并发线程上限数设置为128够干啥，线上的并发连接数动不动就上千了。

产生这个疑问的原因，是搞混了并发连接和并发查询。

set global innodb_thread_concurrency=3;CREATE TABLE t̀ ̀( ìd ̀int(11) NOT NULL, `c ̀int(11) DEFAULT NULL, PRIMARY KEY (̀ id )̀) ENGINE=InnoDB; insert into t values(1,1)并发连接和并发查询，并不是同一个概念。

你在show processlist的结果里，看到的几千个连接，指的就是并发连接。

而“当前正在执行”的语句，才是我们所说的并发查询。

并发连接数达到几千个影响并不大，就是多占一些内存而已。

我们应该关注的是并发查询，因为并发查询太高才是CPU杀手。

这也是为什么我们需要设置innodb_thread_concurrency参数的原因。

然后，你可能还会想起我们在第7篇文章中讲到的热点更新和死锁检测的时候，如果把innodb_thread_concurrency设置为128的话，那么出现同一行热点更新的问题时，是不是很快就把128消耗完了，这样整个系统是不是就挂了呢？实际上，在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在128里面的。

MySQL这样设计是非常有意义的。

因为，进入锁等待的线程已经不吃CPU了；更重要的是，必须这么设计，才能避免整个系统锁死。

为什么呢？假设处于锁等待的线程也占并发线程的计数，你可以设想一下这个场景：

1. 线程1执行begin; update t set c=c+1 where id=1, 启动了事务trx1， 然后保持这个状态。

这时候，线程处于空闲状态，不算在并发线程里面。

2. 线程2到线程129都执行 update t set c=c+1 where id=1; 由于等行锁，进入等待状态。

这样就有128个线程处于等待状态；

3. 如果处于锁等待状态的线程计数不减一，InnoDB就会认为线程数用满了，会阻止其他语句进入引擎执行，这样线程1不能提交事务。

而另外的128个线程又处于锁等待状态，整个系统就堵住了。

下图2显示的就是这个状态。

图2 系统锁死状态（假设等行锁的语句占用并发计数）这时候InnoDB不能响应任何请求，整个系统被锁死。

而且，由于所有线程都处于等待状态，此时占用的CPU却是0，而这明显不合理。

所以，我们说InnoDB在设计时，遇到进程进入锁等待的情况时，将并发线程的计数减1的设计，是合理而且是必要的。

虽然说等锁的线程不算在并发线程计数里，但如果它在真正地执行查询，就比如我们上面例子中前三个事务中的select sleep(100) from t，还是要算进并发线程的计数的。

在这个例子中，同时在执行的语句超过了设置的innodb_thread_concurrency的值，这时候系统其实已经不行了，但是通过select 1来检测系统，会认为系统还是正常的。

因此，我们使用select 1的判断逻辑要修改一下。

查表判断为了能够检测InnoDB并发线程数过多导致的系统不可用情况，我们需要找一个访问InnoDB的场景。

一般的做法是，在系统库（mysql库）里创建一个表，比如命名为health_check，里面只放一行数据，然后定期执行：

mysql> select * from mysql.health_check; 使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况。

但是，我们马上还会碰到下一个问题，即：空间满了以后，这种方法又会变得不好使。

我们知道，更新事务要写binlog，而一旦binlog所在磁盘的空间占用率达到100%，那么所有的更新语句和事务提交的commit语句就都会被堵住。

但是，系统这时候还是可以正常读数据的。

因此，我们还是把这条监控语句再改进一下。

接下来，我们就看看把查询语句改成更新语句后的效果。

更新判断既然要更新，就要放个有意义的字段，常见做法是放一个timestamp字段，用来表示最后一次执行检测的时间。

这条更新语句类似于：

节点可用性的检测都应该包含主库和备库。

如果用更新来检测主库的话，那么备库也要进行更新检测。

但，备库的检测也是要写binlog的。

由于我们一般会把数据库A和B的主备关系设计为双M结构，所以在备库B上执行的检测命令，也要发回给主库A。

但是，如果主库A和备库B都用相同的更新命令，就可能出现行冲突，也就是可能会导致主备同步停止。

所以，现在看来mysql.health_check 这个表就不能只有一行数据了。

为了让主备之间的更新不产生冲突，我们可以在mysql.health_check表上存入多行数据，并用A、B的server_id做主键。

由于MySQL规定了主库和备库的server_id必须不同（否则创建主备关系的时候就会报错），这mysql> update mysql.health_check set t_modified=now();mysql> CREATE TABLE `health_check ̀( ìd ̀int(11) NOT NULL, t̀_modified ̀timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (̀ id )̀) ENGINE=InnoDB;/* 检测命令 */insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();样就可以保证主、备库各自的检测命令不会发生冲突。

更新判断是一个相对比较常用的方案了，不过依然存在一些问题。

其中，“判定慢”一直是让DBA头疼的问题。

你一定会疑惑，更新语句，如果失败或者超时，就可以发起主备切换了，为什么还会有判定慢的问题呢？其实，这里涉及到的是服务器IO资源分配的问题。

首先，所有的检测逻辑都需要一个超时时间N。

执行一条update语句，超过N秒后还不返回，就认为系统不可用。

你可以设想一个日志盘的IO利用率已经是100%的场景。

这时候，整个系统响应非常慢，已经需要做主备切换了。

但是你要知道，IO利用率100%表示系统的IO是在工作的，每个请求都有机会获得IO资源，执行自己的任务。

而我们的检测使用的update命令，需要的资源很少，所以可能在拿到IO资源的时候就可以提交成功，并且在超时时间N秒未到达之前就返回给了检测系统。

检测系统一看，update命令没有超时，于是就得到了“系统正常”的结论。

也就是说，这时候在业务系统上正常的SQL语句已经执行得很慢了，但是DBA上去一看，HA系统还在正常工作，并且认为主库现在处于可用状态。

之所以会出现这个现象，根本原因是我们上面说的所有方法，都是基于外部检测的。

外部检测天然有一个问题，就是随机性。

因为，外部检测都需要定时轮询，所以系统可能已经出问题了，但是却需要等到下一个检测发起执行语句的时候，我们才有可能发现问题。

而且，如果你的运气不够好的话，可能第一次轮询还不能发现，这就会导致切换慢的问题。

所以，接下来我要再和你介绍一种在MySQL内部发现数据库问题的方法。

内部统计针对磁盘利用率这个问题，如果MySQL可以告诉我们，内部每一次IO请求的时间，那我们判断数据库是否出问题的方法就可靠得多了。

其实，MySQL 5.6版本以后提供的performance_schema库，就在file_summary_by_event_name表里统计了每次IO请求的时间。

file_summary_by_event_name表里有很多行数据，我们先来看看event_name=’wait/io/file/innodb/innodb_log_file’这一行。

图3 performance_schema.file_summary_by_event_name的一行图中这一行表示统计的是redo log的写入时间，第一列EVENT_NAME 表示统计的类型。

接下来的三组数据，显示的是redo log操作的时间统计。

第一组五列，是所有IO类型的统计。

其中，COUNT_STAR是所有IO的总次数，接下来四列是具体的统计项， 单位是皮秒；前缀SUM、MIN、AVG、MAX，顾名思义指的就是总和、最小值、平均值和最大值。

第二组六列，是读操作的统计。

最后一列SUM_NUMBER_OF_BYTES_READ统计的是，总共从redo log里读了多少个字节。

第三组六列，统计的是写操作。

最后的第四组数据，是对其他类型数据的统计。

在redo log里，你可以认为它们就是对fsync的统计。

在performance_schema库的file_summary_by_event_name表里，binlog对应的是event_name =”wait/io/file/sql/binlog”这一行。

各个字段的统计逻辑，与redo log的各个字段完全相同。

这里，我就不再赘述了。

因为我们每一次操作数据库，performance_schema都需要额外地统计这些信息，所以我们打开这个统计功能是有性能损耗的。

我的测试结果是，如果打开所有的performance_schema项，性能大概会下降10%左右。

所以，我建议你只打开自己需要的项进行统计。

你可以通过下面的方法打开或者关闭某个具体项的统计。

如果要打开redo log的时间监控，你可以执行这个语句：

假设，现在你已经开启了redo log和binlog这两个统计信息，那要怎么把这个信息用在实例状态诊断上呢？很简单，你可以通过MAX_TIMER的值来判断数据库是否出问题了。

比如，你可以设定阈值，单次IO请求时间超过200毫秒属于异常，然后使用类似下面这条语句作为检测逻辑。

发现异常后，取到你需要的信息，再通过下面这条语句：

把之前的统计信息清空。

这样如果后面的监控中，再次出现这个异常，就可以加入监控累积值了。

小结今天，我和你介绍了检测一个MySQL实例健康状态的几种方法，以及各种方法存在的问题和演进的逻辑。

你看完后可能会觉得，select 1这样的方法是不是已经被淘汰了呢，但实际上使用非常广泛的MHA（Master High Availability），默认使用的就是这个方法。

MHA中的另一个可选方法是只做连接，就是 “如果连接成功就认为主库没问题”。

不过据我所知，选择这个方法的很少。

其实，每个改进的方案，都会增加额外损耗，并不能用“对错”做直接判断，需要你根据业务实际情况去做权衡。

我个人比较倾向的方案，是优先考虑update系统表，然后再配合增加检测performance_schema的信息。

最后，又到了我们的思考题时间。

今天，我想问你的是：业务系统一般也有高可用的需求，在你开发和维护过的服务中，你是怎么判断服务有没有出问题的呢？mysql> update setup_instruments set ENABLED=’YES’, Timed=’YES’ where name like ‘%wait/io/file/innodb/innodb_log_file%’;mysql> select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in (‘wait/io/file/innodb/innodb_log_file’,’wait/io/file/sql/binlog’) and MAX_TIMER_WAIT>200*1000000000;mysql> truncate table performance_schema.file_summary_by_event_name;你可以把你用到的方法和分析写在留言区，我会在下一篇文章中选取有趣的方案一起来分享和分析。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期的问题是，如果使用GTID等位点的方案做读写分离，在对大表做DDL的时候会怎么样。

假设，这条语句在主库上要执行10分钟，提交后传到备库就要10分钟（典型的大事务）。

那么，在主库DDL之后再提交的事务的GTID，去备库查的时候，就会等10分钟才出现。

这样，这个读写分离机制在这10分钟之内都会超时，然后走主库。

这种预期内的操作，应该在业务低峰期的时候，确保主库能够支持所有业务查询，然后把读请求都切到主库，再在主库上做DDL。

等备库延迟追上以后，再把读请求切回备库。

通过这个思考题，我主要想让关注的是，大事务对等位点方案的影响。

当然了，使用gh-ost方案来解决这个问题也是不错的选择。