SQL优化--插入数据

插入数据（insert优化）

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

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

优化方案一：批量插入数据

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

即便是批量插入也不建议一次性插入太多，500-1000条较为合适。如果需要插入更多，建议分割成多次进行插入

为什么批量插入不建议太多

对于批量插入数据，确实不建议一次性插入太多记录，通常建议控制在500到1000条左右的范围内。这是因为一次性插入大量数据可能会导致以下问题：

1. 事务大小：插入太多记录可能导致事务变得非常大，这会增加数据库的资源消耗和锁定时间，从而影响其他事务的性能。

2. 内存消耗：大批量插入可能需要大量的内存来处理，这可能导致数据库服务器的内存不足，从而导致性能下降或崩溃。

3. 日志写入：每次插入操作都会写入事务日志，大批量插入会导致大量的日志写入，增加了磁盘IO负担。

4. 锁定和竞争：大批量插入可能会导致与其他操作的锁定竞争，影响并发性能。

为了避免这些问题，通常建议将批量插入分批进行，每次插入500到1000条记录，然后在每个批次之间进行提交。这样可以控制事务的大小，减少内存消耗，降低日志写入量，并减少锁定竞争，从而提高性能和稳定性。

此外，如果可能的话，可以考虑使用数据库特定的批量插入工具或语法，以进一步优化性能。例如，在MySQL中，可以使用`INSERT INTO ... VALUES (), (), ...`语法来一次性插入多行数据，而不是多个单独的INSERT语句。不同数据库管理系统可能具有不同的优化策略，因此要根据具体的数据库系统来选择合适的方法。

优化方案二：手动提交数据

在MySQL当中，默认是自动提交的，也就是意味着你每执行完一条insert语句便会自动提交。这样会涉及到频繁的事务的开启和提交，所以我们可以手动控制事务

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

优化方案三：主键顺序插入

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

这种方法可以帮助确保主键值按照顺序分配，从而提高性能并减少竞争

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

大批量数据插入

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

通过load指令，我们可以一次性将本地文件当中的数据，全部加载到数据库当中

操作如下：

左侧是我们本地磁盘文件的原始结构，我们可以通过一条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' ;

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