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:
[color=red]分库分表(sharding)无法保证唯一性
无法防爬虫爬数据(优惠券号码)[/color]
[b](3)预生成模式[/b]
使用表统一管理所有需要增长的字段,每次取出值后做加1更新:
[color=red]需要synchronized排他[/color]
[b](4)基于时间戳(Time-based)生成[/b]
Twitter的Snowflake
以下是Instagram采用PL/pgSQL的完整ID策略:
生成的ID:842362613529576449
最后,如何获取刚插入记录的ID的值:
MySQL:
PostgreSQL:
JDBC 的 getGeneratedKeys() 方法:
参考:
[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.csdn.net/bluishglc/article/details/7710738]http://blog.csdn.net/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]
[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.csdn.net/bluishglc/article/details/7710738]http://blog.csdn.net/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]