How Indexes Speed Up Database Queries: A Clear Explanation

Computer storage fundamentals

Data persisted by a database resides on non‑volatile storage such as hard‑disk drives (HDD). An HDD requires mechanical actions—seek to the correct track, rotate the platter to the target sector, and read the sector—introducing latency. Operating systems therefore cache data in RAM, which has orders‑of‑magnitude lower read/write latency, before applications access it.

How a database index works

An index functions like a book’s table of contents: it lets the database locate rows without scanning the entire table. Consider a table with 100,000 fixed‑length rows (204 bytes each) stored in 1,024‑byte blocks. Each block holds 5 rows, so the table occupies 20,000 blocks.

Scanning all blocks requires 20,000 I/O operations. By sorting the rows on the indexed column, the database can apply binary search on the index structure (typically a B‑tree). Binary search needs only log₂(20,000) ≈ 14.3 steps, reducing the number of block reads by roughly 800 ×.

Why indexes accelerate queries

Because the index stores keys in sorted order, lookup time drops from linear O(N) to logarithmic O(log N). Primary‑key columns are unique and frequently used for point lookups, making them ideal index candidates.

Costs of excessive indexing

Each INSERT or UPDATE must modify both the data row and every associated index entry, doubling write work for indexed columns. When an index grows to the size of the table, the overhead of maintaining the index can outweigh its read‑time benefit.

Write operations become slower.

Indexes consume additional disk space.

Applying functions, implicit type conversions, OR conditions, or leading wildcards in LIKE can render an index unusable, forcing a full‑table scan.

Clustered vs. non‑clustered indexes

A clustered index stores rows physically in the order of the indexed key; its leaf nodes contain the actual data rows. Only one clustered index can exist per table. A non‑clustered index stores only the indexed columns and a pointer to the data row; its leaf nodes contain index entries, not the rows themselves.

Primary keys are commonly implemented as clustered indexes because they guarantee uniqueness and provide the most efficient single‑row lookup.

When to choose a clustered index

Suitable columns have many distinct values, are used in range predicates (BETWEEN, >, <), appear frequently in JOIN, GROUP BY, or ORDER BY clauses, or serve as foreign keys. Columns that are updated often should avoid clustered indexes because row movement is costly.

Common SQL optimization techniques

Avoid full‑table scans by ensuring predicates reference indexed columns.

Prevent index loss by not applying functions or implicit conversions to indexed columns.

Use covering indexes so that the query can be satisfied from the index alone.

In MySQL, the operators != (or <>), IS NULL / IS NOT NULL, and leading wildcards in LIKE ('%abc') prevent index usage.

Minimize unnecessary sorting, selection of extra fields, and creation of temporary tables.

Code example

·················END·················
资料链接
清华学姐自学的Linux笔记，天花板级别！
新版鸟哥Linux私房菜资料
阿里大佬总结的《图解Java》火了，完整版PDF开放下载！
Alibaba官方上线！SpringBoot+SpringCloud全彩指南
国内最强的SpringBoot+Vue全栈项目天花板，不接受反驳！