SQL 优化指南：执行计划、索引策略、查询调优、分区与数据库扩展

SQL 性能可以决定一个应用的成败。一条慢查询可能级联导致超时、锁争用和用户体验下降。本指南涵盖 13 个核心 SQL 优化主题——从阅读 EXPLAIN 计划、设计索引到分区、连接池和数据库扩展策略。每个部分都包含可直接应用于 PostgreSQL、MySQL 或其他数据库的实用代码示例。

1. EXPLAIN 与查询计划

每次优化都从理解数据库如何执行查询开始。EXPLAIN 显示优化器选择的查询计划，包括连接策略、索引使用和成本估计。EXPLAIN ANALYZE 实际执行查询并报告真实执行时间。

关键检查项：大表上的顺序扫描（缺少索引）、高成本估计、估计行数与实际行数差异大（统计信息过期）、大结果集上的嵌套循环、以及没有索引支持的排序操作。

在 PostgreSQL 中，EXPLAIN 输出包含多种节点类型：Seq Scan（全表扫描）、Index Scan（索引扫描）、Bitmap Heap Scan（位图堆扫描）、Nested Loop、Hash Join 和 Merge Join。每个节点显示估计成本（startup..total）、估计行数和宽度。EXPLAIN ANALYZE 增加实际时间和行数，帮助发现估计偏差。

-- Run EXPLAIN ANALYZE to see actual execution
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 20;

-- Key output fields to examine:
-- Seq Scan vs Index Scan (is an index being used?)
-- actual time=0.02..12.45 (startup..total time)
-- rows=1000 (estimated) vs actual rows=847
-- Sort Method: quicksort Memory: 128kB

2. 索引类型与策略

索引是最具影响力的优化工具。不同索引类型服务于不同查询模式。B-tree 索引处理等值和范围查询。Hash 索引仅优化等值查找。GIN 索引支持全文搜索和数组包含。GiST 索引处理几何和范围类型查询。部分索引只索引满足条件的行以减小索引大小。覆盖索引包含查询所需的所有列，实现仅索引扫描。

选择正确的索引类型至关重要。B-tree 覆盖 90% 的场景。对 JSONB 列使用 GIN 索引。对地理空间数据使用 GiST 索引。部分索引通过只索引热数据显著减少索引大小和维护开销。记住每个索引都会减慢 INSERT/UPDATE 操作——只创建查询确实需要的索引。

-- B-tree index (default, most common)
CREATE INDEX idx_users_email ON users (email);

-- Partial index (only active users)
CREATE INDEX idx_active_users ON users (email)
  WHERE status = 'active';

-- GIN index for full-text search
CREATE INDEX idx_posts_search ON posts
  USING gin(to_tsvector('english', title || ' ' || body));

-- Covering index (includes columns for index-only scan)
CREATE INDEX idx_orders_covering ON orders (user_id)
  INCLUDE (total_amount, created_at);

-- Hash index (equality only, slightly faster)
CREATE INDEX idx_sessions_token ON sessions
  USING hash (session_token);

3. 查询优化

编写高效 SQL 需要理解优化器如何处理 WHERE 子句、JOIN 和子查询。避免在索引列上使用函数，因为它们会阻止索引使用。关联子查询优先使用 EXISTS 而非 IN。CTE 提高可读性，但在某些数据库中可能充当优化屏障。

JOIN 顺序很重要——优化器通常会重新排序连接，但复杂查询可能需要提示或显式排序。始终尽早过滤以减少中间结果集大小。

另一个常见陷阱是隐式类型转换。当 WHERE 子句中的列类型与比较值不匹配时（如将字符串与整数比较），数据库可能对整列应用类型转换函数，使索引失效。始终确保比较值的类型与列类型一致。

-- BAD: function on indexed column prevents index use
SELECT * FROM users
WHERE LOWER(email) = 'user@example.com';

-- GOOD: use expression index or store normalized
CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));

-- BAD: SELECT * fetches unnecessary columns
SELECT * FROM orders WHERE user_id = 42;

-- GOOD: select only needed columns
SELECT id, total_amount, created_at
FROM orders WHERE user_id = 42;

-- Use EXISTS instead of IN for correlated subquery
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

4. 索引设计模式

有效的索引设计需要理解查询工作负载。组合索引必须遵循最左前缀规则：(a, b, c) 上的索引可以满足 (a)、(a, b) 或 (a, b, c) 的查询，但不能满足仅 (b, c) 的查询。覆盖索引包含所有 SELECT 列，完全消除表查找。仅索引扫描是最快的访问路径。

设计组合索引时的经验法则（ESR 规则）：先放 Equality 列（精确匹配），再放 Sort 列（ORDER BY），最后放 Range 列（范围扫描）。这个顺序让索引满足尽可能多的查询操作而无需额外排序或回表。使用 pg_stat_user_indexes 视图监控索引使用情况，删除未使用的索引。

-- Composite index: equality first, range second
-- Query: WHERE status = 'active' AND created_at > ?
CREATE INDEX idx_status_created
  ON orders (status, created_at);

-- This index supports ORDER BY as well
-- WHERE status = 'active' ORDER BY created_at DESC
CREATE INDEX idx_status_created_desc
  ON orders (status, created_at DESC);

-- Covering index for index-only scans
-- Query: SELECT email, name FROM users WHERE status = 'active'
CREATE INDEX idx_users_status_covering
  ON users (status) INCLUDE (email, name);

-- Verify index-only scan in EXPLAIN output:
-- "Index Only Scan using idx_users_status_covering"

5. N+1 查询问题

N+1 问题发生在代码获取 N 条记录后，对每条记录执行一次额外查询来加载关联数据。这导致 N+1 次查询而非 1 或 2 次。这是最常见的 ORM 相关性能问题，可能将 5ms 的页面加载变成 5 秒。

检测方法：启用查询日志，查找参数不同但结构相同的重复查询。修复方式：预加载（JOIN FETCH）、批量查询（WHERE id IN (...)）或 dataloader 模式。

在 GraphQL 应用中，N+1 问题尤为严重，因为解析器按字段独立执行。Facebook 的 DataLoader 模式是标准解决方案：它收集一个事件循环周期内的所有加载请求，然后用一次批量查询满足所有请求。Node.js、Python、Ruby 和 Java 都有 DataLoader 实现。

-- N+1 Problem: 1 query for users + N queries for orders
-- Query 1: SELECT * FROM users LIMIT 50;
-- Query 2..51: SELECT * FROM orders WHERE user_id = ?;

-- FIX 1: JOIN (eager loading)
SELECT u.*, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (SELECT id FROM users LIMIT 50);

-- FIX 2: Batch query (two queries total)
-- Step 1: SELECT * FROM users LIMIT 50;
-- Step 2: collect user_ids, then:
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5, 7, 8, 12, 15, 20);

-- FIX 3: Django ORM example
-- BAD:  User.objects.all()[:50]  (triggers N+1)
-- GOOD: User.objects.prefetch_related('orders')[:50]

6. 分页策略

OFFSET/LIMIT 分页简单但扩展性差——数据库必须扫描并丢弃偏移量之前的所有行。第 1000 页每页 20 行，数据库读取 20,000 行却丢弃 19,980 行。游标分页使用索引列上的 WHERE 子句直接跳到下一页，保持恒定性能。

游标分页的限制是不支持"跳转到第 N 页"，只支持"下一页/上一页"导航。对于需要页码的 UI，可以用混合方案：前几页用 OFFSET（性能尚可），深层页面引导用户使用搜索或过滤来缩小结果集。API 分页几乎总应该使用游标方式。

-- BAD: OFFSET pagination (slow for deep pages)
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 10000;  -- scans 10020 rows!

-- GOOD: Keyset / cursor pagination
SELECT * FROM products
WHERE id > 50240  -- last seen ID from previous page
ORDER BY id
LIMIT 20;  -- always scans exactly 20 rows

-- For multi-column sorting:
SELECT * FROM products
WHERE (created_at, id) > ('2025-06-15', 9823)
ORDER BY created_at, id
LIMIT 20;

-- Encode cursor as base64 for API responses
-- cursor: eyJjcmVhdGVkX2F0IjoiMjAyNS0wNi0xNSIsImlkIjo5ODIzfQ==

7. 窗口函数

窗口函数在与当前行相关的一组行上执行计算，而不将其折叠为单一输出行。它们替代了许多之前需要自连接或关联子查询的模式。常用窗口函数包括 ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM 和 AVG 配合 OVER 子句。

窗口函数的 PARTITION BY 子句将行分组（类似 GROUP BY 但不折叠行），ORDER BY 定义窗口内的排序。帧子句（ROWS BETWEEN / RANGE BETWEEN）控制计算包含哪些行。默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这对运行总计很有用，但对移动平均需要显式指定帧范围。

-- ROW_NUMBER: assign sequential numbers
SELECT name, department, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

-- LAG / LEAD: access previous / next row values
SELECT date, revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
FROM daily_sales;

-- Running total with SUM window function
SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM transactions;

8. 公共表表达式

CTE（WITH 子句）通过将复杂逻辑分解为命名步骤来提高查询可读性。递归 CTE 可以遍历层级数据，如组织架构或分类树。在 PostgreSQL 12+ 中，非递归 CTE 被优化器内联（不再是优化屏障）。需要时使用 MATERIALIZED 提示强制物化以提升性能。

递归 CTE 的核心结构是：基础查询 UNION ALL 递归查询。递归查询引用 CTE 自身，每次迭代产生新行，直到没有新行产生为止。始终添加深度限制（WHERE depth < N）或使用 CYCLE 检测来防止无限循环。递归 CTE 是处理树形结构、图遍历和层级数据的标准方法。

-- Recursive CTE: traverse org chart hierarchy
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive step: find direct reports
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
  WHERE t.depth < 10  -- prevent infinite loops
)
SELECT * FROM org_tree ORDER BY depth, name;

-- Materialized CTE (PostgreSQL 12+)
WITH expensive_calc AS MATERIALIZED (
  SELECT user_id, SUM(amount) AS total
  FROM orders GROUP BY user_id
)
SELECT * FROM expensive_calc WHERE total > 1000;

9. 分区

表分区将大表拆分为较小的物理部分，同时维护单一逻辑表。范围分区按日期或数值范围划分。列表分区按离散值划分（地区、状态）。哈希分区将行均匀分布到各分区。优化器裁剪查询不需要的分区，显著减少 I/O。

最佳分区候选：数亿行的表、基于日期查询的时序数据、以及定期归档或删除旧数据的表。

分区维护建议：为时序数据设置自动创建未来分区的 cron 任务。使用 pg_partman 扩展自动化分区管理。旧分区可以 DETACH 后移动到冷存储或直接 DROP，比 DELETE 百万行快几个数量级。PostgreSQL 14+ 支持分区表上的并行查询，进一步提升性能。

-- Range partitioning by date (PostgreSQL)
CREATE TABLE events (
  id         BIGSERIAL,
  event_type TEXT,
  payload    JSONB,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Queries automatically prune irrelevant partitions
SELECT * FROM events
WHERE created_at >= '2025-02-01'
  AND created_at < '2025-03-01';
-- Only scans events_2025_02 partition

10. 连接池

数据库连接是昂贵的资源。每个 PostgreSQL 连接消耗约 10MB 内存和一个服务器端进程。连接池在应用请求之间复用固定数量的连接。流行的连接池包括 PgBouncer（外部、轻量级）和 HikariCP（JVM、嵌入式）。最佳池大小公式：连接数 = (CPU核心数 * 2) + 有效磁盘数。

常见错误是设置过大的连接池。16 核数据库的最佳总连接数约 33-35 个，而非数百个。过多连接导致过度上下文切换。

PgBouncer 支持三种池模式：session（连接绑定到整个会话，兼容性最好）、transaction（连接在事务结束后归还池，推荐用于大多数应用）、statement（每条语句后归还，不支持事务）。对于微服务架构，建议在每个服务前部署 PgBouncer 实例，然后服务器端再部署一个汇总层。

-- PgBouncer configuration (pgbouncer.ini)
-- [databases]
-- mydb = host=localhost port=5432 dbname=mydb
--
-- [pgbouncer]
-- listen_port = 6432
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 20
-- min_pool_size = 5

-- Pool sizing formula:
-- connections = (core_count * 2) + spindle_count
-- 4-core SSD server: (4 * 2) + 1 = 9 connections

-- HikariCP (Java / Spring Boot)
-- spring.datasource.hikari.maximum-pool-size=10
-- spring.datasource.hikari.minimum-idle=5
-- spring.datasource.hikari.idle-timeout=300000
-- spring.datasource.hikari.connection-timeout=20000
-- spring.datasource.hikari.max-lifetime=1200000

11. 锁与并发

PostgreSQL 使用 MVCC（多版本并发控制）——读不阻塞写，写不阻塞读。每个事务看到数据库的一个快照。UPDATE/DELETE 获取行级锁。理解锁模式和死锁预防对高并发应用至关重要。

悲观锁（SELECT FOR UPDATE）防止并发修改但降低吞吐量。乐观锁（版本列）允许并发读取并在写入时检测冲突。始终以一致的顺序访问表以防止死锁。

死锁预防的关键原则：所有事务以相同顺序访问表和行。保持事务尽量短小。避免在事务中进行用户交互或外部 API 调用。设置合理的 lock_timeout 和 statement_timeout。使用 pg_locks 视图和 log_lock_waits 参数监控锁争用。出现死锁时，PostgreSQL 会自动中止一个事务——应用层需要实现重试逻辑。

-- Pessimistic locking: SELECT FOR UPDATE
BEGIN;
SELECT * FROM inventory
WHERE product_id = 42
FOR UPDATE;  -- locks this row

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42;
COMMIT;

-- Optimistic locking: version column
UPDATE products
SET price = 29.99, version = version + 1
WHERE id = 42 AND version = 5;
-- If 0 rows updated -> conflict detected, retry

-- Advisory locks for application-level locking
SELECT pg_advisory_lock(hashtext('process-orders'));
-- ... do exclusive work ...
SELECT pg_advisory_unlock(hashtext('process-orders'));

12. 慢查询分析

查找和修复慢查询需要系统性监控。PostgreSQL pg_stat_statements 跟踪所有查询的执行统计。MySQL slow_query_log 捕获超过时间阈值的查询。两者都提供总执行时间、调用次数和平均持续时间——关注总时间最高的查询，而非单次最慢的执行。

优化工作流程：首先按总执行时间排序识别高影响查询。然后对每个查询运行 EXPLAIN ANALYZE 理解执行计划。检查是否缺少索引、统计信息是否过期（运行 ANALYZE 更新）、查询是否可以重写。修复后验证改进并持续监控。auto_explain 扩展可以自动记录慢查询的执行计划，是诊断间歇性性能问题的利器。

-- Enable pg_stat_statements (postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Find top 10 slowest queries by total time
SELECT query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- MySQL: enable slow query log
-- SET GLOBAL slow_query_log = 'ON';
-- SET GLOBAL long_query_time = 1;  -- seconds
-- SET GLOBAL log_queries_not_using_indexes = 'ON';

13. 数据库扩展

当单台服务器无法承载负载时，扩展策略包括垂直扩展（更大的服务器）、读副本（分发读流量）和分片（将写流量分布到多个数据库）。读副本是最简单的扩展方式——将所有 SELECT 路由到副本，写操作发送到主库。分片增加显著的应用复杂性，应作为最后手段。

分片前先穷尽其他选项：优化查询、添加索引、实现缓存（Redis/Memcached）、使用读副本、表分区和归档旧数据。大多数应用永远不需要分片。

读副本的实现要点：配置流复制（streaming replication），应用层按查询类型路由（写到主库，读到副本）。注意副本存在复制延迟（通常毫秒级），对一致性要求极高的读操作仍需路由到主库。ProxySQL（MySQL）或 Pgpool-II（PostgreSQL）可以自动处理读写分离。分片策略包括按 ID 范围分片、按哈希分片和按租户分片（多租户 SaaS 最常用）。

-- Read replica routing (application level)
-- Primary: INSERT, UPDATE, DELETE
-- Replica: SELECT queries

-- PostgreSQL streaming replication setup:
-- primary postgresql.conf:
-- wal_level = replica
-- max_wal_senders = 10

-- replica recovery.conf:
-- primary_conninfo = 'host=primary port=5432'
-- standby_mode = on

-- Application-level read/write splitting
-- const db = {
--   primary: new Pool({ host: 'primary-db' }),
--   replica: new Pool({ host: 'replica-db' }),
-- };
-- const read = (sql) => db.replica.query(sql);
-- const write = (sql) => db.primary.query(sql);

总结：SQL 优化检查清单

SQL 优化不是一次性工作，而是持续的过程。以下是一个实用的优化检查清单，按优先级排序：

1. 监控优先 — 启用 pg_stat_statements 或 slow_query_log，识别总执行时间最高的查询
2. 分析查询计划 — 对高影响查询运行 EXPLAIN ANALYZE，检查顺序扫描和行数估计偏差
3. 添加缺失索引 — 根据查询模式创建组合索引和覆盖索引，遵循 ESR 规则
4. 修复 N+1 查询 — 在开发环境启用查询检测工具，使用预加载或批量查询替代
5. 优化分页 — 将 OFFSET 分页迁移到游标分页，尤其是 API 端点
6. 调整连接池 — 使用公式设置合理的池大小，部署 PgBouncer 或 HikariCP
7. 考虑分区 — 对超过 1 亿行的表评估分区策略
8. 最后才扩展 — 先读副本、后缓存、最后再考虑分片

常见问题