简介
MySQL Performance Schema (PS) 和 SYS Schema 是MySQL官方提供的，可以用来监控性能和诊断故障。
PS 库主要用来收集数据库运行时的性能数据，通过充分利用PS库的数据，让DBA更了解数据库的运行状态，也有利于定位问题。
SYS库，是MySQL 从 5.7.7 版本开始提供的。DBA和开发可以使用SYS库进行数据库的性能调优和问题诊断，SYS库除了 sys_config 表，其他都是视图，视图来源于 PS 库和 IS(informantion_schema) 库。也就是说 SYS 库是为了更直观易懂的展示 PS 库和 IS 库。
PS库
开启 PS 库
MYSQL 5.6 之后的版本是默认开启的。如果需要手动开启和关闭，可以在 my.cof 文件中修改相应配置。例如添加以下参数，开启 PS 库：
[mysqld]
performance_schema=ON
在 MySQL 实例中检查是否开启：
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.07 sec)
PS 库的开启或关闭，需要修改配置文件后重启实例生效。如果在线修改 performance_schema 参数的值，会报错：
mysql> set global performance_schema=off;
ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable
PS 库的表
PS 库的表使用 performance_schema 存储引擎，可以使用 show create table tablename的命名查看表结构。
例如查看 accounts 表结构：
mysql> show create table accounts \G
*************************** 1. row ***************************
Table: accounts
Create Table: CREATE TABLE `accounts` (
`USER` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`HOST` char(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
`CURRENT_CONNECTIONS` bigint NOT NULL,
`TOTAL_CONNECTIONS` bigint NOT NULL,
UNIQUE KEY `ACCOUNT` (`USER`,`HOST`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
setup表保存了监控的配置数据：
mysql> select table_name from information_schema.tables where table_schema = 'performance_schema' and table_name like 'setup%';
+-------------------+
| TABLE_NAME |
+-------------------+
| setup_actors |
| setup_consumers |
| setup_instruments |
| setup_objects |
| setup_threads |
+-------------------+
5 rows in set (0.00 sec)
setup_actors 表用于配置新的线程和监控状态，默认监控所有用户：
mysql> select * from setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+
1 row in set (0.02 sec)
setup_consumers表存放所有事件消息的消费者类型以及时间的开启状态：
mysql> select * from setup_consumers;
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | YES |
| events_transactions_history | YES |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+
15 rows in set (0.00 sec)
steup_instruments 表存放6种类型（idle、wait、stage、statement、transaction、和memory）相关的 instrument 对象和开启状态。
mysql> select NAME,ENABLED,TIMED,DOCUMENTATION from setup_instruments limit 1,3;
+-------------------------------------------------------+---------+-------+---------------+
| NAME | ENABLED | TIMED | DOCUMENTATION |
+-------------------------------------------------------+---------+-------+---------------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | NULL |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | NULL |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO | NULL |
+-------------------------------------------------------+---------+-------+---------------+
3 rows in set (0.01 sec)
set_opject 存放对象和所属db的监控列表，setup_timers 存放的是 instrument 对象和所使用的timer类型。
mysql> select * from setup_objects limit 5;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| EVENT | mysql | % | NO | NO |
| EVENT | performance_schema | % | NO | NO |
| EVENT | information_schema | % | NO | NO |
| EVENT | % | % | YES | YES |
| FUNCTION | mysql | % | NO | NO |
+-------------+--------------------+-------------+---------+-------+
5 rows in set (0.00 sec)
对于上面配置信息，除非有特殊要求，维持默认即可。
PS 库按照各个维度对数据库进行性能监控，包括事件、文件使用、内存使用、复制相关、
会话统计、Socket 使用、表相关和锁等维度，其中事件包括阶段事件、语句事件、事务事件
和等待事件。
| 统计维度 | 关键字 | 主要功能 |
|---|---|---|
| 阶段事件 | %stages% | 记录线程监控的阶段事件的状态 |
| 语句事件 | %statements% | 记录线程监控的语句事件的状态 |
| 事务事件 | %transctions% | 记录线程监控的事务事件的状态 |
| 等待事件 | %waits% | 记录线程监控的等待事件的状态 |
| 文件使用 | %file% | 汇总有关I/O操作的信息 |
| 内存使用 | %memory% | 检车内存使用和聚合内存使用统计 |
| 复制统计 | %replication% | 记录多源复制和MGR的信息 |
| 会话统计 | %session% | 记录会话属性和连接的状态参数 |
| Socket使用 | %socket% | 记录活跃会话的实例 |
| 表使用 | %table% | 按表和索引聚合每个表的I/O操作 |
| 表统计 | %lock% | 记录持有读写锁的记录和汇总表的锁等待信息 |
结合图中所给的关键字，找到对应统计维度的表。例如，查找阶段事件的相关表。
mysql> select table_name from information_schema.tables where table_schema = 'performance_schema' and table_name like '%stages%';
+------------------------------------------------+
| TABLE_NAME |
+------------------------------------------------+
| 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 |
+------------------------------------------------+
8 rows in set (0.01 sec)
查询结果包含3中不同维度的数据：
- 当前事务表：表名含有“current”，包含每个线程的最新事件
- 历史事件表：表名含有“history”，历史表与当前表结构相同但包含更多行。
- 汇总表：表名含有“summary”，汇总表包含通过事件聚合的信息，包括已经从历史记录中丢弃的事件。
events_stages_current 包含每个线程最近的256个事件，
events_stages_history 包含最近的2560个事件。
mysql> select table_rows from information_schema.tables
-> where table_name="events_stages_current" ;
+------------+
| TABLE_ROWS |
+------------+
| 256 |
+------------+
1 row in set (0.00 sec)
mysql> select table_rows from information_schema.tables
-> where table_name="events_stages_history" ;
+------------+
| TABLE_ROWS |
+------------+
| 2560 |
+------------+
1 row in set (0.01 sec)
SYS库
SYS 库的对象
SYS库中的对象按名称划分，一种是以字母开头，另一种是以 x$ 开头。比如 host_summary 和 x$host_summary 。host_summary 对某些字段进行格式化处理，方便阅读；x$host_summary 直接保存原始数据，方便进行二次加工。
SYS 库的对象主要基于 PS 库，所以 SYS 库的监控维度跟 PS 库类似，主要包括主机相关、InnoDB 相关、IO 相关、内存相关、连接数和会话、DB相关、SQL语句和等待事件相关。
| 统计维度 | 关键字 | 主要功能 |
|---|---|---|
| 主机相关 | host% 和 x$host% | 按照主机维度汇众全局信息、文件IO信息和语句信息 |
| InnoDB | innodb% 和 x$innodb% | 统计 InnoDB 缓冲池信息和锁等待信息 |
| IO 相关 | io% 和 x$io% | 按照线程、文件和等待事件维护统计 IO 操作信息 |
| 内存 | memory% 和 x$memory% | 按照主机、线程、用户和全局维度统计内存信息 |
| 连接和会话 | porcesslist% 和 x$porcesslist% | 记录连接和会话信息 |
| DB | schema% 和 x$schema% | 从DB角度统计索引、锁和表的信息 |
| SQL语句 | statement% 和 x$statement% | 从表的维护汇总排序、全局扫描等信息 |
| 等待事件 | wait% 和 x$wait% | 按照主机、用户、用户和全局维度统计内存信息 |
同上类似，使用表中的关键之可以找到对应统计维度的对象。
例如主机相关的表：
mysql> select table_name from information_schema.tables
-> where table_schema = 'sys' and (table_name like 'host%' or table_name like 'x$host%');
+-------------------------------------+
| TABLE_NAME |
+-------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
+-------------------------------------+
12 rows in set (0.00 sec)
查询结果中包含 summary 字样，都属于汇总表，通过特定事件来聚合信息。
SYS对象的实际使用
SYS对象基于 PS 库和 IS 库进行整合，并格式化输出，所以直接查询 SYS 库将会更方便，结果也更直观。在实际工作中，通过查询 SYS 库的对象获取信息，可以对连接会话、表的使用、内存等进行优化。
主机相关
以连接MySQL的host为例，分析文件 IO 等待时间、文件 IO 的等待事件、SQL 语句的查询时间的信息。例如 host_summary:
mysql> select * from host_summary \G
*************************** 1. row ***************************
host: localhost
statements: 2084
statement_latency: 10.94 s
statement_avg_latency: 5.25 ms
table_scans: 54
file_ios: 572
file_io_latency: 1.70 s
current_connections: 2
total_connections: 8
unique_users: 2
current_memory: 7.62 MiB
total_memory_allocated: 350.50 MiB
4 rows in set (0.21 sec)
关键字段解释如下：
- host： 连接到 MySQL 实例的主机 IP。
- statements：主机 IP 执行总的 SQL 语句数量。
- statement_latency：主机 IP 执行 SQL 语句消耗的总时间。
- file_io_latency：主机 IP 发生文件等待的消耗的总时间。
- current_connections：主机 IP 当前的连接数。
- current_memory：主机 IP 单签分配的内存
用过上面语句可以得知，localhost的主机执行了 2084 条SQL语句，执行这些语句一共消耗了 10.94 S，文件等待的总时间为 1.7 S，但钱连接数有 2个 ，消耗了 7.62 MiB 内存。如果数据异常，可以进一步分析。
InnoDB 相关
该对象分为俩部分，其中 innodb_buffer_by_% 用于分析那些对象占用存储池的内存比较多；另一个 innodb_lock_waits 用于行锁等待分析，得到阻塞和被阻塞的会话消息。
下面从表维度分析缓冲池的使用情况。
mysql> select * from innodb_buffer_stats_by_table order by pages desc limit 1\G
*************************** 1. row ***************************
object_schema: mysql
object_name: columns
allocated: 2.62 MiB
data: 2.05 MiB
pages: 168
pages_hashed: 118
pages_old: 60
rows_cached: 7452
1 row in set (0.55 sec)
通过上面结果，可以看到 mysql.columns 表占用了缓存池 2.62 MiB 的内存，这样就找到了占用缓冲池最多的对象了。
连接数和会话相关
对象 sys.processlist 和 sys.session 的信息比 information_schema.processlist 全面，其中 sys.session 去除了后台线程的信息，只保留了用户会话的信息。
mysql> select * from session \G
*************************** 1. row ***************************
thd_id: 1735
conn_id: 1687
user: ****@*.*.*.*
db: ****
command: Sleep
state: NULL
time: 410
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 1.26 ms
rows_examined: 1266
rows_sent: 316
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: SHOW TABLE STATUS
last_statement_latency: 39.03 ms
current_memory: 16.54 KiB
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: 37.54 ms
trx_state: COMMITTED
trx_autocommit: YES
pid: 17360
program_name: NULL
查询 sys.session 可以得到会话的 ID 、连接用户信息、连接时间、查询影响行数、韩慧行数，最后执行的 SQL信息。
DB 相关
以表为第一维度，分析表和索引的使用情况。
查询 schema_redundant_indexes 表，可以找出冗余的索引。按照左原则找出冗余的索引。
例如表 A 有2个索引，inde1(a)、index2(a, b) 。index2 最左边第一个字段包含了index1，所以 index1是冗余索引，建议删除。
create table emploees (
emp_no int not null,
birth_date date not null,
first_name varchar(14) not null,
last_name varchar(16) not null,
gender enum('M','F') not null,
hire_date date not null,
primary key (emp_no),
key idx_birth_date (birth_date),
key idx_birth_date_gender(birth_date,gender)
)engine = innoDB default charset=utf8 collate=utf8_unicode_ci;
可以看到表中 key idx_birth_date (birth_date) 和 key idx_birth_date_gender(birth_date,gender) 重复了，我们再来看看 schema_redundant_indexes 表信息：
mysql> select * from sys.schema_redundant_indexes where table_schema = 'testdb' \G
*************************** 1. row ***************************
table_schema: testdb
table_name: emploees
redundant_index_name: idx_birth_date
redundant_index_columns: birth_date
redundant_index_non_unique: 1
dominant_index_name: idx_birth_date_gender
dominant_index_columns: birth_date,gender
dominant_index_non_unique: 1
subpart_exists: 0
sql_drop_index: ALTER TABLE `testdb`.`emploees` DROP INDEX `idx_birth_date`
1 row in set (0.05 sec)
MySQL 建议删除 idx_birth_date 索引，并在 sql_drop_index 中给出了完整的命令。
查询 shecma_table_statistics 表，得到实例启动以来，每个表的详细使用情况。
mysql> select * from sys.schema_table_statistics
-> order by rows_fetched + rows_inserted +
-> update_latency + rows_deleted desc limit 1 \G
*************************** 1. row ***************************
table_schema: greatsql_db
table_name: pre_common_block_item
total_latency: 136.12 ms
rows_fetched: 2524
fetch_latency: 100.91 ms
rows_inserted: 106
insert_latency: 8.78 ms
rows_updated: 106
update_latency: 26.43 ms
rows_deleted: 0
delete_latency: 0 ps
io_read_requests: 8
io_read: 128.00 KiB
io_read_latency: 29.51 ms
io_write_requests: 34
io_write: 544.00 KiB
io_write_latency: 2.03 ms
io_misc_requests: 32
io_misc_latency: 189.26 ms
1 row in set, 489 warnings (0.11 sec)
按照表增删改查的总函数进行倒叙排序，找到写操作影响行数最多的表。日常优化过程中，通过调整‘影响行数’和‘执行时间’的组合规则，可以找出符合规则的热点表，然后进行优化，提高数据库实例的整体性能。
SQL 语句相关
按照多个维度分析 SQL 执行情况。分析维护有：全表扫描次数、是否经历排序、是否创建临时表等。
例如，statement_analysis 表：
mysql> select * from statement_analysis
-> where rows_examined > 0 and rows_affected >0
-> order by exec_count desc limit 1 \G
*************************** 1. row ***************************
query: DELETE FROM `pre_common_proces ... processid` = ? OR `expiry` < ?
db: ****
full_scan:
exec_count: 104
err_count: 0
warn_count: 0
total_latency: 600.53 ms
max_latency: 125.32 ms
avg_latency: 5.77 ms
lock_latency: 32.65 ms
rows_sent: 0
rows_sent_avg: 0
rows_examined: 56
rows_examined_avg: 1
rows_affected: 52
rows_affected_avg: 1
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: b73bde936d1b3997f56dd9242756f8ff10d4a4413b65fa288f98b34ce80e0f81
first_seen: 2022-09-13 22:26:13.434758
last_seen: 2022-09-15 12:23:53.139200
1 row in set (1.26 sec)
重要字段解释入下：
- query：替换 SQL 语句变量并美化。
- db：应用执行 SQL 时连接的 db 名。
- exec_count：SQL 执行的总次数。
- rows_examined: SQL 执行查询的总记录数。
- row_affected：SQL 执行影响的总记录数。
通过上面的信息，得到SQL的执行次数、影响函数、创建临时表次数和排序记录数。如果有异常的指标可以进一步分析和优化。
小节
PS库从多个维度收集数据库运行的性能信息，SYS 库通过队 PS 库和 IS 库的封装，让我们能更方便的查询这些性能信息。合理使用 PS 库和 SYS 库的信息，将为我们调优带来很大的便利