MySQL主键设计原则

MySQL主键应当是对用户没有意义的

所有涉及到业务的字段，无论它看上去是否唯一，都决不能用作主键。例如，用户表的Email字段是唯一的，但是，如果用它作主键，就会导致其他表到处引用Email字段，从而泄露用户信息。

此外，修改Email实际上是一个业务操作，这个操作就直接违反了上一条原则。

那么，主键应该使用哪个字段呢？

主键必须使用单独的，完全没有业务含义的字段，也就是主键本身除了唯一标识和不可修改这两个责任外，主键没有任何业务含义。

在开发过程中，读者可能会看到将一些表使用有意义的字段表示主键，例如“用户登录信息表”将“登录名”（英文名）作为主键，“订单表”中将“订单编号”作为主键，如此设计主键一般都是没什么问题，因为将这些主键基本不具有“意义更改”的可能性。但是，也有一些例外的情况，例如“订单表”需要支持需求“订单可以作废，并重新生成订单，而且订单号要保持原订单号一致”，那将“订单编号”作为主键就满足不了要求了。因此读者在使用具有实际意义的字段作为主键时，需要考虑是否存在这种可能性。

类似的，看上去唯一的用户名、身份证号等，也不能用作主键。对这些唯一字段，应该加上unique索引约束。

MySQL主键应该是单列的，以便提高连接和筛选操作的效率

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

所谓的联合主键 就是指你表的主键含有一个以上的字段组成

比如

1
2
3
4
5
6
7

create table test 
( 
   name varchar(19), 
   id number, 
   value varchar(10), 
   primary key (name,id) 
)

上面的name和id字段组合起来就是你test表的复合主键

它的出现是因为你的name字段可能会出现重名，所以要加上ID字段这样就可以保证你记录的唯一性

一般情况下，主键的字段长度和字段数目要越少越好,没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升,可以用唯一索引替代.

使用联合的人通常有两个理由为自己开脱，而这两个理由都是错误的。其一是主键应当具有实际意义，然而，让主键具有意义只不过是给人为地破坏数据库提 供了方便。其二是利用这种方法可以在描述多对多关系的连接表中使用两个外部键来作为主键，我也反对这种做法，理由是：复合主键常常导致不良的外键，即当连接表成为另一个从表的主表，而依据上面的第二种方法成为这个表主键的一部分，然后,这个表又有可能再成为其它从表的主表，其主键又有可能成了其它从表主键的 一部分，如此传递下去，越靠后的从表，其主键将会包含越多的列了。

唯一索引与主键的区别分析

定义了UNIQUE约束的字段中不能包含重复值，可以为一个或多个字段定义UNIQUE约束,因此，UNIQUE即可以在字段级也可以在表级定义，在UNIQUE约束的字段上可以包含空值. ORACLE自动会为具有PRIMARY KEY约束的字段(主码字段)建立一个唯一索引和一个NOT NULL约束,定义PRIMARY KEY约束时可以为它的索引； UNIQUED 可空，可以在一个表里的一个或多个字段定义；

PRIMARY KEY不可空不可重复，在一个表里可以定义联合主键；简单的说, primary key = unique + not null

unique就是唯一,当你需要限定你的某个表字段每个值都唯一,没有重复值时使用. 比如说,如果你有一个person_Info表,并且表中有个身份证的column,那么你就可以指定该字段unique.

从技术的角度来看，Primary Key和Unique Key有很多相似之处。但还是有以下不同： 　　

  1. 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；
  2. 主键不允许为空值，唯一索引列允许空值；
  3. 一个表只能有一个主键，但是可以有多个唯一索引；
  4. 主键可以被其他表引用为外键，唯一索引列不可以；
  5. 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的差别

永远也不要更新MySQL主键

对于数据库来说，主键其实是可以修改的，只要不和其他主键冲突就可以。但是，对于应用来说，如果一条记录要修改主键，那就会出大问题。

因为主键的第二个作用是让其他表的外键引用自己，从而实现关系结构。一旦某个表的主键发生了变化，就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束（仅仅引用主键但并没有设置外键约束），修改主键会导致数据完整性直接被破坏。

最好使用整数类型

强烈建议使用 INT/BIGINT并且自增做为主键，顺序insert效率更高，表空间碎片率更低

主键避免采用字符型，如VARCHAR/CHAR/UUID，会导致原本可以顺序写入的请求变成随机写入，效率更低

业务主键与逻辑主键与复合主键

业务主键（自然主键）：在数据库表中把具有业务逻辑含义的字段作为主键，称为“自然主键(Natural Key)”。

逻辑主键（代理主键）：在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键，称为“代理主键”。

复合主键（联合主键）：通过两个或者多个字段的组合作为主键。

使用逻辑主键的主要原因:

  1. 业务主键一旦改变则系统中关联该主键的部分的修改将会是不可避免的，并且引用越多改动越大。而使用逻辑主键则只需要修改相应的业务主键相关的业务逻辑即可，减少了因为业务主键相关改变对系统的影响范围。业务逻辑的改变是不可避免的，因为“永远不变的是变化”，没有任何一个公司是一成不变的，没有任何一个业务是永远不变的。最典型的例子就是身份证升位和驾驶执照号换用身份证号的业务变更。而且现实中也确实出现了身份证号码重复的情况，这样如果用身份证号码作为主键也带来了难以处理的情况。当然应对改变，可以有很多解决方案，方案之一是做一新系统与时俱进，这对软件公司来说确实是件好事。

  2. 业务主键过大，不利于传输、处理和存储。我认为一般如果业务主键超过8字节就应该考虑使用逻辑主键了，因为int是4字节的，bigint是8字节的，而业务主键一般是字符串，同样是 8 字节的 bigint 和 8 字节的字符串在传输和处理上自然是 bigint 效率更高一些。想象一下 code == “12345678” 和 id == 12345678 的汇编码的不同就知道了。当然逻辑主键不一定是 int 或者 bigint ，而业务主键也不一定是字符串也可以是 int 或 datetime 等类型，同时传输的也不一定就是主键，这个就要具体分析了，但是原理类似，这里只是讨论通常情况。同时如果其他表需要引用该主键的话，也需要存储该主键，那么这个存储空间的开销也是不一样的。而且这些表的这个引用字段通常就是外键，或者通常也会建索引方便查找，这样也会造成存储空间的开销的不同，这也是需要具体分析的。

  3. 使用 int 或者 bigint 作为外键进行联接查询，性能会比以字符串作为外键进行联接查询快。原理和上面的类似，这里不再重复。

  4. 存在用户或维护人员误录入数据到业务主键中的问题。例如错把 RMB 录入为 RXB ，相关的引用都是引用了错误的数据，一旦需要修改则非常麻烦。如果使用逻辑主键则问题很好解决，如果使用业务主键则会影响到其他表的外键数据，当然也可以通过级联更新方式解决，但是不是所有都能级联得了的。

使用业务主键的主要原因:

  1. 增加逻辑主键就是增加了一个业务无关的字段，而用户通常都是对于业务相关的字段进行查找（比如员工的工号，书本的 ISBN No. ），这样我们除了为逻辑主键加索引，还必须为这些业务字段加索引，这样数据库的性能就会下降，而且也增加了存储空间的开销。所以对于业务上确实不常改变的基础数据而言，使用业务主键不失是一个比较好的选择。另一方面，对于基础数据而言，一般的增、删、改都比较少，所以这部分的开销也不会太多，而如果这时候对于业务逻辑的改变有担忧的话，也是可以考虑使用逻辑主键的，这就需要具体问题具体分析了。

  2. 对于用户操作而言，都是通过业务字段进行的，所以在这些情况下，如果使用逻辑主键的话，必须要多做一次映射转换的动作。我认为这种担心是多余的，直接使用业务主键查询就能得到结果，根本不用管逻辑主键，除非业务主键本身就不唯一。另外，如果在设计的时候就考虑使用逻辑主键的话，编码的时候也是会以主键为主进行处理的，在系统内部传输、处理和存储都是相同的主键，不存在转换问题。除非现有系统是使用业务主键，要把现有系统改成使用逻辑主键，这种情况才会存在转换问题。暂时没有想到还有什么场景是存在这样的转换的。

  3. 对于银行系统而言安全性比性能更加重要，这时候就会考虑使用业务主键，既可以作为主键也可以作为冗余数据，避免因为使用逻辑主键带来的关联丢失问题。如果由于某种原因导致主表和子表关联关系丢失的话，银行可是会面临无法挽回的损失的。为了杜绝这种情况的发生，业务主键需要在重要的表中有冗余存在，这种情况最好的处理方式就是直接使用业务主键了。例如身份证号、存折号、卡号等。所以通常银行系统都要求使用业务主键，这个需求并不是出于性能的考虑而是出于安全性的考虑。

使用复合主键的主要原因:

  1. 使用复合主键和使用业务主键是相关的，通常业务主键只使用一个字段不能解决问题，那就只能使用多个字段了。例如使用姓名字段不够用了，再加个生日字段。这种使用复合主键方式效率非常低，主要原因和上面对于较大的业务主键的情况类似。另外如果其他表要与该表关联则需要引用复合主键的所有字段，这就不单纯是性能问题了，还有存储空间的问题了，当然你也可以认为这是合理的数据冗余，方便查询，但是感觉有点得不偿失。

  2. 对于关系表来说必须关联两个实体表的主键，才能表示它们之间的关系，那么可以把这两个主键联合组成复合主键即可。如果两个实体存在多个关系，可以再加一个顺序字段联合组成复合主键，但是这样就会引入业务主键的弊端。当然也可以另外对这个关系表添加一个逻辑主键，避免了业务主键的弊端，同时也方便其他表对它的引用。

综合来说，网上大多数人是倾向于用逻辑主键的，而对于实体表用复合主键方式的应该没有多少人认同。支持业务主键的人通常有种误解，认为逻辑主键必须对用户来说有意义，其实逻辑主键只是系统内部使用的，对用户来说是无需知道的。

结论或推论：

1、尽量避免使用业务主键，尽量使用逻辑主键。

2、如果要使用业务主键必须保证业务主键相关的业务逻辑改变的概率为0，并且业务主键不太大，并且业务主键不能交由用户修改。

3、除关系表外，尽量不使用复合主键。

主键设计的常用方案

自增ID

优点:

  1. 数据库自动编号，速度快，而且是增量增长，聚集型主键按顺序存放，对于检索非常有利。
  2. 数字型，占用空间小，易排序，在程序中传递方便。

缺点：

  1. 不支持水平分片架构，水平分片的设计当中，这种方法显然不能保证全局唯一。

  2. 表锁 在MySQL5.1.22之前，InnoDB自增值是通过其本身的自增长计数器来获取值，该实现方式是通过表锁机制来完成的（AUTO-INC LOCKING）。锁不是在每次事务完成后释放，而是在完成对自增长值插入的SQL语句后释放，要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候，innoDB会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个表锁来锁住这个计数器，直到插入结束。如果大量的并发插入，表锁会引起SQL堵塞。 在5.1.22之后，InnoDB为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode： 0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking（表锁机制）。 1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行。 2：对所有的insert-like 自增长值的产生使用互斥量机制完成，性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。

  3. 自增主键不连续

    1
2
3
4
5
6

    Create Table: CREATE TABLE tmp_auto_inc (
id int(11) NOT NULL AUTO_INCREMENT,
talkid int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

    当插入10条记录的时候，因为AUTO_INCREMENT=16，所以下次再插入的时候，主键就会不连续。

max+1主键

由于增主键存在的缺点，所以有些开发人员就采用自己生成同样是数字型的的主键，但不是系统自动生成的。具体的方式是：在INSERT时，读取主键ID的Max值后，加1，这种方法可以避免自动编号的问题，但是也存在各种缺点：

  1. 效率问题：如果记录非常大的话，那么Max()也会影响效率的；

  2. 并发性问题：如果同时有两人读到相同的Max后，加一后插入的ID值会重复。

基于这些明显的缺点，笔者不提倡使用此种方式。

自制+1主键

考虑max+1的效率后，有的开发人员采用自制加1，也就是建一个特别的表，字段为：表名、当前序列值。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，但也可能会存在并发处理，但是开发人员可以采用lock线程的方式来避免：在生成此值的时，先Lock，取到值以后，再unLock出来，这样不会有两人同时生成了。这比max+1的速度要快多了。

就算解决了并发的问题，但此种方式同样存在致命的缺点：

在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与导入后的保持一致。

因为此种还需要创建新表，比较麻烦，因此笔者也不推荐此种方式。

UUID

优点：

  1. 全局唯一性、安全性、可移植性。
  2. 能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。
  3. 保证生成的ID不仅是表独立的，而且是库独立的，在切分数据库的时候尤为重要

缺点：

  1. 针对InnoDB引擎会徒增IO压力，InnoDB为聚集主键类型的引擎，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。

  2. UUID长度过长，一个UUID占用128个比特（16个字节）。主键索引KeyLength长度过大，而影响能够基于内存的索引记录数量，进而影响基于内存的索引命中率，而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

因为UUID的性能问题,不建议此种方式.

snowflake

分布式系统优先选择snowflake算法

参考:
https://www.imooc.com/article/43564
https://www.jianshu.com/p/d3ee16a89e73
http://www.voidcn.com/article/p-xzfhtvdp-brp.html
https://cloud.tencent.com/developer/news/268733