Performance Comparison of Auto‑Increment, UUID, and Random Keys in MySQL

Introduction: MySQL officially recommends using auto_increment primary keys instead of UUID or non‑sequential Snowflake IDs; this article investigates why.

Test setup: Three tables (user_auto_key, user_uuid, user_random_key) are created with identical columns except for the primary key generation strategy. Data insertion and query performance are measured using Spring Boot, JdbcTemplate, JUnit, and Hutool.

Code: package com.wyq.mysqldemo; import cn.hutool.core.collection.CollectionUtil; import com.wyq.mysqldemo.databaseobject.UserKeyAuto; import com.wyq.mysqldemo.databaseobject.UserKeyRandom; import com.wyq.mysqldemo.databaseobject.UserKeyUUID; import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService; import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService; import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService; import com.wyq.mysqldemo.util.JdbcTemplateService; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.util.StopWatch; import java.util.List; @SpringBootTest class MysqlDemoApplicationTests { @Autowired private JdbcTemplateService jdbcTemplateService; @Autowired private AutoKeyTableService autoKeyTableService; @Autowired private UUIDKeyTableService uuidKeyTableService; @Autowired private RandomKeyTableService randomKeyTableService; @Test void testDBTime() { StopWatch stopwatch = new StopWatch("执行sql时间消耗"); // auto_increment test final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)"; List insertData = autoKeyTableService.getInsertData(); stopwatch.start("自动生成key表任务开始"); long start1 = System.currentTimeMillis(); if (CollectionUtil.isNotEmpty(insertData)) { boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false); System.out.println(insertResult); } long end1 = System.currentTimeMillis(); System.out.println("auto key消耗的时间:" + (end1 - start1)); stopwatch.stop(); // UUID test final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)"; List insertData2 = uuidKeyTableService.getInsertData(); stopwatch.start("UUID的key表任务开始"); long begin = System.currentTimeMillis(); if (CollectionUtil.isNotEmpty(insertData)) { boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true); System.out.println(insertResult); } long over = System.currentTimeMillis(); System.out.println("UUID key消耗的时间:" + (over - begin)); stopwatch.stop(); // Random key test final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)"; List insertData3 = randomKeyTableService.getInsertData(); stopwatch.start("随机的long值key表任务开始"); Long start = System.currentTimeMillis(); if (CollectionUtil.isNotEmpty(insertData)) { boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true); System.out.println(insertResult); } Long end = System.currentTimeMillis(); System.out.println("随机key任务消耗时间:" + (end - start)); stopwatch.stop(); System.out.println(stopwatch.prettyPrint()); } }

Results: Inserting 130 W rows shows auto_increment fastest, random key second, UUID slowest; with an additional 10 W rows the UUID performance drops sharply.

Index analysis: Auto‑increment keys produce sequential inserts, minimizing page splits and random I/O, while UUIDs cause random page placement, frequent page splits, fragmentation, and higher lock contention.

Drawbacks of auto_increment: exposure of business growth, hotspot lock contention under high concurrency, and lock‑mode overhead.

Conclusion: For InnoDB tables, sequential primary keys are recommended for better insert performance and lower fragmentation, though trade‑offs exist.

Demo source code is available at https://gitee.com/Yrion/mysqlIdDemo.