MySql性能监控performance_schema数据库常用查询语句

select DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

–2、哪类SQL的平均响应时间最多

SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

–3、哪类SQL排序记录数最多

select digest_text,sum_sort_rows from events_statements_summary_by_digest order by count_star desc

–4、哪类SQL扫描记录数最多

select digest_text,sum_rows_examined from events_statements_summary_by_digest ORDER BY count_star desc

–5、哪类SQL使用临时表最多

select digest_text,sum_created_tmp_tables,sum_created_tmp_disk_tables from events_statements_summary_by_digest ORDER BY count_star DESC

–6、哪类SQL返回结果集最多？

select digest_text,sum_rows_sent from events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

–7、哪个表物理IO最多？

select file_name,event_name,sum_number_of_bytes_read,sum_number_of_bytes_write from file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC

–8、哪个表逻辑IO最多？

select object_name,count_read,count_write,count_fetch,sum_timer_wait FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait desc

–9、哪个索引访问最多?

select object_name,index_name,count_fetch,count_insert,count_update,count_delete from table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC

–10、哪个索引从来没有用过？

select object_schema,object_name,index_name from 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

–11、哪个等待事件消耗事件最多

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

–12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息

select event_id,sql_text from events_statements_history where sql_text like '%count(*)%'

–12-2、查看每个阶段的事件消耗

select event_id,event_name,source,timer_end-timer_start from events_stages_history_long where NESTING_EVENT_ID=1553;

–12-3、查看每个阶段的锁等待情况

select event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id from events_waits_history_long where NESTING_EVENT_ID=1553