Why InnoDB Stores Primary Keys in Secondary Index Leaves – A Deep Dive

Understanding InnoDB Index Types

InnoDB maintains two main index categories: a clustered index (the primary key) whose leaf nodes store full row records, and secondary (non‑clustered) indexes whose leaf nodes store only the primary key values.

Clustered Index (Primary Key)

If a table defines a PRIMARY KEY, that column becomes the clustered index.

If no PRIMARY KEY exists, the first NOT NULL UNIQUE column is chosen.

Otherwise InnoDB creates a hidden ROWID as the clustered index.

This design makes PK‑based queries extremely fast because the index directly points to the row data.

Secondary Index (Non‑Clustered Index)

Secondary indexes store the primary key value in their leaf nodes. For InnoDB this reduces maintenance overhead when rows move or pages split, because only the clustered index needs updating.

When a query uses a secondary index, MySQL must first locate the PK value from the secondary index, then perform a second lookup on the clustered index to fetch the full row – a process called “row‑lookup” or 回表 (table‑lookup). This double‑scan is less efficient than a single clustered‑index scan.

Practical Example

1. Create Table

mysql> create table user(</code>
<code>    id int(10) auto_increment,</code>
<code>    name varchar(30),</code>
<code>    sex tinyint(4),</code>
<code>    type varchar(8),</code>
<code>    primary key (id),</code>
<code>    index idx_name (name)</code>
<code>) engine=innodb charset=utf8mb4;

id is the clustered index; name is a secondary index.

2. Insert Sample Data

mysql> select * from user;</code>
<code>+----+------+-----+------+</code>
<code>| id | name | sex | type |</code>
<code>+----+------+-----+------+</code>
<code>| 1  | sj   | m   | A    |</code>
<code>| 3  | zs   | m   | A    |</code>
<code>| 5  | ls   | m   | A    |</code>
<code>| 9  | ww   | f   | B    |</code>
<code>+----+------+-----+------+

3. Index Structures

Clustered Index – leaf nodes store the full row data.

Secondary Index – leaf nodes store the primary key value.

4. Lookup Process

If the query condition uses the primary key, only one B+‑tree scan is needed: select * from user where id = 5; If the condition uses a secondary index (e.g., name = 'lisi'), MySQL first scans the secondary index to obtain the PK value, then scans the clustered index to retrieve the row – two scans:

select * from user where name = 'lisi';

This double‑scan is the “row‑lookup” (回表) operation, which is slower than a single clustered‑index scan.

Covering Indexes

A covering index contains all columns required by the query, allowing MySQL to satisfy the query using only the secondary index without a row‑lookup.

Creating a Covering Index

Define a secondary index that includes the needed columns (single‑column or composite):

explain select id, name from user where name = 'lisi';

Because name is indexed, the query can be answered with a single B+‑tree scan, achieving a covering index.

Key Takeaways

InnoDB’s secondary index leaf nodes store the primary key to simplify maintenance.

Queries that rely on secondary indexes trigger a row‑lookup (回表), requiring two index scans.

Covering indexes eliminate the row‑lookup by storing all needed columns in the secondary index.