Why Auto‑Increment Beats UUID in MySQL: Performance Test & Deep Dive

Test Setup

Three InnoDB tables were created with identical non‑key columns (user_id, user_name, sex, address, city, email, state). The primary key differs: user_auto_key:

BIGINT AUTO_INCREMENT PRIMARY KEY

user_uuid

: CHAR(36) PRIMARY KEY (UUID string) user_random_key: BIGINT PRIMARY KEY generated by Snowflake algorithm (18‑digit long)

Benchmark program

Implemented with Spring Boot, JdbcTemplate, JUnit and Hutool. For each table the program generates the same amount of random rows (names, emails, etc.) and inserts them in batch using a utility method JdbcTemplateService.insert(sql, dataList, generateKey). Execution time is measured with StopWatch.

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;

@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(?,?,?,?,?,?,?)";
        var autoData = autoKeyTableService.getInsertData();
        stopwatch.start("auto_key");
        long t1 = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(autoData)) {
            jdbcTemplateService.insert(insertSql, autoData, false);
        }
        System.out.println("auto key time:" + (System.currentTimeMillis() - t1));
        stopwatch.stop();

        // UUID insert
        final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        var uuidData = uuidKeyTableService.getInsertData();
        stopwatch.start("uuid_key");
        long t2 = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(uuidData)) {
            jdbcTemplateService.insert(insertSql2, uuidData, true);
        }
        System.out.println("UUID key time:" + (System.currentTimeMillis() - t2));
        stopwatch.stop();

        // random long key insert
        final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";
        var randomData = randomKeyTableService.getInsertData();
        stopwatch.start("random_key");
        long t3 = System.currentTimeMillis();
        if (CollectionUtil.isNotEmpty(randomData)) {
            jdbcTemplateService.insert(insertSql3, randomData, true);
        }
        System.out.println("random key time:" + (System.currentTimeMillis() - t3));
        stopwatch.stop();

        System.out.println(stopwatch.prettyPrint());
    }
}

Insertion results

When inserting up to one million rows, the auto‑increment table consistently showed the shortest elapsed time, the random‑key table was slower, and the UUID table was the slowest. Additional tests inserting 100 k rows into a table that already contained 1.3 M rows confirmed the same ordering: auto > random > UUID . The performance gap widens as the existing data volume grows.

Index structure comparison

Auto‑increment primary key

Because the key values increase monotonically, InnoDB appends each new row to the end of the current leaf page. When a page reaches its fill factor (default 15/16), a new page is allocated. This minimizes page splits, reduces random I/O, and keeps the clustered index dense.

UUID primary key

UUID values are essentially random. New rows may need to be placed anywhere in the B‑tree, causing frequent page splits and random disk reads. The resulting fragmentation increases write amplification and may require OPTIMIZE TABLE to rebuild the clustered index.

Random Snowflake key

Snowflake‑generated long values are also non‑sequential, so they exhibit similar but slightly better behavior than UUIDs because the numeric type is smaller and comparisons are cheaper. Nevertheless, page splits and random I/O still occur.

Drawbacks of auto‑increment

Predictable identifiers expose business growth when scraped.

High‑concurrency inserts can create a hotspot on the auto‑increment lock, leading to gap‑lock contention.

The lock can become a performance bottleneck; tuning innodb_autoinc_lock_mode may be required.

Conclusion

The benchmark validates MySQL’s recommendation: for InnoDB tables a monotonically increasing primary key (auto‑increment) yields the best insert performance. UUIDs and random Snowflake keys incur significant overhead due to non‑sequential inserts, page fragmentation, and additional I/O. In performance‑critical workloads auto‑increment should be preferred, while being aware of its predictability and concurrency implications.

Demo source code and scripts are available at https://gitee.com/Yrion/mysqlIdDemo