statement_analysis

statement_analysis,x$statement_analysis

查看语句汇总统计信息，这些视图模仿MySQL企业版监控的查询分析视图列出语句的聚合统计信息，默认情况下按照总延迟时间(执行时间)降序排序。数据来源：performance_schema.events_statements_summary_by_digest

视图查询语句文本

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 1\G
*************************** 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 1\G;
*************************** 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)

视图字段含义如下：

• query：经过标准化转换的语句字符串，不带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：语句执行时出现排序合并的总次数
• digest：语句摘要计算的md5 hash值
• first_seen：该语句第一次出现的时间
• last_seen：该语句最近一次出现的时间