1 限定只收集当前用户
UPDATE performance_schema.setup_actors SET ENABLED = 'NO', HISTORY = 'NO' WHERE HOST = '%' AND USER = '%'; INSERT INTO performance_schema.setup_actors (HOST,USER,ROLE,ENABLED,HISTORY) VALUES('localhost','root','%','YES','YES'); SELECT * FROM performance_schema.setup_actors; +-----------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +-----------+------+------+---------+---------+ | % | % | % | NO | NO | | localhost | root | % | YES | YES | +-----------+------+------+---------+---------+
2 启用statement 和stage 检测
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%statement/%'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE '%stage/%';
3 启用events_statements*和 events_stages*消费者
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
4 执行查询
set optimizer_switch='index_condition_pushdown=off'; select * from t1 where name='cym11' and addr like '%1cym'; set optimizer_switch='index_condition_pushdown=on'; select * from t1 where name='cym11' and addr like '%1cym';
5 获取event_id
SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%cym11%';
+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT |
+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 22 | 0.0415 | select * from t1 where name='cym11' and addr like '%1cym' |
| 41 | 0.3338 | SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%cym11%' |
| 78 | 0.0092 | select * from t1 where name='cym11' and addr like '%1cym' |
+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 查看阶段事件
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=22;
+------------------------------------------------+----------+
| Stage | Duration |
+------------------------------------------------+----------+
| stage/sql/starting | 0.0001 |
| stage/sql/Executing hook on transaction begin. | 0.0000 |
| stage/sql/starting | 0.0000 |
| stage/sql/checking permissions | 0.0000 |
| stage/sql/Opening tables | 0.0000 |
| stage/sql/init | 0.0000 |
| stage/sql/System lock | 0.0000 |
| stage/sql/optimizing | 0.0000 |
| stage/sql/statistics | 0.0001 |
| stage/sql/preparing | 0.0000 |
| stage/sql/executing | 0.0411 | -- 时间花在执行上
| stage/sql/end | 0.0000 |
| stage/sql/query end | 0.0000 |
| stage/sql/waiting for handler commit | 0.0000 |
| stage/sql/closing tables | 0.0000 |
| stage/sql/freeing items | 0.0000 |
| stage/sql/cleaning up | 0.0000 |
+------------------------------------------------+----------+
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=78;
+------------------------------------------------+----------+
| Stage | Duration |
+------------------------------------------------+----------+
| stage/sql/starting | 0.0000 |
| stage/sql/Executing hook on transaction begin. | 0.0000 |
| stage/sql/starting | 0.0000 |
| stage/sql/checking permissions | 0.0000 |
| stage/sql/Opening tables | 0.0000 |
| stage/sql/init | 0.0000 |
| stage/sql/System lock | 0.0000 |
| stage/sql/optimizing | 0.0000 |
| stage/sql/statistics | 0.0000 |
| stage/sql/preparing | 0.0000 |
| stage/sql/executing | 0.0089 | -- ICP下,执行时间减少
| stage/sql/end | 0.0000 |
| stage/sql/query end | 0.0000 |
| stage/sql/waiting for handler commit | 0.0000 |
| stage/sql/closing tables | 0.0000 |
| stage/sql/freeing items | 0.0000 |
| stage/sql/cleaning up | 0.0000 |
+------------------------------------------------+----------+
该方法没看到类似Oracle Fetching 阶段耗时,也没有类似Oracle 逻辑读部分。