Performance Comparison of Auto‑Increment, UUID, and Random Keys in MySQL

MySQL officially advises using sequential auto_increment primary keys instead of UUIDs or non‑sequential Snowflake IDs; this article explores the underlying reasons by designing three tables— user_auto_key , user_uuid , and user_random_key —that differ only in their primary‑key generation strategy.

Using a controlled‑variables approach, the author inserts identical data into each table and measures both insert and query performance. The test environment is a Spring Boot application that leverages JdbcTemplate and JUnit, with the full Java test class provided 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时间消耗");
        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
        List
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
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
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);
    }
}

The benchmark results (shown as screenshots in the original article) reveal that the auto_increment table consistently outperforms the random‑key table, which in turn outperforms the UUID table; the performance gap widens dramatically when the dataset grows to around one million rows.

From an index‑structure perspective, sequential auto_increment keys keep new rows at the end of the clustered index, maximizing page‑fill factor, minimizing page splits, and reducing random I/O. In contrast, UUIDs are unordered, forcing InnoDB to insert rows into random pages, causing frequent page splits, fragmentation, and costly disk reads.

While auto_increment keys have advantages, the article also notes their drawbacks: they expose business growth patterns, can become a hotspot under high concurrency leading to lock contention, and the auto_increment lock mode may introduce performance overhead.

In conclusion, for InnoDB tables the recommended practice is to use monotonically increasing primary keys; UUIDs should be used only when their uniqueness across systems outweighs the significant insertion‑performance penalty.