statement_analysis,x$statement_analysis

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

• statement_analysis和x$statement_analysis视图字段含义如下：

  • query：经过标准化转换的语句字符串
  • 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：该语句最近一次出现的时间

• 视图定义语句

# statement_analysis
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW statement_analysis (
  query,  db,  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,  first_seen,  last_seen
) AS
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$statement_analysis
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$statement_analysis (
  query,  db,  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,  first_seen,  last_seen
) AS
SELECT 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,
      SUM_TIMER_WAIT AS total_latency,
      MAX_TIMER_WAIT AS max_latency,
      AVG_TIMER_WAIT AS avg_latency,
      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;

• 视图查询信息示例

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)

ERROR: 
No query specified

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)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-statement-analysis.html

---

上一篇： session_ssl_status视图 |

下一篇： statements_with_errors_or_warnings,x$statements_with_errors_or_warnings视图