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

Introduction

In MySQL table design, the official recommendation is to avoid UUID or non‑sequential Snowflake IDs and to use auto_increment primary keys. This article analyzes why UUID is discouraged and what drawbacks it has.

1. MySQL and Program Example

1.1 Create Three Tables

We create three tables: user_auto_key (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (random long key generated by the Snowflake algorithm). All other columns remain identical. Using the controlled variable method, we only change the primary‑key generation strategy and test insert and query performance.

Note: the random key refers to an 18‑digit long value generated by the Snowflake algorithm.

Table definitions are shown in the accompanying images.

1.2 Program Implementation

Technical stack: Spring Boot, jdbcTemplate, JUnit, Hutool. The program connects to a test database, inserts the same amount of randomly generated data, and measures execution time with StopWatch .

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

1.3 Program Results

Insert results for each table are displayed in the accompanying screenshots.

1.4 Efficiency Test Results

When the existing data volume reaches 1.3 million rows, we insert another 100 k rows and observe the results (image omitted). The test shows that with around 1 million rows, UUID insertion efficiency is the lowest and degrades sharply as the data set grows.

2. Index Structure Comparison Between UUID and Auto‑Increment

2.1 Internal Structure of Auto‑Increment ID

Auto‑increment keys are sequential, so InnoDB stores each new record after the previous one, filling pages efficiently, reducing page splits, and allowing fast location of the new row.

2.2 Index Structure of UUID

UUIDs are random; new rows may not be larger than existing keys, so InnoDB cannot always append to the end of the index. It must locate a suitable page, causing random I/O, frequent page splits, and fragmentation.

2.3 Drawbacks of Auto‑Increment ID

Despite its advantages, auto‑increment IDs have drawbacks:

They expose business growth patterns when the database is scraped.

Under high concurrency, the primary‑key hotspot leads to lock contention and gap‑lock competition.

The auto_increment lock mechanism adds performance overhead.

Appendix: Auto_increment lock contention can be mitigated by tuning innodb_autoinc_lock_mode .

3. Conclusion

This blog first posed the question, created tables, used jdbcTemplate to test different key generation strategies on large data sets, and then analyzed the underlying index mechanisms and pros/cons, explaining why UUID and random non‑sequential IDs incur performance penalties. In practice, MySQL’s official recommendation to use auto‑increment IDs remains the best choice, though many other optimization points exist.