MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1:sys.statement_analysis视图

news/2024/10/30 15:26:35/

文章目录

    • MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1:sys.statement_analysis视图
      • 视图sys.statement_analysis各列定义
      • 视图sys.statement_analysis视图的定义
      • 视图sys.statement_analysis各列解释
      • 例题
      • 例题解析
      • 参考

MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1:sys.statement_analysis视图

通过系统性能视图sys.statement_analysis可以查看规范化后SQL语句的汇总统计信息。相当于MySQL Enterprise Monitor的“查询分析”视图。默认情况下,输出结果按总等待时间(max_latency)降序排序。

视图sys.statement_analysis各列定义

视图sys.statement_analysis的各列定义如下:

mysql> desc sys.statement_analysis;
+-----------------------+-----------------+------+-----+---------+-------+
| Field                 | Type            | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| query                 | longtext        | YES  |     | NULL    |       |
| db                    | varchar(64)     | YES  |     | NULL    |       |
| full_scan             | varchar(1)      | NO   |     |         |       |
| exec_count            | bigint unsigned | NO   |     | NULL    |       |
| err_count             | bigint unsigned | NO   |     | NULL    |       |
| warn_count            | bigint unsigned | NO   |     | NULL    |       |
| total_latency         | varchar(11)     | YES  |     | NULL    |       |
| max_latency           | varchar(11)     | YES  |     | NULL    |       |
| avg_latency           | varchar(11)     | YES  |     | NULL    |       |
| lock_latency          | varchar(11)     | YES  |     | NULL    |       |
| cpu_latency           | varchar(11)     | YES  |     | NULL    |       |
| rows_sent             | bigint unsigned | NO   |     | NULL    |       |
| rows_sent_avg         | decimal(21,0)   | NO   |     | 0       |       |
| rows_examined         | bigint unsigned | NO   |     | NULL    |       |
| rows_examined_avg     | decimal(21,0)   | NO   |     | 0       |       |
| rows_affected         | bigint unsigned | NO   |     | NULL    |       |
| rows_affected_avg     | decimal(21,0)   | NO   |     | 0       |       |
| tmp_tables            | bigint unsigned | NO   |     | NULL    |       |
| tmp_disk_tables       | bigint unsigned | NO   |     | NULL    |       |
| rows_sorted           | bigint unsigned | NO   |     | NULL    |       |
| sort_merge_passes     | bigint unsigned | NO   |     | NULL    |       |
| max_controlled_memory | varchar(11)     | YES  |     | NULL    |       |
| max_total_memory      | varchar(11)     | YES  |     | NULL    |       |
| digest                | varchar(64)     | YES  |     | NULL    |       |
| first_seen            | timestamp(6)    | NO   |     | NULL    |       |
| last_seen             | timestamp(6)    | NO   |     | NULL    |       |
+-----------------------+-----------------+------+-----+---------+-------+
26 rows in set (0.00 sec)

视图sys.statement_analysis视图的定义

根据视图sys.statement_analysis的定义,可以看到数据主要是通过performance_schema.events_statements_summary_by_digest表取得的。

视图的定义如下:

mysql> show create view statement_analysis\G
*************************** 1. row ***************************View: statement_analysisCreate View: 
CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `statement_analysis` (`query`,`db`,`full_scan`,`exec_count`,`err_count`,`warn_count`,`total_latency`,`max_latency`,`avg_latency`,`lock_latency`,`cpu_latency`,`rows_sent`,`rows_sent_avg`,`rows_examined`,`rows_examined_avg`,`rows_affected`,`rows_affected_avg`,`tmp_tables`,`tmp_disk_tables`,`rows_sorted`,`sort_merge_passes`,`max_controlled_memory`,`max_total_memory`,`digest`,`first_seen`,`last_seen`
)ASSELECT`sys`.`format_statement`(`performance_schema`.`events_statements_summary_by_digest`.`digest_text`)                                                                                              AS`query`,`performance_schema`.`events_statements_summary_by_digest`.`schema_name`                                                                                                                        AS`db`,if(((`performance_schema`.`events_statements_summary_by_digest`.`sum_no_good_index_used` > 0) OR(`performance_schema`.`events_statements_summary_by_digest`.`sum_no_index_used` > 0)), '*', '') AS `full_scan`,`performance_schema`.`events_statements_summary_by_digest`.`count_star`                                                                                                                         AS`exec_count`,`performance_schema`.`events_statements_summary_by_digest`.`sum_errors`                                                                                                                         AS`err_count`,`performance_schema`.`events_statements_summary_by_digest`.`sum_warnings`                                                                                                                       AS`warn_count`,format_pico_time(`performance_schema`.`events_statements_summary_by_digest`.`sum_timer_wait`)                                                                                                   AS`total_latency`,format_pico_time(`performance_schema`.`events_statements_summary_by_digest`.`max_timer_wait`)                                                                                                   AS`max_latency`,format_pico_time(`performance_schema`.`events_statements_summary_by_digest`.`avg_timer_wait`)                                                                                                   AS`avg_latency`,format_pico_time(`performance_schema`.`events_statements_summary_by_digest`.`sum_lock_time`)                                                                                                    AS`lock_latency`,format_pico_time(`performance_schema`.`events_statements_summary_by_digest`.`sum_cpu_time`)                                                                                                     AS`cpu_latency`,`performance_schema`.`events_statements_summary_by_digest`.`sum_rows_sent`                                                                                                                      AS`rows_sent`,round(ifnull((`performance_schema`.`events_statements_summary_by_digest`.`sum_rows_sent` / nullif(`performance_schema`.`events_statements_summary_by_digest`.`count_star`, 0)), 0), 0)          AS `rows_sent_avg`,`performance_schema`.`events_statements_summary_by_digest`.`sum_rows_examined`                                                                                                                  AS`rows_examined`,round(ifnull((`performance_schema`.`events_statements_summary_by_digest`.`sum_rows_examined` / nullif(`performance_schema`.`events_statements_summary_by_digest`.`count_star`, 0)), 0), 0)      AS `rows_examined_avg`,`performance_schema`.`events_statements_summary_by_digest`.`sum_rows_affected`                                                                                                                  AS`rows_affected`,round(ifnull((`performance_schema`.`events_statements_summary_by_digest`.`sum_rows_affected` / nullif(`performance_schema`.`events_statements_summary_by_digest`.`count_star`, 0)), 0), 0)      AS `rows_affected_avg`,`performance_schema`.`events_statements_summary_by_digest`.`sum_created_tmp_tables`                                                                                                             AS`tmp_tables`,`performance_schema`.`events_statements_summary_by_digest`.`sum_created_tmp_disk_tables`                                                                                                        AS`tmp_disk_tables`,`performance_schema`.`events_statements_summary_by_digest`.`sum_sort_rows`                                                                                                                      AS`rows_sorted`,`performance_schema`.`events_statements_summary_by_digest`.`sum_sort_merge_passes`                                                                                                              AS`sort_merge_passes`,format_bytes(`performance_schema`.`events_statements_summary_by_digest`.`max_controlled_memory`)                                                                                                AS`max_controlled_memory`,format_bytes(`performance_schema`.`events_statements_summary_by_digest`.`max_total_memory`)                                                                                                     AS`max_total_memory`,`performance_schema`.`events_statements_summary_by_digest`.`digest`                                                                                                                             AS`digest`,`performance_schema`.`events_statements_summary_by_digest`.`first_seen`                                                                                                                         AS`first_seen`,`performance_schema`.`events_statements_summary_by_digest`.`last_seen`                                                                                                                          AS`last_seen`FROM`performance_schema`.`events_statements_summary_by_digest`ORDER BY`performance_schema`.`events_statements_summary_by_digest`.`sum_timer_wait` DESC
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

视图sys.statement_analysis各列解释

各列的含义如下:

列名英文解释中文解释
queryThe normalized statement string规范化的语句字符串
dbThe default database for the statement, or NULL if there is none语句的默认数据库,如果没有,则为NULL
full_scanThe total number of full table scans performed by occurrences of the statement执行的全表扫描的总数
exec_countThe total number of times the statement has executed语句执行的总次数
err_countThe total number of errors produced by occurrences of the statement语句执行所产生的错误总数。
warn_countThe total number of warnings produced by occurrences of the statement语句执行所产生的警告总数。
total_latencyThe total wait time of timed occurrences of the statement语句执行总等待时间。
max_latencyThe maximum single wait time of timed occurrences of the statement语句执行最大单次等待时间。
avg_latencyThe average wait time per timed occurrence of the statement语句执行平均等待时间。
lock_latencyThe total time waiting for locks by timed occurrences of the statement语句执行总锁等待时间。
cpu_latencyThe time spent on CPU for the current thread当前线程在cpu上花费的时间
rows_sentThe total number of rows returned by occurrences of the statement语句执行所返回的总行数
rows_sent_avgThe average number of rows returned per occurrence of the statement语句单次执行平均返回的行数
rows_examinedThe total number of rows read from storage engines by occurrences of the statement语句执行从存储引擎读取的总行数
rows_examined_avgThe average number of rows read from storage engines per occurrence of the statement语句单次执行从存储引擎读取的平均行数
rows_affectedThe total number of rows affected by occurrences of the statement语句执行影响的总行数
rows_affected_avgThe average number of rows affected per occurrence of the statement语句单次执行影响的平均行数
tmp_tablesThe total number of internal in-memory temporary tables created by occurrences of the statement语句执行创建的内存中内部临时表的总数
tmp_disk_tablesThe total number of internal on-disk temporary tables created by occurrences of the statement语句执行创建的磁盘上内部临时表的总数
rows_sortedThe total number of rows sorted by occurrences of the statement语句执行排序的总行数
sort_merge_passesThe total number of sort merge passes by occurrences of the statement语句执行排序合并传递总数.
max_controlled_memoryThe maximum amount of controlled memory (bytes) used by the statement 。This column was added in MySQL 8.0.31语句使用的最大受控内存量(字节),在MySQL 8.0.31中添加
max_total_memoryThe maximum amount of memory (bytes) used by the statement。This column was added in MySQL 8.0.31语句使用的最大内存量(字节),在MySQL 8.0.31中添加
digestThe statement digest语句摘要
first_seenThe time at which the statement was first seen语句的最初时间
last_seenThe time at which the statement was most recently seen语句的最近时间

例题

Choose two.
Examine this statement and output:mysql> SELECT ROW_NUMBER() OVER() AS QN,
query, exec_count, avg_latency, lock_latency
FROM sys.statement_analysis
ORDER BY exec_count;

在这里插入图片描述

You must try to reduce query execution time.
Which two queries should you focus on?A) QN=2
B) QN=3
C) QN=4
D) QN=1
E) QN=5

例题解析

根据题目我们要减少查询的执行时间。
实践中在调优的过程中,我们通常找的是总执行时间最长或者单次执行时间最长的SQL。
对于题目而言,我们要关注总等待时间,查询的总等待时间为exec_count * avg_latency ,lock等待时间为 lock_latency。

1 秒(s)=1000 毫秒(ms)
1 毫秒(ms)=1000 微秒(us)

各查询总等待时间:

QN=1  381268 * 31.44          =11987065.92 ms
QN=2  150317 * 358.34        =53864593.78 us = 53864.59378 ms
QN=3  600 * 523.32             = 313992 ms
QN=4  200 * 10.32 = 2064 s  = 2064000 ms
QN=5  1* 21.03  = 21.03 s    = 21030 ms 

所以,我们可以看到总等待时间最长是QN=1和QN=4 。

参考答案: CD

参考

https://dev.mysql.com/doc/refman/8.0/en/sys-statement-analysis.html


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

相关文章

Java实现输入行数打印取缔字符,打印金字塔三角形的两个代码程序

目录 前言 一、实现输入行数,打印取缔字符 1.1运行流程(思想) 1.2代码段 1.3运行截图 二、打印金字塔三角形 1.1运行流程(思想) 1.2代码段 1.3运行截图​​​​​​​ 前言 1.因多重原因,本博文有…

Linux基础篇(四)打包和解压

目录 一、打包和压缩 二、zip 和 unzip 三、tar指令 一、打包和压缩 1.是什么? 打包:将东西放到一起。 压缩:采用某种压缩算法,压缩它的存储空间。 2.为什么? 便于传输,或者归档&a…

丝滑的打包部署,一套带走~

以下文章来源于悟空聊架构 ,作者悟空聊架构 本文主要内容如下: 目录 一、背景 Docker打包部署方案 项目背景:新项目的后端框架是刚起步,搭建的是一套微服务框架,基础服务有网关 Gateway, Nacos 注册中心…

免费ChatGPT接入-国内怎么玩chatGPT

免费ChatGPT中文版 OpenAI 的 GPT 模型目前并不提供中文版的免费使用,但是有许多机器学习平台和第三方服务提供商也提供了基于 GPT 技术的中文版模型和 API。下面是一些常见的免费中文版 ChatGPT: Hugging Face:Hugging Face 是一个开源社区…

一文解决ethtool 原理介绍和解决网卡丢包排查思路

前言 之前记录过处理因为 LVS 网卡流量负载过高导致软中断发生丢包的问题,RPS 和 RFS 网卡多队列性能调优实践[1],对一般人来说压力不大的情况下其实碰见的概率并不高。这次想分享的话题是比较常见服务器网卡丢包现象排查思路,如果你是想了解…

这篇文章价值很大:股票历史分时成交数据怎么简单获取?【干货】

文章目录前言一、准备二、使用步骤1.引入库2,使用这个API查询历史分时数据:3.查询完整历史分时数据4.其他查询方法参数格式:[(市场代码, 股票代码), ...]参数:市场代码, 股票代码, 文件名, 起始位置, 数量参数:市场代码…

MVCC底层原理

目录说明MVCC的底层原理隐藏字段undo logRead View说明 在被面试官问面试题的时候,首先它问了Mysql的事务的隔离级别有几种?默认是哪种?他们分别解决了什么问题? 我在一顿回答“巴巴巴巴。。。。”之后,它又继续问题…

长江流域9省2市可视化(不展示业务信息水质及真实断面)

一、处理9省2市地理信息为geojson集成到项目 shp转geojson关键Java代码 /*** shp转换为Geojson* param shpPath* return*/ public static Map shape2Geojson(String shpPath,String filePath){Map map new HashMap();FeatureJSON fjson new FeatureJSON();try{StringBuffer …