Understanding MySQL Index Types, Structures, and Best Practices

MySQL indexes are essential for fast data retrieval, and their design must consider large, dynamically changing datasets. The article begins by outlining the goals of indexing: rapid lookup using appropriate search algorithms.

1. Common Index Types (Implementation Layer)

1.1 B‑Tree (B+Tree) Index

MySQL uses B+Tree structures for most indexes. A B+Tree stores all index values in leaf nodes, which are linked for range queries. Example table creation:

create table User(
  `name` varchar(50) not null,
  `uid` int(4) not null,
  `gender` int(2) not null,
  key(`uid`)
);

When inserting rows with uid values, the storage engine builds an index tree (illustrated in the article). Leaf nodes contain ordered index values; non‑leaf nodes store pointers to locate the correct leaf.

For multi‑column (composite) indexes, the tree orders first by the leftmost column, then by subsequent columns, following the left‑most matching principle.

1.2 Hash Index

Hash indexes use a hash table and only support exact matches on all indexed columns. Example:

create table User(
  `name` varchar(50) not null,
  `uid` int(4) not null,
  `gender` int(2) not null,
  key(`uid`,`name`)
);

Query execution involves computing the hash of the search value, locating the corresponding bucket, and verifying the row.

2. Index Types (Application Layer)

Primary Key Index

create table User(
  `name` varchar(50) not null,
  `uid` int(4) not null,
  `gender` int(2) not null,
  primary key(`uid`)
);

Unique, usually the table's ID, and only one per table.

Unique Index

create table User(
  `name` varchar(50) not null,
  `uid` int(4) not null,
  `gender` int(2) not null,
  unique key(`name`)
);

Enforces business‑level uniqueness; a table can have multiple unique indexes.

Single‑Column Index

create table User(
  `name` varchar(50) not null,
  `uid` int(4) not null,
  `gender` int(2) not null,
  key(`name`)
);

Indexes a single column.

Composite (Multi‑Column) Index

create table User(
  `name` varchar(50) not null,
  `uid` int(4) not null,
  `gender` int(2) not null,
  key(`name`,`uid`)
);

Indexes two or more columns; queries must satisfy the left‑most column rule.

3. Clustered vs. Non‑Clustered Indexes

Clustered indexes store the row data together with the index key in leaf nodes (typically the primary key). Non‑clustered (secondary) indexes store only the index key and a pointer to the primary key.

In InnoDB, the primary key becomes the clustered index; if no primary key exists, a unique non‑null index is chosen, otherwise an implicit key is generated.

4. Covering Index

A covering index contains all columns required by a query, eliminating the need to read the table rows. Example:

select name,uid from User where name in ('a','b') and uid >= 98 and uid <= 100;

Because the query only needs name and uid, the composite index on those columns satisfies the request directly, improving performance.

5. Best Index Usage Strategies

Ensure indexed columns are not expressions or functions.

Follow the left‑most matching principle for composite indexes.

Avoid NULL values in indexed columns, as they can render the index ineffective.

Prefer clustered and covering indexes to reduce back‑table lookups.

Use short prefixes for long string columns to save space.

By applying these guidelines, developers can significantly improve MySQL query performance.