mysql performance_schema主要几个性能优化表介绍

events_statements_summary_by_digest：按照事件的语句进行聚合。

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;