mysql的主键选择

快来打我* 2024-03-22 20:19 97阅读 0赞

### **一.没有定义主键有什么问题** ###

*  如果定义了主键，那么InnoDB会使用主键作为聚簇索引
 *  如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
 *  如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个不可见的名为row\_id的列名为GEN\_CLUST\_INDEX的聚簇索引，该列是一个6字节的自增数值，随着插入而自增--补充：该全局row\_id在代码实现上使用的是bigint unsigned类型，但实际上只给row\_id留了6字节，这种设计就会存在一个问题：如果全局row\_id一直涨，一直涨，直到2的48幂次-1时，这个时候再+1，row\_id的低48位都为0，结果在插入新一行数据时，拿到的row\_id就为0，存在主键冲突的可能性。

##### **自动生成的名为row\_id主键有什么问题** #####

*  使用不了主键索引，查询会进行全表扫描
 *  影响数据插入性能，插入数据需要生成ROW\_ID，而生成的ROW\_ID是全局共享的（InnoDB 维护了一个全局的 dictsys.row\_id，所有未定义主键的表都共享该row\_id），并发会导致锁竞争，影响性能

### 二.有主键,但是主键达到最大值有什么问题 ###

如果申明了用int类型的数据库自增的主键，当主键达到最大值，再插入则主键不会再增长，而是报主键重复错误。  
MySQL主键当达到最大值（如果为int类型，最大值为21亿多），此时再插入数据，会提示主键重复错误。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXIyMDI1_size_16_color_FFFFFF_t_70][]

### 三.主键的选择 ###

#### 1.规范 ####

##### 1)规范推荐使用**int,bigint 无符号做自增键** #####

在《阿里巴巴 Java 开发手册》第五章 MySQL 规定第九条中，强制规定了单表的主键 id 必须为无符号的 bigint 类型，且是自增的。

![format_png][]

MySQL开发规范中经常可以看到：

*  推荐使用int,bigint 无符号做自增键
 *  禁止使用uuid做主键

关于主键的类型选择上最常见的争论是用整型还是字符型的问题，关于这个问题《高性能MySQL》一书中有明确论断：  
整数通常是标识列的最好选择，因为它很快且可以使用AUTO\_INCREAMENT,如果可能，应该避免使用字符串类型作为标识列，因为很消耗空间，且通常比数字类型慢。

如果是使用MyISAM，则就更不能用字符型，因为MyISAM默认会对字符型采用压缩引擎，从而导致查询变得非常慢。

##### 2)规范背后的原因 #####

通常主键 id 的数据类型有两种选择：字符串或者整数，主键通常要求是唯一的，如果使用字符串类型，我们可以选择 UUID 或者具有业务含义的字符串来作为主键。

对于 UUID 而言，它由 32 个字符+4 个'-'组成，长度为 36，虽然 UUID 能保证唯一性，但是它有两个致命的缺点：

1.  不是递增的。MySQL 中索引的数据结构是 B+Tree，这种数据结构的特点是索引树上的节点的数据是有序的，而如果使用 UUID 作为主键，那么每次插入数据时，因为无法保证每次产生的 UUID 有序，所以就会出现新的 UUID 需要插入到索引树的中间去，这样可能会频繁地导致页分裂，使性能下降。
2.  太占用内存。每个 UUID 由 36 个字符组成，在字符串进行比较时，需要从前往后比较，字符串越长，性能越差。另外字符串越长，占用的内存越大，由于页的大小是固定的，这样一个页上能存放的关键字数量就会越少，这样最终就会导致索引树的高度越大，在索引搜索的时候，发生的磁盘 IO 次数越多，性能越差。

对于整数的数字类型，MySQL 中主要有 int 和 bigint 类型。其中 int 占用 4 个字节，bigint 占用 8 个字节，这和 Java 中的 int 和 long 对应。如果使用无符号的 int 类型作为主键，那么主键的最大值为 2^32-1，即 4294967295，这个值不到 43 亿，似乎有点太小了。虽然一张表的数据，我们不可能让其达到 43 亿条（太大会影响性能），但是对于频繁进行插入、删除的表来说，43 亿这个值是可以达到的。而如果使用无符号的 bigint 类型的话，主键的最大值可以达到 2^64-1，这个数足够大了，如果以每秒插入 100 万条数据计算的，58 万年以后才能达到最大值。所以 bigint 作为主键的数据类型，完全不用担心超过最大值的问题。

而强制要求主键 id 是自增的，则是为了在数据插入的过程中，尽可能的避免索引树上页分裂的问题。

#### 2.介绍下long和雪花id和uuid ####

##### **1)主键id：** #####

![format_png 1][]

tinyint、smallint、mediumint，这三个不常用就不说了。无符号是设置了 unsigned 属性，表示不允许负值，这大致可以使正数的上限提高一倍。

以无符号int类型为例，42亿虽然看起来是个很大的数字，但是对于一些插入删除很频繁的业务来说，并非无法触达这个上限。特别是有的业务表设置的步长比较大，会导致id自增的速度更快。如果你的业务预期会产生很多数据，那么建议你在创建表时，直接使用bigint。

因为MySQL的主键策略：id自增值达到上限以后，再申请下一个 id 时，仍然是最大值,就会报主键重复错误。

如果bigint真的还不够使用的话，我们可以使用雪花算法生成的id做主键，由于其也是大致递增的，对性能也不会产生影响，只需要由bigint改成更大范围的decimal就行。

**2)雪花id：**

我之前文章已有介绍

##### **3)UUID：** #####

我之前文章已有介绍

#### 3.实战： ####

在mysql新建3张结构一模一样的表

**1)效率测试结果**

每个表新增10w,30w,100w数据

![dbc4965e0d76457cba55d1f0dc51aafb.png][]

在已有数据量为130W的时候：我们再来测试一下插入10w数据，看看会有什么结果：

![d2fa874ebbb14bbbb7556de5a9d31f81.png][]

可以看出在数据量100W左右的时候,uuid的插入效率垫底，并且在后序增加了130W的数据，uudi的时间又直线下降。时间占用量总体可以打出的效率排名为：auto\_key>random\_key>uuid,uuid的效率最低，在数据量较大的情况下，效率直线下滑。

#### **4.使用自增id的缺点** ####

那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：

①：别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况

②：对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

③：Auto\_Increment锁机制会造成自增锁的抢夺,有一定的性能损失

**5.总结**

总结:总体来看,效率简单排名为：auto\_key>random\_key>uuid,

但是,根据自己项目的需求,权衡利弊,选择三个中的一个就行了.

\----------------------------------------------------------------------------------------------------

[为什么?mysql不推荐使用uuid或者雪花id作为主键? - 知乎 (zhihu.com)][mysql_uuid_id_ - _ _zhihu.com]  
链接：https://juejin.cn/post/7206197077909782588  
来源：稀土掘金  
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXIyMDI1_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/2e8822583c3f404c9e145ec0ff2c9db3.png
[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/a8a753c046d34055af0de3d3f22eb94f.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/212d6a896ed34129bb9a56b83b013128.png
[dbc4965e0d76457cba55d1f0dc51aafb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/ebc29f4bd2854b9785abd4a5a9166bac.png
[d2fa874ebbb14bbbb7556de5a9d31f81.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/8b7f9b86a33b48ddb5eaf2a4501668b9.png
[mysql_uuid_id_ - _ _zhihu.com]: https://zhuanlan.zhihu.com/p/250117430