MySQL 中的语句摘要
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-digests.html
解析器也由 and 函数使用，应用程序可以调用这些函数来分别从 SQL 语句计算规范化语句摘要和摘要哈希值。STATEMENT_DIGEST_TEXT() 、STATEMENT_DIGEST()
性能模式中的events_statement_summary_by_digest包含信息
events_statement_summary_by_digest in Performance Schema contains information
+-----------------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+-----------------+------+-----+---------+-------+
| SCHEMA_NAME | varchar(64) | YES | MUL | NULL | |
| DIGEST | varchar(64) | YES | | NULL | |
| DIGEST_TEXT | longtext | YES | | NULL | |
| COUNT_STAR | bigint unsigned | NO | | NULL | |
| SUM_TIMER_WAIT | bigint unsigned | NO | | NULL | |
| MIN_TIMER_WAIT | bigint unsigned | NO | | NULL | |
| AVG_TIMER_WAIT | bigint unsigned | NO | | NULL | |
| MAX_TIMER_WAIT | bigint unsigned | NO | | NULL | |
| SUM_LOCK_TIME | bigint unsigned | NO | | NULL | |
| SUM_ERRORS | bigint unsigned | NO | | NULL | |
| SUM_WARNINGS | bigint unsigned | NO | | NULL | |
| SUM_ROWS_AFFECTED | bigint unsigned | NO | | NULL | |
| SUM_ROWS_SENT | bigint unsigned | NO | | NULL | |
| SUM_ROWS_EXAMINED | bigint unsigned | NO | | NULL | |
| SUM_CREATED_TMP_DISK_TABLES | bigint unsigned | NO | | NULL | |
| SUM_CREATED_TMP_TABLES | bigint unsigned | NO | | NULL | |
| SUM_SELECT_FULL_JOIN | bigint unsigned | NO | | NULL | |
| SUM_SELECT_FULL_RANGE_JOIN | bigint unsigned | NO | | NULL | |
| SUM_SELECT_RANGE | bigint unsigned | NO | | NULL | |
| SUM_SELECT_RANGE_CHECK | bigint unsigned | NO | | NULL | |
| SUM_SELECT_SCAN | bigint unsigned | NO | | NULL | |
| SUM_SORT_MERGE_PASSES | bigint unsigned | NO | | NULL | |
| SUM_SORT_RANGE | bigint unsigned | NO | | NULL | |
| SUM_SORT_ROWS | bigint unsigned | NO | | NULL | |
| SUM_SORT_SCAN | bigint unsigned | NO | | NULL | |
| SUM_NO_INDEX_USED | bigint unsigned | NO | | NULL | |
| SUM_NO_GOOD_INDEX_USED | bigint unsigned | NO | | NULL | |
| SUM_CPU_TIME | bigint unsigned | NO | | NULL | |
| COUNT_SECONDARY | bigint unsigned | NO | | NULL | |
| FIRST_SEEN | timestamp(6) | NO | | NULL | |
| LAST_SEEN | timestamp(6) | NO | | NULL | |
| QUANTILE_95 | bigint unsigned | NO | | NULL | |
| QUANTILE_99 | bigint unsigned | NO | | NULL | |
| QUANTILE_999 | bigint unsigned | NO | | NULL | |
| QUERY_SAMPLE_TEXT | longtext | YES | | NULL | |
| QUERY_SAMPLE_SEEN | timestamp(6) | NO | | NULL | |
| QUERY_SAMPLE_TIMER_WAIT | bigint unsigned | NO | | NULL | |
+-----------------------------+-----------------+------+-----+---------+-------+复制events_statements_history_long[events_statement_current / events_statement_history] 包含有关 SQL 语句和摘要的信息以及执行时间。通过引用 events_statement_history_long，我们可以跟踪执行时间与表“events_statements_summary_by_digest”中的平均执行时间相比。
1. 开启“performance_schema”收集events_statements_history_long，我们需要开启consumer（对于*history_long）。默认值为OFF，即events_statements_history_long为空，没有任何数据。开启events_statements_history_long可以捕获更多的SQL语句，与平均执行时间相比。
mysql > 更新 setup_consumers 设置启用 ='YES' 其中名称 = 'events_statements_history_long';
2. 检索摘要相同且执行时间比平均时间多 30% 的 SQL 语句列表。
mysql >选择 a.sql_text, a.digest, (a.timer_end - a.timer_start) 持续时间, b.avg_timer_wait
> 来自 events_statements_history_long a，events_statements_summary_by_digest b
> 其中 a.digest = b.digest 和 b.avg_timer_wait > (a.timer_end - a.timer_start) *1.3
性能架构/系统架构包含有价值的信息，我们可以使用它们来跟踪/调整我们的系统。它可以是 SQL。它可以是存储或文件。
您可以与其他人分享更多关于如何重用 performance_schema 表来改进您的应用程序的信息。
原文标题：MySQL Performance Schema to identify SQL Statements which the execution duration exceeds average timing by 30%
原文作者：Ivan Ma
原文链接：https://mysqlhk.blogspot.com/2022/10/identify-sql-statements-which-execution.html
