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

最新推荐文章于 2024-07-17 09:31:09 发布

crpp0902

最新推荐文章于 2024-07-17 09:31:09 发布

阅读量1.7k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/crpp0902/article/details/77371973

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

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;