Master MySQL Indexes: B‑Tree, B+Tree, MyISAM vs InnoDB, and Interview Tips

1. Interview Questions

Explain the principle and data structure of MySQL indexes.

Difference between B‑Tree and B+Tree.

Difference between clustered and non‑clustered indexes.

How are they stored?

Principles for using MySQL indexes.

How to use composite indexes.

2. Interviewer Psychology

Database questions are a must‑have for engineers with less than 30k experience; MySQL dominates, while older Oracle skills are less valued and big‑data technologies like Hadoop or HBase are now pluses.

3. Index Analysis

3.1 Data Structure of Indexes

MySQL indexes are implemented with B‑Tree structures; the answer is usually a B+Tree, not a binary tree. The article lists the formal B‑Tree rules (degree d, height h, node composition, leaf constraints, ordering, pointers, etc.).

Example table:

CREATE TABLE t (
  id INT,
  name VARCHAR(255),
  age INT
);

Index on id with values 15,56,77,20,49 leads to queries like SELECT * FROM t WHERE id = 49 or SELECT * FROM t WHERE id = 15.

Illustration of a B‑Tree:

3.2 Storage Engine Index Implementation

MyISAM : Index file stores pointers to data file rows (e.g., 0x07). The data file is separate; lookup follows index → data file → row.

Illustration of MyISAM index file:

InnoDB : The data file itself is the clustered index keyed by the primary key; leaf nodes contain the full row. Non‑primary indexes store the primary key as the pointer, requiring a second lookup (back‑lookup).

Illustration of InnoDB index structure:

Using auto‑increment primary keys is recommended to keep the clustered index ordered and avoid costly B+Tree splits.

3.3 Index Usage Rules

Use left‑most prefix matching for composite indexes.

Full column match uses the index; partial prefix also works.

If the leftmost column is missing, the index cannot be used.

Range queries must follow the leftmost prefix.

Functions on indexed columns prevent index usage.

3.4 Drawbacks and Best Practices

Indexes increase disk usage and can hurt write performance under high concurrency. Create only a few selective indexes; avoid indexing low‑selectivity columns (e.g., a status column with only two values). Consider prefix indexes for long strings.

Overall, mastering MySQL indexes—B+Tree structure, storage‑engine differences, and proper query patterns—covers most interview requirements and real‑world performance tuning.