2. 首页 > MySQL教程 > 正文

MySQL教程FG111-MySQL sys模式查询技巧

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

Part01-基础概念与理论知识

1.1 MySQL sys模式查询基础

MySQL sys模式提供了一系列视图和函数，用于更方便地查询和分析Performance Schema和INFORMATION_SCHEMA中的数据。sys模式的查询使用更友好的语法和格式化的输出，使得性能监控和问题诊断变得更加容易。风哥教程参考MySQL官方文档MySQL sys Schema。

1.2 MySQL sys模式查询类型

sys模式的查询可以分为以下几类：

  • 性能查询：监控SQL语句的执行情况
  • 索引查询：监控索引的使用情况
  • 资源查询：监控内存、I/O等资源的使用情况
  • 锁查询：监控锁的使用情况
  • 复制查询：监控复制的状态
  • 用户查询：监控用户的活动情况

1.3 MySQL sys模式查询优化

为了提高sys模式查询的效率，我们可以：

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

Part02-生产环境规划与建议

2.1 MySQL sys模式查询策略

生产环境建议：

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

2.2 MySQL sys模式查询频率

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

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

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

2.3 MySQL sys模式查询结果分析

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

  • 识别性能瓶颈，如慢查询、锁竞争等
  • 分析趋势，如资源使用增加、查询响应时间变长等
  • 对比基准值，发现性能退化
  • 关联其他指标，如业务量、系统负载等

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

3.1 MySQL sys模式性能查询

3.1.1 慢查询分析

# 查找执行时间最长的SQL语句
SELECT
query,
db,
exec_count,
total_latency,
avg_latency,
max_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
ORDER BY
avg_latency DESC
LIMIT 10;

3.1.2 全表扫描查询

# 查找使用全表扫描的SQL语句
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_full_table_scans
ORDER BY
exec_count DESC
LIMIT 10;

3.1.3 临时表使用查询

# 查找使用临时表的SQL语句
SELECT
query,
db,
exec_count,
total_latency,
avg_latency,
tmp_tables,
tmp_disk_tables
FROM
sys.statements_with_temp_tables
ORDER BY
tmp_disk_tables DESC
LIMIT 10;

3.2 MySQL sys模式索引查询

3.2.1 未使用索引查询

# 查找未使用的索引
SELECT
table_schema,
table_name,
index_name,
index_columns
FROM
sys.schema_unused_indexes
WHERE
table_schema NOT IN (‘sys’, ‘information_schema’, ‘performance_schema’, ‘mysql’);

3.2.2 冗余索引查询

# 查找冗余的索引
SELECT
table_schema,
table_name,
redundant_index,
redundant_columns,
dominant_index,
dominant_columns
FROM
sys.schema_redundant_indexes
WHERE
table_schema NOT IN (‘sys’, ‘information_schema’, ‘performance_schema’, ‘mysql’);

3.2.3 索引使用统计

# 查询索引使用统计信息
SELECT
table_schema,
table_name,
index_name,
rows_selected,
rows_inserted,
rows_updated,
rows_deleted
FROM
sys.schema_index_statistics
WHERE
table_schema NOT IN (‘sys’, ‘information_schema’, ‘performance_schema’, ‘mysql’)
ORDER BY
rows_selected DESC
LIMIT 10;

3.3 MySQL sys模式资源查询

3.3.1 内存使用查询

# 按主机分组查询内存使用情况
SELECT
host,
current_count_used,
current_allocated,
current_avg_alloc,
current_max_alloc,
total_allocated
FROM
sys.memory_by_host_by_current_bytes
ORDER BY
current_bytes DESC;

3.3.2 I/O使用查询

# 按文件分组查询I/O使用情况
SELECT
file_name,
event_name,
total_read,
total_write,
total_bytes
FROM
sys.io_global_by_file_by_bytes
ORDER BY
total_bytes DESC
LIMIT 10;

3.3.3 连接数查询

# 查询连接数情况
SELECT
user,
host,
total_connections,
current_connections
FROM
sys.user_summary
ORDER BY
total_connections DESC;

3.4 MySQL sys模式锁查询

3.4.1 InnoDB锁等待查询

# 查询InnoDB锁等待情况
SELECT
waiting_trx_id,
waiting_thread_id,
waiting_query,
blocking_trx_id,
blocking_thread_id,
blocking_query,
wait_age,
wait_age_secs
FROM
sys.innodb_lock_waits
ORDER BY
wait_age_secs DESC;

3.4.2 表锁等待查询

# 查询表锁等待情况
SELECT
object_schema,
object_name,
waiting_thread_id,
waiting_pid,
waiting_account,
waiting_query,
blocking_thread_id,
blocking_pid,
blocking_account,
blocking_query,
wait_age,
wait_age_secs
FROM
sys.schema_table_lock_waits
ORDER BY
wait_age_secs DESC;

3.5 MySQL sys模式复制查询

3.5.1 复制应用状态查询

# 查询复制应用状态
SELECT
channel_name,
service_state,
last_error_number,
last_error_message
FROM
sys.replication_applier_status_by_coordinator;

3.5.2 复制工作线程状态查询

# 查询复制工作线程状态
SELECT
channel_name,
thread_id,
service_state,
last_error_number,
last_error_message
FROM
sys.replication_applier_status_by_worker;

Part04-生产案例与实战讲解

4.1 MySQL sys模式查询实战

案例1：性能问题诊断

# 诊断数据库性能问题
— 1. 查找执行时间最长的SQL语句
SELECT ‘=== 执行时间最长的SQL语句 ===’;
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
ORDER BY
avg_latency DESC
LIMIT 5;

— 2. 查找使用全表扫描的SQL语句
SELECT ‘\n=== 使用全表扫描的SQL语句 ===’;
SELECT
query,
db,
exec_count
FROM
sys.statements_with_full_table_scans
ORDER BY
exec_count DESC
LIMIT 5;

— 3. 查找未使用的索引
SELECT ‘\n=== 未使用的索引 ===’;
SELECT
table_schema,
table_name,
index_name
FROM
sys.schema_unused_indexes
WHERE
table_schema = ‘fgedudb’;

— 4. 查找锁等待情况
SELECT ‘\n=== 锁等待情况 ===’;
SELECT
waiting_trx_id,
waiting_query,
blocking_trx_id,
blocking_query,
wait_age
FROM
sys.innodb_lock_waits
ORDER BY
wait_age DESC
LIMIT 5;

案例2：资源使用分析

# 分析资源使用情况
— 1. 内存使用情况
SELECT ‘=== 内存使用情况 ===’;
SELECT
host,
current_allocated
FROM
sys.memory_by_host_by_current_bytes
ORDER BY
current_bytes DESC;

— 2. I/O使用情况
SELECT ‘\n=== I/O使用情况 ===’;
SELECT
file_name,
total_bytes
FROM
sys.io_global_by_file_by_bytes
ORDER BY
total_bytes DESC
LIMIT 10;

— 3. 连接数情况
SELECT ‘\n=== 连接数情况 ===’;
SELECT
user,
host,
current_connections
FROM
sys.user_summary
ORDER BY
current_connections DESC;

4.2 MySQL sys模式监控案例

案例：定期监控数据库性能

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

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

# 执行查询
mysql -u root -p”password” -e ”
— 执行时间最长的SQL语句
SELECT ‘=== 执行时间最长的SQL语句 ===’;
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
ORDER BY
avg_latency DESC
LIMIT 10;

— 未使用的索引
SELECT ‘\n=== 未使用的索引 ===’;
SELECT
table_schema,
table_name,
index_name
FROM
sys.schema_unused_indexes
WHERE
table_schema = ‘fgedudb’;

— 冗余的索引
SELECT ‘\n=== 冗余的索引 ===’;
SELECT
table_schema,
table_name,
redundant_index,
dominant_index
FROM
sys.schema_redundant_indexes
WHERE
table_schema = ‘fgedudb’;

— 内存使用情况
SELECT ‘\n=== 内存使用情况 ===’;
SELECT
host,
current_allocated
FROM
sys.memory_by_host_by_current_bytes
ORDER BY
current_bytes DESC;

— I/O使用情况
SELECT ‘\n=== I/O使用情况 ===’;
SELECT
file_name,
total_bytes
FROM
sys.io_global_by_file_by_bytes
ORDER BY
total_bytes DESC
LIMIT 10;

— 锁等待情况
SELECT ‘\n=== 锁等待情况 ===’;
SELECT
waiting_trx_id,
waiting_query,
blocking_trx_id,
blocking_query,
wait_age
FROM
sys.innodb_lock_waits
ORDER BY
wait_age DESC
LIMIT 5;

— 连接数情况
SELECT ‘\n=== 连接数情况 ===’;
SELECT
user,
host,
current_connections
FROM
sys.user_summary
ORDER BY
current_connections DESC;
” > $OUTPUT_FILE

# 压缩输出文件
gzip $OUTPUT_FILE

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

4.3 MySQL sys模式查询优化技巧

优化sys模式查询的技巧：

  • 使用合适的WHERE子句过滤数据，减少结果集大小
  • 只查询必要的列，避免SELECT *
  • 使用ORDER BY和LIMIT限制结果集大小
  • 定期清理Performance Schema中的历史数据，提高查询速度
  • 使用sys模式的函数格式化数据，使输出更易读
# 优化后的查询示例
SELECT
query,
db,
exec_count,
total_latency,
avg_latency
FROM
sys.statements_with_runtimes_in_95th_percentile
WHERE
db = ‘fgedudb’
ORDER BY
avg_latency DESC
LIMIT 5;

Part05-风哥经验总结与分享

5.1 MySQL sys模式查询最佳实践

风哥提示：sys模式查询是MySQL性能监控的重要手段，掌握查询技巧可以帮助我们快速识别和解决性能问题。
  • 定期使用sys模式查询进行性能监控，至少每周一次
  • 根据监控目标选择合适的查询语句
  • 结合其他监控工具，如pt-query-digest，获得更全面的性能分析
  • 自动化查询过程，减少人工干预
  • 根据查询结果采取相应的优化措施

5.2 MySQL sys模式查询经验

查询经验分享：

  • sys模式的查询语句简洁明了，易于理解和使用
  • sys模式的输出格式友好，包含格式化的时间和字节数
  • sys模式的视图可以直接用于监控和分析，无需编写复杂的查询
  • sys模式是学习MySQL性能监控的良好起点，适合初学者
  • sys模式可以与其他监控工具结合使用，获得更全面的性能分析

学习交流加群风哥QQ113257174

5.3 MySQL sys模式查询常见问题

常见问题及解决方案：

  • 问题：sys模式查询结果为空
    解决方案：检查Performance Schema是否启用，以及相关的仪器和消费者是否启用
  • 问题：sys模式查询执行缓慢
    解决方案：优化查询语句，只查询必要的列和行，使用LIMIT限制结果集大小
  • 问题：sys模式查询数据不准确
    解决方案：确保Performance Schema的数据收集是准确的，定期清理历史数据
  • 问题：sys模式未安装
    解决方案：手动安装sys模式，执行sys_schema.sql脚本

更多视频教程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，节假日休息