返回 筑基・数据元府藏真
数据库监控与性能分析体系
博主
大约 12 分钟
数据库监控与性能分析体系
一、问题引入：数据库突然变慢
1.1 真实案例：凌晨2点的数据库告警
事故时间：凌晨2:15
事故场景：数据库CPU突然飙升到100%，业务响应超时
问题排查过程：
┌─────────────────────────────────────────────────────────────┐
│ T1 02:15 - 监控系统发出CPU告警 │
│ CPU使用率：15% → 100%（1分钟内） │
│ 活跃连接数：50 → 500+ │
│ │
│ T2 02:16 - 值班人员收到告警，开始排查 │
│ 问题：没有详细的SQL执行监控 │
│ 不知道哪个SQL导致CPU飙升 │
│ │
│ T3 02:25 - 登录数据库，查看processlist │
│ 发现大量相同SQL在执行 │
│ SELECT * FROM order WHERE create_time > '2023-01-01' │
│ │
│ T4 02:30 - 分析SQL执行计划 │
│ type: ALL（全表扫描） │
│ rows: 1000万+ │
│ Extra: Using where; Using temporary; Using filesort │
│ │
│ T5 02:35 - 紧急处理 │
│ kill掉慢查询 │
│ 添加create_time索引 │
│ │
│ T6 02:40 - 服务恢复，但已影响25分钟 │
│ 订单损失：约500单 │
│ 用户投诉：200+ │
└─────────────────────────────────────────────────────────────┘
根本原因：
1. 缺乏完善的监控体系
2. 没有慢查询预警机制
3. 缺少SQL执行计划审查
4. 没有性能基线数据
改进措施：
1. 建立全链路监控
2. 慢查询实时告警
3. 定期SQL审查
4. 性能基线测试
1.2 监控体系的重要性
数据库监控的四个层次：
┌──────────────────────────────────────────────────────────────┐
│ │
│ 1. 基础设施层 │
│ - 服务器CPU、内存、磁盘IO │
│ - 网络延迟、丢包率 │
│ - 磁盘空间使用率 │
│ │
│ 2. 数据库实例层 │
│ - QPS、TPS、连接数 │
│ - 缓存命中率、锁等待 │
│ - 复制延迟、主从状态 │
│ │
│ 3. SQL执行层 │
│ - 慢查询数量、执行时间 │
│ - SQL执行计划变化 │
│ - 事务吞吐量 │
│ │
│ 4. 业务影响层 │
│ - 业务响应时间 │
│ - 错误率、超时率 │
│ - 用户体验指标 │
│ │
└──────────────────────────────────────────────────────────────┘
二、监控指标体系
2.1 核心监控指标
| 类别 | 指标 | 说明 | 告警阈值 | 采集频率 |
|---|---|---|---|---|
| 性能 | QPS | 每秒查询数 | - | 10s |
| TPS | 每秒事务数 | - | 10s | |
| 平均响应时间 | SQL执行耗时 | >100ms | 10s | |
| 95分位响应时间 | P95延迟 | >500ms | 10s | |
| 资源 | CPU使用率 | 数据库进程CPU | >80% | 10s |
| 内存使用率 | 缓冲池使用率 | >90% | 10s | |
| 磁盘IO | IOPS、吞吐量 | >80% | 10s | |
| 磁盘空间 | 数据目录使用率 | >85% | 60s | |
| 连接 | 活跃连接数 | 正在执行的连接 | >80% | 10s |
| 总连接数 | 所有连接数 | >90% | 10s | |
| 等待连接数 | 等待线程 | >10 | 10s | |
| 连接错误数 | 连接失败次数 | >5/分钟 | 60s | |
| 复制 | 主从延迟 | 复制延迟时间 | >1s | 10s |
| 复制状态 | IO/SQL线程状态 | 非Running | 10s | |
| 复制错误 | 复制错误数 | >0 | 10s | |
| 查询 | 慢查询数 | 超过阈值的查询 | >10/分钟 | 60s |
| 全表扫描数 | 无索引查询 | >100/分钟 | 60s | |
| 临时表创建 | 内存/磁盘临时表 | >1000/分钟 | 60s | |
| 锁 | 锁等待数 | 等待锁的线程 | >10 | 10s |
| 死锁数 | 死锁发生次数 | >1/小时 | 60s | |
| 锁等待时间 | 平均等待时间 | >1s | 60s |
2.2 性能基线建立
-- 1. 收集性能基线数据
-- 创建性能基线表
CREATE TABLE performance_baseline (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
metric_name VARCHAR(100) NOT NULL,
metric_value DECIMAL(18,4),
unit VARCHAR(20),
collect_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_metric_time (metric_name, collect_time)
);
-- 2. 定期采集基线数据（存储过程）
DELIMITER //
CREATE PROCEDURE collect_baseline()
BEGIN
-- QPS
INSERT INTO performance_baseline (metric_name, metric_value, unit)
SELECT 'QPS',
(SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Queries') /
(SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Uptime'),
'queries/s';
-- 活跃连接数
INSERT INTO performance_baseline (metric_name, metric_value, unit)
SELECT 'ActiveConnections', COUNT(*), 'connections'
FROM information_schema.processlist
WHERE command != 'Sleep';
-- 缓存命中率
INSERT INTO performance_baseline (metric_name, metric_value, unit)
SELECT 'BufferPoolHitRatio',
(1 - (SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads') /
(SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_read_requests')) * 100,
'percent';
END //
DELIMITER ;
-- 3. 创建定时任务采集
CREATE EVENT baseline_collector
ON SCHEDULE EVERY 1 HOUR
DO CALL collect_baseline();
三、慢查询分析与优化
3.1 慢查询日志配置
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录无索引查询
SET GLOBAL log_slow_admin_statements = 'ON'; -- 记录慢DDL
SET GLOBAL log_slow_slave_statements = 'ON'; -- 记录复制慢查询
-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query%';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
-- 使用performance_schema分析（MySQL 5.6+）
-- 开启statement instrumentation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';
-- 查询最慢的SQL
SELECT
DIGEST_TEXT AS query,
COUNT_STAR AS exec_count,
AVG_TIMER_WAIT/1000000000000 AS avg_latency_sec,
MAX_TIMER_WAIT/1000000000000 AS max_latency_sec,
SUM_ROWS_SENT AS total_rows_sent,
SUM_ROWS_EXAMINED AS total_rows_examined,
FIRST_SEEN,
LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;
3.2 pt-query-digest分析
# 安装percona-toolkit
yum install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/lib/mysql/slow.log
# 输出到文件
pt-query-digest /var/lib/mysql/slow.log > slow_query_report.txt
# 分析最近1小时的慢查询
pt-query-digest --since='1h' /var/lib/mysql/slow.log
# 只分析SELECT语句
pt-query-digest --filter '$event->{arg} =~ m/^SELECT/i' /var/lib/mysql/slow.log
# 分析结果解读
# Rank: 排名
# Query ID: 查询指纹
# Response time: 总响应时间和占比
# Calls: 执行次数
# R/Call: 每次执行平均时间
# V/M: 响应时间方差均值比（越大越不稳定）
3.3 实时慢查询监控
/**
* 慢查询监控服务
*/
@Component
@Slf4j
public class SlowQueryMonitor {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private AlertService alertService;
/**
* 定期检查慢查询
*/
@Scheduled(fixedRate = 60000) // 每分钟检查
public void checkSlowQueries() {
String sql = "SELECT * FROM mysql.slow_log " +
"WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 MINUTE) " +
"ORDER BY query_time DESC LIMIT 10";
List<Map<String, Object>> slowQueries = jdbcTemplate.queryForList(sql);
for (Map<String, Object> query : slowQueries) {
BigDecimal queryTime = (BigDecimal) query.get("query_time");
String sqlText = (String) query.get("sql_text");
// 超过5秒的查询发送告警
if (queryTime.compareTo(new BigDecimal("5")) > 0) {
alertService.sendAlert(
"SLOW_QUERY",
String.format("发现慢查询，执行时间：%s秒\nSQL：%s",
queryTime, sqlText.substring(0, Math.min(200, sqlText.length())))
);
}
}
// 统计慢查询数量
long slowQueryCount = slowQueries.size();
if (slowQueryCount > 10) {
alertService.sendAlert(
"SLOW_QUERY_BURST",
String.format("1分钟内慢查询数量：%d，请检查数据库性能", slowQueryCount)
);
}
}
}
四、Prometheus + Grafana监控
4.1 mysqld_exporter配置
# docker-compose.yml
version: '3'
services:
mysql-exporter:
image: prom/mysqld-exporter:latest
container_name: mysql-exporter
environment:
- DATA_SOURCE_NAME="exporter:password@(mysql:3306)/"
ports:
- "9104:9104"
command:
- '--collect.global_status'
- '--collect.info_schema.innodb_metrics'
- '--collect.auto_increment.columns'
- '--collect.info_schema.processlist'
- '--collect.info_schema.tables'
- '--collect.info_schema.tablestats'
- '--collect.info_schema.userstats'
- '--collect.perf_schema.eventsstatements'
- '--collect.perf_schema.eventswaits'
- '--collect.perf_schema.file_events'
- '--collect.perf_schema.indexiowaits'
- '--collect.perf_schema.tableiowaits'
- '--collect.perf_schema.tablelocks'
prometheus:
image: prom/prometheus:latest
container_name: prometheus
volumes:
- ./prometheus.yml:/etc/prometheus/prometheus.yml
- prometheus_data:/prometheus
ports:
- "9090:9090"
grafana:
image: grafana/grafana:latest
container_name: grafana
volumes:
- grafana_data:/var/lib/grafana
ports:
- "3000:3000"
volumes:
prometheus_data:
grafana_data:
# prometheus.yml
global:
scrape_interval: 15s
evaluation_interval: 15s
scrape_configs:
- job_name: 'mysql'
static_configs:
- targets: ['mysql-exporter:9104']
metrics_path: /metrics
scrape_interval: 10s
- job_name: 'node'
static_configs:
- targets: ['node-exporter:9100']
# 告警规则
rule_files:
- /etc/prometheus/mysql_rules.yml
alerting:
alertmanagers:
- static_configs:
- targets: ['alertmanager:9093']
# mysql_rules.yml
groups:
- name: mysql_alerts
rules:
- alert: MySQLDown
expr: mysql_up == 0
for: 1m
labels:
severity: critical
annotations:
summary: "MySQL实例宕机"
description: "MySQL实例 {{ $labels.instance }} 已宕机超过1分钟"
- alert: MySQLHighConnections
expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL连接数过高"
description: "连接使用率超过80%，当前值：{{ $value }}%"
- alert: MySQLSlowQueries
expr: rate(mysql_global_status_slow_queries[5m]) > 10
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL慢查询过多"
description: "慢查询速率超过10个/秒"
- alert: MySQLReplicationLag
expr: mysql_slave_lag_seconds > 1
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL复制延迟"
description: "复制延迟超过1秒，当前值：{{ $value }}秒"
- alert: MySQLHighCPU
expr: rate(mysql_global_status_cpu_time[5m]) > 0.8
for: 5m
labels:
severity: warning
annotations:
summary: "MySQL CPU使用率高"
description: "CPU使用率超过80%"
4.2 应用层监控集成
/**
* 数据库连接池监控
*/
@Component
@Slf4j
public class ConnectionPoolMetrics {
@Autowired
private MeterRegistry meterRegistry;
@Autowired
private HikariDataSource dataSource;
@PostConstruct
public void init() {
// 注册连接池指标
Gauge.builder("db.connections.active",
dataSource.getHikariPoolMXBean(),
HikariPoolMXBean::getActiveConnections)
.description("活跃连接数")
.register(meterRegistry);
Gauge.builder("db.connections.idle",
dataSource.getHikariPoolMXBean(),
HikariPoolMXBean::getIdleConnections)
.description("空闲连接数")
.register(meterRegistry);
Gauge.builder("db.connections.total",
dataSource.getHikariPoolMXBean(),
HikariPoolMXBean::getTotalConnections)
.description("总连接数")
.register(meterRegistry);
Gauge.builder("db.connections.waiting",
dataSource.getHikariPoolMXBean(),
HikariPoolMXBean::getThreadsAwaitingConnection)
.description("等待连接数")
.register(meterRegistry);
}
}
/**
* SQL执行监控拦截器
*/
@Component
@Intercepts({
@Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "update",
args = {MappedStatement.class, Object.class})
})
@Slf4j
public class SqlMetricsInterceptor implements Interceptor {
@Autowired
private MeterRegistry meterRegistry;
@Override
public Object intercept(Invocation invocation) throws Throwable {
long startTime = System.currentTimeMillis();
try {
return invocation.proceed();
} finally {
long duration = System.currentTimeMillis() - startTime;
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
String sqlId = mappedStatement.getId();
String sqlType = mappedStatement.getSqlCommandType().name();
// 记录SQL执行时间
meterRegistry.timer("db.query.time",
"sql_id", sqlId,
"sql_type", sqlType)
.record(duration, TimeUnit.MILLISECONDS);
// 慢查询记录
if (duration > 1000) {
log.warn("Slow query detected: {} took {}ms", sqlId, duration);
}
}
}
}
五、性能分析工具
5.1 EXPLAIN分析
-- 基础EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 10001;
-- 详细分析
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10001;
-- JSON格式输出
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 10001;
-- 树形格式（MySQL 8.0.16+）
EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE user_id = 10001;
-- 关键字段解读
-- id: 执行顺序标识
-- select_type: 查询类型（SIMPLE/PRIMARY/SUBQUERY/DERIVED）
-- table: 访问的表
-- partitions: 使用的分区
-- type: 访问类型（system > const > eq_ref > ref > range > index > ALL）
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 索引长度
-- ref: 与索引比较的列
-- rows: 扫描行数估计
-- filtered: 过滤比例
-- Extra: 额外信息
-- 优化案例
-- 问题SQL
EXPLAIN SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.create_time > '2024-01-01' AND o.status = 1;
-- 优化后：添加复合索引
ALTER TABLE orders ADD INDEX idx_create_time_status (create_time, status);
5.2 性能诊断流程
数据库性能诊断流程：
┌─────────────────────────────────────────────────────────────┐
│ 1. 确认问题现象 │
│ - 响应时间变慢？ │
│ - 连接数暴增？ │
│ - CPU/内存飙升？ │
└────────────────┬────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 2. 查看实时监控 │
│ - show processlist │
│ - show engine innodb status │
│ - performance_schema │
└────────────────┬────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 3. 定位问题SQL │
│ - 慢查询日志 │
│ - performance_schema.events_statements_current │
│ - sys.statement_analysis │
└────────────────┬────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 4. 分析执行计划 │
│ - EXPLAIN │
│ - 是否使用索引 │
│ - 扫描行数是否合理 │
└────────────────┬────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 5. 制定优化方案 │
│ - 添加/优化索引 │
│ - SQL改写 │
│ - 架构调整（读写分离等） │
└────────────────┬────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ 6. 验证优化效果 │
│ - 对比执行计划 │
│ - 监控响应时间 │
│ - 观察资源使用 │
└─────────────────────────────────────────────────────────────┘
六、经验总结与最佳实践
6.1 监控体系搭建清单
┌─────────────────────────────────────────────────────────────────────┐
│ 数据库监控体系搭建清单 │
├─────────────────────────────────────────────────────────────────────┤
│ │
│ 【基础监控】 │
│ □ 1. 部署Prometheus + Grafana │
│ □ 2. 配置mysqld_exporter采集指标 │
│ □ 3. 配置Node Exporter监控服务器 │
│ □ 4. 配置Alertmanager告警通知 │
│ │
│ 【告警配置】 │
│ □ 1. 实例存活告警 │
│ □ 2. 连接数告警（>80%） │
│ □ 3. 慢查询告警（>1秒） │
│ □ 4. 复制延迟告警（>1秒） │
│ □ 5. 磁盘空间告警（>85%） │
│ □ 6. 内存使用告警（>90%） │
│ │
│ 【应用监控】 │
│ □ 1. 集成Micrometer指标采集 │
│ □ 2. 监控连接池状态 │
│ □ 3. 记录SQL执行时间 │
│ □ 4. 监控事务吞吐量 │
│ │
│ 【日志分析】 │
│ □ 1. 开启慢查询日志 │
│ □ 2. 定期分析慢查询 │
│ □ 3. 配置错误日志监控 │
│ │
│ 【容量规划】 │
│ □ 1. 建立性能基线 │
│ □ 2. 定期容量评估 │
│ □ 3. 预测增长趋势 │
│ │
└─────────────────────────────────────────────────────────────────────┘
6.2 常见错误
| 错误 | 后果 | 正确做法 |
|---|---|---|
| 无监控上线 | 问题发现滞后 | 监控先于业务上线 |
| 忽略慢查询 | 性能逐渐恶化 | 每日分析慢查询 |
| 告警过多 | 告警疲劳，重要告警被忽略 | 合理设置告警阈值 |
| 只监控不分析 | 监控数据无价值 | 定期分析趋势 |
| 无恢复预案 | 故障处理混乱 | 制定详细预案 |
系列上一篇：时序数据处理与分析
系列下一篇：备份恢复与灾难恢复策略
知识点测试
读完文章了？来测试一下你对知识点的掌握程度吧！
评论区
使用 GitHub 账号登录后即可发表评论，支持 Markdown 格式。
如果评论系统无法加载，请确保：
- 您的网络可以访问 GitHub
- giscus GitHub App 已安装到仓库
- 仓库已启用 Discussions 功能