This section reveals one of the most misunderstood and expensive InnoDB behaviors. Choosing the wrong primary key (especially random or non-sequential values) turns every insert into a costly page split, leading to index fragmentation, random I/O spikes, and dramatically slower performance once your table grows beyond a few million rows.

7.1 Understanding Primary Key Options: AUTO_INCREMENT, UUID v4, and UUID v7

Before we dive into page splitting, let’s clearly understand the three most common choices for a primary key.

1. AUTO_INCREMENT This is MySQL’s built-in way of generating sequential numbers automatically.

• When you insert a new row and don’t specify the id, MySQL gives it the next number (1, 2, 3, 4…).

• It is strictly sequential and always increasing.

• Example:

  CREATE TABLE sensor_logs (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  sensor_id   INT,
  reading     FLOAT,
  recorded_at DATETIME
  ) ENGINE=InnoDB;
  

Most traditional applications use this because it is simple, narrow (only 8 bytes), and produces perfectly ordered values.

2. UUID v4 UUID stands for Universally Unique Identifier — a 128-bit (36-character) value that is guaranteed to be unique across the entire world.

• v4 is the most common version.
• It is generated completely randomly (except for a few fixed bits).
• Example of a UUID v4: f47ac10b-58cc-4372-a567-0e02b2c3d479
• Every new value is totally unrelated to the previous one — there is no ordering at all.

Developers love UUID v4 because it works great in distributed systems (no need to coordinate ID generation across servers). However, its randomness is exactly what causes problems in InnoDB.

3. UUID v7 (The Modern Alternative) UUID v7 is a newer standard (defined in RFC 9562) that most developers have not yet discovered.

• It is time-based: The first 48 bits are a timestamp (milliseconds since 1970).
• The remaining bits are random.
• Because the timestamp part is always increasing, new UUID v7 values are mostly sequential (they generally go forward in time).
• Example of a UUID v7: 018f2a5e-8c3b-7c90-9b12-345678901234 (You can see the timestamp part makes newer IDs larger than older ones.)
• Clear visual breakdown of why UUID v7 is mostly sequential.