mysql8 查询性能_mysql8 参考手册-使用性能模式进行查询分析

以下示例演示了如何使用Performance Schema语句事件和stage事件来检索与SHOW PROFILES和SHOW PROFILE语句提供的概要分析信息相当的数据。

该setup_actors表可用于限制主机，用户或帐户对历史事件的收集，以减少运行时开销和历史表中收集的数据量。该示例的第一步显示了如何将历史事件的收集限制为特定用户。

性能架构以皮秒(万亿分之一秒)为单位显示事件计时器信息，以将计时数据标准化为标准单位。在下面的示例中，将 TIMER_WAIT值除以1000000000000，以秒为单位显示数据。值也将被截断为小数点后6位，以与SHOW PROFILESand SHOW PROFILE语句相同的格式显示数据。

将历史事件的收集限制为将运行查询的用户。默认情况下， setup_actors配置为允许监视和历史事件收集所有前台线程：

mysql> SELECT * FROM performance_schema.setup_actors;

+------+------+------+---------+---------+

| HOST | USER | ROLE | ENABLED | HISTORY |

+------+------+------+---------+---------+

| % | % | % | YES | YES |

+------+------+------+---------+---------+

更新setup_actors表中的默认行 以禁用所有前台线程的历史事件收集和监视，并插入新行，为将运行查询的用户启用监视和历史事件收集：

mysql> UPDATE performance_schema.setup_actors

SET ENABLED = 'NO', HISTORY = 'NO'

WHERE HOST = '%' AND USER = '%';

mysql> INSERT INTO performance_schema.setup_actors

(HOST,USER,ROLE,ENABLED,HISTORY)

VALUES('localhost','test_user','%','YES','YES');

setup_actors现在 ，表中的数据应类似于以下内容：

mysql> SELECT * FROM performance_schema.setup_actors;

+-----------+-----------+------+---------+---------+

| HOST | USER | ROLE | ENABLED | HISTORY |

+-----------+-----------+------+---------+---------+

| % | % | % | NO | NO |

| localhost | test_user | % | YES | YES |

+-----------+-----------+------+---------+---------+

通过更新setup_instruments表确保已启用语句和阶段检测 。默认情况下，某些仪器可能已启用。

mysql> UPDATE performance_schema.setup_instruments

SET ENABLED = 'YES', TIMED = 'YES'

WHERE NAME LIKE '%statement/%';

mysql> UPDATE performance_schema.setup_instruments

SET ENABLED = 'YES', TIMED = 'YES'

WHERE NAME LIKE '%stage/%';

确保已启用eventsstatements和 eventsstages使用者。默认情况下，某些使用者可能已启用。

mysql> UPDATE performance_schema.setup_consumers

SET ENABLED = 'YES'

WHERE NAME LIKE '%events_statements_%';

mysql> UPDATE performance_schema.setup_consumers

SET ENABLED = 'YES'

WHERE NAME LIKE '%events_stages_%';

在您要监视的用户帐户下，运行要分析的语句。例如：

mysql> SELECT * FROM employees.employees WHERE emp_no = 10001;

+--------+------------+------------+-----------+--------+------------+

| emp_no | birth_date | first_name | last_name | gender | hire_date |

+--------+------------+------------+-----------+--------+------------+

| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |

+--------+------------+------------+-----------+--------+------------+

EVENT_ID通过查询events_statements_history_long 表来 标识语句 。此步骤类似于运行 SHOW PROFILES以标识 Query_ID。以下查询产生类似于以下内容的输出SHOW PROFILES：

mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT

FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';

+----------+----------+--------------------------------------------------------+

| event_id | duration | sql_text |

+----------+----------+--------------------------------------------------------+

| 31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 |

+----------+----------+--------------------------------------------------------+

查询 events_stages_history_long 表以检索语句的阶段事件。阶段使用事件嵌套链接到语句。每个阶段事件记录都有一个NESTING_EVENT_ID包含EVENT_ID父语句的的列。

mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration

FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;

+--------------------------------+----------+

| Stage | Duration |

+--------------------------------+----------+

| stage/sql/starting | 0.000080 |

| stage/sql/checking permissions | 0.000005 |

| stage/sql/Opening tables | 0.027759 |

| stage/sql/init | 0.000052 |

| stage/sql/System lock | 0.000009 |

| stage/sql/optimizing | 0.000006 |

| stage/sql/statistics | 0.000082 |

| stage/sql/preparing | 0.000008 |

| stage/sql/executing | 0.000000 |

| stage/sql/Sending data | 0.000017 |

| stage/sql/end | 0.000001 |

| stage/sql/query end | 0.000004 |

| stage/sql/closing tables | 0.000006 |

| stage/sql/freeing items | 0.000272 |

| stage/sql/cleaning up | 0.000001 |

+--------------------------------+----------+