MySQL的Performance Schema：如何利用它构建一个实时的SQL性能排行榜？

MySQL Performance Schema：构建实时SQL性能排行榜

大家好！今天我们来探讨如何利用MySQL的Performance Schema构建一个实时的SQL性能排行榜。Performance Schema是MySQL提供的一个强大的性能监控工具，它可以收集服务器运行时的各种性能数据，包括SQL语句的执行时间、锁等待、内存使用等等。利用这些数据，我们可以分析系统的瓶颈，优化SQL语句，提高数据库的整体性能。

1. Performance Schema 简介

Performance Schema 默认是关闭的，需要手动开启。它的核心思想是通过instrument和consumer来收集和消费性能数据。

• Instruments: 是性能监控的度量点，比如SQL语句、锁、文件I/O等等。每个instrument对应一个或多个event。
• Events: 是instrument的执行实例，包含了开始时间、结束时间、持续时间等信息。
• Consumers: 是数据的消费者，负责将events写入到不同的表中。

Performance Schema的数据存储在内存中，不会对数据库的性能产生太大的影响。但是，如果开启了过多的instrument，也会消耗一定的资源，所以需要根据实际情况进行配置。

2. 开启Performance Schema

首先，我们需要检查Performance Schema是否已经开启。可以通过执行以下SQL语句来查看：

SELECT @@performance_schema;

如果结果是OFF，则需要修改MySQL的配置文件my.cnf（或my.ini）并重启MySQL服务。

[mysqld]
performance_schema=ON

重启MySQL服务后，再次执行上面的SQL语句，确认Performance Schema已经开启。

3. 配置 Performance Schema

Performance Schema的配置主要包括两个方面：

• 控制哪些instruments被启用: 这决定了收集哪些类型的性能数据。
• 控制哪些consumers被启用: 这决定了数据如何被存储。

我们可以通过修改setup_instruments和setup_consumers表来配置Performance Schema。

3.1 setup_instruments 表

setup_instruments表用于控制哪些instruments被启用。例如，要启用所有SQL语句的instrument，可以执行以下SQL语句：

UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'statement/%';

这条语句将所有以statement/开头的instrument的enabled和timed字段设置为YES。enabled表示启用该instrument，timed表示记录该instrument的执行时间。

3.2 setup_consumers 表

setup_consumers表用于控制哪些consumers被启用。例如，要启用将SQL语句的执行时间写入events_statements_summary_global_by_digest表的consumer，可以执行以下SQL语句：

UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_summary_global_by_digest';

这条语句将名为events_statements_summary_global_by_digest的consumer的enabled字段设置为YES。

3.3 常用配置示例

以下是一些常用的Performance Schema配置示例：

Instrument 类型	描述	启用命令
statement/%	所有SQL语句	UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'statement/%';
stage/%	SQL语句的执行阶段，例如parsing, optimizing, executing等。	UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'stage/%';
wait/synch/%	同步等待事件，例如锁等待、IO等待等。	UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'wait/synch/%';
memory/%	内存分配事件。	UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'memory/%';
events_statements_current	当前正在执行的SQL语句。	UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_current';
events_statements_history	最近执行的SQL语句的历史记录。	UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history';
events_statements_summary_global_by_digest	按照SQL语句的摘要（digest）进行汇总的全局统计信息。	UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_summary_global_by_digest';

4. 构建 SQL 性能排行榜

现在我们已经开启并配置了Performance Schema，接下来就可以利用它构建一个实时的SQL性能排行榜。

4.1 数据来源

构建SQL性能排行榜的主要数据来源是events_statements_summary_global_by_digest表。这个表按照SQL语句的摘要（digest）进行汇总，包含了SQL语句的执行次数、总执行时间、平均执行时间、最大执行时间等信息。

4.2 SQL 查询

以下是一个查询events_statements_summary_global_by_digest表，按照总执行时间排序，获取前10条最耗时SQL语句的SQL语句：

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    MAX_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_global_by_digest
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

这个查询返回的结果包含了以下字段：

• DIGEST_TEXT: SQL语句的摘要。
• COUNT_STAR: SQL语句的执行次数。
• SUM_TIMER_WAIT: SQL语句的总执行时间（单位是皮秒）。
• AVG_TIMER_WAIT: SQL语句的平均执行时间（单位是皮秒）。
• MAX_TIMER_WAIT: SQL语句的最大执行时间（单位是皮秒）。

4.3 将皮秒转换为更友好的时间单位

上面的查询结果中，时间单位是皮秒，不太容易理解。我们可以将其转换为更友好的时间单位，例如微秒、毫秒或秒。

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000000 AS sum_ms, -- 总执行时间（毫秒）
    AVG_TIMER_WAIT / 1000000000000 AS avg_ms, -- 平均执行时间（毫秒）
    MAX_TIMER_WAIT / 1000000000000 AS max_ms  -- 最大执行时间（毫秒）
FROM
    performance_schema.events_statements_summary_global_by_digest
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

4.4 获取完整的SQL语句

上面的查询只能获取SQL语句的摘要，无法获取完整的SQL语句。要获取完整的SQL语句，需要查询events_statements_history表或者events_statements_current表。

• events_statements_history: 存储最近执行的SQL语句的历史记录。
• events_statements_current: 存储当前正在执行的SQL语句。

以下是一个查询events_statements_history表，获取与指定摘要匹配的完整SQL语句的示例：

SELECT
    EVENT_ID,
    SQL_TEXT
FROM
    performance_schema.events_statements_history
WHERE
    DIGEST = '你的SQL摘要'
ORDER BY
    EVENT_ID DESC
LIMIT 1;

注意：

• 需要替换 '你的SQL摘要' 为实际的摘要值。
• 由于events_statements_history存储的是历史记录，如果SQL语句已经执行完毕，才能在这个表中找到。
• events_statements_current表中存储的是当前正在执行的SQL语句，可以实时获取SQL语句，但是只能获取当前正在执行的SQL语句。

4.5 组合查询

为了获取完整的SQL语句和性能数据，可以将events_statements_summary_global_by_digest表和events_statements_history表进行联合查询。

SELECT
    ess.DIGEST_TEXT,
    ess.COUNT_STAR,
    ess.SUM_TIMER_WAIT / 1000000000000 AS sum_ms,
    ess.AVG_TIMER_WAIT / 1000000000000 AS avg_ms,
    ess.MAX_TIMER_WAIT / 1000000000000 AS max_ms,
    esh.SQL_TEXT
FROM
    performance_schema.events_statements_summary_global_by_digest ess
LEFT JOIN
    performance_schema.events_statements_history esh ON ess.DIGEST = esh.DIGEST
ORDER BY
    ess.SUM_TIMER_WAIT DESC
LIMIT 10;

这个查询会将events_statements_summary_global_by_digest表和events_statements_history表按照DIGEST字段进行关联，获取SQL语句的摘要、执行次数、总执行时间、平均执行时间、最大执行时间和完整的SQL语句。

需要注意的是，这种关联查询的性能可能会比较差，特别是当events_statements_history表的数据量比较大的时候。可以考虑创建索引来优化查询性能。

4.6 定时刷新

为了实现实时的SQL性能排行榜，需要定时刷新数据。可以使用MySQL的事件调度器（Event Scheduler）来定时执行上面的查询。

首先，需要确认事件调度器是否已经开启。可以通过执行以下SQL语句来查看：

SELECT @@event_scheduler;

如果结果是OFF，则需要修改MySQL的配置文件my.cnf（或my.ini）并重启MySQL服务。

[mysqld]
event_scheduler=ON

重启MySQL服务后，再次执行上面的SQL语句，确认事件调度器已经开启。

然后，可以创建一个事件，定时执行查询SQL性能数据的SQL语句，并将结果存储到一个临时表中。

CREATE EVENT update_sql_performance_data
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    --  将历史记录表的内容拷贝到临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_sql_performance_data AS
    SELECT
        ess.DIGEST_TEXT,
        ess.COUNT_STAR,
        ess.SUM_TIMER_WAIT / 1000000000000 AS sum_ms,
        ess.AVG_TIMER_WAIT / 1000000000000 AS avg_ms,
        ess.MAX_TIMER_WAIT / 1000000000000 AS max_ms,
        esh.SQL_TEXT
    FROM
        performance_schema.events_statements_summary_global_by_digest ess
    LEFT JOIN
        performance_schema.events_statements_history esh ON ess.DIGEST = esh.DIGEST
    ORDER BY
        ess.SUM_TIMER_WAIT DESC
    LIMIT 10;

    -- 如果临时表已存在，先删除再创建
    DROP TABLE IF EXISTS temp_sql_performance_data;
    CREATE TEMPORARY TABLE temp_sql_performance_data AS
    SELECT
        ess.DIGEST_TEXT,
        ess.COUNT_STAR,
        ess.SUM_TIMER_WAIT / 1000000000000 AS sum_ms,
        ess.AVG_TIMER_WAIT / 1000000000000 AS avg_ms,
        ess.MAX_TIMER_WAIT / 1000000000000 AS max_ms,
        esh.SQL_TEXT
    FROM
        performance_schema.events_statements_summary_global_by_digest ess
    LEFT JOIN
        performance_schema.events_statements_history esh ON ess.DIGEST = esh.DIGEST
    ORDER BY
        ess.SUM_TIMER_WAIT DESC
    LIMIT 10;
END;

这个事件会每分钟执行一次，并将查询结果存储到名为temp_sql_performance_data的临时表中。

最后，可以创建一个视图，从临时表中读取数据，用于展示SQL性能排行榜。

CREATE VIEW sql_performance_ranking AS
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    sum_ms,
    avg_ms,
    max_ms,
    SQL_TEXT
FROM
    temp_sql_performance_data
ORDER BY
    sum_ms DESC;

现在，就可以通过查询sql_performance_ranking视图来获取实时的SQL性能排行榜。

SELECT * FROM sql_performance_ranking;

4.7 数据可视化

有了数据，就可以将其可视化，例如使用Grafana等工具，创建一个dashboard，展示SQL性能排行榜。可以展示以下信息：

• SQL语句的摘要
• SQL语句的执行次数
• SQL语句的总执行时间
• SQL语句的平均执行时间
• SQL语句的最大执行时间
• 完整的SQL语句

通过数据可视化，可以更直观地了解系统的性能瓶颈，并及时进行优化。

5. 注意事项

• Performance Schema 会消耗一定的资源，需要根据实际情况进行配置。 如果开启了过多的instrument，可能会对数据库的性能产生一定的影响。
• 定期清理events_statements_history表，避免数据量过大。 可以使用TRUNCATE TABLE语句来清理表。
• events_statements_summary_global_by_digest表中的数据是累积的，需要定期重置。 可以使用TRUNCATE TABLE语句来重置表。
• 联合查询events_statements_summary_global_by_digest表和events_statements_history表的性能可能会比较差，可以考虑创建索引来优化查询性能。
• SQL语句的摘要可能会发生变化，需要定期更新。

6. 代码示例

以下是一个完整的代码示例，用于构建一个实时的SQL性能排行榜：

-- 1. 开启 Performance Schema
-- 修改 my.cnf 文件，添加以下配置：
-- [mysqld]
-- performance_schema=ON
-- 重启 MySQL 服务

-- 2. 配置 Performance Schema
-- 启用所有 SQL 语句的 instrument
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES' WHERE name LIKE 'statement/%';

-- 启用将 SQL 语句的执行时间写入 events_statements_summary_global_by_digest 表的 consumer
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_summary_global_by_digest';

-- 启用 events_statements_history consumer
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history';

-- 3. 创建事件，定时更新 SQL 性能数据
DROP EVENT IF EXISTS update_sql_performance_data;
CREATE EVENT update_sql_performance_data
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    -- 如果临时表已存在，先删除再创建
    DROP TEMPORARY TABLE IF EXISTS temp_sql_performance_data;

    --  将历史记录表的内容拷贝到临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_sql_performance_data AS
    SELECT
        ess.DIGEST_TEXT,
        ess.COUNT_STAR,
        ess.SUM_TIMER_WAIT / 1000000000000 AS sum_ms,
        ess.AVG_TIMER_WAIT / 1000000000000 AS avg_ms,
        ess.MAX_TIMER_WAIT / 1000000000000 AS max_ms,
        esh.SQL_TEXT
    FROM
        performance_schema.events_statements_summary_global_by_digest ess
    LEFT JOIN
        performance_schema.events_statements_history esh ON ess.DIGEST = esh.DIGEST
    ORDER BY
        ess.SUM_TIMER_WAIT DESC
    LIMIT 10;

END;

-- 4. 创建视图，展示 SQL 性能排行榜
DROP VIEW IF EXISTS sql_performance_ranking;
CREATE VIEW sql_performance_ranking AS
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    sum_ms,
    avg_ms,
    max_ms,
    SQL_TEXT
FROM
    temp_sql_performance_data
ORDER BY
    sum_ms DESC;

-- 5. 查询 SQL 性能排行榜
SELECT * FROM sql_performance_ranking;

-- 6. (可选) 定期清理 events_statements_history 表和重置 events_statements_summary_global_by_digest 表
-- 创建事件，定时清理 events_statements_history 表
DROP EVENT IF EXISTS cleanup_performance_schema_history;
CREATE EVENT cleanup_performance_schema_history
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    TRUNCATE TABLE performance_schema.events_statements_history;
END;

-- 创建事件，定时重置 events_statements_summary_global_by_digest 表
DROP EVENT IF EXISTS reset_performance_schema_summary;
CREATE EVENT reset_performance_schema_summary
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    TRUNCATE TABLE performance_schema.events_statements_summary_global_by_digest;
END;

7. 总结与展望

通过Performance Schema，我们可以深入了解MySQL数据库的内部运行情况，从而更好地优化SQL语句，提高数据库的整体性能。这个例子展示了如何构建一个简单的实时SQL性能排行榜，但Performance Schema的功能远不止于此。大家可以根据自己的实际需求，灵活运用Performance Schema，进行更深入的性能分析和优化。

8. 核心要点回顾

• Performance Schema 是MySQL的性能监控工具，通过 instrument 和 consumer 收集和消费数据。
• 通过配置setup_instruments和setup_consumers表来控制收集哪些数据。
• 利用events_statements_summary_global_by_digest和events_statements_history表构建SQL性能排行榜。
• 可以使用事件调度器定时刷新数据，实现实时监控。
• 注意Performance Schema的资源消耗，并定期清理历史数据。