从我的写入表中删除所有 FK
我有一些非常繁重的写入密集型表（用户跟踪表），它们将不间断地写入。问题是在完全规范化的模式上，我将有 16 个外键。有些键纯粹用于查找引用，有些键则用于链接用户 ID、用户会话 ID、活动 ID 等。
在写入密集型表上有这么多 FK，性能就会成为问题。 （我有一个需要近乎实时更新的用户内容网站）。因此，我计划删除这些写入密集型表的所有 FK，但在此之前我想知道我还可以如何链接数据？当人们在代码中说，我们到底在代码级别做什么来将数据链接在一起，因为我假设在应用程序中我们不能建立关系？
其次，如果我不使用 FK，我假设只要写入正确的 ID，数据仍然会保持一致？不像如果会员 ID 是 2000 它会写 3000 而不是如果出于某种原因没有使用 FK？
最后，这不会影响连接吧？虽然我希望避免加入，但我可能需要一些。但我认为 FK 或不加入仍然可以按原样完成吗？
I have some very heavy write intensive tables (user tracking tables) which will be writing nonstop. Problem is on a fully normalized schema I will have 16 foreign keys. Some keys are purely for lookup references, some are imp like linking user ID, user session ID, activity ID, etc.
With this many FK on a write intensive table performance is an issue. (I have a user content website which needs near to real time updates). So I am planning to drop all FKs for these write intensive tables but before that I want to know how else can i link data? When people say in the code, what exactly are we doing at the code level to keep data linked together as i assume in the application we cannot have relationships?
Secondly, if I dont use FKs I assume data will still be consistent as long as the the corect ID is written? Not like if member ID is 2000 it will write 3000 instead if no FK is used for whatever reason?
Lastly, this will not effect joins right? While i hope to avoid joins I may need some. But i assume FKs or not joins can still be done as is?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的。
正确的。
这是真正的问题。实际上，真正真正的两个问题是：
1) 您对传入值都是有效且不需要检查的信心有多大。
2) 引用的查找表有多大？
如果答案“不是很自信”并且“非常小”，那么您可以通过在应用程序层缓存答案并在插入之前使用这些超快的内存表进行查找来在代码中强制执行。但是，考虑到这一点，数据库也会缓存这些小表，因此保留 fks 可能仍然更简单。
如果答案是“不太有信心”和“真的很大”，那么你就有一个选择。您可以删除 FK 约束，故意插入错误值并进行一些作业后清理，或者您可以将这些 FK 保留在数据库中，否则您将获得所有错误数据。
对于这种组合，在应用程序中缓存表是不切实际的，如果您删除 fk 并从应用程序中进行查找，它甚至比在数据库中包含 fk 还要慢。
如果答案是“100% 自信”，那么第二个问题就无关紧要了。删除 fk，快速、自信地插入数据。
Yes.
right.
This is the real question. Actually, the really real two questions are:
1) How confident are you that the incoming values are all valid and do not need to be checked.
2) How big are the lookup tables being referenced?
If the answers are "not very confident" and "really small" then you can enforce in code by caching the answers in the app layer and just doing lookups using these super-fast in-memory tables before inserting. however, consider this, the database will also cache those small tables, so it might still be simpler to keep the fks.
If the answers are "not very confident" and "really huge" then you have a choice. You can drop the FK constraints, knowingly insert bad values and do some post-job cleanup, or you can keep those fks in the database because otherwise you've got all of that bad data.
For this combination it is not practical to cache the tables in the app, and if you drop thee fks and do lookups from the app it is even slower than having fk's in the database.
If the answers are "100% confident" then the 2nd question does not matter. Drop the fk's and insert the data with speed and confidence.