Skip to content

查询 Optimization

制作查询快 使用 EXPLAIN, 索引, selective 连接, and avoiding 常见 pitfalls.

你将学到

  • Using EXPLAIN and EXPLAIN ANALYZE 到理解查询计划
  • Identifying sequential scans and missing 索引
  • Optimizing 连接通过筛选 early and 合并 in 正确顺序
  • Avoiding N+1 查询 and SELECT * trap
  • Partitioning and denormalization for 大表

概念讲解

EXPLAIN 和 EXPLAIN ANALYZE

EXPLAIN shows the query plan the database will use — which tables are scanned, which indexes are used, how tables are joined, and estimated costs. EXPLAIN ANALYZE actually runs the query and shows real timing data. This is the single most important tool for optimization. Look for Sequential Scan on large tables (bad — it reads every row) vs Index Scan (good — it uses an index). Look for high-cost nodes at the top of the plan.

Missing Indexes

If EXPLAIN shows a sequential scan on a large table, you likely need an index on the filtered column. Add the index and re-run EXPLAIN to confirm it is used. But remember: the optimizer may choose not to use an index if the table is small or if the query returns a large fraction of rows (an index is not helpful when you read most of the table anyway).

Optimizing JOINs

The database optimizer usually picks a good join order, but you can help by: (1) filtering early — put restrictive WHERE conditions before joining, (2) joining on indexed columns (foreign keys should be indexed), (3) avoiding joining large tables without a filter. If a join is slow, check that the join columns have indexes and that the WHERE clause reduces the row count significantly before the join.

The N+1 Problem

The N+1 query problem occurs when you fetch N records and then make one additional query per record (N+1 total queries). For example, fetching 100 orders, then querying the user for each order individually — 101 queries. The fix is a JOIN (fetch orders and users in one query) or batch loading (load all 100 users in one query by their IDs). ORM eager-loading options exist specifically to solve this.

Partitioning and Denormalization

For very large tables (millions/billions of rows), consider partitioning — splitting the table into physical chunks by date or ID range. The database prunes partitions that cannot contain matching rows, turning a full scan of a billion-row table into a scan of one million-row partition. Denormalization — duplicating data to avoid expensive joins — is another option for read-heavy workloads, but it adds complexity to writes.

示例代码

              -- See the query plan without running
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- See the plan with actual timing (runs the query)
EXPLAIN ANALYZE
SELECT u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- If EXPLAIN shows Seq Scan on orders, add an index:
CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

-- Avoid N+1: use a JOIN instead of per-row queries
SELECT u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

-- Partition a large orders table by year (PostgreSQL)
CREATE TABLE orders (
  id         BIGSERIAL,
  user_id    BIGINT NOT NULL,
  total      DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
            

EXPLAIN ANALYZE 到 inspect 计划, 添加 composite 索引到 fix sequential scan, JOIN 到 avoid N+1, and 范围 partitioning for 大表.

动手试一试

  • SQL Formatter

    格式 EXPLAIN and 复杂查询 for analysis.

  • Code Diff

    Compare 查询计划之前 and 之后 optimization.

常见错误

错误写法

添加索引不使用检查如果 optimizer uses 它

正确做法

运行 EXPLAIN 之前 and 之后添加索引. 如果 optimizer still 做 sequential scan, 索引是 不 helping — 查询可能返回也 许多行 or 条件可能不 match 索引.

错误写法

N+1 查询 problem in ORM code

正确做法

Use eager 加载 (JOIN) or batch 加载相反的 lazy-加载 related 记录 in 循环. Monitor 查询数量每 请求 in development.

练习 查询 Optimization

1 个练习

练习 查询 Optimization

查阅不认识的术语

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

查看术语表

动手做项目

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

查看项目

解读错误信息

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

查看错误