【MySQL高级】使用SHOW PROFILE命令分析性能

摘要:分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中,可以通过配置profiling参数来启用SQL剖析。该参数开启后,后续执行的SQL语句都将记录其资源开销,诸如IO,上下文切换,CPUMemory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整。本篇文章主要介绍了相关语法及参数说明、结合实例分析如何使用及剖析结论。
show-profile-03

概述

MySQL数据库中,可以通过配置profiling参数来启用SQL剖析。该参数开启后,后续执行的SQL语句都将记录其资源开销,诸如IO,上下文切换,CPUMemory等,根据这些开销分析当前SQL瓶颈从而进行优化与调整。`

语法

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]
type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

参数

参数 说明
ALL 显示所有的开销信息
BLOCK IO 显示块IO开销信息
CONTEXT SWITCHES 上下文切换相关开销
CPU 显示CPU开销信息
IPC 显示发送和接收相关开销信息
MEMORY 显示内存开销信息
PAGE FAULTS 显示页面错误开销信息
SOURCE 显示来自源代码的函数名,以及函数发生的文件的名称和行号
SWAPS 显示交换次数相关开销信息

使用

profiling是由 profiling 会话变量控制的,它的默认值为0(OFF)。通过将profiling设置为1或者ON来启用profiling

查看profiling的值并设置开启:

mysql> show variables like 'profiling%'; /*查看profiling的值*/
mysql> select @@profiling;
mysql> SET profiling = 1; /*设置为1开启*/

show-profile-01
基于上篇文章利用存储过程批量插入的数据,运行如下SQL:

mysql> select * from emp group by id%10 limit 150000;
mysql> select * from emp group by id%20 limit  5;
mysql> show profiles;

show profiles显示发送到服务器的最近语句的列表,列表的大小由profiling_history_size会话变量控制,该变量的默认值为15,最大值是100。
show-profile-02

通过以下命令进一步查看某一条资源的消耗情况,如iocpu

mysql> show profile cpu,block io for query 9; 

show-profile-03
由上图可知一条SQL的完整生命周期,包括初始化,连接前检查权限,优化语句,策略分析,预处理,创建临时表,执行语句,传送数据,删除临时表,查询结束,关闭表,释放资源等一系列工作,其中可以看出因为语句用到group by,会导致创建和删除临时表操作,这是导致这条SQL语句执行慢的最主要原因。

剖析结论

当使用上面的语句诊断SQL后,如果出现以下四种情况,会导致性能下降,需要进行语句的优化处理:
converting HEAP to MyISAM,查询结果太大,内存不够使用磁盘。
Creating tmp table 创建临时表,先拷贝数据到临时表,用完后在进行删除。
Copying to tmp table on disk 将内存中的临时表复制到磁盘,出现此状态非常危险。
locked 锁表。

参考

官方SHOW PROFILE Syntax文档说明

相关推荐

Leave a Reply

Your email address will not be published. Required fields are marked *

微信扫一扫,分享到朋友圈

【MySQL高级】使用SHOW PROFILE命令分析性能
返回顶部

显示

忘记密码?

显示

显示

获取验证码

Close