Performance Comparison of Auto‑Increment, UUID, and Snowflake IDs as MySQL Primary Keys

The author was criticized for using Snowflake IDs or UUIDs as MySQL primary keys and decided to conduct a systematic performance test.

1. Test tables – Three tables with identical columns were created: user_auto_key (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (Snowflake‑generated 18‑digit long key). The only variable was the primary‑key generation strategy.

2. Benchmark framework – A Spring Boot application using JdbcTemplate , JUnit, and Hutool generated random data (names, emails, addresses) and inserted the same volume into each table while measuring elapsed time with StopWatch .

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时间消耗");
        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));
        // ... similar blocks for UUID and random key ...
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }
}

The benchmark inserted roughly 100,000 rows into each table (with an existing 1.3 million rows) and recorded the time taken for each primary‑key type.

3. Results – The auto‑increment key consistently showed the best insertion performance, followed by the Snowflake random long key, while the UUID key performed the worst, especially as the total row count approached one million.

4. Index structure analysis – In InnoDB, auto‑increment keys are sequential, allowing new rows to be appended to the end of the clustered index, minimizing page splits, random I/O, and fragmentation. UUIDs (and other non‑sequential keys) cause random page inserts, frequent page splits, increased I/O, and eventual fragmentation, which explains their poorer performance.

5. Drawbacks of auto‑increment – Although faster, auto‑increment keys expose business growth patterns and can become a contention point under high concurrency due to lock hot‑spots.

Conclusion – For most MySQL workloads, especially with InnoDB, using a sequential auto‑increment primary key yields the best performance. If UUIDs or Snowflake IDs are required, consider secondary indexes or periodic table re‑organization (e.g., OPTIMIZE TABLE ) to mitigate fragmentation.

The full demo code is available at https://gitee.com/Yrion/mysqlIdDemo .