Sys Schema：简化 Performance Schema 查询的利器
各位同学，大家好！今天我们来探讨一下 MySQL 中一个非常有用的 schema，那就是 sys schema。 它的主要目的就是简化对 Performance Schema 的查询，让 DBA 和开发者更容易地分析数据库性能问题。 我们将深入了解 sys schema 的设计思想、核心组件以及如何利用它来提高工作效率。
Performance Schema 的挑战
Performance Schema 提供了 MySQL 服务器运行时的底层详细信息，包括线程状态、内存分配、锁等待等等。它是一个强大的工具，但直接使用它也存在一些挑战：
- 数据量大：
Performance Schema收集的数据非常详细，导致相关表的数据量巨大，直接查询效率较低。 - 数据结构复杂：
Performance Schema的表结构设计面向底层实现，字段名称和数据类型相对晦涩，不易理解。 - 缺乏聚合和统计：
Performance Schema记录的是原始事件数据，缺乏对数据的聚合和统计，需要用户自行编写复杂的 SQL 进行分析。
例如，如果我们想知道哪些 SQL 语句执行时间最长，直接查询 Performance Schema 的 events_statements_summary_by_digest 表，需要考虑以下几个问题：
DIGEST列是什么意思？COUNT_STAR、SUM_TIMER_WAIT、AVG_TIMER_WAIT这些字段代表什么？单位是什么？- 如何将
TIMER值转换为可读的时间单位？ - 如何过滤出执行时间较长的 SQL 语句？
编写这样一个查询语句需要对 Performance Schema 有深入的了解，并且需要花费大量时间。
Sys Schema 的设计理念
Sys Schema 正是为了解决上述问题而设计的。 它的核心理念是：
- 简化： 通过视图和存储过程，将复杂的
Performance Schema数据转换为更易于理解和使用的形式。 - 聚合： 对
Performance Schema的原始数据进行聚合和统计，提供预先计算好的指标。 - 可读性： 使用更友好的字段名称和数据类型，提高数据的可读性。
- 易用性： 提供常用的查询语句和分析脚本，方便用户快速定位性能问题。
Sys Schema 本质上是一个建立在 Performance Schema 之上的视图集合。 它并不存储数据，而是通过视图动态地从 Performance Schema 中提取和转换数据。
Sys Schema 的核心组件
Sys Schema 主要由以下几类对象组成：
- 视图 (Views): 这是
Sys Schema的核心组成部分，提供了对Performance Schema数据的各种聚合和统计视图。 - 存储过程 (Stored Procedures): 提供了一些常用的管理和诊断功能，例如重置
Performance Schema的统计数据。 - 函数 (Functions): 提供了一些辅助函数，例如将
TIMER值转换为可读的时间单位。
我们重点关注视图，因为它们是 Sys Schema 提供简化查询的主要方式。 Sys Schema 的视图按照功能可以分为以下几类：
- 连接信息： 提供关于客户端连接的信息，例如连接数、连接时长等。
- 语句执行： 提供关于 SQL 语句执行的信息，例如执行次数、执行时间、锁定时间等。
- I/O 操作： 提供关于 I/O 操作的信息，例如读取和写入的数据量、I/O 等待时间等。
- 内存使用： 提供关于内存使用情况的信息，例如全局内存分配、线程内存分配等。
- 锁信息： 提供关于锁的信息，例如锁等待时间、锁冲突次数等。
- 文件 I/O： 提供更详细的文件 I/O 信息，例如哪些文件被频繁访问。
- 等待事件： 提供更详细的等待事件信息，例如等待的类型、等待的时间等。
- Host 信息： 提供关于 Host 的统计信息，例如来自哪些 Host 的连接最多。
- 用户信息： 提供关于用户的统计信息，例如哪些用户执行的语句最多。
接下来，我们将通过一些示例来演示如何使用 Sys Schema 的视图来分析数据库性能问题。
Sys Schema 示例：查找执行时间最长的 SQL 语句
回到我们之前提出的问题：如何查找执行时间最长的 SQL 语句？ 使用 Sys Schema，我们可以非常轻松地完成这个任务。
Sys Schema 提供了一个名为 statement_analysis 的视图，它已经对 Performance Schema 的 events_statements_summary_by_digest 表进行了聚合和统计。 我们可以直接查询这个视图来获取执行时间最长的 SQL 语句。
SELECT
query,
exec_count,
total_latency,
avg_latency,
lock_time,
rows_sent,
rows_examined
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;这个查询语句的含义如下：
query： SQL 语句的文本。exec_count： SQL 语句的执行次数。total_latency： SQL 语句的总执行时间。avg_latency： SQL 语句的平均执行时间。lock_time： SQL 语句的锁定时间。rows_sent： SQL 语句返回的行数。rows_examined： SQL 语句扫描的行数。
total_latency 列已经将 TIMER 值转换为可读的时间单位，例如秒、毫秒等。 我们可以直接根据 total_latency 列进行排序，找到执行时间最长的 SQL 语句。
与直接查询 Performance Schema 相比，使用 Sys Schema 的 statement_analysis 视图，我们无需关心底层的数据结构和单位转换，可以直接获取我们需要的信息，大大简化了查询过程。
Sys Schema 示例：查找消耗最多 I/O 的表
如何查找消耗最多 I/O 的表？ Sys Schema 提供了一个名为 io_global_by_file_by_bytes 的视图，它已经对 Performance Schema 的文件 I/O 数据进行了聚合和统计。 我们可以直接查询这个视图来获取消耗最多 I/O 的表。
SELECT
file,
count_read,
sum_number_of_bytes_read,
count_write,
sum_number_of_bytes_write,
total
FROM sys.io_global_by_file_by_bytes
ORDER BY total DESC
LIMIT 10;这个查询语句的含义如下：
file： 文件名。count_read： 读取次数。sum_number_of_bytes_read： 读取的总字节数。count_write： 写入次数。sum_number_of_bytes_write： 写入的总字节数。total： 总的 I/O 量 (读取 + 写入)。
通过这个查询，我们可以快速找到消耗最多 I/O 的表，从而可以针对这些表进行优化，例如添加索引、调整数据存储方式等。
Sys Schema 示例：查看当前连接信息
如何查看当前连接信息？ Sys Schema 提供了一个名为 processlist 的视图，它类似于 SHOW PROCESSLIST 命令，但提供了更多的信息。
SELECT
user,
host,
db,
command,
time,
state,
info
FROM sys.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;这个查询语句的含义如下：
user： 连接的用户。host： 连接的客户端主机。db： 连接的数据库。command： 连接正在执行的命令。time： 连接已经处于当前状态的时间。state： 连接的当前状态。info： 连接正在执行的 SQL 语句。
通过这个查询，我们可以实时监控数据库的连接情况，例如哪些连接正在执行长时间的查询，哪些连接处于等待状态等。
Sys Schema 的优势总结
通过以上示例，我们可以看到 Sys Schema 的优势：
- 易于使用：
Sys Schema的视图提供了友好的字段名称和数据类型，方便用户理解和使用。 - 减少代码量：
Sys Schema已经对Performance Schema的数据进行了聚合和统计，减少了用户编写复杂 SQL 语句的需求。 - 提高效率：
Sys Schema的视图已经进行了优化，查询效率较高。 - 可扩展性： 用户可以根据自己的需求创建自定义的视图，扩展
Sys Schema的功能。
Sys Schema 的限制
虽然 Sys Schema 提供了很多便利，但它也有一些限制：
- 依赖于 Performance Schema：
Sys Schema的视图是建立在Performance Schema之上的，因此必须启用Performance Schema才能使用Sys Schema。 启用Performance Schema会带来一定的性能开销，需要根据实际情况进行评估。 - 数据延迟：
Sys Schema的视图是动态地从Performance Schema中提取和转换数据的，因此存在一定的数据延迟。 对于实时性要求非常高的场景，可能需要直接查询Performance Schema。 - 可能存在不准确性： 由于
Performance Schema记录的是原始事件数据，因此Sys Schema的聚合和统计结果可能存在一定的不准确性。
启用 Sys Schema 和 Performance Schema
要使用 Sys Schema，首先需要确保已经安装了 Sys Schema，并且启用了 Performance Schema。
- 安装 Sys Schema：
Sys Schema通常是 MySQL 默认安装的一部分。 如果没有安装，可以使用mysql_upgrade命令进行安装。 -
启用 Performance Schema：
Performance Schema默认是禁用的。 可以在 MySQL 配置文件 (例如my.cnf或my.ini) 中启用它：[mysqld] performance_schema=ON修改配置文件后，需要重启 MySQL 服务器。 也可以在运行时动态启用
Performance Schema：UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; UPDATE performance_schema.setup_consumers SET enabled = 'YES';但是，这种方式只在当前会话有效，重启服务器后会失效。
总结
Sys Schema 是一个非常有用的工具，它可以简化对 Performance Schema 的查询，帮助 DBA 和开发者更容易地分析数据库性能问题。 通过视图和存储过程，Sys Schema 将复杂的 Performance Schema 数据转换为更易于理解和使用的形式，提高了工作效率。
希望今天的讲座能够帮助大家更好地理解和使用 Sys Schema。 谢谢大家！
记住Sys Schema，简化性能分析
Sys Schema 通过视图简化了Performance Schema的查询，让性能分析更加便捷。 理解其设计理念和核心组件，可以帮助我们更高效地定位和解决数据库性能问题。