How to Generate Global IDs After Table Sharding? Interview Guide and Solution Comparison

Why a global ID is required after sharding

Before sharding a table uses AUTO_INCREMENT to guarantee uniqueness. After splitting into multiple tables (e.g., order_0 … order_15) each shard starts its own counter at 1, producing duplicate primary keys such as order_0.id=1 and order_1.id=1. Duplicate keys break cross‑table queries, pagination, data migration and external exposure. Exposing a simple incremental number also leaks business volume. Therefore a qualified global ID must satisfy:

Globally unique across databases and tables.

Monotonically increasing to keep B‑tree indexes efficient.

High‑performance generation that does not become a bottleneck.

High availability – the generator must not bring down the whole system.

Optional information security – should not allow outsiders to infer traffic volume.

Six major distributed ID generation categories

UUID – locally generated random/timestamp string via UUID.randomUUID(). Very high generation speed, no external dependency. Drawbacks: 36‑character string, large storage, random order causes InnoDB page splits, unreadable for users. Not suitable for sharded primary keys.

Database auto‑increment with step – different nodes start with different initial values and use a fixed step. Example:

DB1: start=1, step=3 → 1,4,7,10…
DB2: start=2, step=3 → 2,5,8,11…
DB3: start=3, step=3 → 3,6,9,12…

Pros: simple, uses MySQL native mechanism, IDs strictly increase. Cons: limited scalability (step fixed), each ID write hits the DB (thousands QPS limit), single‑point risk without multi‑master. Suitable for small, fixed‑size tables.

Database segment (range) mode – fetch a batch of IDs from the DB and cache locally. Representative implementation: Meituan Leaf‑Segment. Leaf maintains two buffers current and next. When current usage reaches a configurable threshold (default 10 %), an asynchronous task loads the next segment. If the DB becomes temporarily unavailable, the pre‑loaded next buffer keeps the service alive.

Leaf’s double‑buffer yields million‑level QPS, IDs strictly increase within a segment, low DB load, and short‑term DB outage tolerance.

Cons: still depends on the DB (long‑term DB failure is fatal), IDs may jump when a segment switches, machine restart can waste a range.

Redis INCR / INCRBY – atomic increment commands in Redis. Pros: high performance, simple implementation, IDs strictly increase. Cons: strong dependency on Redis (outage crashes the ID service unless Redis Cluster or Sentinel is used), persistence issues (RDB may lose a range, AOF adds overhead), capacity limits compared with DB. Suitable for small‑scale systems that already have Redis.

Snowflake (Twitter) – 64‑bit integer composed of:

1‑bit sign (always 0)

41‑bit timestamp (millisecond precision, ~69 years)

10‑bit machine identifier (often 5‑bit data‑center + 5‑bit worker, supports 1024 nodes)

12‑bit sequence (0‑4095 per millisecond, ~4 million+ QPS per node)

// pseudo‑code
long timestamp = currentMillis - twepoch;
long id = (timestamp << 22) | (machineId << 12) | sequence;

Pros: local generation, microsecond‑level latency, monotonic increase, no DB/Redis dependency, storage‑friendly 64‑bit long. Cons:

Clock rollback can cause duplicate IDs. Handling strategies: short rollback – sleep; long rollback – throw exception or use historical‑time fallback; protect clocks with ZooKeeper or NTP.

Machine‑ID allocation becomes cumbersome in containerized or auto‑scaled environments; solutions include ZooKeeper temporary nodes or DB registration.

IDs expose business volume because high bits are timestamps and low bits are sequences.

Industrial‑grade open‑source solutions

Meituan Leaf – supports two modes:

Segment mode (same as the DB segment described above) for strictly increasing order numbers.

Snowflake mode – uses ZooKeeper to allocate workerId, solving machine‑ID and clock‑rollback issues.

Both modes can coexist; selection depends on business requirements.

Baidu UidGenerator – enhanced Snowflake variant:

WorkerId allocated via a DB auto‑increment record at startup.

Second‑level timestamp + RingBuffer pre‑generates IDs, achieving >6 million QPS per node.

Core innovation is the RingBuffer that decouples ID generation from real‑time calculation.

Production‑level scheme selection

Order numbers, payment流水号 (strictly increasing, DB‑friendly) → Meituan Leaf‑Segment.

High‑concurrency logs, event IDs → Snowflake or Baidu UidGenerator.

Internal systems with controllable scale → Database step scheme.

Existing Redis infrastructure, modest scale → Redis INCR.

External exposure where business volume must be hidden → Snowflake + hash or custom obfuscation.

In practice many teams run Leaf‑Segment for external order IDs and Snowflake for internal high‑throughput IDs.

Typical interview follow‑up questions

How to handle Snowflake clock rollback? – short rollback: sleep; long rollback: throw exception or use historical‑time fallback; protect clocks with ZooKeeper/NTP.

What if the DB used for segment mode goes down? – Double‑buffer provides a temporary next segment; DB failover (master‑slave switch) or read from replica is required.

How to allocate Snowflake workerId? – ZooKeeper temporary nodes, DB registration, configuration center, or Kubernetes StatefulSet ordinal.

How to prevent IDs from revealing business volume? – Apply bit‑mixing, hash, or prepend a business prefix with checksum.

Can MySQL auto‑increment + sharding work? – Possible with multiple masters and different steps, but scalability and performance are limited; rarely used in large‑scale services.