Why Using Snowflake IDs or UUIDs as MySQL Primary Keys Hurts Performance

MySQL officially recommends auto_increment over UUID or non‑sequential Snowflake IDs for primary keys. This article investigates the underlying reasons through experiments and index‑structure analysis.

1. Experiment Setup

1.1 Create three tables

Three tables are created with identical columns except for the primary‑key generation strategy: user_auto_key uses an auto_increment key, user_uuid uses a UUID primary key, and user_random_key uses a Snowflake‑style 18‑digit long random key.

1.2 Test program

A Spring Boot application with JdbcTemplate, JUnit and Hutool generates random data (names, emails, addresses, etc.) and inserts the same amount 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("Execution time");
        // 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("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 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 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 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("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();
        String result = stopwatch.prettyPrint();
        System.out.println(result);
    }
}

1.3 Insertion results

Images (omitted here) show that the auto_increment table has the shortest insertion time, the random‑key table is slower, and the UUID table is the slowest.

1.4 Efficiency test with existing data

When the database already contains about 1.3 million rows, inserting an additional 100 k rows reveals that UUID insertion time drops sharply after the large data load, but overall UUID remains the least efficient. The ranking of insertion speed is: auto_increment > random_key > UUID.

2. Index Structure Comparison

2.1 Auto‑increment internal structure

Because auto_increment values are sequential, InnoDB stores each new row at the end of the current page until the page reaches its fill factor (default 15/16). This yields near‑sequential page filling, minimizes page splits, and allows fast locating of the insertion point.

2.2 UUID internal structure

UUID values are random, so new rows may need to be placed anywhere in the clustered index. This forces InnoDB to locate arbitrary pages (often causing random I/O), perform frequent page splits (each insert may modify three or more pages), and creates fragmentation. Occasionally an OPTIMIZE TABLE is required to rebuild the table and improve page density.

2.3 Drawbacks of auto‑increment

Despite its performance advantages, auto_increment has downsides: (1) exposing sequential IDs reveals business growth when the data is scraped; (2) under high concurrency, the primary‑key hotspot can cause lock contention and gap‑lock competition; (3) the auto_increment lock mechanism itself can become a bottleneck. Adjusting innodb_autoinc_lock_mode can mitigate some of these issues.

3. Conclusion

The experiments and index‑structure analysis explain why UUID and random Snowflake IDs incur higher insertion costs in MySQL. Following MySQL’s recommendation to use sequential auto_increment primary keys—and, more generally, monotonic increasing clustered keys—yields the best overall performance and storage efficiency.