Choosing the Right Primary Key for InnoDB: An Architecture Note

Requirements

Every InnoDB table must have a clustered index. If you do not declare a PRIMARY KEY or a UNIQUE NOT NULL index, InnoDB silently creates a hidden 6‑byte ROW_ID. The table then behaves like a heap organized around a synthetic key, which prevents deterministic replication and blocks online DDL.

Smallest Suitable Design

Define an explicit PRIMARY KEY on a monotonically increasing column — typically BIGINT AUTO_INCREMENT or a time‑ordered UUID (UUIDv7). This keeps inserts append‑only, minimizes page splits, and makes secondary indexes compact because they store the PK value as the row pointer.

Trust and Data Boundaries

The PK value is embedded in every secondary index entry. Changing a PK forces a rewrite of all secondary indexes, so treat the PK as immutable after insert. Expose only surrogate keys to applications; never use business keys that may change (e.g., email, username) as the clustered index.

Operational Checks

• Monitor Innodb_buffer_pool_pages_dirty flush rate and Innodb_row_lock_waits via SHOW GLOBAL STATUS.
• Run SHOW ENGINE INNODB STATUS and count “index page split” occurrences.
• Query INFORMATION_SCHEMA.INNODB_TABLESPACES for tablespace fragmentation metrics.

Failure Modes

A random PK such as UUIDv4 causes 10‑100× more page splits and buffer‑pool churn. Missing PK forces the hidden ROW_ID, which breaks GTID‑based replication (the replica cannot deterministically apply rows) and prevents ALGORITHM=INSTANT DDL. Write‑heavy workloads on tables > 1 TB amplify these effects.

Conditions That Change the Design

• Sharding key differs from PK – use a composite PK with the shard identifier as the leading column.
• Heavy range scans on non‑PK columns – add a covering secondary index that includes the scanned columns.
• Write‑heavy workloads on very large tables – consider native partitioning by PK range to isolate hot partitions.

Practical Example: Sequential vs Random PK

Create two identical tables, one with BIGINT AUTO_INCREMENT PK and one with CHAR(36) UUIDv4 PK. Benchmark with sysbench (run on a client host with network access to the MySQL server, requires INSERT privilege on the test schema):

# sequential PK
sysbench oltp_insert --tables=1 --table-size=1000000 \\\\\\n  --mysql-host=127.0.0.1 --mysql-user=bench --mysql-password=**** \\\\\\n  --mysql-db=test --db-driver=mysql run

# random PK (UUIDv4 generated in application)
sysbench oltp_insert --tables=1 --table-size=1000000 \\\\\\n  --mysql-host=127.0.0.1 --mysql-user=bench --mysql-password=**** \\\\\\n  --mysql-db=test --db-driver=mysql run

Expected observation: the sequential‑PK run shows far fewer “index page split” messages in SHOW ENGINE INNODB STATUS and lower Innodb_buffer_pool_pages_dirty flush spikes.

Verification Steps

1. Confirm an explicit PK exists: SHOW CREATE TABLE `db`.`tbl`; (run in any MySQL client with SELECT privilege).
2. Check the internal flag: SELECT NAME, FLAG FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='db/tbl'; – bit 1 set means explicit PK.
3. Validate query plans: EXPLAIN ANALYZE SELECT * FROM `db`.`tbl` WHERE pk_col = 123; should show a clustered index scan.
4. Correlate replication lag: monitor Seconds_Behind_Master during peak writes; a random PK typically spikes lag.

Limitations & Rollback

Adding a PK to an existing table (ALTER TABLE ... ADD PRIMARY KEY) rebuilds the entire table and all secondary indexes. Plan a maintenance window or use pt-online-schema-change / gh-ost for zero‑downtime migration. MySQL 8.0.13+ supports instant column addition but not instant PK addition. If you must revert, restore from a logical backup taken before the ALTER.