在mysql中的多个表中批量插入和更新行的最佳方法
我有大约 10 个表插入，每个插入有 1 1000 甚至 10 万条记录 并且还更新了 4 个表格。基本上对于 10 万条记录，这将是 100 万次插入和 40 万次更新，并且这可以每小时运行一次。主要限制是 10 次表插入和 4 次表更新必须在一个事务中进行。
我读到了几种方法。为了处理更新，我可能会使用临时表并将所有必须更新的 ID 转储到表中，然后使用与更新必须更新到的 REAL 表中的临时表的连接完成了。但我仍然没有有效的解决方案来批量插入 10 个表。它消耗了很多时间。 LOAD_DATA 文件是一个选项，但这可能不是实时提供的好选择，我可能最终会创建数百个或数千个文件，因为此操作将在许多数据库和范围内发生。请提出提高这些批量插入速度的建议。
我计划更改 mysql 中的“bulk_insert_buffer_size”以及任何其他有用的参数。所以任何建议都会对我有很大帮助
I have like 10 table inserts and each insert has 1 either 1000 or even 1 lakh records
and have got 4 table updates as well. Basically for 1 lakh records, it would be 10 lakh inserts and 4 lakh updates and this could run on an hourly basis. And the main constraint is that the whole 10 table inserts and 4 table updates has to be in one transaction.
I read about several ways.For handling updates I might go with using a temporary table and and dump all the IDs which has to be updated to the table and then using a join with that temporary table from the REAL table to which the update has to be done. But I still dont have an effective solution for bulk inserting into 10 tables. It is eating up lot of time. LOAD_DATA file is an option, but that might not be a good choice provided in realtime I might end up creating 100s of thousands of files as this operation will happen across many databases and scopes. Please give your suggestions to increase the speed for these bulk inserts.
I am planning to change "bulk_insert_buffer_size" in mysql and any other parameters that would be useful as well. So any kind of suggestion would help me a lot here
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您打算如何进行这些批量插入？您是否使用一些客户端 GUI，例如 phpMyAdmin？请尝试在 MySQL 客户端提示符下运行 SQL。
这应该足够快来完成所有插入和更新。请记住在适用的地方输入实际值。 SQL 文件应包含有效的 INSERT 和 UPDATE 语句。
几点建议：
SETforeign_key_checks = 0并在末尾添加SETforeign_key_checks = 1ALTER TABLE...DISABLE KEYS和ALTER TABLE...ENABLE KEYS希望它有帮助！
How are you trying to do these bulk inserts? Are you using some client GUI like phpMyAdmin? Please try to run the SQL on the MySQL client prompt.
This should be fast enough to do all INSERT and UPDATES. Please do remember to put actual values where applicable. And the SQL file should contain valid INSERT and UPDATE statements.
Few suggestions:
SET foreign_key_checks = 0at the beginning of your SQL file andSET foreign_key_checks = 1at the endALTER TABLE...DISABLE KEYSandALTER TABLE...ENABLE KEYSHope it helps!