Why Using UUID as MySQL Primary Key Is Inefficient: A Performance Comparison with Auto‑Increment and Random Keys

The author, a senior architect, starts by questioning MySQL's official advice to avoid UUID or non‑sequential keys and to prefer auto_increment primary keys, then outlines the goal of analyzing why UUIDs may be problematic.

1. MySQL Experiment Setup

Three tables are created: user_auto_key (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (snowflake‑generated 18‑digit long key). All other columns remain identical to isolate the effect of the primary key generation strategy.

The test inserts a large volume of randomly generated data using SpringBoot, JdbcTemplate, JUnit, and Hutool, measuring insert time with StopWatch. The relevant Java test code is:

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<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 task
        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 long key task
        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);
    }
}

Result screenshots (auto‑increment, random key, UUID) are shown below:

When the table already contains about 1.3 million rows, inserting another 100 k rows shows that UUID insert speed drops dramatically, while auto‑increment remains fastest and random (snowflake) is intermediate.

2. Index Structure Comparison

2.1 Auto‑increment index – because the primary key values are sequential, InnoDB stores rows in order, minimizing page splits, reducing random I/O, and keeping the fill factor high.

2.2 UUID index – UUIDs are random, so new rows cannot be appended to the end of the clustered index. InnoDB must locate a suitable page, causing frequent page splits, random disk reads, and fragmentation.

The article lists the concrete drawbacks of UUID inserts: extra random I/O, frequent page splits (modifying three or more pages per insert), and eventual need for OPTIMIZE TABLE to rebuild the table.

3. Drawbacks of Auto‑Increment

Although auto‑increment performs best, it has security and concurrency issues: exposure of business growth through predictable IDs, lock contention on the primary‑key hotspot under high concurrency, and the overhead of the auto‑increment lock mode.

4. Conclusion

The benchmark demonstrates that for large‑scale data insertion, auto‑increment keys outperform UUID and random snowflake keys, while UUIDs suffer severe performance degradation as data volume grows. The recommendation is to follow MySQL's guidance and use monotonic, sequential primary keys for clustered tables, while being aware of the security and lock‑contention trade‑offs.