Why Auto‑Increment Beats UUID in MySQL: Performance & Index Insights

Introduction

MySQL officially recommends using auto_increment primary keys instead of UUIDs or non‑sequential snowflake IDs, but why? This article analyzes the disadvantages of UUIDs and compares three key‑generation strategies.

1. Test Setup

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 long key). All other columns are identical to isolate the effect of the primary key.

1.2 Programmatic test with Spring JDBC

The test uses Spring Boot, JdbcTemplate, JUnit, and Hutool to insert the same amount of randomly generated data into each table and measure execution time.

<code>@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 key
        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();
        // UUID key
        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(insertData2)) {
            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 long 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("随机的long值key表任务开始");
        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("随机key任务消耗时间:" + (end - start));
        stopwatch.stop();
        System.out.println(stopwatch.prettyPrint());
    }
}
</code>

1.3 Test results

Insertion results for each table are shown below.

1.4 Efficiency comparison

When the existing data volume reaches about 1.3 million rows, inserting an additional 100 k rows shows the following performance ranking: auto_increment > random > UUID, with UUID becoming the slowest and its time sharply increasing as data grows.

Further testing with 100 k inserts on a table already containing 1.3 million rows confirms the same trend.

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, so new rows may need to be inserted into any page, causing frequent page splits, random disk I/O, and fragmentation, which dramatically degrades insert performance.

2.3 Drawbacks of auto‑increment

Despite its advantages, auto_increment has issues: exposure of business growth through sequential IDs, lock contention under high concurrency, and auto_increment lock overhead. Tuning

innodb_autoinc_lock_mode

can mitigate some of these problems.

3. Conclusion

The article demonstrates that auto_increment primary keys generally provide superior insert performance and index efficiency compared to UUID or random snowflake keys, especially as data volume grows. However, developers should also consider the security and concurrency implications of sequential IDs and adjust MySQL configurations accordingly.