MySQL教程FG109-MySQL sys模式使用

Part01-基础概念与理论知识

1.1 MySQL sys模式概述

MySQL sys模式是MySQL 5.7及以上版本引入的一个系统模式，它提供了一系列视图和函数，用于更方便地查询和分析Performance Schema和INFORMATION_SCHEMA中的数据。sys模式的视图使用更友好的命名和格式，使得性能监控和问题诊断变得更加容易。风哥教程参考MySQL官方文档MySQL sys Schema。

1.2 MySQL sys模式视图分类

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

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

1.3 MySQL sys模式配置参数

# 查看sys模式相关参数
SHOW VARIABLES LIKE ‘sys%’;

+—————————————-+———-+
| Variable_name | Value |
+—————————————-+———-+
| sys.analyze_thread_txt_col_width | 120 |
| sys.config_history | ON |
| sys.config_history_keep_days | 31 |
| sys.statement_truncate_len | 64 |
| sys.timetable_history | OFF |
| sys.timetable_history_keep_days | 31 |
+—————————————-+———-+

Part02-生产环境规划与建议

2.1 MySQL sys模式启用建议

生产环境建议：

在生产环境中，建议启用sys模式，它是MySQL 5.7+的默认功能
sys模式本身不会产生额外的性能开销，因为它只是对现有数据的视图
定期使用sys模式的视图进行性能监控和问题诊断

2.2 MySQL sys模式性能影响

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

2.3 MySQL sys模式使用场景

sys模式适用于以下场景：

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

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

3.1 MySQL sys模式安装与配置

在MySQL 5.7及以上版本中，sys模式是默认安装的。如果需要手动安装或更新，可以执行以下操作：

# 手动安装sys模式
mysql -u root -p < /usr/share/mysql/sys_schema.sql # 查看sys模式是否安装成功 SHOW DATABASES LIKE 'sys';

+——————+
| Database (sys) |
+——————+
| sys |
+——————+

3.2 MySQL sys模式视图详解

sys模式包含多个视图，以下是一些常用的视图：

3.2.1 语句相关视图

sys.statements_with_errors_or_warnings：查询有错误或警告的语句
sys.statements_with_full_table_scans：查询使用全表扫描的语句
sys.statements_with_runtimes_in_95th_percentile：查询执行时间在95百分位以上的语句
sys.statements_with_temp_tables：查询使用临时表的语句

3.2.2 索引相关视图

sys.schema_unused_indexes：查询未使用的索引
sys.schema_redundant_indexes：查询冗余的索引
sys.schema_index_statistics：查询索引使用统计信息

3.2.3 内存相关视图

sys.memory_by_host_by_current_bytes：按主机分组的内存使用情况
sys.memory_by_thread_by_current_bytes：按线程分组的内存使用情况
sys.memory_by_user_by_current_bytes：按用户分组的内存使用情况

3.2.4 I/O相关视图

sys.io_global_by_file_by_bytes：按文件分组的I/O字节数
sys.io_global_by_wait_by_bytes：按等待事件分组的I/O字节数

3.3 MySQL sys模式函数使用

sys模式提供了多个函数，用于格式化和分析数据：

# 常用的sys模式函数
SELECT
sys.format_bytes(1024),
sys.format_pico_time(1000000000),
sys.format_statement(‘SELECT * FROM fgedu_users WHERE id = 1’);

+———————+—————————+——————————————–+
| sys.format_bytes(1024) | sys.format_pico_time(1000000000) | sys.format_statement(‘SELECT * FROM fgedu_users WHERE id = 1’) |
+———————+—————————+——————————————–+
| 1.00 KiB | 1.00 ms | SELECT * FROM fgedu_users WHERE id = 1 |
+———————+—————————+——————————————–+

Part04-生产案例与实战讲解

4.1 MySQL sys模式查询实战

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

# 查找执行时间最长的SQL语句
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile ORDER BY avg_timer_wait DESC LIMIT 10;

+——————————————————————-+—————+————-+—————-+—————-+—————-+————+————–+
| query | db | exec_count | err_count | warn_count | total_latency | avg_latency | max_latency |
+——————————————————————-+—————+————-+—————-+—————-+—————-+————+————–+
| SELECT * FROM fgedu_users WHERE age > ? | fgedudb | 10 | 0 | 0 | 25.20 s | 2.52 s | 3.10 s |
| SELECT * FROM fgedu_orders WHERE create_time BETWEEN ? AND ? | fgedudb | 5 | 0 | 0 | 9.35 s | 1.87 s | 2.20 s |
| UPDATE fgedu_users SET name = ? WHERE id = ? | fgedudb | 15 | 0 | 0 | 3.20 s | 213.33 ms | 450.00 ms |
| INSERT INTO fgedu_orders (user_id, amount) VALUES (?, ?) | fgedudb | 20 | 0 | 0 | 1.80 s | 90.00 ms | 120.00 ms |
| SELECT * FROM fgedu_products WHERE category_id = ? | fgedudb | 8 | 0 | 0 | 1.20 s | 150.00 ms | 200.00 ms |
+——————————————————————-+—————+————-+—————-+—————-+—————-+————+————–+

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

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

案例3：查找冗余的索引

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

4.2 MySQL sys模式监控案例

案例：监控内存使用情况

# 监控内存使用情况
SELECT * FROM sys.memory_by_host_by_current_bytes ORDER BY current_bytes DESC;

+———–+——————–+——————-+——————-+——————-+————+
| host | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+———–+——————–+——————-+——————-+——————-+————+
| 192.168.1.100 | 100 | 104.86 MiB | 1.05 MiB | 10.00 MiB | 104.86 MiB |
| localhost | 50 | 52.43 MiB | 1.05 MiB | 5.00 MiB | 52.43 MiB |
| 192.168.1.101 | 20 | 20.97 MiB | 1.05 MiB | 2.00 MiB | 20.97 MiB |
+———–+——————–+——————-+——————-+——————-+————+

4.3 MySQL sys模式优化技巧

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

定期查询sys模式的视图，识别性能瓶颈
使用sys模式的函数格式化数据，使输出更易读
结合Performance Schema使用，获得更全面的性能分析
使用sys模式的视图监控索引使用情况，优化索引设计
使用sys模式的视图监控SQL语句执行情况，优化查询

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

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

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

— 查找未使用的索引
SELECT ‘\n=== 未使用的索引 ===’;
SELECT * FROM sys.schema_unused_indexes WHERE schema_name = ‘fgedudb’;

— 查找冗余的索引
SELECT ‘\n=== 冗余的索引 ===’;
SELECT * FROM sys.schema_redundant_indexes WHERE schema_name = ‘fgedudb’;

— 监控内存使用情况
SELECT ‘\n=== 内存使用情况 ===’;
SELECT * FROM sys.memory_by_host_by_current_bytes ORDER BY current_bytes DESC;

— 监控I/O情况
SELECT ‘\n=== I/O情况 ===’;
SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total_bytes DESC LIMIT 10;
” > $OUTPUT_FILE

# 压缩输出文件
gzip $OUTPUT_FILE

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

Part05-风哥经验总结与分享

5.1 MySQL sys模式使用最佳实践

风哥提示：sys模式是MySQL性能监控的强大工具，掌握其使用方法可以帮助我们快速识别和解决性能问题。

定期使用sys模式的视图进行性能监控，至少每周一次
结合其他监控工具，如pt-query-digest，获得更全面的性能分析
使用sys模式的函数格式化数据，使输出更易读
定期清理未使用的索引，优化数据库性能
监控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