Why MySQL Discourages UUID as Primary Key: Performance Comparison with Auto‑Increment and Random Keys

1. MySQL Table Design and Test Setup

Three tables are created: user_auto_key (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (random long key generated by a Snowflake algorithm). All other columns remain identical to isolate the effect of the primary‑key strategy.

Images of the three table schemas are shown below:

1.2. Benchmark Program

The benchmark uses Spring Boot, JdbcTemplate , JUnit, and Hutool to insert a large number of randomly generated rows into each table under identical conditions. The 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);
    }
}

1.3. Test Results

Result screenshots for each table are displayed below:

1.4. Efficiency Findings

When the existing data volume reaches 1.3 million rows, inserting another 100 k rows shows the ranking: auto_increment > random > UUID . As the dataset grows, UUID insertion time degrades sharply, confirming its poor scalability.

2. Index Structure Comparison

2.1. Auto‑Increment Index

Because auto‑increment values are sequential, InnoDB stores new rows at the end of the current page, achieving high page‑fill factor, minimal page splits, and fast locating of the insertion point.

2.2. UUID Index

UUIDs are random; new rows may need to be placed anywhere in the B‑tree, causing frequent page splits, random I/O, and fragmentation. This often forces an OPTIMIZE TABLE operation to rebuild the clustered index.

2.3. Drawbacks of Auto‑Increment

Despite its performance benefits, auto‑increment keys expose business growth patterns, can become a hotspot under high concurrency (gap‑lock contention), and suffer from the auto‑increment lock mechanism; tuning innodb_autoinc_lock_mode can mitigate some issues.

3. Conclusion

The blog demonstrates, through practical benchmarks and index‑structure analysis, that MySQL’s recommendation to use sequential auto‑increment primary keys is justified: they provide superior insert performance and lower fragmentation, while UUIDs and random keys incur significant overhead as data volume grows.

In real‑world development, following MySQL’s official guidance to prefer auto‑increment keys is advisable, though deeper tuning of InnoDB parameters may further improve performance.