在上一篇《统计信息查询视图|全方位认识 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:语句全表扫描查询的总次数<