Why Auto‑Increment Beats UUID in MySQL: Performance Test & Deep Dive

Preface: MySQL officially recommends using auto_increment primary keys instead of UUIDs or non‑sequential Snowflake IDs. This article analyzes why UUIDs are discouraged and examines their drawbacks.

1: MySQL and Application Instances

1.1 To illustrate, three tables are created: user_auto_key (auto‑increment PK), user_uuid (UUID PK), and user_random_key (random Snowflake‑style PK). All other columns are identical, allowing a controlled comparison of insert and query performance.

Note: the random key is a non‑sequential 18‑digit long value generated by the Snowflake algorithm.

Table schemas are shown below:

User UUID table:

Random primary key table:

1.2 Test Program

The test uses Spring Boot, JdbcTemplate, JUnit, and Hutool to insert a large number of rows into each table under identical conditions.

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 Program 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 is the lowest, and its time degrades sharply as data grows. Overall ranking: auto_increment > random_key > UUID.

2: Index Structure Comparison Between UUID and Auto‑Increment IDs

2.1 Internal Structure of Auto‑Increment ID

Auto‑increment keys are sequential, so InnoDB stores each new record after the previous one. When a page reaches its fill factor (default 15/16), a new page is allocated. This yields near‑sequential page fills, fast locating, and reduced page splits and fragmentation.

2.2 Index Structure of UUID

UUIDs are random; new rows may need to be inserted anywhere in the B‑tree. InnoDB must locate and load target pages from disk, causing random I/O, frequent page splits, and fragmentation. Occasionally an OPTIMIZE TABLE is required to rebuild the table and improve fill factor.

2.3 Drawbacks of Auto‑Increment IDs

Despite advantages, auto‑increment IDs have downsides:

They expose business growth if the database is scraped.

High‑concurrency inserts cause lock contention on the primary‑key hotspot.

The auto_increment lock mechanism can incur performance loss; tuning innodb_autoinc_lock_mode can mitigate this.

3: Conclusion

The blog first posed the question, created tables, and used JdbcTemplate to benchmark different ID generation strategies on large data volumes. It then analyzed the underlying index mechanisms and their pros and cons, explaining why UUIDs and random IDs suffer performance penalties during inserts. In practice, following MySQL’s recommendation to use auto_increment primary keys yields better performance, though developers should be aware of the associated trade‑offs and possible tuning.

Demo code repository: https://gitee.com/Yrion/mysqlIdDemo