Skip to content

索引

速度上 查询使用索引: B-树, 唯一, composite, and 当到 avoid 它们.

你将学到

  • 什么索引是 and 如何它 速度上 查询
  • B-树索引 — 默认 and 大多数常见类型
  • Composite 索引 and 列排序
  • 唯一索引对比唯一 constraints
  • trade-off: faster 读取对比 slower 编写 and storage

概念讲解

什么是索引?

An index is a separate data structure that the database maintains alongside your table to find rows quickly. Without an index, the database must scan every row (a sequential scan) to find matches. With an index, it looks up the value in the index (typically O(log n) for a B-tree) and jumps directly to the matching rows. The difference on a million-row table can be milliseconds vs seconds.

B-Tree Indexes

The default index type in most databases is a B-tree (balanced tree). It keeps values sorted, so it accelerates equality lookups (WHERE id = 42), range queries (WHERE price BETWEEN 10 AND 50), and ORDER BY on the indexed column. PostgreSQL, MySQL, and SQLite all create B-tree indexes by default.

Composite Indexes

A composite index covers multiple columns: CREATE INDEX idx ON 顺序 (user_id, created_at). It speeds up queries that filter on both columns in order, or on just user_id (the leftmost prefix). It does not help queries that filter only on created_at — column order matters. Put the most selective column first, and the column used in equality (vs range) conditions first.

Unique Indexes

A CREATE UNIQUE INDEX enforces uniqueness just like a UNIQUE constraint. In fact, UNIQUE and PRIMARY KEY constraints are implemented as unique indexes under the hood. You can create a unique index directly when you need uniqueness on a column that is not part of a constraint declaration.

The Trade-Off

Indexes are not free. Every INSERT, UPDATE, and DELETE must update every index on the table, which slows down writes. Indexes also consume disk space. A good rule of thumb: index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses, and avoid indexing columns that are rarely queried or that are updated very frequently. Use EXPLAIN to verify that an index is actually being used.

示例代码

              -- Create a B-tree index on a frequently filtered column
CREATE INDEX idx_orders_status ON orders (status);

-- Composite index: user_id first (equality), then created_at (range/sort)
CREATE INDEX idx_orders_user_date
  ON orders (user_id, created_at);

-- Unique index to enforce uniqueness on a column
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Partial index: only index active users (PostgreSQL)
CREATE INDEX idx_active_users ON users (last_login)
  WHERE active = TRUE;

-- Check if the index is used
EXPLAIN SELECT * FROM orders
  WHERE user_id = 42
  ORDER BY created_at DESC;
            

Five 索引模式: 单个-列, composite 使用 careful 排序, 唯一, 部分 (PostgreSQL), and EXPLAIN 到验证 usage.

动手试一试

  • SQL Formatter

    格式 CREATE INDEX and EXPLAIN 语句.

  • Code Diff

    Compare 查询计划之前 and 之后添加索引.

常见错误

错误写法

添加也 许多索引

正确做法

每个索引 slows 下编写 and uses storage. 索引仅 列那个是 频繁地 queried. Use EXPLAIN 到确认索引是 used 之前 keeping 它.

错误写法

错误列 顺序 in composite 索引

正确做法

Put equality-筛选列 之前范围-筛选列. composite 索引 on (, b) helps WHERE = 1 AND b > 5, 但不 WHERE b > 5 alone.

练习 索引

5 个练习

练习 索引

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误