发布于 由admin

MySQL编程进阶之：如何利用`Performance Schema`来监控和分析存储过程和函数的性能。

嘿，各位好！今天咱们来聊聊MySQL存储过程和函数的性能监控，当然，少不了咱们的秘密武器：Performance Schema。别被这名字吓到，其实它就像个性能侦探，能帮你揪出代码里的“慢动作”环节。

一、 什么是Performance Schema？别把它当成外星科技！

Performance Schema，简称PS，是MySQL 5.5版本之后引入的一个性能监控工具。它收集MySQL服务器运行时的各种统计信息，比如SQL语句执行时间、锁等待、IO操作等等。你可以把它想象成一个实时监控系统，能告诉你数据库都在干些啥，哪些地方比较忙，哪些地方闲得发慌。

PS不同于慢查询日志，它提供更详细、更结构化的数据，方便我们进行深入分析。而且，PS是通过内存表实现的，对数据库的性能影响非常小，可以放心地开启和使用。

二、 开启Performance Schema：开门见山，直接上代码！

默认情况下，PS可能没有完全开启。你需要检查并确认它已经启动。

  1. 检查PS是否启用：

    SELECT @@performance_schema;

    如果结果是1，说明已经启用。如果是0，那就需要手动开启。

  2. 开启PS：

    在MySQL配置文件（my.cnf或my.ini）中，找到[mysqld]段，添加或修改以下行：

    performance_schema=ON

    然后重启MySQL服务。

  3. 验证PS是否成功开启：

    再次执行 SELECT @@performance_schema;，确认结果为1。

三、 Performance Schema里的存储过程和函数监控表：别迷路，认准这些门牌号！

PS里有很多表，但我们主要关注和存储过程/函数相关的几个：

  • events_statements_summary_by_program：按存储过程/函数汇总的语句执行信息。这是我们的重点！
  • events_statements_history：最近执行的语句的历史记录。
  • events_statements_current：当前正在执行的语句。
  • events_errors_summary_by_account_by_error：错误信息汇总，可以帮助我们发现存储过程/函数中可能存在的错误。

四、 利用events_statements_summary_by_program：揪出“慢动作”存储过程！

events_statements_summary_by_program表按程序（存储过程/函数）汇总了语句的执行信息，包括执行次数、总执行时间、平均执行时间等等。

  1. 查看最耗时的存储过程/函数：

    SELECT
    OBJECT_SCHEMA AS `Database`,
    OBJECT_NAME AS `Procedure/Function`,
    COUNT_STAR AS `Calls`,
    SUM_TIMER_WAIT/1000000000000 AS `Total Time (s)`,
    AVG_TIMER_WAIT/1000000000000 AS `Average Time (s)`
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

    这个查询会列出调用次数最多、总执行时间最长的10个存储过程/函数。SUM_TIMER_WAIT是总执行时间，单位是皮秒(ps)，需要除以1000000000000转换成秒。

    Database Procedure/Function Calls Total Time (s) Average Time (s)
    my_db slow_procedure 100 10.5 0.105
    my_db another_slow_proc 50 5.2 0.104
    … … … … …

    有了这个列表，我们就知道哪些存储过程/函数需要重点优化了。

  2. 查看特定存储过程/函数的详细信息：

    假设我们要分析名为slow_procedure的存储过程：

    SELECT
    OBJECT_SCHEMA AS `Database`,
    OBJECT_NAME AS `Procedure/Function`,
    COUNT_STAR AS `Calls`,
    SUM_TIMER_WAIT/1000000000000 AS `Total Time (s)`,
    AVG_TIMER_WAIT/1000000000000 AS `Average Time (s)`,
    MIN_TIMER_WAIT/1000000000000 AS `Min Time (s)`,
    MAX_TIMER_WAIT/1000000000000 AS `Max Time (s)`
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION')
AND OBJECT_SCHEMA = 'my_db'  -- 替换为你的数据库名
AND OBJECT_NAME = 'slow_procedure';

    这个查询会显示slow_procedure的调用次数、总执行时间、平均执行时间、最短执行时间和最长执行时间。 通过最大时间，最小时间可以发现一些偶发的性能问题。

  3. 查看存储过程/函数内部的SQL语句执行情况：

    这需要结合events_statements_history或events_statements_current表。但是直接查这些表信息量太大，需要先开启语句级别的监控。

    首先，确保setup_instruments表中statement/%的ENABLED列为YES。如果没有，需要更新：

    UPDATE performance_schema.setup_instruments
SET   ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

    然后，修改setup_consumers表，确保events_statements_history_long和events_statements_current的ENABLED列为YES：

    UPDATE performance_schema.setup_consumers
SET   ENABLED = 'YES'
WHERE NAME IN ('events_statements_history_long', 'events_statements_current');

    现在，我们就可以查询events_statements_history_long表，找到slow_procedure执行过的SQL语句：

    SELECT
    EVENT_ID,
    SQL_TEXT,
    CURRENT_SCHEMA,
    TIMER_WAIT/1000000000000 AS `Time (s)`
FROM performance_schema.events_statements_history_long
WHERE NESTING_EVENT_NAME LIKE '%sp/stmt%'  -- 存储过程/函数内部的语句
AND SQL_TEXT LIKE '%slow_procedure%'      -- 匹配存储过程/函数名
ORDER BY TIMER_WAIT DESC
LIMIT 10;

    这个查询会列出slow_procedure执行过的SQL语句，并按执行时间排序。这样，你就能找到存储过程内部的“慢动作”SQL语句了。

五、 利用events_errors_summary_by_account_by_error：发现隐藏的错误！

存储过程/函数出错也会影响性能，甚至导致程序崩溃。我们可以利用events_errors_summary_by_account_by_error表来监控错误信息。

SELECT
    ERROR_NUMBER,
    SQL_ERROR_MESSAGE,
    COUNT_STAR
FROM performance_schema.events_errors_summary_by_account_by_error
ORDER BY COUNT_STAR DESC
LIMIT 10;

这个查询会列出最常见的错误信息，以及出现的次数。如果发现存储过程/函数相关的错误，就要及时修复。

六、 案例分析：优化一个“慢动作”存储过程

假设我们发现slow_procedure的平均执行时间很长，通过分析events_statements_history_long表，发现其中一条SQL语句执行时间最长：

SELECT * FROM very_large_table WHERE some_column = 'some_value';

这条SQL语句查询一个非常大的表，而且没有使用索引。

优化方案：

  1. 添加索引：

    ALTER TABLE very_large_table ADD INDEX idx_some_column (some_column);

  2. 重写SQL语句（如果可能）：

    看看是否有更有效的查询方式，比如使用JOIN代替子查询，或者使用LIMIT限制返回结果的数量。

  3. 分析表结构：

    如果表结构设计不合理，可能会导致查询效率低下。可以考虑对表进行分区或分表。

  4. 优化存储过程逻辑：

    检查存储过程的逻辑，看看是否有不必要的循环或判断。

七、 Performance Schema的配置和维护：让它更好地为你服务！

  1. 调整数据保留时间：

    PS的数据是保存在内存中的，所以需要定期清理。可以通过调整performance_schema_events_statements_history_long_size等参数来控制数据保留的时间。

    SET GLOBAL performance_schema_events_statements_history_long_size = 1000; -- 设置为1000条记录

  2. 只监控需要的事件：

    PS会收集大量的事件信息，如果不需要所有的信息，可以关闭一些事件，以减少内存占用。

    UPDATE performance_schema.setup_instruments
SET   ENABLED = 'NO', TIMED = 'NO'
WHERE NAME LIKE 'wait/%'; -- 关闭wait事件

  3. 定期分析和优化：

    定期分析PS的数据，找出性能瓶颈，并进行优化。

八、 总结：Performance Schema，你值得拥有！

Performance Schema是一个强大的性能监控工具，可以帮助我们深入了解MySQL服务器的运行情况，找出存储过程/函数的性能瓶颈，并进行优化。虽然刚开始使用可能会觉得有点复杂，但只要掌握了基本的使用方法，就能让你事半功倍。

记住，优化是一个持续的过程，需要不断地监控、分析和调整。希望今天的分享能帮助你更好地利用Performance Schema，打造高性能的MySQL应用！

发表回复

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