Shulou(Shulou.com)06/01 Report--

Global ID generation scheme under the environment of MySQL sub-library and sub-table

Directory [-]

1. Database self-adding ID-- comes from the solution of Flicker 2. Stand-alone applications-- Solutions from Twitter

In large-scale Internet applications, with the increase of the number of users, we often need to split the database and table in order to improve the performance of the application. In the era of single table, we can rely entirely on the self-increasing ID of the database to uniquely identify a user or data object. However, when we divide the database and tables, we can not rely on the self-increasing ID of each table to globally and uniquely identify the data. Therefore, we need to provide a globally unique ID number generation strategy to support the environment of sub-libraries and sub-tables. Here are two excellent solutions:

1. The solution of database self-adding ID-- from Flicker

Because MySQL itself supports auto_increment operations, it is natural for us to think of using this feature to achieve this function. Flicker adopts the mechanism of MySQL self-growing ID (auto_increment + replace into + MyISAM) in solving the global ID generation solution. A scenario for generating 64-bit ID looks like this:

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

When we insert the record, execute SELECT * from Tickets64, and the query result looks like this:

+-+ | id | stub | +-+-+ | 72157623227190423 | a | +-+-+

On our application side, we need to do the following two operations to commit in a transaction session:

REPLACE INTO Tickets64 (stub) VALUES ('a'); SELECT LAST_INSERT_ID ()

In this way, we can get the growing and non-repetitive ID.

So far, we have only generated ID on a single database, and from a high availability point of view, the next step is to solve the problem of single point of failure: Flicker enables two database servers to generate ID, and generates odd and even ID by distinguishing the starting value and step size of auto_increment.

TicketServer1: auto-increment-increment = 2 auto-increment-offset = 1 TicketServer2: auto-increment-increment = 2 auto-increment-offset = 2

Finally, you only need to get the ID by polling on the client side.

Advantages: make full use of the self-increasing ID mechanism of the database, provide high reliability, and generate orderly ID. Disadvantages: occupy two separate MySQL instances, some waste of resources, the cost is high.

Reference: http://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/

two。 Stand-alone applications-- Solutions from Twitter

In the process of migrating the storage system from MySQL to Cassandra, because Cassandra does not have a sequential ID generation mechanism, Twitter has developed a set of globally unique ID generation service: Snowflake. GitHub address: https://github.com/twitter/snowflake. According to the business requirements of twitter, the snowflake system generates 64-bit ID. It consists of three parts:

41-bit time series (accurate to milliseconds, 41-bit length can be used for 69 years) 10-bit machine identification (10-bit length supports deployment of up to 1024 nodes) 12-bit count sequence numbers (12-bit count sequence numbers support 4096 ID sequence numbers per node per millisecond)

The highest bit is the symbol bit, which is always 0.

Advantages: high performance, low latency; independent applications; ordered by time. Cons: independent development and deployment are required.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.