MySQL内置数据库performance_schema详解(一)

performanceschema 简介

performance_schema 是 MySQL 数据库中的一个内置的系统数据库，最早从MySQL5.5版本产生，这个数据库主要用于收集和存储与数据库性能相关的统计信息和指标。

使用它可以帮助运维人员对数据库性能监控、调优和故障排查提供依据。

performanceschema 特点

• performanceschema数据库是mysql5.5及后续的版本才会有，并且在MySQL5.7当中默认启用，可以在MySQL配置参数里面关闭，可以节约一部分性能的消耗。
• performanceschema数据库中的表使用performanceschema存储引擎管理，主要是监控数据库运行过程中的性能数据的收集。
• performanceschema通过监视server的事件（函数调用、操作系统等待、SQL语句执行阶段等）来实现监视server内部运行情况。
• performanceschema中的事件与写入二进制日志中的事件、事件计划调度程序，事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
• performanceschema中的事件只记录在本地server的performanceschema中，表中数据发生变化时不会被写入binlog中，也不会通过复制机制被复制到其他server中。
• performanceschema存储引擎使用server源代码中的“检测点”来实现事件数据的收集。

收集的事件数据存储在performanceschema数据库的表中，支持select进行查询。

• performanceschema表数据非持久化存储，而是保存在内存当中，MySQL服务重启，该数据库所有的表数据库也会被清空。

performanceschema 事件记录表介绍

语句事件记录表主要记录了语句事件信息，查看所有表

     show tables like '%statement%';

当前语句事件记录表

events_statements_current

• 作用：用于显示当前正在执行的语句的信息，包括执行时间、等待时间、扫描行数等。

SELECT * from events_statements_current

字段说明

• THREAD_ID：正在执行语句的线程 ID。
• EVENT_ID：语句的事件 ID。
• END_EVENT_ID：语句执行结束时的事件 ID。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• DIGEST：语句的摘要。
• USER：执行语句的用户。
• HOST：执行语句的主机。
• DB：执行语句的数据库。
• COMMAND_TYPE：语句的命令类型。
• SQL_TEXT：语句的文本。
• TIME_MS：执行语句的时间，以毫秒为单位。
• LOCK_TIME_MS：语句等待锁的时间，以毫秒为单位。
• ROWS_AFFECTED：语句影响的行数。
• ROWS_SENT：语句返回的行数。
• ROWS_EXAMINED：语句扫描的行数。
• CREATED_TMP_DISK_TABLES：在执行语句过程中创建的临时磁盘表数目。
• CREATED_TMP_TABLES：在执行语句过程中创建的临时表数目。
• SORT_ROWS：在执行语句过程中排序的行数。
• NO_INDEX_USED：是否没有使用索引。
• NO_GOOD_INDEX_USED：是否没有使用合适的索引。

历史语句事件记录表

events_statements_history

• 作用：最近执行的语句的详细信息，包括执行时间、等待时间、影响的行数等

SELECT * from  events_statements_history

字段说明

• THREAD_ID：执行语句的线程 ID。
• EVENT_ID：语句的事件 ID。
• END_EVENT_ID：语句执行结束时的事件 ID。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• DIGEST：语句的摘要。
• USER：执行语句的用户。
• HOST：执行语句的主机。
• DB：执行语句的数据库。
• COMMAND_TYPE：语句的命令类型。
• SQL_TEXT：语句的文本。
• TIME_MS：执行语句的时间，以毫秒为单位。
• LOCK_TIME_MS：语句等待锁的时间，以毫秒为单位。
• ROWS_AFFECTED：语句影响的行数。
• ROWS_SENT：语句返回的行数。
• ROWS_EXAMINED：语句扫描的行数。
• CREATED_TMP_DISK_TABLES：在执行语句过程中创建的临时磁盘表数目。
• CREATED_TMP_TABLES：在执行语句过程中创建的临时表数目。
• SORT_ROWS：在执行语句过程中排序的行数。
• NO_INDEX_USED：是否没有使用索引。
• NO_GOOD_INDEX_USED：是否没有使用合适的索引。

长语句历史事件记录表

events_statements_history_long

• 作用：查询最近执行的语句的详细信息，包括执行时间、等待时间、影响的行数等。

SELECT * from  events_statements_history_long;

字段说明

• THREAD_ID：执行语句的线程 ID。
• EVENT_ID：语句的事件 ID。
• END_EVENT_ID：语句执行结束时的事件 ID。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• DIGEST：语句的摘要。
• USER：执行语句的用户。
• HOST：执行语句的主机。
• DB：执行语句的数据库。
• COMMAND_TYPE：语句的命令类型。
• SQL_TEXT：语句的文本。
• TIME_MS：执行语句的时间，以毫秒为单位。
• LOCK_TIME_MS：语句等待锁的时间，以毫秒为单位。
• ROWS_AFFECTED：语句影响的行数。
• ROWS_SENT：语句返回的行数。
• ROWS_EXAMINED：语句扫描的行数。
• CREATED_TMP_DISK_TABLES：在执行语句过程中创建的临时磁盘表数目。
• CREATED_TMP_TABLES：在执行语句过程中创建的临时表数目。
• SORT_ROWS：在执行语句过程中排序的行数。
• NO_INDEX_USED：是否没有使用索引。
• NO_GOOD_INDEX_USED：是否没有使用合适的索引。

按账户和事件名称对语句执行进行汇总的统计信息表

events_statements_summary_by_account_by_event_name

• 作用：每个账户在不同事件下执行语句的频率和性能表现。

events_statements_summary_by_account_by_event_name

字段说明

• USER：执行语句的用户。
• HOST：执行语句的主机。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• COUNT_STAR：在特定事件下由该账户执行的语句次数。
• SUM_TIMER_WAIT：在特定事件下由该账户执行语句的总等待时间（纳秒）。
• MIN_TIMER_WAIT：在特定事件下由该账户执行语句的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：在特定事件下由该账户执行语句的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：在特定事件下由该账户执行语句的最大等待时间（纳秒）。

语句摘要对语句执行进行汇总的统计信息

events_statements_summary_by_digest

• 作用：每个语句的执行频率和性能表现

SELECT * from  events_statements_summary_by_digest

字段说明

• DIGEST：语句的摘要，用于唯一标识语句。
• SCHEMA_NAME：执行语句的数据库模式名称。
• COUNT_STAR：该语句执行的次数。
• SUM_TIMER_WAIT：该语句执行的总等待时间（纳秒）。
• MIN_TIMER_WAIT：该语句执行的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：该语句执行的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：该语句执行的最大等待时间（纳秒）。

按主机和事件名称对语句执行进行汇总的统计信息

events_statements_summary_by_host_by_event_name

• 作用： 查询每个主机在不同事件下执行语句的频率和性能表现。

SELECT * from  events_statements_summary_by_host_by_event_name

字段说明

• HOST：执行语句的主机名。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• COUNT_STAR：在特定事件下由该主机执行的语句次数。
• SUM_TIMER_WAIT：在特定事件下由该主机执行语句的总等待时间（纳秒）。
• MIN_TIMER_WAIT：在特定事件下由该主机执行语句的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：在特定事件下由该主机执行语句的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：在特定事件下由该主机执行语句的最大等待时间（纳秒）。

按程序名称对语句执行进行汇总的统计信息

events_statements_summary_by_program

• 作用：查询不同事件下语句执行的频率和性能表现。

SELECT * from   events_statements_summary_by_program

字段说明

• USER：执行语句的用户。
• HOST：执行语句的主机。
• PROGRAM_NAME：执行语句的程序名称。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• COUNT_STAR：在特定事件下由该程序执行的语句次数。
• SUM_TIMER_WAIT：在特定事件下由该程序执行语句的总等待时间（纳秒）。
• MIN_TIMER_WAIT：在特定事件下由该程序执行语句的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：在特定事件下由该程序执行语句的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：在特定事件下由该程序执行语句的最大等待时间（纳秒）。

按线程和事件名称对语句执行进行汇总的统计信息表

events_statements_summary_by_thread_by_event_name

• 作用：每个线程在不同事件下执行语句的频率和性能表现

SELECT * from    events_statements_summary_by_thread_by_event_name

字段说明

• THREAD_ID：执行语句的线程 ID。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• COUNT_STAR：在特定事件下由该线程执行的语句次数。
• SUM_TIMER_WAIT：在特定事件下由该线程执行语句的总等待时间（纳秒）。
• MIN_TIMER_WAIT：在特定事件下由该线程执行语句的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：在特定事件下由该线程执行语句的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：在特定事件下由该线程执行语句的最大等待时间（纳秒）。

按用户和事件名称对语句执行进行汇总的统计信表

events_statements_summary_by_user_by_event_name

• 作用：查询每个用户在不同事件下执行语句的频率和性能表现。这对于识别性能瓶颈和优化查询提供帮助。

SELECT * from  events_statements_summary_by_user_by_event_name;

字段说明

• USER：执行语句的用户。
• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• COUNT_STAR：该用户在特定事件下执行语句的次数。
• SUM_TIMER_WAIT：该用户在特定事件下执行语句的总等待时间（纳秒）。
• MIN_TIMER_WAIT：该用户在特定事件下执行语句的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：该用户在特定事件下执行语句的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：该用户在特定事件下执行语句的最大等待时间（纳秒）。

按事件名称对全局语句执行进行汇总的统计信息

events_statements_summary_global_by_event_name

• 作用：查询不同事件下语句执行的频率和性能表现。

SELECT * from  events_statements_summary_global_by_event_name;

字段说明

• EVENT_NAME：语句的事件名称，例如 SQL SELECT、SQL UPDATE 等。
• COUNT_STAR：在特定事件下执行的语句次数。
• SUM_TIMER_WAIT：在特定事件下执行语句的总等待时间（纳秒）。
• MIN_TIMER_WAIT：在特定事件下执行语句的最小等待时间（纳秒）。
• AVG_TIMER_WAIT：在特定事件下执行语句的平均等待时间（纳秒）。
• MAX_TIMER_WAIT：在特定事件下执行语句的最大等待时间（纳秒）。

存储已准备的语句实例的信息表

prepared_statements_instances

• 作用：获取已准备的语句实例的相关信息，包括语句文本、执行次数、错误次数等。

字段说明

• OBJECT_INSTANCE_BEGIN：语句实例的内存地址。
• STATEMENT_ID：语句的唯一标识符。
• STATEMENT_NAME：语句的名称。
• SQL_TEXT：语句的文本内容。
• OWNER_THREAD_ID：拥有该语句实例的线程 ID。
• OWNER_EVENT_ID：拥有该语句实例的事件 ID。
• LATENCY：语句实例的延迟时间（纳秒）。
• EXECUTION_COUNT：语句实例的执行次数。
• ERROR_COUNT：语句实例的错误次数。
• LAST_SEEN_TIMESTAMP：语句实例最后一次出现的时间戳。

events_statements_history 和 events_statements_history_long 区别

两者用于记录执行过的语句的历史信息。它们的主要区别在于记录的时间范围和详细程度。

events_statements_history： 记录了最近执行的语句的历史信息，包括执行时间、等待时间、扫描行数等。它只会保留一定数量的最近执行语句的记录，默认保留 10 万个记录，可以通过配置参数 performance_schema_events_statements_history_size 来调整。这个表适用于查看最近执行的语句的概要信息，并进行性能分析和优化。

events_statements_history_long ：记录了更长时间范围内的执行语句的历史信息，包括执行时间、等待时间、扫描行数等。它会保留更多的历史记录，默认情况下是保留 100 万个记录，可以通过配置参数 performance_schema_events_statements_history_long_size 来调整。这个表适用于更详细的性能分析和长期趋势分析。

总结：events_statements_history 适用于查看最近执行的语句的概要信息和性能分析，而 events_statements_history_long 则适用于更详细的性能分析和长期趋势分析。

我正在参与2023腾讯技术创作特训营第四期有奖征文，快来和我瓜分大奖！