关于主键的那些事儿

1. 主键和外键

主键 就是主关键字(primary key, PK),它可以是表中的一个字段或由多个字段组合而成。
它的值用于唯一的标识表中的某一条记录。在两个表的关系中,主关键字用来在一个表中引用来自于另一个表中的特定记录。
外键 就是外关键字(foreign key, FK),如果某个关键字是两个关系的公共关键字,且在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。
举个简单的例子,比如在学生表中,学生id(或者叫学号)就是主键,因为它可以标识确定的一个学生。而姓名就不行,因为有可能会有同名的学生。在课程表中,课程id就是主键,因为它同样可以确定一门课程。
而在成绩表中,为了确定某个学生的某一门课程的成绩,就必须需要学号+课程id才能确定,这样的组合被称为联合主键。而学号和课程id又分别是学生表和课程表中的主键,于是它们又分别是成绩表中的外键。
2. 自增主键
自增主键 就是将一个主键定义为AUTO_INCREMENT,这样在生成数据的时候就不用再设置主键值,而是由数据库自动去按顺序填充。
那数据库是按照什么顺序填充的呢?
新插入的数据的主键值与当前的自增值有关,比如说当前的自增值是 Y,要插入的主键值是 X:
主键自增值的生成算法是:从auto_increment_offset(初始值)开始,以auto_increment_increment(步长)为步长,持续叠加,直到找到第一个大于X的值,作为新的自增值
那如果插入数据时偏要去设置自增主键的值会怎么样呢?
  • 如果库中已经有该主键值的数据了,那肯定会主键冲突,导致插入失败
  • 如果库中没有该主键值的数据,那么就可以插入,并将新的自增值设置为Y+步长
那如果自增主键值用完了会怎么样呢?
自增主键字段在达到定义类型上限后,如果再插入一行记录,主键不会再自增,所以会报主键冲突的错误
3. 为什么自增主键可能会不连续?
  1. 第一种情况就是上边提到过的,因为步长不为1,或者有特定值插入的情况
  2. 第二种情况就是插入失败,拿Mysql举例,当插入数据的时候,如果Mysql发现你没有设置主键,那么就会去取自增值作为主键,但是如果插入失败或者出现事务回滚的话,Mysql是不会回滚对应的自增主键的值的,这主要是出于对性能的考虑。
  3. 第三种情况就是对于批量插入的语句(select … insert,replace … select 和 load data 语句),Mysql有一个批量申请自增id的策略。语句执行过程中,第一次申请自增 id,会分配 1 个;1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。
4. 水平分表时为什么不推荐使用自增主键进行分表? 1. 需要提前做好分片规划,且要求自增主键必须连续,否则会造成资源的浪费
比如开始时规划成3个分片,每个分片承载1亿条数据。但是如果数据总共只有1.5亿条,则明显规划的不合适,而此时已经无法在运行期间进行动态扩展了,剩下的分片就会浪费。
另外,如果自增主键出现了不连续,则也会造成分片的浪费。
2. 在数据库集群的场景下,只能采用 “范围分片” 的方式进行分片,会产生 “尾部热点” 效应
比如还是上边的条件,现在已经承载了2.5亿条数据了,则前两个分片已经满了,所有的插入操作都会在第三个分片进行,对其压力是非常大的
5. 为什么不推荐使用UUID作为主键?
UUID 是指Universally Unique Identifier,翻译为中文是通用唯一识别码,UUID 的目的是让分布式系统中的所有元素都能有唯一的识别信息。如此一来,每个人都可以创建不与其它人冲突的 UUID,就不需考虑数据库创建时的名称重复问题。
UUID 是由一组32位数的16进制数字所构成,UUID 的十六个八位字节被表示为 32个十六进制数字,以连字号分隔的五组来显示,形式为 8-4-4-4-12,总共有 36个字符(即三十二个英数字母和四个连字号)。例如:
123e4567-e89b-12d3-a456-426655440000
因为UUID是无序的,将其作为主键会涉及大量 索引重排
什么是索引重排?
索引 在MySQL中也叫做 “键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。
索引的一般采用B+树的数据结构来存储,如果使用UUID作为主键,为了维护B+树的结构,需要对其他的数据进行重排,在大数据量场景下这种代价是非常大的。
6. 主键有什么好的选择吗? 一种分布式且有序的主键生成算法
雪花算法(Snowflake)是Twitter公司分布式项目中采用的ID生成算法
关于主键的那些事儿
文章图片

符号位(1位):没有实际用处,主要是为了兼容长整型的格式
时间戳(1位):本地的毫秒时间
机器ID(10位):数据库所在机器的编码,最大可以有1024个节点
序列(12位):序列的长度决定了一个节点1毫秒能产生的ID数量,最大可以有4096个ID
实现雪花算法时需要注意时间回拨带来的影响。
参考鸣谢: 【关于主键的那些事儿】哔哩哔哩:https://www.bilibili.com/vide...
主键不连续:https://baijiahao.baidu.com/s...
Mysql主键自增机制:https://zhuanlan.zhihu.com/p/...
UUID:https://www.jianshu.com/p/da6...
Mysql索引:https://www.cnblogs.com/bypp/...

    推荐阅读