服务器之家
您的位置： 首页 数据库 mysql

MySQL性能分析神器—Profiling

100人浏览   2025-01-23 00:06:30

前言
通常遇到SQL查询慢的情况，我们都会使用explain工具去查看SQL的执行计划，然后根据执行计划去建立合适的索引或者优化SQL提高性能。但是，如果执行计划是正确的，SQL语句的性能还是很慢，该怎么办呢？
这时候，MySQL中的Profiling工具可以派上用场了，通过该工具可以获取一条SQL语句在执行过程中多种资源的消耗情况，如CPU、IO、IPC、SWAP等。
Profiling使用
Profiling是 MySQL 提供的可以用来分析当前会话中 SQL 都做了什么、执行的资源消耗工具的情况，可用于 sql 调优的测量。

  1. 查看Profiling是否开启
select @@profiling;
或
show variables like 'profiling';



  • select @profiling 或者 show variables like '%profiling' 查看是否开启，开启它可以让MySQL收集在SQL。
  • 0 或者OFF代表关闭。

开启Profiling

SET profiling = 1;

关闭Profiling

SET profiling = 0;

查看当前会话下执行过的SQL

查看当前会话下执行过的SQL

如下图所示：


  • Query_ID：SQL语句的ID编号，Query_ID会一直递增，后期我们会使用这个id，来具体查询某一条SQL的执行耗时清单。
  • Duration：SQL语句执行时长。
  • Query：具体的SQL语句。

  • 默认情况下，最多保存最近15次的运行结果。
  1. 查看SQL具体的执行情况
# 查看最近一条数据的执行情况
show profile;

# 查看指定query id的执行情况
show profile for query n;

如下图所示：



  • status显示了SQL执行的一个完整的生命周期，涉及到各个阶段。
  • Duration表示SQL在这个阶段的耗时。

也可以查看具体的CPU、IO等资源详细的消耗情况：

SHOW PROFILE CPU, BLOCK IO FOR QUERY [$Query_ID];



完整地使用语法如下：

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
}


type说明如下：

  • ALL：显示所有的开销信息。
  • BLOCK IO：显示块存储设备输入和输出的次数，即从硬盘读取和写入数据的次数。正常情况下，只有当数据量大于内存可用量时，才会借助硬盘进行内存交换（Swap），因此产生如此大量的硬盘读取和写入。
  • CONTEXT SWITCHES：上下文切换开销。
  • CPU：显示CPU开销信息。
  • IPC：显示发送和接收开销信息。
  • MEMORY：显示内存开销信息。
  • PAGE FAULTS：显示页面错误开销信息。
  • SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。
  • SWAPS：显示交换次数开销信息。

优化建议

  1. 如果我们关注SHOW PROFILE结果中每个阶段的耗时，分析耗时最长的阶段。
  2. 我们关注SHOW PROFILE结果中的status列，如果出现下面的status，就需要引起我们的注意了，就要考虑对sql语句进行优化：
  • converting HEAP to MyISAM :查询结果太大，内存不够用。
  • Creating tmp table:创建了临时表。先拷贝数据到临时表，用完后再删除临时表。
  • Copying to tmp table on disk: 把内存中临时表复制到磁盘上。
  • locked: 发生了死锁行为。

总结
总的来说，Profiling还是一款非常好用的性能分析工具

相关推荐