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

Introduction

MySQL recommends using auto_increment primary keys rather than UUIDs or non‑sequential snowflake IDs. This article investigates why UUIDs are discouraged by testing insertion and query performance.

Test Setup

Three tables were created: user_auto_key (auto_increment), user_uuid (UUID primary key), and user_random_key (snowflake‑like random long). Using Spring Boot, JdbcTemplate, JUnit, and Hutool, identical data sets were inserted to measure speed.

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<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
        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(insertData2)) {
            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 key
        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(insertData3)) {
            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

Insertion performance (rows per second) showed auto_increment fastest, random key second, and UUID slowest. With 1.3 million existing rows, inserting 100 k more highlighted UUID’s performance degradation. Images illustrate the insert results and timing charts.

Index Structure Comparison

Auto‑Increment Index

Sequential primary keys allow InnoDB to store rows consecutively, minimizing page splits, reducing random I/O, and achieving a high fill factor.

UUID Index

UUIDs are random; inserts require locating new pages, causing random I/O, frequent page splits, and fragmentation, which leads to slower performance.

Drawbacks of Auto‑Increment

Predictable IDs expose business growth to external observers.

High concurrency can cause lock contention on the primary key.

Auto_increment lock mechanism may incur performance loss (tunable via innodb_autoinc_lock_mode).

Conclusion

The benchmarks confirm that auto_increment keys generally outperform UUIDs and random keys in MySQL insert operations, especially at large data volumes. While auto_increment has security and concurrency considerations, it remains the recommended choice according to MySQL documentation.

Demo code is available at https://gitee.com/Yrion/mysqlIdDemo .