索引情况

#1.查询冗余索引
select * from sys.schema_redundant_indexes;
#2.查询未使用过的情况
select * from sys.schema_unused_indexes;
#3.查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted 
from sys.schema_index_statistics where table_schema = 'dbname';

表相关

#1.查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io
from sys.schema_table_statistics group by table_schema,table_name order by io desc;
#2.查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
#3.查看全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1.监控sql执行频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2.监控使用了排序的sql
select db,exec_count,first_seen,last_seen,query 
from sys.statements_with_sorting limit 10;
#3.监控使用了临时表或者磁盘零时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables > 0 or tmp_disk_tables > 0
order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1.行锁阻塞情况
select * from sys.innodb_lock_waits;
#删除所有索引
CALL proc_drop_index('dbname','table');