MySQL性能诊断与调优之：`MySQL`的`Sys Schema`：其在简化性能监控中的视图。

MySQL性能诊断与调优：Sys Schema在简化性能监控中的应用

大家好，今天我们来聊聊MySQL性能诊断与调优中的一个非常强大的工具——Sys Schema。很多DBA和开发人员在进行MySQL性能监控和问题排查时，常常会感到力不从心，因为需要编写复杂的SQL语句来获取所需的信息。Sys Schema的出现，极大地简化了这一过程，它提供了一系列预定义的视图，可以帮助我们快速定位性能瓶颈。

什么是Sys Schema？

Sys Schema 是 MySQL 5.7.7 版本引入的一个数据库，它通过查询 Performance Schema 和 Information Schema 中的数据，提供了更易于理解和使用的视图，用于监控 MySQL 的性能。简单来说，Sys Schema 就是一个将复杂的性能数据转化为人类可读信息的翻译器。

核心功能：

• 简化性能监控： 提供预定义的视图，无需编写复杂的 SQL 语句。
• 数据聚合与分析： 将原始的性能数据进行聚合和分析，呈现更高级别的性能指标。
• 问题定位： 帮助快速定位性能瓶颈，如慢查询、锁等待、IO 压力等。

Sys Schema 的安装与启用

通常情况下，Sys Schema 会随 MySQL 5.7.7 及更高版本一起安装。如果你的 MySQL 版本低于 5.7.7，或者 Sys Schema 没有正确安装，你可以手动安装它。

检查 Sys Schema 是否已安装：

SHOW DATABASES LIKE 'sys';

如果结果中包含 sys 数据库，则表示已安装。

手动安装 Sys Schema（如果未安装）：

CREATE DATABASE sys;
USE sys;

-- 使用 mysql_upgrade 创建 sys schema 视图
mysql_upgrade -u root -p --force

-- 或者从源代码安装，这需要从 MySQL 官方网站下载源代码
-- 具体步骤可以参考 MySQL 官方文档

安装完成后，你需要确保 sys 数据库的用户具有足够的权限来访问 Performance Schema 和 Information Schema 中的数据。

授予用户访问权限：

GRANT SELECT ON performance_schema.* TO 'your_user'@'your_host';
GRANT SELECT ON information_schema.* TO 'your_user'@'your_host';
GRANT SELECT ON sys.* TO 'your_user'@'your_host';
FLUSH PRIVILEGES;

Sys Schema 中的核心视图

Sys Schema 包含大量的视图，涵盖了 MySQL 性能监控的各个方面。下面列出一些常用的视图，并进行详细讲解：

1. host_summary 和 host_summary_by_file_io: 按主机统计信息，例如连接数、SQL 执行时间、文件 I/O 等。
2. io_global_by_file_by_bytes: 按文件统计全局 I/O，例如读取/写入的字节数。
3. io_global_by_wait_by_bytes: 按等待事件统计全局 I/O，例如等待 I/O 的时间。
4. memory_global_by_event_name: 按事件名统计全局内存使用情况。
5. processlist: 类似于 SHOW PROCESSLIST，但提供了更详细的信息，例如 SQL 执行时间、锁等待等。
6. schema_auto_increment_columns: 显示数据库中所有自增列的信息。
7. schema_index_statistics: 显示数据库中索引的使用统计信息。
8. schema_table_statistics 和 schema_table_statistics_with_buffer: 显示数据库中表的统计信息，包括行数、数据大小、索引大小等。
9. session 和 session_ssl: 显示当前会话的信息，例如连接时间、客户端 IP 地址等。
10. statement_analysis: 显示 SQL 语句的分析结果，例如执行次数、平均执行时间、最大执行时间等。
11. user_summary 和 user_summary_by_statement_type: 按用户统计信息，例如连接数、SQL 执行时间、不同类型 SQL 语句的执行次数等。
12. wait_classes_global_by_avg_latency: 按等待事件类型统计全局平均延迟。

下面我们通过一些具体的例子来演示如何使用这些视图进行性能监控和问题排查。

实际应用案例

1. 查找执行时间最长的 SQL 语句：

SELECT * FROM sys.statement_analysis ORDER BY exec_time DESC LIMIT 10;

这个查询会返回执行时间最长的 10 条 SQL 语句，包括它们的执行次数、平均执行时间、最大执行时间、以及 SQL 语句本身。

输出示例：

query	exec_count	total_latency	avg_latency	lock_time	rows_sent	rows_examined	first_seen	last_seen	db	full_scan	full_join	tmp_table	tmp_disk_table
SELECT * FROM slow_table	1	1.23s	1.23s	0s	1000	10000	2023-10-27 10:00:00 UTC	2023-10-27 10:00:00 UTC	test	YES	NO	NO	NO
SELECT COUNT(*) FROM another_slow_table	1	0.87s	0.87s	0s	1	5000	2023-10-27 10:01:00 UTC	2023-10-27 10:01:00 UTC	test	YES	NO	NO	NO

通过 statement_analysis 视图，我们可以快速找到哪些 SQL 语句是性能瓶颈，然后针对这些语句进行优化，例如添加索引、重写 SQL 语句等。

2. 查找执行全表扫描的 SQL 语句：

SELECT * FROM sys.statement_analysis WHERE full_scan = 'YES' ORDER BY exec_time DESC LIMIT 10;

这个查询会返回执行全表扫描的 SQL 语句，这意味着 MySQL 需要扫描整个表才能找到所需的数据，这通常会导致性能问题。

输出示例：

query	exec_count	total_latency	avg_latency	lock_time	rows_sent	rows_examined	first_seen	last_seen	db	full_scan	full_join	tmp_table	tmp_disk_table
SELECT * FROM slow_table	1	1.23s	1.23s	0s	1000	10000	2023-10-27 10:00:00 UTC	2023-10-27 10:00:00 UTC	test	YES	NO	NO	NO
SELECT COUNT(*) FROM another_slow_table	1	0.87s	0.87s	0s	1	5000	2023-10-27 10:01:00 UTC	2023-10-27 10:01:00 UTC	test	YES	NO	NO	NO

找到全表扫描的 SQL 语句后，我们需要检查是否缺少索引，或者索引是否使用不当。

3. 查找执行全表连接的 SQL 语句：

SELECT * FROM sys.statement_analysis WHERE full_join = 'YES' ORDER BY exec_time DESC LIMIT 10;

全表连接是指 MySQL 在连接两个表时，没有使用索引，而是对两个表都进行全表扫描，这会导致性能问题。

输出示例：

query	exec_count	total_latency	avg_latency	lock_time	rows_sent	rows_examined	first_seen	last_seen	db	full_scan	full_join	tmp_table	tmp_disk_table
SELECT * FROM table1 JOIN table2	1	2.50s	2.50s	0s	500	1000000	2023-10-27 10:02:00 UTC	2023-10-27 10:02:00 UTC	test	NO	YES	NO	NO

对于全表连接，我们需要检查连接条件是否缺少索引，或者索引是否使用不当。

4. 查找使用临时表的 SQL 语句：

SELECT * FROM sys.statement_analysis WHERE tmp_table = 'YES' ORDER BY exec_time DESC LIMIT 10;

使用临时表意味着 MySQL 需要将中间结果存储在磁盘或内存中，这会增加 I/O 压力和内存消耗。

输出示例：

query	exec_count	total_latency	avg_latency	lock_time	rows_sent	rows_examined	first_seen	last_seen	db	full_scan	full_join	tmp_table	tmp_disk_table
SELECT * FROM table ORDER BY column	1	0.90s	0.90s	0s	1000	1000	2023-10-27 10:03:00 UTC	2023-10-27 10:03:00 UTC	test	NO	NO	YES	NO

对于使用临时表的 SQL 语句，我们需要尽量避免使用 ORDER BY、GROUP BY 等操作，或者优化 SQL 语句以减少临时表的使用。

5. 查找执行慢查询的 SQL 语句（超过 1 秒）：

SELECT * FROM sys.statement_analysis WHERE total_latency > '1s' ORDER BY total_latency DESC LIMIT 10;

这个查询会返回总执行时间超过 1 秒的 SQL 语句，可以根据实际情况调整时间阈值。

输出示例：

query	exec_count	total_latency	avg_latency	lock_time	rows_sent	rows_examined	first_seen	last_seen	db	full_scan	full_join	tmp_table	tmp_disk_table
SELECT * FROM slow_table	1	1.23s	1.23s	0s	1000	10000	2023-10-27 10:00:00 UTC	2023-10-27 10:00:00 UTC	test	YES	NO	NO	NO
SELECT COUNT(*) FROM another_slow_table	1	0.87s	0.87s	0s	1	5000	2023-10-27 10:01:00 UTC	2023-10-27 10:01:00 UTC	test	YES	NO	NO	NO

6. 查看表占用空间大小：

SELECT
    table_schema AS `Database`,
    table_name AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size in MB`
FROM
    information_schema.TABLES
WHERE
    table_schema = 'your_database_name'
ORDER BY
    (data_length + index_length) DESC;

--  使用 sys schema 简化
SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'your_database_name' ORDER BY data_size + index_size DESC;

这两个查询都可以查看数据库中表的大小，但是使用 sys schema 的查询更加简洁易懂。

7. 监控 InnoDB 缓冲池效率：

Sys Schema 本身不直接提供 InnoDB 缓冲池的详细指标，但可以通过结合 Performance Schema 和 Information Schema 来实现监控。 然而, 我们可以通过观察全局IO来间接评估缓冲池的效率。 频繁的磁盘读取可能暗示缓冲池效率低下。

SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY read_bytes DESC LIMIT 10;

这个查询会显示哪些文件读取的字节数最多，如果发现某个表的数据文件读取的字节数很多，可能意味着 InnoDB 缓冲池的效率不高，需要调整 innodb_buffer_pool_size 参数。

8. 查看当前活跃连接数：

SELECT * FROM sys.host_summary ORDER BY current_connections DESC LIMIT 10;

这个查询可以显示每个主机的当前连接数，可以帮助我们了解哪些主机正在连接到数据库。

9. 查找锁等待最长的会话：

虽然 Sys Schema 没有直接提供锁等待的详细视图，但可以通过结合 Performance Schema 和 Information Schema 来实现。 我们可以使用 processlist 视图查看当前正在等待锁的会话。

SELECT * FROM sys.processlist WHERE TIME > 10 AND state LIKE '%lock%'; --  查找等待时间超过10秒的锁

这个查询会显示等待锁的时间超过 10 秒的会话，可以帮助我们找到导致锁等待的会话，然后分析这些会话执行的 SQL 语句，找出导致锁等待的原因。

10. 监控用户连接数和活动：

SELECT * FROM sys.user_summary ORDER BY connections DESC LIMIT 10;
SELECT * FROM sys.user_summary_by_statement_type ORDER BY statement_count DESC LIMIT 10;

第一个查询显示了每个用户的连接数，第二个查询显示了每个用户执行的不同类型的 SQL 语句的数量。

Sys Schema 的优势与局限性

优势：

• 易用性： 提供预定义的视图，无需编写复杂的 SQL 语句。
• 可读性： 将原始的性能数据转化为人类可读的信息。
• 问题定位： 帮助快速定位性能瓶颈。
• 降低学习成本： 简化了 MySQL 性能监控的学习曲线。

局限性：

• 依赖 Performance Schema： Sys Schema 依赖于 Performance Schema，如果 Performance Schema 未启用或配置不当，Sys Schema 将无法正常工作。
• 数据滞后性： Sys Schema 中的数据是基于 Performance Schema 的，因此可能存在一定的滞后性。
• 不够灵活： Sys Schema 提供的视图是预定义的，可能无法满足所有需求，有时需要编写自定义的 SQL 语句。
• 性能开销： 启用 Performance Schema 会带来一定的性能开销，特别是在高并发的场景下。

最佳实践

• 启用 Performance Schema： 确保 Performance Schema 已启用，并且配置合理。
• 定期监控： 定期使用 Sys Schema 监控 MySQL 的性能，及时发现潜在的问题。
• 结合其他工具： 将 Sys Schema 与其他性能监控工具（如 pt-query-digest、Grafana）结合使用，可以获得更全面的性能信息。
• 自定义视图： 如果 Sys Schema 提供的视图无法满足需求，可以创建自定义的视图。
• 注意性能开销： 在生产环境中启用 Performance Schema 时，需要注意性能开销，并根据实际情况进行调整。

总结：Sys Schema 简化了性能监控的复杂性

Sys Schema 是 MySQL 性能诊断与调优中一个非常有用的工具，它通过提供预定义的视图，简化了性能监控的复杂性，帮助我们快速定位性能瓶颈。 虽然它有局限性，但只要合理使用，就可以极大地提高 MySQL 的性能管理效率。 熟悉并掌握 Sys Schema 的使用，是成为一名优秀的 MySQL DBA 的必备技能。