MySQL中Performance Schema库的详解（上）

news/2024/12/26 8:12:26/

使用Performance Schema

将通过一些示例来演示如何使用Performance Schema解决常见的故障案例。

检查SQL语句

要启用语句检测，需要启用statement类型的插桩，如表1所述

​ 表1：statement类型的插桩及其描述

插装类描述
statement/spSQL语句，如SELECT，或者CREATE TABLE
statement/scheduler存储过程控制事件调度器命令
statement/sql事件调度器
statement/com命令，如quit、kill、DROP DATABASE，或者 Binlog Dump，有些命令是用户不可用的，只能由mysqld进程调用。
statement/abstract包括四类命令:clone、Query、new packet和relay log

常规SQL语句

Performance Schema将语句指标存储在events_statements_current、events_statements_history和events_statements_history_long表中。这三个表具有相同的结构

接下来我用events_statements_history表来举例，下列注释由AI生成，大家可以参考一下。

查询语句# 
select * from events_statements_history \G

			   THREAD_ID: 47  # 执行该语句的MySQL线程的ID。EVENT_ID: 351  # 该事件的唯一标识符。END_EVENT_ID: 351  # 对于此事件，开始和结束事件ID相同，表示这是一个单一事件。EVENT_NAME: statement/sql/select  # 事件类型，这里是SQL SELECT语句。SOURCE: init_net_server_extension.cc:94  # 事件源代码的位置，可能不是直接相关，因为通常这指向MySQL内部代码位置。TIMER_START: 327261072671000  # 事件开始的时间戳（纳秒）。TIMER_END: 327261372405000  # 事件结束的时间戳（纳秒）。TIMER_WAIT: 299734000  # 事件等待的总时间（纳秒）。LOCK_TIME: 116000000  # 语句获取锁所需的时间（纳秒）。SQL_TEXT: select * from events_transactions_history  where EVENT_ID=22  # 实际执行的SQL语句。DIGEST: 54f0cbe2356984985c1af5f13f557284349025f640ed3c8b4b959f2b62a6e60a  # SQL语句的摘要（一种哈希值）。DIGEST_TEXT: SELECT * FROM `events_transactions_history` WHERE `EVENT_ID` = ?  # 摘要的文本表示，使用了占位符。CURRENT_SCHEMA: performance_schema  # 当前使用的数据库模式（schema）。OBJECT_TYPE: NULL  # 事件关联的对象类型（如果有的话），这里为空。OBJECT_SCHEMA: NULL  # 事件关联对象的模式（schema），这里为空。OBJECT_NAME: NULL  # 事件关联对象的名称，这里为空。OBJECT_INSTANCE_BEGIN: NULL  # 事件关联对象的实例ID（如果有的话），这里为空。MYSQL_ERRNO: 0  # MySQL错误代码，0表示没有错误。RETURNED_SQLSTATE: NULL  # SQLSTATE错误代码，NULL表示没有错误。MESSAGE_TEXT: NULL  # 错误或警告的消息文本，这里为空。ERRORS: 0  # 发生的错误数量。WARNINGS: 0  # 发生的警告数量。ROWS_AFFECTED: 0  # 被语句影响的行数（对于SELECT语句，通常为0）。ROWS_SENT: 0  # 发送给客户端的行数（对于SELECT语句，这通常与返回的行数相关）。ROWS_EXAMINED: 22  # 语句执行过程中检查（读取）的行数。
CREATED_TMP_DISK_TABLES: 0  # 创建的临时磁盘表数量。CREATED_TMP_TABLES: 0  # 创建的临时表数量（包括内存表和磁盘表）。SELECT_FULL_JOIN: 0  # 执行全连接操作的次数。SELECT_FULL_RANGE_JOIN: 0  # 使用全范围扫描的连接次数。SELECT_RANGE: 0  # 使用范围扫描的次数。SELECT_RANGE_CHECK: 0  # 范围检查的次数。SELECT_SCAN: 1  # 全表扫描的次数。SORT_MERGE_PASSES: 0  # 排序合并传递的次数。SORT_RANGE: 0  # 使用范围排序的次数。SORT_ROWS: 0  # 排序的行数。SORT_SCAN: 0  # 使用扫描进行排序的次数。NO_INDEX_USED: 1  # 没有使用索引的情况。NO_GOOD_INDEX_USED: 0  # 没有使用良好索引的情况（基于优化器的判断）。NESTING_EVENT_ID: NULL  # 嵌套事件的ID，这里为空表示没有嵌套事件。NESTING_EVENT_TYPE: NULL  # 嵌套事件的类型，这里为空。NESTING_EVENT_LEVEL: 0  # 嵌套事件的级别，0表示没有嵌套。STATEMENT_ID: 342  # 语句的ID，用于标识该语句。

表2列出了可以用于标识需要优化查询的指标的列。用了4列只是为了查询结果排序。重要性这一列表示该指标的重要程度

​ 表2：event statement history表中可以作为优化指标的列

列描述重要性
CREATED_TMP_DISK_TABLES查询创建的磁盘临时表的数量。有两个选项可以解决这个问题:优化查询或增加内存临时表的最大大小高
CREATED_TMP_TABLES查询创建的内存临时表的数量。使用内存中临时表本身并不是坏事。但是，如果基础表数量增加，超过了内存临时表的空间，可能会转换为磁盘临时表。最好能提前为这种情况做好准备中
SELECT_FULL_ JOIN因为没有合适的索引，所以导致JOIN执高行了全表扫描。除非表非常小，否则需要重新考虑索引的设计高
SELECT_FULL_RANGE_JOINJOIN 操作是否使用了被引用表的范围搜索中
SELECT_RANGEJOIN操作是否使用了范围搜索来解析第一个表中的行低
SELECT_RANGE_CHECK如果JOIN操作没有索引，则会检查每行之后的键。如果该值大于零，则需要重新考虑表中的索引设计高
SELECT_SCANJOIN操作是否对第一个表执行了全表扫描，如果第一个表很大则会是一个问题中
SORT_MERGE_PASSES排序必须执行的合并过程数。如果该值大于零且查询性能较低，则可能需要增加sort_buffer_size的值。低
SORT_RANGE是否使用的是范围排序。低
SORT_ROWS排序的行数。如果排序的行数比返回的行数多，则可能需要优化查询。中
SORT_SCAN排序是否是通过扫描表完成的。除非有意选择表中的所有行而不使用索引。高
NO_INDEX_USED查询没有使用索引。高值表示可能存在问题，除非表非常小。高
NO_GOOD_INDEX_USED查询所用的索引不是最合适的。如果该值大于零，则需要重新考虑表的索引设计。高

要找出哪些语句需要优化，可以选择上述列中的任何一列，并将其与0进行比较。

单个查询语句

SELECT THREAD_ID, SQL_TEXT, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, 
NO_INDEX_USED, NO_GOOD_INDEX_USED  FROM performance_schema.events_statements_history_long  
WHERE NO_INDEX_USED > 0 OR NO_GOOD_INDEX_USED > 0;

具体解释如下：

-- 从performance_schema数据库的events_statements_history_long表中查询数据
SELECT THREAD_ID,          -- 线程ID，代表执行SQL语句的数据库线程SQL_TEXT,           -- SQL语句的文本内容，即实际执行的SQL查询ROWS_SENT,          -- 查询返回给客户端的行数ROWS_EXAMINED,      -- 查询执行过程中检查（读取）的行数，可能包括未返回给客户端的行CREATED_TMP_TABLES, -- 查询过程中创建的临时表的数量NO_INDEX_USED,      -- 指示查询是否未使用索引（1表示是，0表示否）NO_GOOD_INDEX_USED  -- 指示查询是否未使用合适的索引（1表示是，0表示否，或者查询使用了索引但不是最佳选择）
FROM performance_schema.events_statements_history_long  -- 数据源表，存储历史SQL语句执行事件的信息
WHERE NO_INDEX_USED > 0    -- 筛选条件，查询未使用索引的事件OR NO_GOOD_INDEX_USED > 0;  -- 或者查询未使用合适索引的事件

查询所有有问题的语句

mysql> SELECT ->     THREAD_ID,->     SQL_TEXT,->     ROWS_EXAMINED,->     ROWS_SENT,->     ROWS_AFFECTED,->     ERRORS,->     CREATED_TMP_DISK_TABLES,->     CREATED_TMP_TABLES,->     SELECT_FULL_JOIN,->     SELECT_FULL_RANGE_JOIN,->     SELECT_RANGE,->     SELECT_RANGE_CHECK,->     SELECT_SCAN,->     SORT_MERGE_PASSES,->     SORT_RANGE,->     SORT_ROWS,->     NO_INDEX_USED,->     NO_GOOD_INDEX_USED -> FROM ->     performance_schema.events_statements_history_long-> WHERE ->     (ROWS_EXAMINED > ROWS_SENT) OR->     (ROWS_EXAMINED > ROWS_AFFECTED) OR->     (ERRORS > 0) OR->     (CREATED_TMP_DISK_TABLES > 0) OR->     (CREATED_TMP_TABLES > 0) OR->     (SELECT_FULL_JOIN > 0) OR->     (SELECT_FULL_RANGE_JOIN > 0) OR->     (SELECT_RANGE > 0) OR->     (SELECT_RANGE_CHECK > 0) OR->     (SELECT_SCAN IS NOT NULL AND SELECT_SCAN > 0) OR->     (SORT_MERGE_PASSES > 0) OR->     (SORT_RANGE > 0) OR->     (SORT_ROWS > 0) OR->     (NO_INDEX_USED > 0) OR->     (NO_GOOD_INDEX_USED > 0);

使用sys schema查询

sys schema提供了可用于查找有问题语句的视图

  • statements_with_errors_or_warnings列出了带有错误和警告的所有语句
  • statements_with_full_table_scans列出了需要全表扫描的所有语句

例如：

use sys
select * from statements_with_errors_or_warnings \G

在这里插入图片描述

更多查询优化语句的视图表如表3所示

​ 表3：可用于查找需要优化的语句的视图

视图描述
statement_analysis具有聚合统计信息的规范化语句视图，按每个规范化语句的总执行时间排序。类似于events_statements，但summary_by_digest表没有那么详细
statements_with_errors_or_warnings所有引起错误或警告的规范化语句
statements_with_full_table_scans所有执行了全表扫描的规范化语句
statements_with_runtimes_in_95th_percentile所有平均执行时间在前95%的规范化语句（注意：此条在图片文字文本中略有出入，但根据上下文理解为此意）
statements_with_sorting所有执行了排序的规范化语句。该视图包括各种类型的排序
statements_with_temp_tables所有使用了临时表的规范化语句

预处理语句

这一段引用的高性能MySQL第4版

prepared_statements_instances表包含服务器中存在的所有预处理语句。它和events_statements_[current|history|history_long]表有相同的统计数据，此外还有关于预处理语句所属的线程以及该语句被执行了多少次的信息。和events_statements_[current|history|history_long]表不同的是，统计数据是累加的，这个表包含所有语句执行的总量。

要启用预处理语句检测，需要启用表3-6所示的插桩。

​ 表4：启用预处理语句检测的插桩

dba39c4487eb46c5f.png" alt="在这里插入图片描述" />

一旦启用预处理语句功能，一个预处理好的语句就可以多次执行：

在这里插入图片描述

然后查看诊断结果：
在这里插入图片描述

注意，只有在服务器中存在的预处理语句才能在prepared_statements_instances表中查到统计信息。一旦预处理语句被删除，就不能再访问这些统计信息了：

存储例程

使用performance_schema可以检索有关存储例程如何执行的信息：例如，IF…ELSE流控制语句的哪个分支被选择了，或者是否调用了错误处理程序。

要启用存储例程检测，需要启用匹配'statement/sp/%'模式的插桩。statement/sp/stmt插桩负责例程内部调用的语句，而其他插桩则负责跟踪事件，例如进入或离开过程、循环或任何其他控制指令。

例如：

-- 定义存储过程的创建者为 'root'@'localhost'
CREATE DEFINER='root'@'localhost' PROCEDURE `sp_test`(val int)
BEGIN-- 声明一个继续处理程序，用于处理特定的错误代码DECLARE CONTINUE HANDLER FOR 1364, 1048, 1366BEGIN-- 尝试向表 t1 插入一个字符串值，如果违反唯一性约束则忽略错误INSERT IGNORE INTO t1 VALUES('Some string');-- 获取堆栈诊断信息，将返回的SQL状态码存储在变量 @stacked_state 中GET STACKED DIAGNOSTICS CONDITION 1 @stacked_state = RETURNED_SQLSTATE;-- 获取堆栈诊断信息，将错误消息文本存储在变量 @stacked_msg 中GET STACKED DIAGNOSTICS CONDITION 1 @stacked_msg = MESSAGE_TEXT;END;-- 向表 t1 插入传入的参数 val 的值INSERT INTO t1 VALUES(val);
END

接下来使用不同的值来调用以上存储过程

mysql> CALL sp_test(1);mysql> SELECT ->     THREAD_ID,         ->     EVENT_NAME,    ->     SQL_TEXT              -> FROM events_statements_history WHERE -> EVENT_NAME LIKE 'statement/sp%';

在这里插入图片描述

在本例中，没有调用错误处理程序，过程将参数值(1)插入表中

mysql> CALL sp_test(null);mysql> SELECT ->     THREAD_ID,         ->     EVENT_NAME,    ->     SQL_TEXT              -> FROM events_statements_history WHERE -> EVENT_NAME LIKE 'statement/sp%';

语句剖析

events_stages_[current|history|history_long]表包含剖析信息，例如MySQL在创建临时表、更新或等待锁时花费了多少时间。要启用剖析，需要启用上述消费者表以及匹配’stage/%'模式的插桩。

例如：

mysql> SELECT ->     eshl.event_name,                  ->     esthl.sql_text,                   ->     eshl.timer_wait/1000000000 AS w_s -> FROM ->     performance_schema.events_stages_history_long eshl -> JOIN ->     performance_schema.events_statements_history_long esthl -> ON ->     eshl.nesting_event_id = esthl.event_id -> WHERE ->     eshl.timer_wait > 1*1000000000;

在这里插入图片描述

使用events_stages_[current|history|history_long]表的另一种技术是注意那些在已知阶段中花费了超过某个阈值的语句，这些语句会导致性能问题。表4列出了这些阶段。

​ 表4：代表不同性能问题的阶段

阶段类描述
stage/sql/%tmp%所有与临时表相关的内容
stage/sql/%lock%所有与锁相关的内容
stage/%/waitingfor%所有与等待资源相关的内容
stage/sql/Sendingdata这个阶段应该与语句统计中的ROWSSENT进行比较。如果ROWSSENT很小，那么在这个阶段花费大量时间的语句可能意味着必须创建一个临时文件或表来解析中间结果。在向客户端发送数据之前，通常会对行进行过滤。这通常是查询优化不良的症状
stage/sql/freeingitems这些是释放资源的阶段。不幸的是，它们不够详细
stage/sql/cleaningup阶段都包含不只一个任务。如果发现查询在这些阶段花费了很长时间，那么很可能由于高并发性而遇到资源争用
stage/sql/closingtables你需要检查CPU、IO和内存使用情况，以及硬件和MySQL选项设置是否足够处理应用程序创建的并发性
stage/sql/end结束阶段是确保正确处理应用程序和数据库之间的并发性

由于时间原因，等晚上或者是明天再把剩余的解析完，谢谢！


http://www.ppmy.cn/news/1558216.html

相关文章

WebRTC服务质量（10）- Pacer机制（02) RoundRobinPacketQueue

WebRTC服务质量（10）- Pacer机制（02) RoundRobinPacketQueue

WebRTC服务质量（01）- Qos概述 WebRTC服务质量（02）- RTP协议 WebRTC服务质量（03）- RTCP协议 WebRTC服务质量（04）- 重传机制（01) RTX NACK概述 WebRTC服务质量（…
阅读更多...
MySQL最左匹配原则是什么

MySQL最左匹配原则是什么

MySQL 索引的最左匹配原则是指在使用联合索引的过程中，查询条件必须从索引的最左侧开始匹配。如果联合索引包含多个列，查询条件必须包含第一个列，然后是第二个列，以此类推。 底层原理：联合索局部有序。比如建立联合索引…
阅读更多...
scala中的正则表达式案例

scala中的正则表达式案例

一、使用正则表达式验证合法用户名 package test // 验证用户名是否合法 // 规则： // 1.长度在6-12之间 // 2.不能数字开头 // 3.只能包含数字1，大小写字母，下划线 object test {def main(args: Array[String]): Unit {val name1 "1ad…
阅读更多...
HarmonyOS NEXT 实战之元服务：静态案例效果---歌单推荐

HarmonyOS NEXT 实战之元服务：静态案例效果---歌单推荐

背景： 前几篇学习了元服务，后面几期就让我们开发简单的元服务吧，里面丰富的内容大家自己加，本期案例 仅供参考 先上本期效果图 ，里面图片自行替换 效果图1完整代码案例如下： Index import { authentica…
阅读更多...
C# 语法糖集锦

C# 语法糖集锦

文章目录 1、自动属性（Auto - Properties）2、对象和集合初始化器（Object and Collection Initializers）3、匿名类型（Anonymous Types）4、扩展方法（Extension Methods）5、Lambda 表达式（Lambda Expressions）6、空合并运算符（??）和空条件运算符（?.）7、隐式类型数…
阅读更多...
C++ 指针基础：开启内存操控之门

C++ 指针基础：开启内存操控之门

1. 指针为何如此重要 在 C 编程领域，指针堪称一项极为关键的特性。它赋予了程序员直接访问和操控内存的能力，这使得程序在处理复杂数据结构与优化性能时具有更高的灵活性。想象一下，在编写大型程序时，高效地管理内存资源是多么重要…
阅读更多...
mac中idea中英文版本切换

mac中idea中英文版本切换

1.快捷键command，或者 2.找到插件（plugins），搜索chinese，安装 第一次安装好点击应用会提示重启，直接重启就好了，如果已经安装过，只需要切换中英文 3.中英文切换 外观与行为&#xf…
阅读更多...
Halcon 的标定

Halcon 的标定

*窗口坐标 宽度 dev_update_window(off)Halcon winw:=10000 winh:=10000 dev_open_window(0, 0, 1000, 1000, black, WindowHandle) dev_set_part(0,0,winw,winh) set_system (clip_region, false) dev_clear_window() dev_set_draw (margin) gen_region_line(xaxis, 0, 0, …
阅读更多...
最新文章

Copyright @ 2022~2023