Why UUIDs Slow Down MySQL Inserts: A Deep Dive into Primary Key Performance

Introduction

MySQL officially recommends using auto_increment rather than UUIDs or other non‑sequential identifiers for primary keys. This article explores why UUIDs can be problematic by examining their impact on insert performance and index structure.

1. MySQL and Program Instance

1.1 Create Three Tables

Three tables are created: user_auto_key (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (Snowflake‑generated long key). The schema of each table is shown below.

1.2 Benchmark Program

The test uses Spring Boot with JdbcTemplate, JUnit, and Hutool to insert the same amount of randomly generated data into each table and measure execution time.

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 test
        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 key test
        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 Write Results

Result screenshots for each table are shown below.

1.4 Efficiency Test Results

When the existing data reaches 1.3 million rows, inserting an additional 100 k rows shows that UUID inserts are the slowest, and performance degrades further as data grows.

Overall efficiency ranking: auto_key > random_key > uuid . UUID performance drops sharply with larger data sets.

2. Index Structure Comparison

2.1 Auto‑Increment Internal Structure

Because auto‑increment keys are sequential, InnoDB stores each new row at the end of the current page, achieving high page‑fill rates, minimal page splits, and fast locating.

2.2 UUID Index Internal Structure

UUIDs are random, so new rows may need to be inserted anywhere in the B‑tree, causing InnoDB to perform random I/O, frequent page splits, and fragmentation.

These operations increase disk reads, page splits, and eventually require OPTIMIZE TABLE to rebuild the table.

2.3 Disadvantages of Auto‑Increment

Auto‑increment keys also have drawbacks: they expose business growth when scraped, cause hotspot lock contention under high concurrency, and incur performance loss due to the auto‑increment lock mechanism.

Improving lock contention may involve tuning innodb_autoinc_lock_mode.

3. Conclusion

The blog demonstrates, from table creation to JDBC benchmarking, that UUID and random keys incur significant performance penalties in MySQL inserts due to their impact on index structure. While auto‑increment keys are generally recommended, developers should be aware of their own security and concurrency considerations.