Understanding MySQL Indexes: Types, Structures, and Optimization Techniques

This article explains the fundamentals and advanced concepts of MySQL indexes, focusing on the InnoDB storage engine.

Index Classification

Indexes are classified by data structure (B+Tree, Hash, …), physical storage (clustered, non‑clustered), characteristics (primary, unique, normal, full‑text, …), and column count (single‑column, composite).

Index Data Structures

Hash Index

Hash indexes are rarely used in InnoDB because the engine only supports adaptive hash indexes. When a hash index is defined on a column, MySQL still stores it as a B+Tree, which can be observed with SHOW INDEX FROM table_name .

B+Tree Index

B+Tree is the most common index structure in MySQL. It stores keys in sorted order and forms the basis of both clustered and secondary indexes.

Clustered Index (Primary Key)

Data rows are stored in the leaf nodes of a B+Tree keyed by the primary key. Pages of 16KB hold rows; each page has a page directory that records the maximum ID of each group to enable fast binary search within a page and across pages.

Secondary (Non‑Clustered) Index

Each secondary index is a separate B+Tree. Leaf nodes store the indexed column values together with the primary key ID, while non‑leaf nodes store the indexed value, primary key, and a pointer to the data page.

Single‑Column Index Example

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Composite Index Example

A composite index on name and age sorts first by name and then by age , enabling efficient range queries that follow this order.

Covering Index

If a query requests only columns that exist in the index (e.g., SELECT id FROM user WHERE name='赵六' ), MySQL can satisfy the query directly from the index without a table lookup, dramatically reducing execution time.

Index Push‑Down

From MySQL 5.6 onward, conditions on columns that are present in a secondary index can be evaluated directly within the index (e.g., age > 22 ), reducing the number of row lookups (back‑table operations) to a minimum.

Index Merge

MySQL can combine multiple indexes for a single query. It supports intersect (AND), union (OR), and sort‑union strategies, provided the individual index scans return ordered primary‑key lists.

Cost‑Based Index Selection

MySQL estimates the I/O cost (1.0 per page read) and CPU cost (0.2 per row evaluation) for full table scans, secondary‑index scans, and back‑table lookups. It chooses the plan with the lowest total cost.

Common Index Pitfalls

Violating the left‑most prefix rule (e.g., using LIKE '%abc' or skipping the first column of a composite index).

Applying functions or implicit type conversions to indexed columns, which prevents index usage.

Out‑of‑date statistics leading to poor cost estimates; running ANALYZE TABLE refreshes them.

Creating indexes on low‑cardinality columns (e.g., gender) that provide little selectivity.

Index Design Guidelines

Avoid excessive indexes per table; each index consumes disk space and adds maintenance overhead.

Index columns frequently used in WHERE , ORDER BY , or GROUP BY clauses.

Do not index columns that are updated very often, as this increases write cost.

Prefer high‑cardinality columns for indexing to improve selectivity.

Conclusion

The article summarizes MySQL index types, their internal storage mechanisms, optimization techniques such as covering indexes, index push‑down, and index merge, and provides practical advice for effective index creation and maintenance.