mysql-要不要使用分区表

发表于 2019-12-17 分类于 mysql

问题解析

要不要使用分区表？我经常被问到这样一个问题：分区表有什么问题，为什么公司规范不让使用分区表呢？今天，我们就来聊聊分区表的使用行为，然后再一起回答这个问题。

分区表是什么？为了说明分区表的组织形式，我先创建一个表t：

CREATE TABLE t̀ ̀( f̀time ̀datetime NOT NULL, `c ̀int(11) DEFAULT NULL, KEY (̀ ftime )̀) ENGINE=InnoDB DEFAULT CHARSET=latin1PARTITION 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);图1 表t的磁盘文件我在表t中初始化插入了两行记录，按照定义的分区规则，这两行记录分别落在p_2018和p_2019这两个分区上。

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

也就是说：

对于引擎层来说，这是4个表；

对于Server层来说，这是1个表。

你可能会觉得这两句都是废话。

其实不然，这两句话非常重要，可以帮我们理解分区表的执行逻辑。

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

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

我们初始化表t的时候，只插入了两行数据， ftime的值分别是，‘2017-4-1’ 和’2018-4-1’ 。

session A的select语句对索引ftime上这两个记录之间的间隙加了锁。

如果是一个普通表的话，那么T1时刻，在表t的ftime索引上，间隙和加锁状态应该是图3这样的。

图3 普通表的加锁范围也就是说，‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。

那么，sesion B的两条插入语句应该都要进入锁等待状态。

但是，从上面的实验效果可以看出，session B的第一个insert语句是可以执行成功的。

这是因为，对于引擎来说，p_2018和p_2019是两个不同的表，也就是说2017-4-1的下一个记录并不是2018-4-1，而是p_2018分区的supremum。

所以T1时刻，在表t的ftime索引上，间隙和加锁的状态其实是图4这样的：

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

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

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

图5 session B被锁住信息看完InnoDB引擎的例子，我们再来一个MyISAM分区表的例子。

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

图6 用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，执行一条插入语句后报错的情况。

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

这时，你一定从表名猜到了，这个表我用的是MyISAM引擎。

是的，因为使用InnoDB引擎的话，并不会出现这个问题。

MyISAM分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由server层控制。

通用分区策略，是MySQL一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。

从MySQL 5.7.9开始，InnoDB引擎引入了本地分区策略（native partitioning）。

这个策略是在InnoDB内部自己管理打开分区的行为。

MySQL从5.7.17开始，将MyISAM分区表标记为即将弃用(deprecated)，意思是“从这个版本开始不建议这么使用，请使用替代方案。

在将来的版本中会废弃这个功能”。

从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。

目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略。

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

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

这句话是什么意思呢？接下来，我就用下面这个例子来和你说明。

如图8和图9所示，分别是这个例子的操作序列和执行结果图。

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

这也是DBA同学经常说的，分区表，在做DDL的时候，影响会更大。

如果你使用的是普通分表，那么当你在truncate一个分表的时候，肯定不会跟另外一个分表上的查询语句，出现MDL锁冲突。

到这里我们小结一下：

MySQL在第一次打开分区表的时候，需要访问所有的分区；
在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
在引擎层，认为这是不同的表，因此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年的分区。

这种情况下，访问分区表的性能自然是不好的。

这里有两个问题需要注意：

分区并不是越细越好。

实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

分区也不要提前预留太多，在使用之前预先创建即可。

比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。

对于没有数据的历史分区，要及时的drop掉。

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

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

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

我们举例的表中没有用到自增主键，假设现在要创建一个自增字段id。

MySQL要求分区表中的主键必须包含分区字段。

如果要在表t的基础上做修改，你会怎么定义这个表的主键呢？为什么这么定义呢？你可以把你的结论和分析写在留言区，我会在下一篇文章的末尾和你讨论这个问题。

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

上期问题时间上篇文章后面还不够多，可能很多同学还没来记得看吧，我们就等后续有更多留言的时候，再补充本期的“上期问题时间”吧。

@夹心面包提到了在grant的时候是支持通配符的：”_”表示一个任意字符，“%”表示任意字符串。

这个技巧在一个分库分表方案里面，同一个分库上有多个db的时候，是挺方便的。

不过我个人认为，权限赋值的时候，控制的精确性还是要优先考虑的。

夹心面包  5我说下我的感想1 经典的利用分区表的场景1 zabbix历史数据表的改造,利用存储过程创建和改造2 后台数据的分析汇总,比如日志数据,便于清理这两种场景我们都在执行,我们对于分区表在业务采用的是hash 用户ID方式,不过大规模应用分区表的公司我还没遇到过2 分区表需要注意的几点总结下1 由于分区表都很大,DDL耗时是非常严重的,必须考虑这个问题2 分区表不能建立太多的分区,我曾被分享一个因为分区表分区过多导致的主从延迟问题3 分区表的规则和分区需要预先设置好,否则后来进行修改也很麻烦2019-02-20 作者回复非常好2019-02-20aliang  2老师，mysql还有一个参数是innodb_open_files，资料上说作用是限制Innodb能打开的表的数量。

它和open_files_limit之间有什么关系吗？2019-02-21精选留言 作者回复好问题。

在InnoDB引擎打开文件超过 innodb_open_files这个值的时候，就会关掉一些之前打开的文件。

其实我们文章中，InnoDB分区表使用了本地分区策略以后，即使分区个数大于open_files_limit ，打开InnoDB分区表也不会报“打开文件过多”这个错误，就是innodb_open_files这个参数发挥的作用。

2019-02-21怀刚  1请教下采用”先做备库、切换、再做备库”DDL方式不支持AFTER COLUMN是因为BINLOG原因吗？以上DDL方式会存在影响“有损”的吧？“无损”有哪些方案呢？如果备库承载读请求但又不能接受“长时间”延时2019-03-09 作者回复1. 对，binlog对原因2. 如果延迟算损失，确实是有损的。

备库上的读流量要先切换到主库（也就是为什么需要在低峰期做做个操作）2019-03-09权恒星  1这个只适合单机吧？集群没法即使用innodb引擎，又支持分区表吧，只能使用中间件了。

之前调研了一下，官方只有ndb cluster才支持分区表？2019-02-20 作者回复对这篇文章讲的是单机上的单表多分区2019-02-20One day  1这次竟然只需要再读两次就能读懂，之前接触过mycat和sharding-jdbc实现分区,老师能否谈谈这方面的呢2019-02-20 作者回复赞两次这个就是我们文章说的“分库分表中间件”不过看到不少公司都会要在这基础上做点定制化2019-02-20于欣磊  0阿里云的DRDS就是分库分表的中间件典型代表。

自己实现了一个层Server访问层在这一层进行分库分表（对透明），然后MySQL只是相当于存储层。

一些Join、负载Order by/Group by都在DRDS中间件这层完成，简单的逻辑插叙计算完对应的分库分表后下推给MySQL https://www.aliyun.com/product/drds2019-02-25  0老师确认下，5.7.9之后的innodb分区表，是访问第一个表时不会去打开所有的分区表了吗？2019-02-25 作者回复第一次访问的时候，要打开所有分区的2019-02-25启程  0老师，你好，请教你个分区表多条件查询建索引的问题；

表A,列a,b,c,d,e,f,g,h (其中b是datetime，a是uuid,其余是varchar)主键索引，(b,a),按月分区查询情况1：

where b>=? and b<=? order by b desc limit 500;查询情况2:where b>=? and b<=? and c in(?) order by b desc limit 500;查询情况3：

where b>=? and b<=? and d in(?) and e in(?) order by b desc limit 500;查询情况4：

where b>=? and b<=? and c in(?) and d in(?) and e in(?) order by b desc limit 500;自己尝试建过不少索引，效果不是很好，请问老师，我要怎么建索引？？？2019-02-25 作者回复这个还是得看不同的语句的执行次数哈如果从语句类型上看，可以考虑加上(b,c)、(b,d)这两个联合索引2019-02-26NICK  0老师，如果用户分区，业务要做分页过滤查询怎么做才好？2019-02-25 作者回复分区表的用法跟普通表，在sql语句上是相同的。

2019-02-25锋芒  0老师，请问什么情况会出现间隙锁？能否专题讲一下锁呢？2019-02-23 作者回复20、21两篇看下2019-02-23daka  0本期提到了ndb，了解了下，这个存储引擎高可用及读写可扩展性功能都是自带，感觉是不错，为什么很少见人使用呢？生产不可靠？2019-02-21helloworld.xs  0请教个问题，一般mysql会有查询缓存，但是update操作也有缓存机制吗？使用mysql console第一次执行一个update SQL耗时明显比后面执行相同update SQL要慢，这是为什么？2019-02-21 作者回复update的话，主要应该第一次执行的时候，数据都读入到了2019-02-21万勇  0老师，请问add column after column_name跟add column不指定位置，这两种性能上有区别吗？我们在add column 指定after column_name的情况很多。

2019-02-21 作者回复仅仅看性能，是没什么差别的但是建议尽量不要加after column_name，也就是说尽量加到最后一列。

因为其实没差别，但是加在最后有以下两个好处：

开始有一些分支支持快速加列，就是说如果你加在最后一列，是瞬间就能完成，而加了after column_name，就用不上这些优化（以后潜在的好处）2. 我们在前面的文章有提到过，如果怕对线上业务造成影响，有时候是通过“先做备库、切换、再做备库”这种方式来执行ddl的，那么使用after column_name的时候用不上这种方式。

实际上列的数据是不应该有影响的，还是要形成好习惯2019-02-21Q  0老师请问下网站开发数据库表是myisam和innodb混合引擎考虑管理比较麻烦想统一成innodb请问是否影响数据库或带来什么隐患吗？网站是网上商城购物类型的2019-02-20 作者回复应该统一成innodb网上商城购物类型更要用InnoDB，因为MyISAM并不是crash-safe的。

测试环境改完回归下2019-02-21夹心面包  0我觉得老师的问题可以提炼为 Mysql复合主键中自增长字段设置问题复合索引可以包含一个auto_increment,但是auto_increment列必须是第一列。

这样插入的话,只需要指定非自增长的列语法 alter table test1 change column id id int auto_increment;2019-02-20 作者回复“但是auto_increment列必须是第一列” 可以不是哦2019-02-20undifined  0老师，有两个问题1. 图三的间隙锁，根据“索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁”，不应该是 (-∞,2017-4-1],(2017-4-1,2018-4-1)吗，图4左边的也应该是 (-∞,2017-4-1],(2017-4-1, supernum)，是不是图画错了2. 现有的一个表，一千万行的数据， InnoDB 引擎，如果以月份分区，即使有 MDL 锁和初次访问时会查询所有分区，但是综合来看，分区表的查询性能还是要比不分区好，这样理解对吗思考题的答案 ALTER TABLE tADD COLUMN (id INT AUTO_INCREMENT ),ADD PRIMARY KEY (id, ftime);麻烦老师解答一下，谢谢老师2019-02-20 作者回复1. 我们语句里面是 where ftime=’2017-5-1’ 哈，不是“4-1”2. “分区表的查询性能还是要比不分区好，这样理解对吗”，其实还是要看表的索引情况。

当然一定存在一个数量级N，把这N行分到10个分区表，比把这N行放到一个大表里面，效率高2019-02-20千木  0老师您好，你在文章里面有说通用分区规则会打开所有引擎文件导致不可用，而本地分区规则应该是只打开单个引擎文件，那你不建议创建太多分区的原因是什么呢？如果是本地分区规则，照例说是不会影响的吧，叨扰了2019-02-20 作者回复“本地分区规则应该是只打开单个引擎文件”，并不是哈，我在文章末尾说了，也会打开所有文件的，只是说本地分区规则有优化，比如如果文件数过多，就会淘汰之前打开的文件句柄（暂时关掉）。

所以分区太多，还是会有影响的2019-02-20郭江伟  0此时主键包含自增列+分区键，原因为对innodb来说分区等于单独的表，自增字段每个分区可以插入相同的值，如果主键只有自增列无法完全保证唯一性。

测试表如下：

mysql> show create table t\GTable: tCreate Table: CREATE TABLE t (id int(11) NOT NULL AUTO_INCREMENT,ftime datetime NOT NULL,c int(11) DEFAULT NULL,PRIMARY KEY (id,ftime),KEY ftime (ftime)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4/*!50100 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 row in set (0.00 sec)mysql> insert into t values(1,’2017-4-1’,1),(1,’2018-4-1’,1);Query OK, 2 rows affected (0.02 sec)mysql> select * from t;+—-+———————+——+| id | ftime | c |+—-+———————+——+| 1 | 2017-04-01 00:00:00 | 1 || 1 | 2018-04-01 00:00:00 | 1 |+—-+———————+——+2 rows in set (0.00 sec)mysql> insert into t values(null,’2017-5-1’,1),(null,’2018-5-1’,1);Query OK, 2 rows affected (0.02 sec)mysql> select * from t;+—-+———————+——+| id | ftime | c |+—-+———————+——+| 1 | 2017-04-01 00:00:00 | 1 || 2 | 2017-05-01 00:00:00 | 1 || 1 | 2018-04-01 00:00:00 | 1 || 3 | 2018-05-01 00:00:00 | 1 |+—-+———————+——+4 rows in set (0.00 sec)2019-02-20 作者回复2019-02-24wljs  0老师我想问个问题我们公司一个订单表有110个字段想拆分成两个表第一个表放经常查的字段第二个表放不常查的现在程序端不想改sql，数据库端来实现当查询字段中第一个表不存在就去关联第二个表查出数据 db能实现不2019-02-20 作者回复用view可能可以实现部分你的需求，但是强烈不建议这么做。

业务不想修改，就好好跟他们说，毕竟这样分（常查和不常查的垂直拆分）是合理的，对读写性能都有明显的提升的。

2019-02-20```

mysql-grant与flush privileges

发表于 2019-12-16 分类于 mysql

问题解析

在MySQL里面，grant语句是用来给用户赋权的。

不知道你有没有见过一些操作文档里面提到，grant之后要马上跟着执行一个flush privileges命令，才能使赋权语句生效。

如果没有执行这个flush命令的话，赋权语句真的不能生效吗？接下来，我就先和你介绍一下grant语句和flush privileges语句分别做了什么事情，然后再一起来分析这个问题。

为了便于说明，我先创建一个用户：

这条语句的逻辑是创建一个用户’ua’@’%’，密码是pa。

注意，在MySQL里面，用户名(user)+地址(host)才表示一个用户，因此 ua@ip1 和 ua@ip2代表的是两个不同的用户。

这条命令做了两个动作：

磁盘上，往mysql.user表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是N；

create user ‘ua‘@’%’ identified by ‘pa’;2. 内存里，往数组acl_users里插入一个acl_user对象，这个对象的access字段值为0。

在MySQL中，用户权限是有不同的范围的。

接下来，我就按照用户权限范围从大到小的顺序依次和你说明。

全局权限全局权限，作用于整个MySQL实例，这些权限信息保存在mysql库的user表里。

如果我要给用户 ua赋一个最高权限的话grant命令做了两个动作：

磁盘上，将mysql.user表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为‘Y’；
内存里，从数组acl_users中找到这个用户对应的对象，将access值（权限位）修改为二进制的“全1”。

在这个grant命令执行完成后，如果有新的客户端使用用户名ua登录成功，MySQL会为新连接维护一个线程对象，然后从acl_users数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。

之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位。

基于上面的分析我们可以知道：

grant 命令对于全局权限，同时更新了磁盘和内存。

命令完成后即时生效，接下来新创建的连接会使用新的权限。

对于一个已经存在的连接，它的全局权限不受grant命令的影响。

需要说明的是，一般在生产环境上要合理控制用户权限的范围。

如果一个用户有所有权限，一般就不应该设置为所有IP地址都可以访问。

如果要回收上面的grant语句赋予的权限，你可以使用revoke命令，用法与grant类似，做了如下两个动作：

磁盘上，将mysql.user表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为“N”；
内存里，从数组acl_users中找到这个用户对应的对象，将access的值修改为0。

db权限除了全局权限，MySQL也支持库级别的权限定义。

如果要让用户ua拥有库db1的所有权限，可以执行下面这条命令：

grant all privileges on . to ‘ua‘@’%’ with grant option;revoke all privileges on . from ‘ua‘@’%’;基于库的权限记录保存在mysql.db表中，在内存里则保存在数组acl_dbs中。

这条grant命令做了如下两个动作：

磁盘上，往mysql.db表中插入了一行记录，所有权限位字段设置为“Y”；
内存里，增加一个对象到数组acl_dbs中，这个对象的权限位为“全1”。

图2就是这个时刻用户ua在db表中的状态。

图2 mysql.db 数据行每次需要判断一个用户对一个数据库读写权限的时候，都需要遍历一次acl_dbs数组，根据user、host和db找到匹配的对象，然后根据对象的权限位来判断。

也就是说，grant修改db权限的时候，是同时对磁盘和内存生效的。

grant all privileges on db1.* to ‘ua‘@’%’ with grant option;grant操作对于已经存在的连接的影响，在全局权限和基于db的权限效果是不同的set global sync_binlog这个操作是需要super权限的。

可以看到，虽然用户ua的super权限在T3时刻已经通过revoke语句回收了，但是在T4时刻执行set global的时候，权限验证还是通过了。

这是因为super是全局权限，这个权限信息在线程对象中，而revoke操作影响不到这个线程对象。

而在T5时刻去掉ua对db1库的所有权限后，在T6时刻session B再操作db1库的表，就会报错“权限不足”。

这是因为acl_dbs是一个全局数组，所有线程判断db权限都用这个数组，这样revoke操作马上就会影响到session B。

这里在代码实现上有一个特别的逻辑，如果当前会话已经处于某一个db里面，之前use这个库的时候拿到的库权限会保存在会话变量中。

你可以看到在T6时刻，session C和session B对表t的操作逻辑是一样的。

但是session B报错，而session C可以执行成功。

这是因为session C在T2 时刻执行的use db1，拿到了这个库的权限，在切换出db1库之前，session C对这个库就一直有权限。

表权限和列权限除了db级别的权限外，MySQL支持更细粒度的表权限和列权限。

其中，表权限定义存放在表mysql.tables_priv中，列权限定义存放在表mysql.columns_priv中。

这两类权限，组合起来存放在内存的hash结构column_priv_hash中。

这两类权限的赋权命令如下：

跟db权限类似，这两个权限每次grant的时候都会修改数据表，也会同步修改内存中的hash结构。

因此，对这两类权限的操作，也会马上影响到已经存在的连接。

看到这里，你一定会问，看来grant语句都是即时生效的，那这么看应该就不需要执行flush privileges语句了呀。

答案也确实是这样的。

flush privileges命令会清空acl_users数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组。

也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。

同样地，对于db权限、表权限和列权限，MySQL也做了这样的处理。

也就是说，如果内存的权限数据和磁盘数据表相同的话，不需要执行flush privileges。

而如果我们都是用grant/revoke语句来执行的话，内存和数据表本来就是保持同步更新的。

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

flush privileges使用场景create table db1.t1(id int, a int);grant all privileges on db1.t1 to ‘ua‘@’%’ with grant option;GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO ‘ua‘@’%’ with grant option;那么，flush privileges是在什么时候使用呢？显然，当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges语句可以用来重建内存数据，达到一致状态。

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

由于在T3时刻直接删除了数据表的记录，而内存的数据还存在。

这就导致了：

T4时刻给用户ua赋权限失败，因为mysql.user表中找不到这行记录；
而T5时刻要重新创建这个用户也不行，因为在做内存判断的时候，会认为这个用户还存在。

小结
MySQL用户权限在数据表和内存中的存在形式，以及grant和revoke命令的执行逻辑。

grant语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。

因此，规范地使用 grant和revoke语句，是不需要随后加上flush privileges语句的。

flush privileges语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。

而这种不一致往往是由于直接用DML语句操作系统权限表导致的，所以我们尽量不要使用这类语句。

另外，在使用grant语句赋权时，你可能还会看到这样的写法：

这条命令加了identified by ‘密码’，语句的逻辑里面除了赋权外，还包含了：

如果用户’ua’@’%’不存在，就创建这个用户，密码是pa；
如果用户ua已经存在，就将密码修改成pa。

grant super on . to ‘ua‘@’%’ identified by ‘pa’;这也是一种不建议的写法，因为这种写法很容易就会不慎把密码给改了。

mysql-怎么最快地复制一张表

发表于 2019-12-15 分类于 mysql

问题解析

怎么最快地复制一张表我在上一篇文章最后，给你留下的问题是怎么在两张表中拷贝数据。

如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用insert … select 语句即可实现。

当然，为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表。

这时，有两种常用的方法。

接下来的内容，我会和你详细展开一下这两种方法。

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

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

mysqldump方法一种方法是，使用mysqldump命令将数据导出成一组INSERT语句。

你可以使用下面的命令：

把结果输出到临时文件。

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

–single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用STARTTRANSACTION WITH CONSISTENT SNAPSHOT的方法；
–add-locks设置为0，表示在输出的文件结果里，不增加” LOCK TABLES t WRITE;” ；
–no-create-info的意思是，不需要导出表结构；

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.tmysqldump -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.sql4. –set-gtid-purged=off表示的是，不输出跟GTID相关的信息；

–result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。

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

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

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

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

需要说明的是，source并不是一条SQL语句，而是一个客户端命令。

mysql客户端执行这个命令的流程是这样的：

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

也就是说，服务端执行的并不是这个“source t.sql”语句，而是INSERT语句。

所以，不论是在慢查询日志（slow log），还是在binlog，记录的都是这些要被真正执行的INSERT语句。

导出CSV文件另一种方法是直接将结果导出成.csv文件。

MySQL提供了下面的语法，用来将查询结果导出到服务端本地目录：

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

这条语句会将结果保存在服务端。

如果你执行命令的客户端和MySQL服务端不在同一个机器上，客户端机器的临时目录下是不会生成t.csv文件的。

mysql -h127.0.0.1 -P13000 -uroot db2 -e “source /client_tmp/t.sql”select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’;2. into outfile指定了文件的生成位置（/server_tmp/），这个位置必须受参数secure_file_priv的限制。

参数secure_file_priv的可选值和作用分别是：

如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；

如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；

如果设置为NULL，就表示禁止在这个MySQL实例上执行select … into outfile 操作。

这条命令不会帮你覆盖文件，因此你需要确保/server_tmp/t.csv这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。
这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。

但是，如果字段中包含换行符，在生成的文本中也会有换行符。

不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开。

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

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

打开文件/server_tmp/t.csv，以制表符(\t)作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
启动事务。
判断每一行的字段数与表db2.t是否相同：

若不相同，则直接报错，事务回滚；

若相同，则构造成一行，调用InnoDB引擎接口，写入到表中。

重复步骤3，直到/server_tmp/t.csv整个文件读入完成，提交事务。

你可能有一个疑问，如果binlog_format=statement，这个load语句记录到binlog里以后，怎么在备库重放呢？由于/server_tmp/t.csv文件只保存在主库所在的主机上，如果只是把这条语句原文写到binlog中，在备库执行的时候，备库的本地机器上没有这个文件，就会导致主备同步停止。

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

主库执行完成后，将/server_tmp/t.csv文件的内容直接写到binlog文件中。

load data infile ‘/server_tmp/t.csv’ into table db2.t;2. 往binlog文件中写入语句load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLEdb2 .̀̀ t。

把这个binlog日志传到备库。
备库的apply线程在执行这个事务日志时：

a. 先将binlog中t.csv文件的内容读出来，写入到本地临时目录/tmp/SQL_LOAD_MB-1-0中；

b. 再执行load data语句，往备库的db2.t表中插入跟主库相同的数据。

执行流程如图2所示：

图2 load data的同步流程注意，这里备库执行的load data语句里面，多了一个“local”。

它的意思是“将执行这条命令的客户端所在机器的本地文件/tmp/SQL_LOAD_MB-1-0的内容，加载到目标表db2.t中”。

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

不加“local”，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下；
加上“local”，读取的是客户端的文件，只要mysql客户端有访问这个文件的权限即可。

这时候，MySQL客户端会先把本地文件传给服务端，然后执行上述的load data流程。

另外需要注意的是，select …into outfile方法不会生成表结构文件, 所以我们导数据时还需要单独的命令得到表结构定义。

mysqldump提供了一个–tab参数，可以同时导出表结构定义文件和csv数据文件。

这条命令的使用方法如下：

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

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

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

因为，一个InnoDB表，除了包含这两个物理文件外，还需要在数据字典中注册。

直接拷贝这两个文件的话，因为数据字典中没有db2.t这个表，系统是不会识别和接受它们的。

不过，在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。

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

执行 create table r like t，创建一个相同表结构的空表；
执行alter table r discard tablespace，这时候r.ibd文件会被删除；
执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL进程要有读写权限）；
执行unlock tables，这时候t.cfg文件会被删除；
执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件mysqldump -h$host -P$port -u$user —single-transaction –set-gtid-purged=OFF db1 t –where=”a>900” –tab=$secure_file_priv的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

至此，拷贝表数据的操作就完成了。

这个流程的执行过程图如下：

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

在第3步执行完flsuh table命令之后，db1.t整个表处于只读状态，直到执行unlock tables命令后才释放读锁；
在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改r.ibd的表空间id。

而这个表空间id存在于每一个数据页中。

因此，如果是一个很大的文件（比如TB级别），每个数据页都需要修改，所以你会看到这个import语句的执行是需要一些时间的。

当然，如果是相比于逻辑导入的方法，import语句的耗时是非常短的。

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

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

物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。

如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。

但是，这种方法的使用也有一定的局限性：

必须是全表拷贝，不能只拷贝部分数据；

需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；

由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。

用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。

这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。

用select … into outfile的方法是最灵活的，支持所有的SQL写法。

但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

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

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

我们前面介绍binlog_format=statement的时候，binlog记录的load data命令是带local的。

既然这条命令是发送到备库去执行的，那么备库执行的时候也是本地执行，为什么需要这个local呢？如果写到binlog中的命令不带local，又会出现什么问题呢？你可以把你的分析写在评论区，我会在下一篇文章的末尾和你讨论这个问题。

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

上期问题时间我在上篇文章最后给你留下的思考题，已经在今天这篇文章的正文部分做了回答。

上篇文章的评论区有几个非常好的留言，我在这里和你分享一下。

@huolang 同学提了一个问题：如果sessionA拿到c=5的记录锁是写锁，那为什么sessionB和sessionC还能加c=5的读锁呢？这是因为next-key lock是先加间隙锁，再加记录锁的。

加间隙锁成功了，加记录锁就会被堵住。

如果你对这个过程有疑问的话，可以再复习一下第30篇文章中的相关内容。

@一大只同学做了一个实验，验证了主键冲突以后，insert语句加间隙锁的效果。

比我在上篇文章正文中提的那个回滚导致死锁的例子更直观，体现了他对这个知识点非常好的理解和思考，很赞。

@roaming 同学验证了在MySQL 8.0版本中，已经能够用临时表处理insert … select写入原表的语句了。

@老杨同志的回答提到了我们本文中说到的几个方法。

poppy  4关于思考题，我理解是备库的同步线程其实相当于备库的一个客户端，由于备库的会把binlog中t.csv的内容写到/tmp/SQL_LOAD_MB-1-0中，如果load data命令不加’local’表示读取服务端的文件，文件必须在secure_file_priv指定的目录或子目录，此时可能找不到该文件，主备同步执行会失败。

而加上local的话，表示读取客户端的文件，既然备份线程都能在该目录下创建临时文件/tmp/SQL_LOAD_MB-1-0,必然也有权限访问，把该文件传给服务端执行。

2019-02-15 作者回复这是其中一个原因2019-02-16☆appleう  3通知对方更新数据的意思是: 针对事务内的3个操作:插入和更新两个都是本地操作，第三个操作是远程调用，这里远程调用其实是想把本地操作的那两条通知对方(对方:远程调用)，让对方把数据更新，这样双方(我和远程调用方)的数据达到一致，如果对方操作失败，事务的前两个操作也会回滚，主要是想保证双方数据的一致性，因为远程调用可能会出现网络延迟超时等因素，极端情况会导致事务10s左右才能处理完毕，想问的是这样耗时的事务会带来哪些影响呢？设计的初衷是想这三个操作能原子执行，只要有不成功就可以回滚，保证两方数据的一致性精选留言耗时长的远程调用不放在事务中执行，会出现我这面数据完成了，而对方那面由于网络等问题，并没有更新，这样两方的数据就出现不一致了2019-02-15 作者回复嗯了解了这种设计我觉得就是会对并发性有比较大的影响。

一般如果网络状态不好的，会建议把这个更新操作放到消息队列。

就是说1. 先本地提交事务。

把通知这个动作放到消息队列，失败了可以重试；
远端接收事件要设置成可重入的，就是即使同一个消息收到两次，也跟收到一次是相同的效果。

2 和3 配合起来保证最终一致性。

这种设计我见到得比较多，你评估下是否符合你们业务的需求哈2019-02-15undifined  3老师，用物理导入的方式执行 alter table r import tablespace 时提示ERROR 1812 (HY000): Tablespace is missing for table db1.r. 此时 db1/ 下面的文件有 db.opt r.cfg r.frm r.ibd t.frm t.ibd；这个该怎么处理执行步骤：

mysql> create table r like t;Query OK, 0 rows affected (0.01 sec)mysql> alter table r discard tablespace;Query OK, 0 rows affected (0.01 sec)mysql> flush table t for export;Query OK, 0 rows affected (0.00 sec)cp t.cfg r.cfgcp t.ibd r.ibdmysql> unlock tables;Query OK, 0 rows affected (0.01 sec)mysql> alter table r import tablespace;ERROR 1812 (HY000): Tablespace is missing for table db1.r.2019-02-15 作者回复应该就是评论区其他同学帮忙回复的权限问题了吧？2019-02-15lionetes  2mysql> select * from t;+—-+——+| id | name |+—-+——+| 1 | Bob || 2 | Mary || 3 | Jane || 4 | Lisa || 5 | Mary || 6 | Jane || 7 | Lisa |+—-+——+7 rows in set (0.00 sec)mysql> create table tt like t;Query OK, 0 rows affected (0.03 sec)mysql> alter table tt discard tablespace;Query OK, 0 rows affected (0.01 sec)mysql> flush table t for export;Query OK, 0 rows affected (0.01 sec)mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)mysql> alter table tt import tablespace;Query OK, 0 rows affected (0.03 sec)mysql> show tables;+—————-+| Tables_in_test |+—————-+| t || t2 || tt |+—————-+3 rows in set (0.00 sec)mysql> select * from t;+—-+——+| id | name |+—-+——+| 1 | Bob || 2 | Mary || 3 | Jane || 4 | Lisa || 5 | Mary || 6 | Jane || 7 | Lisa |+—-+——+7 rows in set (0.00 sec)mysql> select * from tt;+—-+——+| id | name |+—-+——+| 1 | Bob || 2 | Mary || 3 | Jane || 4 | Lisa || 5 | Mary || 6 | Jane || 7 | Lisa |+—-+——+7 rows in set (0.00 sec)ll 后查看 tt.cfg 文件没有自动删除 5.7mysql-rw-r—–. 1 mysql mysql 380 2月 15 09:51 tt.cfg-rw-r—–. 1 mysql mysql 8586 2月 15 09:49 tt.frm-rw-r—–. 1 mysql mysql 98304 2月 15 09:51 tt.ibd2019-02-15 作者回复你说得对，细致import动作不会自动删除cfg文件，我图改一下2019-02-15☆appleう  2老师，我想问一个关于事务的问题，一个事务中有3个操作，插入一条数据(本地操作),更新一条数据(本地操作)，然后远程调用，通知对方更新上面数据(如果远程调用失败会重试，最多3次，如果遇到网络等问题，远程调用时间会达到5s,极端情况3次会达到15s)，那么极端情况事务将长达5-15s，这样会带来什么影响吗？2019-02-15 作者回复“通知对方更新上面数据” 是啥概念，如果你这个事务没提交，其他线程也看不到前两个操作的结果的。

设计上不建议留这么长的事务哈，最好是可以先把事务提交了，再去做耗时的操作。

2019-02-15AstonPutting  1老师，mysqlpump能否在平时代替mysqldump的使用？2019-02-22 作者回复我觉得是2019-02-23PengfeiWang  1老师，您好：

文中“–add-locks 设置为 0，表示在输出的文件结果里，不增加” LOCK TABLES t WRITE;” 是否是笔误，–add-locks应该是在insert语句前后添加锁，我的理解此处应该是–skip-add-locks，不知道是否是这样？2019-02-18 作者回复嗯嗯，命令中写错了，是–add-locks=0，效果上跟–skip-add-locks是一样的哈细致2019-02-19长杰  1课后题答案不加“local”，是读取服务端的文件，这个文件必须在 secure_file_priv 指定的目录或子目录下；

而备库的apply线程执行时先讲csv内容读出生成tmp目录下的临时文件，这个目录容易受secure_file_priv的影响，如果备库改参数设置为Null或指定的目录，可能导致load操作失败，加local则不受这个影响。

2019-02-17 作者回复2019-02-18尘封  1老师mysqldump导出的文件里，单条sql里的value值有什么限制吗默认情况下，假如一个表有几百万，那mysql会分为多少个sql导出？问题：因为从库可能没有load的权限，所以local2019-02-15 作者回复好问题，会控制单行不会超过参数net_buffer_length，这个参数是可以通过–net_buffer_length 传给mysqldump 工具的2019-02-28佳  0老师好，这个/tmp/SQL_LOAD_MB-1-0 是应该在主库上面，还是备库上面？为啥我执行完是在主库上面出现了这个文件呢？2019-03-14 作者回复就是在MySQL的运行进程所在的主机上2019-03-16xxj123go  0传输表空间方式对主从同步会有影响么2019-03-12 作者回复你可以看下执行以后，进不进binlog 2019-03-13王显伟  0第一位留言的朋友报错我也复现了，原因是用root复制的文件，没有修改属组导致的2019-02-16 作者回复2019-02-17夜空中最亮的星（华仔）  0学习完老师的课都想做dba了2019-02-15undifined  0老师错误信息的截屏 https://www.dropbox.com/s/8wyet4bt9yfjsau/mysqlerror.png?dl=0MySQL 5.7，Mac 上的 Docker 容器里面跑的，版本是 5.7.172019-02-15 作者回复额，打不开。

。

可否发个微博贴图2019-02-16晨思暮语  0不好意思，第一条留言中，实验三的最后一天语句还是少了，在这里贴一下，mysql> select * from t where id=1;+—-+——+| id | a |+—-+——+| 1 | 3 |+—-+——+1 row in set (0.00 sec)2019-02-15晨思暮语  0老师好，由于字数限制，分两条：

我用的是percona数据库，问题是第15章中的思考题。

根据我做的实验，结论应该是：

MySQL 调用了 InnoDB 引擎提供的“修改为 (1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回一直没有想明白，老师再帮忙看看，谢谢！2019-02-15 作者回复我两个留言连在一起看没看明白你对哪个步骤的哪个结果有疑虑，可以写在现象里面（用注释即可）哈2019-02-16晨思暮语  0mysql> select version();+————+| version() |+————+| 5.7.22-log |+————+实验1:SESSION A:mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where id=1;+—-+——+| id | a |+—-+——+| 1 | 2 |+—-+——+1 row in set (0.00 sec)SESSION B:mysql> update t set a=3 where id=1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0SESSION A:mysql> update t set a=3 where id=1;Query OK, 0 rows affected (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 0mysql> select * from t where id=1;+—-+——+| id | a |+—-+——+| 1 | 2 |+—-+——+1 row in set (0.00 sec)实验2:SESSION A:mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where id=1;+—-+——+| id | a |+—-+——+| 1 | 2 |+—-+——+1 row in set (0.00 sec)SESSION B:mysql> update t set a=3 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0SESSION A:mysql> update t set a=3 where id=1;BLOCKEDSESSION B:mysql> commit;Query OK, 0 rows affected (0.00 sec)SESSION A:UPDATEmysql> update t set a=3 where id=1;Query OK, 0 rows affected (5.43 sec)Rows matched: 1 Changed: 0 Warnings: 0mysql> mysql> select * from t where id=1;+—-+——+| id | a |+—-+——+| 1 | 2 |+—-+——+1 row in set (0.00 sec)实验3:SESSION A:mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t where id=1;+—-+——+| id | a |+—-+——+| 1 | 2 |+—-+——+1 row in set (0.00 sec)SESSION B:mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update t set a=3 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0SESSION A:mysql> update t set a=3 where id=1;blockedSESSION B:mysql> rollback;Query OK, 0 rows affected (0.00 sec)SESSION A:UPDATEmysql> update t set a=3 where id=1;Query OK, 1 row affected (5.21 sec)Rows matched: 1 C2019-02-15库淘淘  0如果不加local 如secure_file_priv 设置为null 或者路径可能就不能成功,这样加了之后可以保证执行成功率不受参数secure_file_priv影响。

还有发现物理拷贝文件后，权限所属用户还得改下，不然import tablespace 会报错找不到文件，老师是不是应该补充上去，不然容易踩坑。

2019-02-15 作者回复嗯嗯，有同学已经踩了，我加个说明进去，多谢提醒2019-02-15lionetes  0@undifined 看下是否是权限问题引起的 cp 完后是不是mysql 权限2019-02-15 作者回复经验丰富如果进程用mysql用户启动，命令行是在root账号下，确实会出现这种情况2019-02-15Ryoma  0问老师一个主题无关的问题：现有数据库中有个表字段为text类型，但是目前发现text中的数据有点不太对。

请问在MySQL中有没有办法确认在插入时是否发生截断数据的情况么？（因为该字段被修改过，我现在不方便恢复当时的现场）2019-02-15 作者回复看那个语句的binlog （是row吧？） 2019-02-15```

mysql-insert语句的锁为什么这么多

发表于 2019-12-14 分类于 mysql

问题解析

insert语句的锁为什么这么多？

MySQL对自增主键锁做了优化，尽量在申请到自增id以后，就释放自增锁。

因此，大部分insert语句是一个很轻量的操作。

不过，也有些insert语句在执行过程中需要给其他资源加锁，或者无法在申请到自增id以后就立马释放自增锁。

如，insert … select 语句在可重复读隔离级别下，binlog_format=statement时执行SQL语句时，需要对表所有行和间隙加锁。

保证并发insert时日志和数据的一致性。

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

但如果没有锁的话，就可能出现session B的insert语句先执行，但是后写入binlog的情况。

CREATE TABLE t̀  ̀(  ìd  ̀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 tinsert into t2(c,d) select c,d from t;insert into t values(-1,-1,-1);insert into t2(c,d) select c,d from t;
```这个语句到了备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。

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

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

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

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

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

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

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

那么，如果我们是要把这样的一行数据插入到表t中，语句的执行流程是怎样的？扫描行数又是多少呢？这时候，我们再看慢查询日志就会发现不对了。

这时候的Rows_examined的值是5。

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);explain结果，从Extra字段可以看到“Using temporary”字样，表示这个语句用到了临时表。

也就是说，执行过程中，需要把表t的内容读出来，写入临时表。

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

那么，这个语句的扫描行数就应该是2，而不是5。

所以，这个猜测不对。

实际上，Explain结果里的rows=1是因为受到了limit 1 的影响。

从另一个角度考虑的话，我们可以看看InnoDB扫描了多少行。

如图5所示，是在执行这个语句前后查看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。

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

使用内存临时表优化时，语句序列的写法如下：

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

对于有唯一键的表，插入数据时出现唯一键冲突也是常见的情况了。

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;

唯一键冲突加锁这个例子也是在可重复读（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几乎同时发现死锁并返回。
这个死锁产生的逻辑是这样的：

在T1时刻，启动session A，并执行insert语句，此时在索引c的c=5上加了记录锁。

注意，这个索引是唯一索引，因此退化为记录锁。

在T2时刻，session B要执行相同的insert语句，发现了唯一键冲突，加上读锁；同样 ，session C也在索引c上，c=5这一个记录上，加了读锁。

T3时刻，session A回滚。


这时候，session B和session C都试图继续执行插入操作，都要加上写锁。
两个session都要等待对方的行锁，所以就出现了死锁。
这个流程的状态变化图如下所示。
图8 状态变化图–死锁insert into … on duplicate key update语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。
insert into t values(11,10,10) on duplicate key update d=100; 如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。
可以看到，主键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锁)。
因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

mysql-自增主键为什么不是连续的

发表于 2019-12-13 分类于 mysql

问题解析

自增主键为什么不是连续的？在第4篇文章中，我们提到过自增主键，由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。

之前我见过有的业务设计依赖于自增主键的连续性，也就是说，这个设计假设自增主键是连续的。

但实际上，这样的假设是错的，因为自增主键不能保证连续递增。

今天这篇文章，我们就来说说这个问题，看看什么情况下自增主键会出现 “空洞”？为了便于说明，我们创建一个表t，其中id是自增主键字段、c是唯一索引。

自增值保存在哪儿？在这个空表t里面执行insert into t values(null, 1, 1);插入一行数据，再执行show create table命CREATE TABLE t̀ ̀( ìd ̀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;令，就可以看到如下图所示的结果：

图1 自动生成的AUTO_INCREMENT值可以看到，表定义里面出现了一个AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成id=2。

其实，这个输出结果容易引起这样的误解：自增值是保存在表结构定义里的。

实际上，表的结构定义存放在后缀名为.frm的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同。

MyISAM引擎的自增值保存在数据文件中。

InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：

在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。

每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。

举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。

这时候，我们删除id=10的行，AUTO_INCREMENT还是11。

但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。

也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。

在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

理解了MySQL对自增值的保存策略以后，我们再看看自增值修改机制。

自增值修改机制在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段；
如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。

假设，某次要插入的值是X，当前的自增值是Y。

如果X<Y，那么这个表的自增值不变；
如果X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法是：从auto_increment_offset开始，以auto_increment_increment为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值。

其中，auto_increment_offset 和 auto_increment_increment是两个系统参数，分别用来表示自增的初始值和步长，默认值都是1。

当auto_increment_offset和auto_increment_increment都是1的时候，新的自增值生成逻辑很简单，就是：

如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；
否则，自增值不变。

这就引入了我们文章开头提到的问题，在这两个参数都设置为1的时候，自增主键id却不能保证是连续的，这是什么原因呢？自增值的修改时机要回答这个问题，我们就要看一下自增值的修改时机。

假设，表t里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

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

备注：在一些场景下，使用的就不全是默认值。

比如，双M的主备结构里要求双写的时候，我们就可能会设置成auto_increment_increment=2，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突。

insert into t values(null, 1, 1); 1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1);2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；

将传入的行的值改成(2,1,1);4. 将表的自增值改成3；
继续执行插入数据操作，由于已经存在c=1的记录，所以报Duplicate key error，语句返回。

对应的执行流程图如下：

图2 insert(null, 1,1)唯一键冲突可以看到，这个表的自增值改成3，是在真正执行插入数据的操作之前。

这个语句真正执行的时候，因为碰到唯一键c冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。

所以，在这之后，再插入新的数据行时，拿到的自增id就是3。

也就是说，出现了自增主键不连续的情况。

如图3所示就是完整的演示结果。

图3 一个自增主键id不连续的复现步骤可以看到，这个操作序列复现了一个自增主键id不连续的现场(没有id=2的行）。

可见，唯一键冲突是导致自增主键id不连续的第一种原因。

同样地，事务回滚也会产生类似的现象，这就是第二种原因。

下面这个语句序列就可以构造不连续的自增id，你可以自己验证一下。

你可能会问，为什么在出现唯一键冲突或者回滚的时候，MySQL没有把表t的自增值改回去呢？如果把表t的当前自增值从3改回2，再插入新数据的时候，不就可以生成id=2的一行数据了吗？其实，MySQL这么设计是为了提升性能。

接下来，我就跟你分析一下这个设计思路，看看自增值为什么不能回退。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定要加锁，然后顺序申请。

假设事务A申请到了id=2，事务B申请到id=3，那么这时候表t的自增值是4，之后继续执行。
事务B正确提交了，但事务A出现了唯一键冲突。
如果允许事务A把自增id回退，也就是把表t的当前自增值改回2，那么就会出现这样的情况：表里面已经有id=3的行，而当前的自增id值是2。
接下来，继续执行的其他事务就会申请到id=2，然后再申请到id=3。

这时，就会出现插入语句报错“主键冲突”。

而为了解决这个主键冲突，有两种方法：

每次申请id之前，先判断表里面是否已经存在这个id。

如果存在，就跳过这个id。

但是，这个方法的成本很高。

因为，本来申请id是一个很快的操作，现在还要再去主键索引树上判断id是否存在。

把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增id。

这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

可见，这两个方法都会导致性能问题。

造成这些麻烦的罪魁祸首，就是我们假设的这个“允许自增id回退”的前提导致的。

因此，InnoDB放弃了这个设计，语句执行失败也不回退自增id。

也正是因为这样，所以才只保证了自增id是递增的，但不保证是连续的。

insert into t values(null,1,1);begin;insert into t values(null,2,2);rollback;insert into t values(null,2,2);//插入的行是(3,2,2)自增锁的优化可以看到，自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

其实，在MySQL 5.1版本之前，并不是这样的。

接下来，我会先给你介绍下自增锁设计的历史，这样有助于你分析接下来的一个问题。

在MySQL 5.0版本的时候，自增锁的范围是语句级别。

也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放。

显然，这样设计会影响并发度。

MySQL 5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值是1。

这个参数的值被设置为0时，表示采用之前MySQL 5.0版本的策略，即语句执行结束后才释放锁；
这个参数的值被设置为1时：

普通insert语句，自增锁在申请之后就马上释放；

类似insert … select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

这个参数的值被设置为2时，所有的申请自增主键的动作都是申请后就释放锁。

你一定有两个疑问：为什么默认设置下，insert … select 要使用语句级的锁？为什么这个参数的默认值不是2？答案是，这么设计还是为了数据的一致性。

我们一起来看一下这个场景：

图4 批量插入数据的自增锁在这个例子里，我往表t1中插入了4行数据，然后创建了一个相同结构的表t2，然后两个session同时执行向表t2中插入数据的操作。

你可以设想一下，如果session B是申请了自增值以后马上就释放自增锁，那么就可能出现这样的情况：

session B先插入了两个记录，(1,1,1)、(2,2,2)；

然后，session A来申请自增id得到id=3，插入了（3,5,5)；

之后，session B继续执行，插入两条记录(4,3,3)、 (5,4,4)。

你可能会说，这也没关系吧，毕竟session B的语义本身就没有要求表t2的所有行的数据都跟session A相同。

是的，从数据逻辑上看是对的。

但是，如果我们现在的binlog_format=statement，你可以设想下，binlog会怎么记录呢？由于两个session是同时执行插入数据命令的，所以binlog里面对表t2的更新日志只有两种情况：

要么先记session A的，要么先记session B的。

但不论是哪一种，这个binlog拿去从库执行，或者用来恢复临时实例，备库和临时实例里面，session B这个语句执行出来，生成的结果里面，id都是连续的。

这时，这个库就发生了数据不一致。

你可以分析一下，出现这个问题的原因是什么？其实，这是因为原库session B的insert语句，生成的id不连续。

这个不连续的id，用statement格式的binlog来串行执行，是执行不出来的。

而要解决这个问题，有两种思路：

一种思路是，让原库的批量插入数据语句，固定生成连续的id值。

所以，自增锁直到语句执行结束才释放，就是为了达到这个目的。

另一种思路是，在binlog里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖于自增主键去生成。

这种情况，其实就是innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row。

因此，在生产上，尤其是有insert … select这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置：innodb_autoinc_lock_mode=2 ，并且binlog_format=row.这样做，既能提升并发性，又不会出现数据一致性问题。

需要注意的是，我这里说的批量插入数据，包含的语句类型是insert … select、replace …select和load data语句。

但是，在普通的insert语句里面包含多个value值的情况下，即使innodb_autoinc_lock_mode设置为1，也不会等语句执行完成才释放锁。

因为这类语句在申请自增id的时候，是可以精确计算出需要多少个id的，然后一次性申请，申请完成后锁就可以释放了。

也就是说，批量插入数据的语句，之所以需要这么设置，是因为“不知道要预先申请多少个id”。

既然预先不知道要申请多少个自增id，那么一种直接的想法就是需要一个时申请一个。

但如果一个select … insert语句要插入10万行数据，按照这个逻辑的话就要申请10万次。

显然，这种申请自增id的策略，在大批量插入数据的情况下，不但速度慢，还会影响并发插入的性能。

因此，对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

语句执行过程中，第一次申请自增id，会分配1个；
1个用完以后，这个语句第二次申请自增id，会分配2个；
2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。

举个例子，我们一起看看下面的这个语句序列：

insert…select，实际上往表t2中插入了4行数据。

但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7。

由于这条语句实际只用上了4个id，所以id=5到id=7就被浪费掉了。

之后，再执行insert into t2values(null, 5,5)，实际上插入的数据就是（8,5,5)。

这是主键id出现自增id不连续的第三种原因。

小结今天，我们从“自增主键为什么会出现不连续的值”这个问题开始，首先讨论了自增值的存储。

在MyISAM引擎里面，自增值是被写在数据文件上的。

而在InnoDB中，自增值是被记录在内存的。

MySQL直到8.0版本，才给InnoDB表的自增值加上了持久化的能力，确保重启前后一个表的自增值不变。

然后，我和你分享了在一个语句执行过程中，自增值改变的时机，分析了为什么MySQL在事务回滚的时候不能回收自增id。

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;insert into t2(c,d) select c,d from t;insert into t2 values(null, 5,5);MySQL 5.1.22版本开始引入的参数innodb_autoinc_lock_mode，控制了自增值申请时的锁范围。

从并发性能的角度考虑，我建议你将其设置为2，同时将binlog_format设置为row。

我在前面的文章中其实多次提到，binlog_format设置为row，是很有必要的。

今天的例子给这个结论多了一个理由。

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

在最后一个例子中，执行insert into t2(c,d) select c,d from t;这个语句的时候，如果隔离级别是可重复读（repeatable read），binlog_format=statement。

这个语句会对表t的所有记录和间隙加锁。

你觉得为什么需要这么做呢？你可以把你的思考和分析写在评论区，我会在下一篇文章和你讨论这个问题。

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

上期问题时间上期的问题是，如果你维护的MySQL系统里有内存表，怎么避免内存表突然丢数据，然后导致主备同步停止的情况。

我们假设的是主库暂时不能修改引擎，那么就把备库的内存表引擎先都改成InnoDB。

对于每个内存表，执行这样就能避免备库重启的时候，数据丢失的问题。

由于主库重启后，会往binlog里面写“delete from tbl_name”，这个命令传到备库，备库的同名的表数据也会被清空。

因此，就不会出现主备同步停止的问题。

如果由于主库异常重启，触发了HA，这时候我们之前修改过引擎的备库变成了主库。

而原来的主库变成了新备库，在新备库上把所有的内存表（这时候表里没数据）都改成InnoDB表。

所以，如果我们不能直接修改主库上的表引擎，可以配置一个自动巡检的工具，在备库上发现内存表就把引擎改了。

同时，跟业务开发同学约定好建表规则，避免创建新的内存表。

set sql_log_bin=off;alter table tbl_name engine=innodb;

mysql-都说InnoDB好那还要不要使用Memory引擎

发表于 2019-12-12 分类于 mysql

问题解析

都说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）。

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

InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引；
InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。

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

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索引。

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

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

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

锁粒度问题；
数据持久化问题。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

业务正常访问主库；
备库硬件升级，备库重启，内存表t1内容被清空；
备库重启后，客户端发送一条update语句，修改表t1的数据行，这时备库应用线程就会报错“找不到要更新的行”。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

如果你的表更新量大，那么并发度是一个很重要的参考指标，InnoDB支持行锁，并发度比内存表好；
能放到内存表的数据量都不大。

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

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

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

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

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

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

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

临时表不会被其他线程访问，没有并发性的问题；
临时表重启后也是需要删除的，清空数据这个问题不存在；
备库的临时表也不会影响主库的用户线程。

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

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

相比于InnoDB表，使用内存表不需要写磁盘，往表temp_t的写数据的速度更快；
索引b使用hash索引，查找的速度比B-Tree索引快；
临时表数据只有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引擎表。

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

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

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

mysql-内部临时表

发表于 2019-12-11 分类于 mysql

问题解析

什么时候会使用内部临时表？今天是大年初二，在开始我们今天的学习之前，我要先和你道一声春节快乐！在第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)。

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

创建一个内存临时表，这个临时表只有一个整型字段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这个值，并存入临时表中。

执行第二个子查询：

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

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

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

从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是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，表示需要排序。

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

创建内存临时表，表里有两个字段m和c，主键是m；
扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；

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

遍历完成后，再根据字段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+树存储，存储效率不如数组来得高。

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

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

初始化sort_buffer，确定放入一个整型字段，记为m；
扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；
扫描完成后，对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. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。

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

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

如果对group by语句的结果没有排序要求，要在语句后面加 order by null；
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Usingfilesort；
如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

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

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

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

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

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

mysql-为什么临时表可以重名

发表于 2019-12-10 分类于 mysql

问题解析

为什么临时表可以重名？在上一篇文章中，我们在优化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 临时表特性示例可以看到，临时表在使用上有以下几个特点：

建表语法是create temporary table …。
一个临时表只能被创建它的session访问，对其他线程不可见。

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

临时表可以与普通表同名。
session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
show tables命令不显示临时表。

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

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

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

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

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

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

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

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

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

比如。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

我们举例的这条语句还算是比较简单的，如果涉及到复杂的操作，比如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：

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，那剩下的部分就固定了。

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

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

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

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

mysql-join语句怎么优化

发表于 2019-12-09 分类于 mysql

问题解析

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优化的设计思路。

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

根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;2. 将read_rnd_buffer中的id进行递增排序；
排序后的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算法对系统的影响主要包括三个方面：

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

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

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

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

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

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

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

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

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

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

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

扫描表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亿次，想想也是浪费。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

实现流程大致如下：

select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构，比如C++里的set、PHP的dict这样的数据结构。
select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。
把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据。

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

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

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

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

在这些优化方法中：

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

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

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

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

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

mysql-使用join

发表于 2019-12-08 分类于 mysql

问题解析

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

我们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 ̀( ì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过程用上了这个索引，因此这个语句的执行流程是这样的：

从表t1中读入一行数据 R；
从数据行R中，取出a字段到表t2里去查找；
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
重复执行步骤1到3，直到表t1的末尾循环结束。

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

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

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

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

对驱动表t1做了全表扫描，这个过程需要扫描100行；
而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。

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

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

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

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

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

执行select * from t1，查出表t1的所有数据，这里有100行；
循环遍历这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对扫描行数的影响更大，因此应该让小表来做驱动表。

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

使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
如果使用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这时候，被驱动表上没有可用的索引，算法的流程是这样的：

把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
扫描表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，那么在这个算法里：

两个表都做一次全表扫描，所以总的扫描行数是M+N；
内存中的判断次数是M*N。

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

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

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

我把join_buffer_size改成1200，再执行：

执行过程就变成了：

扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
清空join_buffer；
继续扫描表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)。

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

扫描行数是 N+λNM；
内存判断 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算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

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

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

所以这种join尽量不要用。

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

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

如果是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两个算法执行过程的分析，我们也得到了文章开头两个问题的答案：

如果可以使用被驱动表的索引，join语句还是有其优势的；
不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；
在使用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不能被回收，导致回滚段空间膨胀。