文章目录
1 insert…from的问题
insert … select 是很常见的在两个表之间拷贝数据的方法。需要注意，在可重复读隔离级别下，这个语句会给
select的表里扫描到的记录和间隙加读锁。
以下对insert…select 进行一下测试
- 全表读或主键排序读
sessionA
mysql> insert into trajectory_min_section_0511_copy select * from trajectory_min_section_0511;
sessionB
mysql> show engine innodb status;
1451 lock struct(s), heap size 155856, 121231 row lock(s), undo log entries 119783
mysql> show engine innodb status;
3088 lock struct(s), heap size 303312, 258340 row lock(s), undo log entries 255254
mysql> show engine innodb status;
15004 lock struct(s), heap size 1466576, 1256305 row lock(s), undo log entries 1241304
终止sessionA后
sessionB
mysql> show engine innodb status;
ROLLING BACK 23737 lock struct(s), heap size 2302160, 1987628 row lock(s), undo log entries 471818
mysql> show engine innodb status;
ROLLING BACK 23737 lock struct(s), heap size 2302160, 1987628 row lock(s), undo log entries 20019
通过测试可以看到row locks是一个慢慢增长的过程。undo log entries也在一直增长，这个的作用是为了rollback恢复。
用主键升序插入以及用主键降序插入:
select * from trajectory_min_section_0511 order id(PK) ASC
select * from trajectory_min_section_0511 order id(PK) DESC
也是一样的效果，感兴趣的可以测试一下。
从上面测试可知：通过主键排序或则不加排序字段的导入操作"insert into A select * from B"，是会锁B表，但他的锁是逐步地锁定已经扫描过的记录。
当终止后可以看到undo log entries数量慢慢降下去
- 非主键排序读
sessionA
mysql> insert into trajectory_min_section_0511_copy select * from trajectory_min_section_0511 order by t_distance desc;
sessionB
sessionA执行过程中执行n次
mysql> show engine innodb status;
63595 lock struct(s), heap size 6168784, 5325513 row lock(s), undo log entries 5118624
从上面测试可知：非主键排序的导入操作，是会锁表，而且糟糕的是，锁是一开始就会锁定整张表。
- 读写验证
sessionA
mysql> insert into trajectory_min_section_0511_copy select * from trajectory_min_section_0511;
sessionB
mysql> select * from trajectory_min_section_0511 where id=1715
搜索靠前主键数据可以正常返回
mysql> update trajectory_min_section_0511 set direction=2 where id=1715
更新靠前主键数据超时无法返回
sessionA
mysql> insert into trajectory_min_section_0511_copy select * from trajectory_min_section_0511 order by id desc;
sessionB
mysql> select * from trajectory_min_section_0511 where id=9999
搜索靠后主键数据可以正常返回
mysql> update trajectory_min_section_0511 set direction=2 where id=1715
更新靠后主键数据超时无法返回
以上测试在上锁过程中，不能dml操作任何被上锁的行，直到锁释放。
如果已知对源表的扫描行数和加锁范围很小的话，简单地使用insert … select 语句即可实现。如果对线上重点表迁移，为了避免对源表加读锁，更稳妥
的方案是先将数据写到外部文本文件，然后再写回目标表。
2 高效加载数据
提高写入、加载速度的几个原则，这些原则在任何数据库上都是通用的：
-
把数据从缓存刷新到磁盘次数越少，数据加载的越快。因此，批量加载一定是比单条加载效率更高，因为批量插入的行可以先行缓存，然后在
加载操作时候一次性刷到磁盘上，减少磁盘的随机读操作。 -
表的索引越少，加载速度越快，如果表有多个列存在索引，每次插入都需要更新所有索引完后才会识别到新的行加入。所以不要建无谓的索引。
-
短sql比长sql加载速度更快，因为在服务器上解析操作耗时更少，并且可以更快的通过网络发送到服务器。
加载数据一般有insert和load两种，接下来详细解读一下底层实现的不同。
2.1 load主备同步流程
-
主库执行完成后，将/root/table.txt文件的内容直接写到一个外部文件中。
-
往binlog文件中写入语句load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE
db2.t。
读取本地文件 -
把这个binlog日志传到备库。
-
备库的apply线程在执行这个事务日志时：
先将binlog中t.csv文件的内容读出来，写入到本地临时目录/tmp/SQL_LOAD_MB-1-0 中；
再执行load data语句，往备库的db2.t表中插入跟主库相同的数据。
2.2 总结
总的来说，根据官方介绍load可以快出insert 20多倍
load 的底层实现的优化：
1 跳过sql解析，直接生成数据文件;
2 在导入之前会关掉索引，导入完成后更新索引；而与之对比的Insert的处理机制是:每插入一条则更新一次数据库,更新一次索引。
官网的一些解答
[insert-optimization]{https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html}
4 数据拷贝方法
介绍三种mysql数据拷贝的主流方法，分别是mysqldump(sql语句拷贝)、load(csv文件拷贝)、以及物理文件拷贝。
4.1 mysql dump
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
主要参数含义如下：
–single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
–add-locks设置为0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；
–no-create-info的意思是，不需要导出表结构；
–set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
–result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。
通过这条mysqldump命令生成的t.sql文件中就包含了如图1所示的INSERT语句。
可以看到一个insert中包含多个value对，这样插入执行速度可以加快。
这也是navicat 上 data transfer功能
4.2 load
show variables like ‘%secure_file_priv%’;
如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；
如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；
如果设置为NULL，就表示禁止在这个MySQL实例上执行select … into outfile 操作。
通过修改配置文件永久生效
设置后可以通过命令导出
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
导出的csv文件可以用如下命令导入
load data infile '/server_tmp/t.csv' into table db2.t;
拷贝到数据库本地后，可以用本地导入，比远程导入更快，因为省去了在load这个事务中网络传输，减轻数据库压力。
load data local infile '/server_tmp/t.csv' into table db2.t;
4.3 物理拷贝
在掌握了逻辑拷贝的方法后，是否有物理导数据的方法呢？比如，直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下，是否可行呢？
答案是不行的。
因为，一个InnoDB表，除了包含这两个物理文件外，还需要在数据字典中注册。直接拷贝这两个文件的话，因为数据字典中没有db2.t这个表，系统是不会识别和接受它们的。
不过，在MySQL 5.6版本引入了可传输表空间(transportable tablespace)的方法，可以通过导出+导入表空间的方式，实现物理拷贝表的功能。
假设我们现在的目标是在db1库下，复制一个跟表trajectory_min_section_0511相同的表trajectory_min_section_0511_copy，具体的执行步骤如下：
– 源端执行
- flush table trajectory_min_section_0511 for export
这时候数据库目录下会生成一个trajectory_min_section_0511.cfg文件； - cp trajectory_min_section_0511.ibd trajectory_min_section_0511_copy.ibd；
在db1目录下执行cp trajectory_min_section_0511.cfg trajectory_min_section_0511_copy.cfg; 这两个命令（
这里需要注意的是，拷贝得到的两个文件，MySQL进程要有读写权限）； - unlock tables
trajectory_min_section_0511.cfg文件会被删除；
– 目标端执行
- create table trajectory_min_section_0511_copy like trajectory_min_section_0511;
首先创建一个相同表结构的空表 - alter table trajectory_min_section_0511_copy discard tablespace;
这时候trajectory_min_section_0511_copy.ibd文件会被删除 - alter table trajectory_min_section_0511_copy import tablespace
将这个trajectory_min_section_0511_copy.ibd文件作为表trajectory_min_section_0511_copy的新的表空间，
由于这个文件的数据内容和trajectory_min_section_0511.ibd是相同的，所以表trajectory_min_section_0511_copy中
就有了和表trajectory_min_section_0511相同的数据。
测试后最后一步导入500w耗时10s左右，其他操作都是立刻执行
几点注意：
-
在第3步执行完flush table命令之后，整个表trajectory_min_section_0511处于只读状态，直到执行unlock tables命令后才释放读锁；
-
在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改trajectory_min_section_0511_copy.ibd的表空间id。
而这个表空间id存在于每一个数据页中。因此，如果是一个很大的文件（比如TB级别），每个数据页都需要修改，import语句的执行是需要一些时间的。
当然，如果是相比于逻辑导入的方法，import语句的耗时是非常短的。
4.4 三种方式对比
对比一下这三种方法的优缺点。
-
物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性：
a) 必须是全表拷贝，不能只拷贝部分数据；
b) 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；
c) 由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。 -
用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。
-
用select … into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。
后两种方式都是逻辑备份方式，是可以跨引擎使用的。
5 当数据量很大时
抛开最后一种物理拷贝，因为在线上时不一定可以登录数据库主机，而且也可能只拷贝部分数据。那一般就是使用load data infile方式导入。
当数据量极大(10+G,千万级别)时，用之前介绍的方式会有很大问题。
- load为一个长事务，最后commit后才插入数据库。
- undo增长快速，无法回收。数据库性能下降，undo大小大于buffer pool，就会开始内存和磁盘的交换。
可以大文件拆小，监控cpu后，在合理的利用率情况下，多线程load。
在线上我写了一个脚本，适当修改即可使用，思路是使用split来拆分文件，parallel来异步load。脚本如下所示，运行时监控数据库cpu，基本上可以跑满cpu的情况下，会带来很大的性能优势。
6 扩展知识点
针对并发场景的一致性问题，第一个能想到的是加锁，但是数据库中所有操作都上锁势必会带来性能的低下，mysql的隔离级别(isolation level)最高级叫串行化，
其设计上可以认为就是加一把大锁，读的时候加共享锁，不能写，写的时候，加的是排它锁，阻塞其它事务的写入和读取，若是其它的事务长时间不能写入就会直接报超时，所以它的性能也是最差的，对于它来就没有什么并发性可言。
在InnoDB 的读提交和可重复读两种级别都使用了多版本并发控制模型(MVCC)
比如在实现可重复读的隔离级别，只需要在事务开始的时候创建一致性视图，也叫做快照，之后的查询里都共用这个一致性视图，后续的事务对数据的更改
是对当前事务是不可见的，这样就实现了可重复读。 中每一个事务都有一个自己的事务id，并且是唯一的，递增的 。
-
最开始数据的版本是V0；
-
T1时刻发起了一个写任务，这是把数据clone了一份，进行修改，版本变为V1，但任务还未完成；
-
T2时刻并发了一个读任务，依然可以读V0版本的数据；
-
T3时刻又并发了一个读任务，依然不会阻塞；
对于Mysql中的每一个数据行都有可能存在多个版本，在每次事务更新数据的时候，都会生成一个新的数据版本，并且把自己的数据id赋值给当前版本的row trx_id。
如图中所示，假如三个事务更新了同一行数据，那么就会有对应的三个数据版本。
实际上版本1、版本2并非实际物理存在的，而图中的U1和U2实际就是undo log，这v1和v2版本是根据当前v3和undo log计算出来的。
当出现事务回滚的时候，通过undo log反向重现redo log的过程，就可以将当前数据回退回事务开始前数据状态。
当事务提交后undo块就会慢慢的回收。要避免长事务，因为如果长事务中有更新，就意味着占用着行锁，导致别的语句更新被锁。还有读的事务会导致undo log不能回收，导致回滚段空间膨胀。
扩展阅读 万字长文，帮你梳理存储引擎之Heap表关键知识点
2683
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
