分析 `sys.host_summary` 和 `sys.statements_with_full_table_scans` 视图的`深层`含义。

好的，下面是关于sys.host_summary和sys.statements_with_full_table_scans视图的深度分析，以技术讲座的形式呈现。

诊断MySQL服务器性能问题的利器：深入解析sys.host_summary和sys.statements_with_full_table_scans

大家好，今天我们深入探讨两个非常有用的MySQL性能诊断视图：sys.host_summary 和 sys.statements_with_full_table_scans。这两个视图是 sys 模式的一部分，它们提供了关于服务器性能和SQL语句执行情况的关键信息，帮助我们快速定位性能瓶颈。

1. sys.host_summary：从主机层面俯瞰MySQL性能

sys.host_summary 视图提供了按主机聚合的统计信息，可以帮助我们识别哪些主机对MySQL服务器的性能影响最大。通过这个视图，我们可以看到每个主机连接到MySQL服务器后所产生的I/O负载、CPU使用情况、内存使用情况等等。

1.1 视图结构和核心字段

sys.host_summary 视图包含以下关键字段：

字段名	数据类型	描述
host	varchar(255)	主机名，表示连接到MySQL服务器的客户端主机。
statements	bigint	从该主机执行的语句总数。
statement_latency	varchar(128)	从该主机执行的语句总延迟时间，格式为时间单位 (例如，10.53 s)。
table_scans	bigint	从该主机执行的表扫描总数。
file_ios	bigint	从该主机执行的文件 I/O 操作总数。
file_io_latency	varchar(128)	从该主机执行的文件 I/O 操作总延迟时间，格式为时间单位 (例如，1.23 s)。
current_connections	bigint	当前来自该主机的连接数。
total_connections	bigint	来自该主机的连接总数。
max_statement_latency	varchar(128)	从该主机执行的单个语句的最大延迟时间。
innodb_io	bigint	从该主机执行的 InnoDB I/O 操作总数。
innodb_io_latency	varchar(128)	从该主机执行的 InnoDB I/O 操作总延迟时间，格式为时间单位 (例如，5.67 s)。
memory_allocations	bigint	从该主机执行的内存分配总数。
memory_latency	varchar(128)	从该主机执行的内存分配总延迟时间，格式为时间单位 (例如，0.89 s)。

1.2 示例查询与分析

假设我们想找出哪个主机对服务器的性能影响最大（例如，执行了最多的语句）：

SELECT
    host,
    statements,
    statement_latency,
    table_scans,
    file_ios,
    file_io_latency,
    current_connections
FROM
    sys.host_summary
ORDER BY
    statements DESC
LIMIT 10;

这个查询会返回执行语句最多的前10个主机。通过查看 statement_latency 和 table_scans 字段，我们可以进一步判断这些主机是否导致了性能瓶颈。

如果某个主机的 statement_latency 很高，但 statements 数量并不多，这可能意味着该主机执行的语句比较复杂，需要进一步分析该主机执行的具体SQL语句。

如果某个主机的 table_scans 很高，说明该主机执行了很多全表扫描操作，这通常是性能问题的根源。

1.3 深入挖掘：结合其他视图

sys.host_summary 视图可以与其他 sys 模式下的视图结合使用，以获得更全面的性能信息。 例如，我们可以结合 sys.processlist 视图，查看来自特定主机的当前连接信息：

SELECT
    p.id,
    p.user,
    p.host,
    p.db,
    p.command,
    p.time,
    p.state,
    p.info
FROM
    sys.processlist p
WHERE
    p.host = 'your_problematic_host'; -- 将 'your_problematic_host' 替换为实际主机名

这个查询会显示来自指定主机的当前所有连接的详细信息，包括用户、数据库、执行的命令、状态和执行时间。这有助于我们了解该主机正在执行哪些操作，以及是否存在长时间运行的查询。

2. sys.statements_with_full_table_scans：追踪全表扫描的罪魁祸首

sys.statements_with_full_table_scans 视图列出了执行了全表扫描的SQL语句，并提供了相关的统计信息。全表扫描通常是性能杀手，因为它需要读取整个表的数据，即使只需要其中的几行。

2.1 视图结构和核心字段

sys.statements_with_full_table_scans 视图包含以下关键字段：

字段名	数据类型	描述
query	mediumtext	执行的SQL查询语句。
db	varchar(64)	执行查询的数据库。
full_scan	enum(‘YES’, ‘NO’)	指示查询是否包含全表扫描。 对于此视图中的所有行，此值始终为 ‘YES’。
exec_count	bigint	查询执行的次数。
errors	bigint	查询执行期间发生的错误次数。
warnings	bigint	查询执行期间发生的警告次数。
total_latency	varchar(128)	查询执行的总延迟时间，格式为时间单位 (例如，10.53 s)。
max_latency	varchar(128)	查询执行的最大延迟时间。
avg_latency	varchar(128)	查询执行的平均延迟时间。
lock_latency	varchar(128)	查询等待锁的总延迟时间。
rows_sent	bigint	查询返回的总行数。
rows_examined	bigint	查询检查的总行数。
first_seen	timestamp	查询首次执行的时间。
last_seen	timestamp	查询最近一次执行的时间。
digest	varchar(32)	查询的摘要，用于标识具有相同结构的查询。

2.2 示例查询与分析

假设我们想找出执行次数最多且全表扫描最频繁的SQL语句：

SELECT
    query,
    db,
    exec_count,
    total_latency,
    rows_examined,
    rows_sent
FROM
    sys.statements_with_full_table_scans
ORDER BY
    exec_count DESC, total_latency DESC
LIMIT 10;

这个查询会返回执行次数最多且总延迟时间最长的前10个全表扫描语句。 通过查看 rows_examined 和 rows_sent 字段，我们可以判断全表扫描的效率是否很低。 如果 rows_examined 远大于 rows_sent，说明查询扫描了大量的行，但只返回了很少的行，这表明全表扫描的效率很低，需要优化。

2.3 优化全表扫描

一旦我们找到了执行全表扫描的SQL语句，就可以采取以下措施进行优化：

• 添加索引： 在经常用于 WHERE 子句中的列上添加索引，可以避免全表扫描，提高查询效率。
• 重写查询： 重新设计查询，使其能够利用索引，避免全表扫描。 例如，可以使用更精确的 WHERE 子句，或者使用 JOIN 操作来连接多个表，而不是依赖于全表扫描。
• 优化表结构： 考虑对表进行分区或分片，以减少每个分区或分片中的数据量，从而减少全表扫描的范围。
• 使用覆盖索引： 创建包含查询所需的所有列的索引（覆盖索引），可以避免回表操作，提高查询效率。

2.4 示例：添加索引优化

假设我们发现以下查询执行了全表扫描：

SELECT * FROM orders WHERE customer_id = 123;

如果 customer_id 列上没有索引，MySQL 将执行全表扫描来查找 customer_id 为 123 的订单。 为了优化这个查询，我们可以添加一个索引：

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

添加索引后，MySQL 可以使用索引来快速定位 customer_id 为 123 的订单，而无需执行全表扫描。

2.5 深入挖掘：结合 EXPLAIN 分析

在优化SQL语句时，我们可以使用 EXPLAIN 语句来分析查询的执行计划。 EXPLAIN 语句会告诉我们 MySQL 如何执行查询，包括是否使用了索引、是否执行了全表扫描等等。

例如：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

EXPLAIN 语句的输出会显示查询的执行计划。 如果 type 列的值为 ALL，则表示执行了全表扫描。 如果 type 列的值为 index 或 range，则表示使用了索引。

3. 案例分析：结合两个视图诊断性能问题

假设我们通过 sys.host_summary 视图发现主机 192.168.1.100 的 table_scans 很高，并且 statement_latency 也很高。这表明该主机执行了很多全表扫描操作，导致了性能瓶颈。

接下来，我们可以使用 sys.statements_with_full_table_scans 视图来找出该主机执行的具体哪些SQL语句导致了全表扫描：

SELECT
    st.query,
    st.db,
    st.exec_count,
    st.total_latency,
    st.rows_examined,
    st.rows_sent
FROM
    sys.statements_with_full_table_scans st
WHERE
    st.query IN (
        SELECT
            ps.info
        FROM
            performance_schema.threads t
        JOIN
            performance_schema.processlist ps ON t.processlist_id = ps.thd_id
        WHERE
            ps.host = '192.168.1.100'
    )
ORDER BY
    st.total_latency DESC
LIMIT 10;

这个查询首先从 performance_schema.threads 和 performance_schema.processlist 视图中获取来自 192.168.1.100 主机的当前正在执行的SQL语句。然后，它从 sys.statements_with_full_table_scans 视图中查找这些语句的全表扫描信息。

通过分析查询结果，我们可以找出导致全表扫描的具体SQL语句，并采取相应的优化措施，例如添加索引、重写查询等等。

4. 注意事项

• sys 模式下的视图依赖于 performance_schema，因此需要确保 performance_schema 已经启用。
• sys 模式下的视图会消耗一定的系统资源，因此不建议在生产环境中频繁查询这些视图。
• sys.statements_with_full_table_scans 视图只记录执行了全表扫描的SQL语句，如果SQL语句使用了索引，但效率仍然很低，则不会被记录在这个视图中。
• sys模式下的视图是动态的，数据会不断更新。

5. 总结：高效诊断和优化MySQL性能

sys.host_summary 和 sys.statements_with_full_table_scans 是非常有用的MySQL性能诊断工具。通过 sys.host_summary，我们可以从主机层面了解服务器的性能瓶颈。通过 sys.statements_with_full_table_scans，我们可以追踪执行全表扫描的SQL语句，并采取相应的优化措施。 结合这两个视图，我们可以快速定位性能问题，并采取有效的解决方案。

6. 案例补充：长时间运行的查询和阻塞

除了全表扫描，长时间运行的查询和阻塞也是常见的性能问题。 sys 模式还提供了其他视图来帮助我们诊断这些问题。

例如，sys.processlist 视图可以显示当前所有连接的详细信息，包括用户、数据库、执行的命令、状态和执行时间。我们可以使用这个视图来找出长时间运行的查询：

SELECT
    p.id,
    p.user,
    p.host,
    p.db,
    p.command,
    p.time,
    p.state,
    p.info
FROM
    sys.processlist p
WHERE
    p.command != 'Sleep'
ORDER BY
    p.time DESC
LIMIT 10;

这个查询会显示当前所有非空闲连接的信息，并按照执行时间降序排列。 通过查看 time 字段，我们可以找出长时间运行的查询。

此外，performance_schema 模式下的 events_statements_current、events_statements_history 和 events_statements_history_long 视图可以提供更详细的SQL语句执行信息，包括执行时间、锁等待时间、I/O 操作等等。 我们可以使用这些视图来分析SQL语句的性能瓶颈。

7. 总结：结合多种视图进行综合分析

sys.host_summary和sys.statements_with_full_table_scans是定位问题的第一步，但解决问题往往需要结合其他视图和工具进行综合分析。

8. 总结：持续监控和定期优化

性能优化是一个持续的过程，需要定期监控服务器的性能指标，并根据实际情况进行调整。

9. 总结：深入理解MySQL的内部机制

只有深入理解MySQL的内部机制，才能更好地利用sys模式下的视图进行性能诊断和优化。