在 Azure Database for MySQL 灵活服务器中使用 sys_schema 优化性能和维护数据库

适用于：Azure Database for MySQL - 单一服务器 Azure Database for MySQL - 灵活服务器

MySQL performance_schema 首先在 MySQL 5.5 中推出，针对许多关键服务器资源提供检测数据，例如内存分配、存储的程序、元数据锁定，等等。但是，performance_schema 包含超过 80 多个表，获取必要的信息通常需要联接 performance_schema 和 information_schema 中的表。 sys_schema 在 performance_schema 和 information_schema 的基础上构建，在一个只读的数据库中提供用户友好视图的强大集合，并且完全在 Azure Database for MySQL 灵活服务器版本 5.7 中启用。

sys_schema 中有 52 个视图，每个视图具有以下前缀之一：

• Host_summary 或 IO：与 I/O 相关的延迟。
• InnoDB：InnoDB 缓冲区状态和锁。
• 内存: 按主机和用户列出的内存用量。
• 架构：与架构相关的信息，如增量、索引等。
• 语句：有关 SQL 语句（导致全表扫描或长时间查询的语句）的信息。
• 用户：按用户分组和消耗的资源。 示例包括文件 I/O、连接和内存。
• 等待：等待按主机或用户分组的事件。

现在，让我们了解 sys_schema 的一些常见使用模式。 首先，我们将使用模式分为两类：“性能调优”和“数据库维护” 。

性能调优

sys.user_summary_by_file_io

IO 是数据库中开销最高的操作。 我们可以通过查询 sys.user_summary_by_file_io 视图找出平均 IO 延迟。 使用 125 GB 默认预配存储时，IO 延迟大约为 15 秒。

由于 Azure Database for MySQL 灵活服务器可根据存储缩放 IO，将预配存储增大到 1 TB 后，IO 延迟减小为 571 毫秒。

sys.schema_tables_with_full_table_scans

尽管经过认真规划，但许多查询仍可能导致全表扫描。 有关索引类型及其优化方式的详细信息，请参阅此文：如何排查查询性能问题。 全表扫描属于资源密集型操作，会降低数据库性能。 查找执行全表扫描的表的最快方法是查询 sys.schema_tables_with_full_table_scans 视图。

sys.user_summary_by_statement_type

若要排查数据库性能问题，识别数据库中发生的事件可能很有帮助，而使用 sys.user_summary_by_statement_type 视图就能实现此目的。

在此示例中，Azure Database for MySQL 灵活服务器花费了 53 分钟来刷新 slow 查询日志 44579 次。 此时间很长，并且消耗了大量 IO 资源。 可以通过禁用慢查询日志或降低登录 Azure 门户的慢查询频率来减少此活动。

数据库维护

sys.innodb_buffer_stats_by_table

[!IMPORTANT]

查询此视图可能会影响性能。 建议在非高峰营业时间执行此故障排除。

InnoDB 缓冲池驻留在内存中，是 DBMS 与存储之间的主要缓存机制。 InnoDB 缓冲池大小与性能层密切相关，除非选择不同的产品 SKU，否则不能更改。 与操作系统中的内存一样，旧页面将被换出，以便为较新数据留出空间。 若要了解哪些表占用了大部分 InnoDB 缓冲池内存，可以查询 sys.innodb_buffer_stats_by_table 视图。

在上图中，很明显，除系统表和视图以外，mysqldatabase033 数据库（托管某个 WordPress 站点）中的每个表占用了 16 KB 或 1 个页面的内存中数据。

Sys.schema_unused_indexes 和 sys.schema_redundant_indexes

索引是提高读取性能的极佳工具，但它们确实会产生额外的插入和存储开销。 Sys.schema_unused_indexes 和 sys.schema_redundant_indexes 提供未使用或重复索引的洞察信息。

结论

总而言之，sys_schema 是用于优化性能和维护数据库的极佳工具。 请务必在 Azure Database for MySQL 灵活服务器实例中利用此功能。

后续步骤

• 若要查找同行对你最关心问题的解答，或者要发布新的问题/答案，请访问 Stack Overflow。