Design Principles and Practices for MySQL Primary Keys

In MySQL, a primary key uniquely identifies each row in a table and, for InnoDB tables, serves as both data and index. Good primary key design should minimize space usage, have a natural ordering, and preferably use an integer type such as INT UNSIGNED.

Design Principles

Keep the key size small so more keys fit in each index page, reducing memory usage.

Prefer keys with a sortable attribute (e.g., sequential INT) to improve insert performance.

Use integer types for the key to benefit from compact storage and fast comparisons.

Business‑Unrelated Primary Keys

MySQL’s recommended approach is an auto‑increment column, typically INT (or BIGINT for very large tables). When sharding is anticipated, use INT64 with @@auto_increment_increment and @@auto_increment_offset to generate non‑conflicting values across nodes.

mysql> set @@auto_increment_increment=2;</code>
<code>Query OK, 0 rows affected (0.00 sec)</code>
<code>mysql> set @@auto_increment_offset=1;</code>
<code>Query OK, 0 rows affected (0.00 sec)</code>
<code>mysql> insert into tmp values (null),(null),(null);</code>
<code>Query OK, 3 rows affected (0.01 sec)</code>
<code>+----+</code>
<code>| id |</code>
<code>+----+</code>
<code>| 1  |</code>
<code>| 3  |</code>
<code>| 5  |</code>
<code>+----+

When merging data from multiple legacy systems, a new surrogate key can be introduced while preserving the original IDs in separate columns, as shown in the following table definitions:

create table n5(
  id int unsigned auto_increment primary key,
  old_id int,
  old_name varchar(64),
  unique key udx_old_id_old_name(old_id,old_name)
);

UUID as Primary Key

UUIDs guarantee uniqueness but are random, large (36 characters), and unsorted, which hurts InnoDB performance. MySQL provides uuid_to_bin() to store UUIDs as VARBINARY(16), and uuid_short() to generate ordered 64‑bit integers.

create table t_binary(
  id varbinary(16) primary key,
  r1 int,
  key idx_r1(r1)
);</code>
<code>insert into t_binary values (uuid_to_bin(uuid()),1),(uuid_to_bin(uuid()),2);</code>
<code>select * from t_binary;</code>
<code>+------------------------------------+------+</code>
<code>| id                                 | r1   |</code>
<code>+------------------------------------+------+</code>
<code>| 0x412234A77DEF11EA9AF9080027C52750 | 1    |</code>
<code>| 0x412236E27DEF11EA9AF9080027C52750 | 2    |</code>
<code>+------------------------------------+------+

Using uuid_short() yields ordered 64‑bit IDs that avoid the mutex overhead of auto‑increment locks, making them a fast alternative when the generation rate stays below 16,777,216 per second.

create table t_uuid_short(
  id bigint unsigned primary key,
  r1 int,
  key idx_r1(r1)
);</code>
<code>insert into t_uuid_short values (uuid_short(),1),(uuid_short(),2);</code>
<code>select * from t_uuid_short;</code>
<code>+-----------------------+------+</code>
<code>| id                    | r1   |</code>
<code>+-----------------------+------+</code>
<code>| 16743984358464946177  | 1    |</code>
<code>| 16743984358464946178  | 2    |</code>
<code>+-----------------------+------+

Performance Test

A stored procedure inserts 300,000 rows into four tables ( t_uuid, t_binary, t_id, t_uuid_short). The observed write times match the expectation: t_uuid (slowest), t_binary, t_id, and t_uuid_short (fastest).

call sp_insert_data('t_uuid',300000);      -- ~5 min 23 sec</code>
<code>call sp_insert_data('t_binary',300000);   -- ~4 min 49 sec</code>
<code>call sp_insert_data('t_id',300000);       -- ~3 min 40 sec</code>
<code>call sp_insert_data('t_uuid_short',300000);-- ~3 min 10 sec

Business‑Related Primary Keys

Using meaningful business fields (e.g., student numbers, order codes) as primary keys is discouraged because changes require updating the clustered index and all dependent tables. Instead, keep a surrogate key (auto‑increment or uuid_short()) as the primary key and create unique indexes on business columns.

create table n5(
  id int unsigned auto_increment primary key,
  userno int unsigned,
  unique key udx_userno(userno)
);

The article concludes with a reminder that primary key design is crucial for performance and maintainability, and invites readers to ask further questions.