Why MySQL Discourages UUID Primary Keys and a Performance Comparison of Auto‑Increment, UUID, and Random Long IDs

MySQL officially recommends using auto_increment integer primary keys instead of UUIDs or non‑sequential snowflake IDs because of performance concerns. This article explores the reasons behind this recommendation through practical experiments.

1. Test Setup

Three tables are created: user_auto_key (auto_increment primary key), user_uuid (UUID primary key), and user_random_key (snowflake‑generated long key). All other columns are identical, allowing a controlled comparison of insert and query speeds.

1.1 Table Definitions

Each table contains the same fields (user_id, user_name, sex, address, city, email, state) with only the primary key generation strategy differing.

1.2 Benchmark Code

package com.wyq.mysqldemo;
import cn.hutool.core.collection.CollectionUtil;
import com.wyq.mysqldemo.databaseobject.*;
import com.wyq.mysqldemo.diffkeytest.*;
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 execution time");
        // auto_increment insert
        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";
        List
insertData = autoKeyTableService.getInsertData();
        stopwatch.start("auto key task");
        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 time:" + (end1 - start1));
        stopwatch.stop();
        // UUID insert
        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 task");
        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 time:" + (over - begin));
        stopwatch.stop();
        // Random key insert
        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("random key task");
        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("random key time:" + (end - start));
        stopwatch.stop();
        System.out.println(stopwatch.prettyPrint());
    }
}

1.3 Test Results

The benchmark shows that with ~1.3 million existing rows, inserting 100 k new rows yields the following relative performance: auto_increment > random > UUID. UUID insertion time degrades sharply as data volume grows, eventually becoming the slowest.

2. Index Structure Comparison

2.1 Auto‑Increment Primary Key

Because the values are sequential, InnoDB stores new rows at the end of the current page, achieving high page fill factor, minimal page splits, and reduced random I/O.

2.2 UUID Primary Key

UUIDs are random, causing InnoDB to insert rows into arbitrary pages, which leads to frequent page splits, random I/O, and fragmentation. Additional overhead such as OPTIMIZE TABLE may be required to rebuild the table.

2.3 Drawbacks of Auto‑Increment

While generally faster, auto_increment keys expose business growth patterns, can become a hotspot under high concurrency (gap‑lock contention), and suffer from auto_increment lock contention.

3. Conclusion

The experiments demonstrate that UUID and random long keys incur significant insertion overhead in MySQL due to non‑sequential index placement, whereas auto_increment keys provide the best performance for large‑scale inserts. Nevertheless, developers should weigh security and concurrency considerations when choosing a primary key strategy.