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

The article begins by stating MySQL's official recommendation to avoid UUID or non‑sequential snowflake IDs and to prefer auto_increment primary keys, then poses the question of why UUIDs are discouraged.

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

A Spring Boot test suite using JdbcTemplate, JUnit, and Hutool generates random data (names, emails, addresses) and inserts the same volume of rows into each table while measuring execution time with StopWatch. The core 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 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);
    }
}

The benchmark results show that with a dataset of about 1.3 million rows, inserting another 100 k rows, the auto_increment table consistently outperforms the random‑key table, which in turn outperforms the UUID table; the UUID insertion time degrades sharply as data volume grows.

Next, the article compares the internal index structures of auto_increment and UUID primary keys. Auto_increment keys are sequential, allowing InnoDB to append new rows to the end of the current page, achieving high page‑fill factors, minimal page splits, and fast locating. In contrast, UUIDs are random, forcing InnoDB to locate arbitrary pages, causing frequent page splits, random I/O, and fragmentation, sometimes requiring an OPTIMIZE TABLE operation.

The author also notes drawbacks of auto_increment keys: exposure of business growth patterns, lock contention under high concurrency, and the auto_increment lock mechanism that can affect performance. Adjusting innodb_autoinc_lock_mode can mitigate some issues.

In conclusion, the blog recommends using auto_increment primary keys for MySQL tables whenever possible, while understanding the trade‑offs of UUIDs and random snowflake IDs, especially regarding index efficiency and scalability.

The demo code repository is provided at https://gitee.com/Yrion/mysqlIdDemo .