MySQL innoDB insert into性能优化

56 阅读 0 评论 37 点赞

我是靠谱客的博主悦耳灯泡，最近开发中收集的这篇文章主要介绍MySQL innoDB insert into性能优化，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

起因：有一个innoDB引擎的表Table，在一个大概3000次的foreach循环中执行 INSERT INTO Table(columnA, columnB) VALUES (valueA, valueB)，结果居然超出了60S的php执行限制（当然这个限制可以在php.ini中修改），这是因为innoDB是mysql存储引擎中唯一支持事务的，默认所有用户的行为都在事务内发生。
默认mysql建立新连接时，innoDB采用自动提交autocommit模式，每个SQL语句在它自己上形成一个单独的事务，即insert一次就commit了一次，InnoDB在该事务提交时必须刷新日志到磁盘，因此效率受限于磁盘读写效率。
你可以通过 mysql_query("SET AUTOCOMMIT = 0"); 来关闭自动提交模式。
如果自动提交模式被关闭，那么我们可以认为一个用户总是有一个事务打开着。一个SQL COMMIT或ROLLBACK语句结束当前事务并且一个新事务开始。两个语句都释放所有在当前事务中被设置的InnoDB锁定。一个COMMIT语句意味着在当前事务中做的改变被生成为永久的，并且变成其它用户可见的。一个ROLLBACK语句，在另一方面，撤销所有当前事务做的修改。
当然如果是自动提交模式，通过用明确的START TRANSACTION或BEGIN语句来开始一个事务，并用COMMIT或者ROLLBACK语句来结束它，这样用户仍旧可以执行一个多重语句事务。

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

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

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

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

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

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