1.Primary Key

  1. What
  2. Necessary
  3. Implementation
    1. 数据库生成
      1. Auto-increment - MySQL为例
      2. UUID by 数据库层 - MySQL为例
      3. UUID_SHORT - MySQL为例
      4. Sequence - Postgresql为例
    2. 应用程序生成
      1. UUID(应用层)
      2. 自增长主键
  4. Others
    1. clustered index是什么?
    2. 能使用字符串作为主键吗?
  5. TODO

What

主键,英文Primary Key,用于表的唯一标识。[Postgresql]

主键的实质是由以下组成的:

Technically, a primary key constraint is simply a combination of a unique constraint and a not-null constraint.

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

  • 唯一约束(unique constraint)
  • 非空约束(not-null constraint)
  • B-tree index 或 partial index

其他参考:https://dev.mysql.com/doc/refman/8.0/en/primary-key-optimization.html

Necessary

When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

MySQL官网 中写到,如果不创建主键(PRIMARY KEY ),又没有唯一部位空的一个列,那么将自动创建一个自增加的列,用作 clustered 索引。所以我们应该创建主键

Implementation

根据主键数值的生成地点的不同,可以分为 由应用程序生成 和 由 由数据库生成 。如果数值是由数据库生成的话,那么数值的递增是不会有冲突的(不可能同一时间有相同的主键数值),但是数值必须依此生成,而普数值往往是在 Insert 之后才会生成,因此堵塞的几率会十分大。另一种折衷的方案则是提前分配,即先让应用层一词获取若干个数值,这样就不需要等待 Insert 后的主键值了。

常见的数值分为 递增的无序。递增的方式适合于B-Tree结构的索引,因为插入新的数值所导致B-Tree结构大幅度变动几率低。而无序适合于hash index,但hash index需要表单为内存模式[mysql]

数值的大小也是会影响性能。由于数据库一般使用的是B-TREE,所以检索时会将范围连续的数据一并检索,所以字段如果过长,将会势必会减少每一次读取的数据数据量,即会增加从磁盘的读取次数,造成读取的时间。所以字段长度要合理。MySQL中的数据长度分为以下:[MySQL]image-20210217114118363

由数据库生成:

由应用程序生成:

  • 分布式数据库主键
    • 利用算法生成的无冲突的全局主键(无序)
    • 自增长主键(递增)

数据库生成

Auto-increment - MySQL为例

优点:简单方便,符合数据库存储结果。

缺点:插入的数据过多时,将会堵塞。

UUID by 数据库层 - MySQL为例

UUID() 是使用了 RFC 4122 中定义的 版本一的UUID,而版本一是根据 时间戳(timestamp) 和 MAC地址 生成的,是递增的,而不是随机的,因此适用于B-Tree。版本一的UIID根据 60-bit的时间戳 + 48-bit MAC地址 生成的,生成的字符串为 128 bit,36个字符。

1
dae38ac4-734b-11eb-9439-0242ac130002

在没有进行任何转换的情况下,36个字符的数据是十分长的,不适合存储。对此优化,我们可以使用 UUID_TO_BIN 将字符转换城二进制,转换后的数据空间大小 16字节

版本一的UUID 可以的解析度为100纳秒,也就是说当达到 1000万每秒 的频率才有可能产生重复的ID。StackOverflow

参考:mysql文档UUID的原理在线UUID生成器

UUID_SHORT - MySQL为例

UUID_SHORT() 是以 64-bit无符号整数(unsigned integer) 的形式进行存储,即储存空间为8个字节,是 UUID() 的一半。根据 server_id(1字节)、服务器启动时间(server_startup_time_in_seconds)、自增数(incremented_variable) 三个部分而生成的。UUID_SHORT() 每秒最大可以达到1600万的频率。

Sequence - Postgresql为例

Sequence 全称 sequence number generator。Sequence 允许我们一次取若干个数字(increment),当数字用完后,才会再次取数据库获取,因此会减少访问数据库的次数,去除了等待 insert 后获取主键的这一段时间。但在多应用架构种,如果 increment 值设得太高,那么如果一个应用堵塞过久,当该应用重新再次插入数据时,它得主键值可能远小于其他应用得主键值,那么就可能导致B-Tree重构的次数增加。

暂时在网上没搜到关于 increment 的最优值计算方式。可能也是需要通过实践去调查的吧,Spring JPA 中的默认值是50。

1
2
3
4
5
6
    @Id
    @SequenceGenerator(name = "address_seq",
            allocationSize = 50)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
            generator = "address_seq")
    private Long id;

应用程序生成

UUID(应用层)

UUID 如果使用版本一,那么和上述的UUID一致。如果使用其他版本,那么可能导致巨大差异。比如,版本四的UUID 是乱序的,那么不适合B-Tree的插入操作。

自增长主键

应用层的自增式UUID 和 MySQL的 UUID_SHORT() 相似,无论从组成结构,还是从存储大小。如

UUID_SHORT() 的区别在于——生成主键值的部件是否是数据库。如果不是的话,耦合性将会降低。Twitter 官方说,因为 Cassandra(NoSQL) 没有内置方法生成 unique ids,所以创造了Snowflake,而snowflake需要得特性:Snowflake

  • 一千万每秒的生成量
  • 有序
  • 存储大小尽可能小

其他参考网站:

Others

clustered index是什么?

MySQL有若干个存储引擎,其中一个存储引擎叫 InnoDB,它使用了的是 B-tree。[mysql]

With the exception of spatial indexes, InnoDB indexes are B-tree data structures. Spatial indexes use R-trees,

B-tree 使用的物理索引是 clustered index,而不是hashed index。B-tree 有以下特性:[wik]i

在计算机科学中,B树(英语:B-tree)是一种自平衡的树,能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作,都在对数时间内完成。

能使用字符串作为主键吗?

答:不应该使用字符串作为主键。因为字符串翻译成字节,将是随机数字,不利于 B-tree 的插入。如果是memory式的引擎的话,可以使用字符串作为主键,而索引应该使用 hash index(类似NoSQL的用法)。

TODO

  • Postgresql 的 Sequence 怎么使用?是怎么样的存在?
  • 数据库的拆分时,Auto-increment 将产生怎么样的问题?