在Java中的MySQL插入语句的性能: 批处理模式准备语句 vs 单个插入语句中的多个值
在Java中的MySQL插入语句的性能: 批处理模式准备语句 vs 单个插入语句中的多个值
我正在设计一个需要处理每秒约600行插入的MySQL数据库，涉及不同的InnoDB表。我目前的实现使用了非批处理的预编译语句。然而，写入MySQL数据库的速度很慢，我的队列大小随时间增长。
这个实现是用Java编写的，我手头不知道版本。它使用了MySQL的Java连接器。我明天需要研究一下是否要切换到JDBC。我假设这两个是不同的连接器包。
我已经阅读了以下关于这个问题的帖子：
- 优化MySQL插入以处理数据流
- MyISAM与InnoDB的比较
- 在不使用预编译语句的情况下向MySQL插入二进制数据
还有MySQL网站上的资料：
- http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
我的问题是：
- 有人对使用批处理模式下预编译语句的插入与使用单个INSERT语句的多个VALUES之间的性能差异有什么建议或经验吗？
- MySQL Java连接器与JDBC之间的性能差异是什么？我应该使用其中之一吗？
- 这些表是用于存档目的，将会看到大约90%的写入和10%的读取（甚至更少）。我正在使用InnoDB。相对于MyISAM，这是正确的选择吗？
提前感谢您的帮助。
MySQL的INSERT语句在Java中的性能：批处理模式预编译语句与单个插入多个值的比较
在使用MySQL的项目中，我们主要使用MyISAM来处理大量的数据，因为它可以在牺牲事务的情况下获得更高的性能。但是一般来说，MyISAM更快但是InnoDB更可靠。
大约一年前，我对INSERT语句的性能也产生了疑问，于是我找到了以下的旧测试代码（抱歉，它有点复杂，超出了您的问题范围）。下面的代码包含了插入测试数据的4种方式：
- 单个INSERT语句；
- 批处理INSERT语句；
- 手动批量INSERT语句（不建议使用，因为它很危险）；
- 最后是预编译批量INSERT语句。
代码使用TestNG作为运行器，并使用了一些自定义的代码，例如：
- runWithConnection()方法：在回调执行后确保连接关闭或放回连接池（但是下面的代码使用了不可靠的语句关闭策略，没有使用try/finally来简化代码）；
- IUnsafeIn
以下是代码示例（省略部分代码）：
...
通过对比上述四种方式的性能，可以发现MyISAM的性能要比InnoDB高很多。
希望这可以帮助到您。
更新：
对于第四种方式，您必须在mysql.ini文件中的[mysqld]部分正确设置max_allowed_packet，以支持非常大的数据包。
感谢这些基准测试，这是我最期待的直接的答案。我今天实现了批处理预编译插入，效果非常好！
不知道为什么批处理插入在InnoDB上比单个插入慢？
嗯，是的，很难说，因为这可能只与特定的测试用例有关。谢谢您的注意。
问题的原因是在使用MySQL的插入语句时，单个插入多个值的性能不如批处理模式准备好的语句。解决方法是在jdbc url中添加"rewriteBatchedStatements=true"参数，可以显著提高批处理语句的性能。
文章正文：
我知道这个帖子很旧了，但我想提一下，在使用MySQL时，如果在jdbc url中添加"rewriteBatchedStatements=true"参数，可以在使用批处理语句时获得巨大的性能提升。
这是我第一次遇到"rewriteBatchedStatements"设置。对于大批量语句，这将使速度提高数个数量级，正是我一直在寻找的。更多信息请参见stackoverflow.com/questions/26307760。
MySQL在Java中的插入语句性能：批处理模式准备语句 vs 多值单插入的性能比较
问题的出现原因：是否在受影响的表中有任何触发器？如果没有的话，每秒600次插入并不算多。JDBC的批量插入功能会在同一事务中多次发出相同的语句，而多值SQL会将所有值压缩到单个语句中。在多值语句的情况下，您将不得不动态构造插入SQL，这可能会增加额外的代码、内存、SQL注入保护机制等开销。首先尝试常规的批处理功能，对于您的工作负载，这应该不是一个问题。
问题的解决方法：如果您不以批量方式接收数据，请考虑在插入之前进行批量处理。我们在单独的线程上使用队列来实现生产者-消费者模式。在这种情况下，我们会保留插入，直到经过一定的时间或队列的大小超过阈值。
代码示例：
if(System.currentTimeMillis()-lastInsertTime > TIME_THRESHOLD || queue.size() > SIZE_THRESHOLD) {
lastInsertTime = System.currentTimeMillis();
// 插入逻辑
} else {
// 什么都不做或者等待一段时间后重试
}
感谢您的建议。我今天进行了一些研究，并创建了一个简单的生产者-消费者关系。我的数据处理器在一个线程中工作，将信息添加到属于MySQL插入线程的队列中。看起来效果不错。我使用InnoDB是因为有一些重要的外键关系我想尝试保持。但似乎在整个计划中它们可能并不是必需的，所以我可能明天会切换到MyISAM并看看效果如何。