mysql关于表设计的主键策略

快来打我* 2022-07-13 03:52 268阅读 0赞

# 1.定义 #

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

# 2.主键设计原则 #

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

## 2.1 确保主键的无意义性 ##

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

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

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

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

## 2.2 采用整型主键 ##

主键通常都是整数，不建议使用字符串当主键。（如果主键是用于集群式服务，可以采用字符串类型），但是这个在一年的开发中又会出现一些问题，就是因为在开发的过程中，因为数据库的id是自增的，所以别有用心的用户可能利用这个自增的特点，对数据库数据非法的读取或者侵入

## 2.3 减少主键的变动 ##

主键的值通常都不允许修改，除非本记录被删除。且删除了id号不会再被其他的数据所占用

## 2.4 避免重复使用主键 ##

主键的值通常不重用，意味着记录被删除后，该主键值不再使用。（即，删除了id号为3的数据，那么无论怎么加，数据的id号都不能再为3）。

## 2.5 主键字段定义区分 ##

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

# 3.主键方案 #

## 3.1 自增ID ##

优点：

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

缺点：

*  当系统与其他系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突。在多个数据库间进行数据的复制时（SQL Server的数据分发、订阅机制允许我们进行库间的数据复制操作），自动增长式字段可能造成数据合并时的主键冲突及表关联关系的丢失。
 *  如果其他系统主键不是数字型，会导致修改主键数据类型，导致其他相关表的修改。
 *  在数据缓冲模式下，很难预先填写主键与外键的值。
 *  自增量的值都是需要在系统中维护一个全局的数据值，每次插入数据时即对此次值进行增量取值。当在产生唯一标识的并发环境中，每次的增量取值都必须为此全局值加锁解锁以保证增量的唯一性。造成并发瓶颈，降低查询性能。每创建一条记录都需要对表加一次锁，在高并发环境下开销较大。

## 3.2 UUID ##

UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。在UUID的算法中，可能会用到诸如网卡MAC地址，IP，主机名，进程ID等信息以保证其独立性。

优点：

*  全局唯一性、安全性、可移植性。
 *  能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。
 *  保证生成的ID不仅是表独立的，而且是库独立的，在你切分数据库的时候尤为重要。
 *  且可以保护数据的完整性，不会被推测到主键规则保护数据

缺点：

*  InnoDB为聚集主键类型的引擎，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关（簇类索引），uuid  
    主键可能会引起数据位置频繁变动，严重影响性能。
 *  作为主键，UUID长度过长，主键索引KeyLength长度过大，而影响能够基于内存的索引记录数量，进而影响基于内存的索引命中率，而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

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

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

主键仍然用auto\_increment\_int来做，而另加一个uuid做唯一索引，表外键关联什么的，还用uuid来做，也就是说auto\_increment\_int只是一个形式上的主键，而uuid才是事实上的主键，这样，一方面int主键不会浪费太多空间，另一方面，还可以继续使用uuid。

优点：

*  InnoDB会对主键进行物理排序，这对auto\_increment\_int类型有好处，因为后一次插入的主键位置总是在最后。但是对uuid来说则有缺点，因为uuid是杂乱无章的，每次插入的主键位置是不确定的，可能在开头，也可能在中间，在进行主键物理排序的时候，势必会造成大量的IO操作影响效率。

缺点：

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

# 4.总结 #

本文主要针对MySQL数据库中的InnoDB存储引擎的主键设计原则进行调研，挑选了几种主流的主键方案进行优缺点的分析和对比，并最终建议选择自增ID作为物理主键，同时使用UUID作为逻辑主键的方案。  
如果一些特殊的表，比如说日志表，其不需要维护，可以采用数据库自动增长ID的方式。这种方式性能好，产生也很方便。但是维护很麻烦。