代码之家 › 专栏 › 技术社区 › Chris Smith

了解奇数数据库密钥设计

database sql-server

Chris Smith · 技术社区 · 15 年前

背景

对于我们支持并编写SQL报告的主要供应商提供的产品,我很难理解数据库中的主键选择。一些不相关的细节已经改变了。

每个主表都有一个唯一的自动编号“内部ID”字段,但该字段并不总是用作主键。

我的问题

尤其是,我对订单和订单行表的主键选择感到困惑:

组织pk( organization_internal_id )
订单表PK (organization_internal_id, order_internal_id)
订单线PK (order_internal_id, organization_internal_id)

订单和订单行表都有一个 internal_id 也可以唯一地标识它们的行。每个表都使用聚集索引的主键。仅内部ID有非聚集索引。

为什么不将内部ID设为主键,而为每个ID分别设置一个唯一的聚集索引(就像现在这样)?

一种可能性

我能想到的一件事是,设计人员不知道主键和聚集索引之间的区别,所以使用奇怪的pk来获取他们想要的聚集索引。公平地说,订单行通常是参照订单访问的,并且在行级别之下没有详细信息可以参照 order_line_internal_id .

查询效率

与订单的聚集索引类似的一个问题是,它鼓励QO在将多个订单和订单行连接在一起时使用扫描和哈希联接(不幸的是,这种情况非常常见,即使我最后只使用了5%的数据)。选择的行太多,无法循环查找到订单行表中(尽管强制这样做有时会有所帮助),并且使组织成为订单键的第一部分会阻止合并联接在没有排序的情况下工作。

更多细节

这些内部ID仅用于联接到其他表;对于系统外部的引用或向用户显示的引用,有单独的外部ID。设计师们喜欢这些自动编号的人造钥匙。
数据库位于MS SQL Server 2000上
我认为供应商过去支持Oracle作为数据库
订单表有1米行~5兆字节
订单行表有30米行~1 GB
整个数据库为~100GB

3 回复 | 直到 15 年前

Community Navdeep Singh 7 年前

我认为你的“一种可能性”很有可能。显然,在过去的数据库设计时代,主键是唯一可用的唯一索引;引用的资料 this question about one-to-many relationship implementation 例如,似乎来自这样一个时代。似乎有理由认为在那个时代受过训练的一些人会保持自己的习惯。

CMB 15 年前

另外,考虑到内部ID可能是本公司产品中使用的ORM随此数据库一起使用的需求。

Cruachan 15 年前

假设它背后有一个明显的理性原因——这很容易是一个遗留问题,可以追溯到软件的一个古老版本,这个版本从来没有被改变过,但现在却深不可测——我建议最可能的解释是,设计者认为访问表通常是通过字段在他们使用的主键中,因此他们认为这将是最有效的查询。如果他们认为大多数时候只需要检索(组织内部ID、订单内部ID)字段,因此将这些字段放入主键中,以避免表读取,那么这可能对他们特别有吸引力。

逻辑运行类似于“我们知道我们通常只需要组织内部ID和从这个表中订购内部ID,所以如果我们将它用作pk,数据将直接检索,而不是强制访问实际的行数据,我们将通过任意的内部ID访问。”

这可能是真的,也可能不是真的,但是一旦这些决定是在这种软件中做出的,他们往往会坚持下去,除非改变它们的理由是令人信服的。

我对很久以前在Oracle Accounting IV中看到类似的构造有着模糊的记忆,所以它可能来自一些旧的Oracle方法(Oracle 6对这些东西相当敏感)。