Understanding MySQL Indexes: Types, Structures, and Usage

Indexes are auxiliary data structures that enable storage engines to retrieve rows efficiently, essentially acting as a directory for data.

What Is an Index?

An index improves query performance by allowing the engine to locate rows quickly, similar to a book's table of contents.

Index Classification

Indexes can be categorized from several perspectives:

Data‑structure perspective: B+Tree, Hash, Full‑text.

Physical storage perspective: clustered index, secondary (auxiliary) index.

Field‑characteristic perspective: primary key index, unique index, normal index, prefix index.

Column‑count perspective: single‑column index, composite (multi‑column) index.

Data‑Structure Perspective

MySQL storage engines (InnoDB, MyISAM, Memory) support different index types; B+Tree is the most widely used.

Compared to B‑Tree, B+Tree stores data only in leaf nodes and links leaves with a singly linked list, reducing node count and I/O.

Compared to red‑black trees, B+Tree has a higher branching factor (degree > 100), resulting in lower tree height and fewer disk accesses.

Hash indexes excel at equality searches but cannot handle range queries, making B+Tree more versatile.

Physical‑Storage Perspective

InnoDB uses a clustered index (the primary key) where leaf nodes contain full row data; secondary indexes store only the primary key values.

create table workers (
  id int(11) not null auto_increment comment '员工工号',
  name varchar(16) not null comment '员工名字',
  sales int(11) default null comment '员工销售业绩',
  primary key (id)
) engine=InnoDB AUTO_INCREMENT=10 default charset=utf8;

Adding a secondary index on name: alter table workers add index index_name(name); Querying name='吕归尘' uses the secondary index to find the primary key, then performs a back‑table lookup to retrieve the full row.

If the query only requests columns present in the secondary index (e.g., select id, name ...), the index covers the query and avoids the back‑table lookup.

MySQL's Index Condition Pushdown (ICP) can further filter rows at the storage engine level, improving performance.

MyISAM Indexes

MyISAM does not have clustered indexes; both primary and secondary indexes store pointers to row data, allowing tables without a primary key.

Field‑Characteristic Perspective

Primary key index: built on the primary key column, unique, not null.

Unique index: built on a UNIQUE column, allows multiple nulls.

Normal index: built on ordinary columns.

Prefix index: built on the leading characters/bytes of a string or binary column.

create table persons (
  id int(11) not null auto_increment comment '主键id',
  eno int(11) comment '工号',
  eid int(11) comment '身份证号',
  veid int(11) comment '虚拟身份证号',
  name varchar(16) comment '名字',
  primary key (id) comment '主键索引',
  unique key (eno) comment 'eno唯一索引',
  unique key (eid) comment 'eid唯一索引'
) engine=InnoDB auto_increment=1000 default charset=utf8;
alter table persons add unique index index_veid (veid) comment 'veid唯一索引';

Column‑Count Perspective

Single‑column indexes target one column; composite indexes (e.g., on id and name) store combined keys and are useful for multi‑column predicates.

create index index_id_name on workers(id, name) comment '组合索引';

Composite index leaf nodes store the combined key values, and non‑leaf nodes order first by the first column, then by the second.

Overall, understanding the types, structures, and usage patterns of MySQL indexes helps design efficient schemas and write performant queries.