optimizing-sql

SQL 优化

通过执行计划分析、战略性索引和查询重写，为 PostgreSQL、MySQL 和 SQL Server 提供切实可行的 SQL 查询性能优化指导。

何时使用此技能

遇到以下情况时触发此技能：

• 查询性能缓慢或数据库超时
• 分析 EXPLAIN 计划或执行计划
• 确定索引需求
• 重写低效查询
• 识别查询反模式（N+1、SELECT *、相关子查询）
• 针对特定数据库的优化需求（PostgreSQL、MySQL、SQL Server）

核心优化工作流

第 1 步：分析查询性能

运行执行计划分析以识别瓶颈：

PostgreSQL：

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

MySQL：

EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;

SQL Server： 使用 SQL Server Management Studio：显示估计执行计划（Ctrl+L）

需要监控的关键指标：

• 成本：估计的资源消耗
• 行数：处理的行数（估计值与实际值）
• 扫描类型：顺序扫描与索引扫描
• 执行时间：操作实际耗时

有关执行计划的详细解读，请参阅 references/explain-guide.md。

第 2 步：识别优化机会

常见警示信号：

指标	问题	解决方案
Seq Scan / Table Scan	对大型表进行全表扫描	为筛选列添加索引
行数过多	处理的行数过多	添加 WHERE 筛选条件或索引
大型外表上的嵌套循环	连接算法效率低下	为连接列添加索引
相关子查询	子查询对每一行执行一次	重写为 JOIN 或 EXISTS
对大型结果集进行排序	排序开销高	添加与 ORDER BY 匹配的索引

有关扫描类型的解读，请参阅 references/scan-types.md。

第 3 步：应用索引策略

索引决策框架：

Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
├─ YES → Is column selective (many unique values)?
│  ├─ YES → Is table frequently queried?
│  │  ├─ YES → ADD INDEX
│  │  └─ NO → Consider based on query frequency
│  └─ NO (low selectivity) → Skip index
└─ NO → Skip index

按使用场景划分的索引类型：

PostgreSQL：

• B-tree（默认）：通用型，支持 <、≤、=、≥、>、BETWEEN、IN
• Hash：仅支持相等比较（=）
• GIN：全文搜索、JSONB、数组
• GiST：空间数据、几何类型
• BRIN：具有自然排序数据的超大型表

MySQL：

• B-tree（默认）：通用索引
• 全文索引：对 VARCHAR/TEXT 列进行文本搜索
• 空间索引：空间数据类型

SQL Server：

• 聚集索引：表数据按索引排序（每张表一个）
• 非聚集索引：独立的索引结构（可创建多个）

有关完整的索引指导，请参阅 references/indexing-decisions.md 和 references/index-types.md。

第 4 步：设计复合索引

对于按多个列筛选的查询，请使用复合索引：

列顺序很重要：

1. 先放等值筛选条件（选择性最高的优先）
2. 其他等值筛选条件（按选择性排序）
3. 范围筛选条件或 ORDER BY（放在最后）

示例：

-- Query pattern
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- Optimal composite index
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

有关复合索引设计模式，请参阅 references/composite-indexes.md。

第 5 步：重写低效查询

应避免的常见反模式：

*1. SELECT （过度获取数据）

-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = 1;

-- ✅ Good: Fetch only needed columns
SELECT id, name, email FROM users WHERE id = 1;

2. N+1 查询

-- ❌ Bad: 1 + N queries
SELECT * FROM users LIMIT 100;
-- Then in loop: SELECT * FROM posts WHERE user_id = ?;

-- ✅ Good: Single JOIN
SELECT users.*, posts.id AS post_id, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

3. 非 Sargable 查询（对已建立索引的列使用函数）

-- ❌ Bad: Function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- ✅ Good: Sargable range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

4. 相关子查询

-- ❌ Bad: Subquery executes per row
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
FROM users;

-- ✅ Good: JOIN with GROUP BY
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

有关完整的反模式参考，请参阅 references/anti-patterns.md。 有关高效查询模式，请参阅 references/efficient-patterns.md。

快速参考表

索引选择指南

查询模式	索引类型	示例
WHERE column = value	单列 B-tree	CREATE INDEX ON table (column)
WHERE col1 = ? AND col2 = ?	复合 B-tree	CREATE INDEX ON table (col1, col2)
WHERE text_col LIKE '%word%'	全文索引（GIN/全文索引）	CREATE INDEX ON table USING GIN (to_tsvector('english', text_col))
WHERE geom && box	空间索引（GiST）	CREATE INDEX ON table USING GIST (geom)
WHERE json_col @> '{"key":"value"}'	JSONB（GIN）	CREATE INDEX ON table USING GIN (json_col)

JOIN 优化检查清单

• 为 JOIN 两侧的外键列创建索引
• 按返回行数从少到多的顺序进行 JOIN
• 尽可能使用 INNER JOIN（比 OUTER JOIN 更高效）
• 避免 JOIN 超过 5 张表（拆分为 CTE 或子查询）
• 对读操作密集型系统中经常 JOIN 的表，考虑反规范化

执行计划性能目标

扫描类型	性能	可接受场景
仅索引扫描	最佳	始终优先选择
索引扫描	优秀	小到中等结果集
位图堆扫描	良好	中等结果集（PostgreSQL）
顺序扫描	较差	仅适用于小表（<1000 行）或全表查询
表扫描	较差	仅适用于小表或无法避免的全表扫描

数据库特定优化

PostgreSQL 特有功能

部分索引（为行的子集创建索引）：

CREATE INDEX idx_active_users_login
ON users (last_login)
WHERE status = 'active';

表达式索引（为计算值创建索引）：

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

覆盖索引（避免访问堆）：

CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (id, name);

有关 PostgreSQL 优化的全面介绍，请参阅 references/postgresql.md。

MySQL 特有功能

索引提示（覆盖优化器的选择）：

SELECT * FROM orders USE INDEX (idx_orders_customer)
WHERE customer_id = 123;

存储引擎选择：

• InnoDB（默认）：支持事务、行级锁、聚簇主键
• MyISAM：读取速度更快、不支持事务、表级锁

有关 MySQL 优化的全面介绍，请参阅 references/mysql.md。

SQL Server 特有功能

查询存储（追踪查询性能随时间的变化）：

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

执行计划警告：

• 在图形执行计划中查找黄色感叹号
• 粗箭头表示行数较多

有关 SQL Server 优化的全面介绍，请参阅 references/sqlserver.md。

高级优化技术

公用表表达式（CTE）

将复杂查询拆分为可读且易于维护的部分：

WITH active_customers AS (
  SELECT id, name FROM customers WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT ac.name, COALESCE(ro.order_count, 0) as orders
FROM active_customers ac
LEFT JOIN recent_orders ro ON ac.id = ro.customer_id;

子查询中的 EXISTS 与 IN

在大型数据集中使用 EXISTS 可获得更好的性能：

-- ✅ Good: EXISTS stops at first match
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- ❌ Less efficient: IN builds full list
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

反规范化决策框架

在以下情况下考虑反规范化：

• 经常查询并 JOIN 3 张或更多表
• 数据相对静态（更新不频繁）
• 读取性能至关重要
• 可以接受写入开销

反规范化策略：

1. 复制列：将外键数据复制到主表中
2. 汇总表：预先聚合数据
3. 物化视图：由数据库维护的反规范化视图
4. 应用缓存：使用 Redis/Memcached 缓存频繁访问的数据

优化工作流示例

场景： API 端点加载耗时 2 秒

步骤 1：找出慢查询

Use APM/observability tools to identify database query causing delay

步骤 2：运行 EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

步骤 3：分析输出

Seq Scan on orders (cost=0.00..2500.00 rows=10)
  Filter: (customer_id = 123)
  Rows Removed by Filter: 99990

问题：顺序扫描后过滤掉 99,990 行

步骤 4：添加复合索引

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

步骤 5：验证改进

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Index Scan using idx_orders_customer_created (cost=0.42..12.44 rows=10)
  Index Cond: (customer_id = 123)

结果：速度提升 200 倍（2000ms → 10ms）

监控与维护

定期优化任务：

• 每周检查慢查询日志
• 定期更新数据库统计信息（PostgreSQL 中使用 ANALYZE，SQL Server 中使用 UPDATE STATISTICS）
• 监控索引使用情况（删除未使用的索引）
• 归档旧数据，使表保持易于管理
• 每季度检查关键查询的执行计划

PostgreSQL 统计信息更新：

ANALYZE table_name;

MySQL 统计信息更新：

ANALYZE TABLE table_name;

SQL Server 统计信息更新：

UPDATE STATISTICS table_name;

相关技能

• databases-relational：模式设计和数据库基础知识
• observability：性能监控和慢查询检测
• api-patterns：API 层优化（分页、缓存）
• performance-engineering：应用性能分析

附加资源

如需查阅完整文档，请参考以下文件：

• references/explain-guide.md - EXPLAIN 计划的详细解读
• references/scan-types.md - 扫描类型的含义及其对性能的影响
• references/indexing-decisions.md - 何时以及如何添加索引
• references/index-types.md - 特定数据库的索引类型
• references/composite-indexes.md - 多列索引设计
• references/anti-patterns.md - 常见反模式及解决方案
• references/efficient-patterns.md - 高效查询模式
• references/postgresql.md - PostgreSQL 专属优化
• references/mysql.md - MySQL 专属优化
• references/sqlserver.md - SQL Server 专属优化

可在 examples/ 目录中查看可运行的 SQL 示例。