数据库表的ID/PK生成策略

最新推荐文章于 2025-05-10 06:30:00 发布

原创最新推荐文章于 2025-05-10 06:30:00 发布 · 782 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #开发工具 #java

设计相关专栏收录该内容

9 篇文章

订阅专栏

本文探讨了不同ID生成策略的优缺点，包括UUID、自动增长、预生成模式及基于时间戳的方法等，重点介绍了Instagram的PL/pgSQL ID生成策略。

SessionID、AccessToken、OrderID、PrimaryKey等等这些都需要一个唯一标示的ID值。

[b]需求：[/b]
生成速度、不可推测、唯一性（高并发）、有序性

[b]场景：[/b]
直接插入数据无需生成的ID
插入数据后需要生成的ID（主从表）

先看看Tomcat和Jetty的SessionID：

[b]Tomcat的SessionID[/b]
https://github.com/apache/tomcat/blob/trunk/java/org/apache/catalina/util/StandardSessionIdGenerator.java

[b]Jetty的SessionID[/b]
https://github.com/eclipse/jetty.project/blob/jetty-9.4.x/jetty-server/src/main/java/org/eclipse/jetty/server/session/DefaultSessionIdManager.java

[b]（1）UUID/GUID[/b]
能保证唯一性，但是[color=red]字节太长，无法排序，索引性能[/color]。

[b]（2）数值自动增长（auto-increment字段 / sequence）[/b]
MySQL： id bigint AUTO_INCREMENT
PostgreSQL ：id bigserial

PostgreSQL ：CREATE SEQUENCE users_id_seq MINVALUE 1;
nextval('users_id_seq')

插入时查找最大ID后加1：

INSERT INTO users(id, name)
VALUES ((select (case when max(id) is null then 1 else (max(id)+1) end) from users), 'xxx')

[color=red]分库分表(sharding)无法保证唯一性
无法防爬虫爬数据（优惠券号码）[/color]

[b]（3）预生成模式[/b]
使用表统一管理所有需要增长的字段，每次取出值后做加1更新：

CREATE TABLE ids (
  table_name character varying(20) NOT NULL, 
  field_name character varying(20) NOT NULL, 
  nextid bigint NOT NULL,
  CONSTRAINT ids_pkc PRIMARY KEY (table_name, field_name)
);

[color=red]需要synchronized排他[/color]

[b]（4）基于时间戳（Time-based）生成[/b]
Twitter的Snowflake

以下是Instagram采用PL/pgSQL的完整ID策略：

create sequence global_id_sequence;

CREATE OR REPLACE FUNCTION id_generator(OUT result bigint) AS $$
DECLARE
    our_epoch bigint := 1314220021721;
    seq_id bigint;
    now_millis bigint;
    -- the id of this DB shard, must be set for each
    -- schema shard you have - you could pass this as a parameter too
    shard_id int := 1;
BEGIN
    SELECT nextval('global_id_sequence') % 1024 INTO seq_id;

    SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
    result := (now_millis - our_epoch) << 23;
    result := result | (shard_id << 10);
    result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

CREATE TABLE users
(
  id bigint NOT NULL DEFAULT id_generator(),
  "name" character varying(50),
  CONSTRAINT users_pkey PRIMARY KEY (id)
);

===============================================================
生成的ID：842362613529576449

最后，如何获取刚插入记录的ID的值：

MySQL：

INSERT INTO users(name) VALUES ('xxx');
SELECT LAST_INSERT_ID();

PostgreSQL：

INSERT INTO users(id, name) VALUES (1, 'xxx') RETURNING id;

JDBC 的 getGeneratedKeys() 方法：

Statement stmt = ..... ;
int count = stmt.executeUpdate( "INSERT语句", Statement.RETURN_GENERATED_KEYS);
if ( count > 0 )  {
  ResultSet rs = stmt.getGeneratedKeys();
  while( rs.next() )  {
    int generatedKey = rs.getInt( 1 ); // .....
  }
  rs.close();
}
if ( !stmt.isClosed() )  stmt.close();

参考：
[url=https://github.com/twitter/snowflake/]https://github.com/twitter/snowflake/[/url]
[url=https://qiita.com/kawasima/items/6b0f47a60c9cb5ffb5c4]https://qiita.com/kawasima/items/6b0f47a60c9cb5ffb5c4[/url]
[url=http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram]http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram[/url]
[url=http://www.wekeroad.com/2014/05/29/a-better-id-generator-for-postgresql/]http://www.wekeroad.com/2014/05/29/a-better-id-generator-for-postgresql/[/url]
[url=http://blog.codinghorror.com/primary-keys-ids-versus-guids/]http://blog.codinghorror.com/primary-keys-ids-versus-guids/[/url]
[url=http://my.oschina.net/u/142836/blog/174465]http://my.oschina.net/u/142836/blog/174465[/url]
[url=http://blog.youkuaiyun.com/bluishglc/article/details/7710738]http://blog.youkuaiyun.com/bluishglc/article/details/7710738[/url]
[url=http://ericliang.info/what-kind-of-id-generator-we-need-in-business-systems/]业务系统需要什么样的ID生成器[/url]
[url=http://www.cnblogs.com/heyuquan/p/global-guid-identity-maxId.html]如何在高并发分布式系统中生成全局唯一Id[/url]