MySQL常用资源消耗sql查询

1.查看哪些sql执行最多
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY COUNT_STAR desc LIMIT 1;

2.哪个SQL平均响应时间最多
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY AVG_TIMER_WAIT desc LIMIT 1;

3.哪个SQL扫描的行数最多(IO消耗)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1;

4.哪个SQL使用的临时表最多
SELECT SCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema'
ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1;

 

————————————————

 

聚焦技术与人文，分享干货，共同成长更多内容请关注“数据与人”