mysql 索引外键优化_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 的性能也有一定的影响。

也就是说如果你插入了 1000 条数据，MySQL 就会提交 1000 次，这大大影响了插入数据的速度。

而如果我们把自动提交关掉，通过程序来控制，只要一次提交就可以了。

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

禁止自动提交语句为：

SET AUTOCOMMIT=0;

恢复自动提交语句为：

SET AUTOCOMMIT=1;