MySQL 怎样设计主键-百分百源码网

MySQL 怎样设计主键

发布时间：09/01 来源：未知浏览：关键词：

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

一、为什么需要主键

数据记载需具有独一性(第一范式)

数据需要关联 join

数据库底层索援用于检索数据所需

以下言之无物，可以直接跳过到下一节。

“信息是用来消弭随机不定性的东西”（香农）。人通过获得、识别天然界和社会的不一样信息来不同不一样事物，得以认识和革新世界。数据是反映客不雅事物属性的记载，是信息的详细展现情势。数据经过加工处置之后，就成为信息；而信息需要经过数字化改变成数据才能储备和传输。数据库就是用于储备数据记载的。既已如此，记载便是具有肯定性(相对)的信息，其肯定性即独一性。我们得出第一条缘由：

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 字节的十六进制数字组成，其中包括以下几部分：

Time：时间戳。4 字节。秒级。

Machine：机器标识。3 字节。一样是机器主机名的散列值，这样就确保了不一样主机生成不一样的机器 hash 值，确保在分布式中不造成冲突，统一台机器的值雷同。

PID：进程 ID。2 字节。上面的 Machine 是为了确保在不一样机器发生的 objectId 不冲突，而 pid 就是为了在统一台机器不一样的 mongodb 进程发生的 objectId 不冲突。

INC：自增计数器。3 字节。前面的九个字节包管了一秒内不一样机器不一样进程生成的 objectId 不冲突，自增计数器，用来确保在统一秒内发生的 objectId 也不会发明冲突，同意 256 的 3 次方等于 16777216 笔记录的独一性。

Cassandra TimeUUID

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

方案

Zookeeper 自增：通过 zk 的自增机制实现。

Redis 自增：通过 Redis 的自增机制实现。