你如何优化缓慢的数据库查询？

Question

你如何优化缓慢的数据库查询？

Accepted Answer

**从查询计划开始，然后修复最大的成本。** 使用 `EXPLAIN`/`EXPLAIN ANALYZE` 查看数据库实际执行的操作，添加正确的 **indexes**，消除 **N+1** 查询，并仅选择需要的数据。

## 为什么这很重要

`EXPLAIN ANALYZE` 显示执行计划和实际执行时间。对大型表进行 **Seq Scan** 是经典的危险信号：

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders  (cost=0.00..18500 rows=120)
--   Filter: (customer_id = 42)
--   rows removed by filter: 999880   ← scanned the whole table!
```

## 添加正确的索引

索引将全表扫描转变为快速查找：

```sql
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now: Index Scan using idx_orders_customer  (cost=0.42..8.5 rows=120)
--   1,000,000-row scan → ~120-row lookup
```

索引对**写操作有成本**：每个 INSERT/UPDATE 都必须更新索引，因此只对用于 filter/join/sort 的列建立索引 — 不是每一列。一个 **composite** 索引 `(customer_id, created_at)` 可以在一个结构中处理 `WHERE customer_id = ? ORDER BY created_at`。

## 修复 N+1 和过度获取

**N+1 问题** — 一个查询获取列表，然后为每一行执行一个查询 — 是导致性能下降的主要原因。用 eager loading、`JOIN` 或批量 `IN (...)` 替代它：

```sql
-- N+1: 1 + 100 queries
SELECT * FROM orders;            -- then per order: SELECT * FROM users WHERE id = ?

-- Fixed: one JOIN, only needed columns
SELECT o.id, o.total, u.name
FROM orders o JOIN users u ON u.id = o.user_id;
```

同时 **只选择需要的列**（避免 `SELECT *`）并用 `LIMIT`/keyset pagination **分页**，这样你永远不会加载数百万行。

## 为什么这很重要

数据库是最常见的瓶颈。阅读执行计划可以告诉你查询*为什么*缓慢，而不是猜测；正确的索引、消除 N+1 以及缩减结果集通常会将耗时数秒的查询转变为毫秒级的查询 — 无需增加硬件。