Why Auto‑Increment Beats UUID in MySQL: Performance Test and Index Analysis

Introduction

MySQL officially recommends using a sequential auto_increment primary key rather than UUID or non‑sequential snowflake IDs. This article analyzes the reasons behind this recommendation and evaluates the performance impact of different key strategies.

1. MySQL Program Instance

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 long key generated by the Snowflake algorithm). All other columns remain identical.

The following images illustrate the table structures:

1.2 Benchmark Code

The benchmark uses SpringBoot, JdbcTemplate, JUnit and Hutool. It inserts the same amount of data into each table and measures the time with StopWatch. The core test class is shown below:

package com.wyq.mysqldemo;
import cn.hutool.core.collection.CollectionUtil;
import com.wyq.mysqldemo.databaseobject.UserKeyAuto;
import com.wyq.mysqldemo.databaseobject.UserKeyRandom;
import com.wyq.mysqldemo.databaseobject.UserKeyUUID;
import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;
import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;
import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;
import com.wyq.mysqldemo.util.JdbcTemplateService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StopWatch;
import java.util.List;

@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 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("自动生成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 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表任务开始");
        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();

        // 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("随机的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

Images below show the insertion outcomes for each table:

1.4 Efficiency Test Results

When the existing data reaches about 1.3 million rows, inserting another 100 k rows shows that the UUID table has the worst insertion speed, and its performance degrades sharply as data volume grows.

The overall ranking of insertion speed is auto_key > random_key > uuid . UUID’s efficiency drops dramatically with larger datasets.

2. Index Structure Comparison

2.1 Auto‑Increment Index

Because auto_increment values are sequential, InnoDB stores each new row at the end of the current page. When a page reaches its fill factor (default 15/16), a new page is allocated, minimizing page splits and random I/O.

Advantages include:

High page fill rate and reduced waste.

Fast location and addressing of new rows.

Fewer page splits and less fragmentation.

2.2 UUID Index

UUID values are random, so new rows may need to be inserted anywhere in the B‑tree. InnoDB must locate the appropriate page, often causing random disk I/O and frequent page splits.

Consequences:

Random I/O due to loading target pages from disk.

Frequent page splits, moving multiple pages per insert.

Increased fragmentation and the need for OPTIMIZE TABLE after large inserts.

2.3 Drawbacks of Auto‑Increment

Despite its advantages, auto_increment has some downsides:

Exposes business growth trends when the database is scraped.

High concurrency can cause lock contention on the primary key hotspot.

Auto_increment lock mechanism introduces a performance overhead.

Improving auto_increment lock contention may require tuning innodb_autoinc_lock_mode .

3. Conclusion

The blog first posed the question of why MySQL discourages UUIDs, then built tables and used JdbcTemplate to benchmark insertion performance across different key strategies. It analyzed the internal index structures, highlighted the performance penalties of UUID and random keys, and discussed the pros and cons of each approach.

In practice, following MySQL’s recommendation to use auto_increment primary keys yields better performance, though developers should still be aware of its limitations and consider further optimizations where needed.