Why Using Snowflake IDs or UUIDs as MySQL Primary Keys Can Backfire

Introduction

MySQL officially recommends auto_increment as the primary key because it yields a sequential, dense index. This article investigates why using UUIDs or Snowflake‑style random long IDs (18‑digit longs) as primary keys can degrade performance and raise other concerns.

1. Experiment Setup

1.1 Create three tables

Three tables are created with identical columns except for the primary key strategy: user_auto_key – auto‑increment integer primary key user_uuid – CHAR(36) UUID primary key user_random_key – Snowflake‑style long primary key (non‑sequential)

1.2 Insert‑and‑query test using Spring Boot

The test uses springboot + jdbcTemplate + junit + hutool. Random data (name, email, address, etc.) is generated, and the same volume of rows is inserted into each table while measuring execution 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 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 insert");
        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 insert");
        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 insert");
        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

Insertion time and throughput are recorded for each table. When the existing row count reaches about 1.3 million, inserting an additional 100 k rows yields the following ranking (fastest to slowest): auto_increment > random > UUID . UUID performance drops sharply as data volume grows.

2. Index Structure Comparison

2.1 Auto‑increment key internal structure

Because the primary key values increase monotonically, InnoDB stores new rows at the end of the current page. When a page reaches its fill factor (default 15/16), the next row starts a new page, minimizing page splits, reducing random I/O, and keeping the clustered index dense.

2.2 UUID key internal structure

UUIDs are essentially random. InnoDB cannot always append new rows to the end of the index; it must locate an appropriate leaf page, which often requires loading pages from disk, causing random I/O. The lack of order leads to frequent page splits, extra page moves, and eventual fragmentation. Occasionally an OPTIMIZE TABLE is needed to rebuild the clustered index.

2.3 Drawbacks of auto‑increment keys

Predictable sequence reveals business growth when the table is scraped.

High‑concurrency inserts contend for the auto‑increment lock (gap lock and auto‑increment lock), creating a hotspot.

The auto‑increment lock itself incurs a performance penalty; tuning innodb_autoinc_lock_mode can mitigate it.

3. Conclusion

The benchmark demonstrates that sequential auto‑increment keys give the best insert performance and the simplest index layout, while UUID or Snowflake random keys suffer from random I/O, page splits, and fragmentation. In practice, follow MySQL’s recommendation to use auto‑increment for primary keys unless you need the security or distribution benefits of UUIDs, and be aware of the lock‑contention issues that may require configuration tweaks.