欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-01 08:45:46
...
SessionID、AccessToken、OrderID、PrimaryKey等等这些都需要一个唯一标示的ID值。

需求:
生成速度、不可推测、唯一性(高并发)、有序性

场景:
直接插入数据无需生成的ID
插入数据后需要生成的ID(主从表)

先看看Tomcat和Jetty的SessionID:

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

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

(1)UUID/GUID
能保证唯一性,但是字节太长,无法排序,索引性能

(2)数值自动增长(auto-increment字段 / sequence)
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')


分库分表(sharding)无法保证唯一性
无法防爬虫爬数据(优惠券号码)


(3)预生成模式
使用表统一管理所有需要增长的字段,每次取出值后做加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)
);


需要synchronized排他

(4)基于时间戳(Time-based)生成
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();


参考:
https://github.com/twitter/snowflake/
https://qiita.com/kawasima/items/6b0f47a60c9cb5ffb5c4
http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
http://www.wekeroad.com/2014/05/29/a-better-id-generator-for-postgresql/
http://blog.codinghorror.com/primary-keys-ids-versus-guids/
http://my.oschina.net/u/142836/blog/174465
http://blog.csdn.net/bluishglc/article/details/7710738
业务系统需要什么样的ID生成器
如何在高并发分布式系统中生成全局唯一Id