Mastering Global ID Generation: From MySQL Auto‑Increment to Snowflake & Leaf

After adopting micro‑services, many previously simple problems become complex, such as generating a global ID.

This article investigates several common global ID generation strategies, compares them, and provides practical guidance.

1. Two Approaches

The problem can be solved in two ways:

Let the database handle it.

Generate the primary key in Java code and insert it directly.

2. Let the Database Handle It

2.1 Modify Database Configuration

When using sharding (e.g., MyCat), the original auto‑increment primary key no longer works. You can adjust MySQL’s auto_increment_increment and auto_increment_offset variables.

<code>SHOW VARIABLES LIKE 'auto_increment%';</code>

Set the step size:

<code>set @@auto_increment_increment=9;</code>

Define the start value when creating a table:

<code>create table test01(id integer PRIMARY KEY auto_increment, username varchar(255)) auto_increment=8;</code>

By assigning different start values and the same step to each shard (e.g., start 1,2,3 with step 3), you can achieve unique IDs, though this method is cumbersome and not recommended for large‑scale systems.

2.2 MySQL + MyCat + ZooKeeper

If you use MyCat as the sharding middleware, you can leverage ZooKeeper to provide a distributed auto‑increment service.

Local file implementation

Database implementation

Local timestamp implementation

Distributed ZK ID generator

ZooKeeper incremental mode

We focus on option 4 (distributed ZK ID generator). Configuration steps include:

Set the primary‑key auto‑increment mode to 4 in server.xml .

Configure the table and primary key in schema.xml .

Provide ZooKeeper connection info in myid.properties .

Define the target table in sequence_conf.properties (table name must be uppercase).

After restarting MyCat and recreating the tables, the service can generate globally unique IDs with good scalability.

3. Java Code Handling

3.1 UUID

UUID is easy to generate locally, but it has drawbacks: long string length hurts MySQL indexing, random distribution harms I/O‑intensive workloads, and MAC‑based UUIDs may expose hardware information.

3.2 Snowflake

Twitter’s Snowflake algorithm produces 64‑bit IDs composed of a sign bit, 41‑bit timestamp, 10‑bit worker ID, and 12‑bit sequence. It guarantees uniqueness and roughly monotonic ordering, which is friendly to index insertion.

Key points:

Timestamp (41 bits) covers about 69.7 years.

Worker ID (10 bits) must be unique per process.

Sequence (12 bits) allows up to 4096 IDs per millisecond.

Implementation example (simplified):

<code>public class IdWorker { /* fields and methods as shown in the source */ }</code>

Usage:

<code>IdWorker idWorker = new IdWorker(0, 0);
for (int i = 0; i < 1000; i++) {
    System.out.println(idWorker.nextId());
}</code>

3.3 LEAF

Leaf is Meituan’s open‑source distributed ID service. It supports two modes:

Segment mode (database‑backed).

Snowflake mode (ZooKeeper‑backed).

Segment mode stores a leaf_alloc table with columns biz_tag , max_id , step , etc. The service fetches ID blocks from the DB, reducing load.

<code>CREATE DATABASE leaf;
CREATE TABLE `leaf_alloc` (
  `biz_tag` varchar(128) NOT NULL DEFAULT '',
  `max_id` bigint(20) NOT NULL DEFAULT '1',
  `step` int(11) NOT NULL,
  `description` varchar(256) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`biz_tag`)
) ENGINE=InnoDB;

insert into leaf_alloc(biz_tag, max_id, step, description) values('leaf-segment-test', 1, 2000, 'Test leaf Segment Mode Get Id');</code>

After starting the service, IDs can be obtained via http://localhost:8080/api/segment/get/leaf-segment-test . Monitoring is available at http://localhost:8080/cache .

Pros: linear scalability, 64‑bit monotonic IDs, high availability via segment cache. Cons: IDs are predictable, and DB outage can affect the service.

3.4 Redis Generation

Redis’s INCRBY can be used to generate incremental IDs; the article notes this approach without further detail.

3.5 ZooKeeper Handling

ZooKeeper can also provide ID generation, but the implementation is more complex and not recommended.

4. Summary

If your project already uses MyCat, the MyCat + ZooKeeper solution is convenient; otherwise, the Leaf service (either segment or Snowflake mode) is generally the better choice for distributed ID generation.