一、Performance Schema 簡介

Performance Schema 是 MySQL 中的一個工具，用于分析和監控 MySQL 數據庫的性能問題。Performance Schema 采用了一種事件捕獲和記錄的方式，可以記錄 MySQL 數據庫中發生的各種事件，例如查詢、鎖等事件，以及事件的執行時間、等待時間等信息，從而可以通過分析這些事件信息來診斷數據庫的性能問題。

二、啟用 Performance Schema

在使用 Performance Schema 之前，需要在 MySQL 數據庫中啟用 Performance Schema。可以通過以下命令查看 MySQL 是否啟用了 Performance Schema：

mysql> SHOW VARIABLES LIKE 'performance_schema';

如果結果為 ON，則表示 MySQL 已經啟用了 Performance Schema。如果結果為 OFF，則需要在 MySQL 配置文件中啟用 Performance Schema。

在配置文件中，將 performance_schema 參數設置為 ON，如下所示：

[mysqld]
performance_schema = ON

重啟 MySQL 服務，使配置文件生效。

三、配置 Performance Schema

在 Performance Schema 啟用后，需要配置 Performance Schema，以滿足具體的性能分析需求。可以通過以下命令查看 Performance Schema 的配置：

mysql> SHOW VARIABLES LIKE 'performance_schema%';

可以根據具體需求，修改 Performance Schema 的配置參數。例如，可以將 performance_schema_events_waits_history_size 參數設置為 1000，以記錄更多的等待事件歷史記錄：

mysql> SET GLOBAL performance_schema_events_waits_history_size = 1000;

四、分析SQL響應時間過長的問題

在 Performance Schema 啟用和配置完成后，可以通過 Performance Schema 分析 MySQL 數據庫的性能問題。例如，可以分析SQL線程響應時間過長的問題。可以通過以下命令查看哪類SQL平均響應時間最長：

mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,AVG_TIMER_WAIT,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest order by AVG_TIMER_WAIT desc limit 10;

該命令將列出響應時間最長的前 10 條SQL文本，可以通過該命令查看哪些SQL占用了較多的響應時間，從而可以對這些SQL進行分析并優化。

五、實驗

使用sysbench創建測試表，并寫入1000w行數據

sysbench --mysql-host=XXX --mysql-port=XXX --mysql-user=XXX --mysql-password=XXX --mysql-db=teletest --mysql-storage-engine=innodb --tables=1 --table-size=10000000 --time=180 --events=0 --report-interval=10 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=off --threads=48 prepare

查看表是否寫入成功。

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.34 sec)

執行查詢

mysql> select * from sbtest1 where pad like '66321586045%';

查看響應時間最長的SQL線程信息，記錄了這條SQL的degest文本：

mysql> SELECT SCHEMA_NAME,DIGEST_TEXT,AVG_TIMER_WAIT,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like 'SELECT%' order by AVG_TIMER_WAIT desc limit 10 \G
*************************** 1. row ***************************
   SCHEMA_NAME: teletest
   DIGEST_TEXT: SELECT * FROM `sbtest1` WHERE `pad` LIKE ?
AVG_TIMER_WAIT: 2490493844000
    FIRST_SEEN: 2023-04-19 08:32:48.024789
     LAST_SEEN: 2023-04-19 09:06:54.754311

對該SQL解析，全表掃描，沒有索引

mysql> explain select * from sbtest1 where pad like '66321586045%';
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800580 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

因此針對這條SQL的pad字段添加索引：

mysql> alter table sbtest1 add index idx(pad);
Query OK, 0 rows affected (1 min 39.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> explain select * from sbtest1 where pad like '66321586045%';
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range | idx           | idx  | 240     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

添加索引后rows掃描的行數變為1，不再是全表掃描。