关闭

MySQL innoDB数据插入性能优化

标签: 数据库sql
1724人阅读 评论(0) 收藏 举报
分类:
起因:有一个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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:8913次
    • 积分:142
    • 等级:
    • 排名:千里之外
    • 原创:0篇
    • 转载:34篇
    • 译文:0篇
    • 评论:0条
    文章分类