MySQL的Performance Schema：如何利用它构建一个基于SQL执行时间的实时性能排行榜（Performance Leaderboard）？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL Performance Schema：构建基于SQL执行时间的实时性能排行榜

大家好！今天我们来聊聊如何利用MySQL的Performance Schema构建一个基于SQL执行时间的实时性能排行榜（Performance Leaderboard）。这是一个非常有用的工具，可以帮助我们实时监控数据库的性能，快速定位慢查询，并及时采取优化措施。

1. Performance Schema 简介

Performance Schema是MySQL 5.5引入的一个用于监控MySQL服务器性能的存储引擎。它提供了各种性能指标，例如线程活动、内存分配、文件I/O、锁等等。与慢查询日志不同，Performance Schema可以提供更细粒度的性能数据，并且对服务器的性能影响更小。

Performance Schema的数据存储在内存中，并通过一系列的表暴露给用户。这些表可以像普通表一样查询，从而实现对服务器性能的实时监控。

2. 启用 Performance Schema

默认情况下，Performance Schema可能没有完全启用。我们需要检查并确保相关的选项已经开启。

首先，连接到MySQL服务器，并执行以下SQL语句：

SHOW VARIABLES LIKE 'performance_schema%';

我们需要关注以下几个变量：

performance_schema: 确保其值为 ON。
performance_schema_max_table_instances: 这个变量控制着 Performance Schema 可以创建多少个表实例。如果你的数据库非常繁忙，可能需要增加这个值。
performance_schema_events_statements_history_long_size: 这个变量控制着存储历史语句事件的数量。数值越大，存储的历史语句越多，但是也会占用更多的内存。

如果 performance_schema 的值为 OFF，可以通过修改MySQL配置文件（例如 my.cnf 或 my.ini）来启用它。在 [mysqld] 节中添加或修改以下行：

[mysqld]
performance_schema=ON
performance_schema_max_table_instances=400
performance_schema_events_statements_history_long_size=10000

修改配置文件后，需要重启MySQL服务器才能使配置生效。

3. 关键 Performance Schema 表

构建性能排行榜，我们需要关注以下几个 Performance Schema 表：

events_statements_summary_global_by_event_name: 这个表汇总了所有SQL语句的统计信息，按照事件名称（即语句类型）进行分组。
events_statements_history_long: 这个表记录了最近执行的SQL语句的详细信息，包括执行时间、锁等待时间、扫描的行数等等。这个表存储的是历史数据，可能会被滚动覆盖。
events_statements_current: 这个表记录当前正在执行的SQL语句的详细信息。
threads: 这个表包含了关于每个线程的信息，包括线程ID、连接ID、用户等等。

4. 构建实时性能排行榜的 SQL 查询

我们可以使用 SQL 查询来从 Performance Schema 表中提取数据，并构建性能排行榜。以下是一个示例 SQL 查询，用于获取执行时间最长的 SQL 语句：

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    MIN_TIMER_WAIT,
    MAX_TIMER_WAIT,
    SCHEMA_NAME
FROM
    performance_schema.events_statements_summary_global_by_digest
WHERE
    COUNT_STAR > 0
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

这个查询做了以下几件事：

DIGEST_TEXT: SQL语句的摘要，用于识别相似的SQL语句。
COUNT_STAR: SQL语句执行的次数。
SUM_TIMER_WAIT: SQL语句总的执行时间（以皮秒为单位）。
AVG_TIMER_WAIT: SQL语句平均执行时间。
MIN_TIMER_WAIT: SQL语句最短执行时间。
MAX_TIMER_WAIT: SQL语句最长执行时间。
SCHEMA_NAME: SQL语句执行的数据库。
WHERE COUNT_STAR > 0: 过滤掉执行次数为0的语句。
ORDER BY SUM_TIMER_WAIT DESC: 按照总执行时间降序排序。
LIMIT 10: 只显示前10条记录。

这个查询的结果可以作为性能排行榜的数据来源。我们可以定期执行这个查询，并将结果显示在一个Web页面或其他监控工具上，从而实现实时性能监控。

5. 改进查询：更详细的信息

上面的查询提供了摘要信息，但是可能无法直接看出具体的SQL语句。我们可以使用 events_statements_history_long 表来获取更详细的信息。

SELECT
    th.USER,
    th.HOST,
    ess.SQL_TEXT,
    ess.CURRENT_SCHEMA,
    ess.DIGEST,
    ess.LAST_ERROR_NUMBER,
    ess.LAST_ERROR_MESSAGE,
    ess.ROWS_EXAMINED,
    ess.ROWS_AFFECTED,
    ess.ROWS_SENT,
    ess.LOCK_TIME,
    ess.TIMER_WAIT
FROM
    performance_schema.events_statements_history_long ess
JOIN
    performance_schema.threads th ON ess.THREAD_ID = th.THREAD_ID
ORDER BY
    ess.TIMER_WAIT DESC
LIMIT 10;

这个查询做了以下几件事：

th.USER: 执行SQL语句的用户。
th.HOST: 执行SQL语句的主机。
ess.SQL_TEXT: 完整的SQL语句文本。
ess.CURRENT_SCHEMA: 当前使用的数据库。
ess.DIGEST: SQL语句的摘要。
ess.LAST_ERROR_NUMBER: 上次执行错误编号。
ess.LAST_ERROR_MESSAGE: 上次执行错误信息。
ess.ROWS_EXAMINED: 扫描的行数。
ess.ROWS_AFFECTED: 影响的行数。
ess.ROWS_SENT: 发送的行数。
ess.LOCK_TIME: 锁等待时间。
ess.TIMER_WAIT: 执行时间。
JOIN performance_schema.threads th ON ess.THREAD_ID = th.THREAD_ID: 连接 events_statements_history_long 表和 threads 表，获取用户信息。
ORDER BY ess.TIMER_WAIT DESC: 按照执行时间降序排序。
LIMIT 10: 只显示前10条记录。

6. 解决 SQL 语句截断问题

events_statements_history_long.SQL_TEXT 列的长度是有限制的，如果SQL语句超过这个长度，会被截断。为了获取完整的SQL语句，可以使用以下方法：

修改 performance_schema_max_sql_text_length 变量: 这个变量控制着 SQL_TEXT 列的最大长度。可以通过修改MySQL配置文件来增加这个值。但是，增加这个值会占用更多的内存。
```
[mysqld]
performance_schema_max_sql_text_length=65535
```
使用 DIGEST 查询 events_statements_summary_global_by_digest 表: events_statements_summary_global_by_digest 表中的 DIGEST_TEXT 列存储了SQL语句的摘要，通常不会被截断。但是，这个列存储的是摘要，而不是完整的SQL语句。
结合慢查询日志: 可以将Performance Schema和慢查询日志结合起来使用。 Performance Schema用于实时监控性能，慢查询日志用于记录完整的SQL语句。当Performance Schema检测到慢查询时，可以在慢查询日志中查找对应的SQL语句。

7. 使用存储过程和事件调度器实现自动化

为了方便地生成性能排行榜，可以创建一个存储过程，用于执行查询并保存结果。然后，可以使用MySQL的事件调度器定期调用这个存储过程。

以下是一个示例存储过程：

DROP PROCEDURE IF EXISTS generate_performance_leaderboard;

CREATE PROCEDURE generate_performance_leaderboard()
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS performance_leaderboard (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user VARCHAR(64),
        host VARCHAR(255),
        sql_text TEXT,
        current_schema VARCHAR(64),
        digest VARCHAR(32),
        last_error_number INT,
        last_error_message VARCHAR(255),
        rows_examined BIGINT UNSIGNED,
        rows_affected BIGINT UNSIGNED,
        rows_sent BIGINT UNSIGNED,
        lock_time BIGINT UNSIGNED,
        timer_wait BIGINT UNSIGNED,
        ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    TRUNCATE TABLE performance_leaderboard;

    INSERT INTO performance_leaderboard (user, host, sql_text, current_schema, digest, last_error_number, last_error_message, rows_examined, rows_affected, rows_sent, lock_time, timer_wait)
    SELECT
        th.USER,
        th.HOST,
        ess.SQL_TEXT,
        ess.CURRENT_SCHEMA,
        ess.DIGEST,
        ess.LAST_ERROR_NUMBER,
        ess.LAST_ERROR_MESSAGE,
        ess.ROWS_EXAMINED,
        ess.ROWS_AFFECTED,
        ess.ROWS_SENT,
        ess.LOCK_TIME,
        ess.TIMER_WAIT
    FROM
        performance_schema.events_statements_history_long ess
    JOIN
        performance_schema.threads th ON ess.THREAD_ID = th.THREAD_ID
    ORDER BY
        ess.TIMER_WAIT DESC
    LIMIT 10;

END;

这个存储过程做了以下几件事：

创建了一个临时表 performance_leaderboard，用于存储性能排行榜的数据。
清空了 performance_leaderboard 表。
将查询结果插入到 performance_leaderboard 表中。

然后，可以使用以下SQL语句创建一个事件，定期调用这个存储过程：

DROP EVENT IF EXISTS update_performance_leaderboard;

CREATE EVENT update_performance_leaderboard
ON SCHEDULE EVERY 1 MINUTE
DO
    CALL generate_performance_leaderboard();

这个事件每分钟调用一次 generate_performance_leaderboard 存储过程，从而实现实时更新性能排行榜。

8. 将数据展示在 Web 界面

有了性能排行榜的数据，就可以将其展示在一个Web页面上。可以使用各种Web开发技术来实现这个功能，例如PHP、Python、Node.js等等。

以下是一个使用PHP的简单示例：

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "SELECT * FROM performance_leaderboard ORDER BY timer_wait DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table>";
    echo "<tr><th>User</th><th>Host</th><th>SQL Text</th><th>Timer Wait</th></tr>";
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["user"]."</td><td>".$row["host"]."</td><td>".$row["sql_text"]."</td><td>".$row["timer_wait"]."</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 结果";
}
$conn->close();
?>

这个PHP脚本连接到MySQL数据库，查询 performance_leaderboard 表，并将结果显示在一个HTML表格中。

9. 注意事项

Performance Schema 的开销: 虽然Performance Schema对服务器的性能影响很小，但是仍然会占用一定的资源。如果你的服务器资源非常紧张，需要谨慎使用Performance Schema。
数据保留时间: Performance Schema的数据存储在内存中，会被定期滚动覆盖。如果需要长期保存性能数据，可以将数据导出到其他存储介质，例如文件或数据库。
权限控制: Performance Schema的表包含了敏感的性能数据，需要进行严格的权限控制，防止未经授权的访问。

10. 其他优化思路

使用 Grafana 和 Prometheus: 可以使用 Grafana 和 Prometheus 等监控工具来可视化 Performance Schema 的数据。这些工具提供了强大的数据分析和可视化功能，可以帮助我们更好地理解数据库的性能。
结合 EXPLAIN 分析慢查询: 当Performance Schema检测到慢查询时，可以使用 EXPLAIN 语句来分析查询的执行计划，找出瓶颈所在。
使用 pt-query-digest 分析慢查询日志: 可以使用 pt-query-digest 工具来分析慢查询日志，找出最常见的慢查询。

11. 总结：监控是第一步，优化是最终目标

通过以上步骤，我们就可以构建一个基于MySQL Performance Schema的实时性能排行榜。监控，分析，改进，再监控，这才是循环上升的性能优化过程。

MySQL Performance Schema：构建基于SQL执行时间的实时性能排行榜

发表回复 取消回复

发表回复取消回复