MySQL优化插入数据速度

在 MySQL 中，向数据表插入数据时，索引、唯一性检查、数据大小是影响插入速度的主要因素。本节将介绍优化插入数据速度的几种方法。

根据不同情况，可以分别进行优化。

对于 MyISAM 引擎的表，常见的优化方法如下：

1. 禁用索引

对非空表插入数据时，MySQL 会根据表的索引对插入的记录进行排序。插入大量数据时，这些排序会降低插入数据的速度。为了解决这种情况，可以在插入数据之前先禁用索引，等到数据都插入完毕后在开启索引。

禁用索引的语句为：

ALTER TABLE table_name DISABLE KEYS;

重新开启索引的语句为：

ALTER TABLE table_name ENABLE KEYS;

对于新创建的表，可以先不创建索引，等到数据都导入以后再创建索引，这样可以提高导入数据的速度。

2. 禁用唯一性检查

插入数据时，MySQL 会对插入的数据进行唯一性检查。这种唯一性检验会降低插入数据的速度。为了降低这种情况对查询速度的影响，可以在插入数据前禁用唯一性检查，等到插入数据完毕后在开启。

禁用唯一性检查的语句为：

SET UNIQUE_CHECKS=0;

开启唯一性检查的语句为：

SET UNIQUE_CHECKS=1;

3. 使用批量插入

在 MySQL 中，插入多条数据有 2 种方式。第一种是使用一个 INSERT 语句插入多条数据。INSERT 语句的情形如下：

INSERT INTO items(name,city,price,number,picture) VALUES ('耐克运动鞋','广州',500,1000,'001.jpg'),('耐克运动鞋2','广州2',500,1000,'002.jpg');

第二种是一个 INSERT 语句只插入一条数据，执行多个 INSERT 语句来插入多条数据。INSERT 语句的情形如下：

INSERT INTO items(name,city,price,number,picture)  VALUES('耐克运动鞋','广州',500,1000,'001.jpg');
INSERT INTO items(name,city,price,number,picture)  VALUES('耐克运动鞋2','广州',500,1000,'002.jpg');

一次性插入多条数据和多次插入数据所耗费的时间是不一样的。第一种方式减少了与数据库之间的连接等操作，其速度比第二种方式要快一些。所以插入大量数据时，建议使用第一种方法。

注意：如果能用 LOAD DATA INFILE 语句，就尽量用 LOAD DATA INFILE 语句。因为 LOAD DATA INFILE 语句导入数据的速度比 INSERT 语句的速度快。

对于 InnoDB 引擎的表，常见的优化方法如下：

1. 禁用唯一性检查

同 MyISAM 引擎相同，插入数据之前先禁用索引，等到数据都插入完毕后在开启索引。

2. 禁用外键检查

使用外键时，在子表中插入一条数据，首先会检查主表中是否有相应的主键值，然后锁定主表的记录，在插入值。相比较，使用外键多了2步操作，速度会慢一些。

所以我们可以在插入数据之前禁止对外键的检查，数据插入完成之后再恢复对外键的检查。不多对于数据完整性要求较高的系统不建议使用。

禁用外键检查语句为：

SET FOREIGN_KEY_CHECKS=0; 

恢复对外键的检查语句为：

SET FOREIGN_KEY_CHECKS=1;

3. 禁止自动提交

在《MySQL设置事务自动提交》一节我们提到 MySQL 的事务自动提交模式默认是开启的，其对 MySQL 的性能也有一定得影响。也就是说如果你插入了 1000 条数据，MySQL 就会提交 1000 次，这大大影响了插入数据的速度。而如果我们把自动提交关掉，通过程序来控制，只要一次提交就可以了。

所以插入数据之前可以先禁止事务的自动提交，待数据导入完成之后，再恢复自动提交操作。

禁止自动提交语句为：

SET AUTOCOMMIT=0; 

恢复自动提交语句为：

SET AUTOCOMMIT=1;

--------------------------------------------------------------------------------------------------------------

调整 innodb_flush_log_at_trx_commit=0

innodb_flush_log_at_trx_commit

提交事务的时候将 redo 日志写入磁盘中，所谓的 redo 日志，就是记录下来你对数据做了什么修改，比如对 “id=10 这行记录修改了 name 字段的值为 xxx”，这就是一个日志。如果我们想要提交一个事务了，此时就会根据一定的策略把 redo 日志从 redo log buffer 里刷入到磁盘文件里去。此时这个策略是通过 innodb_flush_log_at_trx_commit 来配置的，他有几个选项。
值为0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。
可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，我们知道写磁盘的速度是很慢的，因此 MySQL 的性能会明显地下降。如果不在乎事务丢失，0和2能获得更高的性能。

# 查询
select @@innodb_flush_log_at_trx_commit;

sync_binlog

该参数控制着二进制日志写入磁盘的过程。

该参数的有效值为0 、1、N：

0：默认值。事务提交后，将二进制日志从缓冲写入磁盘，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志。

1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。

N：每写N次操作系统缓冲就执行一次刷新操作。

将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。
二进制日志文件涉及到数据的恢复，以及想在主从之间获得最大的一致性，那么应该将该参数设置为1，但同时也会造成一定的性能损耗。

参考：

http://c.biancheng.net/view/8253.html

https://www.cnblogs.com/klvchen/p/10861850.html

https://blog.csdn.net/chichenyin9669/article/details/100926480

https://blog.csdn.net/qq_35642036/article/details/82820178