A database index helps the database find rows faster without scanning every row.
A database index is an additional data structure that helps the database locate matching rows more efficiently.
It stores selected column values in an organized form and keeps a reference to the rows where those values appear.
Simple Definition: An index is a shortcut for finding rows in a table.
You search every page of a phone book from the beginning.
The answer may be there, but you must inspect many entries first.
You use the alphabetized names to jump close to the answer.
You still verify the matching entry, but you skip most unrelated entries.
An index can help the database:
The database optimizer decides whether an index is useful for a particular query.
Indexes are commonly used on columns that applications search, join, sort, or require to be unique.
User email, order status, customer ID, created date, or foreign keys.
An index takes storage and can make writes slower, so every column does not need one.
Suppose an orders table contains millions of rows and the application often finds one customer’s orders.
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
SELECT *
FROM orders
WHERE customer_id = 42;The index gives the database a faster path to rows whose customer_id is 42. It does not change the result that the query should return.
An index is one possible access path. The optimizer chooses it only when its estimated cost is better for that query.
A structure that helps locate rows faster. It can improve reads but adds storage and write work.
The structure that stores the actual rows and columns. The table remains the source of the returned data.
Index ≠ a copy of the whole table. It is an access aid maintained alongside the table.
| ENGLISH TERM | 中文 | SIMPLE ENGLISH | 中文小白理解 |
|---|---|---|---|
| Index | 索引 | A structure that helps a database find rows faster. | 帮助数据库更快找到数据行的辅助结构。 |
| Index Key | 索引键 | The indexed value used to organize and search entries. | 用来组织和查找索引条目的字段值。 |
| Table Scan | 全表扫描 | Reading rows across a table to find matches. | 把表里的很多行逐行检查来寻找符合条件的数据。 |
| Query Optimizer | 查询优化器 | The database component that chooses a query execution plan. | 数据库中负责选择查询执行方案的部分。 |
| Unique Index | 唯一索引 | An index that prevents duplicate indexed values. | 不允许索引字段出现重复值的索引。 |
| Composite Index | 复合索引 | An index built from more than one column. | 由多个字段一起组成的索引。 |
| Query Plan | 查询计划 | The steps a database plans to use to run a query. | 数据库准备如何执行一个查询的步骤方案。 |
| Write Overhead | 写入开销 | Extra work required to update indexes when data changes. | 数据新增或修改时同步维护索引所需的额外工作。 |
An index is a shortcut that can help a database find rows without scanning every row.
Indexes can speed up reads, but they use storage and add work to writes.
The database optimizer chooses whether an index helps a particular query.