如何提高MySQL的INSERT和UPDATE性能？

1. 检查您的硬件和操作系统是否正确configuration和调整：

   • 问题的来源（CPU / IO /内存/交换使用）。 你有很多IOP的吗？ CPU是否加载？ 如果你有很多读取IOP的可能你没有足够大的InnoDB buffer_pool。 如果CPU加载可能您的查询做全表扫描，而不是使用适当的索引。
   • 磁盘/ RAID / LVM设置。 在一些特定的设置中， LVM条带化可以通过磁盘负载（无硬件RAID，连接多个LUN）
   • IO调度程序：当你有好的硬件RAID控制器时，可能noop是最好的。 RedHat做了一些testing ，他们说，对于Oracle（和其他DB）CFQ是最好的select。 您需要运行一些基准testing（如tpc-c或tpc-e）并select最适合您的硬件。
   • 好的文件系统 – ext3在数据库特定的工作负载中performance不佳。 更好的是XFS或OCFS2。 你需要一些基准。
   • 注意，如果你的系统使用交换。 使用交换会降低mysql的性能 。

2. 检查一下，如果你的MySQL / InnoDB实例调整得当，

   • 缓冲池大小 – caching内存中的数据页面
   • innodb_flush_method = O_DIRECT – 避免双IO缓冲
   • 增加InnoDB日志文件大小 – 为了编写密集型工作负载，这可以提高性能。 但请记住：更大的日志文件大小意味着更长的崩溃恢复 有时在几小时内！
   • innodb_flush_log_at_trx_commit = 0或2 – 如果您不关心ACID，并且可能会在最后一秒或两秒内失去事务处理。
   • key_buffer_size – 对于MyISAM非常重要，但它用于磁盘临时表。
   • 注意你的INNODB状态
3. 分析你的工作量 – 抓住所有的查询来慢速查询日志并运行mk-query-digest。 你可以使用tcpdump和maatkit来捕获所有的查询
   • 什么查询需要大部分服务器时间？
   • 是否创build了临时表，尤其是大的临时表？
   • 学习，如何使用解释
   • 你的应用程序使用交易吗？ 当你使用autocommit = 1（默认为MySQL）运行查询时，每个插入/更新查询开始新的事务，这会产生一些开销。 如果可能的话，最好禁用自动提交（在Python中，默认情况下禁用MySQL驱动程序自动提交），并在所有修改完成后手动执行提交。
   • 您的应用程序是否将循环中的一系列插入操作添加到同一个表中？ Load data infile命令对于一系列插入要快得多。
   • 记住： select count(*) from table; innodb比myisam慢得多。
   • 什么types的INSERT / UPDATE查询占用大部分服务器时间？ 他们如何优化？
   • 检查，如果你的数据库有适当的索引，并添加它们，如果需要的话。

在我们的环境中，我们遇到了一种更新查询速度慢的情况。 估计完成批量工作的时间是2天！ 分析slowquery日志后，我们发现，这种types的更新查询需要4秒钟才能完成。 查询是这样的： update table1 set field1=value1 where table1.field2=xx table2.field3=yy and table2.field4=zz 。 在转换更新查询以select查询并运行该select查询的解释之后，我们发现，这种types的查询不使用索引。 在创build适当的索引后，我们将更新查询执行时间缩短到了几毫秒，整个作业在不到两个小时内完成。

一些有用的链接：

• 显示INNODB状态走过
• 安装后在MySQL服务器中调整什么
• Innodb性能优化基础
• 如何计算一个好的InnoDB日志文件的大小
• selectinnodb_buffer_pool_size
• select适当的innodb_log_file_size

使用默认的innoDBconfiguration，你将受限于你写入和刷新交易到磁盘的速度。 如果您可以处理丢失一点ACID，请试用innodb_flush_log_at_trx_commit。 设置为0可以每秒钟将日志写入磁盘并刷新到磁盘。 设置为1（默认）在每次提交时写入和刷新。 设置为2以在每次提交后写入日志文件，但每秒仅刷新一次。

如果您可以处理丢失1笔交易，这可以大大提高写入性能。

另外，请注意你的磁盘正在做什么。 RAID 10>用于写入的RAID 5是以额外的磁盘为代价的。

locking问题将以show full processlist;的连接状态show full processlist;

通读my.cnf和MySQL文档。 configuration选项有很好的logging。

一般来说，您希望尽可能多地在内存中进行处理。 对于查询优化，这意味着避免临时表。 正确应用索引。

调优将特定于您的首选数据库引擎和应用程序体系结构。 有大量的资源预先存在互联网search。

• MySQL文档
• 安装后在MySQL服务器中调整什么

开启Innodb监视器可以帮助识别locking和死锁的原因：

SHOW ENGINE INNODB STATUS和InnoDB Monitors

InnoDB是一个不错的引擎。 但是，它高度依赖于“调整”。 有一件事是，如果你的插入不是增加主键的顺序，innoDB可能比MyISAM稍长一些。 这可以通过设置更高的innodb_buffer_pool_size轻松解决。 我的build议是把它设置在你的总RAM的60-70％。 我现在正在生产4台这样的服务器，每分钟插入大约350万行。 他们已经有接近3千兆字节。 必须是InnoDB，因为高度并发的插入。 还有更多的方法来加速插入。 我已经做了一些基准testing。