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

MySQL性能诊断与调优之：Sys Schema：简化性能监控的视图

各位同学，今天我们来聊聊MySQL性能诊断与调优中一个非常有用的工具：Sys Schema。很多时候，我们面对缓慢的查询、高CPU使用率或者IO瓶颈，都感觉束手无策，不知道从何下手。Sys Schema 就像一位经验丰富的侦探，它能帮助我们快速定位问题，简化性能监控流程。

什么是Sys Schema？

Sys Schema 是 MySQL 5.7 版本引入的一个系统数据库，它提供了一系列预定义的视图，这些视图基于 Performance Schema 和 Information Schema，旨在以更易于理解和使用的格式呈现性能数据。简单来说，Sys Schema 就是 Performance Schema 的“翻译器”，它将原始的、复杂的性能数据转化为更有意义的统计信息，方便我们分析和监控数据库的运行状况。

相比于直接查询 Performance Schema，Sys Schema 的优势在于：

• 易于使用: 提供了更友好的视图名称和字段名称，更接近人类语言。
• 预聚合: 对数据进行了预处理和聚合，减少了查询复杂度，提高了查询效率。
• 诊断建议: 部分视图提供了诊断建议，帮助我们快速找到潜在的性能问题。

Sys Schema 的核心功能

Sys Schema 主要通过以下几个方面简化性能监控：

• I/O 监控: 了解磁盘I/O瓶颈。
• 内存监控: 分析内存使用情况，避免内存溢出。
• CPU 监控: 识别消耗大量CPU资源的查询。
• 锁监控: 检测锁竞争情况，优化并发性能。
• 连接监控: 监控连接数和连接状态，避免连接数耗尽。
• 语句监控: 追踪执行时间长的SQL语句，进行优化。
• 等待事件监控: 分析等待事件，找出瓶颈所在。
• 索引使用监控: 评估索引使用情况，优化索引设计。

Sys Schema 的主要视图分类

Sys Schema 包含了大量的视图，为了方便理解，我们可以将它们大致分为以下几类：

分类	描述	示例视图
Host Stats	关注主机级别的统计信息，例如连接数、I/O 负载等。	host_summary, host_summary_by_file_io, host_summary_by_stages, host_summary_by_statement_type
User Stats	关注用户级别的统计信息，例如每个用户的连接数、执行的查询等。	user_summary, user_summary_by_file_io, user_summary_by_stages, user_summary_by_statement_type
Connection Stats	关注连接级别的统计信息，例如连接的客户端IP地址、连接时长等。	session, session_ssl_status
Statement Analysis	分析SQL语句的执行情况，例如执行次数、平均执行时间、最大执行时间等。这是性能调优的核心部分。	statement_analysis, statements_with_errors_or_warnings, statements_with_full_table_scans, statements_with_sorting
Wait Analysis	分析等待事件，例如I/O等待、锁等待等。等待事件是导致性能瓶颈的重要原因。	wait_classes_global_by_avg_latency, waits_global_by_latency, waits_by_host_by_latency, waits_by_user_by_latency
IO Analysis	关注I/O相关的统计信息，例如文件I/O、表空间I/O等。	io_global_by_file_by_bytes, io_global_by_file_by_latency, schema_auto_index_usage, schema_index_statistics, schema_table_statistics, schema_table_statistics_with_buffer
Memory Analysis	分析内存使用情况，例如全局内存使用、表缓存使用等。	memory_global_by_current_bytes, memory_global_total
Schema Analysis	关注数据库级别的统计信息，例如表的大小、索引的使用情况等。	schema_auto_index_usage, schema_index_statistics, schema_table_statistics, schema_table_statistics_with_buffer
Object Analysis	关注特定对象的统计信息，例如存储过程、函数等。	ps_check_lost_instrumentation
Processlist	类似 SHOW PROCESSLIST，但提供了更多信息。	processlist
Diagnosis	提供了一些诊断视图，帮助我们快速发现潜在的问题。	diagnostics

案例分析：使用 Sys Schema 进行性能诊断

接下来，我们通过几个实际的案例来演示如何使用 Sys Schema 进行性能诊断。

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

假设我们发现数据库响应速度变慢，怀疑是某个 SQL 语句执行时间过长导致的。我们可以使用 statement_analysis 视图来查找执行时间最长的 SQL 语句。

SELECT
    query,
    exec_count,
    ROUND(avg_latency / 1000000, 2) AS avg_latency_sec,
    ROUND(max_latency / 1000000, 2) AS max_latency_sec,
    ROUND(sum_latency / 1000000, 2) AS sum_latency_sec,
    ROUND(lock_time / 1000000, 2) AS lock_time_sec,
    ROUND(errors, 2) AS errors,
    ROUND(warnings, 2) AS warnings,
    rows_examined,
    rows_sent,
    db
FROM
    sys.statement_analysis
ORDER BY
    sum_latency DESC
LIMIT 10;

这个查询会返回执行时间最长的 10 个 SQL 语句，包括它们的执行次数、平均执行时间、最大执行时间、锁等待时间、错误数、警告数、扫描的行数、返回的行数以及所在的数据库。通过分析这些信息，我们可以找出潜在的性能瓶颈。

示例结果：

query	exec_count	avg_latency_sec	max_latency_sec	sum_latency_sec	lock_time_sec	errors	warnings	rows_examined	rows_sent	db
SELECT * FROM orders WHERE customer_id = ?	1000	0.50	1.00	500.00	0.01	0	0	100000	1	ecommerce
UPDATE products SET stock = stock – ? WHERE id = ?	500	0.20	0.40	100.00	0.00	0	0	500	1	ecommerce

从结果可以看出，SELECT * FROM orders WHERE customer_id = ? 这个查询执行时间最长，总共消耗了 500 秒。接下来，我们可以针对这个查询进行优化，例如添加索引或者重写 SQL 语句。

案例 2：查找全表扫描的 SQL 语句

全表扫描通常会导致性能下降，因为它需要扫描整个表才能找到符合条件的记录。我们可以使用 statements_with_full_table_scans 视图来查找执行全表扫描的 SQL 语句。

SELECT
    query,
    exec_count,
    ROUND(avg_latency / 1000000, 2) AS avg_latency_sec,
    ROUND(lock_time / 1000000, 2) AS lock_time_sec,
    rows_examined,
    rows_sent,
    db
FROM
    sys.statements_with_full_table_scans
ORDER BY
    rows_examined DESC
LIMIT 10;

这个查询会返回扫描行数最多的 10 个执行全表扫描的 SQL 语句，包括它们的执行次数、平均执行时间、锁等待时间、扫描的行数、返回的行数以及所在的数据库。通过分析这些信息，我们可以找出需要添加索引的表。

示例结果：

query	exec_count	avg_latency_sec	lock_time_sec	rows_examined	rows_sent	db
SELECT * FROM products WHERE price > ?	10	0.10	0.00	1000000	1000	ecommerce
SELECT * FROM users WHERE city = ?	5	0.05	0.00	500000	500	ecommerce

从结果可以看出，SELECT * FROM products WHERE price > ? 这个查询扫描了 100 万行数据，但是只返回了 1000 行数据，说明需要在这个表的 price 字段上添加索引。

案例 3：查找使用临时表的 SQL 语句

使用临时表也会导致性能下降，因为它需要额外的磁盘 I/O 和 CPU 资源。我们可以使用 statement_analysis 视图结合 tmp_tables 字段来查找使用临时表的 SQL 语句。

SELECT
    query,
    exec_count,
    ROUND(avg_latency / 1000000, 2) AS avg_latency_sec,
    ROUND(lock_time / 1000000, 2) AS lock_time_sec,
    rows_examined,
    rows_sent,
    tmp_tables,
    db
FROM
    sys.statement_analysis
WHERE
    tmp_tables > 0
ORDER BY
    tmp_tables DESC, sum_latency DESC
LIMIT 10;

这个查询会返回使用临时表次数最多的 10 个 SQL 语句，包括它们的执行次数、平均执行时间、锁等待时间、扫描的行数、返回的行数、使用的临时表数量以及所在的数据库。通过分析这些信息，我们可以尝试优化 SQL 语句，避免使用临时表。

示例结果：

query	exec_count	avg_latency_sec	lock_time_sec	rows_examined	rows_sent	tmp_tables	db
SELECT order_id, SUM(amount) FROM order_items GROUP BY order_id	5	0.20	0.00	100000	1000	1	ecommerce
SELECT * FROM users ORDER BY signup_date LIMIT 100	10	0.15	0.00	500000	100	1	ecommerce

从结果可以看出，SELECT order_id, SUM(amount) FROM order_items GROUP BY order_id 这个查询使用了临时表，可以尝试优化这个查询，例如添加索引或者使用物化视图。

案例 4：监控文件 I/O

磁盘 I/O 是影响数据库性能的重要因素之一。我们可以使用 io_global_by_file_by_bytes 视图来监控文件 I/O 情况。

SELECT
    file,
    ROUND(sum_number_of_bytes_read / 1024 / 1024, 2) AS mb_read,
    ROUND(sum_number_of_bytes_written / 1024 / 1024, 2) AS mb_written,
    ROUND((sum_number_of_bytes_read + sum_number_of_bytes_written) / 1024 / 1024, 2) AS mb_total
FROM
    sys.io_global_by_file_by_bytes
ORDER BY
    mb_total DESC
LIMIT 10;

这个查询会返回读取和写入字节数最多的 10 个文件，包括文件名、读取的字节数、写入的字节数以及总的字节数。通过分析这些信息，我们可以找出 I/O 瓶颈所在，例如数据文件、日志文件或者临时文件。

示例结果：

file	mb_read	mb_written	mb_total
./ecommerce/orders.ibd	1000.00	500.00	1500.00
./ecommerce/products.ibd	500.00	200.00	700.00
./mysql/undo_001	100.00	50.00	150.00
./binlog.000001	0.00	100.00	100.00

从结果可以看出，./ecommerce/orders.ibd 文件的 I/O 量最大，说明 orders 表的读写操作比较频繁，可以考虑优化这个表的查询和更新操作。

案例 5：监控锁等待

锁等待是导致并发性能下降的重要原因。我们可以使用 waits_global_by_latency 视图来监控全局的锁等待情况。

SELECT
    event_name,
    COUNT(*) AS cnt,
    ROUND(SUM(sum_timer_wait) / 1000000000000, 2) AS sum_sec,
    ROUND(AVG(avg_timer_wait) / 1000000000000, 2) AS avg_sec
FROM
    sys.waits_global_by_latency
ORDER BY
    sum_sec DESC
LIMIT 10;

这个查询会返回等待时间最长的 10 个事件，包括事件名称、等待次数、总的等待时间以及平均等待时间。通过分析这些信息，我们可以找出锁竞争激烈的资源。

示例结果：

event_name	cnt	sum_sec	avg_sec
wait/synch/mutex/innodb/lock_sys_mutex	100	10.00	0.10
wait/io/file/innodb/innodb_data_file	50	5.00	0.10
wait/synch/cond/sql/MDL::wait_for_lock	20	2.00	0.10

从结果可以看出，wait/synch/mutex/innodb/lock_sys_mutex 事件的等待时间最长，说明 InnoDB 的锁系统存在竞争，可以考虑优化事务或者调整锁的粒度。

案例 6：查看连接信息

processlist 视图提供了当前数据库连接的详细信息，类似于 SHOW PROCESSLIST 命令，但提供了更多的列和更友好的格式。

SELECT * FROM sys.processlist;

这个查询会返回所有当前连接的信息，包括连接 ID、用户、主机、数据库、命令、状态、执行时间、SQL 语句等。通过分析这些信息，可以监控连接数、找出长时间运行的查询、以及识别潜在的恶意连接。

关键列：

• id: 连接 ID
• user: 连接用户
• host: 连接主机
• db: 连接数据库
• command: 执行的命令 (例如 Sleep, Query)
• state: 连接状态
• time: 执行时间 (秒)
• info: 正在执行的 SQL 语句

使用 Sys Schema 的注意事项

• Performance Schema 必须启用: Sys Schema 依赖于 Performance Schema，所以必须确保 Performance Schema 已经启用。可以通过以下命令检查 Performance Schema 是否启用：

  SELECT @@performance_schema;

  如果结果为 0，则需要启用 Performance Schema：

  UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE '%lock%';
  UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%events_%';
  UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'wait/synch/mutex/innodb%';
  UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'wait/io/file/innodb/innodb_data_file';
  SET GLOBAL performance_schema = 1;

  注意: 启用 Performance Schema 会带来一定的性能开销，所以需要根据实际情况进行权衡。建议在生产环境开启必要的instrumentation,而不是全部开启。

• Sys Schema 版本: Sys Schema 的视图定义可能会随着 MySQL 版本的升级而改变，所以需要注意 Sys Schema 的版本与 MySQL 版本的兼容性。

• 数据采样: Performance Schema 的数据是采样的，所以 Sys Schema 的统计信息可能不是 100% 精确，但仍然可以提供有价值的参考。

• 权限: 访问 Sys Schema 需要相应的权限，通常需要 SELECT 权限。

编写自定义视图

Sys Schema 提供的视图已经非常丰富，但在某些情况下，我们可能需要根据自己的需求编写自定义视图。我们可以基于 Performance Schema 和 Information Schema，结合 Sys Schema 的设计思想，编写自定义视图来满足特定的监控需求。

例如，假设我们需要监控每个数据库的表数量和总大小，可以编写如下自定义视图：

CREATE VIEW database_table_size AS
SELECT
    table_schema AS database_name,
    COUNT(*) AS table_count,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_size_mb
FROM
    information_schema.TABLES
WHERE
    table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY
    table_schema
ORDER BY
    total_size_mb DESC;

然后，我们可以查询这个自定义视图：

SELECT * FROM database_table_size;

结论：利用Sys Schema 简化MySQL性能分析

总而言之，Sys Schema 是 MySQL 性能诊断与调优的利器，它简化了性能监控流程，降低了性能分析的门槛。通过合理利用 Sys Schema 提供的视图，我们可以快速定位性能瓶颈，优化数据库配置，提升数据库性能。

总结

今天我们学习了 Sys Schema 的基本概念、核心功能、主要视图分类以及使用案例。希望大家能够在实际工作中灵活运用 Sys Schema，提升 MySQL 数据库的性能。

快速定位并解决性能问题

Sys Schema 提供了丰富的性能监控视图，帮助我们快速定位性能瓶颈，并通过分析数据，找到优化方案，从而提升数据库性能。

持续监控和优化

数据库性能是一个持续优化的过程，我们需要定期监控数据库的运行状况，及时发现潜在的问题，并采取相应的措施进行优化。Sys Schema 为我们提供了强大的监控工具，可以帮助我们实现持续监控和优化。