mysql performance_schemaの主ないくつかのパフォーマンス最適化テーブルの紹介

CREATE TABLE `events_statements_summary_by_digest` (
  `SCHEMA_NAME` varchar(64) DEFAULT NULL COMMENT '  ',
  `DIGEST` varchar(32) DEFAULT NULL COMMENT ' SQL_TEXT MD5   32    。   consumer      statement_digest  ，  NULL',
  `DIGEST_TEXT` longtext COMMENT '            ，  SQL    。   consumer      statement_digest  ，  NULL。',
  `COUNT_STAR` bigint(20) unsigned NOT NULL COMMENT '    ',
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT '      ',
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT '      ',
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT '      ',
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT '      ',
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL COMMENT '      ',
  `SUM_ERRORS` bigint(20) unsigned NOT NULL COMMENT '     ',
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL COMMENT '     ',
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL COMMENT '      ',
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL COMMENT '     ',
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL COMMENT '       ',
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL COMMENT '           ',
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL COMMENT '         ',
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL COMMENT '            ',
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL COMMENT '    range       ',
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL COMMENT '      range        ',
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL COMMENT '',
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL COMMENT '             ',
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL COMMENT '',
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL COMMENT '      ',
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL COMMENT '        ',
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL COMMENT '           ',
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL COMMENT '        ',
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL COMMENT '',
  `FIRST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '       ',
  `LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '        '
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;

  SQL            events_statements_summary_by_digest  ，   SQL     digest，      SQL            ：
  SQL    ：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest
 ORDER BY COUNT_STAR desc LIMIT 1\G





  SQL        ：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY AVG_TIMER_WAIT desc LIMIT 1\G




  SQL       ：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest
 ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1\G



  SQL        ：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY SUM_CREATED_TMP_DISK_TABLES、SUM_CREATED_TMP_TABLES desc LIMIT 1\G



  SQL        ：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY SUM_ROWS_SENT desc LIMIT 1\G



  SQL     ：
SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest 
ORDER BY SUM_SORT_ROWS desc LIMIT 1\G



   、    IO  （   ）：
SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY
 SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\G



        ：
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 limit 1;



         ：
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;



             ：
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;