Why Auto‑Increment Beats UUID in MySQL: Performance Tests and Deep Dive

Preface

MySQL officially recommends avoiding UUID or non‑sequential Snowflake IDs and using auto_increment primary keys. This article analyses why UUIDs are discouraged and what drawbacks they have.

1. MySQL and program examples

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 (random Snowflake‑like key). All other columns remain identical, allowing a controlled comparison of insert and query speed.

Note: The random key is generated by a Snowflake algorithm, producing an 18‑digit long value.

Table schemas:

User UUID table schema:

Random primary key table schema:

1.2 Program implementation

The test uses Spring Boot, JdbcTemplate, JUnit, and Hutool. It connects to a test database, inserts the same amount of randomly generated data (names, emails, addresses) into each table, and measures the time spent on inserts.

@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时间消耗");
        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("自动生成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();
        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表任务开始");
        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();
        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("随机的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();
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }
}

1.3 Insertion results

Auto‑increment table result:

Random key table result:

UUID table result:

1.4 Efficiency test results

When the existing data volume reaches 1.3 million rows, inserting another 100 k rows shows that UUID insertion performance falls to the bottom, and with further growth the UUID time degrades linearly. Overall ranking: auto_key > random_key > UUID.

2. Index structure comparison

2.1 Auto‑increment internal structure

Because auto‑increment keys are sequential, InnoDB stores each new row after the previous one, filling pages efficiently, reducing page splits, and minimizing random I/O.

2.2 UUID internal structure

UUIDs are unordered; new rows may need to be inserted anywhere in the index, causing InnoDB to perform random page reads, frequent page splits, and fragmentation, which leads to higher I/O and lower insert speed.

Consequences: random I/O, frequent page splits, sparse pages, and the need for occasional OPTIMIZE TABLE to rebuild the table.

2.3 Drawbacks of auto‑increment

Auto‑increment is not without issues: (1) Predictable IDs expose business growth; (2) High‑concurrency inserts can cause lock contention on the primary key; (3) Auto_increment lock mechanism adds performance overhead. Tuning innodb_autoinc_lock_mode can mitigate some contention.

3. Conclusion

The article starts with the problem statement, builds tables, runs JdbcTemplate performance tests, and analyzes how different primary‑key strategies affect MySQL’s index structure and insert speed. It concludes that, despite UUID’s uniqueness, MySQL’s official recommendation to use auto‑increment keys remains the best choice for performance and simplicity.

Author: Yrion Source: cnblogs.com/wyq178/p/12548864.html