Why Databases Skip the First Page: How Indexes Speed Up Queries

After outlining an e‑commerce request flow that ultimately lands in a database, the author chooses to study the most common exam topic: indexes.

Without an index, the database must perform a full‑table scan, checking each row sequentially (e.g., searching for id = 10086 by examining row 1, row 2, …). This is tolerable for small tables but extremely slow for millions of rows.

Indexes accelerate queries by reducing the number of scanned rows and the amount of disk I/O, which is critical because databases store most data on disk and disk reads are far slower than memory accesses.

MySQL InnoDB implements indexes with a B+Tree. Three key characteristics make it suitable: (1) it is a multi‑way tree, so many keys fit in one node and the tree stays shallow, minimizing I/O; (2) non‑leaf nodes store only keys, allowing more keys per page; (3) leaf nodes are linked in a list, enabling fast range scans such as finding all rows with id BETWEEN 100 AND 200.

The leaf‑node linked list is likened to a book whose pages are already ordered, so once you locate page 100 you can flip forward without consulting the table of contents again.

In a clustered index, the leaf nodes contain the full row data. In InnoDB the primary‑key index is usually clustered. For example, SELECT * FROM user WHERE id = 10086; searches the primary B+Tree, lands on a leaf that holds the entire row, and returns it directly. Only one clustered index can exist per table because the whole table can be stored in only one physical order.

A non‑clustered (secondary) index stores only the primary‑key values in its leaf nodes. For a query like SELECT * FROM user WHERE name = '张三';, the engine first looks up the name index, obtains the corresponding id, and then uses that id to fetch the full row from the clustered index – a process called “回表” (table‑lookup). This is analogous to a directory that tells you which page to turn to.

“回表” therefore involves two tree traversals and is slightly more expensive than a direct primary‑key lookup.

A covering index is the opposite of a “回表”. When all columns required by a query are present in the index, the engine can satisfy the query using only the index. For instance, a composite index on (name, age) allows SELECT name, age FROM user WHERE name = '张三'; to be answered without accessing the table.

Ordinary indexes allow duplicate values; unique indexes forbid them. A table may have many ordinary indexes but only one primary key (which is typically clustered). Over‑indexing has costs: additional storage, slower inserts/updates/deletes because each write must maintain every index, and poorly chosen indexes may never be used.

The article provides a sample exam answer: the ID query is fast because the primary key is clustered; the name query is slow because no index exists, and adding a suitable secondary or covering index would reduce scans and I/O. However, one should balance index count against write performance.

Finally, a self‑test lists seven questions covering full‑table scans, B+Tree suitability, clustered vs. non‑clustered leaf contents, the meaning of “回表”, covering indexes, and why more indexes are not always better.

The key takeaway is that indexes are not magic; they are a directory structure that minimizes page flips and disk reads, making database queries fast.