历史TOPSQL
在此页面可统计不同维度的TOPSQL，可选择不同的节点，以及根据时间进行筛选。
执行时间
select SCHEMA_NAME,（数据库名）
DIGEST,
(DIGEST_TEXT), （SQL语句）
COUNT_STAR, （执行次数）
round(AVG_TIMER_WAIT/1000000000,2), （平均时间）
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR, 2), （锁时间）
SUM_ERRORS,（错误次数）
SUM_WARNINGS,（告警次数）
round(SUM_ROWS_AFFECTED / COUNT_STAR),（影响行数）
round(SUM_ROWS_SENT / COUNT_STAR),（返回行数）
round(SUM_ROWS_EXAMINED / COUNT_STAR), （检查行数）
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),（临时磁盘表）
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),（临时表）
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),（全表连接）
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),（排序行数）
round(SUM_TIMER_WAIT/1000000000,2),（总时间）
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),（全表连接）
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)（全表扫描）
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in (' ','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_TIMER_WAIT>1000000000000 order by SUM_TIMER_WAIT desc
limit 50;
执行次数
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and COUNT_STAR>10 order by COUNT_STAR desc
limit 50;
平均执行时间
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_TIMER_WAIT>1000000000000 order by AVG_TIMER_WAIT desc,SUM_TIMER_WAIT desc
limit 50;
平均锁时间
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_LOCK_TIME>1000000000000 order by SUM_LOCK_TIME/COUNT_STAR desc,SUM_LOCK_TIME desc
limit 50;
平均检查行数
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_ROWS_EXAMINED>100 order by SUM_ROWS_EXAMINED/COUNT_STAR desc
limit 50;
平均返回行数
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_ROWS_SENT>100 order by SUM_ROWS_SENT/COUNT_STAR desc
limit 50;
平均排序行数
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_SORT_ROWS>100 order by SUM_SORT_ROWS/COUNT_STAR desc
limit 50;
平均临时表创建
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-23 13:26:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_CREATED_TMP_TABLES>1 order by SUM_CREATED_TMP_TABLES/COUNT_STAR desc,SUM_CREATED_TMP_TABLES desc
limit 50;
错误数汇总
select SCHEMA_NAME,
DIGEST,
(DIGEST_TEXT),
COUNT_STAR,
round(AVG_TIMER_WAIT/1000000000,2),
round(MAX_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000/COUNT_STAR),
SUM_ERRORS,
SUM_WARNINGS,
round(SUM_ROWS_AFFECTED / COUNT_STAR),
round(SUM_ROWS_SENT / COUNT_STAR),
round(SUM_ROWS_EXAMINED / COUNT_STAR),
round(SUM_CREATED_TMP_DISK_TABLES / COUNT_STAR, 2),
round(SUM_CREATED_TMP_TABLES / COUNT_STAR, 2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_SELECT_RANGE_CHECK / COUNT_STAR, 2),
round(SUM_SELECT_SCAN / COUNT_STAR, 2),
round(SUM_SORT_MERGE_PASSES / COUNT_STAR, 2),
round(SUM_SORT_ROWS / COUNT_STAR),
round(SUM_TIMER_WAIT/1000000000,2),
round(SUM_LOCK_TIME/1000000000,2),
round(SUM_SELECT_FULL_JOIN / COUNT_STAR, 2),
round(SUM_NO_INDEX_USED / COUNT_STAR, 2)
from performance_schema.events_statements_summary_by_digest
where COUNT_STAR > 0 and LAST_SEEN>=timestamp '2022-11-29 14:56:05' and substr(DIGEST_TEXT,1,5) in ('SELEC','DELET','UPDAT','INSER','CREAT','ALTER')
and locate('performance_schema',DIGEST_TEXT) = 0 and locate('information_schema',DIGEST_TEXT) = 0
and SUM_ERRORS>0 order by SUM_ERRORS desc,SUM_WARNINGS desc
limit 50;
