您如何优化缓慢的 SQL 查询？

Question

您如何优化缓慢的 SQL 查询？

Accepted Answer

优化缓慢的查询首先要**分析执行计划**（使用 `EXPLAIN`）找到瓶颈，然后应用有针对性的修复——最常见的是**添加索引**、重写查询和避免常见的反模式。最大的改进通常是将全表扫描转变为索引查找。

## 步骤 1：使用 EXPLAIN 分析

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN`（或 `EXPLAIN ANALYZE`）显示数据库如何执行查询——特别是它是在进行**全表扫描**（缓慢）还是使用**索引**（快速）。始终在优化前进行性能分析。

## 最大的改进：添加索引

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

最常见和最有影响力的修复：在 `WHERE`、`JOIN` 和 `ORDER BY` 中使用的列上**添加索引**。这将全表扫描转变为快速查找。

## 需要修复的常见反模式

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## 其他优化技术

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## 为什么这很重要

查询优化是数据库性能的关键知识——缓慢的查询是导致应用性能不佳的主要原因，理解如何找到和修复这些查询是对任何大规模使用数据库的开发人员来说都至关重要的高级技能。

基础学科是**使用 `EXPLAIN` 进行分析**（检查执行计划以找到实际瓶颈——特别重要的是查询是进行缓慢的全表扫描还是进行快速的索引查找）然后再进行优化，而不是凭猜测——这种以测量为驱动的方法是使优化有效的关键。

**单一最大且最常见的改进是在过滤、连接和排序列上添加索引**（将全表扫描转变为快速查找——通常能将查询时间改进数个数量级），使索引知识成为优化的核心。

理解**常见的反模式**同样重要：对列使用函数和以通配符开头的 `LIKE` 会阻止索引使用，`SELECT *` 会获取不必要的数据，特别是 **N+1 查询**（在循环中查询而不是单个 JOIN——应用代码中常见的、严重的性能杀手）——识别和修复这些问题解决了导致缓慢查询的最常见原因。

其他技术（复合索引、减少扫描的数据、将子查询改写为 JOIN、保持统计信息最新、为昂贵的查询使用物化视图）完善了工具集。

由于查询性能直接影响应用的响应能力和可伸缩性，而且由于系统化方法（使用 EXPLAIN 分析，然后进行修复——通常是添加索引和消除 N+1 等反模式）是在负载下保持数据库快速运行的关键，掌握查询优化是重要的、高价值的高级知识，它将那些能够构建高性能、可伸缩的数据库驱动应用程序的开发人员区分开来——一个频繁相关、至关重要的技能，也是数据库性能方面的常见面试话题。