改进批量插入
我有一个流程，除其他外，还需要对其所做的事情进行审核。此审计是对审计表的插入。我对其进行了基准测试，看来这次审核使流程运行缓慢（两个数量级）。
审计表上有一些索引，在进程结束后删除所有索引并重建它们是个好主意吗？
表结构为 id(fk) |文字| id（索引）| id（索引）|大约还有 10 个文本字段，
我有一个 50 的缓冲区，当它填满时，我将其全部作为批量插入插入。我总共插入了大约 300K 条记录。
I have a process, that among other things needs to keep an audit of what it does. This audit is an INSERT into an audit table. I benchmarked it and it seems that this audit is making the process run slow (two orders of magnitude).
The audit table has some indexes on it, would it be a good idea to drop ALL indexes and rebuild them once the process ends?
The table structure is id(fk) | text | id(indexed) | id (indexed) | about 10 more text fields
I have a buffer of 50, when it fills up, I insert it all as bulk insert. Overall I insert about 300K records.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
如果您使用的是 MyISAM，则可以使用 ALTER TABLE AuditTable DISABLE KEYS，然后在完成插入后重新启用您的键 (ALTER TABLEauditTable ENABLE KEYS)。但是，这只会禁用非唯一键。
http://dev.mysql.com/doc/refman/5.1 /en/alter-table.html
如果您有较大的 PK/唯一键，则删除键然后在导入后将它们添加回来可能会快得多。
您还可以尝试将审核记录写入平面文件，然后使用 LOAD DATA INFILE 将它们一次性导入。同样，在加载数据之前禁用或删除密钥将大大加快速度。
If you're using MyISAM, you can use ALTER TABLE auditTable DISABLE KEYS, then re-enable your keys (ALTER TABLE auditTable ENABLE KEYS) after you're done with your insert. However, this will only disable nonunique keys.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
If you have large PK/Unique keys, dropping the keys then adding them back after your import would likely be a lot faster.
You could also try writing your audit records to a flat file, then import them all at once using LOAD DATA INFILE. Again, disabling or droping your keys prior to the LOAD DATA will speed things up considerably.