Why UUID Primary Keys Slow Down MySQL Inserts: A Performance Deep Dive

Introduction

MySQL officially recommends using auto_increment sequential primary keys instead of UUIDs or non‑sequential Snowflake IDs. This article examines the drawbacks of UUIDs, conducts performance tests, and analyzes the underlying index structures.

1. MySQL and Program Example

1.1 Create Three Tables

Three tables are created: user_auto_key (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (Snowflake‑generated random key). All other columns remain identical to isolate the effect of the primary‑key strategy.

Random key refers to a Snowflake‑generated 18‑digit long value.

Table schemas:

1.2 Benchmark Code

The test uses SpringBoot, JdbcTemplate, JUnit, and Hutool. It inserts the same amount of randomly generated data (names, emails, addresses) into each table and measures the time taken.

@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 execution time");
        // auto_increment
        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
        List<UserKeyAuto> insertData = autoKeyTableService.getInsertData();
        stopwatch.start("Auto key insert");
        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 time:" + (end1 - start1));
        stopwatch.stop();
        // UUID
        final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();
        stopwatch.start("UUID key insert");
        long begin = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData2)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);
            System.out.println(insertResult);
        }
        long over = System.currentTimeMillis();
        System.out.println("UUID key time:" + (over - begin));
        stopwatch.stop();
        // Random key
        final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();
        stopwatch.start("Random key insert");
        long start = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(insertData3)) {
            boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);
            System.out.println(insertResult);
        }
        long end = System.currentTimeMillis();
        System.out.println("Random key time:" + (end - start));
        stopwatch.stop();
        System.out.println(stopwatch.prettyPrint());
    }
}

1.3 Insertion Results

Result screenshots for each table are shown below:

1.4 Efficiency Test Results

When the existing data volume reaches 1.3 million rows, inserting an additional 100 k rows shows the following performance ranking: auto_key > random_key > uuid . UUID insertion time degrades sharply as data grows.

2. Index Structure Comparison

2.1 Auto‑increment Internal Structure

Sequential primary keys allow InnoDB to store rows consecutively, minimizing page splits, reducing random I/O, and keeping the fill factor high.

2.2 UUID Index Internal Structure

UUIDs are random; new rows cannot be appended to the end of the index, forcing InnoDB to locate arbitrary pages, causing random I/O, frequent page splits, and fragmentation.

2.3 Disadvantages of Auto‑increment

Despite its performance benefits, auto‑increment has drawbacks:

Predictable IDs expose business growth patterns.

High‑concurrency inserts create hotspot contention on the primary‑key index.

Auto‑increment lock contention can reduce throughput; tuning

innodb_autoinc_lock_mode

may mitigate this.

3. Conclusion

The blog first poses the question of why MySQL discourages UUIDs, then builds three tables and uses

JdbcTemplate

to benchmark insert performance at large data volumes. It analyzes how different primary‑key strategies affect InnoDB’s index layout, explains the performance penalties of random keys, and ultimately recommends following MySQL’s guidance to prefer sequential auto‑increment keys while being aware of their own security and concurrency trade‑offs.