MySQL使用performance_schema诊断sql语句性能

1 检查MySQL中激活的actor（即监控的主机），

mysql> SELECT *
    -> FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

2 打开对sql语句的监视，可以针对某一类sql语句单数打开监控

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED = 'YES', TIMED = 'YES'
    -> WHERE NAME LIKE '%statement/%';
Query OK, 0 rows affected (0.12 sec)
Rows matched: 213  Changed: 0  Warnings: 0

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%stage/%';

3 查看是否打开

select name, enabled from performance_schema.setup_instruments where name like 'statement%' or name like 'stage%';

4 打开statement和stage consumer

update setup_consumers set ENABLED='YES' where name='events_stages_history_long';
update setup_consumers set ENABLED='YES' where name='events_stages_history';
update setup_consumers set ENABLED='YES' where name='events_stages_current';

5 检查所需consumer是否被打开

select * from performance_schema.setup_consumers where name like '%stage%';
+----------------------------+---------+
| NAME                       | ENABLED |
+----------------------------+---------+
| events_stages_current      | YES     |
| events_stages_history      | YES     |
| events_stages_history_long | YES     |
+----------------------------+---------+
3 rows in set (0.00 sec)

6 运行sql查询

select * from a limit 100 ；

7 从performance_schema.events_statements_history_long查询sql语句的event id

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

-> FROM performance_schema.events_statements_history_long;

12 rows in set (0.00 sec)
8 查询这条语句每个阶段的执行时间

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

-> FROM performance_schema.events_stages_history_long
-> WHERE NESTING_EVENT_ID = 99;

17 rows in set (0.00 sec)
可以看到每个sql语句每个阶段的执行时间