Understanding MySQL Indexes: Principles, Types, and Performance Optimization

Introduction: The article introduces MySQL indexes, comparing them to a library catalog, and explains how indexes help the MySQL engine quickly locate rows without scanning the entire table.

Index basics: An index (called "Index" in English) is a data structure; MySQL uses a B+‑tree (a reversed tree with the root at the top) to store index entries, enabling fast data retrieval.

Types of indexes: MySQL supports ordinary indexes, unique indexes, full‑text indexes, single‑column indexes, multi‑column (composite) indexes, spatial indexes, primary (clustered) indexes, and secondary (non‑clustered) indexes. The article also distinguishes clustering vs. non‑clustering and single‑ vs. multi‑column indexes.

Performance demonstration: Using a student table with 3 million rows, the author shows query times without an index (≈0.47 s, full table scan) and with a simple index on stu_no (≈0.0013 s). Additional tests illustrate the impact of primary key indexes, ordinary indexes, and composite indexes (e.g., age + classId) reducing query time from 0.46 s to 0.014 s, a 30× speedup.

Creating indexes: The article provides SQL syntax for creating indexes in three ways, with examples wrapped in ... blocks:

CREATE TABLE table_name ( ... , INDEX idx_name (col1, col2) );

CREATE INDEX idx_name ON table_name (col1, col2);

ALTER TABLE table_name ADD INDEX idx_name (col1, col2);

Specific examples include adding an index on stu_no:

ALTER TABLE `test`.`student` ADD INDEX `index_stu_no` (`stu_no` ASC) VISIBLE;

and creating a composite index on age and classId:

CREATE INDEX index_age_class_id ON test.student (age, classId);

Conclusion: Indexes significantly improve query performance but incur storage overhead and maintenance cost; choosing the right index type and columns is essential for optimal database efficiency.