⚡

SQL优化Skill optimizing-sql

这个技能用于优化SQL查询性能，通过分析执行计划、设计索引策略和重写查询，提高PostgreSQL、MySQL和SQL Server等数据库的效率和响应速度。关键词：SQL优化、查询性能、数据库索引、PostgreSQL、MySQL、SQL Server、执行计划分析、查询重写、索引设计。

后端开发 0 次安装 2 次浏览 更新于 3/23/2026

名称: 优化SQL 描述: 通过EXPLAIN分析、索引策略和查询重写优化SQL查询性能，适用于PostgreSQL、MySQL和SQL Server。用于调试慢查询、分析执行计划或提高数据库性能。

SQL优化

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

何时使用此技能

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

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

核心优化工作流程

步骤1：分析查询性能

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

PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

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过滤器或索引
Nested Loop with large outer table 低效的连接算法 索引连接列
Correlated subquery 子查询每行执行 重写为JOIN或EXISTS
Sort operation on large result set 昂贵的排序 添加匹配ORDER BY的索引

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

步骤3：应用索引策略

索引决策框架:

列是否用于WHERE、JOIN、ORDER BY或GROUP BY？
├─ 是 → 列是否具有选择性（许多唯一值）？
│  ├─ 是 → 表是否频繁查询？
│  │  ├─ 是 → 添加索引
│  │  └─ 否 → 基于查询频率考虑
│  └─ 否（低选择性） → 跳过索引
└─ 否 → 跳过索引

按用例分类的索引类型:

PostgreSQL:

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

MySQL:

  • B-tree（默认）：通用索引
  • Full-text：在VARCHAR/TEXT列上的文本搜索
  • Spatial：空间数据类型

SQL Server:

  • Clustered：按索引排序的表数据（每表一个）
  • Non-clustered：独立的索引结构（允许多个）

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

步骤4：设计复合索引

对于在多列上过滤的查询，使用复合索引：

列顺序重要:

  1. 等式过滤器优先（最具选择性）
  2. 其他等式过滤器（按选择性）
  3. 范围过滤器或ORDER BY（最后）

示例:

-- 查询模式
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- 最优复合索引
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

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

步骤5：重写低效查询

常见反模式避免:

*1. SELECT （过度获取）

-- ❌ 差：获取所有列
SELECT * FROM users WHERE id = 1;

-- ✅ 好：仅获取所需列
SELECT id, name, email FROM users WHERE id = 1;

2. N+1查询

-- ❌ 差：1 + N查询
SELECT * FROM users LIMIT 100;
-- 然后在循环中：SELECT * FROM posts WHERE user_id = ?;

-- ✅ 好：单一JOIN
SELECT users.*, posts.id AS post_id, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

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

-- ❌ 差：函数阻止索引使用
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- ✅ 好：Sargable范围条件
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

4. 相关子查询

-- ❌ 差：子查询每行执行
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
FROM users;

-- ✅ 好：JOIN与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/Full-text） 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两侧索引外键列
  • [ ] 从返回最少行的表开始排序连接
  • [ ] 尽可能使用INNER JOIN（比OUTER JOIN更高效）
  • [ ] 避免连接超过5个表（拆分为CTE或子查询）
  • [ ] 考虑在读取密集型系统中对频繁连接的表进行反规范化

执行计划性能目标

扫描类型 性能 何时可接受
Index-Only Scan 最佳 总是首选
Index Scan 优秀 中小结果集
Bitmap Heap Scan 好 中等结果集（PostgreSQL）
Sequential Scan 差 仅用于小表（<1000行）或全表查询
Table Scan 差 仅用于小表或不可避免的全扫描

数据库特定优化

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 vs IN 用于子查询

对大型数据集使用EXISTS以获得更好性能：

-- ✅ 好：EXISTS在首次匹配时停止
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- ❌ 效率较低：IN构建完整列表
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

反规范化决策框架

考虑反规范化当：

  • 查询频繁连接3+个表
  • 数据相对静态（更新不频繁）
  • 读取性能关键
  • 写入开销可接受

反规范化策略:

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

优化工作流程示例

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

步骤1：识别慢查询

使用APM/可观测性工具识别导致延迟的数据库查询

步骤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特定优化

有关工作SQL示例，请参阅examples/目录。