数据库索引是什么？· 图解读本

What is an index? 索引是什么？

索引 indexindex a sorted list of values, each pointing at its row 按顺序排好的一份目录，记着每个值在第几行

An index is a pre-sorted copy stored next to the table. 索引是一份排好序的副本，单独存在表的旁边。

It's like…就像……

The index at the back of a book: sorted entries, each with a page number. You look up the entry, then turn straight to the page. 书后面的索引页：词条按拼音排好，旁边写着页码。先查到页码，再直接翻到那一页。

The pairing is one-to-one: an entry is a value, a page number is a row number. 对应关系是一对一的：词条就是值，页码就是行号。

Without an index, how does it search? 没有索引，数据库怎么找？

全表扫描 full table scanfull table scan read every row from the first to the last, skipping none 从第一行一页页翻到最后一行，一行也不跳

Without an index, every query is a full table scan. 没有索引，每次查询都是一次全表扫描。

One row per second: 20,000,000 rows ≈ 231 days — over seven months of turning pages. 一行一秒：2000 万行 ≈ 231 天，写成月就是七个多月。

With an index, how does it find the row? 有了索引，它怎么找？

It never flips the table itself — it flips the sorted copy. 数据库不翻原表，先翻这份排好序的副本。

Each check discards half: 25 checks cut 20,000,000 rows to one. At machine speed the full scan takes 4 seconds, the index 4 milliseconds — a thousand times faster. 每比一次就丢掉剩下的一半：25 次就从 2000 万行剩 1 行。换成机器的速度：全表扫描 4 秒，走索引 4 毫秒——快了一千倍。

1

Open the index, not the table 翻开索引，不翻原表

2

Halve, halve again — one entry left 一次丢一半，剩下一格

3

Take the row number, fetch the row 拿着行号，去取那一行

An index takes space too 索引也要占地方

An index is stored for real: a 3GB table carries about 300MB — one tenth of the table. Every insert writes twice, so writing slows down a little. 索引自己也占硬盘：3GB 的表，索引约 300MB，是表的十分之一。每存一条新数据，表和索引都要各写一次，写入会慢一点。

A heavy tome and its slim booklet: a tenth more shelf, and no more page-flipping. 一本大部头配一本薄册子：多占十分之一的地方，换来不用逐页翻。

Now you know 现在你知道了

An index is a sorted copy pointing each value at its row. 索引是一份排好序的副本，记着每个值在原表的第几行。
A query halves its way through the index, then fetches by row number. 查询在索引里一次丢一半，最后按行号直接取那一行。
The same query drops from 4 seconds to 4 milliseconds; the cost is space and slower writes. 查询从 4 秒变 4 毫秒；代价是占空间、写入慢一点。