Mysql:小主键,大问题

大家好我是佩奇
发布于 2022-8-8 19:18
浏览
0收藏

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

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

 

一、为什么需要主键
1.数据记录需具有「唯一性」(第一范式)
2.数据需要关联 「join」
3.数据库底层索引用于检索数据所需
 
以下废话连篇,可以直接跳过到下一节。

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

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

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

「2.数据需要关联」

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

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

 

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

Mysql:小主键,大问题-鸿蒙开发者社区

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

 

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

Mysql:小主键,大问题-鸿蒙开发者社区

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

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

Mysql:小主键,大问题-鸿蒙开发者社区

四、业务 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位毫秒内的序列。


开源实现

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

 

 

 

文章转载自公众号:码哥字节

已于2022-8-8 19:18:26修改
收藏
回复
举报
回复
    相关推荐