mysql 优化之 performance Schema 常用查询 sql

use `performance_schema`

SELECT  DIGEST_TEXT,  COUNT_STAR,  FIRST_SEEN,  LAST_SEENFROM  events_statements_summary_by_digestORDER BY  COUNT_STAR DESC

SELECT  DIGEST_TEXT,  AVG_TIMER_WAITFROM  events_statements_summary_by_digestORDER BY  COUNT_STAR DESC

SELECT  DIGEST_TEXT,  SUM_SORT_ROWSFROM  events_statements_summary_by_digestORDER BY  COUNT_STAR DESC

SELECT  DIGEST_TEXT,  SUM_ROWS_EXAMINEDFROM  events_statements_summary_by_digestORDER BY  COUNT_STAR DESC

SELECT  DIGEST_TEXT,  SUM_CREATED_TMP_TABLES,  SUM_CREATED_TMP_DISK_TABLESFROM  events_statements_summary_by_digestORDER BY  COUNT_STAR DESC

SELECT  DIGEST_TEXT,  SUM_ROWS_SENTFROM  events_statements_summary_by_digestORDER BY  COUNT_STAR DESC

SELECT  file_name,  event_name,  SUM_NUMBER_OF_BYTES_READ,  SUM_NUMBER_OF_BYTES_WRITEFROM  file_summary_by_instanceORDER BY  SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC

SELECT  object_name,  COUNT_READ,  COUNT_WRITE,  COUNT_FETCH,  SUM_TIMER_WAITFROM  table_io_waits_summary_by_tableORDER BY  sum_timer_wait DESC

SELECT  OBJECT_NAME,  INDEX_NAME,  COUNT_FETCH,  COUNT_INSERT,  COUNT_UPDATE,  COUNT_DELETEFROM  table_io_waits_summary_by_index_usageORDER BY  SUM_TIMER_WAIT DESC

SELECT  OBJECT_SCHEMA,  OBJECT_NAME,  INDEX_NAMEFROM  table_io_waits_summary_by_index_usageWHERE  INDEX_NAME IS NOT NULLAND COUNT_STAR = 0AND OBJECT_SCHEMA <> 'mysql'ORDER BY  OBJECT_SCHEMA,OBJECT_NAME;

SELECT  EVENT_NAME,  COUNT_STAR,  SUM_TIMER_WAIT,  AVG_TIMER_WAITFROM  events_waits_summary_global_by_event_nameWHERE  event_name != 'idle'ORDER BY  SUM_TIMER_WAIT DESC

SELECT  EVENT_ID,  sql_textFROM  events_statements_historyWHERE  sqL_text LIKE '%count(*)%';

SELECT  event_id,  EVENT_NAME,  SOURCE,  TIMER_END - TIMER_STARTFROM  events_stages_history_longWHERE  NESTING_EVENT_ID = 1553

SELECT  event_id,  event_name,  source,  timer_wait,  object_name,  index_name,  operation,  nesting_event_idFROM  events_waits_history_longWHERE  nesting_event_id = 1553