Why MySQL Does Not Recommend UUIDs as Primary Keys: Performance Analysis and Index Structure Comparison

The article starts by questioning MySQL's official advice to avoid UUIDs or non‑sequential random IDs as primary keys and to prefer auto_increment, then sets out to explore the underlying reasons.

Three tables are created— user_auto_key (auto‑increment), user_uuid (UUID primary key), and user_random_key (Snowflake‑generated long values)—while keeping all other columns identical to isolate the impact of the primary key strategy.

Using Spring Boot with JdbcTemplate , JUnit, and Hutool, the author inserts a large volume of randomly generated data into each table and measures the time taken for insert operations. The test 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 test
        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 test
        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 test
        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);
    }
}

The benchmark results show that with around 1.3 million existing rows, inserting 100,000 new rows makes UUID inserts the slowest, followed by random keys, while auto‑increment remains the fastest. As data volume grows, UUID performance degrades sharply.

The article then compares the internal index structures. Auto‑increment keys are sequential, allowing InnoDB to append new rows to the end of the clustered index, minimizing page splits, random I/O, and fragmentation. In contrast, UUIDs are random, causing InnoDB to locate arbitrary pages, trigger frequent page splits, increase random I/O, and create fragmentation, sometimes requiring an OPTIMIZE TABLE operation.

Despite the advantages of sequential keys, the author notes drawbacks of auto‑increment: exposure of business growth patterns, lock contention under high concurrency, and the auto_increment lock mechanism that can affect performance. Tuning innodb_autoinc_lock_mode can mitigate some issues.

In conclusion, the blog recommends following MySQL's guidance to use auto‑increment primary keys for most scenarios, while acknowledging that understanding the internal storage engine behavior is essential for advanced optimization.