MySQL的`sys`模式：如何利用其提供的视图（View）快速诊断性能瓶颈，如`sys.session`？

MySQL sys 模式：性能诊断的利器，从 sys.session 入手

各位听众，大家好。今天我们要深入探讨 MySQL 的 sys 模式，特别是如何利用它提供的视图，比如 sys.session，来快速诊断性能瓶颈。sys 模式是 MySQL 5.7 及更高版本自带的一个强大的数据库模式，它提供了一系列视图，这些视图基于 Performance Schema 和 Information Schema，经过精心设计，以易于理解和使用的格式呈现 MySQL 的内部运行状态。它简化了性能监控和故障排除的过程，使 DBA 和开发人员能够更有效地识别和解决性能问题。

sys 模式的价值：拨开迷雾见真章

在没有 sys 模式之前，要获取 MySQL 的性能数据，通常需要直接查询 Performance Schema 或 Information Schema。这需要对这些模式的底层结构有深入的了解，并且编写复杂的 SQL 查询。sys 模式通过提供预定义的视图，屏蔽了底层的复杂性，将原始数据转换成更易于理解的指标，极大地简化了性能分析的过程。

sys 模式的价值体现在以下几个方面：

• 易于使用: 预定义的视图，无需编写复杂的 SQL 查询。
• 可读性强: 数据以更友好的格式呈现，例如将字节转换为更易读的单位 (KB, MB, GB)。
• 实时监控: 基于 Performance Schema，可以实时监控 MySQL 的性能指标。
• 问题诊断: 通过分析视图中的数据，可以快速识别性能瓶颈，例如慢查询、锁争用、IO 瓶颈等。

sys.session：一览会话全局

sys.session 视图提供了关于当前 MySQL 会话的详细信息，包括会话 ID、用户、主机、连接状态、当前执行的语句、等待事件、资源消耗等等。利用 sys.session，我们可以实时监控会话状态，识别长时间运行的查询，以及导致阻塞的会话，从而快速定位性能问题。

sys.session 视图的结构：

sys.session 视图包含了大量列，以下列出一些关键列及其含义：

列名	数据类型	描述
session_id	bigint	会话 ID，对应于 Performance Schema 中的 threads.thread_id。
user	varchar	连接到 MySQL 服务器的用户。
db	varchar	当前会话使用的数据库。
command	varchar	会话当前执行的命令，例如 Sleep, Query, Connect。
state	varchar	会话的当前状态，例如 Sending data, Waiting on table metadata lock。
time	bigint	会话处于当前状态的时间，单位为秒。
current_statement	longtext	会话当前正在执行的 SQL 语句。
statement_latency	varchar	会话当前正在执行的 SQL 语句的延迟，以人类可读的格式显示。
lock_latency	varchar	会话等待锁的时间，以人类可读的格式显示。
rows_examined	bigint	会话执行的 SQL 语句扫描的行数。
rows_sent	bigint	会话执行的 SQL 语句返回的行数。
memory	varchar	会话当前使用的内存量，以人类可读的格式显示。
cpu	varchar	会话使用的 CPU 时间，以人类可读的格式显示。
last_wait	varchar	会话最后一次等待的事件。
last_wait_latency	varchar	会话最后一次等待事件的延迟，以人类可读的格式显示。
tmp_disk_tables	bigint	会话创建的磁盘临时表的数量。
tmp_memory_tables	bigint	会话创建的内存临时表的数量。
tables_locked	varchar	会话锁定的表。
created_tmp_disk_tables	bigint	会话创建的磁盘临时表的总数。
created_tmp_tables	bigint	会话创建的内存临时表的总数。

利用 sys.session 诊断性能瓶颈：实战演练

下面我们通过一些实际的例子，演示如何利用 sys.session 诊断性能瓶颈：

1. 查找长时间运行的查询：

长时间运行的查询是导致性能下降的常见原因。我们可以使用以下 SQL 语句查找运行时间超过 5 秒的查询：

SELECT
    session_id,
    user,
    db,
    command,
    state,
    time,
    current_statement,
    statement_latency
FROM
    sys.session
WHERE
    command = 'Query'
    AND time > 5
ORDER BY
    time DESC;

这条 SQL 语句会列出所有 command 为 Query 且 time 大于 5 秒的会话，并按照 time 降序排列。通过查看 current_statement 列，我们可以知道哪些 SQL 语句运行时间过长。statement_latency 列可以更精确地显示语句执行的延迟。

2. 查找等待锁的会话：

锁争用是导致性能问题的另一个常见原因。我们可以使用以下 SQL 语句查找正在等待锁的会话：

SELECT
    session_id,
    user,
    db,
    command,
    state,
    time,
    current_statement,
    lock_latency,
    last_wait,
    last_wait_latency
FROM
    sys.session
WHERE
    state LIKE '%lock%'
ORDER BY
    lock_latency DESC;

这条 SQL 语句会列出所有 state 包含 lock 的会话，并按照 lock_latency 降序排列。last_wait 列显示了会话最后一次等待的事件，last_wait_latency 列显示了等待事件的延迟。通过这些信息，我们可以确定哪些会话正在等待锁，以及等待锁的原因。

3. 查找消耗大量资源的会话：

消耗大量 CPU 或内存的会话也可能导致性能问题。我们可以使用以下 SQL 语句查找消耗大量 CPU 或内存的会话：

SELECT
    session_id,
    user,
    db,
    command,
    state,
    time,
    current_statement,
    cpu,
    memory
FROM
    sys.session
ORDER BY
    cpu DESC,
    memory DESC
LIMIT 10;

这条 SQL 语句会列出消耗 CPU 和内存最多的前 10 个会话，并按照 CPU 和内存降序排列。通过查看 cpu 和 memory 列，我们可以知道哪些会话消耗了大量的资源。

4. 查找创建大量临时表的会话：

创建大量临时表也可能导致性能问题，特别是磁盘临时表。我们可以使用以下 SQL 语句查找创建大量临时表的会话：

SELECT
    session_id,
    user,
    db,
    command,
    state,
    time,
    current_statement,
    tmp_disk_tables,
    tmp_memory_tables,
    created_tmp_disk_tables,
    created_tmp_tables
FROM
    sys.session
WHERE
    tmp_disk_tables > 0 OR tmp_memory_tables > 0
ORDER BY
    tmp_disk_tables DESC,
    tmp_memory_tables DESC;

这条 SQL 语句会列出创建了磁盘临时表或内存临时表的会话，并按照磁盘临时表和内存临时表降序排列。通过查看 tmp_disk_tables 和 tmp_memory_tables 列，我们可以知道哪些会话创建了大量的临时表。 created_tmp_disk_tables 和 created_tmp_tables 列显示了会话生命周期内创建的临时表总数。

5. 结合其他 sys 视图进行分析：

sys.session 可以与其他 sys 视图结合使用，以获得更全面的性能分析。例如，我们可以将 sys.session 与 sys.processlist 视图结合使用，以获取更详细的进程信息。

示例：查找长时间运行的查询并查看其进程信息：

SELECT
    s.session_id,
    s.user,
    s.db,
    s.command,
    s.state,
    s.time,
    s.current_statement,
    s.statement_latency,
    p.cpu_time,
    p.memory_used
FROM
    sys.session s
JOIN
    sys.processlist p ON s.session_id = p.thd_id
WHERE
    s.command = 'Query'
    AND s.time > 5
ORDER BY
    s.time DESC;

在这个例子中，我们将 sys.session 视图与 sys.processlist 视图连接起来，根据 session_id 和 thd_id 关联。然后，我们筛选出运行时间超过 5 秒的查询，并显示其进程信息，例如 cpu_time 和 memory_used。

更进一步：优化建议

通过 sys.session 视图识别出性能瓶颈后，我们需要采取相应的优化措施。以下是一些常见的优化建议：

• 优化 SQL 语句: 使用 EXPLAIN 分析 SQL 语句的执行计划，优化索引，避免全表扫描，减少数据返回量。
• 调整 MySQL 配置: 根据服务器的硬件资源和应用负载，调整 MySQL 的配置参数，例如 innodb_buffer_pool_size, innodb_log_file_size, max_connections。
• 增加硬件资源: 如果服务器的硬件资源不足，可以考虑升级 CPU、内存、磁盘等硬件。
• 使用连接池: 使用连接池可以减少连接建立和断开的开销，提高性能。
• 避免长事务: 长事务会占用大量的资源，并可能导致锁争用。尽量将事务拆分成更小的单元。
• 监控和告警: 建立完善的监控和告警系统，及时发现和解决性能问题。

代码示例：杀死长时间运行的查询

在确认某个查询确实是性能瓶颈，并且没有其他优化方案时，可以考虑将其杀死。

SELECT
    session_id,
    user,
    db,
    command,
    state,
    time,
    current_statement
FROM
    sys.session
WHERE
    command = 'Query'
    AND time > 30; -- 查找运行超过30秒的查询

找到对应的 session_id 后，执行以下命令：

KILL [CONNECTION | QUERY] session_id;

例如：

KILL QUERY 12345; -- 杀死查询 ID 为 12345 的查询

请谨慎使用 KILL 命令，确保不会影响正常业务。

总结：用 sys 模式武装自己，洞察 MySQL 性能

sys 模式是 MySQL 性能诊断的利器。通过 sys.session 视图，我们可以实时监控会话状态，快速识别性能瓶颈。结合其他 sys 视图和优化建议，可以有效地解决性能问题，提升 MySQL 的整体性能。掌握 sys 模式的使用方法，能够让我们更好地了解 MySQL 的内部运行机制，从而更好地管理和优化 MySQL 数据库。