1 缘起

最近的某一天，我在某平台看到一篇文章，是关于SQL优化的，
其中，讲到如何查看SQL执行情况，常用的工具有EXPLAIN查看执行计划，
profiling查看执行时间，于是，我打算实战一下，
我使用的MySQL版本为5.7.34，发现，配置profiling属性时，提示该方法将来会被移除，
现在已处于Deprecated状态（不过还能用），但是，我并没有尝试使用，
因为，MySQL官方既然废弃profiling功能，肯定会有替代的方案，
于是，开始搜索，找到了替代方案：Performance Schema（MySQL5.6.x开始支持），
当我搜索中文如何使用Performance Schema时，只有一点点理论上的语句，
并没有完整的测试案例，如通过Performance Schema检测具体某一条SQL的执行时间，
所以，参考官网文档，理论和实践相结合，使用Performance Schema实战监控SQL相关执行过程，
分享如下，提供实践参考案例，帮助读者理解和使用Performance Schema。
官方文档：https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

2 Performance Schema

2.1 特点

• Performance Schema提供监测服务器在运行时执行情况服务。Performance Schema通过PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现监控功能。Performance Schema主要关注数据性能，而INFORMATION_SCHEMA主要关注元数据。
• Performance Schema监控服务器事件。“事件”即服务器需要耗时完成并且可以采集到时间的任何事件。通常，事件可以是函数调用、操作系统等待、SQL描述执行阶段（如解析、排序或者完整的SQL描述、一组SQL描述）。事件采集为服务器以及多个存储引擎提供对同步调用（如互斥）文件或表I/O，表锁等信息的访问。
• Performance Schema事件不同于写入服务器的binary日志事件（日志事件描述数据变更）和事件调度器事件（调度器事件是程序存储类型）。
• Performance Schema事件指向确定的MySQL服务器。Performance Schema表被当做服务器本地表，这些表的变更不会复制或写入binary日志。
• 当前事件是可得到的，如历史记录和统计事件。这可以开发人员决定采集数据次数以及采集数据的时长。事件信息可用于展示线程活跃数或者关联的活跃对象。
• PERFORMANCE_SCHEMA存储引擎通过服务器源码中的“instrumentation points”采集事件数据。
• 采集的事件数据存储在performance_schema数据库中，数据查询与普通的表查询一样，通过SELECT。
• Performance Schema动态配置可以直接更新对应的表，如上所述，可直接使用UPDATE更新，即时生效。
• Performance Schema中的表是不使用持久化磁盘存储的内存表。表内容在服务启动时填充，服务关闭时清除。
• MySQL支持的所有平台均可查看监控。
• 数据采集通过修改服务器源码添加采集器。Performance Schema与数据复制或时间调度等不同的地方是没有单独的线程。

2.2 设计规则

Performance Schema为提供访问服务器执行信息而设计，同时消耗最少的服务器资源，具体设计原则如下：

• 激活Performance Schema不会改变任何服务器功能，如，不会改变定时线程，不会改变执行计划。
• 除了服务器启动期间产生内存分配外，其他时间不进行内存分配。早期通过固定内存尺寸分配架构，不需要对内存扩缩容或重新分配，这对于实现运行时高性能是至关重要的。
• 服务器监控开销小，持续且无感知地进行。激活Performance Schema不影响服务可用性。
• 不改变解析器。不产生新的关键字或者描述。
• Performance Schema即使出现异常也不能影响服务器代码的正常运行。
• 在最初事件采集或后面进行的事件检索之间选择处理时，优先考虑加快采集速度，因为，采集一直存在，然而，检索是按需进行，也可能不需要。
• 方便添加采集点。
• 采集器版本化（兼容性）。如果采集器更新，之前的代码应该继续正常运作。这有利于第三方开发者，因为不需要与Performance Schema最新版始终保持一致。

3 配置

3.1 查看版本

-- 查看MySQL版本信息
SHOW variables like "%version%";
1
2

3.2 配置profiling

3.2.1 查看profiling状态

-- 查看profile
SHOW variables like "%prof%";
1
2

profiling为OFF，此时无法查看SQL执行时间。

3.2.2 开启profiling支持

-- 开启profiling
set profiling=1;
1
2

开启profiling之后，发现，该MySQL版本（5.7.34）提示profiling已标识为deprecated，未来MySQL版本将移除该功能，如下图所示。

于是，搜索了一下相关信息，在Github上发现profile未来将被删除，使用Performance Schema替代，github截图如下图所示。

https://github.com/phpmyadmin/phpmyadmin/issues/15209

3.3 Performance Schema

3.3.1 查看performance schema状态

-- 查看performance schema
SHOW VARIABLES LIKE 'performance_schema';
1
2

由结果可知，performance schema默认开启。

3.3.2 wait配置

• 查看采集器wait

-- 查询等待事件采集器状态
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'wait%';
1
2

• 开启消费者wait属性

-- 开启等待事件采集器
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'wait%';
1
2

• 查看消费者wait

-- 查询等待事件消费者状态
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%wait%';
1
2

• 开启消费者wait

-- 开启等待事件消费者
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE '%wait%'; 
1
2

3.3.3 采集器描述

• 查看采集器描述

-- 查询
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%statement/%';
1
2

• 开启采集器描述

-- 更新
UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%statement/%';
1
2
3
4

3.3.4 采集器阶段

• 查看采集器阶段

-- 查询
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%stage/%';
1
2

• 开启采集器阶段

-- 更新
UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';
1
2
3
4

3.3.4 消费者事件描述

• 查看消费者事件描述

-- 查看消费者事件状态
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%events_statements_%';
1
2

• 开启消费者事件描述

-- 开启消费者事件状态
UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';
1
2
3
4

3.3.5 消费者事件阶段

• 查看消费者事件阶段

-- 查看alter
SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%events_stages_%';
1
2

• 开启消费者事件阶段

--
UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';
1
2
3
4

4 实战

4.1 查询

-- 查询测试
SELECT * FROM db_monkey_run.tb_sys_user where user_id = "0x001";
1
2

先看下执行计划：

-- 查看执行计划
EXPLAIN SELECT * FROM db_monkey_run.tb_sys_user where user_id = "0x001";
1
2

执行计划结果如下图所示。由结果可知，该条SQL为简单查询，无分区，type为ref可知使用非主键索引等值扫描。

4.2 获取SQL事件ID

执行一条SQL后，Performance Schema会生成该条SQL的事件，通过事件ID可以查询该条SQL的相关执行信息，
查询该SQL的事件语句如下：

-- 查看sql对应的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 '%0x001%';
1
2
3

由上述语句可知，将时间TIMER_WAIT处理为秒，保留6位有效数字，
Performance Schema中时间单位为皮秒，1秒=10^12皮秒，
因此，TIMER_WAIT除以10^12。
查询SQL的结果如下图所示，事件ID：EVENT_ID为1286，可根据该事件ID查询SQL执行信息，
该条SQL总耗时为0.000444秒。

4.3 查询SQL各阶段耗时

上面获取SQL执行的事件ID为1286，因此，根据该ID查询SQL各阶段执行时间，
语句如下：

-- 根据event_id查询各阶段耗时       
SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=1286;
1
2
3

SQL各阶段耗时如下图所示。由图可知，SQL执行共拆解为15个阶段，
其中，
SQL
优化耗时：optimizing为0.000008秒。
准备耗时：preparing为0.000009秒。
执行耗时：executing为0.000002秒。
发送数据：Sending data为0.000054秒。
由此可知，一条SQL完整生命周期耗时不单单是执行SQL耗时，还包括打开表、优化SQL、关闭表、发送数据等过程，
SQL的优化可以针对不同的阶段进行，如果SQL自动优化时间长，可以重新设计SQL等。

5 小结

核心：
（1）Performance Schema用于监控SQL执行，如各阶段耗时、阶段的锁等待、索引访问、物理I/O和逻辑I/O等；
（2）Performance Schema中的表是内存表，随MySQL服务器启动载入数据，关闭清除数据，资源消耗少，配置即时生效；
（3）Performance Schema的异常不影响SQL的正常执行；
（4）使用Performance Schema需要配置采集器和消费者，具体要开启：events_statements_history_long和events_stages_history_long等。

6 Q&A

11:03:46 UPDATE performance_schema.setup_instruments SET ENABLED=‘YES’, TIMED=‘YES’ WHERE NAME LIKE ‘wait%’ Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec

-- 关闭安全更新
SET SQL_SAFE_UPDATES=0;
1
2