MySQL的Performance Schema：如何利用其`events_statements_summary_by_digest`表分析热点SQL？

MySQL Performance Schema: 挖掘 events_statements_summary_by_digest 寻找性能瓶颈

大家好，今天我们来深入探讨MySQL Performance Schema，特别是 events_statements_summary_by_digest 表，以及如何利用它来识别并解决数据库中的热点SQL。 热点SQL通常是指那些执行频率高、消耗资源多的SQL语句，它们是数据库性能瓶颈的主要来源。 准确地找出这些热点SQL，并针对性地进行优化，可以显著提升数据库的整体性能。

Performance Schema 简介

Performance Schema是MySQL 5.5及更高版本中引入的一个性能监控工具。 它通过收集服务器运行时的各种指标数据，帮助我们深入了解MySQL的内部工作机制。与慢查询日志相比，Performance Schema的优势在于：

• 更细粒度的监控: Performance Schema可以追踪更细粒度的事件，例如单个SQL语句的执行时间、锁等待、I/O操作等。
• 实时性: Performance Schema的数据是实时的，可以动态地观察数据库的运行状态。
• 更结构化的数据: Performance Schema将数据存储在表中，方便我们使用SQL进行查询和分析。
• 可配置性: 可以根据需要启用或禁用特定的instrumentation，以减少对数据库性能的影响。

events_statements_summary_by_digest 表详解

events_statements_summary_by_digest 表是Performance Schema中最核心的表之一，它按SQL语句的digest进行聚合，记录了每种类型的SQL语句的执行统计信息。 了解这个表的结构至关重要。

该表的主要字段包括：

字段名称	数据类型	描述
SCHEMA_NAME	VARCHAR(64)	数据库名。
DIGEST	VARCHAR(32)	SQL语句的摘要，用于标识具有相同逻辑结构的SQL语句。例如，SELECT * FROM t1 WHERE id = ? 的不同参数值会生成不同的SQL文本，但拥有相同的DIGEST。
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_SELECT_FULL_JOIN	BIGINT	执行全连接的次数
SUM_SELECT_FULL_RANGE_JOIN	BIGINT	执行使用range优化的全连接的次数。
SUM_SELECT_RANGE	BIGINT	执行范围扫描的次数。
SUM_SELECT_RANGE_CHECK	BIGINT	执行范围检查的次数。
SUM_SELECT_SCAN	BIGINT	执行全表扫描的次数。
SUM_SORT_MERGE_PASSES	BIGINT	执行排序合并的次数。
SUM_SORT_RANGE	BIGINT	执行范围排序的次数。
SUM_SORT_SCAN	BIGINT	执行全表扫描排序的次数。
SUM_NO_INDEX_USED	BIGINT	SQL语句执行时没有使用索引的次数。
SUM_NO_GOOD_INDEX_USED	BIGINT	SQL语句执行时使用了不好的索引的次数。
FIRST_SEEN	TIMESTAMP	SQL语句首次出现的时间。
LAST_SEEN	TIMESTAMP	SQL语句最后一次出现的时间。

如何利用 events_statements_summary_by_digest 找出热点SQL

以下是一些常用的查询方法，可以帮助我们找出热点SQL：

1. 按执行时间排序，找出消耗时间最多的SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

这个查询会返回执行时间最长的前10条SQL语句的摘要信息，包括数据库名、SQL语句文本、执行次数、总执行时间和平均执行时间。 SUM_TIMER_WAIT 是关键指标，表示SQL语句的总执行时间。

2. 按执行次数排序，找出执行频率最高的SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    COUNT_STAR DESC
LIMIT 10;

这个查询会返回执行次数最多的前10条SQL语句的摘要信息。 COUNT_STAR 是关键指标，表示SQL语句的执行次数。 高频执行的SQL语句也可能是性能瓶颈，尤其是在平均执行时间较长的情况下。

3. 结合执行时间和执行次数，找出综合性能最差的SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT,
    (SUM_TIMER_WAIT / COUNT_STAR) AS avg_latency
FROM
    performance_schema.events_statements_summary_by_digest
WHERE COUNT_STAR > 10 -- 过滤掉执行次数过少的SQL，避免偶然因素干扰
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

这个查询首先过滤掉执行次数过少的SQL语句，然后按照总执行时间排序，找出综合性能最差的SQL语句。 avg_latency 列计算了平均延迟，可以更清晰地了解SQL语句的性能。

4. 找出锁等待时间最长的SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_LOCK_TIME,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    SUM_LOCK_TIME DESC
LIMIT 10;

这个查询会返回锁等待时间最长的前10条SQL语句。 SUM_LOCK_TIME 是关键指标，表示SQL语句的总锁等待时间。 锁等待通常是并发问题导致的，需要仔细分析SQL语句的执行计划和表结构，以及数据库的并发访问模式。

5. 找出扫描行数最多的SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_ROWS_EXAMINED,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    SUM_ROWS_EXAMINED DESC
LIMIT 10;

这个查询会返回扫描行数最多的前10条SQL语句。 SUM_ROWS_EXAMINED 是关键指标，表示SQL语句扫描的总行数。 大量扫描行数通常意味着SQL语句没有使用索引，或者索引选择不当，需要优化SQL语句或添加合适的索引。

6. 找出没有使用索引的SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SUM_NO_INDEX_USED > 0
ORDER BY
    SUM_NO_INDEX_USED DESC
LIMIT 10;

这个查询会返回没有使用索引的SQL语句。 SUM_NO_INDEX_USED 是关键指标，表示SQL语句执行时没有使用索引的次数。 如果 SUM_NO_INDEX_USED 的值很大，则说明该SQL语句需要优化，可以考虑添加索引或修改SQL语句的写法。

7. 找出执行全表扫描的SQL:

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_SELECT_SCAN,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SUM_SELECT_SCAN > 0
ORDER BY
    SUM_SELECT_SCAN DESC
LIMIT 10;

这个查询会返回执行全表扫描的SQL语句。SUM_SELECT_SCAN 是关键指标，表示SQL语句执行全表扫描的次数。全表扫描通常是性能杀手，应尽量避免。

8. 按数据库过滤，分析特定数据库的热点SQL：

SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT,
    AVG_TIMER_WAIT
FROM
    performance_schema.events_statements_summary_by_digest
WHERE
    SCHEMA_NAME = 'your_database_name' -- 替换为你的数据库名
ORDER BY
    SUM_TIMER_WAIT DESC
LIMIT 10;

这个查询可以限制分析范围，只关注特定数据库的热点SQL。 将 'your_database_name' 替换为实际的数据库名。

9. 结合时间段进行分析：

虽然 events_statements_summary_by_digest 本身不直接存储时间范围数据，但是可以通过结合其他表或者外部工具来分析特定时间段内的热点SQL。 例如，可以定期将 events_statements_summary_by_digest 的数据导出到外部存储，然后进行时间序列分析。

10. 使用存储过程定期分析数据：

为了方便日常维护，可以将上述查询封装成存储过程，定期执行并生成报告。 例如：

DELIMITER //
CREATE PROCEDURE AnalyzeHotSQL()
BEGIN
    -- 创建临时表存储分析结果
    CREATE TEMPORARY TABLE IF NOT EXISTS HotSQLAnalysis (
        id INT AUTO_INCREMENT PRIMARY KEY,
        SCHEMA_NAME VARCHAR(64),
        DIGEST_TEXT LONGTEXT,
        COUNT_STAR BIGINT,
        SUM_TIMER_WAIT BIGINT,
        AVG_TIMER_WAIT BIGINT,
        analysis_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- 清空临时表
    TRUNCATE TABLE HotSQLAnalysis;

    -- 插入分析结果
    INSERT INTO HotSQLAnalysis (SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT)
    SELECT
        SCHEMA_NAME,
        DIGEST_TEXT,
        COUNT_STAR,
        SUM_TIMER_WAIT,
        AVG_TIMER_WAIT
    FROM
        performance_schema.events_statements_summary_by_digest
    ORDER BY
        SUM_TIMER_WAIT DESC
    LIMIT 10;

    -- 查询分析结果
    SELECT * FROM HotSQLAnalysis;

    -- （可选）可以将分析结果写入日志文件或发送邮件
    -- SELECT * FROM HotSQLAnalysis INTO OUTFILE '/tmp/hot_sql_analysis.txt';
END //
DELIMITER ;

-- 调用存储过程
CALL AnalyzeHotSQL();

这个存储过程会定期分析 events_statements_summary_by_digest 表，并将结果存储在临时表中，最后查询临时表并显示结果。 可以根据需要修改存储过程，例如修改分析的SQL语句、调整分析的频率、添加更详细的分析指标等。

优化热点SQL的策略

找到热点SQL后，接下来就是针对性地进行优化。 以下是一些常见的优化策略：

• 添加索引： 这是最常见的优化手段。 根据SQL语句的查询条件，选择合适的列添加索引。 可以使用 EXPLAIN 命令分析SQL语句的执行计划，查看是否使用了索引，以及索引的使用情况。
• 优化SQL语句： 避免使用 SELECT *，只选择需要的列。 避免在 WHERE 子句中使用函数或表达式。 尽量使用 JOIN 代替子查询。 优化 ORDER BY 和 GROUP BY 子句。
• 重写SQL语句： 有时候，即使添加了索引，SQL语句的性能仍然很差，这时可能需要重写SQL语句。 例如，可以将复杂的SQL语句拆分成多个简单的SQL语句，或者使用不同的SQL语句来实现相同的功能。
• 优化表结构： 选择合适的数据类型。 将经常一起查询的列放在同一个表中。 将大表拆分成多个小表。
• 调整MySQL配置： 调整 innodb_buffer_pool_size、key_buffer_size 等参数，以提高MySQL的性能。
• 使用缓存： 对于一些不经常变化的查询结果，可以使用缓存来提高性能。 可以使用MySQL的查询缓存，或者使用外部缓存系统，例如Redis或Memcached。
• 升级硬件： 如果以上优化手段都无法解决性能问题，那么可能需要升级硬件，例如增加CPU、内存或磁盘。

案例分析

假设通过 events_statements_summary_by_digest 表发现以下SQL语句是热点SQL：

SELECT * FROM orders WHERE customer_id = ?;

这个SQL语句的执行次数很多，而且平均执行时间也比较长。 通过 EXPLAIN 命令分析SQL语句的执行计划，发现没有使用索引。 因此，可以考虑在 customer_id 列上添加索引：

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

添加索引后，再次分析SQL语句的执行计划，发现已经使用了索引。 再次查询 events_statements_summary_by_digest 表，发现该SQL语句的执行时间和执行次数都有所下降，性能得到了显著提升。

注意事项

• Performance Schema的性能开销： 启用Performance Schema会带来一定的性能开销。 应该根据实际需要，选择性地启用instrumentation，避免对数据库性能产生过大的影响。
• 数据采样： Performance Schema的数据是采样数据，可能存在一定的误差。 在分析数据时，应该注意这一点。
• 数据清理： Performance Schema的数据会占用一定的磁盘空间。 应该定期清理旧数据，释放磁盘空间。可以使用 TRUNCATE TABLE 命令清理Performance Schema的表，但是需要注意，这会清空所有数据。
• 权限： 需要具有 PROCESS 和 SUPER 权限才能访问Performance Schema的表。

结论：通过数据驱动的性能优化

events_statements_summary_by_digest 表是MySQL Performance Schema中一个强大的工具，它能帮助我们快速定位数据库中的热点SQL，为性能优化提供数据支撑。 通过分析执行时间、执行次数、锁等待时间、扫描行数等指标，可以深入了解SQL语句的性能瓶颈，并采取针对性的优化策略。 记住，性能优化是一个持续的过程，需要不断地监控、分析和调整。 掌握Performance Schema的使用，可以帮助我们更好地管理和优化MySQL数据库，提升整体性能。

进一步研究的方向

本文介绍了如何使用 events_statements_summary_by_digest 表来识别热点SQL。 然而，这只是Performance Schema的冰山一角。 还有许多其他的表和instrumentation可以帮助我们更深入地了解MySQL的内部工作机制。 例如，可以使用 events_waits_summary_global_by_event_name 表来分析锁等待事件，使用 file_summary_by_event_name 表来分析I/O操作。 深入研究Performance Schema，可以帮助我们成为真正的MySQL专家。