发布于 由admin

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

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

大家好，今天我们来探讨如何利用MySQL的Performance Schema构建一个基于SQL执行时间的实时性能排行榜。Performance Schema是MySQL 5.5版本引入的一个用于监控MySQL服务器性能的工具。它通过收集服务器运行时的各种性能数据，并将其以表的形式存储在名为performance_schema的数据库中，从而为我们提供了深入了解服务器内部运行状况的窗口。

1. Performance Schema 简介

Performance Schema 并非默认开启。它通过收集服务器运行时的各种性能事件，例如SQL语句的执行时间、锁的等待时间、I/O操作的耗时等，为我们提供深入了解服务器内部运行状况的窗口。 这些数据以表的形式存储在名为performance_schema的数据库中。

与传统的查询慢日志不同，Performance Schema具有以下优势：

  • 更细粒度的数据: Performance Schema可以收集更细粒度的数据，例如SQL语句的每个阶段的执行时间。
  • 更低的性能开销: Performance Schema的性能开销通常比慢查询日志更低，因为它只在需要时才收集数据。
  • 更灵活的配置: Performance Schema可以灵活配置，只收集需要的数据。

2. 启用 Performance Schema

如果你的MySQL服务器尚未启用Performance Schema，需要进行如下配置：

  • 修改配置文件: 打开MySQL的配置文件(例如my.cnf或my.ini)，添加或修改以下配置项：
performance_schema = on

  • 重启MySQL服务器: 保存配置文件后，重启MySQL服务器以使配置生效。

  • 验证是否启用: 重启后，登录MySQL，执行以下命令验证Performance Schema是否已启用：

SELECT @@performance_schema;

如果返回值为1，则表示Performance Schema已成功启用。

3. 关键 Performance Schema 表

构建SQL执行时间排行榜，主要依赖以下几个Performance Schema表：

  • events_statements_summary_by_digest: 该表汇总了具有相同DIGEST值的SQL语句的执行信息。DIGEST是SQL语句的规范化表示，例如将字面常量替换为?，从而将具有相同逻辑结构的SQL语句归为一类。

    列名 数据类型 描述
    DIGEST VARCHAR(32) SQL语句的摘要，用于标识具有相同逻辑结构的SQL语句。
    DIGEST_TEXT LONGTEXT SQL语句的规范化文本。
    COUNT_STAR BIGINT SQL语句的执行次数。
    SUM_TIMER_WAIT BIGINT SQL语句的总执行时间（单位：皮秒）。
    MIN_TIMER_WAIT BIGINT SQL语句的最小执行时间（单位：皮秒）。
    AVG_TIMER_WAIT BIGINT SQL语句的平均执行时间（单位：皮秒）。
    MAX_TIMER_WAIT BIGINT SQL语句的最大执行时间（单位：皮秒）。
    SUM_LOCK_TIME BIGINT SQL语句的总锁等待时间（单位：皮秒）。
    SUM_ERRORS BIGINT SQL语句执行错误的次数。
    SUM_WARNINGS BIGINT SQL语句执行警告的次数。
    SUM_ROWS_AFFECTED BIGINT SQL语句影响的行数总和。
    SUM_ROWS_SENT BIGINT SQL语句发送的行数总和。
    SUM_ROWS_EXAMINED BIGINT SQL语句检查的行数总和。
    SUM_CREATED_TMP_DISK_TABLES BIGINT SQL语句创建的磁盘临时表数量总和。
    SUM_CREATED_TMP_TABLES BIGINT SQL语句创建的临时表数量总和。
    SUM_SELECT_FULL_JOIN BIGINT 使用全连接的SQL语句的数量总和。
    SUM_SELECT_FULL_RANGE_JOIN BIGINT 使用范围连接的SQL语句的数量总和。
    SUM_SELECT_RANGE BIGINT 使用范围扫描的SQL语句的数量总和。
    SUM_SELECT_RANGE_CHECK BIGINT 使用范围检查的SQL语句的数量总和。
    SUM_SELECT_SCAN BIGINT 使用全表扫描的SQL语句的数量总和。
    SUM_SORT_MERGE_PASSES BIGINT 排序合并通过的次数总和。
    SUM_SORT_RANGE BIGINT 使用范围扫描进行排序的SQL语句的数量总和。
    SUM_SORT_ROWS BIGINT 排序的行数总和。
    SUM_SORT_SCAN BIGINT 使用全表扫描进行排序的SQL语句的数量总和。
    SUM_NO_INDEX_USED BIGINT 未使用索引的SQL语句的数量总和。
    SUM_NO_GOOD_INDEX_USED BIGINT 未使用良好索引的SQL语句的数量总和。
    FIRST_SEEN TIMESTAMP SQL语句首次被观察到的时间。
    LAST_SEEN TIMESTAMP SQL语句最后一次被观察到的时间。

  • events_statements_history_long: 该表记录了最近执行的SQL语句的详细信息，包括执行时间、锁等待时间、I/O操作耗时等。由于该表存储详细的事件信息，因此会占用较多的存储空间。

    列名 数据类型 描述
    EVENT_ID BIGINT 事件ID，唯一标识一个事件。
    EVENT_NAME VARCHAR(128) 事件名称，通常为statement/sql/开头，后面跟着SQL语句类型。
    SOURCE VARCHAR(64) 事件发生的源代码位置。
    THREAD_ID BIGINT 执行事件的线程ID。
    TIMER_START BIGINT 事件开始时间（单位：皮秒）。
    TIMER_END BIGINT 事件结束时间（单位：皮秒）。
    TIMER_WAIT BIGINT 事件持续时间（单位：皮秒）。
    LOCK_TIME BIGINT 事件锁等待时间（单位：皮秒）。
    ERRORS BIGINT 事件执行错误的次数。
    WARNINGS BIGINT 事件执行警告的次数。
    ROWS_AFFECTED BIGINT 事件影响的行数。
    ROWS_SENT BIGINT 事件发送的行数。
    ROWS_EXAMINED BIGINT 事件检查的行数。
    CREATED_TMP_DISK_TABLES BIGINT 事件创建的磁盘临时表数量。
    CREATED_TMP_TABLES BIGINT 事件创建的临时表数量。
    SELECT_FULL_JOIN BIGINT 事件是否使用全连接。
    SELECT_FULL_RANGE_JOIN BIGINT 事件是否使用范围连接。
    SELECT_RANGE BIGINT 事件是否使用范围扫描。
    SELECT_RANGE_CHECK BIGINT 事件是否使用范围检查。
    SELECT_SCAN BIGINT 事件是否使用全表扫描。
    SORT_MERGE_PASSES BIGINT 事件排序合并通过的次数。
    SORT_RANGE BIGINT 事件是否使用范围扫描进行排序。
    SORT_ROWS BIGINT 事件排序的行数。
    SORT_SCAN BIGINT 事件是否使用全表扫描进行排序。
    NO_INDEX_USED BIGINT 事件是否未使用索引。
    NO_GOOD_INDEX_USED BIGINT 事件是否未使用良好索引。
    NESTING_EVENT_ID BIGINT 嵌套事件的ID。
    NESTING_EVENT_TYPE ENUM 嵌套事件的类型。
    DIGEST VARCHAR(32) SQL语句的摘要，用于标识具有相同逻辑结构的SQL语句。
    DIGEST_TEXT LONGTEXT SQL语句的规范化文本。

  • setup_instruments: 该表用于控制哪些事件需要被收集。通过修改该表中的ENABLED列，可以开启或关闭特定事件的收集。

  • setup_consumers: 该表用于控制哪些事件数据需要被存储到相应的表中。例如，可以将events_statements_history_long消费者设置为OFF，以停止收集SQL语句的详细历史信息。

4. 构建实时性能排行榜

以下提供两种构建实时性能排行榜的方案：

方案一：基于 events_statements_summary_by_digest 表

该方案基于events_statements_summary_by_digest表，汇总相同DIGEST值的SQL语句的执行信息，并按照平均执行时间进行排序。这种方案的优点是简单易懂，性能开销较低。

步骤：

  1. 创建存储过程: 创建一个存储过程，定期从events_statements_summary_by_digest表中查询数据，并将其插入到临时表中。
DROP PROCEDURE IF EXISTS refresh_statement_summary;
DELIMITER //
CREATE PROCEDURE refresh_statement_summary()
BEGIN
  -- 创建临时表，如果存在则先删除
  DROP TEMPORARY TABLE IF EXISTS tmp_statement_summary;
  CREATE TEMPORARY TABLE tmp_statement_summary AS
  SELECT
    DIGEST,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    MAX_TIMER_WAIT,
    FIRST_SEEN,
    LAST_SEEN
  FROM performance_schema.events_statements_summary_by_digest
  ORDER BY AVG_TIMER_WAIT DESC
  LIMIT 100; -- 可以调整LIMIT值
END //
DELIMITER ;
  1. 创建定时任务: 使用MySQL的事件调度器创建一个定时任务，定期调用该存储过程。
DROP EVENT IF EXISTS refresh_statement_summary_event;
CREATE EVENT refresh_statement_summary_event
ON SCHEDULE EVERY 1 MINUTE -- 可以调整刷新频率
DO
  CALL refresh_statement_summary();
  1. 查询排行榜数据: 创建一个视图或者直接查询临时表，获取性能排行榜数据。
-- 创建视图
DROP VIEW IF EXISTS statement_summary_ranking;
CREATE VIEW statement_summary_ranking AS
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT,
  AVG_TIMER_WAIT,
  MAX_TIMER_WAIT,
  FIRST_SEEN,
  LAST_SEEN
FROM tmp_statement_summary
ORDER BY AVG_TIMER_WAIT DESC;

-- 直接查询临时表
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_TIMER_WAIT,
  AVG_TIMER_WAIT,
  MAX_TIMER_WAIT,
  FIRST_SEEN,
  LAST_SEEN
FROM tmp_statement_summary
ORDER BY AVG_TIMER_WAIT DESC;
  1. 重置 Performance Schema (可选): 为了避免数据积累，可以定期重置events_statements_summary_by_digest表。 注意：重置会导致数据丢失，请谨慎操作。
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

方案二：基于 events_statements_history_long 表

该方案基于events_statements_history_long表，直接查询最近执行的SQL语句的执行时间，并按照执行时间进行排序。这种方案的优点是可以获取更详细的执行信息，但性能开销较高，需要谨慎使用。

步骤：

  1. 创建存储过程: 创建一个存储过程，定期从events_statements_history_long表中查询数据，并将其插入到临时表中。
DROP PROCEDURE IF EXISTS refresh_statement_history;
DELIMITER //
CREATE PROCEDURE refresh_statement_history()
BEGIN
  -- 创建临时表，如果存在则先删除
  DROP TEMPORARY TABLE IF EXISTS tmp_statement_history;
  CREATE TEMPORARY TABLE tmp_statement_history AS
  SELECT
    EVENT_ID,
    DIGEST_TEXT,
    TIMER_WAIT,
    LOCK_TIME,
    ERRORS,
    WARNINGS,
    ROWS_AFFECTED,
    ROWS_SENT,
    ROWS_EXAMINED
  FROM performance_schema.events_statements_history_long
  ORDER BY TIMER_WAIT DESC
  LIMIT 100; -- 可以调整LIMIT值
END //
DELIMITER ;
  1. 创建定时任务: 使用MySQL的事件调度器创建一个定时任务，定期调用该存储过程。
DROP EVENT IF EXISTS refresh_statement_history_event;
CREATE EVENT refresh_statement_history_event
ON SCHEDULE EVERY 1 MINUTE -- 可以调整刷新频率
DO
  CALL refresh_statement_history();
  1. 查询排行榜数据: 创建一个视图或者直接查询临时表，获取性能排行榜数据。
-- 创建视图
DROP VIEW IF EXISTS statement_history_ranking;
CREATE VIEW statement_history_ranking AS
SELECT
  EVENT_ID,
  DIGEST_TEXT,
  TIMER_WAIT,
  LOCK_TIME,
  ERRORS,
  WARNINGS,
  ROWS_AFFECTED,
  ROWS_SENT,
  ROWS_EXAMINED
FROM tmp_statement_history
ORDER BY TIMER_WAIT DESC;

-- 直接查询临时表
SELECT
  EVENT_ID,
  DIGEST_TEXT,
  TIMER_WAIT,
  LOCK_TIME,
  ERRORS,
  WARNINGS,
  ROWS_AFFECTED,
  ROWS_SENT,
  ROWS_EXAMINED
FROM tmp_statement_history
ORDER BY TIMER_WAIT DESC;
  1. 控制 events_statements_history_long 的大小: 由于该表存储详细的事件信息，因此会占用较多的存储空间。 可以通过修改performance_schema.setup_instruments和performance_schema.setup_consumers表来控制收集哪些事件，以及存储多少事件。 另外，也可以定期清空该表。 注意：清空会导致数据丢失，请谨慎操作。
TRUNCATE TABLE performance_schema.events_statements_history_long;

5. 优化 Performance Schema 配置

Performance Schema 的默认配置可能不适合所有场景。 可以通过以下方式优化 Performance Schema 配置：

  • 只收集需要的事件: 通过修改performance_schema.setup_instruments表，只开启需要收集的事件，可以减少性能开销。 例如，如果只需要监控SQL语句的执行时间，可以关闭其他事件的收集。
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'wait/%';
  • 调整历史记录大小: 通过修改performance_schema.setup_consumers表，调整历史记录的大小，可以控制Performance Schema的存储空间占用。 例如，可以减小events_statements_history_long表的大小。
UPDATE performance_schema.setup_consumers SET ENABLED = 'NO' WHERE NAME = 'events_statements_history_long';
  • 使用过滤条件: 在查询Performance Schema表时，可以使用过滤条件，只查询需要的数据，可以提高查询效率。 例如，可以只查询特定用户的SQL语句的执行信息。
SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST LIKE '%SELECT%';

6. 注意事项

  • 性能开销: 启用Performance Schema会带来一定的性能开销，需要根据实际情况进行评估和调整。
  • 存储空间: Performance Schema需要占用一定的存储空间，需要根据实际情况进行规划。
  • 数据安全: Performance Schema 存储了敏感的性能数据，需要注意数据安全，防止泄露。
  • 版本兼容性: Performance Schema 的表结构和配置方式可能因MySQL版本而异，需要注意版本兼容性。
  • 重置操作: 清空Performance Schema表会导致数据丢失，请谨慎操作。

7. 额外优化方向

  • 前端展示: 可以将排行榜数据通过Web界面或者其他方式进行展示，方便用户查看和分析。
  • 告警系统: 可以根据排行榜数据设置告警规则，当出现性能问题时及时通知相关人员。
  • 数据分析: 可以对排行榜数据进行深入分析，找出性能瓶颈，并进行优化。
  • 集成监控系统: 可以将Performance Schema 数据集成到现有的监控系统中，实现统一的监控和管理。
  • 定制化报表: 可以根据实际需求，定制化生成各种性能报表，例如SQL执行时间趋势图、慢查询Top N等。

8. 总结：利用Performance Schema，监控并优化SQL性能

Performance Schema 是一个强大的MySQL性能监控工具，通过合理的配置和使用，可以帮助我们深入了解MySQL服务器的运行状况，及时发现和解决性能问题。 基于Performance Schema构建实时性能排行榜，可以帮助我们更好地监控SQL语句的执行情况，及时发现慢查询和性能瓶颈，并进行优化，最终提升数据库的整体性能。 通过上述两种方案，我们分别利用汇总表和历史表构建了排行榜，并且说明了优化Performance Schema配置的方法，希望能帮助大家更好地利用这个强大的工具。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注