暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

MySql 监控SQL1

原创 王铮 2023-04-18
193

statement_analysis,x $ statement_analysis
查看语句汇总统计信息,监控的查询分析视图列出语句的聚合统计信息,根据情况下按照总时序时间(执行时间)降序排序。
首先查询语句文本

SELECT sys.format_statement(DIGEST_TEXT) AS query,
SCHEMA_NAME AS db,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, “*”, “”) AS full_scan,
COUNT_STAR AS exec_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
sys.format_time(SUM_TIMER_WAIT) AS total_latency,
sys.format_time(MAX_TIMER_WAIT) AS max_latency,
sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
sys.format_time(SUM_LOCK_TIME) AS lock_latency,
SUM_ROWS_SENT AS rows_sent,
ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
SUM_ROWS_EXAMINED AS rows_examined,
ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
SUM_ROWS_AFFECTED AS rows_affected,
ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
SUM_CREATED_TMP_TABLES AS tmp_tables,
SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
SUM_SORT_ROWS AS rows_sorted,
SUM_SORT_MERGE_PASSES AS sort_merge_passes,
DIGEST AS digest,
FIRST_SEEN AS first_seen,
LAST_SEEN as last_seen
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC;

不带x$前缀的视图

admin@localhost : sys 12:46:07> select * from statement_analysis limit 1G
*************************** 1. row ***************************
query: ALTER TABLE test ADD INDEX i_k ( test )
db: xiaoboluo
full_scan:
exec_count: 2
err_count: 2
warn_count: 0
total_latency: 56.56 m
max_latency: 43.62 m
avg_latency: 28.28 m
lock_latency: 0 ps
rows_sent: 0
rows_sent_avg: 0
rows_examined: 0
rows_examined_avg: 0
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: f359a4a8407ee79ea1d84480fdd04f62
first_seen: 2017-09-07 11:44:35
last_seen: 2017-09-07 12:36:47
1 row in set (0.14 sec)

带x$前缀的视图

admin@localhost : sys 12:46:34> select * from x$statement_analysis limit 1G;
*************************** 1. row ***************************
query: ALTER TABLE test ADD INDEX i_k ( test )
db: xiaoboluo
full_scan:
exec_count: 2
err_count: 2
warn_count: 0
total_latency: 3393877088372000
max_latency: 2617456143674000
avg_latency: 1696938544186000
lock_latency: 0
rows_sent: 0
rows_sent_avg: 0
rows_examined: 0
rows_examined_avg: 0
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: f359a4a8407ee79ea1d84480fdd04f62
first_seen: 2017-09-07 11:44:35
last_seen: 2017-09-07 12:36:47
1 row in set (0.01 sec)

说明:
查询:通过标准化转换的语句字符串,不带x $的视图长度限制为64字节,带x $的视图长度限制为1024字节
db:语句对应的默认数据库,如果没有分布式数据库,该分区为NULL
full_scan:语句全表扫描查询的总次数
exec_count:语句执行的总次数
err_count:语句发生的错误总次数
warn_count:语句发生的警告总次数
total_latency:语句的总延迟时间(执行时间)
max_latency:个别语句的最大延迟时间(执行时间)
avg_latency:每个语句的平均延迟时间(执行时间)
lock_latency:语句的总锁等待时间
rows_sent:语句返回客户端的总数据行数
rows_sent_avg:每个语句返回客户端的平均数据行数
rows_examined:语句从存储引擎读取的总数据数
rows_examined_avg:每个语句从存储引擎检查的平均数据行数
rows_affected:语句影响的总数据行数
rows_affected_avg:每个语句影响的平均数据行数
tmp_tables:语句执行时创建的内部内存临时表的总数
tmp_disk_tables:语句执行时创建的内部磁盘临时表的总数
rows_sorted:语句执行时出现排序的总数据行数
sort_merge_passes:语句执行时出现排序合并的总次数
摘要:语句摘要计算的md5哈希值
first_seen:该语句第一次出现的时间
last_seen:该语句最近一次出现的时间

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

目录
  • 不带x$前缀的视图
  • 带x$前缀的视图