一、PERFORMANCE_SCHEMA库介绍

 MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

在MySQL5.6、MySQL5.7中，PERFORMANCE_SCHEMA有很大改进，包括引入大量新加入的监控项、降低占用空间和负载，以及通过新的sys schema机制显著提升易用性。

二、启用PERFORMANCE_SCHEMA监控

2.1 查看MySQL支持的引擎

mysql> select * from information_schema.engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2.2 开启PERFORMANCE_SCHEMA

（1）修改配置文件，持久化配置

[root@X ~]# vim /etc/my.conf  
[mysqld]
performance_schema=ON

（2）查看开启状态

mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

三、PERFORMANCE_SCHEMA使用技巧

注：由于测试库是空的，并且没有业务，所以查询出来的数据较少！！！以下是PERFORMANCE_SCHEMA库中的表情况：

mysql> use performance_schema;
mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema                         |
+------------------------------------------------------+
| accounts                                             |
| cond_instances                                       |
| events_stages_current                                |
| events_stages_history                                |
| events_stages_history_long                           |
| events_stages_summary_by_account_by_event_name       |
| events_stages_summary_by_host_by_event_name          |
| events_stages_summary_by_thread_by_event_name        |
| events_stages_summary_by_user_by_event_name          |
| events_stages_summary_global_by_event_name           |
| events_statements_current                            |
| events_statements_history                            |
| events_statements_history_long                       |
| events_statements_summary_by_account_by_event_name   |
| events_statements_summary_by_digest                  |
| events_statements_summary_by_host_by_event_name      |
| events_statements_summary_by_program                 |
| events_statements_summary_by_thread_by_event_name    |
| events_statements_summary_by_user_by_event_name      |
| events_statements_summary_global_by_event_name       |
| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
| events_waits_current                                 |
| events_waits_history                                 |
| events_waits_history_long                            |
| events_waits_summary_by_account_by_event_name        |
| events_waits_summary_by_host_by_event_name           |
| events_waits_summary_by_instance                     |
| events_waits_summary_by_thread_by_event_name         |
| events_waits_summary_by_user_by_event_name           |
| events_waits_summary_global_by_event_name            |
| file_instances                                       |
| file_summary_by_event_name                           |
| file_summary_by_instance                             |
| global_status                                        |
| global_variables                                     |
| host_cache                                           |
| hosts                                                |
| memory_summary_by_account_by_event_name              |
| memory_summary_by_host_by_event_name                 |
| memory_summary_by_thread_by_event_name               |
| memory_summary_by_user_by_event_name                 |
| memory_summary_global_by_event_name                  |
| metadata_locks                                       |
| mutex_instances                                      |
| objects_summary_global_by_type                       |
| performance_timers                                   |
| prepared_statements_instances                        |
| replication_applier_configuration                    |
| replication_applier_status                           |
| replication_applier_status_by_coordinator            |
| replication_applier_status_by_worker                 |
| replication_connection_configuration                 |
| replication_connection_status                        |
| replication_group_member_stats                       |
| replication_group_members                            |
| rwlock_instances                                     |
| session_account_connect_attrs                        |
| session_connect_attrs                                |
| session_status                                       |
| session_variables                                    |
| setup_actors                                         |
| setup_consumers                                      |
| setup_instruments                                    |
| setup_objects                                        |
| setup_timers                                         |
| socket_instances                                     |
| socket_summary_by_event_name                         |
| socket_summary_by_instance                           |
| status_by_account                                    |
| status_by_host                                       |
| status_by_thread                                     |
| status_by_user                                       |
| table_handles                                        |
| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |
| threads                                              |
| user_variables_by_thread                             |
| users                                                |
| variables_by_thread                                  |
+------------------------------------------------------+
87 rows in set (0.00 sec)

3.1 查询执行最多的SQL

mysql> 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
*************************** 1. row ***************************
      SCHEMA_NAME: NULL
      DIGEST_TEXT: SHOW SLAVE STATUS 
       COUNT_STAR: 1487755
    SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
       FIRST_SEEN: 2021-11-17 11:38:55
        LAST_SEEN: 2021-12-09 22:47:17
1 row in set (0.00 sec)

3.2 查询响应时间最长的SQL

mysql> 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
*************************** 1. row ***************************
      SCHEMA_NAME: mysql
      DIGEST_TEXT: GRANT SELECT , INSERT , UPDATE , DELETE ON * . * TO ? @? REQUIRE NONE WITH MAX_QUERIES_PER_HOUR ? MAX_CONNECTIONS_PER_HOUR ? MAX_UPDATES_PER_HOUR ? MAX_USER_CONNECTIONS ? 
       COUNT_STAR: 1
   AVG_TIMER_WAIT: 17413918000
    SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
       FIRST_SEEN: 2021-11-25 17:16:01
        LAST_SEEN: 2021-11-25 17:16:01
1 row in set (0.00 sec)

3.3 查找逻辑IO最高的表、文件（热数据）

mysql> 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
*************************** 1. row ***************************
                FILE_NAME: /opt/udb/instance/mysql-5.7/569ae9f9-53a5-4017-9d7e-31283aa9096a/data/ibtmp1
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 0
 SUM_NUMBER_OF_BYTES_READ: 0
              COUNT_WRITE: 1906642
SUM_NUMBER_OF_BYTES_WRITE: 31250808832
*************************** 2. row ***************************
                FILE_NAME: /opt/udb/instance/mysql-5.7/569ae9f9-53a5-4017-9d7e-31283aa9096a/data/ibdata1
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 167
 SUM_NUMBER_OF_BYTES_READ: 4849664
              COUNT_WRITE: 9
SUM_NUMBER_OF_BYTES_WRITE: 294912
2 rows in set (0.00 sec)

3.4 查找使用最多的索引

mysql> 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;
+-------------+------------+-------------+--------------+--------------+--------------+
| OBJECT_NAME | INDEX_NAME | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+------------+-------------+--------------+--------------+--------------+
| sys_config  | PRIMARY    |           0 |            0 |            0 |            0 |
+-------------+------------+-------------+--------------+--------------+--------------+
1 row in set (0.00 sec)

3.5 查询未使用过的索引

mysql> 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;
+---------------+-------------+------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME |
+---------------+-------------+------------+
| sys           | sys_config  | PRIMARY    |
+---------------+-------------+------------+
1 row in set (0.01 sec)

3.6 查找最耗时的等待

mysql> 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;
+--------------------------------------+------------+----------------+----------------+
| EVENT_NAME                           | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT |
+--------------------------------------+------------+----------------+----------------+
| wait/io/file/innodb/innodb_data_file |    1907387 | 38437677983232 |       20151552 |
+--------------------------------------+------------+----------------+----------------+
1 row in set (0.00 sec)