MySQL数据库主键设计原则

⽬录

1. 主键定义... 52. 主键设计原则... 52.1 确保主键的⽆意义性... 52.2 采⽤整型主键... 52.3 减少主键的变动... 52.4 避免重复使⽤主键... 62.5 主键字段定义区分... 63. 主键⽅案... 63.1 ⾃增ID.. 63.2 UUID.. 7

3.3 ID物理主键+UUID逻辑主键... 74. 总结... 8

1. 主键定义

表中经常有⼀个列或多列的组合，其值能唯⼀地标识表中的每⼀⾏。这样的⼀列或多列称为表的主键，通过它可强制表的实体完整性。

2. 主键设计原则

总原则：根据数据库表的具体使⽤范围来决定采⽤不同的表主键定义。

2.1 确保主键的⽆意义性

在开发过程中，有意义的字段例如“⽤户登录信息表”将“登录名”（英⽂名）作为主键，“订单表”中将“订单编号”作为主键，如此设计主键⼀般都是没什么问题，因为将这些主键基本不具有“意义更改”的可能性。

但是，也有⼀些例外的情况，例如“订单表”需要⽀持需求“订单可以作废，并重新⽣成订单，⽽且订单号要保持原订单号⼀致”，那将“订单编号”作为主键就满⾜不了要求了。

因此在使⽤具有实际意义的字段作为主键时，需要考虑是否存在这种可能性。

要⽤代理主键，不要使⽤业务主键。任何⼀张表，强烈建议不要使⽤有业务含义的字段充当主键。我们通常都是在表中单独添加⼀个整型的编号充当主键字段。

2.2 采⽤整型主键

主键通常都是整数，不建议使⽤字符串当主键。（如果主键是⽤于集群式服务，可以采⽤字符串类型）

2.3 减少主键的变动

󰀀 主键的值通常都不允许修改，除⾮本记录被删除。

2.4 避免重复使⽤主键

󰀀 主键的值通常不重⽤，意味着记录被删除后，该主键值不再使⽤。

2.5 主键字段定义区分

主键不要直接定义成【id】，⽽要加上前缀，定义成【表名id】或者【表名_id】

3. 主键⽅案

3.1 ⾃增ID

优点：

n 数据库⾃动编号，速度快，⽽且是增量增长，聚集型主键按顺序存放，对于检索⾮常有利。n 数字型，占⽤空间⼩，易排序，在程序中传递⽅便。 缺点：

n 当系统与其他系统集成时，需要数据导⼊时，很难保证原系统的ID不发⽣主键冲突。在多个数据库间进⾏数据的复制时（SQL Server的数据分发、订阅机制允许我们进⾏库间的数据复制操作），⾃动增长式字段可能造成数据合并时的主键冲突及表关联关系的丢失。n 如果其他系统主键不是数字型，会导致修改主键数据类型，导致其他相关表的修改。n 在数据缓冲模式下，很难预先填写主键与外键的值。

n ⾃增量的值都是需要在系统中维护⼀个全局的数据值，每次插⼊数据时即对此次值进⾏增量取值。当在产⽣唯⼀标识的并发环境中，每次的增量取值都必须为此全局值加锁解锁以保证增量的唯⼀性。造成并发瓶颈，降低查询性能。每创建⼀条记录都需要对表加⼀次锁，在⾼并发环境下开销较⼤。

3.2 UUID

UUID是指在⼀台机器上⽣成的数字，它保证对在同⼀时空中的所有机器都是唯⼀的。在UUID的算法中，可能会⽤到诸如⽹卡MAC地址，IP，主机名，进程ID等信息以保证其独⽴性。 优点：

n 全局唯⼀性、安全性、可移植性。

n 能够保证独⽴性，程序可以在不同的数据库间迁移，效果不受影响。

n 保证⽣成的ID不仅是表独⽴的，⽽且是库独⽴的，在你切分数据库的时候尤为重要。 缺点：

n InnoDB为聚集主键类型的引擎，数据会按照主键进⾏排序，由于UUID的⽆序性，InnoDB会产⽣巨⼤的IO压⼒。InnoDB主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。

n 作为主键，UUID长度过长，主键索引KeyLength长度过⼤，⽽影响能够基于内存的索引记录数量，进⽽影响基于内存的索引命中率，⽽基于硬盘进⾏索引查询性能很差。严重影响数据库服务器整体的性能表现。

3.3 ID物理主键+UUID逻辑主键

InnoDB不适合使⽤UUID做物理主键，可以把它作为逻辑主键，物理主键依然使⽤⾃增ID。

主键仍然⽤auto_increment_int来做，⽽另加⼀个uuid做唯⼀索引，表外键关联什么的，还 ⽤uuid来做，也就是说auto_increment_int只是⼀个形式上的主键，⽽uuid才是事实上的主键，这样，⼀⽅⾯int主键不会浪费太多空间，另⼀⽅⾯，还可以继续使⽤uuid。 优点：

n InnoDB会对主键进⾏物理排序，这对auto_increment_int类型有好处，因为后⼀次插⼊的主键位置总是在最后。但是对uuid来说则有缺点，因为uuid是杂乱⽆章的，每次插⼊的主键位置是不确定的，可能在开头，也可能在中间，在进⾏主键物理排序的时候，势必会造成⼤量的 IO操作影响效率。 缺点：

n 同⾃增ID的缺点：全局值加锁解锁以保证增量的唯⼀性带来的性能问题。

4. 总结

本⽂主要针对MySQL数据库中的InnoDB存储引擎的主键设计原则进⾏调研，挑选了⼏种主流的主键⽅案进⾏优缺点的分析和对⽐，并最终建议选择⾃增ID作为物理主键，同时使⽤UUID作为逻辑主键的⽅案。

如果⼀些特殊的表，⽐如说⽇志表，其不需要维护，可以采⽤数据库⾃动增长ID的⽅式。这种⽅式性能好，产⽣也很⽅便。但是维护很⿇烦。