语句效率统计视图 | 全方位认识 sys 系统库

在上一篇《统计信息查询视图|全方位认识 sys 系统库》中，我们介绍了利用sys 系统库的查询统计信息的快捷视图，本期将为大家介绍语句查询效率语句统计信息相关的视图，这些视图可以快速找出数据库中哪些语句使用了全表扫描、哪些语句使用了文件排序、哪些语句使用了临时表。

PS：由于本文中所提及的视图功能的特殊性（DBA日常工作中可能需要查询一些信息做一些数据分析使用），所以下文中会列出部分视图中的select语句文本，以便大家更直观地学习。

01.schema_tables_with_full_table_scans,x$schema_tables_with_full_table_scans

查询执行过全扫描访问的表，默认情况下按照表扫描的行数进行降序排序。数据来源：performance_schema.table_io_waits_summary_by_index_usage

视图查询语句文本

SELECT object_schema,
  object_name,
  count_read AS rows_full_scanned,
  sys.format_time(sum_timer_wait) AS latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NULL
AND count_read > 0
ORDER BY count_read DESC;

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
admin@localhost : sys 12:39:48> select * from schema_tables_with_full_table_scans limit 3;
+---------------+-------------+-------------------+---------+
| object_schema | object_name | rows_full_scanned | latency |
+---------------+-------------+-------------------+---------+
| sbtest        | sbtest1    |          16094049 | 24.80 s |
+---------------+-------------+-------------------+---------+
1 row in set (0.00 sec)

# 带x$前缀的视图
admin@localhost : sys 12:39:52> select * from x$schema_tables_with_full_table_scans limit 3;
+---------------+-------------+-------------------+----------------+
| object_schema | object_name | rows_full_scanned | latency        |
+---------------+-------------+-------------------+----------------+
| sbtest        | sbtest1    |          16094049 | 24795682856625 |
+---------------+-------------+-------------------+----------------+
1 row in set (0.00 sec)

视图字段含义如下：

• object_schema：schema名称

• OBJECT_NAME：表名

• rows_full_scanned：全表扫描的总数据行数

• latency：完整的表扫描操作的总延迟时间（执行时间）

02.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：语句全表扫描查询的总次数<