数据库设计 -- 主键设计

数据库设计 -- 主键设计

 

 

       在数据库设计时，主要就是对实体和关系的设计，实体表现出来就是表，关系表现出来就是外键。而对于一个表，由两部分组成：主键和属性。主键的简单定义就是表中为每一行数据的唯一标识。其实更准确的说法，每一行数据的唯一标识是候选键（Candidate Key），一个表中可以有很多个候选键，主键是候选键中的一个，主要用于更方便的检索和管理数据。一个表中可以有多个候选键，但是只有一个主键。由于主键常常用于检索数据，也用于表之间的关联，所以主键的设计的好坏将会严重影响数据操作的性能。

 

 

 

 

主键的数据类型

 

• 数字类型
• 固定长度的字符类型
• GUID类型。

 

一般对主键有以下两个要求：

 

• 越短越好: 越短在一个Page中存储的节点越多，检索速度就越快。
• 顺序增长: 如果每一条插入的数据的主键都比前面的主键大，那么B-Tree上的节点也是顺序增长的，不会造成频繁的B-Tree分割。

 

• 越短越好是为了查询的速度快
• 顺序增长是为了插入的速度快

 

主键类型分析：

 

• 数字类型：MySQL的话，一般用int就够了，可以用数据库自增或系统自己做自增。

• 字符类型：基本不满足前面提到的2点要求，字符类型一般不会很短，而且也很可能不是顺序增长的，所以不是特别推荐的主键类型。当然如果确实业务需求使用字符类型，那么也尽量使用char(XX)而不要使用varchar(XX)，因为在RDBMS中，对于定长字符串和变成字符串的数据结构和处理是不一样的，varchar的性能更差。

• GUID类型：这个类型并不是所有数据库都有对应的数据类型，SQL Server有uniqueidentifier，MySQL没有。GUID类型在SQL Server中是16个字节，不算短，比4个字节的Int32长多了。在插入新数据时，GUID一般都是使用NewId()这样的生成随机GUID的方式生成的，所以也不是顺序增长的，在插入速度上不会很快。当然也可以系统做UUID。

 

问题：通过上面的比较，我们知道使用数字类型是更好的方式，那么我们为什么还会有人使用GUID和字符串来当主键呢？

 

• 字符串或GUID是唯一的，适合分布式系统
• 数据导入时不容易出现冲突
• 降低插入速度，不影响查询速度

 

 

 

数据库主键与业务主键

 

哪些情况可以拿来做主键

 

• 一种方案是再新建一个独立的字段作为主键，该字段并没有业务含义，只是一个自增列或者流水号，用于唯一标识每一行数据，这是数据库主键。

• 另外一种方案是选择其中较短较常用的属性作为主键，这是业务主键。

 

建议

 

       不要使用任何有业务含义的字段作主键，而是使用一个自增的（或者系统生成的）没有实际业务意义的字段作为主键。

 

 

为什么？

 

• 具有业务意义的字段很可能是用户从系统录入的，不要信任用户的任何输入，只要是用户自己录入的，那么就很有可能录错了，如果发现录入错误，这个时候再对主键进行修改，将会涉及到大量关联的外键表的修改，是很麻烦的一件事情。比如在做人员表的时候，就不要使用员工号或者身份证号做主键。

• 具有业务意义的字段虽然在当前阶段是唯一的，是不变的，但是并不能保证随着公司政策变动、业务调整等原因，导致该业务字段需要修改，以满足新的业务要求，这个时候要修改主键也是很麻烦的事情。比如部门表，我们以部门Code作为主键，但是后来部门变动，Code修改，则系统部门表的主键也得更改。

• 还有一个原因是业务主键在数据录入的时候不一定是明确知道的，有时我们会在不知道业务主键的情况下，就录入其他相关信息，这个时候，如果使用业务主键做数据库的主键，那么数据将无法录入。比如员工表把员工号作为主键，那么员工还没有入职，没有员工号的时候，HR需要先维护一些该预入职员工的信息是不可能的。

 

 

 

联合主键

 

       联合主键就是以多个字段来唯一标识每一行数据。前面已经说到主键应该越短越好，而且是建议是一个没有意义的自增列，那么是不是就不会再需要联合主键呢？答案是否定的，我们仍然可能会使用到联合主键。联合主键主要使用在多对多的关系时，中间表就需要使用联合主键。在简单的多对多关系中，我们不需要为中间的关联建立实体，所以中间表可能就只需要两列，分别是两个实体表的主键。

 

 

 

 

主键值的生成

 

 

自增

 

    数据库提供的自增长功能，新建一个ID字段，自动增长，非常方便也满足主键的原则。

 

    优点：

 

• 速度快
• 增量增长，聚集型主 键按顺序存放，对于检索非常有利
• 数字型的，占用空间小，易排序，在程序中传递也方便

    缺点：

 

• 数据导入时，怕出现ID冲突
• 对于双主数据库，要重新设计增长，错开以免ID冲突

 

 

自制Max加一

 

    可以使用lock线程的方式，来避免同时拿到最大值；也可以将最大值放到redis，每次通过redis进行加一，因为redis时单线程而且适度非常快。

 

    优点：

 

• 可以解决在分布式系统中生成主键ID的问题
• 解决双主数据库ID冲突问题
• 可以用数字型，可以用字符串类型

    缺点：

 

• 数据导入时依然会怕ID冲突
• 插入速度会比前面慢一点

 

 

GUID主键

 

    目前一个比较好的主键是采用GUID，当然我是推荐主键还是字符型的，但值由GUID生成，GUID是可以自动生成，也可以程序生成，而且键值不可能重 复，可以解决系统集成问题，几个系统的GUID值导到一起时，也不会发生重复，而且效率很高。

 

    优点：

 

• 都是唯一的，导入数据不会出现冲突，便于数据迁移

    缺点：

 

• GUID 值较长，不容易记忆和输入
• 值是随机、无顺序的
• 插入比前面慢点，查询应该差不多

 

 

Sequence对象

 

       这是Oracle常用的主键生成方式，现在SQL Server已支持。主要是在数据库中有一个Sequence对象，通过该对象生成主键。

 

 

 

Hilo值

 

       这是一种使用高低位算法生成的数字值的主键。该值由NHibernate程序内部生成。

 

 

 

 

主键与索引

 

       在概念和作用上，主键与索引是完全两个不同的东西，但是由于我们大部分情况下都是使用主键检索数据，所以大部分数据库的默认实现，在建立主键时会自动建立对应的索引。

 

       以MySQL为例，默认情况下，建立主键的列，就会建立聚集索引，但是实际上，我们可以在建立主键时不使用聚集索引。另外还有一个唯一约束（索引）的概念，该索引中的数据必须是唯一不能重复的，感觉和主键的意义一样，但是还是有一点点区别。

 

• 主键是只能由一个，而唯一约束（索引）在一个表中可以有多个。
• 主键不能为空，而唯一约束（索引）是可以为空的。