数据库设计和性能
我目前正在设计一个数据库。我们现有的系统设计得不是很好，我们正在解决所有明显可见的小问题，例如 varchar 代替 true/false 标志位。
我想知道的是，你怎么知道你已经有了一个甜蜜的数据库设计？或者这是一个神话？我的意思是，这个结构在纸面上看起来可能很神奇，但是当其中有一些数据时，它会如何执行。
存储“查找”值的表比存储完整描述性文本更快吗？例如，
错误表
Id ErrorId DateCreated
1 1 09/12/2011
2 5 10/12/2011
错误描述表
Id Description
1 Warning - failed to validate
2 Failed to locate file
在这种情况下，创建一个视图会比编写包含必要连接的 SQL 更有利吗？
抱歉，如果我把这个问题发布在错误的地方。
I'm currently in the process of designing a database. We have an existing system that was not designed very well and we are ironing out all of the small issues that are clearly visible, e.g. a varchar for a true/false flag instead of a bit.
What I want to know is, how do you know that you have got a sweet database design? Or is this a myth? I mean, the structure may look amazing on paper, but when some data is in there how will it perform.
Are tables that store "lookup" values faster than storing full descriptive text? e.g.
Error table
Id ErrorId DateCreated
1 1 09/12/2011
2 5 10/12/2011
Error Description table
Id Description
1 Warning - failed to validate
2 Failed to locate file
In this scenario, would creating a view be more beneficial than writing the SQL including the necessary join?
Sorry if I have posted this question in the wrong place.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我们在我工作的地方进行了测试。 （有点。我们没有将查找表与任何其他类型的表区分开来。）但是让我指出，您的问题与查找表无关；而是关于查找表。您的问题是关于代理键（id 号）。您可以创建一个“查找”表而不使用 ID 号。
有关计时的示例，请参阅这个问题。看来有一个转折点了。在临界点以下，基于自然键的查询通常比基于 ID 号的查询运行得更快。 （更窄的表和更少的连接。）但是过了临界点，连接代理键比自然键运行得更快。
但“更快”并不一定意味着“快”。而且“慢一点”可能仍然足够快。
We tested that where I work. (Kind of. We didn't distinguish lookup tables from any other kind of table.) But let me point out that your question isn't about lookup tables; your question is about surrogate keys (id numbers). You can create a "lookup" table without using id numbers.
For an example with timings, see this SO question. It seems there's a tipping point. Below the tipping point, queries based on natural keys will usually run faster than queries based on id numbers. (Narrower tables and fewer joins.) But past the tipping point, joins surrogate keys run faster than natural keys.
But "faster" doesn't necessarily mean "fast". And "slower" might still be fast enough.