Why MySQL Auto‑Increment Beats UUID: A Deep Dive into Insertion Performance and Index Structure

Experiment Setup

Tables

Three tables are created with identical non‑key columns: user_auto_key (auto_increment primary key), user_uuid (UUID primary key), and user_random_key (Snowflake‑generated 18‑digit long key). This controlled‑variable design isolates the impact of primary‑key generation strategy.

Test program

The benchmark uses Spring Boot, JdbcTemplate, JUnit and Hutool. For each table a large batch of random rows (name, email, address, etc.) is generated and inserted with a StopWatch to measure bulk‑insert latency.

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);
    }
}

Results

Screenshot output shows that the auto_increment table consistently finishes fastest, the random‑key table is slower, and the UUID table is the slowest.

Large‑data insertion test

When each table already contains 1.3 million rows, inserting an additional 100 k rows reveals a sharp slowdown for the UUID table once the total row count exceeds roughly 1 million.

Overall insertion speed ranking: auto_increment > random_key > UUID . UUID becomes a bottleneck as data volume grows.

Index Structure Comparison

Auto‑increment primary key

Sequential values cause InnoDB to append 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, minimizing page splits, reducing random I/O, and keeping the clustered index tightly packed.

UUID primary key

UUID values are essentially random. New rows may need to be inserted anywhere in the B‑tree, forcing InnoDB to locate the target page (often from disk), causing random I/O. Frequent page splits are required to make space, moving multiple pages per insert and producing fragmentation.

Consequences:

Extra random I/O to read target pages.

Frequent page splits, each moving three or more pages.

Sparse pages and fragmentation.

Occasional need to run OPTIMIZE TABLE to rebuild the clustered index.

Drawbacks of auto‑increment

Predictable IDs expose business growth when the table is scraped.

Under high concurrency the auto_increment column becomes a hotspot, leading to lock contention and gap‑lock competition.

The auto_increment lock mechanism adds overhead.

Adjusting innodb_autoinc_lock_mode can mitigate lock contention.

Conclusion

Bulk‑insert benchmarks demonstrate that a sequential auto_increment primary key outperforms both UUID and random Snowflake keys, especially beyond one million rows. UUID’s randomness incurs significant random I/O, page splits and fragmentation, causing its insertion time to degrade sharply. Auto_increment offers superior performance but introduces predictability concerns and potential lock contention under extreme concurrency; these issues can be alleviated by tuning innodb_autoinc_lock_mode. In typical MySQL workloads, following the official recommendation to use auto_increment yields the best overall performance.