文章目录
mysql-主键设计
1 主键定义
表中经常有一个列或多列的组合,其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可强制表的实体完整性。
2 主键设计原则
总原则:根据数据库表的具体使用范围来决定采用不同的表主键定义。
数据库设计的三大范式
◆ 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列。简而言之,第一范式就是无重复的列
◆ 第二范式(2NF):首先要满足它是1NF,另外还需要包含两部分内容:一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。简而言之,第二范式就是非主属性非部分依赖于主关键字
◆ 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
反范式主键的设计原则
◆ 主键应当是对用户没有意义的。业务上的‘主键’可以通过唯一键(Unique Key)或唯一索引(Unique Index)和其它约束条件实现
◆ 主键应该是单列的,以便提高连接和筛选操作的效率
◆ 不要更新主键。实际上,因为主键除了惟一地标识一行之外再没有其他的用途了,所以也就没有理由去对它更新。另外,主键的值通常不重用,意味着记录被删除后,该主键值不再使用
◆ 主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等
◆ 主键应当由计算机自动生成。
反范式跟范式所要求的正好相反,在反范式的设计模式,我们可以允许适当的数据的冗余,用这个冗余去取操作数据时间的缩短。本质上就是用空间来换取时间,把数据冗余在多个表中,当查询时可以减少或者是避免表之间的关联(手册中也有禁止三表以上 JOIN 的条款)。
但需要注意的是,采用代理主键是为了避免业务逻辑变化导致主键变更,以及提高 JOIN 效率等。但在实际查询中,数据查询还是需要通过业务上的唯一键进行匹配的,而不应该将代理主键作为查询条件,尤其不能将代理主键作为查询条件输入项提供给用户。
总体来说,实际应用中应当具体问题具体分析,结合范式和反范式两种设计思想。对数据一致性和完整性较高、而对查询效率要求并不严格的地方,应当更倾向于遵从范式;而类似分布式、高并发集群的场景,则应当更考虑反范式的设计方案。具体主键的选取原则与策略,请参考文章 Choose a Primary Key: Natural or Surrogate
2.1 确保主键的无意义性
在开发过程中,有意义的字段例如“用户登录信息表”将“登录名”(英文名)作为主键,“订单表”中将“订单编号”作为主键,如此设计主键一般都是没什么问题,因为将这些主键基本不具有“意义更改”的可能性。
但是,也有一些例外的情况,例如“订单表”需要支持需求“订单可以作废,并重新生成订单,而且订单号要保持原订单号一致”,那将“订单编号”作为主键就满足不了要求了。
因此在使用具有实际意义的字段作为主键时,需要考虑是否存在这种可能性。
要用代理主键,不要使用业务主键。任何一张表,强烈建议不要使用有业务含义的字段充当主键。我们通常都是在表中单独添加一个整型的编号充当主键字段。
2.2 采用整型主键
主键通常都是整数,不建议使用字符串当主键。(如果主键是用于集群式服务,可以采用字符串类型)
2.3 减少主键的变动
主键的值通常都不允许修改,除非本记录被删除。