How to Build a High‑Performance Distributed Unique ID Generator with MySQL and Redis

Background

In distributed architectures, generating a unique serial number is a frequent problem, especially when using sharding tables. During Ctrip's MySQL account database migration, a new user‑ID generation scheme was needed to support the platform's massive registration volume.

Feature Requirements

Globally unique

Support high concurrency

Reflect certain attributes

Highly reliable with fault‑tolerant single‑point failure handling

High performance

Industry Solutions

Common ID generation methods include:

Database auto‑increment (global uniqueness) – simple and controllable but puts heavy load on a single DB table.

UUID – 128‑bit hexadecimal string reduces DB pressure but lacks natural ordering and can become very long.

Snowflake (Twitter) – 41‑bit timestamp, 10‑bit machine ID, 12‑bit sequence; offers high performance and time‑ordered IDs but requires a dedicated service.

Redis – uses atomic INCR/INCRBY to generate globally unique IDs; can be clustered for higher throughput, provides natural numeric ordering, but adds a new component and operational complexity.

Final Solution

We adopted a Flicker‑style approach based on MySQL auto‑increment, enhanced with in‑memory segment caching.

First, create a table:

SEQUENCE_GENERATOR_TABLE
id   stub
1    192.168.1.1

The id column is auto‑incremented; stub stores the server IP to avoid conflicts when multiple servers update the table.

Insert or replace a row to obtain a unique ID:

REPLACE INTO SEQUENCE_GENERATOR_TABLE (stub) VALUES ("192.168.1.1");

Then retrieve the generated ID:

SELECT id FROM SEQUENCE_GENERATOR_TABLE WHERE stub = "192.168.1.1";

For high availability, each server updates only its own row, ensuring single‑threaded access to a single record. The table may look like:

id   stub
5    192.168.1.1
2    192.168.1.2
3    192.168.1.3
4    192.168.1.4

Each server receives a distinct ID (5, 2, 3, 4). To reduce DB round‑trips, we fetch a block of IDs (e.g., 1000) and cache them in memory. An AtomicLong holds the current value, and currentMaxId stores the block’s upper bound:

atomic.set(n * 1000);
currentMaxId = (n + 1) * 1000;

When a request arrives, we increment the atomic counter: Long uid = atomic.incrementAndGet(); If the counter exceeds currentMaxId, the thread blocks, fetches a new segment from the DB, and updates the two values. This logic fits within about 20 lines of code and solves distributed ID generation efficiently.

One caveat: after a server restart, unused IDs in the cached segment are lost, so a smaller segment size reduces waste. In practice, the performance gains outweigh the minor ID loss.

Online Results

After more than five months in production, the service remains stable with the following metrics:

SOA service average response time: 0.59 ms

Client call average response time: 2.52 ms

Diagram