骏马金龙 (新博客：www.junmajinlong.com)

网名骏马金龙，钟情于IT世界里的各种原理和实现机制，强迫症重症患者。爱研究、爱翻译、爱分享。特借此一亩三分田记录自己成长点滴！！！

(MariaDB/MySQL)之DML(1)：数据插入

1.insert和replace插入数据

先解释insert。

insert插入数据有三种语法，简洁版如下：

1.使用values()或value()：insert into table_name values(),(),(),()...

2.使用set子句：insert into table_name set column_name=value,...

3.使用select子句：insert into table_name select_statement

第二种语法是MySQL/MariaDB对标准SQL insert语句的扩展。

1.1 insert into values()

给定如下表结构：

create or replace table t(
    id int primary key,
    sex char(3) default('nv'),
    name char(20)
);

其中字段'sex'含有默认值属性，其默认值为'nv'，name字段允许NULL，这也是该字段的默认值。

用insert插入几行数据：

insert into t values(1,'nan','longshuai1'); # 插入一行数据
insert into t values(2,'nan','longshuai2'),(3,'nv','xiaofang1'); # 一次性插入两行数据
insert into t values(4,DEFAULT,'xiaofang2'); # sex字段使用默认值
insert into t(id,name) values(5,'xiaofang3'); # 指定插入的字段
insert into t(id,sex,name) values(6,'nv','xiaofang4'); # 等价于insert into t values()
insert into t(name,id) values('xiaofang5',7); # 指定插入的字段，且改变字段顺序
insert into t value(8,'nan','longshuai3'); # 使用value，而非values

查看插入的数据：

MariaDB [test]> select * from t;
+----+------+------------+
| id | sex  | name       |
+----+------+------------+
|  1 | nan  | longshuai1 |
|  2 | nan  | longshuai2 |
|  3 | nv   | xiaofang1  |
|  4 | nv   | xiaofang2  |
|  5 | nv   | xiaofang3  |
|  6 | nv   | xiaofang4  |
|  7 | nv   | xiaofang5  |
|  8 | nan  | longshuai3 |
+----+------+------------+

注意，MySQL/MariaDB中默认变量autocommit=1，这意味着InnoDB表每次insert(以及其他的DML语句)都会自动提交事务，提交事务意味着会将脏数据刷到磁盘，这很影响插入效率。

insert into t values(),(),()一次插入多个值的效率比分多次执行insert效率要高得多。不仅不用频繁地打开、关闭连接，还能将要插入的数据缓存起来，一定时间之后再一次性刷到磁盘。

关于如何快速插入数据到MariaDB/MySQL中，见后文：如何快速插入大量数据。

1.2 insert into set

insert into set语法是使用set子句为每个字段设置值。所以，字段的顺序没有任何要求。

例如：

insert into t set name='longshuai4',id=9,sex='nan';

1.3 insert into select_statement

insert into tbl_name select_statement语法是从其他表中检索数据，并将检索到的数据插入到表tbl_name中。要求tbl_name必须已经存在。

例如：

insert into t select 10,'nan','longshuai5';
insert into t select 11,'nan','longshuai6' from dual;
insert into t select 12,'nv','xiaofang6' union select 13,'nan','longshuai7';

如果从其他表中检索数据，则select检索的字段需要和tbl_name表的字段对应。

create or replace table tx(user char(20),host char(20),password char(50));
insert into tx select user,host,password from mysql.user;

如果表tbl_name不存在，则insert into tbl_name select_statement语句会失败。此时可以使用create table select的方式来创建表的同时插入数据。

1.4 create table select

创建表并插入数据两种语法：

create table tbl_name select_statement
create table tbl_name as select_statement

这些语句检索数据，并按照检索目标字段新建一张表，表必须不能已经存在，除非使用or replace或者if not exists子句。

# 创建新表，并插入几条数据
create or replace table t20 select user,host,password from mysql.user where user='root';

只创建表结构，不插入数据：

create table tbl_name1 like tbl_name2      # 创建完全相同的表结构
create table tbl_name1 select col1,col2,col3 from tbl_name2 where 1=0;  # where false。可以筛选部分字段作为新表的结构

例如：

create table t10 like mysql.user;   # 以mysql.user表为模板创建t10表
create table t11 select user,host,password from mysql.user where false; # 选出3个字段创建新表
create table t12(col1 char(20),col2 char(20),col3 char(50)) as  # 选出3个字段，但自定义新表的字段名称
       select user,host,password from mysql.user where 1=0;

需要注意：

create table [as] select_statement创建表的时候，只会创建几个字段并插入一些数据，不会复制模板表字段的属性，例如索引、默认值、auto_increment等。

create table like创建表的时候，新表和模板表的结构会完全相同，包括字段的属性(如default属性、auto_increment属性、索引等)。实际上，它是根据模板表的".frm"文件新建的。

1.5 处理键值重复的问题

当表中存在唯一性索引(例如primary key，unique index)时，插入的记录如果键值重复，则插入操作会失败。MariaDB中有三种方法解决重复值冲突的问题：

  1. 使用ignore关键字忽略所有错误行，使insert操作继续插入后面的数据。
  2. 使用insert ... on duplicate key update，将有重复值的行update为新的值。
  3. 使用replace into语句替代insert into语句，将有重复值的行替换为新行。

例如，创建带有主键的表，并插入几行数据。

create or replace table t(id int primary key,sex char(3),name char(20));

insert into t values
(1,'nan','longshuai1'),(2,'nan','longshuai2'),(3,'nv','xiaofang1'),
(4,'nv','xiaofang2'),(5,'nv','xiaofang3'),(6,'nv','xiaofang4'),
(7,'nv','xiaofang5'),(8,'nan','longshuai3'),(9,'nan','longshuai4');

当插入id=5的新记录，默认会报错，如果是多值插入，所有的插入都会失败。

insert into t values(5,'nv','xiaofang33'),(10,'nan','longshuai5');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

select * from t where id=5 or id=10;
+----+------+-----------+
| id | sex  | name      |
+----+------+-----------+
|  5 | nv   | xiaofang3 |
+----+------+-----------+

以下是三种解决重复值冲突的方法。

1.ignore关键字，忽略将要重复的记录。

完整语法见下文。此处只给使用示例。

insert ignore into t values(5,'nv','xiaofang33'),(10,'nan','longshuai5');
Query OK, 1 row affected, 1 warning (0.003 sec)
Records: 2  Duplicates: 1  Warnings: 1

从返回中可以看到：1 row affected, 1 warning。说明只成功插入了一行记录，id=5的重复记录被忽略直接跳过了。

show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '5' for key 'PRIMARY' |
+---------+------+---------------------------------------+

select * from t where id=5 or id=10;
+----+------+------------+
| id | sex  | name       |
+----+------+------------+
|  5 | nv   | xiaofang3  |
| 10 | nan  | longshuai5 |
+----+------+------------+

2.ON DUPLICATE KEY UPDATE。

可以在INSERT语句后面加上on duplicate key update子句来修改重复值记录。

这有两种情况：(1)插入的记录没有键值重复冲突；(2)插入的记录有键值重复冲突。

对于第一种情况，INSERT语句中带有on duplicate key update子句并没有任何区别，它们都只是纯粹地insert数据。

对于第二种情况，INSERT语句中带有on duplicate key update子句会更新表中原有的记录。

例如：

# 插入无键值重复冲突的记录
insert into t values(11,'nv','xiaofang4'),(12,'nan','longshuai6');
 
# 插入键值重复冲突的记录id=5，更新其name
insert into t values(5,'nv','xiaofang33'),(13,'nv','xiaofang5') on duplicate key update name='xiaofang333';
Query OK, 3 rows affected (0.002 sec)
Records: 2  Duplicates: 1  Warnings: 0

3行受到影响，其中1行重复值。这里的3行记录中，有1行是插入id=13的记录，另两行是因为id=5重复而受到的影响，涉及的操作是将旧id=5行update为新id=5的行。

select * from t where id=5 or id=13;
+----+------+-------------+
| id | sex  | name        |
+----+------+-------------+
|  5 | nv   | xiaofang333 |
| 13 | nv   | xiaofang5   |
+----+------+-------------+

可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...ON DUPLICATE KEY UPDATE语句的INSERT部分引用列值。例如：

insert into t values(5,'nv','xiaofang33') on duplicate key update name=concat('xiaofang',values(id));

select * from t where id=5;
+----+------+-----------+
| id | sex  | name      |
+----+------+-----------+
|  5 | nv   | xiaofang5 |
+----+------+-----------+

VALUES()函数只在INSERT...ON DUPLICATE KEY UPDATE语句中有意义，其它时候会返回NULL。

INSERT...ON DUPLICATE KEY UPDATE语句的执行原理是：

(1).尝试插入新行，判断新行是否和表中已有记录存在键值冲突。此时会触发before insert触发器。

(2).如果没有冲突，就直接插入新行，此时会触发after insert触发器。

(3).如果有冲突，则更新旧行为新行。此时会先触发before update触发器，更新后触发after update触发器。

所以，当有重复值冲突的时候，将触发before insert、before update和after update触发器。详细内容见：on duplicate key update分析触发器触发原理。

3.replace into语句，更新重复值所在的记录行。

replace into和insert into语法完全一样，可以完全替代insert into语句。它们之间只有存在键值重复冲突的时候才有区别。

当没有键值重复冲突时，replace into和insert into完全等价。

当键值重复冲突时，replace into语句会将表中产生冲突的记录完全替换为新行。

replace into t values(5,'nv','xiaofang33');

select * from t where id=5;
+----+------+------------+
| id | sex  | name       |
+----+------+------------+
|  5 | nv   | xiaofang33 |
+----+------+------------+

replace into语句的执行原理是：

(1).尝试插入新行，判断新行是否和表中已有记录存在键值冲突。此时会触发before insert触发器。

(2).如果没有冲突，就直接插入新行，此时会触发after insert触发器。

(3).如果有冲突，则删除旧行，并插入新行。此时会触发before delete触发器，删除后触发after delete触发器，最后插入数据后触发after insert触发器。详细内容见：replace into算法分析。

1.6 完整的insert语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
 [ ON DUPLICATE KEY UPDATE
   col=expr
     [, col=expr] ... ]
 
或：
 
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]
 
或：
 
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

其中on duplicate key update子句是在出现重复值冲突时使用的，前文已介绍。

IGNORE关键字的作用是忽略错误。当insert语句插入记录时，如果某行出现错误(例如不满足数据类型、重复值冲突等等)，则INSERT语句不会停止，而是继续插入后面的记录。前文已介绍通过ignore解决键值重复冲突的问题。

DELAYED关键字设置INSERT的异步插入。当使用该关键字时，服务端立即返回OK给客户端，然后服务端将插入操作放进队列，等待没有其他进程访问该表的时候才会将队列中的数据插入到表中。在表被频繁访问的时候插入数据，使用DELAYED的效率比直接使用INSERT高，但如果没有任何进程访问表的时候，INSERT DELAYED比直接INSERT速度慢，因为MariaDB需要额外的资源来维护这个队列。如果某一时刻有进程访问该表，则多个客户端同时使用INSERT DELAYED插入数据的时候，这些数据会全部放进队列，效率比各客户端分开插入要高，因为队列中的数据是成批插入的。如果队列中的数据还没插入到表中，突然mysqld进程死掉了，则队列中的数据会丢失，即没有插入到表中。

LOW_PRIORITY和HIGH_PRIORITY关键字的意义在见(MariaDB/MySQL)MyISAM存储引擎读、写操作的优先级。

2.导出、导入数据

load data infile和select into outfile语句是配套的。select into outfile语句是将检索出来的数据按格式导出到文件中，数据迁移跨数据库系统时，该选项很有用，因为它可以指定分隔符。load data infile是将带有格式的数据文件导入到表中。

导出、导入数据时需要指定格式(如不指定，则使用默认)。格式涉及几个方面：字段分隔符、行分隔符、引用符号、转义符号。

还需注意一点，默认情况下(MySQL 5.6.34之后)这两个语句无法执行成功，因为全局变量secure_file_priv的默认值为null，它表示禁用这两种语句的导入导出。

所以应该将其设置为空(不指定任何值)或者指定一个目录，将来该目录中的所有文件都可以进行mysql file类的交互。当然，变量指定的目录必须已经存在，且mysql系统用户和组必须对该目录有读写权限。

mkdir /data
chown -R mysql.mysql /data

这个变量是全局静态变量，只能在mysqld实例未启动的时候才能修改。所以将其写入配置文件。

[mysqld]
secure-file-priv=/data
# 或者
# secure-file-priv=

查看变量。

select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+

再看这两个语句的语法：

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
 
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
        [export_options]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]
 
 
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

其中'char'表示只能使用一个字符，'string'表示可以指定多个字符。

fields terminated by 'string'指定字段分隔符；enclosed by 'char'指定所有字段都使用char符号包围，如果指定了optionally则只用在字符串和日期数据类型等字段上，默认未指定；escaped by 'char'指定转义符。

lines starting by 'string'指定行开始符，如每行开始记录前空一个制表符；lines terminated by 'string'为行分隔符。

要注意，在几种情况下需要使用转义符：数据中含有转义符本身或者字段分隔符。当指定了字段引用符enclosed by时，如果数据中含有字段引用符，则也需要转义，若未指定enclosed by，则默认不使用字段引用符，所以无需转义。

以下为它们的默认值：

fileds terminated by '\t' enclosed by '' escaped by '\\'
lines terminated by '\n' starting by ''

看上去语法还挺复杂的，使用示例来说明就很清晰易懂了。

给定如下表结构和数据。

create or replace table t(id int primary key,sex char(3),name char(20),ins_day date);

insert into t values(1,'nan','longshuai1','2010-04-19'),
                    (2,'nan','longshuai2','2011-04-19'),
                    (3,'nv','xiaofang1','2012-04-19'),
                    (4,'nv','xiaofang2','2013-04-19'),
                    (5,'nv','xiaofang3','2014-04-19'),
                    (6,'nv','xiaofang4','2015-04-19'),
                    (7,'nv','tun\'er','2016-04-19'),
                    (8,'nan','longshuai3','2017-04-19');

2.1 select into outfile导出数据

使用默认设置：

select * from t into outfile '/data/t_data.sql';

\! cat /data/t_data.sql
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

指定字段分隔符","，使用单引号包围各字段，每行前加上制表符。

select * from t into outfile '/data/t_data1.sql' fields terminated by ',' enclosed by '\'' lines starting by '\t' terminated by '\n';

\! cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun\'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

2.2 load data infile导入数据

要导入格式化后的纯数据，可以使用load data infile，加载纯数据的插入方式比直接执行insert插入至少快20多倍。但在内部，它们其实是等价行为，load data infile也会触发insert相关触发器。

其中可以使用local关键字表示从客户端主机读取文件，如果没有指定local则表示从服务端主机读取文件。

fields和lines的相关选项和select ... into outfile是一样的，只不过load data infile多了几个选项。其中ignore N lines|rows表示忽略前N行数据不导入，col_name_or_user_var表示按此处给定的字段和顺序来导入数据，set col_name=expr表示对列进行一些表达式运算，如给某数值字段加5，给某字符串列尾部加上@qq.com字符等。

例如要加载如下文件到test.t表中。

cat /data/t_data.txt
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

首先删除表中数据，再导入。

truncate test. t;
load data infile '/data/t_data.sql' into table test.t fields terminated by '\t';

将如下包含字段分隔符","，字段引用符"'"，转义符"\"，行前缀"\t"的文件加载到test.t表中。

[root@xuexi ~]# cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun\'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

首先删除表中数据，然后加载。

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n';

若要忽略前两行，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' ignore 2 rows;

如果想在id列值加上5，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set id=id+5;

如果想name列后加上"@qq.com"字符串，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com');

如果想同时执行上面两个set，则：

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by '\'' escaped by '\\' lines starting by '\t' terminated by '\n' set name=concat(name,'@qq.com'), id=id+5;

2.3 mysqldump导出数据

和select into outfile功能类似的语句还有：此方法导出的数据中还包含了列名。

mysql -uroot -p123456 -e "select * from test.t">/tmp/t_data2.sql

cat /tmp/t_data2.sql
id      sex     name    ins_day
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

虽说select ... into outfile导出数据后可修改性和加载性非常强，但是毕竟没有导出结构。要导出结构，可以使用mysqldump的"--tab"选项，它既会导出表的结构定义语句到同表名的.sql文件中，还会导出数据到同表名的.txt文件中。

mysqldump -uroot -p123456 --tab /data test t;

ls -l /data/t.*
-rw-r--r-- 1 root  root  1408 Apr 19 14:46 /data/t.sql   # test.t表定义语句
-rw-rw-rw- 1 mysql mysql  211 Apr 19 14:46 /data/t.txt   # test.t表内数据

mysqldump的"--tab"选项同样可以指定各种分隔符。如"--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=..."。以下是指定字段分隔符为","。

mysqldump -uroot -p123456 --tab /data --fields-terminated-by=',' test t;

cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

2.4 mysqlimport导入数据

mysqlimport和load data infile的本质是一样的。mysqlimport在执行时会像服务端发送load data infile来加载数据，并且mysqlimport支持多进程并行导入多张表的数据。

mysqlimport的语法和load data infile基本一致。不同的是它在MySQL/MariaDB的外部执行，且可以一次性并行多线程导入多张表(并非并行导入一张表)，所以能更快地导入所有数据。

mysqlimport [OPTIONS] database textfile...

注意：mysqlimport只能指定数据库名来导入，所以导入的文件名必须和数据库中的表名相对应(文件名后缀无所谓)。例如文件名为stu2.sql，而表名为student则无法导入，它会找stu2这个表。

例如，将以下格式的文件t.txt使用mysqlimport导入到test.t表中：

[root@xuexi ~]# cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

[root@xuexi ~]# mysqlimport -uroot -p123456 --fields-terminated-by=',' test '/data/t.txt'

使用"--use-threads"选项可以指定导入线程数。

例如，下面指定两个线程，导入两张表到数据库test库中的t1和t2表中。

mysqlimport -uroot -p123456 --use-threads=2 --fields-terminated-by=',' test '/data/t1.txt' '/data/t2.txt'

3.如何快速插入大量数据

见我翻译的官方手册：https://mariadb.com/kb/zh-cn/how-to-quickly-insert-data-into-mariadb/

大致说几点：

(1).禁用非唯一性索引，以及约束检查。

对于插入海量数据操作而言，该行为带来的效率提升是最大的。

ALTER TABLE table_name DISABLE KEYS;

BEGIN;
   INSERT_statement
# or
   LOAD DATA INFILE...
COMMIT;

ALTER TABLE table_name ENABLE KEYS;

多数存储引擎(至少MyISAM和Aria是如此)的ENABLE KEYS会扫描表中的行并收集索引键值，然后对它们排序，最后创建索引块。因此，先DISABLE KEYS，插入数据后启用KEY的整体速度比每行都更新一次索引的速度至少要快一个数量级，并且所需要的buffer也更少。

一般来说，DISABLE KEYS操作的时间极快，insert操作消耗的时间则要长很多，但消耗的最长时间还是在ENABLE KEYS操作上。

需要注意的是，当INSERT和LOAD DATA INFILE的目标是一张空表时，存储引擎会自动先DISABLE KEYS，然后插入数据，插入结束后再自动ENABLE KEYS。

当插入海量数据时，花在完整性检查上的时间也会很长。可以通过设置系统变量unique_checks和foreign_key_checks来禁用UNIQUE索引以及foreign keys约束对数据的检查：

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

对于XtraDB/InnoDB表，可以临时将AUTO_INCREMENT的锁模式变量innodb_autoinc_lock_mode设置为2(见官方手册)，如下：

SET @@global.innodb_autoinc_lock_mode = 2;

该变量默认值为1，当执行大批量插入语句(如insert...select和load data infile)时，这些语句会持有表锁直到插入语句执行完毕，而单个的insert语句则会持有粒度更细、更轻量级的互斥锁而非表锁。当该变量设置为0时，表示任意插入语句包括单个insert语句都会持有表锁，直到语句执行完毕。当该变量设置为2时，表示任意语句都不会持有表锁，而总是持有轻量级互斥锁。

此外，如果表上有INSERT触发器，或者PERSISTENT字段(具有确定性的计算列，每次插入数据时都会计算)，可以先删除它们，在数据插入完成之后再重建它们。

# 查看表上有哪些insert触发器
select EVENT_OBJECT_TABLE,
        ACTION_TIMING,
        EVENT_MANIPULATION
from information_schema.triggers
where event_object_table='emp' and event_manipulation='INSERT';
 
# 查看表中是否有persistent字段
DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type        | Null | Key | Default | Extra      |
+-------+-------------+------+-----+---------+------------+
| a     | int(11)     | NO   |     | NULL    |            |
| b     | varchar(32) | YES  |     | NULL    |            |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL    |
| d     | varchar(5)  | YES  |     | NULL    | PERSISTENT |
+-------+-------------+------+-----+---------+------------+

SHOW CREATE TABLE table1;
| table1 | CREATE TABLE `table1` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` int(11) AS (a mod 10) VIRTUAL,
  `d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

(2).使用load data [local] infile或者mysqlimport从文本文件中导入数据。

这是最快的数据插入方式。从客户端上读取文件的速度比直接在服务端上读取文件的速度要慢一些，但差距不会很大。

LOAD DATA INFILE之所以很快，是因为：

1. 不需要解析SQL语句。

2. 读取数据时可以一次读取很多个数据块。

3. 如果load data之前表是空的，则所有的非唯一索引都会先禁用，插入数据成功后才启用。

4. 它会告诉存储引擎先缓存一些行的数据，达到一定数量后再一次性插入到表中(至少MyISAM和Aria支持该行为)。

5. 对于空表，某些事务性引擎(如Aria)不会记录数据插入的事务日志，因为我们可以随时通过truncate操作来回滚。

(3).将多个insert操作放进一个事务中。所以可以设置autocommit=0或者手动开启一个事务。

插入数据的时候使用大事务能极大提升效率，因为每次事务结束都会将内存中的数据sync到磁盘上。而刷盘操作所消耗的时间相对其他操作而言要久的多。

start transaction
insert ...
insert ...
commit
# 或者:
set @@session.autocommit=0;
insert ...
insert ...
set @@session.autocommit=1;

(4).尽量使用insert语句多值插入语法来插入数据。

即下面的第一条语句代替后3条语句：

insert into t values(),(),();
insert into t values();
insert into t values();
insert into t values();

(5).插入MyISAM表或Aria表时，使用INSERT DELAYED异步插入。

DELAYED关键字表示INSERT语句发送给服务端后，当语法检查正确后，服务端会立即返回OK给客户端表示插入成功。但实际上数据还未插入，只是放在服务端的内存队列中。

对于正在访问的表，使用DELAYED关键字后效率比一般的INSERT语句要高。但如果表目前是完全空闲的，则一般的INSERT语句效率比INSERT DELAYED效率要高，因为需要消耗一些资源去维护这个队列。

(6).修改几个系统变量。

posted @ 2018-04-22 15:47  骏马金龙  阅读(4187)  评论(0)  编辑  收藏  举报