Designing Short Numeric ID Generation with MySQL: From Snowflake to Segment Allocation

Background: the project required short numeric account IDs instead of long UUID strings to improve storage, transmission performance, and user friendliness.

Initial version considered the classic Twitter Snowflake algorithm, which produces 64‑bit IDs; however, the length conflicted with the goal of keeping IDs as short as possible.

Improved version leveraged MySQL auto‑increment features by creating a dedicated ID generation table (tbl_accid) and a sharded user‑map table, inserting rows with REPLACE INTO to obtain sequential IDs.

Under concurrent load, REPLACE INTO caused deadlocks because InnoDB implements it as a DELETE followed by INSERT, leading to row‑level lock conflicts.

Stress testing with mysqlslap reproduced the deadlock issue; switching the table engine to MyISAM avoided deadlocks but introduced lower write performance, prompting abandonment of the auto‑increment approach.

Several alternative schemes were examined: (1) deploying multiple MySQL servers and serialising REPLACE INTO operations, (2) using a single writable table with periodic migration to read‑only shards, both of which introduced operational complexity.

The final solution introduced a segment‑based ID pool using a free‑ID table (tbl_account_freeid). Each login server requests a block of IDs (e.g., 1000 IDs) by inserting a row; the block range is calculated from the auto‑incremented segment value. On graceful shutdown, the remaining unused count (left) is written back, preventing waste. This design eliminates deadlocks, supports server scaling, and provides short, sequential numeric IDs.