Understanding MySQL Indexes in InnoDB: Structures, Types, and Optimization Techniques

The article follows a previous discussion on MySQL query execution and now focuses on MySQL indexes, especially the structures and usage of indexes in the InnoDB storage engine.

An index is a sorted data structure in a DBMS that speeds up data lookup and updates, similar to a dictionary's table of contents.

Indexes can be classified from three perspectives: data‑structure (e.g., B+Tree, Hash, R‑Tree), storage level (clustered vs. non‑clustered), and logical level (primary, ordinary, composite, unique, spatial).

In InnoDB, tables are stored as clustered indexes based on the primary key, meaning the full row data resides in the leaf pages of the primary‑key index. The article creates a sample table to illustrate this:

create table person(
  id int primary key,
  age int not null,
  index (age) engine=InnoDB;
)

After inserting five rows, the clustered index stores rows sorted by id , while a secondary index on age stores the age values sorted and points to the corresponding primary‑key rows.

When querying by primary key (e.g., select * from person where id = 6 ), MySQL uses the clustered index and retrieves the row in a single step. Querying by a non‑primary index (e.g., select * from person where age = 18 ) first finds the matching age entries, then performs a second lookup on the primary key to fetch the full row—a process called “back‑table” (回表).

To avoid back‑table lookups, a covering index can be created so that all columns needed by the query are stored in the index itself. The article shows a composite index on name and age :

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

With this index, the query select name,age from person where name = "Barry" can be satisfied entirely from the index without a back‑table step.

The leftmost‑prefix rule allows using the leftmost N columns of a composite index. For example, select name from person where name like 'A%' can use the (name,age) index because the leftmost column name matches the condition.

MySQL 5.6 introduced Index Condition Pushdown (ICP), which evaluates additional predicates inside the index scan to filter rows earlier. In the query select * from person where name like 'A%' and age = 19 , ICP lets InnoDB check the age = 19 condition while scanning the name index, reducing the number of back‑table operations.

Advantages of indexes include reduced data scanning, avoidance of sorting and temporary tables, and conversion of random I/O to sequential I/O. Disadvantages are extra storage consumption and the overhead of maintaining indexes during inserts and updates.

In summary, the article covers the definition and classification of indexes, explains InnoDB’s clustered and secondary index structures, describes how primary and secondary indexes differ, shows how covering indexes and the leftmost‑prefix rule can improve performance, and introduces index condition pushdown as a further optimization.