数据库查询优化
概览
慢速数据库查询是常见的性能瓶颈。通过索引、高效查询和执行计划分析进行优化，可以显著提高应用程序性能。
何时使用
- 响应时间慢
- 数据库CPU使用率高
- 性能回归
- 新功能部署
- 定期维护
指令
1. 查询分析
-- 分析查询性能
EXPLAIN ANALYZE
SELECT users.id, users.name, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE users.created_at > '2024-01-01'
GROUP BY users.id, users.name
ORDER BY order_count DESC;
-- 结果显示：
-- - 顺序扫描（慢）与索引扫描（快）
-- - 行数：实际与计划（高方差=不好）
-- - 执行时间（毫秒）
-- 关键指标：
-- - 顺序扫描：全表读取（慢）
-- - 索引扫描：使用索引（快）
-- - 嵌套循环：带有循环的连接
-- - 排序：内存或磁盘排序
2. 索引策略
索引类型：
单列：
CREATE INDEX idx_users_email ON users(email);
使用：WHERE email = ?
大小：小，快速创建
复合索引：
CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at);
使用：WHERE user_id = ? AND created_at > ?
顺序：选择性最大的在前
覆盖索引：
CREATE INDEX idx_orders_covering
ON orders(user_id) INCLUDE (total_amount);
好处：不需要表查找
部分索引：
CREATE INDEX idx_active_users
ON users(id) WHERE status = 'active';
好处：更小，更快
全文索引：
CREATE FULLTEXT INDEX idx_search
ON articles(title, content);
使用：文本搜索查询
---
索引规则：
- 为WHERE条件创建索引
- 为JOIN列创建索引
- 为ORDER BY创建索引
- 不要过度索引（减慢写入）
- 监控索引使用情况
- 移除未使用的索引
- 定期更新统计信息
- 为过滤查询创建部分索引
缺失索引查询：
SELECT object_name, equality_columns
FROM sys.dm_db_missing_index_details
ORDER BY equality_columns;
3. 查询优化技术
# 常见优化模式
# 之前（N+1查询）
for user in users:
orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
# 1 + N查询
# 之后（单查询与JOIN）
orders = db.query("""
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > ?
""", date_threshold)
# 之前（低效WHERE）
SELECT * FROM users
WHERE LOWER(email) = LOWER('Test@Example.com')
# 不能使用索引（使用了函数）
# 之后（索引友好）
SELECT * FROM users
WHERE email = 'test@example.com'
# 案例不敏感约束+索引
# 之前（通配符在开始）
SELECT * FROM users WHERE email LIKE '%example.com'
# 不能使用索引（通配符在开始）
# 之后（通配符在结束）
SELECT * FROM users WHERE email LIKE 'user%'
# 可以使用索引
# 之前（慢速聚合）
SELECT user_id, COUNT(*) as cnt
FROM orders
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10
# 之后（预聚合）
SELECT user_id, order_count
FROM user_order_stats
WHERE order_count IS NOT NULL
ORDER BY order_count DESC
LIMIT 10
4. 优化清单
分析：
[ ] 运行EXPLAIN ANALYZE在慢查询上
[ ] 检查实际与估计行数
[ ] 查找顺序扫描
[ ] 识别昂贵操作
[ ] 比较执行计划
索引：
[ ] 索引WHERE列
[ ] 索引JOIN列
[ ] 索引ORDER BY列
[ ] 检查未使用索引
[ ] 移除重复索引
[ ] 策略性创建复合索引
[ ] 分析索引统计信息
查询优化：
[ ] 移除不必要的列（SELECT *）
[ ] 使用JOIN代替子查询
[ ] 避免WHERE中使用函数
[ ] 谨慎使用通配符（避免%）
[ ] 批量操作
[ ] 使用LIMIT对结果集
[ ] 归档旧数据
缓存：
[ ] 实施查询缓存
[ ] 缓存聚合
[ ] 使用Redis进行热数据缓存
[ ] 策略性失效
监控：
[ ] 跟踪慢查询
[ ] 监控索引使用情况
[ ] 设置警报
[ ] 定期更新统计信息
[ ] 测量改进
---
预期改进：
通过适当的索引：
- 顺序扫描 → 索引扫描
- 响应时间：5秒 → 50ms（100倍速度提升）
- CPU使用率：80% → 20%
- 并发用户：100 → 1000
快速胜利：
- 对频繁过滤的列添加索引
- 修复N+1查询
- 对大型结果集使用LIMIT
- 归档旧数据
- 预期：20-50%的改进
关键点
- EXPLAIN ANALYZE显示查询执行
- 索引必须匹配WHERE/JOIN/ORDER BY
- 避免WHERE子句中的函数
- 修复N+1查询（连接代替循环）
- 定期监控慢查询日志
- 需要更新统计信息以确保准确性
- 预先计算聚合
- 归档历史数据
- 使用explain计划前后
- 持续测量和监控