MYSQL 8 優化之06 (LOAD DATA INFILE)

1. 创建保存数据文件的目录

[root@XAG816 local]# mkdir -p /usr/local/outfile
[root@XAG816 local]# chown -R mysql:mysql /usr/local/outfile

2. 创建测试表

create table test_load
 (
  load_seq int auto_increment not null,
  load_name varchar(20),
  creator varchar(10),
  create_date datetime,
  primary key(load_seq)
 ) auto_increment=1001;

root@127.0.0.1 : testdb【10:25:06】10 SQL->insert into test_load(load_name,creator,create_date) values('name_01','xag',now());

root@127.0.0.1 : testdb【10:26:33】11 SQL->insert into test_load(load_name,creator,create_date) values('name_02','张三',now());

root@127.0.0.1 : testdb【10:26:50】12 SQL->insert into test_load(load_name,creator,create_date) values('名称3','sys',now());

root@127.0.0.1 : testdb【10:27:32】13 SQL->select * from test_load;
+----------+-----------+---------+---------------------+
| load_seq | load_name | creator | create_date         |
+----------+-----------+---------+---------------------+
|     1001 | name_01   | xag     | 2019-07-25 22:26:33 |
|     1002 | name_02   | 张三    | 2019-07-25 22:26:50 |
|     1003 | 名称3     | sys     | 2019-07-25 22:27:32 |
+----------+-----------+---------+---------------------+

3. select into outfile

root@127.0.0.1 : testdb【10:34:29】17 SQL->show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         | NULL  |
+--------------------------+-------+

#配置參數my.cnf
在my.ini文件里修改此项，[mysqld]里面 改为secure-file-priv="/"。表示不限制mysqld在任意目录的导入导出

root@127.0.0.1 : testdb【10:39:34】3 SQL->show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         | /     |
+--------------------------+-------+

select * from test_load into outfile "/usr/local/outfile/test_load_01.sql" fields terminated by ',' lines terminated by '\r\n';

[root@XAG816 ~]# cat /usr/local/outfile/test_load_01.sql
1001,name_01,xag,2019-07-25 22:26:33
1002,name_02,张三,2019-07-25 22:26:50
1003,名称3,sys,2019-07-25 22:27:32

select * from test_load into outfile "/usr/local/outfile/test_load_02.sql" fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

[root@XAG816 ~]# cat /usr/local/outfile/test_load_02.sql
1001,"name_01","xag","2019-07-25 22:26:33"
1002,"name_02","张三","2019-07-25 22:26:50"
1003,"名称3","sys","2019-07-25 22:27:32"

4. load data

root@127.0.0.1 : testdb【10:49:43】10 SQL->
delete from test_load;

root@127.0.0.1 : testdb【10:50:51】12 SQL->
load data infile "/usr/local/outfile/test_load_01.sql" into table test_load fields terminated by ',' lines terminated by '\r\n';

root@127.0.0.1 : testdb【10:51:17】13 SQL-> select * from test_load;
+----------+-----------+---------+---------------------+
| load_seq | load_name | creator | create_date         |
+----------+-----------+---------+---------------------+
|     1001 | name_01   | xag     | 2019-07-25 22:26:33 |
|     1002 | name_02   | 张三    | 2019-07-25 22:26:50 |
|     1003 | 名称3     | sys     | 2019-07-25 22:27:32 |
+----------+-----------+---------+---------------------+

root@127.0.0.1 : testdb【10:49:43】10 SQL->
delete from test_load;

root@127.0.0.1 : testdb【10:50:51】12 SQL->
load data infile "/usr/local/outfile/test_load_02.sql" into table test_load fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

5. MyISAM 表的load ( disable key 关闭MyISAM 表的唯一索引更新)

root@127.0.0.1 : testdb【09:23:18】24 SQL->create table test_load_myisam like test_load;

root@127.0.0.1 : testdb【09:25:56】25 SQL->alter table test_load_myisam engine=myisam;

root@127.0.0.1 : testdb【09:26:20】26 SQL->insert into test_load_myisam select * from test_load;

#开始导入数据
root@127.0.0.1 : testdb【11:01:06】20 SQL->delete from test_load_myisam;

root@127.0.0.1 : testdb【11:01:23】21 SQL->alter table test_load_myisam disable keys;

root@127.0.0.1 : testdb【11:02:12】22 SQL->load data infile "/usr/local/outfile/test_load_01.sql" into table test_load_myisam fields terminated by ',' lines terminated by '\r\n';

root@127.0.0.1 : testdb【11:03:23】24 SQL->alter table test_load_myisam enable keys;

root@127.0.0.1 : testdb【11:03:40】25 SQL->select * from test_load_myisam;
+----------+-----------+---------+---------------------+
| load_seq | load_name | creator | create_date         |
+----------+-----------+---------+---------------------+
|     1003 | 名称3     | sys     | 2019-07-25 22:27:32 |
|     1002 | name_02   | 张三    | 2019-07-25 22:26:50 |
|     1001 | name_01   | xag     | 2019-07-25 22:26:33 |
+----------+-----------+---------+---------------------+

6. InnoDB 表的load ( unique_checks 关闭唯一性校验，可提高导入速度)

root@127.0.0.1 : testdb【09:12:27】2 SQL->delete from test_load;

#关闭唯一性校验
root@127.0.0.1 : testdb【09:13:12】3 SQL->set unique_checks=0;

root@127.0.0.1 : testdb【09:13:44】4 SQL->load data infile "/usr/local/outfile/test_load_01.sql" into table test_load fields terminated by ',' lines terminated by '\r\n';
#开启唯一性校验
root@127.0.0.1 : testdb【09:14:08】5 SQL->set unique_checks=1;

7. InnoDB 表的load ( 关闭autocommit自动提交，可提高导入速度)

root@127.0.0.1 : testdb【09:14:14】6 SQL->delete from test_load;

#关闭autocommit自动提交
root@127.0.0.1 : testdb【09:17:43】7 SQL->set autocommit = 0;

root@127.0.0.1 : testdb【09:17:55】8 SQL->load data infile "/usr/local/outfile/test_load_01.sql" into table test_load fields terminated by ',' lines terminated by '\r\n';

root@127.0.0.1 : testdb【09:18:12】9 SQL->commit;

#开启autocommit自动提交
root@127.0.0.1 : testdb【09:18:48】10 SQL->set autocommit = 1;

8. InnoDB 类型的表时按主键的顺序保存的，所以将导入的数据按主键的顺序排列，可以有效地提高导入数据的效率