如何优化表仅用于快速插入？
我有一个日志表，它将接收来自多个网络应用程序的插入内容。我不会对这些数据进行任何搜索/排序/查询。我将把数据提取到另一个数据库来运行报告。初始表严格用于接收日志消息。
有没有办法确保 Web 应用程序不必等待这些插入？例如，我知道添加大量索引会减慢插入速度，所以我不会。还有什么？我不应该添加主键吗？ （每天晚上，该表将被泵送到报告数据库，该数据库将有很多键/索引）
I have a log table that will receive inserts from several web apps. I wont be doing any searching/sorting/querying of this data. I will be pulling the data out to another database to run reports. The initial table is strictly for RECEIVING the log messages.
Is there a way to ensure that the web applications don't have to wait on these inserts? For example I know that adding a lot of indexes would slow inserts, so I won't. What else is there? Should I not add a primary key? (Each night the table will be pumped to a reports DB which will have a lot of keys/indexes)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果性能很关键，您可能不想将此数据写入数据库。我认为大多数事情都会将数据库写入处理为往返，但听起来您不想等待返回的确认消息。检查是否如 S. Lott 所建议的那样，将一行附加到某个简单文本文件中可能不会更快。
如果数据库写入速度更快（或者出于安全或其他业务/操作原因而有必要），我会在表上不放置任何索引，其中包括主键。如果它不会用于读取或更新，并且您不需要关系完整性，那么您就不需要在此表上进行 PK。
建议显而易见：作为夜间报告运行的一部分，清除表的内容。另外，永远不要重置数据库文件大小（老旧的收缩数据库命令）；经过一周左右的常规使用后，数据库文件应该达到所需的大小，并且您不必担心文件增长对性能的影响。
If performance is key, you may not want to write this data to a database. I think most everything will process a database write as a round-trip, but it sounds like you don't want to wait for the returned confirmation message. Check if, as S. Lott suggests, it might not be faster to just append a row to a simple text file somewhere.
If the database write is faster (or necessary, for security or other business/operational reasons), I would put no indexes on the table--and that includes a primary key. If it won't be used for reads or updates, and if you don't need relational integrity, then you just don't need a PK on this table.
To recommend the obvious: as part of the nightly reports run, clear out the contents of the table. Also, never reset the database file sizes (ye olde shrink database command); after a week or so of regular use, the database files should be as big as they'll ever need to be and you won't have to worry about the file growth performance hit.
这里有一些想法，请注意最后一个很重要，您将拥有极高的容量：
Here are a few ideas, note for the last ones to be important you would have extremly high volumns:
没有钥匙，
没有任何限制，
没有验证，
没有触发器，
无计算列
如果可以，请让服务异步插入，以便不等待结果（如果可以接受）。
您甚至可以尝试插入“每日”表，该表的记录应该更少，
然后在晚上批处理运行之前将其移动。
但大部分在表上没有键/验证（PK 和唯一索引会杀了你）
No keys,
no constraints,
no validation,
no triggers,
No calculated columns
If you can, have the services insert async, so as to not wait for the results (if that is acceptable).
You can even try to insert into a "daily" table, which should then be less records,
and then move this across before the batch runs at night.
But mostly on the table NO KEYS/Validation (PK and Unique indexes will kill you)