Reprint a blog, there are a lot of knowledge and ideas worthy of our thinking.
——————————————————————————————————————————————————————————————————————
In large-scale Internet applications, with the increase of the number of users, in order to improve the performance of the application, we often need to carry out database sub-table operation. In the single table era, we can rely entirely on the database's self-increment ID to uniquely identify a user or data object. But when we have a database of sub-tables, we cannot rely on the self-increment ID of each table to globally uniquely identify the data. Therefore, we need to provide a globally unique ID number generation policy to support the sub-database sub-table environment. Here are two excellent solutions:
1. Database self-increment id--solutions from flicker
Because MySQL natively supports auto_increment operations, it is natural to think of this feature as a way to achieve this. Flicker uses the MySQL self-growth ID mechanism in resolving the global ID Generation scheme (auto_increment + replace into + MyISAM). A scenario for generating a 64-bit ID is exactly this:
Create a separate database (Eg:ticket), and then create a table:
CREATE TABLE Tickets64 ( id bigint(20) unsigned NOT NULL auto_increment, stub char(1) NOT NULL default ‘‘, PRIMARY KEY (id), UNIQUE KEY stub (stub) ) ENGINE=MyISAM
When we insert a record, SELECT * from Tickets64
it executes, and the query results are this:
+-------------------+------+| id | stub |+-------------------+------+| 72157623227190423 | a |+-------------------+------+
The following two actions need to be done on our application side to commit in a transactional session:
REPLACE INTO Tickets64 (stub) VALUES (‘a‘);SELECT LAST_INSERT_ID();
So we can get an ever-growing and non-repeating ID.
Up to this point, we just generate IDs on a single database, and from a high-availability perspective, the next step is to solve the single points of failure: Flicker enables two database servers to generate IDs, and generates even-numbered IDs by distinguishing between Auto_increment's starting value and step size.
TicketServer1:auto-increment-increment = 2auto-increment-offset = 1TicketServer2:auto-increment-increment = 2auto-increment-offset = 2
Finally, the client only needs to take the ID by polling.
- Advantages: Full use of the database's self-increment ID mechanism to provide high reliability, the generated ID is ordered.
- Cons: Occupy two separate MySQL instances, some waste resources, high cost.
Reference: http://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/
2. Standalone application--a solution from Twitter
When Twitter migrated the storage system from MySQL to Cassandra because Cassandra had no sequential ID generation mechanism, it developed a set of global unique ID generation services: Snowflake. GitHub Address: Https://github.com/twitter/snowflake. Based on Twitter's business needs, the snowflake system generates a 64-bit ID. Consists of 3 parts:
41位的时间序列(精确到毫秒,41位的长度可以使用69年)10位的机器标识(10位的长度最多支持部署1024个节点)12位的计数顺序号(12位的计数顺序号支持每个节点每毫秒产生4096个ID序号)
The highest bit is the sign bit, which is always 0.
- Advantages: High performance, low latency, independent applications, and orderly time-based.
- Cons: Requires independent development and deployment.
"Go" MySQL the Global ID generation scheme under the sub-database sub-table environment