批量insert优化

insert … select 语句

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

insert 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;

insert 循环写入

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);

1. 创建临时表，表里有两个字段c和d。

2. 按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表。这时，Rows_examined=4。

3. 由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中。这时，Rows_examined的值加1，变成了5。

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;

insert 唯一键冲突

备注：这个bug，是我在写这篇文章查阅文档时发现的，已经发给官方并被verified了。

1. 在T1时刻，启动session A，并执行insert语句，此时在索引c的c=5上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁（加锁规则）。

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

3. T3时刻，session A回滚。这时候，session B和session C都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁。

insert into … on duplicate key update

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

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

mysqldump方法

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表示生成的文件是在客户端机器上的。

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

1. 打开文件，默认以分号为结尾读取一条条的SQL语句；

2. 将SQL语句发送到服务端执行。

导出CSV文件

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. 这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开。

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整个文件读入完成，提交事务。

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表中插入跟主库相同的数据。

1. 不加“local”，是读取服务端的文件，这个文件必须在secure_file_priv指定的目录或子目录下；

2. 加上“local”，读取的是客户端的文件，只要mysql客户端有访问这个文件的权限即可。这时候，MySQL客户端会先把本地文件传给服务端，然后执行上述的load data流程。

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

物理拷贝方法

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 tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

1. 在第3步执行完flsuh 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写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

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

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