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

Source: Internet
Author: User
Tags cassandra unique id ticket

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.