各位朋友，晚上好！很高兴能在这里跟大家一起聊聊MySQL Performance Schema 和 sys schema 这两个宝贝。它们就像MySQL的内置监控系统，能帮助我们深入了解数据库的运行状况，找到性能瓶颈，然后像医生一样“诊断”并“治疗”我们的数据库。
今天咱们就来一场“庖丁解牛”式的探索，从Performance Schema的原始数据开始，一步步看到sys schema 怎样把这些原始数据变成更友好的“高阶视图”，以及我们如何利用这些视图来提升数据库性能。
一、Performance Schema：MySQL的“黑匣子”
Performance Schema（简称P_S）是MySQL 5.5版本之后引入的一个性能监控特性。它就像飞机的“黑匣子”，记录了各种服务器事件的详细信息，比如语句执行的耗时、锁的等待情况等等。这些信息都以表格的形式存储在performance_schema数据库中。
要开启P_S，需要在MySQL的配置文件（如my.cnf或my.ini）中进行配置：
[mysqld]
performance_schema=ON然后重启MySQL服务。注意，开启P_S会对性能产生一定影响，因为它要记录大量的事件。所以，要根据实际情况来决定是否开启以及开启哪些事件。
P_S里有很多表，但是我们常用的包括：
events_statements_summary_global_by_event_name: 按照事件名称汇总的语句执行信息。events_statements_current: 当前正在执行的语句。events_waits_summary_global_by_event_name: 按照事件名称汇总的等待事件信息。threads: 线程信息。file_summary_by_event_name: 按照事件名称汇总的文件操作信息。memory_summary_global_by_event_name: 按照事件名称汇总的内存使用信息。
这些表里的数据都是原始的、未经加工的。它们就像未经提炼的矿石，虽然蕴藏着巨大的价值，但是直接使用起来比较困难。
举个例子，我们来看看events_statements_summary_global_by_event_name这张表：
SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT,
AVG_TIMER_WAIT,
MIN_TIMER_WAIT,
MAX_TIMER_WAIT
FROM
performance_schema.events_statements_summary_global_by_event_name
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;这个查询会按照总耗时降序排列，列出前10个事件名称以及它们的统计信息。这些统计信息包括：
EVENT_NAME: 事件名称，比如statement/sql/select。COUNT_STAR: 事件发生的次数。SUM_TIMER_WAIT: 事件的总耗时（单位是皮秒）。AVG_TIMER_WAIT: 事件的平均耗时。MIN_TIMER_WAIT: 事件的最小耗时。MAX_TIMER_WAIT: 事件的最大耗时。
虽然这些数据很有价值，但是直接分析起来比较费劲。比如，耗时的单位是皮秒，需要转换成更容易理解的单位（如毫秒或秒）。而且，这些数据都是汇总的，无法看到具体的语句信息。
二、sys schema: Performance Schema的“翻译官”
sys schema 是MySQL 5.7版本之后引入的一个系统数据库。它建立在Performance Schema之上，提供了一系列视图和存储过程，将P_S中的原始数据转换成更容易理解和使用的形式。你可以把它想象成P_S的“翻译官”，把P_S的“火星文”翻译成我们能看懂的“地球语”。
要安装sys schema，可以使用以下命令：
CREATE SCHEMA IF NOT EXISTS sys;
SOURCE /usr/share/mysql/sys_schema.sql（路径根据你的MySQL安装位置调整）。
sys schema 提供了大量的视图，涵盖了数据库的各个方面，比如：
host_summary_by_statement_type: 按照主机和语句类型汇总的语句执行信息。innodb_lock_waits: InnoDB锁等待信息。io_global_by_wait_by_bytes: 按照等待事件汇总的IO信息。memory_global_by_event_name: 按照事件名称汇总的内存使用信息。processlist: 当前的进程列表，比SHOW PROCESSLIST更详细。schema_table_statistics: 数据库表的统计信息。user_summary_by_statement_type: 按照用户和语句类型汇总的语句执行信息。
这些视图都经过了精心设计，使用起来非常方便。
举个例子，我们来看看processlist这个视图：
SELECT * FROM sys.processlist WHERE time > 5 ORDER BY time DESC LIMIT 10;这个查询会列出执行时间超过5秒的进程，并按照执行时间降序排列。相比于SHOW PROCESSLIST，sys.processlist提供了更多的信息，比如：
user: 执行语句的用户。host: 执行语句的主机。db: 执行语句的数据库。command: 执行的命令类型（如Query、Sleep）。time: 语句执行的时间（秒）。state: 语句的当前状态。info: 正在执行的语句。rows_examined: 扫描的行数。rows_sent: 发送的行数。memory_used: 使用的内存。
这些信息可以帮助我们快速定位慢查询和性能瓶颈。
再比如，我们想知道哪些SQL语句消耗了最多的时间，可以使用statement_analysis视图：
SELECT * FROM sys.statement_analysis ORDER BY exec_time DESC LIMIT 10;这个视图会列出执行时间最长的SQL语句，并提供以下信息：
query: SQL语句。db: 数据库。full_scan: 是否进行了全表扫描。exec_count: 执行次数。errors: 错误次数。warnings: 警告次数。total_latency: 总耗时。max_latency: 最大耗时。avg_latency: 平均耗时。lock_latency: 锁等待时间。rows_sent: 发送的行数。rows_examined: 扫描的行数。rows_affected: 影响的行数。tmp_tables: 使用的临时表数量。tmp_disk_tables: 使用的磁盘临时表数量。digest: SQL语句的摘要。
通过分析这些信息，我们可以找到性能瓶颈，并采取相应的优化措施，比如添加索引、优化SQL语句等等。
三、从原始数据到高阶视图：转换与应用
sys schema 的核心价值在于它对Performance Schema原始数据的转换和聚合。它通过视图将原始数据转换成更易于理解和使用的形式，并提供了一些常用的查询和分析功能。
我们以statement_analysis视图为例，来看看它是如何从Performance Schema的原始数据转换而来的。statement_analysis视图的定义如下（简化版）：
CREATE OR REPLACE VIEW sys.statement_analysis AS
SELECT
statement AS query,
db,
full_scan,
exec_count,
errors,
warnings,
total_latency,
max_latency,
avg_latency,
lock_latency,
rows_sent,
rows_examined,
rows_affected,
tmp_tables,
tmp_disk_tables,
digest
FROM
(
SELECT
DIGEST AS digest,
TRUNCATE(SUM(TIMER_WAIT) / 1000000000000, 2) AS total_latency,
TRUNCATE(MAX(TIMER_WAIT) / 1000000000000, 2) AS max_latency,
TRUNCATE(AVG(TIMER_WAIT) / 1000000000000, 2) AS avg_latency,
TRUNCATE(SUM(LOCK_TIME) / 1000000000000, 2) AS lock_latency,
SUM(ROWS_SENT) AS rows_sent,
SUM(ROWS_EXAMINED) AS rows_examined,
SUM(ROWS_AFFECTED) AS rows_affected,
SUM(ERRORS) AS errors,
SUM(WARNINGS) AS warnings,
SUM(COUNT_STAR) AS exec_count,
SUM(SUM_CREATED_TMP_TABLES) AS tmp_tables,
SUM(SUM_CREATED_TMP_DISK_TABLES) AS tmp_disk_tables,
(SUM(ROWS_EXAMINED) > 0) AS full_scan
FROM
performance_schema.events_statements_summary_by_digest
WHERE
DIGEST_TEXT IS NOT NULL
GROUP BY
DIGEST
) AS st
JOIN
(
SELECT
DIGEST AS digest,
MAX(SQL_TEXT) AS statement,
MAX(SCHEMA_NAME) AS db
FROM
performance_schema.events_statements_history_long
WHERE
SCHEMA_NAME IS NOT NULL
GROUP BY
DIGEST
) AS ht ON st.digest = ht.digest;可以看到，statement_analysis视图主要从以下两个Performance Schema表中获取数据：
events_statements_summary_by_digest: 按照SQL语句摘要汇总的语句执行信息。events_statements_history_long: 记录SQL语句的完整文本和数据库信息。
statement_analysis视图的转换过程主要包括以下几个步骤：
- 数据聚合： 从
events_statements_summary_by_digest表中按照SQL语句摘要（DIGEST）进行分组，计算总耗时、最大耗时、平均耗时、锁等待时间、发送的行数、扫描的行数等等。 - 单位转换： 将耗时的单位从皮秒转换成秒（通过除以1000000000000）。
- 信息关联： 将聚合后的数据与
events_statements_history_long表关联，获取SQL语句的完整文本和数据库信息。 - 逻辑判断： 根据扫描的行数判断是否进行了全表扫描。
通过这些转换，statement_analysis视图将Performance Schema的原始数据转换成更易于理解和使用的形式，并提供了一些常用的查询和分析功能。
四、实际应用案例：性能诊断与优化
现在，我们来结合实际案例，看看如何利用Performance Schema和sys schema来进行性能诊断和优化。
案例一：慢查询诊断
假设我们的数据库突然变得很慢，我们需要找到导致性能下降的罪魁祸首。
首先，我们可以使用statement_analysis视图来找出执行时间最长的SQL语句：
SELECT * FROM sys.statement_analysis ORDER BY exec_time DESC LIMIT 10;如果发现某个SQL语句的执行时间很长，而且full_scan为TRUE，那么很可能这个SQL语句没有使用索引，导致了全表扫描。
接下来，我们可以使用EXPLAIN命令来分析这个SQL语句的执行计划：
EXPLAIN SELECT * FROM your_table WHERE your_column = 'your_value';如果EXPLAIN命令的输出结果中type列的值为ALL，那么说明这个SQL语句确实进行了全表扫描。
最后，我们可以通过添加索引来优化这个SQL语句：
CREATE INDEX your_index ON your_table (your_column);添加索引后，再次执行EXPLAIN命令，如果type列的值变成了ref或eq_ref，那么说明索引已经生效，SQL语句的性能应该会有所提升。
案例二：锁等待诊断
假设我们的数据库中出现了大量的锁等待，导致事务执行效率低下。
我们可以使用innodb_lock_waits视图来查看当前的锁等待信息：
SELECT * FROM sys.innodb_lock_waits;这个视图会列出正在等待锁的事务以及持有锁的事务的信息。
通过分析这些信息，我们可以找到导致锁等待的根源。比如，可能某个事务长时间持有锁不释放，导致其他事务无法执行。
找到原因后，我们可以采取相应的措施来解决锁等待问题，比如优化事务逻辑、减少锁的持有时间等等。
案例三：IO瓶颈诊断
假设我们的数据库的IO负载很高，导致性能下降。
我们可以使用io_global_by_wait_by_bytes视图来查看当前的IO信息：
SELECT * FROM sys.io_global_by_wait_by_bytes ORDER BY total DESC LIMIT 10;这个视图会按照等待事件汇总IO信息，包括读取的字节数、写入的字节数等等。
通过分析这些信息，我们可以找到导致IO瓶颈的原因。比如，可能某个表的数据量很大，导致读取数据时需要进行大量的IO操作。
找到原因后，我们可以采取相应的措施来解决IO瓶颈问题，比如优化数据存储结构、使用SSD等等。
五、Performance Schema和sys schema的配置与优化
虽然Performance Schema和sys schema可以帮助我们进行性能诊断和优化，但是它们也会对性能产生一定的影响。因此，我们需要合理配置和优化它们，以减少对性能的影响。
以下是一些常用的配置和优化技巧：
- 开启必要的事件： Performance Schema提供了大量的事件，但是我们不需要开启所有的事件。只需要开启我们关心的事件即可，比如
statement、wait、file等等。 - 调整事件的采样率： Performance Schema可以对事件进行采样，以减少对性能的影响。我们可以调整事件的采样率，比如只采样1%的事件。
- 限制事件的存储大小： Performance Schema的事件数据存储在内存中，如果存储的数据量太大，可能会导致内存溢出。我们可以限制事件的存储大小，比如只存储最近1000个事件。
- 定期清理事件数据： Performance Schema的事件数据会不断增长，如果不定期清理，可能会导致性能下降。我们可以定期清理事件数据，比如每天凌晨清理一次。
- 监控Performance Schema的性能： 我们可以使用Performance Schema来监控自身的性能，比如查看Performance Schema占用的内存、CPU等等。
总之，Performance Schema和sys schema是MySQL性能诊断和优化的利器，但是我们需要合理配置和优化它们，才能发挥它们的最大价值。
六、总结
今天我们一起学习了MySQL Performance Schema和sys schema，了解了它们的作用、原理和应用。希望通过今天的学习，大家能够更加深入地理解MySQL的运行机制，能够更加熟练地使用Performance Schema和sys schema来进行性能诊断和优化。
记住，Performance Schema是MySQL的“黑匣子”，记录了各种服务器事件的详细信息；sys schema是Performance Schema的“翻译官”，将原始数据转换成更易于理解和使用的形式。
通过合理配置和优化Performance Schema和sys schema，我们可以打造一个更加稳定、高效的MySQL数据库系统。
好了，今天的分享就到这里，谢谢大家！有什么问题可以随时提问。祝大家学习愉快，工作顺利！