Generating Short Sequential Numeric IDs in MySQL Without Snowflake Overhead

Background

In a project we needed to generate short numeric account IDs instead of long UUID strings, which are less efficient for storage, transmission, and user input.

Initial Snowflake Approach

We first considered Twitter's Snowflake algorithm, which produces 64‑bit IDs that are globally unique and distributed, but the IDs are too long for our requirement of short, sequential numbers.

Improved Version with Auto‑Increment Table

We switched to using MySQL's auto‑increment feature. An account table stores user accounts, and a separate ID generation table provides incremental IDs:

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

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 insertion into tbl_accid returns a new auto‑incremented id, which becomes the short account ID.

Deadlock Issues

We used REPLACE INTO tbl_accid(`stub`) VALUES('a'); to keep the table at a single row, but under concurrency MySQL expands REPLACE into DELETE + INSERT, causing row‑level deadlocks:

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

Testing with mysqlslap confirmed deadlocks even with just two concurrent threads. Switching the table engine to MyISAM avoided deadlocks but reduced write performance, so this approach was abandoned.

Alternative Schemes

We explored batch allocation (pre‑requesting a range of IDs) similar to Meituan's MT‑Leaf, and sharding across multiple MySQL servers. However, these required complex deployment and could waste IDs if a server crashed before using its allocated range.

Final Solution – Free‑ID Table

The final design introduces a free‑ID table that tracks ID segments per login server:

CREATE TABLE `tbl_account_freeid` (
  `segment` int NOT NULL AUTO_INCREMENT,
  `svr` int NOT NULL,
  `left` int NOT NULL,
  PRIMARY KEY (`segment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When a login server needs IDs it inserts a row requesting a fixed batch size (e.g., 1000). The segment auto‑increment determines the ID range [(segment‑1)*1000, segment*1000). The left column records how many IDs remain unused when the server shuts down, preventing waste.

During normal operation the server reads IDs from its in‑memory segment, incrementing by one. On restart it checks the left value to resume from the correct point. This approach keeps ID generation fast, sequential, and short while avoiding the deadlock problems of REPLACE and the complexity of multi‑server sharding.

Conclusion

The short‑ID generation evolved from Snowflake to MySQL auto‑increment, through a deadlock‑prone REPLACE strategy, and finally to a dedicated free‑ID table that satisfies the requirements of brevity, sequential order, and low operational overhead.