Why Auto‑Increment Beats UUID: MySQL Insert Performance Test

Introduction

MySQL officially recommends using auto_increment primary keys rather than UUID or non‑sequential keys; this article analyses the reasons behind that recommendation.

1. MySQL and Test Tables

1.1 Create three tables

Three tables are created with identical columns except for the primary‑key generation strategy: user_key_auto (auto_increment), user_uuid (UUID stored as VARCHAR), and user_random_key (random long generated by Snowflake algorithm).

create table user_key_auto(
  id int unsigned not null auto_increment,
  userid BIGINT(64) not null default 0,
  user_name VARCHAR(64) not null default '',
  sex int(2) not null,
  address VARCHAR(255) not null default '',
  city VARCHAR(64) not null default '',
  email VARCHAR(64) not null default '',
  state int(6) not null default 0,
  primary key(id),
  key user_name_key(user_name)
) ENGINE=INNODB;

create table user_uuid(
  id VARCHAR(36) not null,
  user_id BIGINT(64) not null default 0,
  user_name VARCHAR(64) not null default '',
  sex int(2) not null,
  address VARCHAR(255) not null default '',
  city VARCHAR(64) not null default '',
  email VARCHAR(64) not null default '',
  state int(6) not null default 0,
  primary key(id),
  key user_name_key(user_name)
) ENGINE=INNODB;

create table user_random_key(
  id BIGINT(64) not null default 0,
  user_id BIGINT(64) not null default 0,
  user_name VARCHAR(64) not null default '',
  sex int(2) not null,
  address VARCHAR(255) not null default '',
  city VARCHAR(64) not null default '',
  email VARCHAR(64) not null default '',
  state int(6) not null default 0,
  primary key(id),
  key user_name_key(user_name)
) ENGINE=INNODB;

1.2 Test using Spring JdbcTemplate

The test uses Spring Boot, JdbcTemplate, JUnit and Hutool to insert the same amount of randomly generated data into each table and measure the execution time.

@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 test
        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 result = jdbcTemplateService.insert(insertSql, insertData, false);
            System.out.println(result);
        }
        long end1 = System.currentTimeMillis();
        System.out.println("auto key time:" + (end1 - start1));
        stopwatch.stop();
        // uuid test
        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 result = jdbcTemplateService.insert(insertSql2, insertData2, true);
            System.out.println(result);
        }
        long over = System.currentTimeMillis();
        System.out.println("UUID key time:" + (over - begin));
        stopwatch.stop();
        // random key test
        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 result = jdbcTemplateService.insert(insertSql3, insertData3, true);
            System.out.println(result);
        }
        long end = System.currentTimeMillis();
        System.out.println("random key time:" + (end - start));
        stopwatch.stop();
        System.out.println(stopwatch.prettyPrint());
    }
}

1.3 Insertion Results

Insertion time for each table:

1.4 Efficiency Test Results

When the existing data reaches 1.3 million rows, inserting an additional 100 k rows shows the following performance ranking:

2. Index Structure Comparison

2.1 Auto‑increment internal structure

Because auto_increment values are sequential, InnoDB stores rows consecutively, leading to high page fill factor, minimal page splits, and fast locating of new rows.

2.2 UUID index structure

UUIDs are random; new rows may need to be inserted anywhere in the clustered index, causing random I/O, frequent page splits, and fragmentation.

2.3 Drawbacks of auto‑increment

Despite its advantages, auto_increment has some downsides:

Exposes business growth information because IDs are predictable.

High‑concurrency inserts can cause lock contention on the primary‑key hotspot.

Auto_increment lock mechanism introduces performance overhead; tuning innodb_autoinc_lock_mode can mitigate this.

Conclusion

The benchmark demonstrates that auto_increment primary keys consistently outperform UUID and random long keys for large‑scale inserts in MySQL. Following MySQL's official recommendation to use sequential primary keys yields better insert speed, lower fragmentation, and more efficient index usage, though specific scenarios may still require careful tuning.