2. 首页 > MySQL教程 > 正文

MySQL教程FG110-MySQL sys模式视图详解

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

Part01-基础概念与理论知识

1.1 MySQL sys模式视图概述

MySQL sys模式包含多个视图，这些视图提供了对Performance Schema和INFORMATION_SCHEMA数据的友好访问方式。sys模式的视图使用更直观的命名和格式化的输出，使得性能监控和问题诊断变得更加容易。风哥教程参考MySQL官方文档MySQL sys Schema。

1.2 MySQL sys模式视图分类

sys模式的视图可以分为以下几类：

  • 语句相关视图：监控SQL语句的执行情况
  • 索引相关视图：监控索引的使用情况
  • 内存相关视图：监控内存的使用情况
  • I/O相关视图：监控I/O操作的情况
  • 锁相关视图：监控锁的使用情况
  • 用户相关视图：监控用户的活动情况
  • 复制相关视图：监控复制的状态
  • 主机相关视图：监控主机的性能指标

1.3 MySQL sys模式视图命名规则

sys模式的视图命名遵循以下规则：

  • 使用下划线分隔单词，如statements_with_errors_or_warnings
  • 视图名称通常描述其功能，如schema_unused_indexes表示未使用的索引
  • 对于按不同维度分组的视图，使用by_xxx后缀，如memory_by_host_by_current_bytes

Part02-生产环境规划与建议

2.1 MySQL sys模式视图使用建议

生产环境建议：

  • 在生产环境中，建议定期使用sys模式的视图进行性能监控
  • 根据监控目标选择合适的视图，避免查询所有视图
  • 使用WHERE子句过滤数据，减少结果集大小
  • 结合其他监控工具，获得更全面的性能分析

2.2 MySQL sys模式视图性能影响

sys模式的视图本身不会产生额外的性能开销，因为它们只是对Performance Schema和INFORMATION_SCHEMA中现有数据的视图。但是，查询sys模式的视图可能会消耗一些资源，特别是当查询涉及大量数据时。学习交流加群风哥微信: itpux-com

2.3 MySQL sys模式视图使用场景

sys模式的视图适用于以下场景：

  • 性能监控和问题诊断
  • 慢查询分析
  • 资源使用监控
  • 索引使用分析
  • 锁竞争分析
  • 复制状态监控
  • 用户活动监控

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

3.1 MySQL sys模式语句相关视图

3.1.1 statements_with_errors_or_warnings

该视图显示有错误或警告的SQL语句。

# 查询有错误或警告的SQL语句
SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY exec_count DESC LIMIT 10;

3.1.2 statements_with_full_table_scans

该视图显示使用全表扫描的SQL语句。

# 查询使用全表扫描的SQL语句
SELECT * FROM sys.statements_with_full_table_scans ORDER BY exec_count DESC LIMIT 10;

3.1.3 statements_with_runtimes_in_95th_percentile

该视图显示执行时间在95百分位以上的SQL语句。

# 查询执行时间在95百分位以上的SQL语句
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile ORDER BY avg_timer_wait DESC LIMIT 10;

3.1.4 statements_with_temp_tables

该视图显示使用临时表的SQL语句。

# 查询使用临时表的SQL语句
SELECT * FROM sys.statements_with_temp_tables ORDER BY exec_count DESC LIMIT 10;

3.2 MySQL sys模式索引相关视图

3.2.1 schema_unused_indexes

该视图显示未使用的索引。

# 查询未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE schema_name = ‘fgedudb’;

3.2.2 schema_redundant_indexes

该视图显示冗余的索引。

# 查询冗余的索引
SELECT * FROM sys.schema_redundant_indexes WHERE schema_name = ‘fgedudb’;

3.2.3 schema_index_statistics

该视图显示索引的使用统计信息。

# 查询索引的使用统计信息
SELECT * FROM sys.schema_index_statistics WHERE table_schema = ‘fgedudb’ ORDER BY rows_selected DESC LIMIT 10;

3.3 MySQL sys模式内存相关视图

3.3.1 memory_by_host_by_current_bytes

该视图按主机分组显示内存使用情况。

# 按主机分组显示内存使用情况
SELECT * FROM sys.memory_by_host_by_current_bytes ORDER BY current_bytes DESC;

3.3.2 memory_by_thread_by_current_bytes

该视图按线程分组显示内存使用情况。

# 按线程分组显示内存使用情况
SELECT * FROM sys.memory_by_thread_by_current_bytes ORDER BY current_bytes DESC LIMIT 10;

3.3.3 memory_by_user_by_current_bytes

该视图按用户分组显示内存使用情况。

# 按用户分组显示内存使用情况
SELECT * FROM sys.memory_by_user_by_current_bytes ORDER BY current_bytes DESC;

3.4 MySQL sys模式I/O相关视图

3.4.1 io_global_by_file_by_bytes

该视图按文件分组显示I/O字节数。

# 按文件分组显示I/O字节数
SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total_bytes DESC LIMIT 10;

3.4.2 io_global_by_wait_by_bytes

该视图按等待事件分组显示I/O字节数。

# 按等待事件分组显示I/O字节数
SELECT * FROM sys.io_global_by_wait_by_bytes ORDER BY total_bytes DESC LIMIT 10;

3.5 MySQL sys模式锁相关视图

3.5.1 innodb_lock_waits

该视图显示InnoDB锁等待情况。

# 查询InnoDB锁等待情况
SELECT * FROM sys.innodb_lock_waits ORDER BY wait_age DESC;

3.5.2 schema_table_lock_waits

该视图显示表锁等待情况。

# 查询表锁等待情况
SELECT * FROM sys.schema_table_lock_waits ORDER BY wait_age DESC;

3.6 MySQL sys模式用户相关视图

3.6.1 user_summary

该视图显示用户活动摘要。

# 查询用户活动摘要
SELECT * FROM sys.user_summary ORDER BY total_connections DESC;

3.6.2 user_summary_by_file_io

该视图按文件I/O分组显示用户活动摘要。

# 按文件I/O分组显示用户活动摘要
SELECT * FROM sys.user_summary_by_file_io ORDER BY total_read + total_write DESC;

3.7 MySQL sys模式复制相关视图

3.7.1 replication_applier_status_by_coordinator

该视图显示复制应用协调器的状态。

# 查询复制应用协调器的状态
SELECT * FROM sys.replication_applier_status_by_coordinator;

3.7.2 replication_applier_status_by_worker

该视图显示复制应用工作线程的状态。

# 查询复制应用工作线程的状态
SELECT * FROM sys.replication_applier_status_by_worker;

Part04-生产案例与实战讲解

4.1 MySQL sys模式视图查询实战

案例1：查找执行时间最长的SQL语句

# 查找执行时间最长的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 5;
+———————————————+—————+————-+—————-+————+————–+
| query | db | exec_count | total_latency | avg_latency | max_latency |
+———————————————+—————+————-+—————-+————+————–+
| SELECT * FROM fgedu_users WHERE age > ? | fgedudb | 10 | 25.20 s | 2.52 s | 3.10 s |
| SELECT * FROM fgedu_orders WHERE create_time BETWEEN ? AND ? | fgedudb | 5 | 9.35 s | 1.87 s | 2.20 s |
| SELECT * FROM fgedu_products WHERE category_id = ? | fgedudb | 8 | 1.20 s | 150.00 ms | 200.00 ms |
| UPDATE fgedu_users SET name = ? WHERE id = ? | fgedudb | 15 | 3.20 s | 213.33 ms | 450.00 ms |
| INSERT INTO fgedu_orders (user_id, amount) VALUES (?, ?) | fgedudb | 20 | 1.80 s | 90.00 ms | 120.00 ms |
+———————————————+—————+————-+—————-+————+————–+

案例2：查找未使用的索引

# 查找未使用的索引
SELECT
table_schema,
table_name,
index_name,
index_columns
FROM
sys.schema_unused_indexes
WHERE
table_schema = ‘fgedudb’;
+—————+—————+————-+—————-+
| table_schema | table_name | index_name | index_columns |
+—————+—————+————-+—————-+
| fgedudb | fgedu_users | idx_email | email |
| fgedudb | fgedu_orders | idx_user_id | user_id |
+—————+—————+————-+—————-+

4.2 MySQL sys模式视图监控案例

案例：监控数据库性能

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

# 输出文件
OUTPUT_FILE=”/mysql/monitor/sys_performance_$(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_thread_id,
waiting_query,
blocking_trx_id,
blocking_thread_id,
blocking_query
FROM
sys.innodb_lock_waits
ORDER BY
wait_age DESC
LIMIT 5;
” > $OUTPUT_FILE

# 压缩输出文件
gzip $OUTPUT_FILE

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

4.3 MySQL sys模式视图优化技巧

使用sys模式视图进行性能优化的技巧：

  • 定期查询sys模式的视图，识别性能瓶颈
  • 使用WHERE子句过滤数据，减少结果集大小
  • 使用LIMIT限制结果集大小
  • 结合其他监控工具，获得更全面的性能分析
  • 根据监控结果采取相应的优化措施

Part05-风哥经验总结与分享

5.1 MySQL sys模式视图使用最佳实践

风哥提示：sys模式的视图是MySQL性能监控的强大工具，掌握其使用方法可以帮助我们快速识别和解决性能问题。
  • 定期使用sys模式的视图进行性能监控，至少每周一次
  • 根据监控目标选择合适的视图，避免查询所有视图
  • 使用sys模式的函数格式化数据，使输出更易读
  • 结合Performance Schema使用，获得更全面的性能分析
  • 根据监控结果采取相应的优化措施，如优化SQL语句、调整索引等

5.2 MySQL sys模式视图使用经验

使用经验分享：

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

学习交流加群风哥QQ113257174

5.3 MySQL sys模式视图常见问题

常见问题及解决方案：

  • 问题：sys模式视图查询缓慢
    解决方案：优化查询语句，只查询必要的列和行，使用LIMIT限制结果集大小
  • 问题：sys模式视图结果为空
    解决方案：检查Performance Schema是否启用，以及相关的仪器和消费者是否启用
  • 问题：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，节假日休息