25.19.1 使用性能模式查询分析
下面的示例演示如何使用 Performance Schema 语句事件和 stage 事件来检索与SHOW PROFILES和SHOW PROFILE语句提供的概要分析信息可比的数据。
setup_actorstable 可用于限制主机，用户或帐户对历史事件的收集，以减少运行时开销和历史 table 中收集的数据量。该示例的第一步显示了如何将历史事件的收集限制为特定用户。
性能架构以皮秒(万亿分之一秒)为单位显示事件计时器信息，以将计时数据标准化为标准单位。在下面的示例中，将TIMER_WAIT值除以 1000000000000，以秒为单位显示数据。值也被截断为小数点后 6 位，以与SHOW PROFILES和SHOW PROFILE语句相同的格式显示数据。
- 将历史事件的收集限制为将运行查询的用户。默认情况下，setup_actors配置为允许对所有前台线程进行监视和历史事件收集：
mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| % | % | % | YES | YES |
+------+------+------+---------+---------+
更新setup_actorstable 中的默认行以禁用所有前台线程的历史事件收集和监视，并插入新行，为将运行查询的用户启用监视和历史事件收集：
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_actorstable 中的数据现在应类似于以下内容：
mysql> SELECT * FROM performance_schema.setup_actors;
+-----------+-----------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+-----------+-----------+------+---------+---------+
| % | % | % | NO | NO |
| localhost | test_user | % | YES | YES |
+-----------+-----------+------+---------+---------+
- 通过更新setup_instrumentstable，确保已启用语句和阶段检测。默认情况下，某些仪器可能已启用。
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/%';
- 确保启用了
events_statements_*和events_stages_*使用者。默认情况下，某些使用者可能已启用。
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 |
+--------+------------+------------+-----------+--------+------------+
- 通过查询events_statements_history_longtable 来标识语句的
EVENT_ID。此步骤类似于运行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_longtable 以检索语句的阶段事件。阶段使用事件嵌套链接到语句。每个阶段事件记录都有一个
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 |
+--------------------------------+----------+
