侯瑞哲的博客

在我看来，能够帮我们扩展一个逻辑的边界的问题，就是好问题。因为通过解决这样的问题，能够加深我们对这个逻辑的理解，或者帮我们关联到另外一个知识点，进而可以帮助我们建立起自己的知识网络。

在工作中会问好问题，是一个很重要的能力。

join的写法

在35篇文章《join语句怎么优化?》中，我在介绍join执行顺序的时候，用的都是strainght_join。有同学哉问候提出了两个问题：

1. 如果用left join的话，左边的表一定是驱动表吗？
2. 如果两个表的join包含多个条件的等值匹配，是都要写到on里面呢，还是只把一个条件写到on里面，其他条件写到where部分？

为了同时回答这两个问题，我来构造两个表a和b：

1
2
3
4

create table a(f1 int, f2 int, index(f1))engine=innodb;
create table b(f1 int, f2 int)engine=innodb;
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
insert into b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

表a和b都有两个字段f1和f2，不同的是表a的字段f1上有索引。然后，我往两个表中都插入了6条记录，其中在表a和b上同时存在的数据有4行。

同学提到的第二个问题，其实就是下面这两种写法的区别：

1
2

select * from a left join b on (a.f1=b.f1) and (a.f2=b.f2); /*Q1*/
select * from a left join b on (a.f1=b.f1) where (a.f2=b.f2); /*Q2*/

我把这两条语句分别记为Q1和Q2。

首先，需要说明的是，这两个left join语句的语义逻辑并不相同。我们先来看一下它们的执行结果。

可以看到：

1. 语句Q1返回的数据集是6行，表a中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表b的各个字段值填成NULL。
2. 语句Q2返回的是4行。从逻辑上可以这么理解，最后的两行，由于表b中没有匹配的字段，结果集里面b.f2的值是空，不满足where部分的条件判断，因此不能作为结果集的一部分。

接下来，我们看看实际执行这两条语句时，MySQL是怎么做的。

我们先一起看看语句Q1的explain结果：

可以看到，这个结果符合我们的预期：

• 驱动表是表a，被驱动表是表b；
• 由于表b的f1字段上没有索引，所以使用的是Block Nested Loop Join（简称BNL）算法。

看到BNL算法，你就应该知道这条语句执行流程其实是这样的：

1. 把表a的内容读入joiin_buffer中。因为是select *，所以字段f1和f2都被放入join_buffer了。
2. 顺序扫描表b，对于每一行数据，判断join条件（也就是(a.f1=b.f1) and (a.f1=1)）是否满足，满足条件的记录，作为结果集的一行返回。如果语句中有where子句，需要先判断where部分满足条件后，再返回。
3. 表b扫描完成后，对于没有被匹配的表a的行（在这个例子中就是(1,1)、(2,2)这两行），把剩余字段补上NULL，再放入结果集中。

对应的流程图如下：

可以看到，这条语句确实是以表a为驱动表，而且从执行效果看，也和使用straight_join是一样的。

你可能会想，语句Q2的查询结果里面少了最后两行数据，是不是就是把上面流程中的步骤3去掉呢？我们还是先看一下语句Q2的explain结果吧。

可以看到，这条语句是以表b为驱动表的。而如果一条join语句的Extra字段什么都没写的话，就表示使用的是Index Nested-Loop Join (NLJ) 算法。

因此，语句Q2使用了a的f1索引，执行流程是这样的：顺序扫描表b，每一行用b.f1到表a中去查，匹配到记录后判断a.f2=b.f2是否满足，满足条件的话就作为结果集的一部分返回。

那么，为什么语句Q1和Q2这两个查询的执行流程会差距这么大呢？其实，这是因为优化器基于Q2这个查询的语义做了优化。

为了理解这个问题，我需要再和你交代一个背景知识点：在MySQL里，NULL跟任何值执行等值判断和不等值判断的结果，都是NULL。这里包括，select NULL = NULL的结果，也是返回NULL。

因此，语句Q2里面where a.f2=b.f2就表示，查询结果里面不会包含b.f2是NULL的行，这样这个left join的语义就是"找到这两个表里面，f1、f2对应相同的行。对于表a中存在，而表b中匹配不到的行，就放弃"。

这样，这条语句虽然用的是left join，但是语义跟join是一致的。

因此，优化器就把这条语句的left join改写成了join，然后因为表a的f1上有索引，就把表b作为驱动表，，这样就可以用上NLJ算法。在执行explain之后，你再执行show warnings，就能看到这个改写的结果，如图5所示。

这个例子说明，即使我们在SQL语句中写成left join，执行过程还是有可能不是从左到右连接的。也就是说，使用left join时，左边的表不一定是驱动表。

这样看来，如果需要left join的语义，就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断，必须都写在on里面。那如果是join语句呢？

这时候，我们再看看这两条语句：

1
2

select * from a join b on(a.f1=b.f1) and (a.f2=b.f2); /*Q3*/
select * from a join b on(a.f1=b.f1) where (a.f2=b.f2); /*Q4*/

我们再使用一次看explain和show warnings的方法，看看优化器是怎么做的。

可以看到，这两条语句都被改写成：

1

select * from a join b where (a.f1=b.f1) and (a.f2=b.f2);

执行计划自然也是一模一样的。

也就是所，在这种情况下，join将判断条件是否全部放在on部分就没有区别了。

Simple Nested Loop Join的性能问题

我们知道，join语句使用不同的算法，对语句的性能影响会很大。在34篇文章《到底可不可以使用join？》的评论区中，有同学提了一个很不错的问题。

我们在文中说道，虽然BNL算法和Simple Nested Loop Join算法都是要判断M*N次（M和N分别是join的两个表的行数），但是Simple Nested Loop Join算法的每轮判断都要走全表扫描，因此性能上BNL算法执行起来会快很多。

为了便于说明，我还是先为你简单描述一下这两个算法。

BNL算法的执行逻辑是：

1. 首先，将驱动表的数据全部读入内存join_buffer中，这里join_buffer是无序数组；
2. 然后，顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回。

Simple Nested Loop join算法的执行逻辑是：顺序取出驱动表中的每一行数据，到被驱动表做全表扫描匹配，匹配成功则作为结果集的一部分返回。

同学的疑问是，Simple Nested Loop Join算法，其实也是把数据读到内存里，然后按照匹配条件进行判断，为什么性能差距会这么大呢？

解释这个问题，需要用到MySQL中索引结构和Buffer Pool的相关知识点：

1. 在对被驱动表做全表扫描的时候，如果数据没有在Buffer Pool中，就需要等待这部分数据从磁盘读入；

   从磁盘读入数据到内存中，会影响正常业务的Buffer Pool命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到Buffer Pool的头部（请参考第35篇文章中的相关内容）；

2. 即使被驱动表数据都是在内存中，每次查找”下一个记录的操作“，都是类似指针操作。而join_buffer中的是数组，遍历的成本更低。

所以说，BNL算法的性能会更好。

distinct和group by的性能

在37篇文章《什么时候会使用内部临时表？》中，同学提了一个好问题：如果只需要去重，不需要执行聚合函数，distinct和group by哪种效率高一些呢？

我来展开一下他的问题：如果表t的字段a上没有索引，那么下面这两条语句：

1
2

select a from t group by a order by null;
select distinct a from t;

的性能是不是相同的？

首先需要说明的是，这种group by的写法，并不是SQL标准的写法。标准的group by语句，是需要在select部分加一个聚合函数，比如：

1

selecrt a, count(*) from t group by a order by null;

这条语句的逻辑是：按照字段a分组，计算每组的a出现次数。在这个结果里，由于做的是聚合计算，相同的a只出现一次。

备注：这里你可以顺便复习一下第37篇文章中关于group by 的相关内容。

没有了count(*)以后，也就是不再需要执行"计算总数"的逻辑时，第一条语句的逻辑就变成是：按照字段a做分组，相同的a的值只返回一行。而这就是distinct的语义，所以不需要执行聚合函数时，distinct和group by这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程是下面这样的：

1. 创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引；
2. 遍历表t，依次取数据插入临时表中：
   • 如果发现唯一键冲突，就跳过；
   • 否则插入成功；
3. 遍历完成后，将临时表作为结果集返回给客户端。

备库自增主键问题

除了性能问题，大家对细节的追问也很到位。在第39篇文章《自增主键为什么不是连续的？》评论区，有同学问道：在binlog_format=statement时，语句A先获取id=1，然后语句B获取id=2；接着语句B提交，写binlog，然后语句A再写binlog。这时候，如果binlog重放，是不是会发生语句B的id为1，而语句A的id为2的不一致情况呢？

首先，这个问题默认了"自增id的生成顺序，和binlog的写入顺序可能是不同的"，这个理解是正确的。

其次，这个问题限定在statement的格式下，也是对的。因为row格式的binlog就没有这个问题了，Write row event里面直接写了每一行的所有字段的值。

而至于为什么不会发生不一致的情况，我们来看一下下面的这个例子。

1
2

create table t(id, it auto_increment primary key);
insert into t values(null);

可以看到，在insert语句之前，还有一句SET INSERT_ID=1。这条命令的意思是，这个线程里下一次需要用到自增值的时候，不论当前表的自增值是多少，固定用1这个值。

这个SET INSERT_ID语句是固定跟在insert语句之前的，比如同学提到的场景，主库上语句A的id是1，语句B的id是2，但是写入binlog的顺序先B后A，那么binlog就变成：

1
2
3
4

SET INSERT_ID=2;
语句B;
SET INSERT_ID=1;
语句A;

你看，在备库上语句B用到的INSERT_ID依然是2，跟主库相同。

因此，即使两个INSERT语句在主备库的执行顺序不同，自增主键字段的值也不会不一致。

小结

今天这篇答疑文章，我选了4个好问题和你分享，并做了分析。在我看来，能够提出好问题，首先表示这些同学理解了我们文章的内容，进而又做了深入思考。

答疑文章也是要有课后思考题的。

在第8篇文章的评论区，有同学提了一个问题：他查看了一下innodb_trx，发现这个事务的trx_id是一个很大的数（281479535353408），而且似乎在同一个session中启动会话得到的trx_Id是保持不变的。当执行任何加写锁的语句后，trx_id都会变成一个很小的数字(118378)。

你可以通过实验验证一下，然后分析看看，事务id的分配规则是什么，以及MySQL为什么要这么设计呢？

解答： 只读事务id是临时生成的，原本trx_id的值就是0。计算的算法是：把当前事务的trx变量的指针地址转成整数，再加上2^48。

实际上，在T1时刻，session A还没有涉及到更新，是一个只读事务。而对于只读事务，InnoDB并不会分配trx_id。也就是说：

1. 在T1时刻，trx_id的值其实就是0。而这个很大的数，只是显示用的。一会儿我会再和你说说这个数据的生成逻辑。
2. 直到session A在T3时刻执行insert语句的时候，InnoDB才真正分配了trx_id。所以，T4时刻，session B查到的这个trx_id的值就是1289。

需要注意的是，除了显而易见的修改类语句外，如果select语句后面加上for update，这个事务也不是只读事务。

在上一篇文章的评论区，有同学提出，实验的时候发现不止加1。这是因为：

1. update和delete语句除了事务本身，还涉及到标记删除旧数据，也就是要把数据放到purge队列里等待后续的物理删除，这个操作也会把max_trx_id+1，因此在一个事务中至少加2；
2. 而InnoDB的后台操作，比如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx_id值并不是按照加1递增的。

那么，T2时刻查到的这个很大的数字是怎么来的呢？

其实，这个数字是每次查询的时候由系统临时计算出来的。它的算法是：把当前事务的trx变量的指针地址转成整数，再加上2^48。使用这个算法，就可以保证以下两点：

1. 因为同一个只读事务在执行期间，它的指针地址是不会变的，所以不论是在innodb_trx还是在innodb_locks表里，同一个只读事务查出来的trx_id就会是一样的。
2. 如果有并行的多个只读事务，每个事务的trx变量的指针地址肯定不同。这样，不同的并发只读事务，查出来的trx_id就是不同的。

那么，为什么还要再加上2^48呢？

在显示值里面加上2^48，目的是保证只读事务显示的trx_id值比较大，正常情况下就会区别于读写事务的id。但是，trx_id跟row_id的逻辑类似，定义长度也是8个字节。因此，在理论上还是可能出现一个读写事务与一个只读事务显示的trx_id相同的情况。不过，这个概率很低，并且也没有什么实质的危害，可以不管它。

另一个问题是，只读事务不分配trx_id，有什么好处呢？

• 一个好处是，这样做可以减小事务视图里面活跃事务数组的大小。因为正在运行的只读事务，是不影响数据的可见性判断的，所以在创建事务的一致性视图时，InnoDB就只需要拷贝读写事务的trx_id；
• 另一个好处是，可以减少trx_id的申请次数。在InnoDB里，即使你只是执行一个普通的select语句，在执行过程中，也是要对应一个只读事务的。所以只读事务优化后，普通的查询语句不需要申请trx_id，就大大减少了并发事务申请trx_id时的锁冲突。

由于只读事务不分配trx_id，一个自然而然的结果就是trx_id的增加速度变慢了。

按照锁的粒度（级别、密度）分，MySQL有三种级别的锁：页级、表级、行级。

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最

高，并发度最低。

2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最

低，并发度也最高。

3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表

锁和行锁之间，并发度一般。

按禁止的操作可分为读锁和写锁，其中，互斥的关系如下

一些重要的锁：

MDL锁 metadata lock 元数据锁

用于解决或保证DDL操作与DML操作之间的一致性。

属于表级锁。

间隙锁

间隙锁加锁规则详见丁奇老师的文章

自增id锁

trx_id锁

涉及到竞争资源，很多处都有锁。

小结一下：表锁、行锁、间隙锁（next-key lock）、自增id锁，MDL metadata lock 元数据锁

MySQL什么时候会使用内部临时表？

1. 如果语句执行过程可以一边读数据，一遍直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
2. join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束，group by还需要用到另外一个字段来存累积计数。

MySQL常用操作

1. explain查看命令优化器优化结果；
2. show engine innodb status;
3. show warnings，看优化器改写后的语句；
4. show create table t; 查看t的建表语句；

MySQL中的重要概念

1. MVCC（Multi View Concurrent Control）多视图并发控制，可重复读（RR，repeatable read）隔离级别下提供一致性视图的重要概念。以及MySQL巧妙的使用了undoLog来完成这些。
2. redoLog，undoLog，binLog重要概念。
3. WAL (write ahead log 技术)，先将日志写在redolog中
4. 索引的最左前缀规则（前缀索引规则），利用最左前缀规则，节省索引数量，节省空间。
5. error log 错误日志，slow log慢查询日志（通过set long_query_time = 0，表示这个线程接下来的语句都会被记录入慢查询日志中；
6. change buffer
7. 普通索引和唯一索引尽量选择普通索引
8. 对于字符串索引，可以用unique元素的百分比，也就是区分度，确定使用多长的字符串作为索引，语句为count distinct。预先设定一个可以接受的损失比例。但是使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
9. 脏页和干净页，redo log中的脏页，刷进磁盘才会删除。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。write_pos， checkpoint

分区表是什么？

为了说明分区表的组织形式，我先创建一个表t；

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

我在表t初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在p_2018和p_2019这两个分区上。

可以看到，这个表包含了一个.frm文件和4个.ibd文件，每个分区对应一个.ibd文件。也就是说：

1. 对于引擎层来说，这是4个表；
2. 对于Server层来说，这是1个表。

你可能会觉得这两句话都是废话。其实不然，这两句话非常重要，可以帮我们理解分区变的执行逻辑。

分区表的引擎层行为

我先给你举个在分区表加间隙锁的例子，目的是说明对于InnoDB来说，这是4个表。

这里顺便复习一下，我在第21篇文章和你介绍的间隙锁加锁规则。

我们初始化表t的时候，只插入了两行数据，ftime的值分别是，’2017-4-1‘和’2018-4-1‘。session A的select语句对索引ftime上这两个记录之间的间隙加了锁。如果是一个普通表的话，那么T1时刻，在表t的ftime索引上，间隙和加锁状态应该是图3这样的。

也就是说，'2017-4-1'和’2018-4-1‘这两个记录之间的间隙是会被锁住的。那么，session B的两条插入语句应该都要进入锁等待状态。

但是，从上面的实验效果可以看出，session B的第一个insert语句是可以执行成功的。这是因为，对于引擎来说，p_2018和p_2019是两个不同的表，也就是说2017-4-1的下一个记录并不是2018-4-1，而是p_2018分区的supremum。所以T1时刻，在表t的ftime索引上，间隙和加锁的状态其实是图4这样的：

由于分区表的规则，session A的select语句其实只操作了分区p_2018，因此加锁范围就是图4中深绿色的部分。

所以，session B要写入一行ftime是2018-2-1的时候是可以成功的，而要写入2017-12-1这个记录，就要等session A的间隙锁。

图5就是这时候的show engine innodb status的部分结果。

看完InnoDB引擎的例子，我们再来一个MyISAM分区表的例子。

我首先用alter table t engine=myisam，把表t改成MyISAM表；然后，我再用下面这个例子说明，对于MyISAM引擎来说，这是4个表。

注：这是在用MyISAM的表锁验证，MyISAM引擎只支持表锁。

在session A里面，我用sleep(100)将这条语句的执行时间设置为100秒。由于MyISAM引擎只支持表锁，所以这条update语句会锁住整个表t上的读。

但我们看到的结果是，session B的第一条查询语句是可以正常执行的，第二条语句才进入锁等待状态。

这正是因为MyISAM的表锁是在引擎层实现的，session A加的表锁，其实是锁在分区p_2018上。因此，只会堵住在这个分区上执行的查询，落到其他分区的查询是不受影响的。

看到这里，你可能会说，分区表看来还不错嘛，为什么不让用呢？我们使用分区表的一个重要原因就是单表过大。那么，如果不使用分区表的话，我们就是要使用手动分表的方式。

接下来，我们一起看看手动分表和分区表有什么区别。

比如，按照年份来划分，我们就分别创建普通表t_2017、t_2018、t_2019等等。手工分表的逻辑，也是找到需要更新的所有分表，然后依次执行更新。在性能上，这和分区表并没有实质的差别。

分区表和手工分表，一个是有server层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的。

其实这两个方案的区别，主要是在server层上。从server层看，我们就不得不提到分区表一个被广为诟病的问题

：打开表的行为。

分区策略

每当第一次访问一个分区表的时候，MySQL需要把所有的分区都访问一遍。一个典型的报错情况是这样的：如果一个分区表的分区很多，比如超过了1000个，而MySQL启动的时候，open_files_limit参数使用的是默认值1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。

下图就是我创建的一个包含了很多分区的表t_myisam，执行一条插入语句后报错的情况。

可以看到，这条insert语句，明显只需要访问一个分区，但语句却无法执行。

这时，你一定从表名猜到了，这个表我用的是MyISAM引擎。是的，因为使用InnoDB引擎的话，并不会出现这个问题。

MyISAM分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由server层控制。通用分区策略，是MySQL一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。

从MySQL 5.7.9开始，InnoDB引擎引入了本地分区策略（native partitioning）。这个策略是在InnoDB内部自己管理打开分区的行为。

MySQL从5.7.17开始，将MyISAM分区表标记为即将弃用（deprecated），意思是”从这个版本开始不建议这么使用，请使用替代方案。在将来的版本中会废弃这个功能“。

从MySQL8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略。

接下来，我们再看一下分区表在server层的行为。

分区表的server层行为

如果从server层看的话，一个分区表就只是一个表。

这句话是什么意思呢？接下来，我就用下面这个例子来和你说明。如图8和如图9所示，分别是这个例子的操作序列和执行结果图。

可以看到，虽然session B只需要操作p_2017这个分区，但是由于session A持有整个表t的MDL锁，就导致了session B的alter语句被堵住。

这也是DBA同学经常说的，分区表，在做DDL的时候，影响会更大。如果你使用的是普通分表，那么当你在truncate一个分表的时候，肯定不会跟另外一个分表上的查询语句，出现MDL锁冲突。

到这里我们小结一下：

1. MySQL在第一次打开分区表的时候，需要访问所有的分区；
2. 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

而关于”必要的分区“的判断，就是根据SQL语句中的where条件，结合分区规则来实现的。比如我们上面的例子中，where ftime=’2018-4-1‘，根据分区规则year函数算出来的值是2018，那么就会落在p_2019这个分区。

但是，如果这个where条件改成where ftime>='2018-4-1'，虽然查询结果相同，但是这时候根据where条件，就要访问p_2019和p_others这两个分区。

如果查询语句的where条件中没有分区key，那就只能访问所有分区了。当然，这并不是分区表的问题。即使是使用业务分表的方式，where条件中没有使用分表的key，也必须访问所有的分表。

我们已经理解了分区表的概念，那么什么场景下适合使用分区表呢？

分区表的应用场景

分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。

如果一项业务跑的时间足够长，往往就会有根据时间删除历史数组的需求。这时候，按照时间分区的分区表，就可以直接通过alter table t drop partition...这个语法删掉分区，从而删掉过期的历史数据。

这个alter table t drop partition...操作是直接删除分区文件，效果跟drop普通表类似。与使用delete语句删除数据相比，优势是速度快、对系统影响小。

小结

这篇文章，我主要和你介绍的是server层和引擎层对分区表的处理方式。我希望通过这些介绍，你能够对是否选择使用分区表，有更清晰的想法。

需要注意的是，我是以范围分区（range）为例和你介绍的。实际上，MySQL还支持hash分区、list分区等分区方法。你可以在需要用到的时候，再翻翻手册。

实际使用时，分区表跟用户分表比起来，有两个绕不开的问题：一个是第一次访问的时候需要访问所有分区，另一个是共用MDL锁。

因此，如果要使用分区表，就不要创建太多的分区。我见过一个用户做了按天分区策略，然后预先创建了10年的分区。这种情况下，访问分区表的性能自然是不好的。这里有两个问题需要注意：

1. 分区并不是越细越好。实际上，单表或者单分区的数据到1千万行，只要没有特别大的索引，对于现在的硬件能力来说已经算是小表了。
2. 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的12个分区创建上即可。对于没有数据的历史分区，要及时的drop掉。

至于分区表的其他问题，比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量的问题，或者说是使用方式的问题了。

当然，如果你的团队已经维护了一个成熟的分库分表中间件，用业务分表，对业务开发同学来说，并没有额外的复杂性，对DBA也更直观，自然是更好的。

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

我们举例的表中，没有用到自增主键，假如现在要创建一个自增字段id。MySQL要求分区表中的主键必须包含分区字段。如果要在表t的基础上做修改，你会怎么定义这个表的主键呢？为什么这么定义呢？

解答：

上期的问题是，怎么给分区表t创建自增主键。由于MySQL要求主键包含所有的分区字段，所以肯定是要创建联合主键的。

这时候就有两种可选：一种是（ftime, id），另一种是（id, ftime）。

如果从利用率上来看，应该使用（ftime, id）这种模式。因为用ftime做分区key，说明大多数语句都是包含ftime的，使用这种模式，可以利用前缀索引的规则，减少一个索引。

这时的建表语句是：

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE `t` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ftime` datatime NOT NULL,
  `c` int(11) DEFAULT NULL,
 PRIMARY KEY (`ftime`, `id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = MyISAM,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = MyISAM,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = MyISAM,
 PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = MyISAM);

当然，我的建议是你要尽量使用InnoDB引擎。InnoDB表要求至少有一个索引，以自增字段作为第一个字段，所以需要一个id的单独索引。

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`ftime`,`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

当然把字段反过来，创建成：

1
2

PRIMARY KEY (`id`,`ftime`),
KEY `id` (`ftime`)

也是可以的。

先说结论：

正常情况下，grant命令之后，没有必要跟着执行flush privileges命令。

那么，flush privileges是什么时候使用呢？显然，当数据表中的权限数据根内存中的权限数据不一样的时候，flush privileges语句可以用来重建内存数据，达到一致状态。

这种不一致往往是由不规范的操作导致的，比如直接用DML语句操作系统权限表。

直接操作系统表是不规范的操作。这个不一致状态也会导致一些更“诡异”的现象发生。不要直接操作系统表。

小结

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用 grant 和 revoke 语句，是不需要随后加上 flush privileges 语句的。

flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

基于TCP的套接字编程：

socket层的位置：socket在传输层和应用层之间

socket是什么：

Socket是应用层与TCP/IP协议族通信的中间软件抽象层，它是一组接口。在设计模式中，Socket其实就是一个门面模式，它把复杂的TCP/IP协议族隐藏在Socket接口后面，对用户来说，一组简单的接口就是全部，让Socket去组织数据，以符合指定的协议。

所以，我们无需深入理解tcp/udp协议，socket已经为我们封装好了，我们只需要遵循socket的规定去编程，写出的程序自然就是遵循tcp/udp标准的。

socket的底层封装对文件描述符的读写。

套接字分类

1. 基于文件类型的套接字家族；套接字家族的名字：AF_UNIX
2. 基于网络类型的套接字家族；套接字家族的名字：AF_INET

套接字工作流程

先从服务端说起。服务端先初始化Socket，然后与端口绑定（bind），对端口进行监听（listen），调用accept阻塞，等待客户端连接。在这时如果有个客户端初始化一个Socket，然后连接服务器（connect），如果连接成功，这时客户端与服务端的连接就建立了。客户端发送数据请求，服务器端接受请求并处理请求，然后把回应数据发送给客户端，客户端读取数据，最后关闭连接，一次交互结束。

socket模块的应用：

服务端套接字函数

s.bind() 绑定（主机，端口号）到套接字、s.listen()开始TCP监听、s.accept()被动接受TCP客户的连接，（阻塞式）等待连接的到来。

accept成功之后，会创建一个连接connection对象，以及客户端的ip_port信息。

con.close()关闭这个连接。s.close()关闭服务器。

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

import socket

# 明确配置变量
ip_port = ('127.0.0.1', 8080)
back_log = 5
buffer_size = 1024
# 创建一个TCP套接字
ser = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
# 套接字类型AF_INET，socket.SOCK_STREAM, tcp协议，基于流式的协议
ser.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
# 对socket的配置重用ip和端口号
# 绑定端口号
ser.bind(ip_port)  # 写哪个ip就要运行在哪台机器上
# 设置半连接池
ser.listen(back_log)  # 最多可以连接多少个客户端
while True:
    # 阻塞等待，创建连接
    con, address = ser.accept()  # 在这个位置进行等待，监听端口号
    while True:
        try:
            # 接受套接字的大小，怎么发就怎么收
            msg = con.recv(buffer_size)
            if msg.decode('utf-8') == '1':
                # 断开连接
                con.close()
            print('服务器收到消息', msg.decode('utf-8'))
        except Exception as e:
            break
    con.close()
    break
# 关闭服务器
ser.close()

简化代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

import socket

ip_port = ('127.0.0.1', 8080)
backlog = 5
buffer_size = 1024
ser = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
ser.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
ser.bind(ip_port)
ser.listen(backlog)
while True:
    con, addr = ser.accept()
    while True:
        try:
            msg = con.recv(buffer_size)
            print("服务端收到的消息是", msg.decode('utf-8'))
            con.send("hello".encode('utf-8'))
            if msg.decode('utf-8') == 'q':
                break
        except:
            break
    con.close()
    break
ser.close()

socket.SOL_SOCKET设置这个让我们可以在套接字级别上设置选项。选项有一些常用取值，常用选项有：

• SO_BROADCAST广播消息的能力（只有udp支持广播，并且还必须是在支持广播消息的网络上（例如以太网，令牌环网等））、

• SO_DEBUG，仅由TCP支持。当给一个TCP套接字开启本选项时，内核将为TCP在该套接字发送和接受的所有分组保留详细跟踪信息。这些信息保存在内核的某个环形缓冲区中，并可使用trpt程序进行检查。

• SO_KEEPALIVE

  给一个TCP套接字设置保持存活选项后，如果2小时内在该套接字的任何一方向上都没有数据交换，TCP就自动给对端发送一个保持存活探测分节。这是一个对端必须相应的TCP分节，它会导致以下3种情况之一。

  （1）对端以期望的ACK响应。应用进程得不到通知（因为一切正常）。在又经过仍无动静的2小时后，TCP将发出另一个探测分节。

  （2）对端以RST响应，它告知本端TCP：对端已崩溃且已重新启动。该套接字的待处理错误被置为ECONNRESET，套接字本身则被关闭。

  （3）对端对保持存活探测分节没有任何响应。

  如果根本没有对TCP的探测分节的响应，该套接字的待处理错误就被置为ETIMEOUT，套接字本身则被关闭。然而如果该套接字收到一个ICMP错误作为某个探测分节的响应，那就返回响应的错误，套接字本身也被关闭。

  本选项的功能是检测对端主机是否崩溃或变的不可达（譬如拨号调制解调器连接掉线，电源发生故障等等）。如果对端进程崩溃，它的TCP将跨连接发送一个FIN，这可以通过调用select很容易的检测到。

  本选项一般由服务器使用，不过客户也可以使用。服务器使用本选项时因为他们花大部分时间阻塞在等待穿越TCP连接的输入上，也就是说在等待客户的请求。然而如果客户主机连接掉线，电源掉电或者系统崩溃，服务器进程将永远不会知道，并将继续等待永远不会到达的输入。我们称这种情况为半开连接。保持存活选项将检测出这些半开连接并终止他们。

• SO_LINGER

  本选项指定close函数对面向连接的协议（例如TCP和SCTP，但不是UDP）如何操作。默认操作是close立即返回，但是如果有数据残留在套接字发送缓冲区中，系统将试着把这些数据发送给对端。

• RCVBUF和SNDBUF接收和发送缓冲区

• RCVLOWAT和SNDLOWAT接收和发送低水位

• SO_RCVTIMEO 和 SO_SNDTIMEO套接字选项

• SO_REUSEADDR 和 SO_REUSEPORT 套接字选项

  SO_REUSEADDR所有的TCP服务器都应该指定本套接字选项，一个最重要的原因如下：

  • SO_REUSEADDR允许启动一个监听服务器并捆绑其众所周知的端口，即使以前建立的将该端口用作他们的本地端口的连接仍存在。这个条件通常是这样碰到的：

    ​ （a）启动一个监听服务器；

    ​ （b）连接请求到达，派生一个子进程来处理这个客户；

    ​ （c）监听服务器终止，但子进程继续为现有连接上的客户提供服务；

    ​ （d）重启监听服务器。

    默认情况下，当监听服务器在步骤d通过调用socket，bind和listen重新启动时，由于他试图捆绑一个现有连接（即正由早先派生的那个子进程处理着的连接）上的端口，从而bind调用会失败。但是如果该服务器在socket和bind两个调用之间设置了SO_REUSEADDR套接字选项，那么将成功。所有TCP服务器都应该指定本套接字选项，以允许服务器在这种情况下被重新启动。

这里关于套接字选项不做更多展开，附上链接参考，https://blog.csdn.net/u010144805/article/details/78579771

客户端：p.connect(ip地址，端口号) 连接服务器

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13

import socket

p = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
p.connect(('127.0.0.1', 8080))
while 1:
    msg = input('please input: ')
    # 防止输入空消息
    if not msg:
        continue
    p.send(msg.encode('utf-8'))  # 收发消息一定要二进制，记得编码
    if msg == '1':
        break
p.close()

简化代码如下：

1
2
3
4
5
6
7
8
9
10
11
12

import socket

p = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
p.connect(('127.0.0.1', 8080))
while True:
    msg = input('please input: ')
    p.send(msg.encode('utf-8'))
    data = p.recv(1024)
    print(data.decode('utf-8'))
    if msg == 'q':
        break
p.close()

• 用户态：专门存放应用程序。内核态：专门存放操作系统的内核。
• 在socket 里面 from socket import * 这样可以减少代码量。
• 当发送回车换行，内容为空，没有必要发所以就会卡顿。当自己这一端 的内核态没东西会卡住recv()。加一个判断是否为空解决。
• 按照socket数据--->内核态----->网卡的顺序发送 send()()都是发送socket数据 send()和recv()都是往自己的内存里面收发。

• 端口号+ip地址+mac地址 = 哪个应用程序+哪台电脑+哪个房间（一一对应） 标示互联网上唯一的一个程序

udp套接字

由于udp是无连接的所以比TCP更简洁。

服务端：recvfrom()接受的结果是发送的信息，和发送方的IP和端口号。sendto（信息，目标主机IP和端口号）

1
2
3
4
5
6
7
8
9
10
11

from socket import *

udp_ser = socket(AF_INET, SOCK_DGRAM)  # 数据报式的套接字
udp_ser.bind(('127.0.0.1', 8080))

while True:
    data = udp_ser.recvfrom(1024)
    print(data)  # (b'asd', ('127.0.0.1', 60606))
    udp_ser.sendto('data'.encode('utf-8'), data[1])

# udp_ser.close()

简化代码如下：

1
2
3
4
5
6
7
8
9
10
11

import socket

udp_ser = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
udp_ser.bind(('127.0.0.1', 8080))
while True:
    data, addr = udp_ser.recvfrom(1024)
    print(data, addr)
    udp_ser.sendto('hello'.encode('utf-8'), addr)
    if data.decode('utf-8') == 'q':
        break
udp_ser.close()

客户端：

1
2
3
4
5
6
7
8
9

from socket import *

s = socket(AF_INET, SOCK_DGRAM)
while True:
    msg = input('input-1: ')
    s.sendto(msg.encode('utf-8'), ('127.0.0.1', 8080))
    data, addr = s.recvfrom(1024)
    print(data.decode('utf-8'))
# s.close()

简化代码如下：

1
2
3
4
5
6
7
8
9
10
11

import socket

p = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
while True:
    msg = input('please input: ')
    p.sendto(msg.encode('utf-8'), ('127.0.0.1', 8080))
    data, addr = p.recvfrom(1024)
    print(data.decode('utf-8'))
    if msg == 'q':
        break
p.close()

这里和tcp的socket区别可以看出，recv方法和recvfrom方法的一个区别

• recv在自己这端缓冲区为空时，阻塞。
  
• 而recvfrom在自己这端的缓冲区为空时，就收一个空。

用udp实现两个不同客户端的交流：

服务端：

1
2
3
4
5
6
7
8
9
10
11

from socket import *

udp_ser = socket(AF_INET, SOCK_DGRAM)
udp_ser.bind(('127.0.0.1', 8080))
data_1, addr_1 = udp_ser.recvfrom(1024)
data_2, addr_2 = udp_ser.recvfrom(1024)
while True:
    data1 = udp_ser.recvfrom(1024)
    udp_ser.sendto(data1[0], addr_2)
    data2 = udp_ser.recvfrom(1024)
    udp_ser.sendto(data2[0], addr_1)

客户端1：

1
2
3
4
5
6
7
8
9

from socket import *

s = socket(AF_INET, SOCK_DGRAM)
s.sendto('hello'.encode('utf-8'), ('127.0.0.1', 8080))
while True:
    msg = input('input-1: ')
    s.sendto(msg.encode('utf-8'), ('127.0.0.1', 8080))
    data, addr = s.recvfrom(1024)
    print(data.decode('utf-8'))

客户端2：

1
2
3
4
5
6
7
8
9

from socket import *

s = socket(AF_INET, SOCK_DGRAM)
s.sendto('hello'.encode('utf-8'), ('127.0.0.1', 8080))
while True:
    data, addr = s.recvfrom(1024)
    print(data.decode('utf-8'))
    msg = input('input-2: ')
    s.sendto(msg.encode('utf-8'), ('127.0.0.1', 8080))

HTTP三次握手、四次挥手

三次握手（因为刚开始没有数据传输所以可以合并），四次挥手（因为客户端到服务端数据传完可以断开，但是服务端的数据不一定发完，所以不能一次性断开） 握手：

1. 客户端发送连接请求（syn）
2. 服务器回应确认发送（ack），第一条客户端到服务器端的连接建好，并且向客户端发送连接请求（syn）
3. 客户端回应确认发送（ack），第二条服务器端到客户端的连接建好。

挥手（谁先发完，谁就断开连接）：

1. 客户端发送请求断开连接（seq）
   
2. 服务端回应确认（ack）此时客户端到服务端的链接断开
3. 服务端发送请求断开连接（seq）
4. 客户端回应确认（ack） 此时服务端到客户端的链接断开

至此双向连接都已断开。

HTTP协议

特点：无状态的协议、基于请求/响应的模式。

POST方式有请求体，而GET方式没有请求体。在请求协议中，空行是用来和请求体分开。

部分参数：

1
2
3
4
5
6
7

Accpect：接收类型  */* 代表全部接受，q=0.8代表权重
Accpect-Encoding  ：可接受压缩格式
Accpect-Language：可接受的语言
Refer：防盗链，从哪里过来的
Connection： 3000毫秒的时间差
Host：主机地址
user-agent：请求头

堆的空间是用来存储我们new出来的对象的。当new的对象填充满堆区的话，就会导致内存爆掉，我们的程序就会OOM。jvm 的 GC是清理堆上的对象的。

首先我们应该判断这个对象应不应该被删除。那判断的标准是什么呢？有一个标准叫GCRoot。被

1. 栈
2. 本地方法栈
3. 方法区（全局变量）

直接或者间接引用的对象，是不能被删除的。

就引出了一个以GCRoot为根的树结构。

思路1：标记需要被删除，在扫描一遍，再删除。这就是标记清理。

缺点：产生内存碎片。（导致虽然有内存，但申请不了大内存）

思路2：标记整理。在清除之后，后面的对象要补上来，后面的对象往前顶，减少内存碎片。

缺点：代价太大。所有的对象都要前移。

思路3：复制算法。将整个内存一分为二。在1区标记是否删除，等到快满了。往2区进行复制，需要删除就不复制，不需要删除的就复制过来，并且是紧凑的复制。这样既避免了内存碎片问题，整个的开销也不大。

缺点：需要两倍的内存。

实际的GC。

对堆区进行了划分，一部分叫年轻代（Young区），另一部分叫老年区（Old区）。对年轻代又进行了划分，有3个区。eden区，survive0区， survive1区。Old区只有一块。new对象都会在eden区。当eden区快满的时候就会触发GC。这个GC只是Young这个区域的GC，所以这个GC又叫做YoungGC。YoungGC的过程采用的是上面的复制算法。不需要删除的依次复制到survive0区。eden比较大的原因是，对象的生命周期往往较短，所以在产生它们的地方区域会比较大，但是幸存下来的比较小。survive0、survive1、eden的比例大概是1：1：8，默认设置下。那为什么需要两块survive区呢？这两个S区是交替工作的。幸存到S0区之后，会将E和S1区全部删除。然后等下一次E区快满了之后，再将S0和E所有对象标记，然后全部复制到S1区。S0和S1交替使用，作为幸存下来的区域。E+S1复制到S0，E+S0复制到S1，E+S1复制到S0，如此往复。结合了对象的"朝生夕死"的特征进行设计的。每一次YoungGC之后，活下来的对象的年龄就会加1，直到对象满了6岁，不再往survive区中复制了，就直接到Old区中。这个原因是因为，如果一个对象在6次GC的清理中都没有被清理掉，那很有可能60次GC都不会清理掉，它可能会永远存在，或者存在很长一段时间。所以我们直接把它放到Old区中维护，这样就省得每次都在这边复制了。另外Old区除了存了年龄是大于等于6岁的这样的一些对象，同时它还存一些大的对象。大对象的原因是如果我们在Eden进行复制的时候，大对象的消耗是比较大的。大对象主要是什么呢？比如说有一个1000万大小的int数组，它就是一个大对象，这个大对象就会直接存到Old区，不进行Young区的存储。那在Old区同样存在它快满了的问题，快满了就会触发GC。OldGC一般会同时伴随着YoungGC，所以它又叫FullGC。FullGC会引起stop the world。stop the world就是说整个java程序直接暂停，然后全力地进行垃圾回收，(因为已经没有内存可用了)。垃圾回收主要采用的是标记清理的算法，或者是标记整理的算法。那我们就明确了，1和2思路（标记清理和标记整理）主要是用于FullGC的，也就是Old区的GC，复制算法主要是Young区的GC。

这里举几个比较有名的垃圾收集器。

年轻代的垃圾收集器可能是ParNew。

老年代比较有名的可能是CMS。

分别用了复制算法和标记清理算法，来进行垃圾回收。

最新版的JDK已经不建议用以前的垃圾收集器了，而采用了一种全新的G1垃圾收集器。它有一种全新的理念。这里就不展开讲了。

jvm在拿到操作系统分配的内存后，将内存分成了5个区域。

按线程私有和线程共有分为：

线程私有：

1. 栈（函数运行过程中的临时变量，存储基本类型，对象类型的指针）
2. 本地方法栈（C++native方法运行的栈区，C++基本类型，与C++对象类型的指针）
3. 程序计数器（指向程序当前运行的位置，字节码序列的位置）

共有（属于同一进程）：

1. 方法区（元空间）（全局静态方法、变量、类加载器，等等一些全局信息）
2. 堆（存储实际对象）

栈的空间出栈即可清空，但是堆上创建的对象是不能够随着函数的运行完毕去清理的，因为也不知道是否有其他线程也引用了当前对象，也使用了当前地址，所以它无法进行清空，因而用到了GC机制。

堆中注意，对于对象类型，堆中存储的依然是对象类型的指针，指向在堆中对象的实际位置，直到基本类型，存储的是实际值，形象地说是一连串的对象。

除了栈和堆，还需要在意方法区。方法区什么时候起作用呢，比如说public static void main这个main函数就是存在方法区的。或者比如static Integer i = 10;这个i就是存在方法区。因为方法区也是一个全局的，所以在不同函数的栈中也可以同时的调用方法区里面的i。Main.i去调用静态变量。

我在上一篇文章最后，给你留下的问题是怎么在两张表中拷贝数据。如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用insert..select语句即可实现。

当然，为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。这时，有两种常用的方法。接下来的内容，我会和你详细展开一下这两种方法。

为了便于说明，我还是先创建一个表db1.t，并插入1000行数据，同时创建一个相同结构的db2.t。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

create database db1;
use db1;

create table t(id int primary key, a int, b int, index(a)) engine=innodb;
delimiter;;
 create procedure idata()
    begin
        declare i int;
     set i=1;
     while (i <= 1000)do
            insert into t values(i,i,i);
           set i = i + 1;
       end while;
  end;;
delimiter;
call idata();

create database db2;
create table db2.t like db1.t; 

假设，我们要把db1.t里面a>900的数据行导出来，插入到db2.t中。

mysqldump方法

一种方法是，使用mysqldump命令将数据导出成一组INSERT语句。你可以使用下面的命令：

1

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

把结果输出到临时文件。

这条命令中，主要参数含义如下：

1. --single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
2. --add-locks设置为0，表示在输出文件结果里，不增加"LOCK TABLES t WRITE;";
3. --no-create-info的意思是，不需要导出表结构。
4. --set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
5. --result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的；

通过这条mysqldump命令生成的t.sql文件中就包含了如图1所示的INSERT语句。

可以看到，一条INSERT语句里面会包含多个value对，这是为了后续用这个文件来写入数据的时候，执行速度可以更快。

如果你希望生成的文件中一条INSERT语句只插入一行数据的话，可以在执行mysqldump命令时，加上参数--skip-extended-insert。

然后，你可以通过下面这条命令，将这些INSERT语句放到db2库里去执行。

1

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

需要说明的是，source并不是一条SQL语句，而是一个客户端命令。mysql客户端执行这个命令的流程是这样的：

1. 打开文件，默认以分号为结尾读取一条条的SQL语句；
2. 将SQL语句发送到服务端执行。

也就是说，服务端执行并不是这个"source t.sql"语句，而是INSERT语句。所以，不论是在慢查询日志（slow log），还是在binlog，记录的都是这些要被真正执行的INSERT语句。

导出CSV文件

另一种方法是直接将结果导出成.csv文件。MySQL提供了下面的语法，用来将查询结果导出到服务端本地目录：

1

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

我们在使用这条语句时，需要注意如下几点。

1. 这条语句会将结果保存在服务端。如果你执行命令的客户端和MySQL服务端不在同一个机器上，客户端机器的临时目录下是不会生辰恶搞t.csv文件的。
2. into outfile指定了文件的生成位置（/server_tmp/)，这个位置必须受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用分别是：
   • 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；
   • 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
   • 如果设置为NULL，就表示禁止在MySQL实例上执行select...into outfile操作。
3. 这条命不会帮你覆盖文件，因此你需要确保/server_tmp/t.csv这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。
4. 这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上'\'这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开。

得到.csv导出文件后，你就可以用下面的load data命令将数据导入到目标表db2.t中。

1

load data infile '/server_tmp/t.csv' into table db2.t;

这条语句的执行流程如下所示。

1. 打开文件/server_tmp/t.csv，以制表符(\t)作为字段间的分隔符，以换行符(\n)作为记录之间的分隔符，进行数据读取；
2. 启动事务。
3. 判断每一行的字段数与表db2.t是否相同；
   • 若不相同，则直接报错，事务回滚；
   • 若相同，则构造成一行，调用InnoDB引擎接口，写入到表中。
4. 重复步骤3，直到/server_tmp/t.csv整个文件读入完成，提交事务。

你可能会有一个疑问，如果binlog_format=statement，这个load语句记录到binlog里以后，怎么在备库重放呢？

由于/server_tmp/t.csv文件只保存在主库所在的主机上，如果只是把这条语句原文写到binlog中，在备库执行的时候，备库的本地机器上没有这个文件，就会导致主备同步停止。

所以，这条语句执行的完整流程，其实是下面这样的。

1. 主库执行完成后，将/server_tmp/t.csv文件的内容直接写到binlog文件中。
2. 往binlog文件中写入语句load data local infile '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `db2`.`t`。
3. 把这个binlog日志传到备库。
4. 备库的apply线程在执行这个事务日志时：
   1. 先将binlog中的t.csv文件的内容读出来，写入到本地临时目录/tmp/SQL_LOAD_MB-1-0中；
   2. 再执行load data语句，往备库的db2.t表中插入跟主库相同的数据。

执行流程如下图所示：

注意，这里备库执行的load data语句里面，多了一个”local“。它的意思是”将执行这条命令的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0的内容，加载到目标表db2.t中“。

也就是说，load data 命令有两种用法：

1. 不加"local"，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下；
2. 加上"local"，读取的是客户端的文件，只要mysql客户端有访问这个文件的权限即可。这时候，MySQL客户端会先把本地文件传给服务端，然后执行上述的load data流程。

另外需要注意的是，select...into outfile 方法不会生成表结构文件，所以我们导数据时还需要单独的命令得到表结构定义。mysqldump提供了一个--tab参数，可以同时导出表结构定义文件和csv文件。这条命令的使用方法如下：

1

mysqldump -h$host -P$port -u$user ---single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

这条命令会在$secure_file_priv定义的目录下，创建一个t.sql文件保存建表语句，同时创建一个t.txt保存CSV数据。

物理拷贝方法

前面我们提到的mysqldump方法和导出CSV文件的方法，都是逻辑导数据的方法，也就是将数据从表db1.t中读出来，生成文本，然后再写入目标表db2.t中。

你可能回文，有物理导数据的方法吗？比如，直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下，是否可行呢？

答案是不行的。

因为，一个InnoDB表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有db2.t这个表，系统是不会识别和接收它们的。

不过，在MySQL5.6版本引入了可传输表空间（transportable tablespace）的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。

假设我们现在的目标是在db1库下，复制一个跟表t相同的表r，具体的执行步骤如下：

1. 执行 create table r like t，创建一个相同表结构的空表；
2. 执行alter table r discard tablespace，这时候r.ibd文件会被删除；
3. 执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
4. 在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd; 这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL进程要有读写权限）；
5. 执行unlock tables，这时候t.cfg文件会被删除；
6. 执行alter table r import tablesapce，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

至此，拷贝表数据操作就完成了。这个流程的执行过程图如下：

关于拷贝表的这个流程，有以下几个注意点：

1. 在第3步执行完flush table命令之后，db1.t整个表处于只读状态，直到执行unlock tables命令后才释放读锁；
2. 在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改r.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此，如果是一个很大的文件（比如TB级别），每个数据页都需要修改，所以你会看到这个import语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import语句的耗时是非常短的。

小结

今天这篇文章，我和你介绍了三种将一个表的数据导入到另外一个表中的方法。

我们来对比一下这三种方法的优缺点。

1. 物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
   • 必须是全表拷贝，不能只拷贝部分数据；
   • 需要到服务器上拷贝数据，在用户无法登陆数据库主机的场景下无法使用；
   • 由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。
2. 用mysqldump生成包含INSERT语句文件的防范，可以在where参数增加锅炉条件，来实现只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。
3. 用select...into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

后两种都是逻辑备份的方式，是可以跨引擎使用的。

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

我们前面介绍binlog_format=statement的时候，binlog记录的load data命令是带local的。既然这条命令是发送到备库去执行的，那么备库执行的时候也是本地执行，为什么需要这个local呢？如果写到binlog中的命令不带local，又会出现什么问题呢？

在上一篇文章中，我提到MySQL对自增主键锁做了优化，尽量在申请到自增id以后，就释放自增锁。

因此，insert语句是一个很轻量的操作。不过，这个结论是对于“普通的insert语句”才有效。也就是说，还有些insert语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增id以后就立马释放自增锁。

那么，今天这篇文章，我们就一起来聊聊这个话题。

1. insert...select语句

我们先从上一篇文章的话题说起吧。表t1和t2的表结构、初始化数据语句如下，今天的例子我们还是针对这两个表展开。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

现在，我们一起来看看为什么在可重复读隔离级别下，binlog_format=statement时执行：

1

insert iinto t2(c, d) select c, d from t;

这个语句时需要对表t的所有行和间隙加锁呢？

其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：

实际的执行效果是，如果session B先执行，由于这个语句对表t主键索引加了（-∞，1]这个next-key lock，会在语句执行完成后，才允许session A的insert语句执行。

但如果没有锁的话，就可能出现session B的insert语句先执行，但是后写入binlog的情况。于是，在binlog_format=statement的情况下，binlog里面就记录了这样的语句序列：

1
2

insert into t values(-1, -1, -1);
insert into t2(c, d) select c, d from t;

这个语句到了备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。

2. insert循环写入

当然了，执行insert...select的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

如果现在有这么一个需求：要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1。

此时，我们可以这么写这条SQL语句：

1

insert into t2(c, d) (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围，就是表t索引c上的(3, 4]和(4, supremum]的两个next-key lock，以及主键索引上id=4这一行。

它的执行流程也比较简单，从表t中按照索引c倒序，扫描第一行，拿到结果写入到表t2中。

因此整条语句的扫描行数是1。

这个语句执行的慢查询日志（slow log），如下图所示：

通过这个慢查询日志，我们看到Rows_examined=1，正好验证了执行这条语句的扫描行数为1。

那么，如果我们是要把这样的一行数据插入到表t中的话：

1

insert into t(c, d) (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的执行流程是怎样的？扫描行数又是多少呢？

这时候，我们再看慢查询日志就会发现不对了。

可以看到，这时候的Rows_examined的值是5。

我在前面的文章提到过，希望你能够学会用explain的结果来“脑补”正条语句的执行过程。今天，我们就来一起试试。

如图4所示就是这条语句的explain结果。i

从Extra字段可以看到“Using temporary"字样，表示这个语句用到了临时表。也就是说，执行过程中，需要把表t的内容读出来，写入临时表。

图中rows显示的是1，我们不妨先对这个语句的执行流程做一个猜测：如果说是把子查询的结果读出来（扫描1行），写入临时表，然后再从临时表读出来（扫描1行），写回表t中。那么，这个语句的扫描行数就应该是2，而不是5。

所以，这个猜测不对。实际上，Explain结果里的rows=1是因为受到了limit 1的影响。

从另一个角度考虑的话，我们可以看看InnoDB扫描了多少行。如下图所示，是在执行这个语句前后查看innodb_rows_read的结果。

可以看到，这个语句执行前后，Innodb_rows_read的值增加了4。因此默认临时表是使用Memory引擎的，所以这4行查的都是表t，也就是说对表t做了全表扫描。

这样，我们就把整个执行过程理清楚了：

1. 创建临时表，表里有两个字段c和d。
2. 按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表。这时，Rows_examined=4。
3. 由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中。这时，Rows_examined的值加1，变成了5。

也就是说，这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。

至于这个语句的执行为什么需要临时表，原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历的过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。

由于实现上这个语句没有在子查询中就直接使用limit 1，从而导致了这个语句的执行需要遍历整个表t。它的优化方法也比较简单，就是用前面介绍的方法，先insert into到临时表temp_t，这样就只需要扫描一行；然后再从表temp_t里面取出这行数据插入表t1。

当然，由于这个语句涉及到的数据量很小，你可以考虑使用内存临时表来做这个优化。使用内存临时表优化时，语句序列的写法如下：

1
2
3
4

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c + 1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

3. insert唯一键冲突

前面的两个例子是使用insert...select的情况，接下来我要介绍的这个例子就是最常见的insert语句出现唯一键冲突的情况。

对于有唯一键的表，插入数据时出现唯一键冲突也是常见的情况了。我先给你举一个简单的唯一键冲突的例子。

这个例子也是在可重复读（repeatable read）隔离级别下执行的。可以看到，session B要执行的insert语句进入了锁等待状态。

也就是说，session A执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。我们前面说过，一个next-key lock就是由它的右边界的值定义的。这时候，session A持有索引c上的(5, 10]共享的next-key lock（读锁）。

至于为什么要加这个锁，其实我也没找到合理的解释。从作用上来看，这样做可以避免这一行被别的事务删掉。

这里官方文档有一个描述错误，认为如果冲突的是主键索引，就加记录锁，唯一索引才加next-key lock。但实际上，这两类索引冲突加的都是next-key lock。

这里，我就先和你分享一个经典的死锁场景。

在session A执行rollback语句回滚的时候，session C几乎同时发现死锁并返回。

这个死锁产生的逻辑是这样的：

1. 在T1时刻，启动session A，并执行insert语句，此时在索引c的c=5上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁（如果你的印象模糊了，可以回顾下第21篇文章介绍的加锁规则）。
2. 在T2时刻，session B要执行相同的insert语句，发现了唯一键冲突，加上读锁；同样地，session C也在索引c上，c=5这一个记录上，加了读锁。
3. T3时刻，session A回滚。这时候，session B和session C都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁。

这个流程的状态变化图如下所示。

4. insert into ... on duplicate key update

上面这个例子是主键冲突后直接报错，如果是改写成

1

insert into t values(11, 10, 10) on duplicate key update d=100;

的话，就会给索引c上(5, 10]加一个排他的next-key lock（写锁）。

insert into ... on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

现在表t里面已经有了(1, 1, 1)和(2, 2, 2)这两行，我们再来看看下面这个语句执行的效果：

可以看到，主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行。

需要注意的是，执行这条语句的affected rows返回的是2，很容易在成误解。实际上，真正更新的只有一行，只是在代码实现桑，insert和update都认为自己成功了，update计数增加了1，insert计数增加了1。

小结

今天这篇文章，我和你介绍了几种特殊情况下的insert语句。

insert...select时很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。

而如果insert和select的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-key lock（S锁）。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

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

你平时在两个表之间拷贝数据用的是什么方法，有什么注意事项吗？在你的应用场景里，这个方法，相较于其他方法的优势是什么呢？

另外，insert..select执行期间，有其他线程操作原表，会导致逻辑错误。其实这是不会的，如果不加锁，就是快照读。

一条语句执行期间，它的一致性视图是不会修改的，所以即使有其他事务修改了原表的数据，也不会影响这条语句看到的数据。

在上一篇文章中，我提到MySQL对自增主键锁做了优化，尽量在申请到自增id以后，就释放自增锁。

因此，insert语句是一个很轻量的操作。不过，这个结论是对于“普通的insert语句”才有效。也就是说，还有些insert语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增id以后就立马释放自增锁。

那么，今天这篇文章，我们就一起来聊聊这个话题。

1. insert...select语句

我们先从上一篇文章的话题说起吧。表t1和t2的表结构、初始化数据语句如下，今天的例子我们还是针对这两个表展开。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

现在，我们一起来看看为什么在可重复读隔离级别下，binlog_format=statement时执行：

1

insert iinto t2(c, d) select c, d from t;

这个语句时需要对表t的所有行和间隙加锁呢？

其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：

实际的执行效果是，如果session B先执行，由于这个语句对表t主键索引加了（-∞，1]这个next-key lock，会在语句执行完成后，才允许session A的insert语句执行。

但如果没有锁的话，就可能出现session B的insert语句先执行，但是后写入binlog的情况。于是，在binlog_format=statement的情况下，binlog里面就记录了这样的语句序列：

1
2

insert into t values(-1, -1, -1);
insert into t2(c, d) select c, d from t;

这个语句到了备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。

2. insert循环写入

当然了，之心吧insert...select的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

如果现在有这么一个需求：要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1。

此时，我们可以这么写这条SQL语句：

1

insert into