©2020-2024 ioDraw All rights reserved,
Privacy Policy
今天主要介绍如何通过Performance-Schema得到DBA关心的数据，比如哪个SQL执行次数最多，哪个表访问最频繁，哪个锁最热等信息。通过充分利用
Performance-Schema表的数据，让DBA更了解DB的运行状态，也更有助于排查定位问题。
这里主要借助了events_statements_summary_by_digest
，这个表保存着许多关键指标，抓取了与每条标准化语句有关的延迟、错误和查询量信息。
-- 查询执行次数最多的SQL
select DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM
performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
-- 哪类SQL的平均响应时间最多
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM
performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT
DESC
-- 哪类SQL排序记录数最多
select digest_text,sum_sort_rows from
performance_schema.events_statements_summary_by_digest order by count_star desc
-- 哪类SQL扫描记录数最多
select digest_text,sum_rows_examined from
performance_schema.events_statements_summary_by_digest ORDER BY count_star desc
-- 哪类SQL使用临时表最多
select digest_text,sum_created_tmp_tables,sum_created_tmp_disk_tables from
performance_schema.events_statements_summary_by_digest ORDER BY count_star DESC
-- 哪个表物理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;