Global ID generation scheme in MySQL database/table sharding Environment

Abstract: This article introduces two global ID generation solutions for the Distributed Environment.

Directory:

1. database auto-increment ID-solution from Flicker
2. independent applications-solutions from Twitter

In large Internet applications, as the number of users increases, we often need to perform database/table sharding for databases to improve application performance. In the single table era, we can rely solely on the auto-increment ID of the database to uniquely identify a user or data object. However, after database sharding and table sharding, we cannot rely on the auto-increment ID of each table to uniquely identify the data. Therefore, we need to provide a globally unique ID generation policy to support the database/table sharding environment. The following describes two outstanding solutions:

1. database auto-increment ID-solution from Flicker
Because MySQL itself supports the auto_increment operation, we naturally think of using this feature to implement this function. In the global ID generation solution, Flicker uses the MySQL auto-increment ID mechanism (auto_increment + replace into + MyISAM ). A 64-bit ID generation scheme is as follows:
First 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

After we insert a record, execute SELECT * from Tickets64. The query result is as follows:

+ ------------------- + ------ +
| Id | stub |
+ ------------------- + ------ +
| 72157623227190423 | a |
+ ------------------- + ------ +

The following two operations must be performed on our application end and committed in one transaction session:

Replace into Tickets64 (stub) VALUES ('A ');
SELECT LAST_INSERT_ID ();

In this way, we can get a growing and non-repeated ID.

For more details, please continue to read the highlights on the next page:

1 2 Next Page