Performance Comparison of Auto‑Increment, UUID, and Random Keys in MySQL

MySQL officially recommends using sequential auto‑increment primary keys instead of UUIDs or non‑sequential snowflake IDs; this article investigates the reasons by creating three tables (auto_increment, UUID, and random key) that differ only in their primary‑key generation strategy.

Using Spring Boot, JdbcTemplate, JUnit, and Hutool, the author inserts identical synthetic data into each table and measures insertion time with a StopWatch, capturing detailed timing for each key type.

The benchmark results show that with around 1.3 million existing rows, inserting 100 k new rows ranks the performance as auto_increment > random_key > UUID, with UUID insertion time degrading sharply as data volume grows.

Further analysis compares the index structures: auto_increment keys produce sequential page fills, minimizing page splits and random I/O, while UUIDs cause random page inserts, frequent page splits, and increased fragmentation, leading to higher I/O and the need for occasional OPTIMIZE TABLE operations.

The article also notes drawbacks of auto_increment keys, such as exposure of business growth patterns, lock contention under high concurrency, and auto_increment lock overhead, suggesting configuration tuning (e.g., innodb_autoinc_lock_mode) when necessary.

In conclusion, for most MySQL workloads, sequential auto‑increment primary keys provide superior insert performance and index efficiency, and developers should prefer them unless specific use‑cases justify UUIDs or other random keys.

<span>package com.wyq.mysqldemo;</span>
<span>import cn.hutool.core.collection.CollectionUtil;</span>
<span>import com.wyq.mysqldemo.databaseobject.UserKeyAuto;</span>
<span>import com.wyq.mysqldemo.databaseobject.UserKeyRandom;</span>
<span>import com.wyq.mysqldemo.databaseobject.UserKeyUUID;</span>
<span>import com.wyq.mysqldemo.diffkeytest.AutoKeyTableService;</span>
<span>import com.wyq.mysqldemo.diffkeytest.RandomKeyTableService;</span>
<span>import com.wyq.mysqldemo.diffkeytest.UUIDKeyTableService;</span>
<span>import com.wyq.mysqldemo.util.JdbcTemplateService;</span>
<span>import org.junit.jupiter.api.Test;</span>
<span>import org.springframework.beans.factory.annotation.Autowired;</span>
<span>import org.springframework.boot.test.context.SpringBootTest;</span>
<span>import org.springframework.util.StopWatch;</span>
<span>import java.util.List;</span>

<span>@SpringBootTest</span>
<span>class MysqlDemoApplicationTests {</span>

<span>    @Autowired</span>
<span>    private JdbcTemplateService jdbcTemplateService;</span>

<span>    @Autowired</span>
<span>    private AutoKeyTableService autoKeyTableService;</span>

<span>    @Autowired</span>
<span>    private UUIDKeyTableService uuidKeyTableService;</span>

<span>    @Autowired</span>
<span>    private RandomKeyTableService randomKeyTableService;</span>

<span>    @Test</span>
<span>    void testDBTime() {</span>
<span>        StopWatch stopwatch = new StopWatch("执行sql时间消耗");</span>

<span>        // auto_increment key test</span>
<span>        final String insertSql = "INSERT INTO user_key_auto(user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?)";</span>
<span>        List<UserKeyAuto> insertData = autoKeyTableService.getInsertData();</span>
<span>        stopwatch.start("自动生成key表任务开始");</span>
<span>        long start1 = System.currentTimeMillis();</span>
<span>        if (CollectionUtil.isNotEmpty(insertData)) {</span>
<span>            boolean insertResult = jdbcTemplateService.insert(insertSql, insertData, false);</span>
<span>            System.out.println(insertResult);</span>
<span>        }</span>
<span>        long end1 = System.currentTimeMillis();</span>
<span>        System.out.println("auto key消耗的时间:" + (end1 - start1));
<span>        stopwatch.stop();</span>

<span>        // UUID key test</span>
<span>        final String insertSql2 = "INSERT INTO user_uuid(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";</span>
<span>        List<UserKeyUUID> insertData2 = uuidKeyTableService.getInsertData();</span>
<span>        stopwatch.start("UUID的key表任务开始");</span>
<span>        long begin = System.currentTimeMillis();</span>
<span>        if (CollectionUtil.isNotEmpty(insertData)) {</span>
<span>            boolean insertResult = jdbcTemplateService.insert(insertSql2, insertData2, true);</span>
<span>            System.out.println(insertResult);</span>
<span>        }</span>
<span>        long over = System.currentTimeMillis();</span>
<span>        System.out.println("UUID key消耗的时间:" + (over - begin));
<span>        stopwatch.stop();</span>

<span>        // Random long key test</span>
<span>        final String insertSql3 = "INSERT INTO user_random_key(id,user_id,user_name,sex,address,city,email,state) VALUES(?,?,?,?,?,?,?,?)";</span>
<span>        List<UserKeyRandom> insertData3 = randomKeyTableService.getInsertData();</span>
<span>        stopwatch.start("随机的long值key表任务开始");</span>
<span>        Long start = System.currentTimeMillis();</span>
<span>        if (CollectionUtil.isNotEmpty(insertData)) {</span>
<span>            boolean insertResult = jdbcTemplateService.insert(insertSql3, insertData3, true);</span>
<span>            System.out.println(insertResult);</span>
<span>        }</span>
<span>        Long end = System.currentTimeMillis();</span>
<span>        System.out.println("随机key任务消耗时间:" + (end - start));
<span>        stopwatch.stop();</span>

<span>        String result = stopwatch.prettyPrint();</span>
<span>        System.out.println(result);
<span>    }
<span>}