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

MySQL officially recommends using sequential auto_increment primary keys instead of UUIDs or other non‑sequential identifiers. This article explores why UUIDs and random long keys can degrade performance by creating three tables— user_auto_key , user_uuid , and user_random_key —that share identical columns except for the primary‑key generation strategy.

Using Spring Boot, JdbcTemplate, JUnit, and Hutool, the author inserts a large volume of synthetic data into each table under identical conditions and measures both insert and query times. The test code 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
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
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
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 shows that with a base of 1.3 million rows, inserting an additional 100 k rows results in the following ranking of speed: auto_increment > random > UUID. As the total row count grows, UUID performance drops sharply, while auto_increment remains consistently fast.

From an index‑structure perspective, auto_increment keys are sequential, allowing InnoDB to append new rows to the end of the clustered index page, achieve high page‑fill factors, avoid page splits, and minimize random I/O. In contrast, UUIDs are random; each insert may require locating a non‑sequential page, causing frequent page splits, random disk reads, and fragmentation, which explains the observed slowdown.

Despite its performance benefits, auto_increment is not without drawbacks: it can expose business growth trends, become a hotspot under high concurrency due to gap‑lock contention, and suffer from the auto_increment lock overhead. Tuning innodb_autoinc_lock_mode can mitigate some of these issues.

In conclusion, for most InnoDB workloads the official recommendation to use sequential auto_increment primary keys holds true; they provide superior insert performance and simpler index maintenance. UUIDs or other random keys should only be chosen when their uniqueness properties outweigh the performance penalties.