Why MySQL Auto‑Increment Beats UUID: Performance and Index Insights

Introduction

MySQL officially recommends using a sequential auto_increment primary key instead of UUIDs or non‑sequential Snowflake IDs. This article explores the reasons behind this recommendation.

1. MySQL and Program Instances

1.1 Build Three Tables

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

Note: The random key is an 18‑digit long value without any regular pattern.

1.2 Performance Test Code

The test uses Spring Boot, JdbcTemplate, JUnit, and Hutool to insert a large number of rows into each table under identical conditions. Random data (name, email, address, etc.) is generated for realism.

@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("Execution Time");
        // auto_increment key task
        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 task");
        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 key task
        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 task");
        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 long key task
        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 task");
        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();
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }
}

1.3 Program Write Results

1.4 Efficiency Test Results

When the existing data volume reaches about 1.3 million rows, inserting another 100 k rows shows the performance ranking: auto_key > random_key > uuid . UUID insertion becomes the slowest, and its time sharply increases as the table grows.

2. Index Structure Comparison

2.1 Auto‑Increment ID Structure

Because auto‑increment keys are sequential, InnoDB stores each new record after the previous one. When a page reaches its fill factor, the next record starts a new page, keeping pages densely packed, minimizing random I/O, and avoiding page splits.

2.2 UUID Index Structure

UUIDs are random, so new rows may need to be inserted anywhere in the B‑tree. This causes frequent page reads from disk, random I/O, page splits, and fragmentation, leading to higher write latency.

2.3 Drawbacks of Auto‑Increment

Predictable IDs expose business growth trends when scraped.

High‑concurrency inserts contend for the auto‑increment lock, creating a hotspot.

Auto‑increment lock mode can cause performance loss; tuning innodb_autoinc_lock_mode may be required.

3. Conclusion

The blog demonstrates, through controlled experiments, that auto‑increment primary keys generally outperform UUIDs and random Snowflake IDs in insert speed and index efficiency. While UUIDs have their use cases, MySQL’s official guidance to prefer sequential keys remains the optimal choice for most scenarios.