MySQL5.7默认自带的存储过程

最新推荐文章于 2024-05-20 13:15:00 发布

十五十六

最新推荐文章于 2024-05-20 13:15:00 发布

阅读量1.9k

点赞数 1

分类专栏：数据库文章标签： mysql 性能排查

本文链接：https://blog.csdn.net/L835311324/article/details/105308684

版权

数据库专栏收录该内容

56 篇文章 4 订阅

订阅专栏

简介

MySQL自带了许多的存储过程，用于性能排查，以及日常管理，可以通过下面的sql进行查看

SELECT 
    name, specific_name, comment
FROM
    mysql.proc
WHERE
    type = 'PROCEDURE';

`create_synonym_db`

创建同义词schema指向目标schema

创建一个同名称的库，最有用的就是创建一个 performance_schema 的同义词 ps，或者is代替information_schema，用法如下

mysql>  CALL sys.create_synonym_db('performance_schema', 'ps');
+---------------------------------------+
| summary                               |
+---------------------------------------+
| Created 87 views in the `ps` database |
+---------------------------------------+
1 row in set (0.16 sec)

Query OK, 0 rows affected (0.16 sec)

mysql> CALL sys.create_synonym_db('information_schema', 'is');
+---------------------------------------+
| summary                               |
+---------------------------------------+
| Created 61 views in the `is` database |
+---------------------------------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| is                 |
| mysql              |
| performance_schema |
| ps                 |
| sbtest             |
| sys                |
| t1                 |
| t2                 |
+--------------------+
9 rows in set (0.00 sec)

`execute_prepared_stmt`

将 SQL 语句指定为 string，将其作为预准备语句执行。准备好的语句在执行后被释放，因此不需要重用。因此，此过程主要用于在 one-time 基础上执行动态语句。

此过程使用sys_execute_prepared_stmt作为预准备语句 name。如果在调用过程时存在该语句 name，则会销毁其先前的内容。


# 打开sys.debug参数
mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON');

mysql> SET @stmt = 'SELECT user,host FROM mysql.user';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sys.format_statement(@stmt);
+----------------------------------+
| sys.format_statement(@stmt)      |
+----------------------------------+
| SELECT user,host FROM mysql.user |
+----------------------------------+
1 row in set (0.00 sec)

mysql> CALL sys.execute_prepared_stmt(@stmt);
+----------------------------------+
| Debug                            |
+----------------------------------+
| SELECT user,host FROM mysql.user |
+----------------------------------+
1 row in set (0.00 sec)

+---------------+-----------+
| user          | host      |
+---------------+-----------+
| archforce     | %         |
| hal           | %         |
| sbtest        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

`diagnostics`

创建当前服务器状态的报告以进行诊断。

为diagnostics()收集的数据包括以下信息：

来自metrics视图的信息
来自其他相关sys schema 视图的信息，例如确定第 95 百分位数中的查询的信息
来自ndbinfo schema 的信息，如果 MySQL 服务器是 NDB Cluster 的一部分
复制状态(master 和 slave)

某些 sys schema 视图计算为初始(可选)，总体和 delta 值：

初始视图是diagnostics()过程开始时视图的内容。此输出与用于增量视图的起始值相同。如果diagnostics.include_raw configuration 选项为ON，则包含初始视图。
整体视图是diagnostics()过程结束时视图的内容。此输出与用于 delta 视图的结束值相同。始终包含整体视图。
增量视图是从过程执行的开始到结束的差异。最小值和最大值分别是端视图中的最小值和最大值。它们不一定反映监测期间的最小值和最大值。除metrics视图外，仅在第一个和最后一个输出之间计算增量。

参数
in_max_runtime INT UNSIGNED：以秒为单位的最大数据收集时间。使用NULL收集默认值为 60 秒的数据。否则，请使用大于 0 的 value。
in_interval INT UNSIGNED：数据集合之间的 sleep time，以秒为单位。使用NULL睡眠默认值为 30 秒。否则，请使用大于 0 的 value。
in_auto_config ENUM('current', 'medium', 'full')：要使用的 Performance Schema configuration。允许的值是：

current：使用当前的设置。
medium：启用一些设置。
full：启用所有设置。

# 输出到日志diag.out外部文件
mysql> tee diag.out;
# 启动
mysql> CALL sys.diagnostics(120, 30, 'current');
# 取消重定向
mysql> notee;

`ps_statement_avg_latency_histogram`

输出在Performance Schema events_statements_summary_by_digest表中跟踪的所有规范化查询的平均延迟值的文本直方图。

mysql> CALL sys.ps_statement_avg_latency_histogram()\G
*************************** 1. row ***************************
Performance Schema Statement Digest Average Latency Histogram:

  . = 1 unit
  * = 2 units
  # = 3 units

(0 - 5885ms)       82 | ###########################
(5885 - 11770ms)   0  |
(11770 - 17656ms)  0  |
(17656 - 23541ms)  0  |
(23541 - 29426ms)  0  |
(29426 - 35311ms)  0  |
(35311 - 41197ms)  0  |
(41197 - 47082ms)  0  |
(47082 - 52967ms)  0  |
(52967 - 58852ms)  0  |
(58852 - 64738ms)  0  |
(64738 - 70623ms)  0  |
(70623 - 76508ms)  0  |
(76508 - 82393ms)  0  |
(82393 - 88279ms)  0  |
(88279 - 94164ms)  1  | .

  Total Statements: 84; Buckets: 16; Bucket Size: 5885 ms;

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

`ps_trace_thread`

将已检测线程的所有 Performance Schema 数据转储到.dot格式化图形文件(对于 DOT 图形描述语言)。从过程返回的每个结果集都应该用于完整的图形。

此过程通过操作log_bin系统变量的 session value 来禁用二进制 logging。这是一个受限制的操作，因此该过程需要足以设置受限 session 变量的权限。

参数列表：

in_thread_id (BIGINT UNSIGNED): 想要追踪的线程id

in_outfile (VARCHAR(255)):输出的dot文件名,需要yum install graphviz

in_max_runtime (DECIMAL(20,2)):收集数据最大时间，默认60s，使用NULL表示使用默认时间

in_interval (DECIMAL(20,2)): 收集数据的间隔，默认1s

in_start_fresh (BOOLEAN):跟踪之前是否刷新以前的数据

in_auto_setup (BOOLEAN):是否禁用所有其他线程并启用所有仪器和消费者。这也会重置 run 末尾的设置。

in_debug (BOOLEAN):是否在图表中包含file:lineno信息。

示例：

-- 追踪25号线程，输出文件名为/tmp/stack-时间.dot,最大追踪时间为NULL也就是默认60s，间隔时间NULL，默认1s，刷新之前的数据，禁用其他线程并启用所有仪器和消费者
CALL sys.ps_trace_thread(25, CONCAT('/tmp/stack-', REPLACE(NOW(), ' ', '-'), '.dot'), NULL, NULL, TRUE, TRUE, TRUE);

然后在/tmp/目录下就可以找到相关信息

`ps_setup_disable_background_threads`

禁用所有后台线程的 Performance Schema 检测。生成一个结果集，指示禁用了多少后台线程。已经禁用的线程不计算在内。

mysql> CALL sys.ps_setup_disable_background_threads();
+--------------------------------+
| summary                        |
+--------------------------------+
| Disabled 24 background threads |
+--------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

`ps_setup_enable_background_threads`

启用所有后台线程的 Performance Schema 检测。生成一个结果集，体是启用了多少后台线程。已经启用的线程不计算在内。

mysql> CALL sys.ps_setup_enable_background_threads();
+-------------------------------+
| summary                       |
+-------------------------------+
| Enabled 24 background threads |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

`ps_setup_disable_consumer`

关闭Performance Schema中匹配的consumer，也可以关闭所有consumer

具体有那些consumer可以通过SELECT * FROM performance_schema.setup_consumers;查看

mysql> SELECT * FROM performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | NO      |
| events_statements_history        | NO      |
| events_statements_history_long   | NO      |
| events_transactions_current      | NO      |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | NO      |
| thread_instrumentation           | NO      |
| statements_digest                | NO      |
+----------------------------------+---------+
15 rows in set (0.00 sec)

具体consumer示例可以参考<https://www.docs4dev.com/docs/zh/mysql/5.7/reference/performance-schema-consumer-configurations.html>

示例

mysql> CALL sys.ps_setup_disable_consumer('');
+----------------------+
| summary              |
+----------------------+
| Disabled 5 consumers |
+----------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL sys.ps_setup_disable_consumer('stage');
+----------------------+
| summary              |
+----------------------+
| Disabled 0 consumers |
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

`ps_setup_enable_consumer`

启用consumer

示例：

mysql> CALL sys.ps_setup_enable_consumer('stage');
+---------------------+
| summary             |
+---------------------+
| Enabled 3 consumers |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sys.ps_setup_enable_consumer('');
+----------------------+
| summary              |
+----------------------+
| Enabled 12 consumers |
+----------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

`ps_setup_disable_instrument`

关闭Performance Schema中匹配的instrument，也可以关闭所有instrument

具体有那些consumer可以通过SELECT * FROM performance_schema.setup_instruments;

示例

-- 关闭所有mutex instruments
mysql> CALL sys.ps_setup_disable_instrument('wait/synch/mutex');
+------------------------+
| summary                |
+------------------------+
| Disabled 0 instruments |
+------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- 关闭所有instruments('')
mysql> CALL sys.ps_setup_disable_instrument('');
+--------------------------+
| summary                  |
+--------------------------+
| Disabled 327 instruments |
+--------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

`ps_setup_enable_instrument`

启用instrument

mysql> CALL sys.ps_setup_enable_instrument('');
+--------------------------+
| summary                  |
+--------------------------+
| Enabled 1021 instruments |
+--------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

`ps_setup_disable_thread`

给定连接 ID，禁用Performance Schema 检测。

mysql> CALL sys.ps_setup_disable_thread(CONNECTION_ID());
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

`ps_setup_enable_thread`

给定连接 ID，为线程启用 Performance Schema 检测。生成一个结果集，指示启用了多少个线程。已经启用的线程不计算在内。

示例

mysql>  CALL sys.ps_setup_enable_thread(CONNECTION_ID());
+-------------------+
| summary           |
+-------------------+
| Enabled 0 threads |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

`ps_setup_reload_saved`

重新载入一个已保存的Performance Schema配置

示例

 mysql> CALL sys.ps_setup_save(1);
  Query OK, 0 rows affected (0.08 sec)
  
  mysql> UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
  Query OK, 547 rows affected (0.40 sec)
  Rows matched: 784  Changed: 547  Warnings: 0
  
  /* Run some tests that need more detailed instrumentation here */
  
  mysql> CALL sys.ps_setup_reload_saved();
  Query OK, 0 rows affected (0.32 sec)

`ps_setup_reset_to_default`

重置Performance到默认配置

CALL sys.ps_setup_reset_to_default(true);
+--------------------------------------------------------------------------------------------------------------------------+
| status                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| Resetting: setup_actors
DELETE FROM performance_schema.setup_actors WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%') |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+-----------------------------------------------------------------------------------------------------------------+
| status                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------+
| Resetting: setup_actors
INSERT IGNORE INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES') |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

...

`ps_setup_save`

保存当前 Performance Schema configuration。这使您可以临时更改配置以进行调试或其他目的，然后通过调用ps_setup_reload_saved()过程将其还原到上一个 state。

 mysql> CALL sys.ps_setup_save(-1);
  Query OK, 0 rows affected (0.08 sec)
  
  mysql> UPDATE performance_schema.setup_instruments 
  ->    SET enabled = 'YES', timed = 'YES';
  Query OK, 547 rows affected (0.40 sec)
  Rows matched: 784  Changed: 547  Warnings: 0
  
  /* Run some tests that need more detailed instrumentation here */
  
  mysql> CALL sys.ps_setup_reload_saved();
  Query OK, 0 rows affected (0.32 sec)

`ps_setup_show_disabled`

查看所有关闭的Performance Schema 配置

mysql> CALL sys.ps_setup_show_disabled(TRUE, TRUE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.01 sec)

Empty set (0.01 sec)

+-------------+----------------------+---------+-------+
| object_type | objects              | enabled | timed |
+-------------+----------------------+---------+-------+
| EVENT       | information_schema.% | NO      | NO    |
| EVENT       | mysql.%              | NO      | NO    |
| EVENT       | performance_schema.% | NO      | NO    |
| FUNCTION    | information_schema.% | NO      | NO    |
| FUNCTION    | mysql.%              | NO      | NO    |
| FUNCTION    | performance_schema.% | NO      | NO    |
| PROCEDURE   | information_schema.% | NO      | NO    |
| PROCEDURE   | mysql.%              | NO      | NO    |
| PROCEDURE   | performance_schema.% | NO      | NO    |
| TABLE       | information_schema.% | NO      | NO    |
| TABLE       | mysql.%              | NO      | NO    |
| TABLE       | performance_schema.% | NO      | NO    |
| TRIGGER     | information_schema.% | NO      | NO    |
| TRIGGER     | mysql.%              | NO      | NO    |
| TRIGGER     | performance_schema.% | NO      | NO    |
+-------------+----------------------+---------+-------+
15 rows in set (0.01 sec)
...

`ps_setup_show_enabled`

查看所有打开的Performance Schema 配置

mysql> CALL sys.ps_setup_show_enabled(TRUE, TRUE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

+---------------+
| enabled_users |
+---------------+
| '%'@'%'       |
+---------------+
1 row in set (0.00 sec)

+-------------+---------+---------+-------+
| object_type | objects | enabled | timed |
+-------------+---------+---------+-------+
| EVENT       | %.%     | YES     | YES   |
| FUNCTION    | %.%     | YES     | YES   |
| PROCEDURE   | %.%     | YES     | YES   |
| TABLE       | %.%     | YES     | YES   |
| TRIGGER     | %.%     | YES     | YES   |
+-------------+---------+---------+-------+
5 rows in set (0.00 sec)
...

`ps_setup_show_disabled_consumers`

查看所有关闭的consumers

mysql> call sys.ps_setup_show_disabled_consumers;
+----------------------------------+
| disabled_consumers               |
+----------------------------------+
| events_stages_current            |
| events_stages_history            |
| events_stages_history_long       |
| events_statements_history        |
| events_statements_history_long   |
| events_transactions_history      |
| events_transactions_history_long |
| events_waits_current             |
| events_waits_history             |
| events_waits_history_long        |
+----------------------------------+
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

`ps_setup_show_disabled_instruments`

查看所有关闭的instruments

mysql> call sys.ps_setup_show_disabled_instruments;
+----------------------------------------------------------------------------+-------+
| disabled_instruments                                                       | timed |
+----------------------------------------------------------------------------+-------+
| memory/archive/FRM                                                         | NO    |
| memory/archive/record_buffer                                               | NO    |
| memory/blackhole/blackhole_share                                           | NO    |
| memory/client/MYSQL                                                        | NO    |
| memory/client/MYSQL_DATA                                                   | NO    |
| memory/client/MYSQL_HANDSHAKE                                              | NO    |
| memory/client/mysql_options                                                | NO    |
| memory/client/MYSQL_RES                                                    | NO    |
| memory/client/MYSQL_ROW                                                    | NO    |
| memory/client/MYSQL_STATE_CHANGE_INFO                                      | NO    |
| memory/csv/blobroot                                                        | NO    |
| memory/csv/row                                                             | NO    |
| memory/csv/tina_set                                                        | NO    |
| memory/csv/TINA_SHARE                                                      | NO    |
| memory/csv/Transparent_file                                                | NO    |
| memory/innodb/adaptive hash index                                          | NO    |
| memory/innodb/api0api                                                      | NO    |
| memory/innodb/btr0btr                                                      | NO    |
| memory/innodb/btr0bulk                                                     | NO    |
| memory/innodb/btr0cur                                                      | NO    |
| memory/innodb/btr0pcur                                                     | NO    |
| memory/innodb/btr0sea                                                      | NO    |

`ps_setup_show_enabled_instruments`

查看所有打开的instruments

mysql> CALL sys.ps_setup_show_enabled(TRUE, TRUE);
+----------------------------+
| performance_schema_enabled |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

+---------------+
| enabled_users |
+---------------+
| '%'@'%'       |
+---------------+
1 row in set (0.00 sec)

+-------------+---------+---------+-------+
| object_type | objects | enabled | timed |
+-------------+---------+---------+-------+
| EVENT       | %.%     | YES     | YES   |
| FUNCTION    | %.%     | YES     | YES   |
| PROCEDURE   | %.%     | YES     | YES   |
| TABLE       | %.%     | YES     | YES   |
| TRIGGER     | %.%     | YES     | YES   |
+-------------+---------+---------+-------+
5 rows in set (0.00 sec)
...

`ps_truncate_all_tables`

清空所有Performance Schema中的汇总表

mysql> CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary             |
+---------------------+
| Truncated 44 tables |
+---------------------+
1 row in set (0.12 sec)

Query OK, 0 rows affected (0.12 sec)

`statement_performance_analyzer`

在服务器上创建 statements running 的报告。视图基于整体 and/or delta 活动计算

参数：

in_action ENUM('snapshot', 'overall', 'delta', 'create_tmp', 'create_table', 'save', 'cleanup')：要采取的行动。允许这些值：

snapshot：存储快照。默认设置是创建 Performance Schema eventsstatements_summary_by_digest table 的当前内容的快照。通过设置in_table，可以覆盖它以复制指定 table 的内容。快照存储在SYS schema tmp_digests temporary table 中。
overall：根据in_table指定的 table 的内容生成分析。对于整体分析，in_table可以NOW()使用新的快照。这会覆盖现有快照。使用NULL表示in_table可以使用现有快照。如果in_table是NULL并且不存在快照，则会创建新快照。 in_views参数和statement_performance_analyzer.limit configuration 选项会影响此过程的操作。
delta：生成增量分析。在in_table指定的 reference table 和必须存在的快照之间计算增量。此操作使用SYS schema tmp_digests_delta temporary table。 in_views参数和statement_performance_analyzer.limit configuration 选项会影响此过程的操作。
create_table：创建一个适合存储快照以供以后使用的常规 table(对于 example，用于计算增量)。
create_tmp：创建一个适合存储快照的临时 table，供以后使用(对于 example，用于计算增量)。
save：将快照保存在in_table指定的 table 中。 table 必须存在且具有正确的结构。如果不存在快照，则会创建新快照。
cleanup：删除用于快照和增量的临时表。

in_table VARCHAR(129)：table 参数用于in_action参数指定的某些操作。使用格式db_name.tbl_name或tbl_name

in_views SET ('with_runtimes_in_95th_percentile', 'analysis', 'with_errors_or_warnings', 'with_full_table_scans', 'with_sorting', 'with_temp_tables', 'custom')：要包含哪些视图。此参数是SET value，因此它可以包含多个视图名称，以逗号分隔。默认设置是包括除custom之外的所有视图。允许以下值：

with_runtimes_in_95th_percentile：使用statements_withruntimes_in_95th_percentile视图。
analysis：使用statement_analysis视图。
with_errors_or_warnings：使用statements_with_errors_orwarnings视图。
with_full_table_scans：使用statements_with_fulltable_scans视图。
with_sorting：使用statements_with_sorting视图。
with_temp_tables：使用statements_with_temptables视图。
custom：使用自定义视图。必须使用statement_performance_analyzer.view configuration 选项指定此视图以 name 查询或现有视图。

-- 要创建自上次截断eventsstatements_summary_by_digest以及 one-minute delta 周期以来第 95 个百分位数的查询的报表：

-- 创建临时 table 以存储初始快照。
mysql> CALL sys.statement_performance_analyzer('create_tmp', 'mydb.tmp_digests_ini', NULL);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

-- 创建初始快照。
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

-- 将初始快照保存在临时 table 中。
mysql> CALL sys.statement_performance_analyzer('save', 'mydb.tmp_digests_ini', NULL);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

-- 等一分钟。
DO SLEEP(60);
-- 创建新快照。
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)
-- 根据新快照执行分析。
mysql>  CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile');
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)

+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query           | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| DO `SLEEP` (?)  | db1  |           |          1 |         0 |          0 | 1.00 m        | 1.00 m      | 1.00 m      |         0 |             0 |             0 |                 0 | 2020-04-04 00:33:34 | 2020-04-04 00:33:34 | 6b53b41e68283d9981dd00bd98da220b |
+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
1 row in set (0.09 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.09 sec)

-- 根据初始快照和新快照之间的增量执行分析。
mysql> CALL sys.statement_performance_analyzer('delta', 'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)

+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query           | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| DO `SLEEP` (?)  | db1  |           |          1 |         0 |          0 | 1.00 m        | 1.00 m      | 1.00 m      |         0 |             0 |             0 |                 0 | 2020-04-04 00:33:34 | 2020-04-04 00:33:34 | 6b53b41e68283d9981dd00bd98da220b |
+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
1 row in set (0.01 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)


-- 使用完整 table 扫描创建第 95 百分位查询和前 10 个查询的总体报告：
mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
                               

mysql> SET @sys.statement_performance_analyzer.limit = 10;
Query OK, 0 rows affected (0.00 sec)    

mysql> CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile,with_full_table_scans');
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

+-----------------------------------------+
| Next Output                             |
+-----------------------------------------+
| Queries with Runtime in 95th Percentile |
+-----------------------------------------+
1 row in set (0.01 sec)

+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| query           | db   | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | rows_sent | rows_sent_avg | rows_examined | rows_examined_avg | first_seen          | last_seen           | digest                           |
+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
| DO `SLEEP` (?)  | db1  |           |          1 |         0 |          0 | 1.00 m        | 1.00 m      | 1.00 m      |         0 |             0 |             0 |                 0 | 2020-04-04 00:33:34 | 2020-04-04 00:33:34 | 6b53b41e68283d9981dd00bd98da220b |
+-----------------+------+-----------+------------+-----------+------------+---------------+-------------+-------------+-----------+---------------+---------------+-------------------+---------------------+---------------------+----------------------------------+
1 row in set (0.01 sec)

+-------------------------------------+
| Next Output                         |
+-------------------------------------+
| Top 10 Queries with Full Table Scan |
+-------------------------------------+
1 row in set (0.01 sec)

Empty set (0.02 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)



-- 使用自定义视图显示按总执行 time 排序的前 10 个查询，使用 Linux 中的watch命令每分钟刷新视图：
mysql> create database mydb;
mysql> CREATE OR REPLACE VIEW mydb.my_statements AS
       SELECT sys.format_statement(DIGEST_TEXT) AS query,
              SCHEMA_NAME AS db,
              COUNT_STAR AS exec_count,
              sys.format_time(SUM_TIMER_WAIT) AS total_latency,
              sys.format_time(AVG_TIMER_WAIT) AS avg_latency,
              ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg,
              ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg,
              ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg,
              DIGEST AS digest
         FROM performance_schema.events_statements_summary_by_digest
       ORDER BY SUM_TIMER_WAIT DESC;
Query OK, 0 rows affected (0.10 sec)

mysql> CALL sys.statement_performance_analyzer('create_table', 'mydb.digests_prev', NULL);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.00 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

shell> watch -n 60 "mysql sys --table -e \"
> SET @sys.statement_performance_analyzer.view = 'mydb.my_statements';
> SET @sys.statement_performance_analyzer.limit = 10;
> CALL statement_performance_analyzer('snapshot', NULL, NULL);
> CALL statement_performance_analyzer('delta', 'mydb.digests_prev', 'custom');
> CALL statement_performance_analyzer('save', 'mydb.digests_prev', NULL);
> \""

Every 60.0s: mysql sys --table -e "        ...  Mon Dec 22 10:58:51 2014

+----------------------------------+
| Next Output                      |
+----------------------------------+
| Top 10 Queries Using Custom View |
+----------------------------------+
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
| query             | db    | exec_count | total_latency | avg_latency | rows_sent_avg | rows_examined_avg | rows_affected_avg | digest                           |
+-------------------+-------+------------+---------------+-------------+---------------+-------------------+-------------------+----------------------------------+
...

`table_exists`

查看表是否存在

参数如下：

in_db (VARCHAR(64)):指定数据库

in_table (VARCHAR(64)):指定表命

out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY'):返回值有如下可能

‘’ ：表不存在
‘BASE TABLE’ ：存在，且是基表
‘VIEW’ ：存在，且是视图
‘TEMPORARY’：存在，且是临时表

  mysql> CREATE DATABASE db1;
  Query OK, 1 row affected (0.07 sec)
  
  mysql> use db1;
  Database changed
  mysql> CREATE TABLE t1 (id INT PRIMARY KEY);
  Query OK, 0 rows affected (0.08 sec)
  
  mysql> CREATE TABLE t2 (id INT PRIMARY KEY);
  Query OK, 0 rows affected (0.08 sec)
  
  mysql> CREATE view v_t1 AS SELECT * FROM t1;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> CALL sys.table_exists('db1', 't1', @exists); SELECT @exists;
  Query OK, 0 rows affected (0.00 sec)
  
  +------------+
  | @exists    |
  +------------+
  | TEMPORARY  |
  +------------+
  1 row in set (0.00 sec)
  
  mysql> CALL sys.table_exists('db1', 't2', @exists); SELECT @exists;
  Query OK, 0 rows affected (0.00 sec)
  
  +------------+
  | @exists    |
  +------------+
  | BASE TABLE |
  +------------+
  1 row in set (0.01 sec)
  
  mysql> CALL sys.table_exists('db1', 'v_t1', @exists); SELECT @exists;
  Query OK, 0 rows affected (0.00 sec)
  
  +---------+
  | @exists |
  +---------+
  | VIEW    |
  +---------+
  1 row in set (0.00 sec)
  
  mysql> CALL sys.table_exists('db1', 't3', @exists); SELECT @exists;
  Query OK, 0 rows affected (0.01 sec)
  
  +---------+
  | @exists |
  +---------+
  |         |
  +---------+
  1 row in set (0.00 sec)

参考文档

https://www.docs4dev.com/docs/zh/mysql/5.7/reference/sys-statement-performance-analyzer.html

十五十六

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL5.7默认自带的存储过程

文章目录简介`create_synonym_db``execute_prepared_stmt``diagnostics``ps_statement_avg_latency_histogram``ps_trace_thread``ps_setup_disable_background_threads``ps_setup_enable_background_threads``ps_setup_dis...
复制链接

扫一扫