Which Primary Key Is Faster in MySQL? Auto‑Increment vs UUID vs Snowflake

1: MySQL and Program Example

We create three tables: user_key_auto (auto‑increment primary key), user_uuid (UUID primary key), and user_random_key (random long key generated by the Snowflake algorithm). All other columns are identical; we compare insert and query speed.

Random key refers to a non‑sequential 18‑digit long value produced by Snowflake.

Table schemas are illustrated below:

1.2 Program Implementation

Using Spring Boot, JdbcTemplate, JUnit and Hutool, we insert the same amount of randomly generated data into each table and measure execution time. The code is available on Gitee (link omitted).

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

Results of insertion are shown in the following screenshots:

1.4 Efficiency Test Results

When the existing data volume reaches 1.3 million rows, inserting an additional 100 k rows shows that auto‑increment performs best, random key is second, and UUID is the slowest. As data grows, UUID performance degrades sharply.

2: Index Structure Comparison Between UUID and Auto‑Increment

2.1 Internal Structure of Auto‑Increment ID

Auto‑increment keys are sequential, so InnoDB stores new rows at the end of the page, minimizing page splits and random I/O.

2.2 Internal Structure of UUID Index

UUIDs are non‑sequential; InnoDB must locate appropriate pages for each insert, causing random I/O, frequent page splits, and fragmentation, which leads to higher latency.

2.3 Drawbacks of Auto‑Increment ID

Despite its performance advantages, auto‑increment IDs expose business growth patterns, become a hotspot under high concurrency, and suffer from auto‑increment lock contention.

3: Conclusion

The article demonstrates that for large‑scale data insertion in MySQL, auto‑increment primary keys generally outperform UUID and random Snowflake IDs due to better index locality and fewer page splits. However, security, concurrency, and business requirements may influence the choice of primary‑key strategy.