sql-query-optimization

SQL 查询优化

状态：已可用于生产环境 ✅ 最后更新：2025-12-15 最新版本：PostgreSQL 17、MySQL 8.4 依赖项：无

---

快速入门（10 分钟）

1. 找出慢查询

-- PostgreSQL: Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
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;

-- Look for:
-- - Seq Scan on large tables → needs index
-- - High "Rows Removed by Filter" → poor selectivity
-- - Temp read/written → increase work_mem

3. 创建索引

-- Add missing index
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);

-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- Execution time should drop 10-100x

---

关键规则

务必做到 ✓

规则	原因	示例
为外键创建索引	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. 大表上的顺序扫描

症状：在 100 万行以上的表上出现 Seq Scan on orders (cost=0.00..150000.00) 原因：筛选列上没有索引 修复方法：CREATE INDEX idx_orders_column ON orders(column) 影响：速度提升 10–100 倍

2. 外键缺少索引

症状：JOIN 速度慢（5 秒以上） 原因：外键列没有建立索引 修复方法：CREATE INDEX idx_orders_user_id ON orders(user_id) 影响：JOIN 速度提升 50–500 倍

3. N+1 查询问题

症状：N 条记录执行 1 + 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 索引）或三元组 影响：速度提升 100–1000 倍

5. 在生产环境中使用 SELECT *

症状：网络流量高、响应慢 原因：获取全部 50 列，而实际只需要 3 列 修复方法：SELECT id, name, email（显式列出列名） 影响：数据传输量减少 90%

6. 大量结果缺少 LIMIT

症状：服务器内存耗尽、查询超时 原因：尝试返回 500 万行 修复方法：SELECT * FROM logs WHERE ... LIMIT 100，并添加分页 影响：内存使用量保持恒定

7. 批量加载后统计信息过时

症状：即使有索引，也选择了错误的查询计划 原因：PostgreSQL 统计信息已过时 修复方法：批量操作后运行 ANALYZE table_name 影响：正确选择查询计划

所有 12 个错误及详细解决方案，请参阅 references/error-catalog.md。

---

常见模式摘要

模式	使用场景	示例	性能
B-Tree 索引	等值、范围和排序查询	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	时间复杂度为 O(1)，而非 O(n)

---

配置摘要

PostgreSQL 配置

-- Increase work_mem for complex queries (reloadable - no restart needed)
SET work_mem = '256MB';

-- Increase shared_buffers for better caching (25% of RAM)
ALTER SYSTEM SET shared_buffers = '8GB';

-- IMPORTANT: shared_buffers requires a full PostgreSQL server restart!
-- This setting is NOT reloadable via pg_reload_conf()
--
-- To apply shared_buffers change:
-- 1. Stop PostgreSQL:   sudo systemctl stop postgresql
-- 2. Start PostgreSQL:  sudo systemctl start postgresql
-- OR use:              sudo systemctl restart postgresql
--
-- Verify the change took effect:
-- SHOW shared_buffers;

-- Enable auto-vacuum (reloadable - can use pg_reload_conf)
ALTER SYSTEM SET autovacuum = on;

-- Reload config (ONLY works for parameters that don't require restart)
-- This will NOT reload shared_buffers - restart required for that!
SELECT pg_reload_conf();

MySQL 配置

# my.cnf
[mysqld]
innodb_buffer_pool_size = 8G  # 70% of RAM
max_connections = 500
slow_query_log = 1
long_query_time = 1

---

何时加载参考资料

性能分析：

• 在阅读 EXPLAIN 输出、理解查询计划、分析缓冲区统计信息、比较 PostgreSQL 与 MySQL 的 EXPLAIN 时，加载 references/explain-analysis.md
• 在设置监控、长期跟踪慢查询、监控缓存命中率、识别膨胀表时，加载 references/performance-monitoring.md

索引优化：

• 在选择索引类型（B-Tree、GIN、GiST、Hash）、创建复合索引、确定列顺序、使用覆盖索引、实现部分索引、监控索引使用情况时，加载 references/index-strategies.md

查询优化：

• 在重写慢查询、将子查询转换为 JOIN、消除 N+1 查询、实现分页、优化 LIKE 查询、批量执行操作时，加载 references/query-rewrites.md

系统化流程：

• 在遵循分步优化流程、提出优化假设、衡量改进效果、监控长期性能时，加载 references/optimization-workflow.md

错误排查：

• 在调试特定错误（顺序扫描、缺少索引、N+1 查询等）、了解根本原因、实施经过验证的解决方案时，加载 references/error-catalog.md

---

使用捆绑资源

模板（复制粘贴 SQL）

# EXPLAIN query templates
templates/explain-query.sql

# Index creation patterns
templates/index-examples.sql

# Query rewrite examples
templates/query-rewrites.sql

# Monitoring queries
templates/monitoring-queries.sql

参考资料（深入探讨）

# Comprehensive guides
references/error-catalog.md              # All 12 errors + solutions
references/explain-analysis.md           # Reading query plans
references/index-strategies.md           # Index types & selection
references/query-rewrites.md             # Before/after optimizations
references/performance-monitoring.md     # Long-term monitoring
references/optimization-workflow.md      # Systematic process

---

依赖项

PostgreSQL 扩展：

• pg_stat_statements - 查询性能跟踪（内置）
• pg_trgm - 三元组相似度搜索（可选，用于模糊匹配）

MySQL：

• performance_schema - 性能监控（在 8.0+ 中默认启用）

无需其他依赖项。

---

预防已知问题

问题	症状	预防措施
顺序扫描	对 1M+ 行执行 Seq Scan	在上线前为筛选列创建索引
缺少 FK 索引	JOIN 缓慢	始终为外键创建索引
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%）
• 每月检查并删除未使用的索引

---

生产环境示例

优化前：

-- Query: Fetch user orders
SELECT * FROM orders WHERE user_id = 123;

-- Performance:
-- Execution time: 2500ms
-- Seq Scan on orders (1M rows scanned)
-- Network: 50MB transferred
-- No index on user_id

优化后：

-- Add index
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Optimize query
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123
LIMIT 100;

-- Performance:
-- Execution time: 12ms (208x faster!)
-- Index Scan using idx_orders_user_id (100 rows)
-- Network: 50KB transferred (1000x less!)
-- Covering index with INCLUDE

结果：执行速度提升 208 倍，数据传输量减少 1000 倍

---

如需全面的优化指导、错误解决方案和生产环境模式，请加载上方“何时加载参考资料”中列出的适当参考文件。