Mysql中Performance_schema SQL執行統計的示例分析

這篇文章主要為大家展示了“Mysql中Performance_schema SQL執行統計的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Mysql中Performance_schema SQL執行統計的示例分析”這篇文章吧。

查看執行最頻繁TOP SQL：

可以解決問題：
1.SQL的執行次數
2.全表掃描
3.臨時表

select digest_text as sql_text,count_star as exec_count,if(sum_no_good_index_used > 0 or sum_no_index_used > 0, '*', '') as full_scan,sum_created_tmp_tables as tmp_tables,
        sum_created_tmp_disk_tables as tmp_disk_tables,
        sum_sort_rows as rows_sorted from events_statements_summary_by_digest where schema_name='ticket' and digest_text like 'select%' order by count_star desc limit 10;

生成mysql sql 執行性能報告
開啟SQL的性能收集
use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
清空SQL性能數據
truncate table events_statements_current; 
truncate table events_statements_history; 
truncate table events_statements_summary_by_digest;
do sleep(60);
統計SQL的執行比例
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star as sql_exec, left(digest_text,150) as stmt, digest from events_statements_summary_by_digest order by 2 desc;


關閉SQL的性能收集：
update setup_consumers set enabled='NO' where name IN ('events_statements_history','events_statements_current','statements_digest');

以上是“Mysql中Performance_schema SQL執行統計的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！