MySQL innoDB数据插入性能优化

转载 2015年11月21日 17:03:58
起因:有一个innoDB引擎的表Table,在一个大概3000次的foreach循环中执行

INSERT INTO Table(columnA, columnB) VALUES (valueA, valueB)

结果居然超出了60S的php执行限制(当然这个限制可以在php.ini中修改),让我很不解为何插入效率如此低下。

经过查找资料以及摸索,得到以下优化方法:

1、innoDB是mysql引擎中唯一支持事务transaction的引擎。默认所有用户行为都在事务内发生。
默认mysql建立新连接时,innoDB采用自动提交autocommit模式,每个SQL语句在它自己上形成一个单独的事务,即insert一次就commit了一次,InnoDB在该事务提交时必须刷新日志到磁盘,因此效率受限于磁盘读写效率。
你可以通过
mysql_query("SET AUTOCOMMIT = 0");

来关闭自动提交模式。

如 果自动提交模式被关闭,那么我们可以认为一个用户总是有一个事务打开着。一个SQL COMMIT或ROLLBACK语句结束当前事务并且一个新事务开始。两个语句都释放所有在当前事务中被设置的InnoDB锁定。一个COMMIT语句意 味着在当前事务中做的改变被生成为永久的,并且变成其它用户可见的。一个ROLLBACK语句,在另一方面,撤销所有当前事务做的修改。
当然如果是自动提交模式,通过用明确的START TRANSACTION或BEGIN语句来开始一个事务,并用COMMIT或者ROLLBACK语句来结束它,这样用户仍旧可以执行一个多重语句事务。

2、因此对于本例,在建立数据库连接后,立即关闭自动提交,在foreach循环结束后,一次commit即可,效率大大提升。

mysql_query("SET AUTOCOMMIT = 0");
foreach(***)
  INSERT INTO Table(columnA, columnB) VALUES (valueA, valueB)
mysql_query("commit");


3、对于多次insert行到同一表的需求,你还可以采用多行插入语法来减少客户端和服务器之间的通讯开支。


INSERT INTO Table(columnA, columnB) VALUES (1,2), (5,5), (3,3), ...


4、如果你的表有索引,索引会拖慢insert速度。大量插入数据时,可以先关闭索引,然后再重建索引。

ALTER TABLE Table DISABLE KEYS;
INSERT INTO ***;
ALTER TABLE Table ENABLE KEYS;

相关文章推荐

MySQL插入语句insert性能优化

对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长。当导入的数据量较大时,插入操作耗费的时间相当可观。因此,提高大数据量系统的MySQL insert效率是很有...

MySQL性能研究之插入优化

本文出自:http://blog.csdn.net/hongchangfirst  序言         这篇文章研究了在海量数据需求下,MySQL数据库的承载能力。如果读者想尽快的获得帮助,而不想纠...

Mysql批量插入更新性能优化

Mysql批量插入性能优化测试对于数据量较大的插入和更新,因io/cpu等性能瓶颈,会产生大量的时间消耗,目前主流的优化主要包括预编译、单条sql插入多条数据、事务插入等,下面详细介绍一下:单条插入(...

mysql服务性能优化—my.cnf配置说明详解(16G内存)

此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明 这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数 MYSQL...

mysql千万级数据库插入速度和读取速度的调整记录

http://www.111cn.net/database/mysql/43315.htm 一般情况下mysql上百万数据读取和插入更新是没什么问题了,但到了上千万级就会出现很慢,下...

MySQL优化之——插入 更新 删除

插入 将多行查询结果插入到表中 语法 INSERT INTO table_name1(column_list1) SELECT (column_list2) FROM table_name2 WHER...

mysql存储引擎InnoDB插入数据的过程详解

当插入,更新,删除等sql语句运行后,mysql为何总能高效,快速的执行返回,而且不管是断电,mysql进程崩溃,还是一些其它非正常因素,mysql总能保持数据完整, 本文将带着这些问题探秘mysql...

InnoDB提高数据insert效率

如果local_infile 被禁用,InnoDB中怎么提高insert的效率呢? 1.尽量保持数据有序。减少数据插入时对索引的维护成本。 2.一次插入多条数据。减少日志,降低日志刷新磁盘的频率。...

关于数据库写入慢的问题autocommit,索引等对Innodb写入速度的影响

关于线上数据库写入慢问题总结分析   之前安排的说主从延迟是否是写入慢的问题,一直没有思路,最近通过看书《MySQL内核---InnodbDB存储引擎》,有点思路,再网上找,果真有这么的测试。下面...

MySQL-SQL插入性能优化

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL innoDB数据插入性能优化
举报原因:
原因补充:

(最多只允许输入30个字)