MySQL主键设计

4,723 阅读8分钟

今日格言:让一切回归原点,回归最初的为什么。

本篇讲解 Mysql 的主键问题,从为什么的角度来了解 Mysql 主键相关的知识,并拓展到主键的生成方案问题。再也不怕被问到 Mysql 时只知道 CRUD 了。

一、为什么需要主键

  1. 数据记录需具有唯一性(第一范式)
  2. 数据需要关联 join
  3. 数据库底层索引用于检索数据所需

以下废话连篇,可以直接跳过到下一节。

信息是用来消除随机不定性的东西”(香农)。人通过获得、识别自然界和社会的不同信息来区别不同事物,得以认识和改造世界。数据是反映客观事物属性的记录,是信息的具体表现形式。数据经过加工处理之后,就成为信息;而信息需要经过数字化转变成数据才能存储和传输。数据库就是用于存储数据记录的。既已如此,记录便是具有确定性(相对)的信息,其确定性即唯一性。我们得出第一条原因:

1.数据记录需具有唯一性

世界是由客观存在及其关系组成的。数据是数字化和模型化的存在关系。数据除了本身的描述价值外,其价值还在于其相互关联性。为实现关联的准确性,数据需要有对外相互关联的标识。所以体现在数据存储上,主键的第二作用,也是存在的第二因素即:

2.数据需要关联

数据用于描述客观实在的,本身没有意义。只有在根据主观需求组织之后,通过一定方式满足人认识事物的过程才具有了意义。所以数据需要被检索,被组织。则主键第三个作用:

3.数据库底层索引用于检索数据所需

二、为什么主键不宜过长

这个问题的点在上。那有什么优势?(嘿嘿嘿,内涵)—— 短不占空间。但这么点磁盘空间相对整个数据量来说微不足道,而且我们一般不怎么用到主键列。那么原因应该在上,而且和原始数据关系不大。以此自然得出和索引相关,而且和索引读取相关。那么为什么长主键在索引中会影响性能?

上面是 Innodb 的索引数据结构。左边是聚簇索引,通过主键定位数据记录。右边是二级索引,对列数据做索引,通过列数据查找数据主键。如果通过二级索引查询数据,流程如图上所示,先从二级索引树上搜索到主键,然后在聚簇索引上通过主键搜索到数据行。其中二级索引的叶子节点是直接存储的主键值,而不是主键指针。所以如果主键太长,一个二级索引树所能存储的索引记录就会变少,这样在有限的索引缓冲中,需要读取磁盘的次数就会变多,所以性能就会下降。

三、为什么建议使用自增 ID

InnoDB 使用聚簇索引,如上图所示,数据记录本身被存于主索引(一颗 B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL 会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB 默认为 15/16),则开辟一个新的页(节点)。

如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上,如下图左侧所示。否则由于每次插入主键的值近似于随机,因此每次新记录都要被插到现有索引页的中间某个位置,MySQL 不得不为了将新记录插到合适位置而移动数据,如下图右侧所示,这样就造成了一定的开销。由于此,Mysql 为维护索引可能需要频繁的刷新缓冲,增加了方法磁盘 IO 的次数,而且时常需要对索引结构进行重组织。

四、业务 Key VS 逻辑 Key

业务 Key,即使用具有业务意义的 id 作为 Key,比如使用订单流水号作为订单表的主键 Key。逻辑 Key,即无关业务的 Key,按某种规则生成 Key,如自增 Key。

业务 Key 的优点

  • Key 具有业务意义,在查询时可以直接作为搜索关键字使用
  • 不需要额外的列和索引空间
  • 可以减少一些 join 操作。

业务 Key 的缺点

  • 当业务发生变化时,有时需要变更主键
  • 涉及多列 Key 时比较难操作
  • 业务 Key 往往比较长,所占空间更大,导致更大的磁盘 IO
  • 在 Key 确定前不能持久化数据,有时我们没有在确定数据 Key 时,就想先添加一条记录,之后再更新业务 Key
  • 设计一个兼具易用和性能的 Key 生成方案比较难

逻辑 Key 的优点

  • 不会因为业务的变动而需要修改 Key 逻辑
  • 操作简单,且易于管理
  • 逻辑 Key 往往更小,性能更优
  • 逻辑 Key 更容易保证唯一性
  • 更易于优化

逻辑 Key 缺点

  • 查询主键列和主键索引需要额外的磁盘空间
  • 在插入数据和更新数据时需要额外的 IO
  • 更多的 join 可能
  • 如果没有唯一性策略限制,容易出现重复的 Key
  • 测试环境和正式环境 Key 不一致,不利于排查问题
  • Key 的值没有和数据关联,不符合三范式
  • 不能用于搜索关键字
  • 依赖不同数据库系统的具体实现,不利于底层数据库的替换

五、主键生成

一般情况下,我们都使用 Mysql 的自增 ID,来作为表的主键,这样简单,而且从上面讲到的来看,性能也是最好的。但是在分库分表的情况情况下,自增 ID 则不能满足需求。我们可以来看看不同数据库生成 ID 的方式,也看一些分布式 ID 生成方案。利于我们思考甚至实现自己的分布式 ID 生成服务。

数据库的实现

Mysql 自增

Mysql 在内存中维护一个自增计数器,每次访问 auto-increment 计数器的时候, InnoDB 都会加上一个名为AUTO-INC 锁直到该语句结束(注意锁只持有到语句结束,不是事务结束)。AUTO-INC 锁是一个特殊的表级别的锁,用来提升包含 auto_increment 列的并发插入性。

在分布式的情况下,其实可以独立一个服务和数据库来做 id 生成,依旧依赖 Mysql 的表 id 自增能力来为第三方服务统一生成 id。为性能考虑可以不同业务使用不同的表。

Mongodb ObjectId

Mongodb 为防止主键冲突,设计了一个 ObjectId 作为主键 id。它由一个 12 字节的十六进制数字组成,其中包含以下几部分:

  1. Time:时间戳。4 字节。秒级。

  2. Machine:机器标识。3 字节。一般是机器主机名的散列值,这样就确保了不同主机生成不同的机器 hash 值,确保在分布式中不造成冲突,同一台机器的值相同。

  3. PID:进程 ID。2 字节。上面的 Machine 是为了确保在不同机器产生的 objectId 不冲突,而 pid 就是为了在同一台机器不同的 mongodb 进程产生的 objectId 不冲突。

  4. INC:自增计数器。3 字节。前面的九个字节保证了一秒内不同机器不同进程生成的 objectId 不冲突,自增计数器,用来确保在同一秒内产生的 objectId 也不会发现冲突,允许 256 的 3 次方等于 16777216 条记录的唯一性。

Cassandra TimeUUID

Cassandra 使用下面规则生成一个唯一的 id:time + MAC + sequence

方案

  1. Zookeeper 自增:通过 zk 的自增机制实现。
  2. Redis 自增:通过 Redis 的自增机制实现。
  3. UUID:使用 UUID 字符串作为 Key。
  4. snowflake 算法:和 Mongodb 的实现类似,1位符号位 + 41位时间戳(毫秒级)+ 10位数据机器位 + 12位毫秒内的序列

开源实现

  1. 百度 UidGenerator:基于snowflake算法。
  2. 美团 Leaf:同时实现了基于 Mysql 自增(优化)和 snowflake 算法的机制。

推荐系列

列式存储
时间序列数据库(TSDB)初识与选择
十分钟了解 Apache Druid
Apache Druid 底层存储设计
Apache Druid 的集群设计与工作流程
Mysql 大表问题和解决

想了解更多数据存储相关知识,请关注我的公众号。

MageByte