如何看待mysql 5.7 sys数据库表
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，如何看待mysql 5.7 sys数据库表，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。引子mysql自mysql 5.6引入了pe
千家信息网最后更新 2025年11月07日如何看待mysql 5.7 sys数据库表
如何看待mysql 5.7 sys数据库表，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
引子
mysql自mysql 5.6引入了performance_schema数据库，对于监控及调优数据库提供了极大的便利。但是performance_schema数据库中有些数据仍显粗放，不易利用，需要数据库同学们进行再次聚合开发。mysql 5.7开始，增加内置数据库sys,对于performance_schema数据库的相关表进行二次开发及封装。便于运维小伙伴直接使用，极大提升了运维的效率。
sys数据库由一系列的表构成。下列罗列一些重要表，方便大家入门，好有个直观感受。
sys数据库表
host_summary表
-----
显示以主机名称分组的 SQL语句的数量，文件IO的数量，即文件IO的延迟，当前的连接会话数量，连接对应数据库用户数量，所对应的内存分配数量
（注：由此可知每个主机的负载分布情况，可以通过基本纵向对比，知道，每个主机节点的负载的变化趋势）
-----
mysql> select * from host_summary;+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+| host | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+| localhost | 17264 | 7.35 s | 425.55 us | 386 | 14512 | 1.07 s | 5 | 33 | 2 | 0 bytes | 0 bytes || three57 | 10 | 1.00 m | 6.00 s | 0 | 12 | 326.38 us | 0 | 1 | 1 | 0 bytes | 0 bytes || two57 | 10 | 1.00 m | 6.00 s | 0 | 12 | 44.42 us | 0 | 1 | 1 | 0 bytes | 0 bytes |+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+3 rows in set (0.01 sec)
host_summary_by_file_io表
----------
基于主机名称进行分组，显示每个主机名称的IO数量及IO延迟
----------
mysql> select * from host_summary_by_file_io;+------------+------+------------+| host | ios | io_latency |+------------+------+------------+| background | 3333 | 1.14 s || localhost | 7256 | 536.72 ms || three57 | 12 | 326.38 us || two57 | 12 | 44.42 us |+------------+------+------------+4 rows in set (0.01 sec)
host_summary_by_file_io_type表
----
某个主机下到底哪个子组件的IO产生最多，然后进行具体性分析
----
mysql> select * from host_summary_by_file_io_type;+------------+--------------------------------------+-------+---------------+-------------+| host | event_name | total | total_latency | max_latency |+------------+--------------------------------------+-------+---------------+-------------+| background | wait/io/file/innodb/innodb_log_file | 323 | 738.16 ms | 19.08 ms || background | wait/io/file/innodb/innodb_data_file | 1423 | 380.97 ms | 21.19 ms || background | wait/io/file/sql/binlog_index | 31 | 12.76 ms | 11.59 ms || background | wait/io/file/sql/binlog | 31 | 6.52 ms | 2.14 ms || background | wait/io/file/sql/FRM | 1404 | 951.13 us | 29.74 us || background | wait/io/file/sql/casetest | 15 | 399.98 us | 340.60 us || background | wait/io/file/myisam/kfile | 41 | 93.75 us | 33.20 us || background | wait/io/file/sql/ERRMSG | 5 | 59.83 us | 25.11 us || background | wait/io/file/myisam/dfile | 53 | 53.63 us | 4.03 us || background | wait/io/file/mysys/cnf | 5 | 18.89 us | 6.34 us || background | wait/io/file/sql/pid | 3 | 16.42 us | 10.14 us || background | wait/io/file/mysys/charset | 3 | 13.50 us | 6.53 us || background | wait/io/file/sql/global_ddl_log | 2 | 3.15 us | 1.87 us || localhost | wait/io/file/innodb/innodb_log_file | 74 | 182.02 ms | 16.42 ms || localhost | wait/io/file/sql/binlog | 95 | 180.14 ms | 15.37 ms || localhost | wait/io/file/sql/file_parser | 438 | 76.83 ms | 7.99 ms || localhost | wait/io/file/innodb/innodb_data_file | 47 | 35.92 ms | 8.78 ms || localhost | wait/io/file/sql/FRM | 2511 | 24.19 ms | 10.98 ms || localhost | wait/io/file/csv/metadata | 8 | 10.64 ms | 6.13 ms |
host_summary_by_statement_latency表
----
每个主机的 延迟，以及最大延迟，延迟的构成子组件
----
mysql> select * from sys.host_summary_by_statement_latency;+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+| host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+| two57 | 10 | 1.00 m | 1.00 m | 0 ps | 5 | 0 | 0 | 0 || three57 | 10 | 1.00 m | 1.00 m | 0 ps | 5 | 0 | 0 | 0 || localhost | 9455 | 3.73 s | 2.07 s | 100.57 ms | 3521 | 179048 | 21 | 197 || background | 0 | 0 ps | 0 ps | 0 ps | 0 | 0 | 0 | 0 |+------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+4 rows in set (0.01 sec)
memory_by_thread_by_current_bytes表
----
各个线程的内存分配的性能对比（注：各种MYSQL线程：IO READ THREAD,IO WRITE THREAD,
PAGE_CLEANER THREAD,IBUF THREAD,WORKER_THREAD,MONITOR THREAD,LOCK TIMEOUT
THREAD,DUMP THREAD,用于组复制的 接受线程及用于组复制的认证广播线程），SLAVE的SQL线程，
MAIN THREAD
(注：这样就了解哪个线程消耗的内存最多，进行纵向对比，就知道 线程的消耗历史，以及是否出现性能问题
----
mysql> select * from sys.memory_by_thread_by_current_bytes;+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+| thread_id | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+| 5 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 6 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 7 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 8 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 9 | innodb/io_write_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 10 | innodb/page_cleaner_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 11 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 12 | innodb/io_log_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 13 | innodb/io_ibuf_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 15 | innodb/srv_master_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 16 | innodb/srv_purge_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 17 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 18 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 19 | innodb/srv_worker_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 20 | innodb/srv_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 21 | innodb/srv_error_monitor_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 22 | innodb/srv_lock_timeout_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 23 | innodb/dict_stats_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 24 | innodb/buf_dump_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 25 | sql/signal_handler | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 26 | sql/compress_gtid_table | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 31 | group_rpl/THD_applier_module_receiver | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 32 | group_rpl/THD_certifier_broadcast | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 33 | sql/slave_sql | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 66 | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 67 | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 68 | root@localhost | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 1 | sql/main | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 2 | sql/thread_timer_notifier | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 3 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes || 4 | innodb/io_read_thread | 0 | 0 bytes | 0 bytes | 0 bytes | 0 bytes |+-----------+---------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+31 rows in set (0.04 sec)
关于如何看待mysql 5.7 sys数据库表问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注行业资讯频道了解更多相关知识。
数据
数据库
主机
线程
数量
延迟
问题
内存
名称
小伙
小伙伴
性能
文件
更多
纵向
组件
分析
分组
分配
帮助
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全相关厂家
计算机网络技术的就业叫什么
数据库怎么只显示获取的日期
网络安全心得1000
网络安全龙头股全
亳州交通软件开发公司哪家好
服务器的硬件配置要求
人社数据怎么进行网络安全保护
初中学历自学软件开发难吗
ps5艾尔登法环无法登录服务器
税控服务器管理系统
中国网络安全与信息化产业协会
南京 软件开发外包
海南网络时钟同步服务器虚拟主机
域名服务器填什么
软件开发岗位职业要求
方舟手机怎么开服务器和朋友玩
轻量云服务器搭建游戏怎么样
滴滴模式软件开发
中国自主网络安全
乐天华网络技术工作怎么样
网络安全教育登录平台
分离数据库学号加姓名
六安市服务器
雷州市网络安全和信息化委员会
合肥网络技术专业专升本大学
网络安全青年聚力内容
金山文档链接服务器异常
数据库技术的研究领域的论文
阿里云轻量服务器怎么找安全组
