分享

MySQL innoDB性能优化手记---ATHER Shu(A.S)的Blog

 Jamin 2009-12-19

sshong 发表于2009-12-09 13时22分 更新于2009-12-09 13时24分
起因:有一个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;

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多