Innodb性能优化
我网站的一部分需要批量插入，innodb 需要大约 40 分钟才能将该文件加载到数据库中。我一直在网上挖掘并发现了一些东西。
- innodb_autoinc_lock_mode=2 （不会生成连续的键）
- UNIQUE_CHECKS=0; （禁用唯一键检查）
- FOREIGN_KEY_CHECKS=0 （禁用外键检查）
- --log_bin=OFF 关闭用于复制的二进制日志
问题
我想为一个会话（即在批量插入期间）设置前 3 个选项。第一个选项不起作用 mysql 说未知系统变量“innodb_autoinc_lock_mode”。我正在使用 MySQL 5.0.4
最后一个选项，我想将其关闭，但我想知道如果我稍后需要复制，如果我再次打开它，它会开始工作吗？
建议
对于如何改进 innodb 引擎的批量插入/更新还有其他建议吗？或者请对我的发现发表评论。
谢谢
One of the portion of my site requires bulk insert, it takes around 40 mins for innodb to load that file into database. I have been digging around the web and found few things.
- innodb_autoinc_lock_mode=2 (It wont generate consecutive keys)
- UNIQUE_CHECKS=0; (disable unique key checks)
- FOREIGN_KEY_CHECKS=0 (disable foreign key checks)
- --log_bin=OFF turn off binary log used for replication
Problem
I want to set first 3 options for just one session i.e. during bulk insert. The first option does not work mysql says unknown system variable 'innodb_autoinc_lock_mode'. I am using MySQL 5.0.4
The last option, I would like to turn it off but I am wondering what if I need replication later will it just start working if I turn it on again?
Suggestions
Any other suggestions how to improve bulk inserts/updates for innodb engine? Or please comments on my findings.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
假设您在单个或几个事务中加载数据，则大部分时间可能都花在构建索引上（取决于表的架构）。
不要在启用自动提交的情况下执行大量小插入，这会破坏每次提交的同步性能。
如果你的表比 innodb 缓冲池更大（或者几乎和 innodb 缓冲池一样大），那么你就有麻烦了；内存中无法容纳其索引的表无法有效插入，因为它必须执行 READS 操作才能插入。这样就可以更新现有的索引块。
请记住，光盘写入是可以的（它们大多是连续的，并且您有一个电池支持的 raid 控制器，对吗？），但读取速度很慢，需要避免。
总之，
不要进行上面列出的任何优化，它们可能会浪费时间（不要相信我的话 - 在您的实验室中的测试系统上对有/没有这些优化的操作进行基准测试）。
关闭唯一检查非常危险，因为您最终会得到损坏的数据。
Assuming you are loading the data in a single or few transactions, most of the time is likely to be spent building indexes (depending on the schema of the table).
Do not do a large number of small inserts with autocommit enabled, that will destroy performance with syncs for each commit.
If your table is bigger (or nearly as big as) the innodb buffer pool you are in trouble; a table which can't fit in ram with its indexes cannot be inserted into efficiently, as it will have to do READS to insert. This is so that existing index blocks can be updated.
Remember that disc writes are ok (they are mostly sequential, and you have a battery-backed raid controller, right?), but reads are slow and need to be avoided.
In summary
Don't make any of the optimisations listed above, they're probably waste of time (don't take my word for it - benchmark the operation on a test system in your lab with/without those).
Turning unique checks off is actively dangerous as you'll end up with broken data.
要回答你问题的最后一部分，不，它不会再次开始工作；它会再次开始工作。如果不复制插入但复制后续更新，结果将不会很漂亮。禁用外键和唯一键应该没问题，前提是您之后重新启用它们，并处理任何约束违规。
您需要多久执行一次此操作？您可以更频繁地加载较小的数据集吗？
To answer the last part of your question, no it won't just start working again; if the inserts are not replicated but subsequent updates are, the result will not be a pretty sight. Disabling foreign and unique keys should be OK, provided you re-enable them afterwards, and deal with any constraint violations.
How often do you have to do this? Can you load smaller datasets more frequently?