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批量插入更新性能优化

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

MySQL插入语句insert性能优化

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

MySQL批量插入性能优化(二)

测试的方案: 执行10万次Insert语句,使用不同方式。   A组:静态SQL,自动提交,没事务控制(MyISAM引擎) 1、逐条执行10万次 2、分批执行将10万分成m批,每批n条,分多...
  • wwd0501
  • wwd0501
  • 2015年04月15日 11:02
  • 1496

MySQL-SQL插入性能优化

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长。特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此,优化数据库插入性能是很有意义的。...
  • alexdamiao
  • alexdamiao
  • 2016年06月28日 22:27
  • 8421

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

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

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

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

数据插入 效率比较 mysql

做数据插入时,发现之前上班做哪些办公系统压根就没考虑过数据库性能这些,因为涉及的数据量小,时间和效率看不出来,可当数据量很大了,大到了每秒需要10000次插入时,这时就不得不考虑你的sql 语句了。当...
  • chenqiangdage
  • chenqiangdage
  • 2015年11月26日 23:13
  • 2388

mysql 批量插入与单条插入 的效率比较

sql中的单条出入与批量插入效率比较
  • bianfu2008zhi
  • bianfu2008zhi
  • 2017年01月16日 11:14
  • 3485

如何优化MySQL insert性能

如何优化MySQL insert性能 对于一些数据量较大系统,面临问题除查询效率低下,还有一个很重要的问题是插入时间长。 提供insert效率方法: 1.一条sql语句插入多条数据 ins...
  • ys1109
  • ys1109
  • 2014年03月12日 10:42
  • 418

INNODB 重要优化设置【解决慢卡的问题】

1.内存利用方面: 首先介绍一个Innodb最重要的参数: innodb_buffer_pool_size   这个参数和MyISAM的key_buffer_size有相似之处,但也是有差别的。...
  • k8080880
  • k8080880
  • 2015年04月17日 08:29
  • 4975
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL innoDB数据插入性能优化
举报原因:
原因补充:

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