注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

@fc_lamp

关注Web应用解决方案MySql/PHP/Python一盏名为"飞川"的灯~

 
 
 

日志

 
 

MYSQL批量插入数据最佳实践(MYISAM,InnoDB)  

2011-03-29 13:22:00|  分类: Web技术-Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

   在使用MYSQL批量插入数据时我们一般可选的方式有:


   * 长SQL  insert into table(xx,xx) values(xx,xx),(xx,xx)

  * 或者执行多个单条INSERT INTO table(xx,xx) VALUES (xx,xx);语句

  * 使用 LOAD DATA INFILE(导入)是SELECT...INTO OUTFILE(导出)

  * 使用 insert into ...select... 等


   但在使用这些方式之前,你可能得考虑一下执行速度问题。因为在MYSQL中的MyISAM与InnoDB引擎在读写操作执行效率有所不同。

   至于MyISAM与InnoDB的区别请看:

Advantages of InnoDB

InnoDB should be used wheredata integritycomes a priority because it inherently takes care of them by the help of relationship constraints and transactions.

Faster in write-intensive(inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have totake more time in designingthe data models which are more complex than those of MyISAM.

Consumes more system resourcessuch as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.

No full-text indexing.

 

Advantages of MyISAM

Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.

Faster than InnoDB on the wholeas a result of the simpler structure thus much less costs of server resources.

Full-text indexing.

Especially good forread-intensive (select) tables.

Disadvantages of MyISAM

No data integrity(e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.

Doesn’t support transactionswhich is essential in critical data applications such as that of banking.

Slowerthan InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

完整原文:http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html

 

 在使用InnoDB引擎批量插入数据时有几点是要说明的:

  InnoDB批量插入最佳方式:

    * When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that

      requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with

      SET autocommit and COMMIT statements:

      SET autocommit=0;
     ... SQL import statements ...
     COMMIT;

      If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table,
      even without wrapping them with the SET autocommit and COMMIT statements.


   * If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off 
     the uniqueness checks during the import session:

      SET unique_checks=0;
      ... SQL import statements ...
      SET unique_checks=1;

      For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index 
      records in a batch. Be certain that the data contains no duplicate keys.


   * If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks 
     off for the duration of the import session:

      SET foreign_key_checks=0;
      ... SQL import statements ...
      SET foreign_key_checks=1;

      For big tables, this can save a lot of disk I/O.

完整的InnoDB性能设置请看:http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

 

另外:如何优化INSERT语句速度的因素请看:http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#insert-speed

?以上方式我这里就不给出具体实例了。

  评论这张
 
阅读(592)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017