Performance Comparison of Auto‑Increment ID vs UUID Primary Keys in MySQL

This technical note, authored by a senior architect, analyses the impact of using auto‑increment integer IDs versus UUID strings as primary keys in MySQL tables.

1. Table and Data Preparation

The UC_USER table uses an auto‑increment bigint primary key and includes typical user fields such as USER_NAME, EMAIL, MOBILE, etc. The full DDL is shown below:

CREATE TABLE `UC_USER` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `USER_NAME` varchar(100) DEFAULT NULL COMMENT '用户名',
  `USER_PWD` varchar(200) DEFAULT NULL COMMENT '密码',
  `BIRTHDAY` datetime DEFAULT NULL COMMENT '生日',
  `NAME` varchar(200) DEFAULT NULL COMMENT '姓名',
  `USER_ICON` varchar(500) DEFAULT NULL COMMENT '头像图片',
  `SEX` char(1) DEFAULT NULL COMMENT '性别, 1:男，2:女，3：保密',
  `NICKNAME` varchar(200) DEFAULT NULL COMMENT '昵称',
  `STAT` varchar(10) DEFAULT NULL COMMENT '用户状态，01:正常，02:冻结',
  `USER_MALL` bigint(20) DEFAULT NULL COMMENT '当前所属MALL',
  `LAST_LOGIN_DATE` datetime DEFAULT NULL COMMENT '最后登录时间',
  `LAST_LOGIN_IP` varchar(100) DEFAULT NULL COMMENT '最后登录IP',
  `SRC_OPEN_USER_ID` bigint(20) DEFAULT NULL COMMENT '来源的联合登录',
  `EMAIL` varchar(200) DEFAULT NULL COMMENT '邮箱',
  `MOBILE` varchar(50) DEFAULT NULL COMMENT '手机',
  `IS_DEL` char(1) DEFAULT '0' COMMENT '是否删除',
  `IS_EMAIL_CONFIRMED` char(1) DEFAULT '0' COMMENT '是否绑定邮箱',
  `IS_PHONE_CONFIRMED` char(1) DEFAULT '0' COMMENT '是否绑定手机',
  `CREATER` bigint(20) DEFAULT NULL COMMENT '创建人',
  `CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `UPDATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改日期',
  `PWD_INTENSITY` char(1) DEFAULT NULL COMMENT '密码强度',
  `MOBILE_TGC` char(64) DEFAULT NULL COMMENT '手机登录标识',
  `MAC` char(64) DEFAULT NULL COMMENT 'mac地址',
  `SOURCE` char(1) DEFAULT '0' COMMENT '1:WEB,2:IOS,3:ANDROID,4:WIFI,5:管理系统, 0:未知',
  `ACTIVATE` char(1) DEFAULT '1' COMMENT '激活，1：激活，0：未激活',
  `ACTIVATE_TYPE` char(1) DEFAULT '0' COMMENT '激活类型，0：自动，1：手动',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `USER_NAME` (`USER_NAME`),
  KEY `MOBILE` (`MOBILE`),
  KEY `IDX_MOBILE_TGC` (`MOBILE_TGC`,`ID`),
  KEY `IDX_EMAIL` (`EMAIL`,`ID`),
  KEY `IDX_CREATE_DATE` (`CREATE_DATE`,`ID`),
  KEY `IDX_UPDATE_DATE` (`UPDATE_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=7122681 DEFAULT CHARSET=utf8 COMMENT='用户表';

A second table UC_USER_PK_VARCHAR_1 uses a varchar(36) UUID as the primary key. Its DDL is analogous and is omitted for brevity.

2. 5‑Million‑Row Test

Two simple count queries were executed on each table:

# Auto‑increment table
mysql> SELECT COUNT(1) FROM UC_USER;
+----------+
| count(1) |
+----------+
| 5720112 |
+----------+
1 row in set (0.00 sec)

# UUID table
mysql> SELECT COUNT(1) FROM UC_USER_PK_VARCHAR_1;
+----------+
| count(1) |
+----------+
| 5720112 |
+----------+
1 row in set (1.91 sec)

The count operation is roughly twice as fast on the integer table, indicating lower I/O for the smaller key.

2.1 Data Insertion Size

Storing 5 million rows shows that the auto‑increment ID consumes about half the disk space of the UUID column.

2.2 Single‑Row Index Lookup

Lookup latency for a single primary‑key value is comparable between the two key types.

2.3 Range LIKE Queries

Range scans over hundreds or thousands of rows perform noticeably better with sequential integer IDs.

2.4 Write Throughput

Insert speed for the auto‑increment table is roughly four times that of the UUID table.

2.5 Backup & Restore

Backup and restore operations are faster when the primary key is an integer.

3. Summary of 5 Million‑Row Tests

Simple queries (single or ~20 rows) show little difference.

Range queries on large result sets favor auto‑increment IDs.

Statistical aggregations on ranges are faster with integers.

Storage consumption of integer IDs is about 50 % of UUIDs.

Backup/restore is slightly faster with integer keys.

4. 10‑Million‑Row Test

Similar patterns were observed with a 10 million‑row dataset, with additional findings:

Single‑row queries are 2–3× faster with integer keys.

Range queries remain superior for integers.

Aggregations are 1.5–2× faster with integers.

Write performance of integer keys is 3–10× higher, especially for small‑range updates.

Backup/restore continues to favor integers.

5. Recommendations

5.1 Auto‑Increment ID + Step Size

For medium‑scale distributed systems, configure auto_increment_increment and auto_increment_offset so that each cluster node generates IDs in a non‑overlapping range, achieving global uniqueness with minimal complexity.

5.2 UUID Primary Key

UUIDs are suitable for small‑scale distributed environments where simplicity outweighs the storage and performance penalties. They avoid the need for coordinated ID generation but double the storage size and degrade I/O due to random insertion order.

5.3 Trade‑offs

Pros of auto‑increment: Simple implementation, easy maintenance, transparent to applications.

Cons of auto‑increment: Requires careful planning of step size for future scaling.

Pros of UUID: No coordination needed for uniqueness.

Cons of UUID: Higher storage cost, degraded read/write performance as data grows.

Overall, for most production systems with moderate to large data volumes, an auto‑increment integer primary key (potentially with a configured step for sharding) provides better performance and lower storage overhead.