[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能

转载

jinanxiaolaohu6 2024-01-10 09:58:09

文章标签 等待事件 sql MySQL 文章分类 Python 后端开发

概述

众所周知，MySQL数据库中的performance_schema的事件统计表中的统计数据计算的是累计值，如果想要计算某段时间的TOP SQL是不行的，这里考虑用函数定期取值存进中间表定期将累计值的结果存入A表，按sql_id分组查A表，筛选最后执行时间，然后直接最大值减最小值来实现需求了。
以下针对MySQL数据库8.0版本。

1、创建中间表

-- 报错记录error_log
CREATE TABLE sys.error_log (
  ERROR varchar(128) NOT NULL,
  AFFECT_ROWS bigint(20) unsigned NOT NULL,
  del_time datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='报错记录表';
-- 中间表ews_event(取PERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name:按照账户、主机、用户或线程统计的等待事件统计表)

CREATE TABLE sys.ews_event(

EVENT_NAME varchar(128) NOT NULL,

COUNT_STAR bigint(20) unsigned NOT NULL,

SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,

MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,

AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,

MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,

insert_date datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='按照账户、主机、用户或线程统计的等待事件统计表';
-- 中间表ews_sql(取performance_schema.events_statements_summary_by_digest:按照事件的语句进行聚合,抓取每条标准化语句有关的延迟、错误和查询量信息)

CREATE TABLE sys.ews_sql (

SCHEMA_NAME varchar(64) DEFAULT NULL,

DIGEST varchar(32) DEFAULT NULL,

DIGEST_TEXT longtext,

COUNT_STAR bigint(20) unsigned NOT NULL,

SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,

MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,

AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,

MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,

SUM_LOCK_TIME bigint(20) unsigned NOT NULL,

SUM_ERRORS bigint(20) unsigned NOT NULL,

SUM_WARNINGS bigint(20) unsigned NOT NULL,

SUM_ROWS_AFFECTED bigint(20) unsigned NOT NULL,

SUM_ROWS_SENT bigint(20) unsigned NOT NULL,

SUM_ROWS_EXAMINED bigint(20) unsigned NOT NULL,

SUM_CREATED_TMP_DISK_TABLES bigint(20) unsigned NOT NULL,

SUM_CREATED_TMP_TABLES bigint(20) unsigned NOT NULL,

SUM_SELECT_FULL_JOIN bigint(20) unsigned NOT NULL,

SUM_SELECT_FULL_RANGE_JOIN bigint(20) unsigned NOT NULL,

SUM_SELECT_RANGE bigint(20) unsigned NOT NULL,

SUM_SELECT_RANGE_CHECK bigint(20) unsigned NOT NULL,

SUM_SELECT_SCAN bigint(20) unsigned NOT NULL,

SUM_SORT_MERGE_PASSES bigint(20) unsigned NOT NULL,

SUM_SORT_RANGE bigint(20) unsigned NOT NULL,

SUM_SORT_ROWS bigint(20) unsigned NOT NULL,

SUM_SORT_SCAN bigint(20) unsigned NOT NULL,

SUM_NO_INDEX_USED bigint(20) unsigned NOT NULL,

SUM_NO_GOOD_INDEX_USED bigint(20) unsigned NOT NULL,

FIRST_SEEN datetime NOT NULL DEFAULT NOW(),

LAST_SEEN datetime NOT NULL DEFAULT NOW()
) ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT='按照事件的语句进行聚合,抓取每条标准化语句有关的延迟、错误和查询量信息';
-- 中间表ews_table_iowaits(取performance_schema.table_io_waits_summary_by_table:按照每个表进行统计的表I/O等待事件)

CREATE TABLE sys.ews_table_iowaits (

OBJECT_TYPE varchar(64) DEFAULT NULL,

OBJECT_SCHEMA varchar(64) DEFAULT NULL,

OBJECT_NAME varchar(64) DEFAULT NULL,

COUNT_STAR bigint(20) unsigned NOT NULL,

SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,

MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,

AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,

MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,

COUNT_READ bigint(20) unsigned NOT NULL,

SUM_TIMER_READ bigint(20) unsigned NOT NULL,

MIN_TIMER_READ bigint(20) unsigned NOT NULL,

AVG_TIMER_READ bigint(20) unsigned NOT NULL,

MAX_TIMER_READ bigint(20) unsigned NOT NULL,

COUNT_WRITE bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE bigint(20) unsigned NOT NULL,

COUNT_FETCH bigint(20) unsigned NOT NULL,

SUM_TIMER_FETCH bigint(20) unsigned NOT NULL,

MIN_TIMER_FETCH bigint(20) unsigned NOT NULL,

AVG_TIMER_FETCH bigint(20) unsigned NOT NULL,

MAX_TIMER_FETCH bigint(20) unsigned NOT NULL,

COUNT_INSERT bigint(20) unsigned NOT NULL,

SUM_TIMER_INSERT bigint(20) unsigned NOT NULL,

MIN_TIMER_INSERT bigint(20) unsigned NOT NULL,

AVG_TIMER_INSERT bigint(20) unsigned NOT NULL,

MAX_TIMER_INSERT bigint(20) unsigned NOT NULL,

COUNT_UPDATE bigint(20) unsigned NOT NULL,

SUM_TIMER_UPDATE bigint(20) unsigned NOT NULL,

MIN_TIMER_UPDATE bigint(20) unsigned NOT NULL,

AVG_TIMER_UPDATE bigint(20) unsigned NOT NULL,

MAX_TIMER_UPDATE bigint(20) unsigned NOT NULL,

COUNT_DELETE bigint(20) unsigned NOT NULL,

SUM_TIMER_DELETE bigint(20) unsigned NOT NULL,

MIN_TIMER_DELETE bigint(20) unsigned NOT NULL,

AVG_TIMER_DELETE bigint(20) unsigned NOT NULL,

MAX_TIMER_DELETE bigint(20) unsigned NOT NULL,

INSERT_DATE datetime NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按照每个表进行统计的表I/O等待事件';
-- 中间表ews_index_iowaits(取performance_schema.table_io_waits_summary_by_index_usage:按照每个索引进行统计的表I/O等待事件)

CREATE TABLE sys.ews_index_iowaits (

OBJECT_TYPE varchar(64) DEFAULT NULL,

OBJECT_SCHEMA varchar(64) DEFAULT NULL,

OBJECT_NAME varchar(64) DEFAULT NULL,

INDEX_NAME varchar(64) DEFAULT NULL,

COUNT_STAR bigint(20) unsigned NOT NULL,

SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,

MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,

AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,

MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,

COUNT_READ bigint(20) unsigned NOT NULL,

SUM_TIMER_READ bigint(20) unsigned NOT NULL,

MIN_TIMER_READ bigint(20) unsigned NOT NULL,

AVG_TIMER_READ bigint(20) unsigned NOT NULL,

MAX_TIMER_READ bigint(20) unsigned NOT NULL,

COUNT_WRITE bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE bigint(20) unsigned NOT NULL,

COUNT_FETCH bigint(20) unsigned NOT NULL,

SUM_TIMER_FETCH bigint(20) unsigned NOT NULL,

MIN_TIMER_FETCH bigint(20) unsigned NOT NULL,

AVG_TIMER_FETCH bigint(20) unsigned NOT NULL,

MAX_TIMER_FETCH bigint(20) unsigned NOT NULL,

COUNT_INSERT bigint(20) unsigned NOT NULL,

SUM_TIMER_INSERT bigint(20) unsigned NOT NULL,

MIN_TIMER_INSERT bigint(20) unsigned NOT NULL,

AVG_TIMER_INSERT bigint(20) unsigned NOT NULL,

MAX_TIMER_INSERT bigint(20) unsigned NOT NULL,

COUNT_UPDATE bigint(20) unsigned NOT NULL,

SUM_TIMER_UPDATE bigint(20) unsigned NOT NULL,

MIN_TIMER_UPDATE bigint(20) unsigned NOT NULL,

AVG_TIMER_UPDATE bigint(20) unsigned NOT NULL,

MAX_TIMER_UPDATE bigint(20) unsigned NOT NULL,

COUNT_DELETE bigint(20) unsigned NOT NULL,

SUM_TIMER_DELETE bigint(20) unsigned NOT NULL,

MIN_TIMER_DELETE bigint(20) unsigned NOT NULL,

AVG_TIMER_DELETE bigint(20) unsigned NOT NULL,

MAX_TIMER_DELETE bigint(20) unsigned NOT NULL,

INSERT_DATE datetime NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按照每个索引进行统计的表I/O等待事件';
-- 中间表sys.ews_table_lockwaits(取performance_schema.table_lock_waits_summary_by_table：按照每个表进行统计的表锁等待事件)

CREATE TABLE sys.ews_table_lockwaits (

OBJECT_TYPE varchar(64) DEFAULT NULL,

OBJECT_SCHEMA varchar(64) DEFAULT NULL,

OBJECT_NAME varchar(64) DEFAULT NULL,

COUNT_STAR bigint(20) unsigned NOT NULL,

SUM_TIMER_WAIT bigint(20) unsigned NOT NULL,

MIN_TIMER_WAIT bigint(20) unsigned NOT NULL,

AVG_TIMER_WAIT bigint(20) unsigned NOT NULL,

MAX_TIMER_WAIT bigint(20) unsigned NOT NULL,

COUNT_READ bigint(20) unsigned NOT NULL,

SUM_TIMER_READ bigint(20) unsigned NOT NULL,

MIN_TIMER_READ bigint(20) unsigned NOT NULL,

AVG_TIMER_READ bigint(20) unsigned NOT NULL,

MAX_TIMER_READ bigint(20) unsigned NOT NULL,

COUNT_WRITE bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE bigint(20) unsigned NOT NULL,

COUNT_READ_NORMAL bigint(20) unsigned NOT NULL,

SUM_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,

MIN_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,

AVG_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,

MAX_TIMER_READ_NORMAL bigint(20) unsigned NOT NULL,

COUNT_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,

SUM_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,

MIN_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,

AVG_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,

MAX_TIMER_READ_WITH_SHARED_LOCKS bigint(20) unsigned NOT NULL,

COUNT_READ_NO_INSERT bigint(20) unsigned NOT NULL,

SUM_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,

MIN_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,

AVG_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,

MAX_TIMER_READ_NO_INSERT bigint(20) unsigned NOT NULL,

COUNT_READ_EXTERNAL bigint(20) unsigned NOT NULL,

SUM_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,

MIN_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,

AVG_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,

MAX_TIMER_READ_EXTERNAL bigint(20) unsigned NOT NULL,

COUNT_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE_ALLOW_WRITE bigint(20) unsigned NOT NULL,

COUNT_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE_CONCURRENT_INSERT bigint(20) unsigned NOT NULL,

COUNT_WRITE_NORMAL bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE_NORMAL bigint(20) unsigned NOT NULL,

COUNT_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,

SUM_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,

MIN_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,

AVG_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,

MAX_TIMER_WRITE_EXTERNAL bigint(20) unsigned NOT NULL,

INSERT_DATE datetime NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='按照每个表进行统计的表锁等待事件';
-- 中间表sys.ews_file_io(取performance_schema.file_summary_by_instance:按照每个文件实例(对应具体的磁盘文件)进行统计的文件I/0等待事件)

CREATE TABLE sys.ews_file_io (

FILE_NAME varchar(512) NOT NULL,

EVENT_NAME varchar(128) NOT NULL,

COUNT_STAR bigint(20) unsigned NOT NULL,

SUM_NUMBER_OF_BYTES_READ bigint(20) NOT NULL,

SUM_NUMBER_OF_BYTES_WRITE bigint(20) NOT NULL,

INSERT_DATE datetime NOT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT='按照每个文件实例(对应具体的磁盘文件)进行统计的文件I/0等待事件';

2、自定义存储过程

use sys;
drop procedure if exists ews;
DELIMITER //

CREATE DEFINER = root @'%' PROCEDURE ews( ) BEGIN

DECLARE

affect_rows INT;

DECLARE

del_day INT;-- 长日志存留期

DECLARE

v_commit INT DEFAULT 2;-- 定义事务用，1为正常，-10为失败

DECLARE

msg text;-- 记录错误信息

-- 异常的时候msg捕获报错信息

DECLARE

CONTINUE HANDLER FOR SQLEXCEPTION BEGIN

get diagnostics CONDITION 1 msg = message_text;
    SET v_commit <span >=</span> - 10<span >;</span>
    
END<span >;</span>

SET @del_day <span >=</span> 8<span >;</span>
START TRANSACTION<span >;</span>-- 设置事务

-- Top 10 Event Summary
INSERT INTO sys.ews_event SELECT
ews.EVENT_NAME,
ews.COUNT_STAR,
round<span >(</span> ews.SUM_TIMER_WAIT / 1000000 <span >)</span> SUM_TIMER_WAIT,
round<span >(</span> ews.AVG_TIMER_WAIT / 1000000 <span >)</span> AVG_TIMER_WAIT,
round<span >(</span> ews.MIN_TIMER_WAIT / 1000000 <span >)</span> MIN_TIMER_WAIT,
round<span >(</span> ews.MAX_TIMER_WAIT / 1000000 <span >)</span> MAX_TIMER_WAIT,
now<span >(</span> <span >)</span> 
FROM
    PERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name ews 
WHERE
    ews.count_star <span >></span> 0 
    AND ews.event_name <span >!=</span> <span >'idle'</span><span >;</span>
    
-- TOP 10 SQL
INSERT INTO sys.ews_sql <span >select</span> ews.SCHEMA_NAME,
ews.DIGEST,
ews.DIGEST_TEXT,
ews.COUNT_STAR,
round<span >(</span> ews.SUM_TIMER_WAIT / 1000000 <span >)</span> SUM_TIMER_WAIT,
round<span >(</span> ews.MIN_TIMER_WAIT / 1000000 <span >)</span> MIN_TIMER_WAIT,
round<span >(</span> ews.AVG_TIMER_WAIT / 1000000 <span >)</span> AVG_TIMER_WAIT,
round<span >(</span> ews.MAX_TIMER_WAIT / 1000000 <span >)</span> MAX_TIMER_WAIT,
round<span >(</span> ews.SUM_LOCK_TIME / 1000000 <span >)</span> SUM_LOCK_TIME,
ews.SUM_ERRORS,
ews.SUM_WARNINGS,
ews.SUM_ROWS_AFFECTED,
ews.SUM_ROWS_SENT,
ews.SUM_ROWS_EXAMINED,
ews.SUM_CREATED_TMP_DISK_TABLES,
ews.SUM_CREATED_TMP_TABLES,
ews.SUM_SELECT_FULL_JOIN,
ews.SUM_SELECT_FULL_RANGE_JOIN,
ews.SUM_SELECT_RANGE,
ews.SUM_SELECT_RANGE_CHECK,
ews.SUM_SELECT_SCAN,
ews.SUM_SORT_MERGE_PASSES,
ews.SUM_SORT_RANGE,
ews.SUM_SORT_ROWS,
ews.SUM_SORT_SCAN,
ews.SUM_NO_INDEX_USED,
ews.SUM_NO_GOOD_INDEX_USED,
ews.FIRST_SEEN,
ews.LAST_SEEN 
FROM
    PERFORMANCE_SCHEMA.events_statements_summary_by_digest ews 
WHERE
    ews.SCHEMA_NAME NOT IN <span >(</span> <span >'perf_stat'</span>, <span >'mysql'</span>, <span >'sys'</span>, <span >'performance_schema'</span>, <span >'information_schema'</span> <span >)</span> 
    AND ews.count_star <span >></span> 0 
    AND ews.digest_text NOT LIKE <span >'SHOW%'</span> 
    AND ews.digest_text NOT LIKE <span >'USE%'</span> 
    AND ews.digest_text NOT LIKE <span >'SET%'</span> 
    AND ews.digest_text NOT LIKE <span >'SELECT @%'</span> 
    AND ews.digest_text NOT LIKE <span >'EXPLAIN%'</span> 
    AND ews.digest_text NOT LIKE <span >'DROP PROCEDURE%'</span> 
    AND ews.digest_text NOT LIKE <span >'CREATE PROCEDURE%'</span><span >;</span>
    

-- Top 10 Table By LOGICAL IO Wait
INSERT INTO sys.ews_table_iowaits <span >select</span>
        ews.OBJECT_TYPE,
        ews.OBJECT_SCHEMA,
        ews.OBJECT_NAME,
        ews.COUNT_STAR,
        round<span >(</span>ews.SUM_TIMER_WAIT/1000000<span >)</span> SUM_TIMER_WAIT,
        round<span >(</span>ews.MIN_TIMER_WAIT/1000000<span >)</span> MIN_TIMER_WAIT,
        round<span >(</span>ews.AVG_TIMER_WAIT/1000000<span >)</span> AVG_TIMER_WAIT,
        round<span >(</span>ews.MAX_TIMER_WAIT/1000000<span >)</span> MAX_TIMER_WAIT,
        ews.COUNT_READ,
        round<span >(</span>ews.SUM_TIMER_READ/1000000<span >)</span> SUM_TIMER_READ,
        round<span >(</span>ews.MIN_TIMER_READ/1000000<span >)</span> MIN_TIMER_READ,
        round<span >(</span>ews.AVG_TIMER_READ/1000000<span >)</span> AVG_TIMER_READ,
        round<span >(</span>ews.MAX_TIMER_READ/1000000<span >)</span> MAX_TIMER_READ,
        ews.COUNT_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE/1000000<span >)</span> SUM_TIMER_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE/1000000<span >)</span> MIN_TIMER_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE/1000000<span >)</span> AVG_TIMER_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE/1000000<span >)</span> MAX_TIMER_WRITE,
        ews.COUNT_FETCH,
        round<span >(</span>ews.SUM_TIMER_FETCH/1000000<span >)</span> SUM_TIMER_FETCH,
        round<span >(</span>ews.MIN_TIMER_FETCH/1000000<span >)</span> MIN_TIMER_FETCH,
        round<span >(</span>ews.AVG_TIMER_FETCH/1000000<span >)</span> AVG_TIMER_FETCH,
        round<span >(</span>ews.MAX_TIMER_FETCH/1000000<span >)</span> MAX_TIMER_FETCH,
        ews.COUNT_INSERT,
        round<span >(</span>ews.SUM_TIMER_INSERT/1000000<span >)</span> SUM_TIMER_INSERT,
        round<span >(</span>ews.MIN_TIMER_INSERT/1000000<span >)</span> MIN_TIMER_INSERT,
        round<span >(</span>ews.AVG_TIMER_INSERT/1000000<span >)</span> AVG_TIMER_INSERT,
        round<span >(</span>ews.MAX_TIMER_INSERT/1000000<span >)</span> MAX_TIMER_INSERT,
        ews.COUNT_UPDATE,
        round<span >(</span>ews.SUM_TIMER_UPDATE/1000000<span >)</span> SUM_TIMER_UPDATE,
        round<span >(</span>ews.MIN_TIMER_UPDATE/1000000<span >)</span> MIN_TIMER_UPDATE,
        round<span >(</span>ews.AVG_TIMER_UPDATE/1000000<span >)</span> AVG_TIMER_UPDATE,
        round<span >(</span>ews.MAX_TIMER_UPDATE/1000000<span >)</span> MAX_TIMER_UPDATE,
        ews.COUNT_DELETE,
        round<span >(</span>ews.SUM_TIMER_DELETE/1000000<span >)</span> SUM_TIMER_DELETE,
        round<span >(</span>ews.MIN_TIMER_DELETE/1000000<span >)</span> MIN_TIMER_DELETE,
        round<span >(</span>ews.AVG_TIMER_DELETE/1000000<span >)</span> AVG_TIMER_DELETE,
        round<span >(</span>ews.MAX_TIMER_DELETE/1000000<span >)</span> MAX_TIMER_DELETE,
        now<span >(</span> <span >)</span> 
    from performance_schema.table_io_waits_summary_by_table ews
    where ews.OBJECT_SCHEMA not <span >in</span> <span >(</span><span >'perf_stat'</span>,<span >'mysql'</span>,<span >'sys'</span>,<span >'performance_schema'</span>,<span >'information_schema'</span><span >)</span>
    and ews.count_star <span >></span> 0<span >;</span> 

-- Top 10 Index By LOGICAL IO Wait
INSERT INTO SYS.ews_index_iowaits <span >select</span> 
        ews.OBJECT_TYPE,
        ews.OBJECT_SCHEMA,
        ews.OBJECT_NAME,
        ews.INDEX_NAME,
        ews.COUNT_STAR,
        round<span >(</span>ews.SUM_TIMER_WAIT/1000000<span >)</span> SUM_TIMER_WAIT,
        round<span >(</span>ews.MIN_TIMER_WAIT/1000000<span >)</span> MIN_TIMER_WAIT,
        round<span >(</span>ews.AVG_TIMER_WAIT/1000000<span >)</span> AVG_TIMER_WAIT,
        round<span >(</span>ews.MAX_TIMER_WAIT/1000000<span >)</span> MAX_TIMER_WAIT,
        ews.COUNT_READ,
        round<span >(</span>ews.SUM_TIMER_READ/1000000<span >)</span> SUM_TIMER_READ,
        round<span >(</span>ews.MIN_TIMER_READ/1000000<span >)</span> MIN_TIMER_READ,
        round<span >(</span>ews.AVG_TIMER_READ/1000000<span >)</span> AVG_TIMER_READ,
        round<span >(</span>ews.MAX_TIMER_READ/1000000<span >)</span> MAX_TIMER_READ,
        ews.COUNT_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE/1000000<span >)</span> SUM_TIMER_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE/1000000<span >)</span> MIN_TIMER_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE/1000000<span >)</span> AVG_TIMER_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE/1000000<span >)</span> MAX_TIMER_WRITE,
        ews.COUNT_FETCH,
        round<span >(</span>ews.SUM_TIMER_FETCH/1000000<span >)</span> SUM_TIMER_FETCH,
        round<span >(</span>ews.MIN_TIMER_FETCH/1000000<span >)</span> MIN_TIMER_FETCH,
        round<span >(</span>ews.AVG_TIMER_FETCH/1000000<span >)</span> AVG_TIMER_FETCH,
        round<span >(</span>ews.MAX_TIMER_FETCH/1000000<span >)</span> MAX_TIMER_FETCH,
        ews.COUNT_INSERT,
        round<span >(</span>ews.SUM_TIMER_INSERT/1000000<span >)</span> SUM_TIMER_INSERT,
        round<span >(</span>ews.MIN_TIMER_INSERT/1000000<span >)</span> MIN_TIMER_INSERT,
        round<span >(</span>ews.AVG_TIMER_INSERT/1000000<span >)</span> AVG_TIMER_INSERT,
        round<span >(</span>ews.MAX_TIMER_INSERT/1000000<span >)</span> MAX_TIMER_INSERT,
        ews.COUNT_UPDATE,
        round<span >(</span>ews.SUM_TIMER_UPDATE/1000000<span >)</span> SUM_TIMER_UPDATE,
        round<span >(</span>ews.MIN_TIMER_UPDATE/1000000<span >)</span> MIN_TIMER_UPDATE,
        round<span >(</span>ews.AVG_TIMER_UPDATE/1000000<span >)</span> AVG_TIMER_UPDATE,
        round<span >(</span>ews.MAX_TIMER_UPDATE/1000000<span >)</span> MAX_TIMER_UPDATE,
        ews.COUNT_DELETE,
        round<span >(</span>ews.SUM_TIMER_DELETE/1000000<span >)</span> SUM_TIMER_DELETE,
        round<span >(</span>ews.MIN_TIMER_DELETE/1000000<span >)</span> MIN_TIMER_DELETE,
        round<span >(</span>ews.AVG_TIMER_DELETE/1000000<span >)</span> AVG_TIMER_DELETE,
        round<span >(</span>ews.MAX_TIMER_DELETE/1000000<span >)</span> MAX_TIMER_DELETE,
        now<span >(</span> <span >)</span> 
    from performance_schema.table_io_waits_summary_by_index_usage ews
    where ews.OBJECT_SCHEMA not <span >in</span> <span >(</span><span >'perf_stat'</span>,<span >'mysql'</span>,<span >'sys'</span>,<span >'performance_schema'</span>,<span >'information_schema'</span><span >)</span>
    and ews.count_star <span >></span> 0<span >;</span>
    
-- Top 10 Table By LOCKS WAITS
INSERT INTO sys.ews_table_lockwaits <span >select</span>
        ews.OBJECT_TYPE,
        ews.OBJECT_SCHEMA,
        ews.OBJECT_NAME,
        ews.COUNT_STAR,
        round<span >(</span>ews.SUM_TIMER_WAIT/1000000<span >)</span> SUM_TIMER_WAIT,
        round<span >(</span>ews.MIN_TIMER_WAIT/1000000<span >)</span> MIN_TIMER_WAIT,
        round<span >(</span>ews.AVG_TIMER_WAIT/1000000<span >)</span> AVG_TIMER_WAIT,
        round<span >(</span>ews.MAX_TIMER_WAIT/1000000<span >)</span> MAX_TIMER_WAIT,
        ews.COUNT_READ,
        round<span >(</span>ews.SUM_TIMER_READ/1000000<span >)</span> SUM_TIMER_READ,
        round<span >(</span>ews.MIN_TIMER_READ/1000000<span >)</span> MIN_TIMER_READ,
        round<span >(</span>ews.AVG_TIMER_READ/1000000<span >)</span> AVG_TIMER_READ,
        round<span >(</span>ews.MAX_TIMER_READ/1000000<span >)</span> MAX_TIMER_READ,
        ews.COUNT_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE/1000000<span >)</span> SUM_TIMER_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE/1000000<span >)</span> MIN_TIMER_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE/1000000<span >)</span> AVG_TIMER_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE/1000000<span >)</span> MAX_TIMER_WRITE,
        ews.COUNT_READ_NORMAL,
        round<span >(</span>ews.SUM_TIMER_READ_NORMAL/1000000<span >)</span> SUM_TIMER_READ_NORMAL,
        round<span >(</span>ews.MIN_TIMER_READ_NORMAL/1000000<span >)</span> MIN_TIMER_READ_NORMAL,
        round<span >(</span>ews.AVG_TIMER_READ_NORMAL/1000000<span >)</span> AVG_TIMER_READ_NORMAL,
        round<span >(</span>ews.MAX_TIMER_READ_NORMAL/1000000<span >)</span> MAX_TIMER_READ_NORMAL,
        ews.COUNT_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.SUM_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> SUM_TIMER_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.MIN_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> MIN_TIMER_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.AVG_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> AVG_TIMER_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.MAX_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> MAX_TIMER_READ_WITH_SHARED_LOCKS,
        ews.COUNT_READ_NO_INSERT,
        round<span >(</span>ews.SUM_TIMER_READ_NO_INSERT/1000000<span >)</span> SUM_TIMER_READ_NO_INSERT,
        round<span >(</span>ews.MIN_TIMER_READ_NO_INSERT/1000000<span >)</span> MIN_TIMER_READ_NO_INSERT,
        round<span >(</span>ews.AVG_TIMER_READ_NO_INSERT/1000000<span >)</span> AVG_TIMER_READ_NO_INSERT,
        round<span >(</span>ews.MAX_TIMER_READ_NO_INSERT/1000000<span >)</span> MAX_TIMER_READ_NO_INSERT,
        ews.COUNT_READ_EXTERNAL,
        round<span >(</span>ews.SUM_TIMER_READ_EXTERNAL/1000000<span >)</span> SUM_TIMER_READ_EXTERNAL,
        round<span >(</span>ews.MIN_TIMER_READ_EXTERNAL/1000000<span >)</span> MIN_TIMER_READ_EXTERNAL,
        round<span >(</span>ews.AVG_TIMER_READ_EXTERNAL/1000000<span >)</span> AVG_TIMER_READ_EXTERNAL,
        round<span >(</span>ews.MAX_TIMER_READ_EXTERNAL/1000000<span >)</span> MAX_TIMER_READ_EXTERNAL,
        ews.COUNT_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> SUM_TIMER_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> MIN_TIMER_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> AVG_TIMER_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> MAX_TIMER_WRITE_ALLOW_WRITE,
        ews.COUNT_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.SUM_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> SUM_TIMER_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.MIN_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> MIN_TIMER_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.AVG_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> AVG_TIMER_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.MAX_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> MAX_TIMER_WRITE_CONCURRENT_INSERT,
        ews.COUNT_WRITE_NORMAL,
        round<span >(</span>ews.SUM_TIMER_WRITE_NORMAL/1000000<span >)</span> SUM_TIMER_WRITE_NORMAL,
        round<span >(</span>ews.MIN_TIMER_WRITE_NORMAL/1000000<span >)</span> MIN_TIMER_WRITE_NORMAL,
        round<span >(</span>ews.AVG_TIMER_WRITE_NORMAL/1000000<span >)</span> AVG_TIMER_WRITE_NORMAL,
        round<span >(</span>ews.MAX_TIMER_WRITE_NORMAL/1000000<span >)</span> MAX_TIMER_WRITE_NORMAL,
        ews.COUNT_WRITE_EXTERNAL,
        round<span >(</span>ews.SUM_TIMER_WRITE_EXTERNAL/1000000<span >)</span> SUM_TIMER_WRITE_EXTERNAL,
        round<span >(</span>ews.MIN_TIMER_WRITE_EXTERNAL/1000000<span >)</span> MIN_TIMER_WRITE_EXTERNAL,
        round<span >(</span>ews.AVG_TIMER_WRITE_EXTERNAL/1000000<span >)</span> AVG_TIMER_WRITE_EXTERNAL,
        round<span >(</span>ews.MAX_TIMER_WRITE_EXTERNAL/1000000<span >)</span> MAX_TIMER_WRITE_EXTERNAL,
        now<span >(</span> <span >)</span> 
    from performance_schema.table_lock_waits_summary_by_table ews
    where ews.OBJECT_SCHEMA not <span >in</span> <span >(</span><span >'perf_stat'</span>,<span >'mysql'</span>,<span >'sys'</span>,<span >'performance_schema'</span>,<span >'information_schema'</span><span >)</span>
    and ews.count_star <span >></span> 0<span >;</span> 

-- TOP 10 Table By PHYSICAL IO Wait
INSERT INTO sys.ews_file_io SELECT
       file_name,
       event_name,
       COUNT_STAR,
       SUM_NUMBER_OF_BYTES_READ,
       SUM_NUMBER_OF_BYTES_WRITE,
       NOW<span >(</span> <span >)</span> 
FROM performance_schema.file_summary_by_instance<span >;</span>

COMMIT<span >;</span>-- 异常回滚且记录日志

START TRANSACTION<span >;</span>-- 设置删除事务
delete from sys.ews_event where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_event成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>   

delete from sys.ews_sql where last_seen <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_sql成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_table_iowaits where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_table_iowaits成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_index_iowaits where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_index_iowaits成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_table_lockwaits where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_table_lockwaits成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_file_io where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_file_io成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span> 

COMMIT<span >;</span>-- 异常回滚且记录日志

IF
    v_commit <span >=</span> - 10 THEN
        ROLLBACK<span >;</span>
    INSERT INTO sys.error_log VALUES <span >(</span> msg, 0, now<span >(</span> <span >)</span> <span >)</span><span >;</span>
END IF<span >;</span>

END //

DELIMITER;

    SET v_commit <span >=</span> - 10<span >;</span>
    
END<span >;</span>

SET @del_day <span >=</span> 8<span >;</span>
START TRANSACTION<span >;</span>-- 设置事务

-- Top 10 Event Summary
INSERT INTO sys.ews_event SELECT
ews.EVENT_NAME,
ews.COUNT_STAR,
round<span >(</span> ews.SUM_TIMER_WAIT / 1000000 <span >)</span> SUM_TIMER_WAIT,
round<span >(</span> ews.AVG_TIMER_WAIT / 1000000 <span >)</span> AVG_TIMER_WAIT,
round<span >(</span> ews.MIN_TIMER_WAIT / 1000000 <span >)</span> MIN_TIMER_WAIT,
round<span >(</span> ews.MAX_TIMER_WAIT / 1000000 <span >)</span> MAX_TIMER_WAIT,
now<span >(</span> <span >)</span> 
FROM
    PERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name ews 
WHERE
    ews.count_star <span >></span> 0 
    AND ews.event_name <span >!=</span> <span >'idle'</span><span >;</span>
    
-- TOP 10 SQL
INSERT INTO sys.ews_sql <span >select</span> ews.SCHEMA_NAME,
ews.DIGEST,
ews.DIGEST_TEXT,
ews.COUNT_STAR,
round<span >(</span> ews.SUM_TIMER_WAIT / 1000000 <span >)</span> SUM_TIMER_WAIT,
round<span >(</span> ews.MIN_TIMER_WAIT / 1000000 <span >)</span> MIN_TIMER_WAIT,
round<span >(</span> ews.AVG_TIMER_WAIT / 1000000 <span >)</span> AVG_TIMER_WAIT,
round<span >(</span> ews.MAX_TIMER_WAIT / 1000000 <span >)</span> MAX_TIMER_WAIT,
round<span >(</span> ews.SUM_LOCK_TIME / 1000000 <span >)</span> SUM_LOCK_TIME,
ews.SUM_ERRORS,
ews.SUM_WARNINGS,
ews.SUM_ROWS_AFFECTED,
ews.SUM_ROWS_SENT,
ews.SUM_ROWS_EXAMINED,
ews.SUM_CREATED_TMP_DISK_TABLES,
ews.SUM_CREATED_TMP_TABLES,
ews.SUM_SELECT_FULL_JOIN,
ews.SUM_SELECT_FULL_RANGE_JOIN,
ews.SUM_SELECT_RANGE,
ews.SUM_SELECT_RANGE_CHECK,
ews.SUM_SELECT_SCAN,
ews.SUM_SORT_MERGE_PASSES,
ews.SUM_SORT_RANGE,
ews.SUM_SORT_ROWS,
ews.SUM_SORT_SCAN,
ews.SUM_NO_INDEX_USED,
ews.SUM_NO_GOOD_INDEX_USED,
ews.FIRST_SEEN,
ews.LAST_SEEN 
FROM
    PERFORMANCE_SCHEMA.events_statements_summary_by_digest ews 
WHERE
    ews.SCHEMA_NAME NOT IN <span >(</span> <span >'perf_stat'</span>, <span >'mysql'</span>, <span >'sys'</span>, <span >'performance_schema'</span>, <span >'information_schema'</span> <span >)</span> 
    AND ews.count_star <span >></span> 0 
    AND ews.digest_text NOT LIKE <span >'SHOW%'</span> 
    AND ews.digest_text NOT LIKE <span >'USE%'</span> 
    AND ews.digest_text NOT LIKE <span >'SET%'</span> 
    AND ews.digest_text NOT LIKE <span >'SELECT @%'</span> 
    AND ews.digest_text NOT LIKE <span >'EXPLAIN%'</span> 
    AND ews.digest_text NOT LIKE <span >'DROP PROCEDURE%'</span> 
    AND ews.digest_text NOT LIKE <span >'CREATE PROCEDURE%'</span><span >;</span>
    

-- Top 10 Table By LOGICAL IO Wait
INSERT INTO sys.ews_table_iowaits <span >select</span>
        ews.OBJECT_TYPE,
        ews.OBJECT_SCHEMA,
        ews.OBJECT_NAME,
        ews.COUNT_STAR,
        round<span >(</span>ews.SUM_TIMER_WAIT/1000000<span >)</span> SUM_TIMER_WAIT,
        round<span >(</span>ews.MIN_TIMER_WAIT/1000000<span >)</span> MIN_TIMER_WAIT,
        round<span >(</span>ews.AVG_TIMER_WAIT/1000000<span >)</span> AVG_TIMER_WAIT,
        round<span >(</span>ews.MAX_TIMER_WAIT/1000000<span >)</span> MAX_TIMER_WAIT,
        ews.COUNT_READ,
        round<span >(</span>ews.SUM_TIMER_READ/1000000<span >)</span> SUM_TIMER_READ,
        round<span >(</span>ews.MIN_TIMER_READ/1000000<span >)</span> MIN_TIMER_READ,
        round<span >(</span>ews.AVG_TIMER_READ/1000000<span >)</span> AVG_TIMER_READ,
        round<span >(</span>ews.MAX_TIMER_READ/1000000<span >)</span> MAX_TIMER_READ,
        ews.COUNT_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE/1000000<span >)</span> SUM_TIMER_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE/1000000<span >)</span> MIN_TIMER_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE/1000000<span >)</span> AVG_TIMER_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE/1000000<span >)</span> MAX_TIMER_WRITE,
        ews.COUNT_FETCH,
        round<span >(</span>ews.SUM_TIMER_FETCH/1000000<span >)</span> SUM_TIMER_FETCH,
        round<span >(</span>ews.MIN_TIMER_FETCH/1000000<span >)</span> MIN_TIMER_FETCH,
        round<span >(</span>ews.AVG_TIMER_FETCH/1000000<span >)</span> AVG_TIMER_FETCH,
        round<span >(</span>ews.MAX_TIMER_FETCH/1000000<span >)</span> MAX_TIMER_FETCH,
        ews.COUNT_INSERT,
        round<span >(</span>ews.SUM_TIMER_INSERT/1000000<span >)</span> SUM_TIMER_INSERT,
        round<span >(</span>ews.MIN_TIMER_INSERT/1000000<span >)</span> MIN_TIMER_INSERT,
        round<span >(</span>ews.AVG_TIMER_INSERT/1000000<span >)</span> AVG_TIMER_INSERT,
        round<span >(</span>ews.MAX_TIMER_INSERT/1000000<span >)</span> MAX_TIMER_INSERT,
        ews.COUNT_UPDATE,
        round<span >(</span>ews.SUM_TIMER_UPDATE/1000000<span >)</span> SUM_TIMER_UPDATE,
        round<span >(</span>ews.MIN_TIMER_UPDATE/1000000<span >)</span> MIN_TIMER_UPDATE,
        round<span >(</span>ews.AVG_TIMER_UPDATE/1000000<span >)</span> AVG_TIMER_UPDATE,
        round<span >(</span>ews.MAX_TIMER_UPDATE/1000000<span >)</span> MAX_TIMER_UPDATE,
        ews.COUNT_DELETE,
        round<span >(</span>ews.SUM_TIMER_DELETE/1000000<span >)</span> SUM_TIMER_DELETE,
        round<span >(</span>ews.MIN_TIMER_DELETE/1000000<span >)</span> MIN_TIMER_DELETE,
        round<span >(</span>ews.AVG_TIMER_DELETE/1000000<span >)</span> AVG_TIMER_DELETE,
        round<span >(</span>ews.MAX_TIMER_DELETE/1000000<span >)</span> MAX_TIMER_DELETE,
        now<span >(</span> <span >)</span> 
    from performance_schema.table_io_waits_summary_by_table ews
    where ews.OBJECT_SCHEMA not <span >in</span> <span >(</span><span >'perf_stat'</span>,<span >'mysql'</span>,<span >'sys'</span>,<span >'performance_schema'</span>,<span >'information_schema'</span><span >)</span>
    and ews.count_star <span >></span> 0<span >;</span> 

-- Top 10 Index By LOGICAL IO Wait
INSERT INTO SYS.ews_index_iowaits <span >select</span> 
        ews.OBJECT_TYPE,
        ews.OBJECT_SCHEMA,
        ews.OBJECT_NAME,
        ews.INDEX_NAME,
        ews.COUNT_STAR,
        round<span >(</span>ews.SUM_TIMER_WAIT/1000000<span >)</span> SUM_TIMER_WAIT,
        round<span >(</span>ews.MIN_TIMER_WAIT/1000000<span >)</span> MIN_TIMER_WAIT,
        round<span >(</span>ews.AVG_TIMER_WAIT/1000000<span >)</span> AVG_TIMER_WAIT,
        round<span >(</span>ews.MAX_TIMER_WAIT/1000000<span >)</span> MAX_TIMER_WAIT,
        ews.COUNT_READ,
        round<span >(</span>ews.SUM_TIMER_READ/1000000<span >)</span> SUM_TIMER_READ,
        round<span >(</span>ews.MIN_TIMER_READ/1000000<span >)</span> MIN_TIMER_READ,
        round<span >(</span>ews.AVG_TIMER_READ/1000000<span >)</span> AVG_TIMER_READ,
        round<span >(</span>ews.MAX_TIMER_READ/1000000<span >)</span> MAX_TIMER_READ,
        ews.COUNT_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE/1000000<span >)</span> SUM_TIMER_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE/1000000<span >)</span> MIN_TIMER_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE/1000000<span >)</span> AVG_TIMER_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE/1000000<span >)</span> MAX_TIMER_WRITE,
        ews.COUNT_FETCH,
        round<span >(</span>ews.SUM_TIMER_FETCH/1000000<span >)</span> SUM_TIMER_FETCH,
        round<span >(</span>ews.MIN_TIMER_FETCH/1000000<span >)</span> MIN_TIMER_FETCH,
        round<span >(</span>ews.AVG_TIMER_FETCH/1000000<span >)</span> AVG_TIMER_FETCH,
        round<span >(</span>ews.MAX_TIMER_FETCH/1000000<span >)</span> MAX_TIMER_FETCH,
        ews.COUNT_INSERT,
        round<span >(</span>ews.SUM_TIMER_INSERT/1000000<span >)</span> SUM_TIMER_INSERT,
        round<span >(</span>ews.MIN_TIMER_INSERT/1000000<span >)</span> MIN_TIMER_INSERT,
        round<span >(</span>ews.AVG_TIMER_INSERT/1000000<span >)</span> AVG_TIMER_INSERT,
        round<span >(</span>ews.MAX_TIMER_INSERT/1000000<span >)</span> MAX_TIMER_INSERT,
        ews.COUNT_UPDATE,
        round<span >(</span>ews.SUM_TIMER_UPDATE/1000000<span >)</span> SUM_TIMER_UPDATE,
        round<span >(</span>ews.MIN_TIMER_UPDATE/1000000<span >)</span> MIN_TIMER_UPDATE,
        round<span >(</span>ews.AVG_TIMER_UPDATE/1000000<span >)</span> AVG_TIMER_UPDATE,
        round<span >(</span>ews.MAX_TIMER_UPDATE/1000000<span >)</span> MAX_TIMER_UPDATE,
        ews.COUNT_DELETE,
        round<span >(</span>ews.SUM_TIMER_DELETE/1000000<span >)</span> SUM_TIMER_DELETE,
        round<span >(</span>ews.MIN_TIMER_DELETE/1000000<span >)</span> MIN_TIMER_DELETE,
        round<span >(</span>ews.AVG_TIMER_DELETE/1000000<span >)</span> AVG_TIMER_DELETE,
        round<span >(</span>ews.MAX_TIMER_DELETE/1000000<span >)</span> MAX_TIMER_DELETE,
        now<span >(</span> <span >)</span> 
    from performance_schema.table_io_waits_summary_by_index_usage ews
    where ews.OBJECT_SCHEMA not <span >in</span> <span >(</span><span >'perf_stat'</span>,<span >'mysql'</span>,<span >'sys'</span>,<span >'performance_schema'</span>,<span >'information_schema'</span><span >)</span>
    and ews.count_star <span >></span> 0<span >;</span>
    
-- Top 10 Table By LOCKS WAITS
INSERT INTO sys.ews_table_lockwaits <span >select</span>
        ews.OBJECT_TYPE,
        ews.OBJECT_SCHEMA,
        ews.OBJECT_NAME,
        ews.COUNT_STAR,
        round<span >(</span>ews.SUM_TIMER_WAIT/1000000<span >)</span> SUM_TIMER_WAIT,
        round<span >(</span>ews.MIN_TIMER_WAIT/1000000<span >)</span> MIN_TIMER_WAIT,
        round<span >(</span>ews.AVG_TIMER_WAIT/1000000<span >)</span> AVG_TIMER_WAIT,
        round<span >(</span>ews.MAX_TIMER_WAIT/1000000<span >)</span> MAX_TIMER_WAIT,
        ews.COUNT_READ,
        round<span >(</span>ews.SUM_TIMER_READ/1000000<span >)</span> SUM_TIMER_READ,
        round<span >(</span>ews.MIN_TIMER_READ/1000000<span >)</span> MIN_TIMER_READ,
        round<span >(</span>ews.AVG_TIMER_READ/1000000<span >)</span> AVG_TIMER_READ,
        round<span >(</span>ews.MAX_TIMER_READ/1000000<span >)</span> MAX_TIMER_READ,
        ews.COUNT_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE/1000000<span >)</span> SUM_TIMER_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE/1000000<span >)</span> MIN_TIMER_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE/1000000<span >)</span> AVG_TIMER_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE/1000000<span >)</span> MAX_TIMER_WRITE,
        ews.COUNT_READ_NORMAL,
        round<span >(</span>ews.SUM_TIMER_READ_NORMAL/1000000<span >)</span> SUM_TIMER_READ_NORMAL,
        round<span >(</span>ews.MIN_TIMER_READ_NORMAL/1000000<span >)</span> MIN_TIMER_READ_NORMAL,
        round<span >(</span>ews.AVG_TIMER_READ_NORMAL/1000000<span >)</span> AVG_TIMER_READ_NORMAL,
        round<span >(</span>ews.MAX_TIMER_READ_NORMAL/1000000<span >)</span> MAX_TIMER_READ_NORMAL,
        ews.COUNT_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.SUM_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> SUM_TIMER_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.MIN_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> MIN_TIMER_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.AVG_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> AVG_TIMER_READ_WITH_SHARED_LOCKS,
        round<span >(</span>ews.MAX_TIMER_READ_WITH_SHARED_LOCKS/1000000<span >)</span> MAX_TIMER_READ_WITH_SHARED_LOCKS,
        ews.COUNT_READ_NO_INSERT,
        round<span >(</span>ews.SUM_TIMER_READ_NO_INSERT/1000000<span >)</span> SUM_TIMER_READ_NO_INSERT,
        round<span >(</span>ews.MIN_TIMER_READ_NO_INSERT/1000000<span >)</span> MIN_TIMER_READ_NO_INSERT,
        round<span >(</span>ews.AVG_TIMER_READ_NO_INSERT/1000000<span >)</span> AVG_TIMER_READ_NO_INSERT,
        round<span >(</span>ews.MAX_TIMER_READ_NO_INSERT/1000000<span >)</span> MAX_TIMER_READ_NO_INSERT,
        ews.COUNT_READ_EXTERNAL,
        round<span >(</span>ews.SUM_TIMER_READ_EXTERNAL/1000000<span >)</span> SUM_TIMER_READ_EXTERNAL,
        round<span >(</span>ews.MIN_TIMER_READ_EXTERNAL/1000000<span >)</span> MIN_TIMER_READ_EXTERNAL,
        round<span >(</span>ews.AVG_TIMER_READ_EXTERNAL/1000000<span >)</span> AVG_TIMER_READ_EXTERNAL,
        round<span >(</span>ews.MAX_TIMER_READ_EXTERNAL/1000000<span >)</span> MAX_TIMER_READ_EXTERNAL,
        ews.COUNT_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.SUM_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> SUM_TIMER_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.MIN_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> MIN_TIMER_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.AVG_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> AVG_TIMER_WRITE_ALLOW_WRITE,
        round<span >(</span>ews.MAX_TIMER_WRITE_ALLOW_WRITE/1000000<span >)</span> MAX_TIMER_WRITE_ALLOW_WRITE,
        ews.COUNT_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.SUM_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> SUM_TIMER_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.MIN_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> MIN_TIMER_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.AVG_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> AVG_TIMER_WRITE_CONCURRENT_INSERT,
        round<span >(</span>ews.MAX_TIMER_WRITE_CONCURRENT_INSERT/1000000<span >)</span> MAX_TIMER_WRITE_CONCURRENT_INSERT,
        ews.COUNT_WRITE_NORMAL,
        round<span >(</span>ews.SUM_TIMER_WRITE_NORMAL/1000000<span >)</span> SUM_TIMER_WRITE_NORMAL,
        round<span >(</span>ews.MIN_TIMER_WRITE_NORMAL/1000000<span >)</span> MIN_TIMER_WRITE_NORMAL,
        round<span >(</span>ews.AVG_TIMER_WRITE_NORMAL/1000000<span >)</span> AVG_TIMER_WRITE_NORMAL,
        round<span >(</span>ews.MAX_TIMER_WRITE_NORMAL/1000000<span >)</span> MAX_TIMER_WRITE_NORMAL,
        ews.COUNT_WRITE_EXTERNAL,
        round<span >(</span>ews.SUM_TIMER_WRITE_EXTERNAL/1000000<span >)</span> SUM_TIMER_WRITE_EXTERNAL,
        round<span >(</span>ews.MIN_TIMER_WRITE_EXTERNAL/1000000<span >)</span> MIN_TIMER_WRITE_EXTERNAL,
        round<span >(</span>ews.AVG_TIMER_WRITE_EXTERNAL/1000000<span >)</span> AVG_TIMER_WRITE_EXTERNAL,
        round<span >(</span>ews.MAX_TIMER_WRITE_EXTERNAL/1000000<span >)</span> MAX_TIMER_WRITE_EXTERNAL,
        now<span >(</span> <span >)</span> 
    from performance_schema.table_lock_waits_summary_by_table ews
    where ews.OBJECT_SCHEMA not <span >in</span> <span >(</span><span >'perf_stat'</span>,<span >'mysql'</span>,<span >'sys'</span>,<span >'performance_schema'</span>,<span >'information_schema'</span><span >)</span>
    and ews.count_star <span >></span> 0<span >;</span> 

-- TOP 10 Table By PHYSICAL IO Wait
INSERT INTO sys.ews_file_io SELECT
       file_name,
       event_name,
       COUNT_STAR,
       SUM_NUMBER_OF_BYTES_READ,
       SUM_NUMBER_OF_BYTES_WRITE,
       NOW<span >(</span> <span >)</span> 
FROM performance_schema.file_summary_by_instance<span >;</span>

COMMIT<span >;</span>-- 异常回滚且记录日志

START TRANSACTION<span >;</span>-- 设置删除事务
delete from sys.ews_event where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_event成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>   

delete from sys.ews_sql where last_seen <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_sql成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_table_iowaits where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_table_iowaits成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_index_iowaits where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_index_iowaits成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_table_lockwaits where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_table_lockwaits成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span>

delete from sys.ews_file_io where insert_date <span ><</span> DATE_SUB<span >(</span>CURDATE<span >(</span><span >)</span>,INTERVAL @del_day DAY<span >)</span><span >;</span>
<span >select</span> ROW_COUNT<span >(</span><span >)</span> into @affect_rows<span >;</span>
insert into sys.error_log values<span >(</span><span >'删除sys.ews_file_io成功'</span>,@affect_rows,now<span >(</span><span >))</span><span >;</span> 

COMMIT<span >;</span>-- 异常回滚且记录日志

IF
    v_commit <span >=</span> - 10 THEN
        ROLLBACK<span >;</span>
    INSERT INTO sys.error_log VALUES <span >(</span> msg, 0, now<span >(</span> <span >)</span> <span >)</span><span >;</span>
END IF<span >;</span>

3、定时任务

-- 定时执行存储过程
USE SYS;
DROP EVENT IF EXISTS ews;
CREATE EVENT ews ON SCHEDULE EVERY 10 MINUTE DO
CALL ews ( );

4、创建索引

考虑到后面查询基本是按日期做筛选来查的，所以建这个索引。

create index idx_date1 on ews_event(insert_date);
create index idx_date2 on ews_sql(last_seen);
create index idx_date3 on ews_table_iowaits(insert_date);
create index idx_date4 on ews_index_iowaits(insert_date);
create index idx_date5 on ews_table_lockwaits(insert_date);
create index idx_date6 on ews_file_io(insert_date);

5、数据查询

5.1、最近30分钟排名前十的等待事件

SELECT
    event_name "等待事件",
    max( count_star ) - min( count_star)+1 "等待次数",
    round( ( max( sum_timer_wait ) - min( sum_timer_wait ) ) / 1000 ) "总时长(s)↓",
    concat(
    round(
         (max( sum_timer_wait ) - min( sum_timer_wait ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev  FROM  sys.ews_event 
                 WHERE  insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY  event_name ) c 
            ) * 100,
         2),
  '%') "总时长占比",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均等待时间(ms)" 
FROM    sys.ews_event 
WHERE   insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    event_name 
ORDER BY    max( sum_timer_wait ) - min( sum_timer_wait ) DESC  LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_MySQL

5.2、最近30分钟总耗时排名前十的sql

SELECT
    DIGEST "sql_id",
    SCHEMA_NAME "数据库",
    max( COUNT_STAR ) - min( COUNT_STAR )+1 "执行次数",
    round( ( max( sum_timer_wait ) - min( sum_timer_wait ) ) / 1000 ) "总时长(s)↓",
    concat(
    round(
         (max( sum_timer_wait ) - min( sum_timer_wait ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev  FROM  sys.ews_sql 
                 WHERE  last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY    DIGEST) c 
            ) * 100,
         2),
  '%') "总时长占比",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均时长(ms)",
    max( SUM_ROWS_EXAMINED ) - min( SUM_ROWS_EXAMINED ) "总检查行数",
    max( LAST_SEEN ) "最后执行时间",
    DIGEST_TEXT "sql语句" 
FROM    sys.ews_sql 
WHERE   last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    DIGEST 
ORDER BY    max( sum_timer_wait ) - min( sum_timer_wait ) DESC  LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_MySQL_02

5.3、最近30分钟执行次数排名前十的sql

SELECT
    SCHEMA_NAME "数据库",
    DIGEST "sql_id",
    max( COUNT_STAR ) - min( COUNT_STAR ) "执行次数↓",
    concat(
    round(
         (max( COUNT_STAR ) - min( COUNT_STAR ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(COUNT_STAR) - min( COUNT_STAR ) ) ev  FROM  sys.ews_sql 
                 WHERE  last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY    DIGEST) c 
            ) * 100,
         2),
  '%') "执行次数占比",
    max( sum_timer_wait ) - min( sum_timer_wait ) "总时长(ms)",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均时长(ms)",
    max( SUM_ROWS_EXAMINED ) - min( SUM_ROWS_EXAMINED ) "总检查行数",
    max( LAST_SEEN ) "最后执行时间",
    DIGEST_TEXT "sql语句" 
FROM    sys.ews_sql 
WHERE   last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    DIGEST 
ORDER BY    max( COUNT_STAR ) - min( COUNT_STAR ) DESC  LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_等待事件_03

5.4、最近30分钟平均耗时最长的前十sql语句

SELECT
    DIGEST "sql_id",
    SCHEMA_NAME "数据库",
    max( COUNT_STAR ) - min( COUNT_STAR ) "执行次数",
    round( ( max( sum_timer_wait ) - min( sum_timer_wait ) ) / 1000 ) "总时长(s)",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均时长(ms)↓",
    concat(
    round(
         (max( avg_timer_wait ) - min( avg_timer_wait ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(avg_timer_wait) - min( avg_timer_wait ) ) ev  FROM  sys.ews_sql 
                 WHERE  last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY    DIGEST) c 
            ) * 100,
         2),
  '%') "平均时长占比",
    max( SUM_ROWS_EXAMINED ) - min( SUM_ROWS_EXAMINED ) "总检查行数",
    max( LAST_SEEN ) "最后执行时间",
    DIGEST_TEXT "sql语句" 
FROM    sys.ews_sql 
WHERE   last_seen >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    DIGEST 
ORDER BY    max( avg_timer_wait ) - min( avg_timer_wait ) DESC  LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_等待事件_04

5.5、最近30分钟逻辑IO总延时排名前十的表

SELECT
    object_type "类型",
    object_schema "数据库",
    object_name "对象名",
    max( count_star ) - min( count_star ) "执行总次数",
    max( sum_timer_wait ) - min( sum_timer_wait ) "总延时(ms)↓",
    concat(
    round(
         (max( sum_timer_wait ) - min( sum_timer_wait ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev  FROM  sys.ews_table_iowaits 
                 WHERE  insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE GROUP BY object_schema,   object_name,object_type ) c 
            ) * 100,
         2),
  '%') "总延时占比",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均延时(ms)",
    max( count_read ) - min( count_read ) "读次数",
    max( sum_timer_read ) - min( sum_timer_read ) "读延时(ms)",
    max( avg_timer_read ) - min( avg_timer_read ) "平均读延时(ms)",
    max( count_write ) - min( count_write ) "写次数",
    max( sum_timer_write ) - min( sum_timer_write ) "写延时(ms)",
    max( avg_timer_write ) - min( avg_timer_write ) "平均写延时(ms)" 
FROM    sys.ews_table_iowaits 
WHERE   insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    object_schema,  object_name,    object_type 
ORDER BY    max( sum_timer_wait ) - min( sum_timer_wait ) DESC LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_MySQL_05

5.6、最近30分钟逻辑IO总延时排名前十的索引

SELECT
    object_type "类型",
    object_schema "数据库",
    object_name "对象名称",
    index_name "索引名称",
    max( count_star ) - min( count_star ) "执行总次数",
    max( sum_timer_wait ) - min( sum_timer_wait ) "总延时(ms)↓",
    concat(
    round(
         (max( sum_timer_wait ) - min( sum_timer_wait ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev  FROM  sys.ews_index_iowaits 
                 WHERE  insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE  and index_name is not null GROUP BY object_schema,   object_name,index_name, object_type ) c 
            ) * 100,
         2),
  '%') "总延时占比",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均延时(ms)",
    max( count_read ) - min( count_read ) "读次数",
    max( sum_timer_read ) - min( sum_timer_read ) "读延时(ms)",
    max( avg_timer_read ) - min( avg_timer_read ) "平均读延时(ms)",
    max( count_write ) - min( count_write ) "写次数",
    max( sum_timer_write ) - min( sum_timer_write ) "写延时(ms)",
    max( avg_timer_write ) - min( avg_timer_write ) "平均写延时(ms)" 
FROM    sys.ews_index_iowaits 
WHERE   insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE and index_name is not null
GROUP BY    object_schema,  object_name,    index_name, object_type 
ORDER BY    max( sum_timer_wait ) - min( sum_timer_wait ) DESC limit 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_等待事件_06

5.7、最近30分钟表锁耗时排名前十的表

SELECT
    object_type "类型",
    object_schema "数据库",
    object_name "对象名称",
    max( count_star ) - min( count_star ) "执行总次数",
    max( sum_timer_wait ) - min( sum_timer_wait ) "总延时(ms)↓",
    concat(
    round(
         (max( sum_timer_wait ) - min( sum_timer_wait ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max(sum_timer_wait) - min( sum_timer_wait ) ) ev  FROM  sys.ews_table_lockwaits 
                 WHERE  insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE  GROUP BY object_schema,object_name,object_type ) c 
            ) * 100,
         2),
  '%') "总延时占比",
    max( avg_timer_wait ) - min( avg_timer_wait ) "平均延时(ms)",
    max( count_read ) - min( count_read ) "读次数",
    max( sum_timer_read ) - min( sum_timer_read ) "读延时(ms)",
    max( avg_timer_read ) - min( avg_timer_read ) "平均读延时(ms)",
    max( count_write ) - min( count_write ) "写次数",
    max( sum_timer_write ) - min( sum_timer_write ) "写延时(ms)",
    max( avg_timer_write ) - min( avg_timer_write ) "平均写延时(ms)" 
FROM    sys.ews_table_lockwaits 
WHERE   insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    object_schema,  object_name,    object_type 
ORDER BY    max( sum_timer_wait ) - min( sum_timer_wait ) DESC LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_MySQL_07

5.8、最近30分钟物理IO排名前十的表

SELECT
    FILE_NAME "文件名",
    EVENT_NAME "等待事件",
    max( COUNT_STAR ) - min( COUNT_STAR ) "文件I/0操作数量",
    round( ( max( sum_number_of_bytes_read ) - min( sum_number_of_bytes_read ) ) / 1024 ) "文件读I/0数据量(KB)",
    round( ( max( sum_number_of_bytes_write ) - min( sum_number_of_bytes_write ) ) / 1024 ) "文件写I/0数据量(KB)",
    concat(
    round(
         (max( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) - min( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ))/(
                 SELECT sum(ev) FROM
             ( SELECT (max( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) - min( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE )) ev  FROM  sys.ews_file_io 
                 WHERE  insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE  GROUP BY FILE_NAME,EVENT_NAME)c 
            ) * 100,
         2),
  '%') "文件I/0占比↓"
FROM    sys.ews_file_io 
WHERE   insert_date >= CURRENT_TIMESTAMP - INTERVAL 30 MINUTE 
GROUP BY    FILE_NAME,  EVENT_NAME 
ORDER BY    max( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) - min( SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE ) DESC LIMIT 10;

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_sql_08

5.9、未使用的索引

通过table_io_waits_summary_by_index_usage表可以获得系统运行到现在，哪些索引从来没有被用过。由于索引也会占用大量的空间，可以利用这个统计信息，结合一定的时间策略将无用的索引删除。

SELECT
    OBJECT_SCHEMA "数据库",
    OBJECT_NAME "对象名称",
    INDEX_NAME "索引名称"
FROM
    performance_schema.table_io_waits_summary_by_index_usage ews 
WHERE
    INDEX_NAME IS NOT NULL 
    AND COUNT_STAR = 0 
    AND OBJECT_SCHEMA  not in ('perf_stat','mysql','sys','performance_schema','information_schema');

[转帖]基于MySQL8.0存储过程实现myawr平台的top sql功能_MySQL_09