mysql的主键选择

火冠蜂鸟

已于 2023-07-26 22:59:24 修改

阅读量370

点赞数

分类专栏： # mysql 文章标签： mysql 数据库

于 2023-07-25 22:19:23 首次发布

本文链接：https://blog.csdn.net/weixin_70280523/article/details/131906503

版权

mysql 专栏收录该内容

33 篇文章 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亿多），此时再插入数据，会提示主键重复错误。

三.主键的选择

1.规范

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

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

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

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

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

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

2)规范背后的原因

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

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

不是递增的。MySQL 中索引的数据结构是 B+Tree，这种数据结构的特点是索引树上的节点的数据是有序的，而如果使用 UUID 作为主键，那么每次插入数据时，因为无法保证每次产生的 UUID 有序，所以就会出现新的 UUID 需要插入到索引树的中间去，这样可能会频繁地导致页分裂，使性能下降。
太占用内存。每个 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 是自增的，则是为了在数据插入的过程中，尽可能的避免索引树上页分裂的问题。