Mysql 主键设计

最新推荐文章于 2025-03-24 09:29:34 发布

pakano

最新推荐文章于 2025-03-24 09:29:34 发布

阅读量783

点赞数 2

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_42170897/article/details/127759028

版权

mysql 专栏收录该内容

10 篇文章

订阅专栏

一、自增主键ID问题

1.可靠性不高

存在主键ID回溯，这个bug在mysql 8.0才修复。主键ID回溯就是: 创建一个表，主键ID且整形且AUTO_INCREMENT，然后插入三条数据，然后show create table 查看，此时的AUTO_INCREMENT为4，删除ID=3的数据，此时AUTO_INCREMENT仍为4，重启mysql-server，此时AUTO_INCREMENT就为3了。

2.安全性不好

对外接口很容易暴露业务信息，如/user/1接口，那么别人就知道用户ID是多少，新注册一个用户就知道用户数量是多少，轻易的使用爬虫工具进行数据的获取。

3.性能差

自增ID由服务器生成。在高并发插入数据时，存在AUTO_INCREMENT锁竞争问题（详见InnodBD内幕p262）。

4.交互多

业务还需要一次last_insert_id才能知道刚才插入数据的ID，这需要多一次的网络交互。这在高并发性能吃紧的场景下是及其浪费的。

5.局部唯一性

只能在当前数据库中唯一，而非全局唯一，对于核心业务表，这在分布式环境下是绝对不允许的。

二、业务字段作为主键的缺点

1.使用手机号：

手机号存在被运营商回收的情况。所以在设计表时，手机号允许为空。

2.使用身份证号：

身份证号是用户的隐私，一般情况下用户不会透露。所以在设计表时，身份证号允许为空。

3.使用卡号作为主键：

卡号	名称	电话	身份证	地址	性别	生日
10000001	张三	15465656311	500228113131313	美国	男	2222-01-01

当用户信息表使用会员ID为主键，则其他表一定关联着这张表，如消费表。但如果张三将卡移交给李四，则用户信息表中张三那一行会员ID不变，其他信息全部更换为李四，那么李四去查询消费记录时，一定会多出很多记录，这些记录就是张三遗留下来的，这显然是不合理的。尤其是在更加严谨的业务场景中。

总结：不要使用跟业务相关的字段作为主键，因为无法预测在项目的生命周期中，哪些业务字段会因项目的业务需求发生重复或重用的情况。

三、淘宝订单主键设计（猜测）

订单1 、2 、3、4 :

1550672064 7623 08113

1481195847 1803 08113

1431156171 142 3 08113

1431146631 5213 08113

由于时间戳是十位：所以猜测用户订单ID是时间戳（10）+去重字段（4）+用户ID后6位（6）。

这样的设计对于分布式系统查询比较友好。

四、推荐的主键ID设计

非核心业务：自增主键ID,如日志，监控。

核心业务：主键应全局唯一且单调递增。全局唯一是保证在各个系统中都是唯一的，单调递增有利于b+树的查询。

1.使用UUID

由于UUID具有全局唯一，无序的特性，所以在插入数据时，性能会很低，尤其是在表已经存在大量数据的情况下。

在UUID 中时间部分占用 60 位，存储的类似 TIMESTAMP 的时间戳，但表示的是从 1582-10-15 00: 00: 00:00 到现在的100ns 的计数。可以看到 UUID 存储的时间精度比 TIMESTAMPE 更高，时间维度发生重复的概率降低到1/100ns 。时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC 地址用于全局唯一。

为什么 UUID 占用 36 个字节？

UUID 根据字符串进行存储，设计时还带有无用 "-" 字符串，因此总共需要 36 个字节。

为什么 UUID 是随机无序的呢？

因为 UUID 的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序

2.改进的UUID

既然UUID作为主键只存在无序的缺点，那么将时间高位与地位置换，则可以得到全局唯一，递增的ID!在mysql 8.0中就支持这样的改进过的UUID，当然也可以使用其他应用包。

mysql8.0还去除了UUID无用字符“-”，并且将十六进制字符转化为二进制存储，所以得到的UUID长度为(36-4)/2 = 16字节。那么我们的主键可能定义为 ID char(16)即可。