Generating Ultra‑Short Numeric IDs in MySQL: From Snowflake to Custom Schemes

Background

The project needed short numeric IDs for user accounts because UUID strings were inefficient for storage, transmission, and human use. A concise, sequential numeric ID would improve memorability and database write performance.

Initial attempt with Snowflake

The classic Twitter Snowflake algorithm generates 64‑bit IDs that are globally unique and distributed, but the resulting IDs are too long for the short‑ID requirement.

https://juejin.cn/post/7386243179278041128

Problems with Snowflake IDs

Long 64‑bit values are hard for users to input and remember.

Long IDs increase storage and write overhead for massive account tables.

Improved version using MySQL auto‑increment

To obtain short IDs, the design switched to MySQL’s auto‑increment feature. The login flow checks whether an OpenID already has an account; if not, a new numeric ID is created via a dedicated ID‑generation table.

Account table schema

CREATE TABLE tbl_global_user_map_00 (
  account varchar(32) NOT NULL,
  accid bigint(20) NOT NULL,
  created_at datetime DEFAULT NULL,
  PRIMARY KEY (account) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ID generation table schema

CREATE TABLE tbl_accid (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  stub char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (id) USING BTREE,
  UNIQUE KEY UQE_tbl_accid_stub (stub) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Each insert into tbl_accid with a constant stub='a' creates a single row; the auto‑increment column supplies the next short ID.

Deadlock issue with REPLACE INTO

Using REPLACE INTO tbl_accid(stub) VALUES('a') caused intermittent deadlocks because REPLACE is internally executed as DELETE followed by INSERT, which acquires row locks that conflict under concurrent access.

ERROR : Deadlock found when trying to get lock; try restarting transaction

Tests with mysqlslap showed deadlocks at concurrency 2, confirming that REPLACE is unsuitable for high‑throughput ID generation.

Alternative approaches

Pre‑allocating ID batches (Meituan MT‑Leaf)

One alternative is to request a block of N IDs at once, reducing request frequency. This method is similar to the MT‑Leaf solution used by Meituan.

Sharding with auto‑increment step

Another idea is to set auto_increment_increment and auto_increment_offset per MySQL instance, giving each shard a distinct numeric range (e.g., 0‑999, 1000‑1999, …). However, these variables are global or session‑level, not per‑table, so they risk affecting unrelated tables.

Read‑write split with a single writable ID table

A read‑write split can keep one writable table that holds the auto‑increment ID while reads are served from many shard tables. Periodic migration moves allocated IDs into the read‑only shards, but the approach adds operational complexity.

Final solution: Free‑ID table

The chosen design introduces tbl_account_freeid with columns segment (auto‑increment), svr (login server ID), and left (remaining IDs in the segment). Each server inserts a row for a 1000‑ID segment, uses the auto‑increment id as the short account ID, and writes back the remaining count on graceful shutdown.

Workflow:

On startup, a server queries tbl_account_freeid for its svr. If a row exists with left>0, the segment is reused; otherwise a new row is inserted, allocating the next 1000‑ID block.

When an account is created, the server inserts a dummy row into tbl_accid (using REPLACE INTO only after the deadlock issue is mitigated) to obtain the next id.

On graceful shutdown, the server updates left with the number of IDs still unused in the segment, allowing reuse after restart.

Case analyses (initial allocation, reuse after shutdown, server scaling) are described in the original tables; the essential point is that each segment’s left tracks unused IDs, preventing waste while keeping the ID space short and sequential.

Conclusion

The short‑ID generation journey progressed from Snowflake to MySQL auto‑increment, encountered deadlock problems with REPLACE INTO, evaluated batch‑allocation and sharding ideas, and finally settled on a dedicated free‑ID table that satisfies the requirements of brevity, monotonicity, and low operational overhead.