1.簡(jiǎn)介
版本：5.5之后
作用：屬于系統(tǒng)庫(kù)，用于收集數(shù)據(jù)庫(kù)服務(wù)器性能參數(shù)
引擎：此庫(kù)中的表的默認(rèn)引擎，也叫做PERFORMANCE_SCHEMA
通過如下語句查看：
select table_catalog ,table_schema ,table_name ,engine
from information_schema.tables
where? table_schema = 'performance_schema';
開啟：5.5版本默認(rèn)是不開的，需要在配置文件my.cnf中添加
[mysqld]
performance_schema=ON
查看是否開啟：
mysql>show variables like 'performance_schema';
5.6版本之后是默認(rèn)開啟的
其中的表的含義，記錄什么內(nèi)容，可以參考：
https://www.cnblogs.com/cchust/p/5022148.html
https://www.cnblogs.com/cchust/p/5057498.html
2.常用
查找執(zhí)行最多的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
此外其他列的含義如下，可以視情況加入select list
SQL的平均響應(yīng)時(shí)間
AVG_TIMER_WAIT
SQL排序記錄數(shù)
SUM_SORT_ROWS
SQL掃描記錄數(shù)
SUM_ROWS_EXAMINED
SQL使用臨時(shí)表
SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES
SQL返回結(jié)果集
SUM_ROWS_SENT
?通過上述指標(biāo)我們可以間接獲得某類SQL的邏輯IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，網(wǎng)絡(luò)帶寬(SUM_ROWS_SENT)的對(duì)比，但還無法得到某類SQL的物理IO消耗，以及某類SQL訪問數(shù)據(jù)的buffer命中率。
查找響應(yīng)時(shí)間最長(zhǎng)的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
查找邏輯IO最高的表、文件（熱數(shù)據(jù)）：
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;
查找最耗時(shí)的等待：
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;
