Understanding InnoDB Index Types: Clustered Index, Secondary Index, and Optimization Techniques

Preface

Hello everyone, I am A Xing.

In the previous article InnoDB Principles: Why Using Indexes Speeds Up Queries? we discussed why indexes are fast.

Now we will talk about how to use indexes effectively.

InnoDB Index Classification

We know that the InnoDB index structure is organized as a B+ tree, and based on the data storage form it can be divided into two categories: clustered index and secondary index .

ps: Some people also call them non‑clustered or auxiliary indexes, but they all refer to the same concept; in this article we uniformly call them secondary indexes .

Clustered Index

Clustered index is by default built on the primary key . If no primary key is defined, InnoDB will choose a non‑null unique index as a substitute; if none exists, InnoDB will implicitly create a hidden primary key to serve as the clustered index.

In essence, a clustered index is the primary‑key index.

Because each table can have only one primary‑key column, each table can have only one clustered index.

Another characteristic of a clustered index is that the table data and the primary key are stored together; its leaf nodes contain the entire row data (the last level of the tree), also called data pages .

Simple rule: If you find the index, you find the row data, so this index is a clustered index.

If this is unclear, you can refer to A Xing's previous two articles.

InnoDB Principles: How Data Pages Become Indexes

InnoDB Principles: Why Using Indexes Speeds Up Queries?

Secondary Index

Having understood the clustered index, let’s look at secondary indexes . In short, any index other than the primary‑key index is a secondary index, such as composite indexes, prefix indexes, unique indexes, etc.

The leaf nodes of a secondary index store the index value + primary key (e.g., id ).

The difference between secondary and clustered indexes lies in whether the leaf nodes store the full row record.

Therefore, a secondary index alone cannot retrieve the complete row; it can only obtain the id information.

How can a secondary index retrieve the full row data?

Index Query

Assume we have a table with a primary‑key column id and a column k that has an index. The CREATE TABLE statement is:

create table T(
id int primary key,
k int not null,
name varchar(16),
index(k)
)engine=InnoDB;

The table contains 5 rows: (id,k) values (100,1), (200,2), (300,3), (500,5), (600,6). At this point there are two trees: the primary‑key id clustered index and the secondary index on k .

Back‑to‑Table (回表)

When we execute a primary‑key query select * from T where id = 100 , only the clustered index on id is searched and the whole row is returned.

For a secondary‑index query select * from T where k = 1 , the process is:

Search the k secondary‑index tree, find id = 100 .

Then look up the clustered index tree for id = 100 to get the full row.

Continue to the next k value (e.g., k = 2 ) and stop when the condition is not met.

This additional lookup of the clustered index is called back‑to‑table (回表). It means a secondary‑index query needs an extra scan of the clustered index, so using the primary key directly is more efficient.

Covering Index

Sometimes we need to use a secondary index but want to avoid back‑to‑table. If the query only needs the indexed columns, the index can “cover” the query. For example:

select id from T where k = 1;

Here the k index already contains the id value, so the query is satisfied by the index alone—this is a covering index . Covering indexes reduce the number of tree traversals and significantly improve query performance.

In high‑frequency scenarios where we need both k and name , we can create a composite index on (k, name) , which allows the query to be fully covered and eliminates back‑to‑table.

Index Pushdown

If we also create a composite index on (name, k) and run:

select k from T where name like '张%' and k = 2;

MySQL 5.6 introduced index pushdown , which evaluates additional predicates (e.g., k = 2 ) while scanning the index, filtering out non‑matching rows early and reducing the number of back‑to‑table operations. In this example the number of back‑to‑table lookups drops from 6 to 0.

Conclusion

This article covered clustered indexes, secondary indexes, back‑to‑table, covering indexes, and index pushdown. When designing databases, minimizing resource access while satisfying query requirements is a key principle. Future articles will explore deeper index design strategies.

Why does memory allocation fail despite abundant RAM?

plotnine: ggplot2 for Python

Dynamic Programming: Solve Maximum Subarray in 8 Lines

Understanding Regex with AST Instead of Docs

This Game Engine Is Open‑Source!