"Go" MySQL the Global ID generation scheme under the sub-database sub-table environment

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