数据库性能调试
概述
数据库性能问题直接影响应用的响应速度。调试重点在于识别慢查询和优化执行计划。
何时使用
- 应用响应速度慢
- 数据库CPU高
- 已识别的慢查询
- 性能回归
- 负载压力下
指南
1. 识别慢查询
-- 启用慢查询日志（MySQL）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
-- 查看慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SELECT * FROM mysql.slow_log;
-- PostgreSQL慢查询
CREATE EXTENSION pg_stat_statements;
SELECT mean_exec_time, calls, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;
-- SQL Server慢查询
SELECT TOP 10
execution_count,
total_elapsed_time,
statement_text
FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC;
-- 查询分析
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;
-- 慢：Seq Scan（全表扫描）
-- 快：Index Scan
2. 常见问题与解决方案
问题：N+1查询问题
症状：1000条记录导致1001次查询
示例（Python）：
for user in users:
posts = db.query(Post).filter(Post.user_id == user.id)
# 1 + 1000次查询
解决方案：
users = db.query(User).options(joinedload(User.posts))
# 单次查询带JOIN
---
问题：缺少索引
症状：Seq Scan而不是Index Scan
解决方案：
CREATE INDEX idx_orders_user_id ON orders(user_id);
验证：EXPLAIN ANALYZE现在显示Index Scan
---
问题：低效JOIN
之前：
SELECT * FROM orders o, users u
WHERE o.user_id = u.id AND u.email LIKE '%@example.com'
# 差：对每个订单扫描users表
之后：
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'exact@example.com'
# 好：单个电子邮件查找
---
问题：大表扫描
症状：SELECT * FROM large_table（100万行）
解决方案：
1. 添加LIMIT子句
2. 添加WHERE条件
3. 选择特定列
4. 使用分页
5. 归档旧数据
---
问题：慢聚合
之前（1分钟）：
SELECT user_id, COUNT(*), SUM(amount)
FROM transactions
GROUP BY user_id
之后（50毫秒）：
SELECT user_id, transaction_count, total_amount
FROM user_transaction_stats
WHERE updated_at > NOW() - INTERVAL 1 DAY
# 物化视图或聚合表
3. 执行计划分析
EXPLAIN输出理解：
Seq Scan（全表扫描）：
- 读取整个表
- 最慢的方法
- 修复：添加索引
Index Scan：
- 使用索引
- 快速
- 理想
Bitmap Index Scan：
- 部分索引扫描
- 转换为堆扫描
- 中等速度
Nested Loop：
- 对于左边的每一行，扫描右边
- O(n*m)复杂度
- 对于大表慢
Hash Join：
- 构建较小表的哈希表
- 用较大的表探测
- 比嵌套循环快
Merge Join：
- 排序两个表，合并
- 对于大型排序数据最快
- 需要排序操作
---
阅读EXPLAIN ANALYZE：
节点：Seq Scan on orders （实际8023.456毫秒）
- Seq Scan = 全表扫描
- 实际时间 = 实际执行时间
- 8023毫秒 = 太慢了
行：1000000（估计）1000000（实际）
- 匹配 = 计划器准确
- 不匹配 = 更新统计信息
节点：Index Scan （实际15.234毫秒）
- Index Scan = 快速
- 15毫秒 = 可接受
4. 调试过程
步骤：
1. 识别慢查询
- 启用慢查询日志
- 运行工作负载
- 查看慢日志
- 记录执行时间
2. 使用EXPLAIN分析
- 运行EXPLAIN ANALYZE
- 查找Seq Scan
- 检查估计与实际行数
- 查看连接方法
3. 找到根本原因
- 缺少索引？
- 低效连接？
- 缺少WHERE子句？
- 过时的统计数据？
4. 尝试修复
- 添加索引
- 重写查询
- 更新统计数据
- 归档旧数据
5. 测量改进
- 修复后运行查询
- 比较执行时间
- 之前：5000毫秒
- 之后：100毫秒（快50倍！）
6. 监控
- 跟踪慢查询
- 设置基线
- 回归时警报
- 定期审查
---
清单：
[ ] 已识别并记录慢查询
[ ] 运行EXPLAIN ANALYZE
[ ] 分析估计与实际行数
[ ] 识别Seq Scans
[ ] 检查索引
[ ] 查看连接策略
[ ] 更新统计数据
[ ] 如有需要重写查询
[ ] 如有需要创建索引
[ ] 验证修复
[ ] 建立性能基线
[ ] 配置监控
[ ] 为团队记录
关键点
- 在生产中启用慢查询日志
- 使用EXPLAIN ANALYZE进行调查
- 查找Seq Scan = 缺少索引
- 在WHERE/JOIN列上添加索引
- 监控查询统计数据
- 定期更新表统计数据
- 重写查询以避免低效
- 对于大型结果集使用分页
- 优化前后测量
- 跟踪慢查询趋势