name: sql-query-optimization description: SQL查询优化，针对PostgreSQL/MySQL，包括索引、EXPLAIN分析。用于慢查询、N+1问题、缺失索引、或遇到顺序扫描、OFFSET分页、临时表溢出、低效JOINs。
关键词：sql优化、查询性能、数据库索引、explain analyze、慢查询、n+1问题、 查询计划、索引策略、复合索引、覆盖索引、postgresql性能、mysql优化、 查询重写、预处理语句、连接池、顺序扫描、缺失索引、SELECT *、 LIMIT优化、子查询性能、分页、基于游标的分页、批量操作、 pg_stat_statements、全文搜索、位图扫描、索引仅扫描、查询调优、数据库性能、 缓存命中率、work_mem、shared_buffers、数据库监控 license: MIT
SQL查询优化
状态: 生产就绪 ✅ 最后更新: 2025-12-15 最新版本: PostgreSQL 17, MySQL 8.4 依赖项: 无
快速开始 (10分钟)
1. 识别慢查询
-- PostgreSQL: 启用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 查找最慢的查询
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
2. 使用 EXPLAIN 分析
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123;
-- 注意：
-- - 大表上的顺序扫描 → 需要索引
-- - 高“过滤移除的行” → 选择性差
-- - 临时读写 → 增加 work_mem
3. 创建索引
-- 添加缺失索引
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);
-- 验证改进
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- 执行时间应下降 10-100 倍
关键规则
总是要做 ✓
| 规则 | 原因 | 示例 |
|---|---|---|
| 索引外键 | JOIN 需要索引列 | CREATE INDEX idx_orders_user ON orders(user_id) |
| 在生产前使用 EXPLAIN ANALYZE | 验证查询计划最优 | EXPLAIN (ANALYZE, BUFFERS) <query> |
| 选择特定列 | 减少数据传输 90% | SELECT id, name FROM users 而不是 SELECT * |
| 为无限查询添加 LIMIT | 防止内存耗尽 | SELECT * FROM logs ORDER BY id LIMIT 100 |
| 使用预处理语句 | 防止 SQL 注入 + 更快 | db.query('SELECT * FROM users WHERE id = $1', [id]) |
| 批量操作后运行 ANALYZE | 更新查询计划器统计 | ANALYZE table_name |
| 监控 pg_stat_statements | 跟踪查询性能随时间变化 | 每日审查回归 |
| 使用连接池 | 减少连接开销 10 倍 | new Pool({ max: 20 }) |
永远不要做 ✗
| 反模式 | 问题 | 修复 |
|---|---|---|
| 生产中使用 SELECT * | 获取不必要列 | 仅选择特定列 |
| 前导通配符 LIKE ‘%term%’ | 无法使用索引 | 改用全文搜索 |
| 字符串拼接构建 SQL | SQL 注入漏洞 | 使用参数化查询 |
| 大结果无 LIMIT | 内存耗尽 | 始终添加 LIMIT + 分页 |
| 循环中的 N+1 查询 | 网络延迟 × N | 使用 JOIN 或批量加载 |
| 忽略 EXPLAIN 输出 | 部署慢查询到生产 | 部署前始终 EXPLAIN |
| 循环中多次 INSERT | 批量操作慢 | 使用带多个 VALUES 的批量 INSERT |
| 分页使用 OFFSET | O(n) 时间，扫描跳过行 | 使用基于游标的分页 |
前 7 个关键错误
1. 大表上的顺序扫描
症状: Seq Scan on orders (cost=0.00..150000.00) 在 1M+ 行上
原因: 过滤列无索引
修复: CREATE INDEX idx_orders_column ON orders(column)
影响: 10-100 倍更快
2. 外键缺失索引
症状: 慢 JOINs (5+ 秒)
原因: 外键列未索引
修复: CREATE INDEX idx_orders_user_id ON orders(user_id)
影响: 50-500 倍更快 JOINs
3. N+1 查询问题
症状: 1 + N 查询用于 N 条记录
原因: ORM 循环中的延迟加载
修复: 使用 JOIN 或急切加载: SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id
影响: N 查询 → 1 查询
4. 前导通配符 LIKE
症状: WHERE name LIKE '%search%' 顺序扫描
原因: 索引无法匹配字符串中间
修复: 使用全文搜索 (GIN 索引) 或 trigrams
影响: 100-1000 倍更快
5. 生产中使用 SELECT *
症状: 高网络流量，慢响应
原因: 获取所有 50 列而不是所需 3 列
修复: SELECT id, name, email (显式列列表)
影响: 数据传输减少 90%
6. 大结果缺失 LIMIT
症状: 服务器内存不足，查询超时
原因: 尝试返回 5M 行
修复: SELECT * FROM logs WHERE ... LIMIT 100 + 分页
影响: 恒定内存使用
7. 批量加载后统计过时
症状: 尽管有索引但选择错误查询计划
原因: PostgreSQL 统计过时
修复: ANALYZE table_name 批量操作后
影响: 正确查询计划选择
参见 references/error-catalog.md 获取所有 12 个错误及详细解决方案。
常见模式总结
| 模式 | 使用场景 | 示例 | 性能 |
|---|---|---|---|
| B-树索引 | 相等、范围、排序查询 | CREATE INDEX idx ON t(col) |
默认，最佳通用用途 |
| 复合索引 | 多列 WHERE 子句 | CREATE INDEX idx ON t(c1, c2) |
比单索引快 5-50 倍 |
| 覆盖索引 | 包含所有查询列 | CREATE INDEX idx ON t(c1) INCLUDE (c2) |
快 2-10 倍 (无堆获取) |
| 部分索引 | 过滤行子集 | CREATE INDEX idx ON t(c) WHERE status='active' |
索引小 50-90% |
| JOIN 重写 | 替换 IN 子查询 | INNER JOIN users u ON o.user_id = u.id |
比子查询快 5-20 倍 |
| 批量 INSERT | 批量数据加载 | INSERT INTO t VALUES (..),(..) |
比单个快 10-100 倍 |
| 游标分页 | 大偏移性能 | WHERE id > last_id LIMIT 100 |
恒定时间 vs O(n) |
配置总结
PostgreSQL 配置
-- 增加 work_mem 用于复杂查询 (可重载 - 无需重启)
SET work_mem = '256MB';
-- 增加 shared_buffers 用于更好缓存 (RAM 的 25%)
ALTER SYSTEM SET shared_buffers = '8GB';
-- 重要：shared_buffers 需要完整 PostgreSQL 服务器重启！
-- 此设置不可通过 pg_reload_conf() 重载！
--
-- 应用 shared_buffers 更改：
-- 1. 停止 PostgreSQL: sudo systemctl stop postgresql
-- 2. 启动 PostgreSQL: sudo systemctl start postgresql
-- 或使用: sudo systemctl restart postgresql
--
-- 验证更改生效：
-- SHOW shared_buffers;
-- 启用自动清理 (可重载 - 可使用 pg_reload_conf)
ALTER SYSTEM SET autovacuum = on;
-- 重载配置 (仅适用于不需要重启的参数)
-- 这不会重载 shared_buffers - 需要重启！
SELECT pg_reload_conf();
MySQL 配置
# my.cnf
[mysqld]
innodb_buffer_pool_size = 8G # RAM 的 70%
max_connections = 500
slow_query_log = 1
long_query_time = 1
何时加载参考
性能分析:
- 加载
references/explain-analysis.md当: 阅读 EXPLAIN 输出、理解查询计划、分析缓冲区统计、比较 PostgreSQL vs MySQL EXPLAIN - 加载
references/performance-monitoring.md当: 设置监控、跟踪慢查询随时间变化、监控缓存命中率、识别膨胀表
索引优化:
- 加载
references/index-strategies.md当: 选择索引类型 (B-树、GIN、GiST、Hash)、创建复合索引、确定列顺序、使用覆盖索引、实现部分索引、监控索引使用
查询优化:
- 加载
references/query-rewrites.md当: 重写慢查询、转换子查询为 JOINs、消除 N+1 查询、实现分页、优化 LIKE 查询、批量操作
系统化流程:
- 加载
references/optimization-workflow.md当: 遵循逐步优化流程、创建优化假设、测量改进、监控长期性能
错误解决:
- 加载
references/error-catalog.md当: 调试特定错误 (顺序扫描、缺失索引、N+1 查询等)、理解根本原因、实施已验证解决方案
使用捆绑资源
模板 (复制粘贴 SQL)
# EXPLAIN 查询模板
templates/explain-query.sql
# 索引创建模式
templates/index-examples.sql
# 查询重写示例
templates/query-rewrites.sql
# 监控查询
templates/monitoring-queries.sql
参考 (深度探讨)
# 综合指南
references/error-catalog.md # 所有 12 个错误 + 解决方案
references/explain-analysis.md # 阅读查询计划
references/index-strategies.md # 索引类型 & 选择
references/query-rewrites.md # 优化前后
references/performance-monitoring.md # 长期监控
references/optimization-workflow.md # 系统化流程
依赖项
PostgreSQL 扩展:
pg_stat_statements- 查询性能跟踪 (内置)pg_trgm- Trigram 相似性搜索 (可选，用于模糊匹配)
MySQL:
performance_schema- 性能监控 (8.0+ 默认启用)
无需额外依赖项。
已知问题预防
| 问题 | 症状 | 预防 |
|---|---|---|
| 顺序扫描 | 1M+ 行上的顺序扫描 | 生产前索引过滤列 |
| 缺失 FK 索引 | 慢 JOINs | 始终索引外键 |
| N+1 查询 | 1+N 数据库调用 | 使用 JOIN 或急切加载 |
| 前导通配符 | LIKE ‘%x%’ 慢 | 使用全文搜索 (GIN) |
| SELECT * 膨胀 | 高网络流量 | 选择特定列 |
| 无 LIMIT | 内存耗尽 | 始终 LIMIT 无限查询 |
| 过时统计 | 错误查询计划 | 批量操作后 ANALYZE |
| 错误索引顺序 | 索引存在但未使用 | 匹配查询模式 |
| 缺失复合索引 | 多个 WHERE 慢 | 创建复合索引 |
| 无连接池 | 高延迟 | 实现池化 (20-50 连接) |
| SQL 注入 | 安全漏洞 | 仅使用预处理语句 |
| 临时溢出 | 排序的磁盘 I/O | 增加 work_mem |
完整设置清单
生产部署:
- [ ] 启用 pg_stat_statements 或 performance_schema
- [ ] 索引所有外键列
- [ ] 索引 WHERE、JOIN、ORDER BY 子句中的列
- [ ] 用特定列替换 SELECT *
- [ ] 为所有无限查询添加 LIMIT
- [ ] 使用预处理语句 (参数化查询)
- [ ] 实现连接池 (20-50 连接)
- [ ] 配置 work_mem (每连接 256MB-1GB)
- [ ] 配置 shared_buffers (PostgreSQL RAM 的 25%)
- [ ] 启用慢查询日志 (阈值: 100-1000ms)
- [ ] 在所有关键查询上运行 EXPLAIN ANALYZE
- [ ] 设置 pg_stat_statements 的每日监控
- [ ] 夜间批量操作后调度 ANALYZE
- [ ] 监控缓存命中率 (目标: >99%)
- [ ] 每月审查并删除未使用索引
生产示例
优化前:
-- 查询: 获取用户订单
SELECT * FROM orders WHERE user_id = 123;
-- 性能:
-- 执行时间: 2500ms
-- 顺序扫描 orders (1M 行扫描)
-- 网络: 50MB 传输
-- user_id 无索引
优化后:
-- 添加索引
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- 优化查询
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123
LIMIT 100;
-- 性能:
-- 执行时间: 12ms (快 208 倍！)
-- 使用 idx_orders_user_id 的索引扫描 (100 行)
-- 网络: 50KB 传输 (少 1000 倍！)
-- 带 INCLUDE 的覆盖索引
结果: 快 208 倍的执行，少 1000 倍的数据传输
对于综合优化指导、错误解决和生产模式，加载上面“何时加载参考”中列出的适当参考文件。