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;
相关文章
- MySQL Cluster在线添加数据节点
- 【MySQL】 DB 回滚崩溃案例一则
- 【MySql】mysql 慢日志查询工具之mysqldumpslow
- Hibernate + mysql 配置
- Mybatis+mysql动态分页查询数据案例——房屋信息的接口(IHouseDao)
- MySQL在大数据、高并发场景下的SQL语句优化和"最佳实践"
- MySQL 之 数据操作
- 【MySQL】解决You can't specify target table 'user_cut_record_0413' for update in FROM clause
- 连接Mysql提示Can’t connect to local MySQL server through socket的解决方法
- 通过binlog恢复mysql备份之前的数据
- MYSQL的用户变量(@)和系统变量(@@)
- ThinkPHP5 (mySQL) 统计各个时间段内的订单量
- mysql索引_效率测试(包含测试sql脚本300万条数据),可用作教学案例。
- 〖Python 数据库开发实战 - MySQL篇㊱〗- 综合案例 - 关于数据表 password 字段的数据加密
- 〖Python 数据库开发实战 - Python与MySQL交互篇①〗- MySQL Connector 驱动模块的连接语法
- mysql数据库(11):恢复数据
- MySQL Study之--Mysql无法启动“mysql.host”
- Eclipse中java获得mysql的查询结果集
- MySQL 数据库实现远程连接
- Linux MySQl 5.7.17 MySQL ERROR 1366(HY000):Incorrect string value 解决方法
- MySQL缺失mysql_config文件
- Mysql之加密连接mysql_ssl_rsa_setup
- 【mysql学习】6.where高级数据过滤
- docker运行mysql并数据持久化
- 【Mysql异构实时同步Oracle】OGG12异构同步mysql到oracle(windows mysql实时同步数据到linux oracle)详细文档