2. 首页 > MySQL教程 > 正文

MySQL教程FG108-MySQL性能模式查询技巧

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：6

Part01-基础概念与理论知识

1.1 MySQL性能模式查询基础

MySQL性能模式（Performance Schema）提供了丰富的表和视图，用于监控数据库的运行状态。通过执行各种查询，我们可以获取数据库的性能指标，识别性能瓶颈。风哥教程参考MySQL官方文档MySQL Performance Schema。

1.2 MySQL性能模式查询类型

Performance Schema查询可以分为以下几类：

  • 语句性能查询：监控SQL语句的执行情况
  • 等待事件查询：监控各种等待事件的发生情况
  • 资源使用查询：监控CPU、内存、I/O等资源的使用情况
  • 连接和线程查询：监控连接数和线程状态
  • 表和索引查询：监控表和索引的使用情况

1.3 MySQL性能模式查询优化

为了提高Performance Schema查询的效率，我们可以：

  • 只查询必要的列和行
  • 使用合适的WHERE子句过滤数据
  • 使用ORDER BY和LIMIT限制结果集大小
  • 避免在查询中使用复杂的函数和表达式

Part02-生产环境规划与建议

2.1 MySQL性能模式查询策略

生产环境建议：

  • 根据监控目标制定合理的查询策略
  • 定期执行查询，建立性能基准
  • 结合其他监控工具，获得更全面的性能分析
  • 设置报警机制，当性能指标异常时及时通知

2.2 MySQL性能模式查询频率

查询频率应根据监控需求和服务器负载来确定：

  • 对于关键指标，如连接数、慢查询数等，可以设置较高的查询频率（如每1分钟）
  • 对于资源使用情况，如CPU、内存等，可以设置中等的查询频率（如每5分钟）
  • 对于历史趋势分析，可以设置较低的查询频率（如每30分钟）

学习交流加群风哥微信: itpux-com

2.3 MySQL性能模式查询结果分析

分析查询结果时，应关注以下几个方面：

  • 识别性能瓶颈，如慢查询、等待事件等
  • 分析趋势，如连接数增长、资源使用增加等
  • 对比基准值，发现性能退化
  • 关联其他指标，如业务量、系统负载等

Part03-生产环境项目实施方案

3.1 MySQL性能模式查询语句

以下是一些常用的Performance Schema查询语句：

3.1.1 语句性能查询

# 查询执行时间最长的SQL语句
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT,
MAX_TIMER_WAIT
FROM
performance_schema.events_statements_summary_by_digest
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;

3.1.2 等待事件查询

# 查询最常见的等待事件
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM
performance_schema.events_waits_summary_global_by_event_name
WHERE
SUM_TIMER_WAIT > 0
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;

3.1.3 资源使用查询

# 查询文件I/O情况
SELECT
FILE_NAME,
EVENT_NAME,
COUNT_READ,
SUM_NUMBER_OF_BYTES_READ,
COUNT_WRITE,
SUM_NUMBER_OF_BYTES_WRITE
FROM
performance_schema.file_summary_by_instance
WHERE
COUNT_READ > 0 OR COUNT_WRITE > 0
ORDER BY
SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 10;

3.1.4 连接和线程查询

# 查询连接数和线程状态
SELECT
USER,
HOST,
COMMAND,
TIME,
STATE,
INFO
FROM
performance_schema.threads
WHERE
PROCESSLIST_ID IS NOT NULL
ORDER BY
TIME DESC
LIMIT 20;

3.2 MySQL性能模式查询脚本

#!/bin/bash
# performance_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# 输出文件
OUTPUT_FILE=”/mysql/monitor/performance_$(date +”%Y%m%d_%H%M%S”).txt”

# 执行查询
mysql -u root -p”password” -e ”
— 查询执行时间最长的SQL语句
SELECT ‘=== 执行时间最长的SQL语句 ===’;
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT,
MAX_TIMER_WAIT
FROM
performance_schema.events_statements_summary_by_digest
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;

— 查询最常见的等待事件
SELECT ‘\n=== 最常见的等待事件 ===’;
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT
FROM
performance_schema.events_waits_summary_global_by_event_name
WHERE
SUM_TIMER_WAIT > 0
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;

— 查询连接数
SELECT ‘\n=== 连接数 ===’;
SELECT
USER,
HOST,
COUNT(*) AS connection_count
FROM
performance_schema.threads
WHERE
PROCESSLIST_ID IS NOT NULL
GROUP BY
USER, HOST
ORDER BY
connection_count DESC;

— 查询文件I/O情况
SELECT ‘\n=== 文件I/O情况 ===’;
SELECT
FILE_NAME,
EVENT_NAME,
COUNT_READ,
SUM_NUMBER_OF_BYTES_READ,
COUNT_WRITE,
SUM_NUMBER_OF_BYTES_WRITE
FROM
performance_schema.file_summary_by_instance
WHERE
COUNT_READ > 0 OR COUNT_WRITE > 0
ORDER BY
SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 10;
” > $OUTPUT_FILE

# 压缩输出文件
gzip $OUTPUT_FILE

# 删除7天前的文件
find /mysql/monitor -name “performance_*.txt.gz” -mtime +7 -delete

3.3 MySQL性能模式查询自动化

可以通过以下方式实现Performance Schema查询的自动化：

  • 使用cron定时执行查询脚本
  • 集成到监控系统，如Prometheus、Zabbix等
  • 使用MySQL Enterprise Monitor等企业级监控工具
  • 开发自定义监控应用，定期执行查询并展示结果

Part04-生产案例与实战讲解

4.1 MySQL性能模式查询实战

案例1：识别慢查询

# 识别执行时间超过1秒的SQL语句
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT / 1000000000 AS sum_time_ms,
AVG_TIMER_WAIT / 1000000000 AS avg_time_ms,
MAX_TIMER_WAIT / 1000000000 AS max_time_ms
FROM
performance_schema.events_statements_summary_by_digest
WHERE
AVG_TIMER_WAIT > 1000000000
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;
+———————————————+————+————+————+————+
| DIGEST_TEXT | COUNT_STAR | sum_time_ms | avg_time_ms | max_time_ms |
+———————————————+————+————+————+————+
| SELECT * FROM fgedu_users WHERE age > ? | 10 | 25200.0 | 2520.0 | 3100.0 |
| SELECT * FROM fgedu_orders WHERE create_time BETWEEN ? AND ? | 5 | 9350.0 | 1870.0 | 2200.0 |
| SELECT * FROM fgedu_products WHERE category_id = ? | 8 | 1200.0 | 150.0 | 200.0 |
+———————————————+————+————+————+————+

案例2：识别I/O瓶颈

# 识别I/O操作频繁的文件
SELECT
FILE_NAME,
EVENT_NAME,
COUNT_READ,
SUM_NUMBER_OF_BYTES_READ,
COUNT_WRITE,
SUM_NUMBER_OF_BYTES_WRITE
FROM
performance_schema.file_summary_by_instance
WHERE
SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE > 104857600 — 100MB
ORDER BY
SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
LIMIT 5;
+———————————————+—————————————-+————+———————-+————-+———————–+
| FILE_NAME | EVENT_NAME | COUNT_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_NUMBER_OF_BYTES_WRITE |
+———————————————+—————————————-+————+———————-+————-+———————–+
| /mysql/data/fgedudb/fgedu_orders.ibd | wait/io/file/innodb/innodb_data_file | 1000 | 838860800 | 500 | 419430400 |
| /mysql/data/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 0 | 0 | 1200 | 629145600 |
| /mysql/data/binlog.000001 | wait/io/file/sql/binlog | 0 | 0 | 800 | 268435456 |
| /mysql/data/fgedudb/fgedu_users.ibd | wait/io/file/innodb/innodb_data_file | 800 | 335544320 | 300 | 167772160 |
| /mysql/data/fgedudb/fgedu_products.ibd | wait/io/file/innodb/innodb_data_file | 600 | 209715200 | 200 | 104857600 |
+———————————————+—————————————-+————+———————-+————-+———————–+

4.2 MySQL性能模式查询案例

案例：监控连接数和线程状态

# 监控连接数和线程状态
SELECT
COMMAND,
COUNT(*) AS count,
MAX(TIME) AS max_time,
AVG(TIME) AS avg_time
FROM
performance_schema.threads
WHERE
PROCESSLIST_ID IS NOT NULL
GROUP BY
COMMAND
ORDER BY
count DESC;
+———+——-+———-+———-+
| COMMAND | count | max_time | avg_time |
+———+——-+———-+———-+
| Sleep | 45 | 3600 | 1800 |
| Query | 10 | 5 | 0.5 |
| Connect | 5 | 0 | 0 |
+———+——-+———-+———-+

4.3 MySQL性能模式查询优化技巧

优化Performance Schema查询的技巧：

  • 使用合适的WHERE子句过滤数据，减少结果集大小
  • 只查询必要的列，避免SELECT *
  • 使用ORDER BY和LIMIT限制结果集大小
  • 定期清理Performance Schema中的历史数据
  • 只启用必要的消费者和仪器，减少数据量
# 优化后的查询示例
SELECT
DIGEST_TEXT,
COUNT_STAR,
SUM_TIMER_WAIT
FROM
performance_schema.events_statements_summary_by_digest
WHERE
SUM_TIMER_WAIT > 10000000000 — 10秒
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 5;

Part05-风哥经验总结与分享

5.1 MySQL性能模式查询最佳实践

风哥提示：Performance Schema查询是MySQL性能监控的重要手段，掌握查询技巧可以帮助我们快速识别性能瓶颈。
  • 定期执行Performance Schema查询，建立性能基准
  • 结合sys模式使用，获得更直观的性能分析结果
  • 根据监控目标选择合适的查询语句
  • 优化查询语句，提高查询效率
  • 自动化查询过程，减少人工干预

5.2 MySQL性能模式查询经验

查询经验分享：

  • 使用Performance Schema查询识别慢查询，然后使用EXPLAIN分析执行计划
  • 监控等待事件，识别I/O瓶颈和锁竞争
  • 定期查询连接数和线程状态，避免连接泄漏
  • 监控文件I/O情况，识别磁盘瓶颈
  • 结合其他监控工具，获得更全面的性能分析

学习交流加群风哥QQ113257174

5.3 MySQL性能模式查询常见问题

常见问题及解决方案：

  • 问题：Performance Schema查询结果为空
    解决方案：检查是否启用了相应的消费者和仪器
  • 问题：Performance Schema查询执行缓慢
    解决方案：优化查询语句，只查询必要的列和行
  • 问题：Performance Schema数据不准确
    解决方案：确保启用了正确的仪器和消费者，定期清理历史数据
  • 问题：Performance Schema占用过多内存
    解决方案：调整历史记录大小，只启用必要的消费者和仪器

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息