32.进阶-SQL优化-插入数据

32.进阶-SQL优化-插入数据

（1）insert

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....

1). 优化方案一

批量插入数据

解释：因为每一次insert都需要与数据库进行连接，进行网络传输，这个性能相对来说是比价低的。批量插入比建议超过1000条。如果有几万条，可以分成过个insert语句。

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2). 优化方案二

手动控制事务

解释：mysql中的事务默认是自动提交的，也就意味了当你执行完了一条sql，他就提交了，再执行一条，执行之前开启事务，执行完毕之后自动提交事务，这个时候就会设计到频繁的事务开启与提交，所以建议手动控制事务。

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

3). 优化方案三

主键顺序插入，性能要高于乱序插入。

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

（2）大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

我们会发现：使用load指令在加载本地文件的到数据库表的时候，本地文件里面写的并不是sql语句，而是符合一定规则的文件。这里面的规则不一定是逗号，可以使冒号，可以使任意其他的符号。

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p	#表示当前客户端连接服务端时，我需要去加载本地文件
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1; #开启这个开关，我们才可以在本地去加载文件到数据库表结构中
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;
#terminated by ','每一个字段之间使用","分隔，lines terminated by '\n' ;每一行数据之间使用"\n"分隔

select @@local_infile; #查看是否打开开关

主键顺序插入性能高于乱序插入

示例演示:

A. 创建表结构

CREATE TABLE `tb_user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `birthday` DATE DEFAULT NULL,
    `sex` CHAR(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

B. 设置参数

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

C. load加载数据

load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

我们看到，插入100w的记录，17s就完成了，性能很好。

在load时，主键顺序插入性能高于乱序插入