MySQL 有效利用 profile 分析 SQL 语句的执行过程

在日常的工作中，我们通常要分析 SQL 语句的性能，通过会使用到执行计划，利用执行计划来分析 SQL 语句的性能，并进行相应的优化；本文将利用 profile 分析 SQL 语句的执行过程来辅助的分析 SQL 语句，做好优化；

分析 SQL 执行带来的开销是优化 SQL 的重要手段。在 MySQ L数据库中，可以通过配置 profiling 参数来启用SQL剖析。该参数可以在全局和 session 级别来设置。对于全局级别则作用于整个 MySQ L实例，而 session 级别紧影响当前 session。该参数开启后，后续执行的 SQL 语句都将记录其资源开销，诸如 IO，上下文切换，CPU，Memory 等等。根据这些开销进一步分析当前 SQL 瓶颈从而进行优化与调整。

本文描述了如何使用 MySQL profile 以及利用 Performance_Schema 来查询 profile 的 SQL 执行过程的样例；

一、有关 profile 的描述

1、查看 profiling 系统变量

参数说明： have_profiling:只读变量，用于控制是否有系统变量开启或关闭 profiling profiling：开启或关系 SQ L语句剖析功能 profiling_history_size：设置保留 profiling 的数据，默认是 15，范围为 0~100，0 表示将禁用 profiling

2、获取 profiling 的帮助信息

3、开启 porfiling

启用 session 级别的 profiling

4、执行 SQL 查询

5、查看当前 session 所有已产生的 profile

发现有一个告警信息

告警是说 SHOW PROFILES 命令将来会被 Performance_Schema 替换掉。关于利用 Performance_Schema 来查询 profile 将在文章第二部分进行介绍；

6、获取 SQL 语句的开销信息

开启 profiling 后，我们可以通过 show profile 等方式查看，其实这些开销信息被记录到 information_schema.profiling 表中。注 show profile 之类的语句不会被 profiling，即自身不会产生 Profiling。

我们下面的这个 show profile 查看的是 show warnings 产生的相应开销。

7、获取指定查询的开销（Druation 表示持续时间）

输入说明 Sending data：这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。 query end：表示语句执行完毕了，但是还有一些后续工作没做完时的状态。 freeing items：释放查询缓存里面的空间，如果是 DML 操作，所以相应的缓存里的记录就无效了，所以需要有这一步做处理。

查看所有开销

查看特定部分的开销，如下为 CPU 部分的开销

查看 MEMORY 部分的开销

同时查看不同资源开销

8、INFORMATION_SCHEMA.PROFILING

上面已经看到了，show profile 命令即将移除，所以可以直接去 information_schema.profiling 表查看，灵活度更大，其表结构信息如下：

下面的 SQL 语句用于查询 query_id 为 3 的 SQL 开销，且按最大耗用时间倒序排列

停止 profile,可以设置 profiling 参数，或者在 session 退出之后, profiling 会被自动关闭。

关于show profile的使用详细可参考：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

二、利用 Performance_Schema 来查询 profile

第一部分中介绍了，输出 show profiles 会有一个告警信息；告警信息内容已经过期即将在未来版本中删除，那么我们来看看新版本中推荐的 performace_schema 如何使用。先切换到 performance_schema 下，这是 MySQL 新增的性能优化引擎，在 5.6 以前是关闭的，5.6, 5.7 中是默认开启的，5.7 切换的时候还会有一句提示：

提示我们在连接 MySQL 的时候可以使用 -A 参数预读数据库。

使用profile涉及几个表，setup_actors、setup_instruments、setup_consumers。默认表 setup_actors 的内容如下：

按照官方的建议，默认是启用，可以根据需求禁用。

禁用后的结果如下：

然后加入指定的用户

好了，setup_actors 的配置就这样，另外两个表的内容修改也是大同小异。

表 setup_consumers 描述各种事件，

表 setup_instruments 描述这个数据库下的表名以及是否开启监控。

我统计了一下，两个表的默认数据还不少。

setup_instruments 1028 rows

setup_consumers 15 rows

下面我们按照官方的建议来修改，可以看到修改的不是一行，而是相关的很多行。

OK，配置完成；

下面我们来看一下具体要怎么用：

1、切换到 wjq 数据库

2、创建一个测试表 test_profile，插入几行数据

3、运行语句来得到一些详细的统计信息。

4、根据下面的语句查询一个历史表，从表名可以看出是和事件相关的，感觉越来越像 Oracle 了

我们通过上面的语句可以得到一个概览，对应的事件和执行时间。然后到stage相关的历史表中查看事件的详细信息，这就是我们期望的性能数据。如此一来应该就明白上面的配置表中所要做的工作是什么意思了。

整体来看，看到这个特性的输出，十分类似于 Oracle 中的 Datapump，输出实在是太像了，很有条理。

关于使用 performance_schema 查看 profile 的信息详细可参考：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html