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

MySQL recommends using auto_increment primary keys instead of UUID or non‑sequential snowflake IDs; this article investigates why.

Test Setup

Three tables— user_auto_key , user_uuid , and user_random_key —are created, each differing only by the primary key generation strategy. Insertion and query performance are measured using a Spring Boot application with JdbcTemplate, JUnit, and Hutool.

Test Code

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);
    }
}

Results

Benchmark images show that with about 1.3 million existing rows, inserting 100 k new rows yields the fastest throughput for the auto_increment table, slower performance for the random‑key table, and the slowest for the UUID table; the gap widens as data volume grows.

Index Structure Comparison

Auto‑Increment Index

Because values are sequential, InnoDB stores rows contiguously, minimizing page splits, reducing random I/O, and keeping the fill factor high.

UUID Index

UUIDs are random, so InnoDB must insert rows into scattered pages, causing frequent page splits, random disk reads, and fragmentation; occasional OPTIMIZE TABLE may be needed to rebuild the clustered index.

Drawbacks of Auto‑Increment

Predictable IDs can expose business growth when the database is scraped.

High‑concurrency workloads may suffer lock contention on the primary‑key insert hotspot.

The auto_increment lock mechanism can reduce throughput; tuning innodb_autoinc_lock_mode can mitigate this.

Conclusion

The experiments demonstrate that auto_increment keys generally provide superior insert performance in MySQL, while UUID and random keys incur significant overhead due to non‑sequential index behavior; developers should select primary‑key strategies based on performance, scalability, and security requirements.

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