Quantum Ink

Writing about the future as it unfolds.

Diagram of InnoDB Cluster architecture illustrating node crashes and automatic recovery processes.

How InnoDB Cluster Handles Node Crashes and Recoveries

,

InnoDB stores data very differently from a traditional heap table. Understanding clustered indexes and primary key design is one of the most effective ways to improve performance without changing hardware.

1. What is a clustered index in InnoDB?

InnoDB does not have a separate “table” and “index” structure for the primary key. The table is the primary key index. This is called a clustered index.

Conceptually:

┌───────────────────────────────┐
│ InnoDB clustered index (PK)  │
├───────────────┬──────────────┤
│ PK value      │ Row data     │
├───────────────┼──────────────┤
│ 1             │ <columns...> │
│ 2             │ <columns...> │
│ 3             │ <columns...> │
└───────────────┴──────────────┘

Rows are stored on disk ordered by the primary key. Secondary indexes then reference this clustered index.

2. How secondary indexes work

A secondary index in InnoDB does not point directly to a physical row location. Instead, each secondary index entry stores:

  • The secondary key columns.
  • The primary key value of that row.

So a secondary index lookup is a two-step process:

  1. Search the secondary index B-tree.
  2. Use the stored primary key to look up the row in the clustered index.

Diagram:

┌───────────────────────────────┐      ┌───────────────────────────────┐
│ Secondary index on email     │      │ Clustered index (PK id)       │
├───────────────┬──────────────┤      ├───────────────┬──────────────┤
│ email         │ PK(id)       │      │ id            │ Row data     │
├───────────────┼──────────────┤      ├───────────────┼──────────────┤
│ [email protected]       │ 10           │  ───▶│ 10            │ ...          │
│ [email protected]       │ 42           │  ───▶│ 42            │ ...          │
└───────────────┴──────────────┘      └───────────────┴──────────────┘

Because every secondary index stores the primary key, your PK choice directly affects:

  • Index size.
  • Buffer pool usage.
  • IO patterns.
  • Replication and backup volume.

3. What happens if you do not define a primary key?

If you omit a primary key (and no unique, non-null index exists), InnoDB silently creates a hidden 6-byte row ID and uses that as the clustered index.

This has several downsides:

  • You cannot use the hidden ID in queries.
  • Secondary indexes store this hidden value, increasing size.
  • Row ordering becomes an implementation detail, not a design choice.

Always define an explicit primary key for InnoDB tables.

4. Choosing a good primary key

4.1 Properties of a good InnoDB primary key

  • Stable: never (or almost never) updated.
  • Unique: enforced by the PK constraint.
  • Short: small data type to reduce index size.
  • Monotonically increasing: avoids random page splits.

A typical pattern:

CREATE TABLE users (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(255)    NOT NULL,
  created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY ux_users_email (email)
) ENGINE=InnoDB;

Here id is stable, short (8 bytes), and increasing. Secondary indexes reference id, which keeps them compact.

4.2 Why monotonically increasing keys matter

Because InnoDB stores rows ordered by the primary key, inserts follow the PK order. With an increasing PK (e.g. AUTO_INCREMENT), new rows are appended to the end of the B-tree most of the time.

This leads to:

  • Fewer page splits and less fragmentation.
  • Better insert throughput.
  • Hot pages confined mostly to the right edge of the index.

With random PKs (e.g. UUID v4), every insert can land in the middle of the tree, causing page splits, random IO, and more buffer churn.

5. Common primary key anti-patterns

5.1 UUID as primary key

Using a random UUID (CHAR(36) or BINARY(16)) as the primary key is a frequent source of performance problems.

Issues:

  • Large key size inflates all secondary indexes.
  • Random distribution causes heavy fragmentation.
  • Insert performance degrades as the table grows.

If you must use UUIDs for external references, a common pattern is:

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_uuid  BINARY(16)      NOT NULL,
  customer_id  BIGINT UNSIGNED NOT NULL,
  total_cents  BIGINT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ux_orders_public_uuid (public_uuid),
  KEY idx_orders_customer_id (customer_id)
) ENGINE=InnoDB;

This keeps the clustered index small and ordered while still providing a UUID for external use.

5.2 Wide composite primary keys

Designs like:

PRIMARY KEY (country_code, category, user_id)

may look attractive for query patterns but can be very wide. Every secondary index will store all three columns as the PK reference.

Instead, consider:

CREATE TABLE user_stats (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  country_code  CHAR(2)         NOT NULL,
  category      VARCHAR(32)     NOT NULL,
  user_id       BIGINT UNSIGNED NOT NULL,
  value         BIGINT          NOT NULL,
  PRIMARY KEY (id),
  KEY idx_stats_lookup (country_code, category, user_id)
) ENGINE=InnoDB;

This trades an extra lookup step (secondary index -> PK) for much smaller indexes overall. For most workloads this is a good trade-off.

5.3 Mutable primary keys

Updating a primary key value is expensive because:

  • The row must move to a new position in the clustered index.
  • All secondary indexes that store the PK need updating.

Avoid using business identifiers that may change (e.g. email, username) as primary keys. Use a surrogate key and keep business identifiers in separate, indexed columns.

6. How primary key choice affects common operations

6.1 Range scans

Range queries on the primary key are very efficient:

SELECT *
FROM   events
WHERE  id BETWEEN 100000 AND 101000;

Because rows are stored in PK order, this is a sequential scan over a contiguous part of the clustered index.

Range scans on secondary indexes are also efficient, but each step requires a lookup in the clustered index. If the PK is large, this increases IO and memory usage.

6.2 Replication and backups

Replication and logical backups (e.g. mysqldump) may order rows by primary key when scanning tables. A monotonically increasing, numeric PK tends to produce more predictable, sequential access patterns, which can improve throughput.

6.3 Deadlocks and locking

InnoDB uses index records to manage locks. Poor key design can increase the likelihood of lock contention. For example:

  • Random PK inserts spread activity across many pages, increasing concurrent lock sets.
  • Wide PKs mean larger lock structures in memory.

While deadlocks depend on query patterns, a simple, narrow, stable PK helps keep locking overhead lower.

7. Practical step-by-step design process

Step 1: Identify the row identity

  • Does a natural, never-changing identifier exist?
  • If not, plan a surrogate numeric ID (BIGINT UNSIGNED AUTO_INCREMENT is a safe default).

Step 2: Decide on the primary key

  • Prefer a single-column numeric PK.
  • Use AUTO_INCREMENT unless you have a strong reason not to.
  • Reserve composite PKs for genuine many-to-many link tables where both columns are short and stable.

Example link table where a composite PK is reasonable:

CREATE TABLE user_roles (
  user_id  BIGINT UNSIGNED NOT NULL,
  role_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  KEY idx_user_roles_role_id (role_id)
) ENGINE=InnoDB;

Step 3: Add supporting secondary indexes

For each important query, ensure there is an index that supports its predicates and ordering. Remember that each secondary index includes the PK, so:

  • Keep the PK small.
  • Avoid redundant columns in secondary indexes.

Step 4: Review existing tables

On a RHEL/Rocky Linux host, you can list InnoDB tables without primary keys:

mysql -u root -p -e '
SELECT t.table_schema, t.table_name
FROM   information_schema.tables t
WHERE  t.engine = "InnoDB"
  AND  t.table_type = "BASE TABLE"
  AND  NOT EXISTS (
         SELECT 1
         FROM   information_schema.statistics s
         WHERE  s.table_schema = t.table_schema
           AND  s.table_name   = t.table_name
           AND  s.index_name   = "PRIMARY"
       );'

For each table returned, plan a migration to add an explicit primary key. On large production tables, test the change in a staging environment and consider using online DDL features to minimise downtime.

8. Best practices summary

  • Always define an explicit primary key on InnoDB tables.
  • Prefer a single-column, numeric, AUTO_INCREMENT PK.
  • Keep the PK short and stable; do not update it.
  • Avoid random UUIDs as clustered PKs; use a surrogate key instead.
  • Use composite PKs sparingly, mainly for small link tables.
  • Design secondary indexes with the PK size in mind.

Conclusion

InnoDB’s clustered index design means your primary key choice shapes how every row and index is stored and accessed. By favouring small, stable, monotonically increasing primary keys and keeping complex business identifiers out of the clustered index, you can reduce IO, shrink indexes, and improve throughput with minimal application changes. Review existing schemas with these rules in mind, and you will often find straightforward optimisations that deliver significant performance gains.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

Smart reads for curious minds

We don’t spam! Read more in our privacy policy