印刻数据库

外观

MySQL 慢查询指标 ​

核心慢查询指标 ​

查询执行时间 ​

指标定义 ​

  • query_time：查询执行的总时间（秒）
  • avg_query_time：平均查询执行时间
  • max_query_time：最大查询执行时间
  • min_query_time：最小查询执行时间
  • query_time_distribution：查询执行时间分布

收集方法 ​

sql
-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log%';

-- 设置慢查询阈值
SET GLOBAL long_query_time = 0.5;

-- 查看当前慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

监控策略 ​

  • 设置合理的阈值
  • 监控查询时间趋势
  • 对超过阈值的查询进行告警

锁等待时间 ​

指标定义 ​

  • lock_time：查询执行过程中的锁等待时间（秒）
  • lock_time_ratio：锁等待时间占总执行时间的比例
  • lock_wait_count：锁等待次数
  • lock_wait_avg_time：平均锁等待时间

收集方法 ​

sql
-- 查看InnoDB锁等待信息
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

-- 查看锁等待详情
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

监控策略 ​

  • 监控锁等待时间变化
  • 分析锁等待原因
  • 对长时间锁等待进行告警

扫描行数与返回行数 ​

指标定义 ​

  • rows_examined：查询扫描的行数
  • rows_sent：查询返回的行数
  • rows_examined_ratio：扫描行数与返回行数的比例
  • full_scan_count：全表扫描次数

收集方法 ​

sql
-- 从慢查询日志中获取
-- 或从performance_schema中获取
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY sum_rows_examined DESC;

监控策略 ​

  • 监控扫描行数与返回行数的比例
  • 识别全表扫描的查询
  • 对扫描行数过多的查询进行优化

执行频率 ​

指标定义 ​

  • query_count：查询执行次数
  • query_frequency：查询执行频率（次/秒）
  • top_queries：执行频率最高的查询

收集方法 ​

sql
-- 从performance_schema中获取
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY COUNT_STAR DESC;

监控策略 ​

  • 监控高频查询的性能
  • 对高频慢查询进行重点优化
  • 分析查询频率变化趋势

索引使用情况 ​

指标定义 ​

  • index_used：是否使用索引
  • index_name：使用的索引名称
  • index_scan_count：索引扫描次数
  • full_table_scan_count：全表扫描次数

收集方法 ​

sql
-- 查看未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 从performance_schema中获取
SELECT DIGEST_TEXT, SUM_NO_INDEX_USED, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
AND SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC;

监控策略 ​

  • 监控未使用索引的查询
  • 分析索引使用效率
  • 为高频全表扫描查询添加合适的索引

扩展慢查询指标 ​

内存使用 ​

指标定义 ​

  • memory_used：查询使用的内存量
  • tmp_table_count：使用临时表的次数
  • tmp_table_on_disk_count：使用磁盘临时表的次数

收集方法 ​

sql
-- 查看临时表使用情况
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- 从performance_schema中获取
SELECT DIGEST_TEXT, SUM_CREATED_TMP_TABLES, SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC;

监控策略 ​

  • 监控临时表使用情况
  • 对使用磁盘临时表的查询进行优化
  • 调整内存参数以减少临时表使用

网络传输 ​

指标定义 ​

  • bytes_sent：查询返回的字节数
  • bytes_received：查询接收的字节数
  • network_latency：网络延迟

收集方法 ​

sql
-- 查看网络传输情况
SHOW GLOBAL STATUS LIKE 'Bytes%';

-- 从performance_schema中获取
SELECT DIGEST_TEXT, SUM_BYTES_SENT, SUM_BYTES_RECEIVED
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY SUM_BYTES_SENT DESC;

监控策略 ​

  • 监控大结果集查询
  • 优化网络传输效率
  • 考虑使用查询结果缓存

解析时间 ​

指标定义 ​

  • parse_time：查询解析时间
  • compile_time：查询编译时间
  • execute_time：查询执行时间

收集方法 ​

sql
-- 从performance_schema中获取
SELECT DIGEST_TEXT, SUM_PARSING_TIME, SUM_COMPILATION_TIME, SUM_EXECUTION_TIME
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY SUM_PARSING_TIME + SUM_COMPILATION_TIME DESC;

监控策略 ​

  • 监控查询解析和编译时间
  • 优化复杂查询的解析效率
  • 考虑使用预处理语句

子查询性能 ​

指标定义 ​

  • subquery_count：子查询数量
  • subquery_execution_time：子查询执行时间
  • correlated_subquery_count：相关子查询数量

收集方法 ​

sql
-- 分析查询执行计划
EXPLAIN SELECT * FROM table WHERE id IN (SELECT id FROM sub_table);

-- 从performance_schema中获取复杂查询
SELECT DIGEST_TEXT, SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
AND DIGEST_TEXT LIKE '%SELECT%(%SELECT%'%'
ORDER BY SUM_TIMER_WAIT DESC;

监控策略 ​

  • 监控子查询性能
  • 优化相关子查询
  • 考虑使用JOIN替代子查询

指标收集方法 ​

慢查询日志 ​

配置 ​

ini
[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 设置慢查询阈值
long_query_time = 0.5

# 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

# 记录管理语句
log_slow_admin_statements = 1

分析工具 ​

  • mysqldumpslow：MySQL自带工具
  • pt-query-digest：Percona Toolkit工具
  • MySQL Enterprise Monitor：企业级监控工具

Performance Schema ​

启用 ​

sql
-- 启用Performance Schema
SET GLOBAL performance_schema = 'ON';

-- 验证启用状态
SHOW VARIABLES LIKE 'performance_schema';

相关表 ​

  • events_statements_summary_by_digest：按语句摘要汇总的语句事件
  • events_statements_history：语句事件历史
  • events_statements_history_long：长语句事件历史

查询示例 ​

sql
-- 查看执行时间最长的查询
SELECT DIGEST_TEXT, SUM_TIMER_WAIT/1000000000 as sum_time_sec,
       AVG_TIMER_WAIT/1000000000 as avg_time_sec,
       COUNT_STAR as exec_count
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY sum_time_sec DESC
LIMIT 10;

Sys Schema ​

简介 ​

  • 基于Performance Schema的视图集合
  • 提供更友好的查询接口
  • 简化性能数据收集

相关视图 ​

  • sys.statements_with_runtimes_in_95th_percentile：95百分位运行时间的语句
  • sys.statements_with_full_table_scans：使用全表扫描的语句
  • sys.statements_with_temp_tables：使用临时表的语句

查询示例 ​

sql
-- 查看慢查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
WHERE schema_name = 'your_database'
ORDER BY avg_execution_time DESC;

-- 查看使用全表扫描的查询
SELECT * FROM sys.statements_with_full_table_scans
WHERE schema_name = 'your_database'
ORDER BY rows_examined DESC;

第三方监控工具 ​

Prometheus + MySQL Exporter ​

  • 开源监控解决方案
  • 支持MySQL指标收集
  • 提供时序数据存储
  • 与Grafana集成实现可视化

MySQL Enterprise Monitor ​

  • 企业级监控工具
  • 提供预定义的慢查询监控面板
  • 支持自动告警
  • 集成到MySQL Enterprise Edition

Zabbix ​

  • 综合监控系统
  • 支持MySQL监控模板
  • 可自定义慢查询监控项
  • 提供丰富的告警机制

监控策略 ​

指标阈值设置 ​

基于业务需求 ​

  • OLTP系统：阈值较低（如0.1秒）
  • OLAP系统：阈值较高（如5秒）
  • 混合系统：根据查询类型设置不同阈值

基于历史数据 ​

  • 分析历史查询执行时间分布
  • 建立性能基线
  • 根据基线设置合理阈值

动态调整 ​

  • 高峰期和低谷期设置不同阈值
  • 根据系统负载动态调整
  • 定期评估和更新阈值

监控频率 ​

实时监控 ​

  • 对关键业务查询进行实时监控
  • 配置实时告警
  • 快速响应性能问题

定期分析 ​

  • 每日慢查询分析
  • 每周性能报告
  • 每月趋势分析

长期趋势 ​

  • 建立长期性能趋势数据
  • 预测性能变化
  • 制定容量规划

告警策略 ​

告警级别 ​

  • 严重：查询执行时间超过阈值5倍
  • 警告：查询执行时间超过阈值
  • 提示：查询执行时间接近阈值

告警方式 ​

  • 邮件：详细的性能报告
  • 短信：紧急告警
  • 即时通讯：实时通知
  • 监控平台：集中展示

告警抑制 ​

  • 避免告警风暴
  • 实现告警聚合
  • 工作时间和非工作时间采用不同策略

指标分析方法 ​

单指标分析 ​

分析步骤 ​

  1. 收集指标数据
  2. 与历史数据对比
  3. 与基线对比
  4. 识别异常值
  5. 分析异常原因

示例 ​

sql
-- 查看慢查询数量趋势
SELECT 
  DATE_FORMAT(event_time, '%Y-%m-%d %H:00:00') as time_slot,
  COUNT(*) as slow_query_count
FROM mysql.slow_log
WHERE start_time >= NOW() - INTERVAL 24 HOUR
GROUP BY time_slot
ORDER BY time_slot;

多指标关联分析 ​

分析步骤 ​

  1. 选择相关指标
  2. 建立关联关系
  3. 分析指标间的相关性
  4. 识别综合性能问题

示例 ​

sql
-- 分析扫描行数与执行时间的关系
SELECT 
  DIGEST_TEXT,
  SUM_TIMER_WAIT/1000000000 as sum_time_sec,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_ROWS_EXAMINED / SUM_ROWS_SENT as scan_to_send_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
AND SUM_ROWS_SENT > 0
ORDER BY sum_time_sec DESC
LIMIT 10;

趋势分析 ​

分析步骤 ​

  1. 收集一段时间的指标数据
  2. 绘制趋势图表
  3. 识别趋势变化点
  4. 分析变化原因

示例 ​

sql
-- 分析慢查询数量趋势
SELECT 
  DATE_FORMAT(event_time, '%Y-%m-%d') as date,
  COUNT(*) as slow_query_count
FROM mysql.slow_log
WHERE start_time >= NOW() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date;

基准对比分析 ​

分析步骤 ​

  1. 建立性能基线
  2. 定期与基线对比
  3. 分析偏差原因
  4. 调整基线

示例 ​

sql
-- 建立基线
CREATE TABLE performance_baseline (
  metric_name VARCHAR(100),
  baseline_value FLOAT,
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入基线数据
INSERT INTO performance_baseline (metric_name, baseline_value)
VALUES ('avg_query_time', 0.1),
       ('max_query_time', 2.0),
       ('slow_query_count', 100);

-- 与基线对比
SELECT 
  'avg_query_time' as metric_name,
  AVG(query_time) as current_value,
  (SELECT baseline_value FROM performance_baseline WHERE metric_name = 'avg_query_time') as baseline_value,
  (AVG(query_time) / (SELECT baseline_value FROM performance_baseline WHERE metric_name = 'avg_query_time') - 1) * 100 as deviation_percent
FROM mysql.slow_log
WHERE start_time >= NOW() - INTERVAL 1 HOUR;

优化建议 ​

基于执行时间的优化 ​

优化策略 ​

  • 添加索引：为WHERE条件和JOIN条件添加合适的索引
  • 优化SQL：重写复杂查询，分解大查询
  • 调整参数：优化MySQL配置参数
  • 硬件升级：增加CPU、内存、SSD等硬件资源

示例 ​

sql
-- 原查询（无索引）
SELECT * FROM users WHERE last_name = 'Smith';

-- 优化后（添加索引）
CREATE INDEX idx_last_name ON users(last_name);
SELECT * FROM users WHERE last_name = 'Smith';

基于锁等待的优化 ​

优化策略 ​

  • 减少事务范围：缩短事务持有锁的时间
  • 优化锁粒度：使用行级锁替代表级锁
  • 合理的索引：减少锁冲突
  • 并发控制：使用乐观锁或悲观锁

示例 ​

sql
-- 原查询（长事务）
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 其他操作...
COMMIT;

-- 优化后（短事务）
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 其他操作...

基于扫描行数的优化 ​

优化策略 ​

  • 添加索引：减少全表扫描
  • 优化WHERE条件：使用索引友好的查询条件
  • 限制结果集：使用LIMIT限制返回行数
  • **避免SELECT ***：只选择需要的列

示例 ​

sql
-- 原查询（全表扫描）
SELECT * FROM orders WHERE order_date > '2023-01-01';

-- 优化后（添加索引）
CREATE INDEX idx_order_date ON orders(order_date);
SELECT order_id, customer_id, total FROM orders WHERE order_date > '2023-01-01' LIMIT 100;

基于执行频率的优化 ​

优化策略 ​

  • 缓存结果：使用查询缓存或应用层缓存
  • 批量操作：将多个单条操作合并为批量操作
  • 预处理语句：使用预处理语句减少解析开销
  • 代码优化：减少应用程序中的重复查询

示例 ​

sql
-- 原查询（多次执行）
for user_id in user_ids:
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

-- 优化后（批量查询）
cursor.execute("SELECT * FROM users WHERE id IN (%s)", (','.join(map(str, user_ids)),))

基于索引使用的优化 ​

优化策略 ​

  • 添加合适的索引：为常用查询添加索引
  • 优化索引结构：使用复合索引，优化索引顺序
  • 避免索引失效：避免在WHERE条件中使用函数
  • 定期维护索引：重建碎片化索引

示例 ​

sql
-- 原查询（索引失效）
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 优化后（使用索引）
CREATE INDEX idx_created_at ON users(created_at);
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

最佳实践 ​

指标收集最佳实践 ​

全面性 ​

  • 收集多维度的慢查询指标
  • 覆盖所有类型的SQL操作
  • 包括执行计划信息

及时性 ​

  • 实时收集关键指标
  • 定期汇总分析
  • 及时发现性能问题

准确性 ​

  • 使用精确的时间戳
  • 避免指标数据丢失
  • 确保数据一致性

指标监控最佳实践 ​

可视化 ​

  • 使用图表展示性能趋势
  • 建立监控仪表板
  • 突出显示异常指标

自动化 ​

  • 自动化指标收集
  • 自动化告警
  • 自动化分析报告

集成性 ​

  • 与其他监控系统集成
  • 与CI/CD流程集成
  • 与日志分析系统集成

指标分析最佳实践 ​

上下文分析 ​

  • 结合系统负载分析
  • 结合业务场景分析
  • 结合代码变更分析

根因分析 ​

  • 深入分析性能问题的根本原因
  • 避免只处理表面现象
  • 提出系统性的解决方案

持续改进 ​

  • 建立性能改进闭环
  • 跟踪优化措施的效果
  • 持续调整优化策略

常见问题（FAQ） ​

Q1: 如何设置合理的慢查询阈值？ ​

A1: 设置慢查询阈值应考虑：

  • 业务需求：不同业务对响应时间的要求不同
  • 系统配置：硬件性能影响查询执行时间
  • 查询类型：OLTP和OLAP查询设置不同阈值
  • 历史数据：分析历史查询执行时间分布
  • 建议：从小值开始（如0.1秒），根据实际情况调整

Q2: 慢查询指标监控会影响MySQL性能吗？ ​

A2: 监控慢查询指标会对MySQL性能产生一定影响：

  • 慢查询日志：额外的I/O操作
  • Performance Schema：额外的内存和CPU开销
  • 影响程度：取决于监控的详细程度和频率
  • 建议：
    • 合理设置慢查询阈值
    • 对Performance Schema进行适当配置
    • 生产环境中选择合适的监控策略

Q3: 如何处理大量的慢查询数据？ ​

A3: 处理大量慢查询数据的方法：

  • 数据聚合：使用工具对慢查询数据进行聚合分析
  • 数据轮转：定期轮转慢查询日志
  • 数据归档：将历史数据归档到外部存储
  • 智能分析：使用机器学习等技术识别重要的慢查询
  • 重点关注：优先分析执行时间长、执行频率高的查询

Q4: 如何区分慢查询和正常查询的边界？ ​

A4: 区分方法：

  • 基于业务SLA：根据服务级别协议确定
  • 基于历史数据：分析查询执行时间分布
  • 基于资源使用：考虑CPU、I/O等资源消耗
  • 基于用户体验：考虑用户感知的响应时间
  • 动态调整：根据系统负载和业务情况动态调整阈值

Q5: 如何将慢查询指标与其他监控指标关联分析？ ​

A5: 关联分析方法：

  • 系统指标：结合CPU、内存、磁盘I/O等指标
  • MySQL指标：结合连接数、缓冲区使用等指标
  • 应用指标：结合应用响应时间、错误率等指标
  • 业务指标：结合交易量、用户数等指标
  • 工具集成：使用Prometheus、Grafana等工具实现多指标关联展示