mysql sum 和 count_基于MySQL实现myawr平台top sql功能（上）

概述

某DBA大师曾说过，一个DBA要像了解自己的老婆一样了解自己管理的数据库，个人认为包含了两个方面的了解：

1，在稳定性层面来说，更多的是关注高可用、读写分离、负载均衡，灾备管理等等high level层面的措施(就好比要保证生活的稳定性)

2，在实例级别的来说，需要关注内存、IO、网络，热点表，热点索引，top sql，死锁，阻塞，历史上执行异常的SQL(好比生活品质细节)

写这个的初衷是因为mysql很难计算最近的top sql，因为performance_schema记录的都是累计的，如果要看一些top sql问题是很难的，刚好myawr平台提供了这个功能，如下

---

myawr功能

需求：抽取myawr平台上的top报告

实现：用存储过程来实现myawr平台的一些功能，先看一下计算方式和配置。

---

一、相关配置

my.cnf参考如下配置：

#监控performance_schema=onperformance-schema-instrument='wait/io/file/%=on'performance-schema-instrument='wait/io/table/%=on'performance-schema-instrument='statement/com/%=on'performance-schema-instrument='statement/sql/%=on'performance-schema-instrument='stage/sql/%=on'performance-schema-instrument='memory/%=on'# 注：只有current=on，performance_schema才会检查是否需要填充到history和long history中，后两者没有关系，均依赖于current，因此current必须无条件启用performance-schema-consumer-events_stages_current=on# performance-schema-consumer-events_stages_history=on# performance_schema_events_stages_history_size=30performance-schema-consumer-events_stages_history_long=onperformance_schema_events_statements_history_long_size=10000performance-schema-consumer-events_statements_current=on# performance-schema-consumer-events_statements_history=on# performance_schema_events_statements_history_size=30performance-schema-consumer-events_statements_history_long=onperformance_schema_events_statements_history_long_size=10000performance-schema-consumer-events_waits_current=on# performance-schema-consumer-events_waits_history=on# performance_schema_events_statements_history_size=30performance-schema-consumer-events_waits_history_long=onperformance_schema_events_waits_history_long_size=10000performance-schema-consumer-statements_digest=on

---

二、MySQL performance sql计算方式(累计值)

1、Top 10 Event Summary

通过events_waits_summary_global_by_event_name表，可以获取到系统运行到现在，消耗时间最多的事件，当然还可以根据其它维度排序，比如平均等待时间，从结果来看wait/io/table/sql/handler这个事件消耗的累计时间最长。

SELECTews.EVENT_NAME "等待事件",ews.COUNT_STAR "等待次数",round( ews.SUM_TIMER_WAIT /1000000 ) "总时长(ms)",round( ews.AVG_TIMER_WAIT / 1000000 ) "平均等待时间(ms)",    round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,    round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAITFROMPERFORMANCE_SCHEMA.events_waits_summary_global_by_event_name ews WHEREews.count_star > 0 AND ews.event_name != 'idle' ORDER BYsum_timer_wait DESC;

2、Top 10 SQL

关于SQL维度的统计信息主要集中在events_statements_summary_by_digest表中，通过将SQL语句抽象出digest，可以统计某类SQL语句在各个维度的统计信息(比如：执行次数，排序次数，使用临时表等)

需要注意的是，这个统计是按照MySQL执行一个事务消耗的资源做统计的，而不是一个语句。这个系统表是为数不多的支持truncate的，当然它在内部，也是在不断收集的一个过程。

selectews.SCHEMA_NAME "数据库",ews.DIGEST "sql_id",ews.DIGEST_TEXT "sql语句",ews.COUNT_STAR "执行次数",round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,round(ews.SUM_LOCK_TIME/1000000) 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_SEENfrom performance_schema.events_statements_summary_by_digest ewswhere ews.SCHEMA_NAME not in ('perf_stat','mysql','sys','performance_schema','information_schema')and ews.count_star > 0and ews.digest_text not like 'SHOW%'and ews.digest_text not like 'USE%'and ews.digest_text not like 'SET%'and ews.digest_text not like 'SELECT @%'and ews.digest_text not like 'EXPLAIN%'and ews.digest_text not like 'DROP PROCEDURE%'and ews.digest_text not like 'CREATE PROCEDURE%'order by SUM_TIMER_WAIT desc;        --AND last_seen >= CURRENT_TIMESTAMP - INTERVAL 15 MINUTE 

FIRST_SEEN和LAST_SEEN分别显示了语句第一次执行和最后一次执行的时间点，代码中连续查询两次结果得出差值即以下AWR平台的结果

3、Top 10 Table By LOGICAL IO Wait

selectews.OBJECT_TYPE,ews.OBJECT_SCHEMA,ews.OBJECT_NAME,ews.COUNT_STAR,round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,ews.COUNT_READ,round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,ews.COUNT_WRITE,round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,ews.COUNT_FETCH,round(ews.SUM_TIMER_FETCH/1000000) SUM_TIMER_FETCH,round(ews.MIN_TIMER_FETCH/1000000) MIN_TIMER_FETCH,round(ews.AVG_TIMER_FETCH/1000000) AVG_TIMER_FETCH,round(ews.MAX_TIMER_FETCH/1000000) MAX_TIMER_FETCH,ews.COUNT_INSERT,round(ews.SUM_TIMER_INSERT/1000000) SUM_TIMER_INSERT,round(ews.MIN_TIMER_INSERT/1000000) MIN_TIMER_INSERT,round(ews.AVG_TIMER_INSERT/1000000) AVG_TIMER_INSERT,round(ews.MAX_TIMER_INSERT/1000000) MAX_TIMER_INSERT,ews.COUNT_UPDATE,round(ews.SUM_TIMER_UPDATE/1000000) SUM_TIMER_UPDATE,round(ews.MIN_TIMER_UPDATE/1000000) MIN_TIMER_UPDATE,round(ews.AVG_TIMER_UPDATE/1000000) AVG_TIMER_UPDATE,round(ews.MAX_TIMER_UPDATE/1000000) MAX_TIMER_UPDATE,ews.COUNT_DELETE,round(ews.SUM_TIMER_DELETE/1000000) SUM_TIMER_DELETE,round(ews.MIN_TIMER_DELETE/1000000) MIN_TIMER_DELETE,round(ews.AVG_TIMER_DELETE/1000000) AVG_TIMER_DELETE,round(ews.MAX_TIMER_DELETE/1000000) MAX_TIMER_DELETEfrom performance_schema.table_io_waits_summary_by_table ewswhere ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')and ews.count_star > 0;

做差值相减可得以下结果：

---

4、Top 10 Index By LOGICAL IO Wait

通过table_io_waits_summary_by_index_usage表，可以获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多。

select ews.OBJECT_TYPE,ews.OBJECT_SCHEMA,ews.OBJECT_NAME,ews.INDEX_NAME,ews.COUNT_STAR,round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,ews.COUNT_READ,round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,ews.COUNT_WRITE,round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,ews.COUNT_FETCH,round(ews.SUM_TIMER_FETCH/1000000) SUM_TIMER_FETCH,round(ews.MIN_TIMER_FETCH/1000000) MIN_TIMER_FETCH,round(ews.AVG_TIMER_FETCH/1000000) AVG_TIMER_FETCH,round(ews.MAX_TIMER_FETCH/1000000) MAX_TIMER_FETCH,ews.COUNT_INSERT,round(ews.SUM_TIMER_INSERT/1000000) SUM_TIMER_INSERT,round(ews.MIN_TIMER_INSERT/1000000) MIN_TIMER_INSERT,round(ews.AVG_TIMER_INSERT/1000000) AVG_TIMER_INSERT,round(ews.MAX_TIMER_INSERT/1000000) MAX_TIMER_INSERT,ews.COUNT_UPDATE,round(ews.SUM_TIMER_UPDATE/1000000) SUM_TIMER_UPDATE,round(ews.MIN_TIMER_UPDATE/1000000) MIN_TIMER_UPDATE,round(ews.AVG_TIMER_UPDATE/1000000) AVG_TIMER_UPDATE,round(ews.MAX_TIMER_UPDATE/1000000) MAX_TIMER_UPDATE,ews.COUNT_DELETE,round(ews.SUM_TIMER_DELETE/1000000) SUM_TIMER_DELETE,round(ews.MIN_TIMER_DELETE/1000000) MIN_TIMER_DELETE,round(ews.AVG_TIMER_DELETE/1000000) AVG_TIMER_DELETE,round(ews.MAX_TIMER_DELETE/1000000) MAX_TIMER_DELETEfrom performance_schema.table_io_waits_summary_by_index_usage ewswhere ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')and ews.count_star > 0

AWR平台输出结果如下：

---

5、Top 10 Table By LOCKS WAITS

selectews.OBJECT_TYPE,ews.OBJECT_SCHEMA,ews.OBJECT_NAME,ews.COUNT_STAR,round(ews.SUM_TIMER_WAIT/1000000) SUM_TIMER_WAIT,round(ews.MIN_TIMER_WAIT/1000000) MIN_TIMER_WAIT,round(ews.AVG_TIMER_WAIT/1000000) AVG_TIMER_WAIT,round(ews.MAX_TIMER_WAIT/1000000) MAX_TIMER_WAIT,ews.COUNT_READ,round(ews.SUM_TIMER_READ/1000000) SUM_TIMER_READ,round(ews.MIN_TIMER_READ/1000000) MIN_TIMER_READ,round(ews.AVG_TIMER_READ/1000000) AVG_TIMER_READ,round(ews.MAX_TIMER_READ/1000000) MAX_TIMER_READ,ews.COUNT_WRITE,round(ews.SUM_TIMER_WRITE/1000000) SUM_TIMER_WRITE,round(ews.MIN_TIMER_WRITE/1000000) MIN_TIMER_WRITE,round(ews.AVG_TIMER_WRITE/1000000) AVG_TIMER_WRITE,round(ews.MAX_TIMER_WRITE/1000000) MAX_TIMER_WRITE,ews.COUNT_READ_NORMAL,round(ews.SUM_TIMER_READ_NORMAL/1000000) SUM_TIMER_READ_NORMAL,round(ews.MIN_TIMER_READ_NORMAL/1000000) MIN_TIMER_READ_NORMAL,round(ews.AVG_TIMER_READ_NORMAL/1000000) AVG_TIMER_READ_NORMAL,round(ews.MAX_TIMER_READ_NORMAL/1000000) MAX_TIMER_READ_NORMAL,ews.COUNT_READ_WITH_SHARED_LOCKS,round(ews.SUM_TIMER_READ_WITH_SHARED_LOCKS/1000000) SUM_TIMER_READ_WITH_SHARED_LOCKS,round(ews.MIN_TIMER_READ_WITH_SHARED_LOCKS/1000000) MIN_TIMER_READ_WITH_SHARED_LOCKS,round(ews.AVG_TIMER_READ_WITH_SHARED_LOCKS/1000000) AVG_TIMER_READ_WITH_SHARED_LOCKS,round(ews.MAX_TIMER_READ_WITH_SHARED_LOCKS/1000000) MAX_TIMER_READ_WITH_SHARED_LOCKS,ews.COUNT_READ_NO_INSERT,round(ews.SUM_TIMER_READ_NO_INSERT/1000000) SUM_TIMER_READ_NO_INSERT,round(ews.MIN_TIMER_READ_NO_INSERT/1000000) MIN_TIMER_READ_NO_INSERT,round(ews.AVG_TIMER_READ_NO_INSERT/1000000) AVG_TIMER_READ_NO_INSERT,round(ews.MAX_TIMER_READ_NO_INSERT/1000000) MAX_TIMER_READ_NO_INSERT,ews.COUNT_READ_EXTERNAL,round(ews.SUM_TIMER_READ_EXTERNAL/1000000) SUM_TIMER_READ_EXTERNAL,round(ews.MIN_TIMER_READ_EXTERNAL/1000000) MIN_TIMER_READ_EXTERNAL,round(ews.AVG_TIMER_READ_EXTERNAL/1000000) AVG_TIMER_READ_EXTERNAL,round(ews.MAX_TIMER_READ_EXTERNAL/1000000) MAX_TIMER_READ_EXTERNAL,ews.COUNT_WRITE_ALLOW_WRITE,round(ews.SUM_TIMER_WRITE_ALLOW_WRITE/1000000) SUM_TIMER_WRITE_ALLOW_WRITE,round(ews.MIN_TIMER_WRITE_ALLOW_WRITE/1000000) MIN_TIMER_WRITE_ALLOW_WRITE,round(ews.AVG_TIMER_WRITE_ALLOW_WRITE/1000000) AVG_TIMER_WRITE_ALLOW_WRITE,round(ews.MAX_TIMER_WRITE_ALLOW_WRITE/1000000) MAX_TIMER_WRITE_ALLOW_WRITE,ews.COUNT_WRITE_CONCURRENT_INSERT,round(ews.SUM_TIMER_WRITE_CONCURRENT_INSERT/1000000) SUM_TIMER_WRITE_CONCURRENT_INSERT,round(ews.MIN_TIMER_WRITE_CONCURRENT_INSERT/1000000) MIN_TIMER_WRITE_CONCURRENT_INSERT,round(ews.AVG_TIMER_WRITE_CONCURRENT_INSERT/1000000) AVG_TIMER_WRITE_CONCURRENT_INSERT,round(ews.MAX_TIMER_WRITE_CONCURRENT_INSERT/1000000) MAX_TIMER_WRITE_CONCURRENT_INSERT,ews.COUNT_WRITE_NORMAL,round(ews.SUM_TIMER_WRITE_NORMAL/1000000) SUM_TIMER_WRITE_NORMAL,round(ews.MIN_TIMER_WRITE_NORMAL/1000000) MIN_TIMER_WRITE_NORMAL,round(ews.AVG_TIMER_WRITE_NORMAL/1000000) AVG_TIMER_WRITE_NORMAL,round(ews.MAX_TIMER_WRITE_NORMAL/1000000) MAX_TIMER_WRITE_NORMAL,ews.COUNT_WRITE_EXTERNAL,round(ews.SUM_TIMER_WRITE_EXTERNAL/1000000) SUM_TIMER_WRITE_EXTERNAL,round(ews.MIN_TIMER_WRITE_EXTERNAL/1000000) MIN_TIMER_WRITE_EXTERNAL,round(ews.AVG_TIMER_WRITE_EXTERNAL/1000000) AVG_TIMER_WRITE_EXTERNAL,round(ews.MAX_TIMER_WRITE_EXTERNAL/1000000) MAX_TIMER_WRITE_EXTERNALfrom performance_schema.table_lock_waits_summary_by_table ewswhere ews.OBJECT_SCHEMA not in ('perf_stat','mysql','sys','performance_schema','information_schema')and ews.count_star > 0

---

6、Top 10 Table By PHYSICAL IO Wait

通过file_summary_by_instance表，可以获得系统运行到现在，哪个文件(表)物理IO最多，这可能意味着这个表经常需要访问磁盘IO。

SELECT    file_name,    event_name,    SUM_NUMBER_OF_BYTES_READ,    SUM_NUMBER_OF_BYTES_WRITE FROM    `performance_schema`.file_summary_by_instance ORDER BY    SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC;

---

7、哪个索引从来没有使用过？

SELECT    OBJECT_SCHEMA,    OBJECT_NAME,    INDEX_NAME FROM    `performance_schema`.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;

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

---

以上的sql计算方式均为累计值，如果需计算某段时间的故障差值是很难算的，篇幅有限，实现方式就放在下篇了...