MySQL常用资源消耗sql查询（2）

5.哪个SQL返回的结果集最多(net消耗)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,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_SENT desc LIMIT 1;

6.哪个SQL排序数最多(CPU消耗)
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,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_SORT_ROWS desc LIMIT 5;

7.哪个表、文件逻辑IO最多（热数据）
SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance
ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2;

8.哪个索引使用最多
SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY SUM_TIMER_WAIT DESC limit 1;

9.哪个索引没有使用过
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

10.哪个等待事件消耗的时间最多
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT
FROM events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 1;

11.通过performance_schema库得到数据库运行的统计信息，更好分析定位问题和完善监控信息
打开标准的innodb监控：
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

打开innodb的锁监控：
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

打开innodb表空间监控：
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

打开innodb表监控：
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB