Why MySQL Auto‑Increment Outperforms UUID: Full Performance Test and Analysis

MySQL officially recommends using auto_increment primary keys instead of UUIDs or non‑sequential Snowflake IDs. This article investigates why, by creating three tables—user_auto_key, user_uuid, and user_random_key—each differing only in primary key generation strategy.

1. MySQL Test Setup

1.1 Create three tables

The tables use auto‑increment, UUID, and a random Snowflake‑generated long value respectively, while keeping all other columns identical. Insertion and query speed are measured using a controlled experiment.

Note: the “random key” refers to an 18‑digit long value generated by the Snowflake algorithm.

1.2 Test program

The tests are implemented with Spring Boot, JdbcTemplate, JUnit and Hutool. Data is randomly generated to simulate realistic workloads.

@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 test
        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 test
        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(insertData)) {
            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 test
        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(insertData)) {
            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 Test results

Insert performance (rows per second) shows: auto_increment > random_key > UUID. With 1.3 million existing rows, inserting 100 k new rows reveals UUID insertion time drops sharply, confirming its inefficiency at larger scales.

2. Index Structure Comparison

2.1 Auto‑increment index

Because auto‑increment keys are sequential, InnoDB stores new rows at the end of the current page, minimizing page splits, random I/O, and fragmentation.

2.2 UUID index

UUIDs are random; new rows may need to be placed anywhere in the B‑tree, causing frequent page splits, random disk reads, and fragmentation, which degrades performance.

2.3 Drawbacks of auto‑increment

Auto‑increment keys expose business growth patterns and can become a contention hotspot under high concurrency, leading to lock competition and a slight performance penalty.

Tip: Adjust innodb_autoinc_lock_mode to mitigate lock contention.

3. Conclusion

The experiments demonstrate that auto‑increment primary keys generally provide superior insert performance and index efficiency compared to UUID or random Snowflake IDs, especially as data volume grows. While auto‑increment has some security and concurrency considerations, it remains the recommended choice for MySQL tables.