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

忘是亡心i 2023-07-22 03:45 2阅读 0赞

### 文章目录 ###

*  简介
 *   *   *  \`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\_disable\_consumer\`
         *  \`ps\_setup\_enable\_consumer\`
         *  \`ps\_setup\_disable\_instrument\`
         *  \`ps\_setup\_enable\_instrument\`
         *  \`ps\_setup\_disable\_thread\`
         *  \`ps\_setup\_enable\_thread\`
         *  \`ps\_setup\_reload\_saved\`
         *  \`ps\_setup\_reset\_to\_default\`
         *  \`ps\_setup\_save\`
         *  \`ps\_setup\_show\_disabled\`
         *  \`ps\_setup\_show\_enabled\`
         *  \`ps\_setup\_show\_disabled\_consumers\`
         *  \`ps\_setup\_show\_disabled\_instruments\`
         *  \`ps\_setup\_show\_enabled\_instruments\`
         *  \`ps\_truncate\_all\_tables\`
         *  \`statement\_performance\_analyzer\`
         *  \`table\_exists\`
 *  参考文档

# 简介 #

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

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

### `create_synonym_db` ###

创建同义词schema指向目标schema

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

### `execute_prepared_stmt` ###

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

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

### `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()][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

### `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

### `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][https_www.docs4dev.com_docs_zh_mysql_5.7_reference_sys-statement-performance-analyzer.html]

[ps_setup_reload_saved]: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/sys-ps-setup-reload-saved.html
[https_www.docs4dev.com_docs_zh_mysql_5.7_reference_sys-statement-performance-analyzer.html]: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/sys-statement-performance-analyzer.html