MySQL使用SHOW PROFILE进行SQL分析

Dear 丶 2022-06-05 11:08 194阅读 0赞

SHOW PROFILE 是MySQL提供可以用来分析当前会话语句执行的资源消耗情况，可以用于SQL 的调优测评依据。在默认的情况下，参数处于关闭状态，并保存着最近15 次的运行结果。

我们可以通过 SHOW VARIABLES LIKE ‘PROFILING’; 命令查看参数的开关情况；通过 SET PROFILING = ON; 命令设置开启状态。

我们看看当我们执行SHOW PROFIES; 会查询到些什么？

Query_ID    Duration    Query
    152         0.0012655   SHOW STATUS 153 0.00120325 SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID 154 0.0002665 SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.001724*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=151 GROUP BY STATE ORDER BY SEQ 155 0.000189 SET PROFILING=1 156 0.00206525 SHOW STATUS 157 0.001668 SHOW STATUS 158 0.00054625 SELECT * FROM t_emp WHERE dept_id in (SELECT dept_id FROM t_dept) LIMIT 5 159 0.001448 SHOW STATUS 160 0.00115475 SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID 161 0.00026725 SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.000550*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=158 GROUP BY STATE ORDER BY SEQ 162 0.00019575 SELECT * FROM `ssm_crud`.`t_emp` LIMIT 0 163 0.0007275 SHOW COLUMNS FROM `ssm_crud`.`t_emp` 164 0.0001305 SET PROFILING=1 165 0.00131475 SHOW STATUS 166 0.0011755 SHOW STATUS

Query\_ID表示从连接上数据库到现在执行的SQL 语句序号，Duration 表示执行该SQL 所耗费的时间(S),Query 表示执行的操作。

如果我们想要具体的知道执行SQL 时CPU 与 I/O 的消耗，可以使用 SHOW PROFILE CPU ,BLOCK IO FOR QUERY Query\_ID; 命令。

通过下图我们可以通过Status 属性清楚的知道MySQL在执行SQL 语句时每个阶段所消耗的时间，CPU 内存 与 I/O 资源。  
![这里写图片描述][SouthEast]  
但是如果在Status 属性中我们看到了如下表中的一些值，就说明我们的SQL 在执行会消耗大量的时间，需要优化。由于我本地的数据库中只有很少的数据信息，并没有出现下面的情况。

<table> 
 <tbody> 
  <tr> 
   <th>Status</th> 
   <th>介绍</th> 
  </tr> 
  <tr> 
   <td> converting HEAP to MyISAM</td> 
   <td>查询结果集太大，内存不够借用磁盘空间 </td> 
  </tr> 
  <tr> 
   <td>Creating tmp table</td> 
   <td>拷贝数据到临时表(先创建，然后拷贝数据，用完后会自动删除),很消耗资源 </td> 
  </tr> 
  <tr> 
   <td>Coping to tmp table on disk </td> 
   <td> 把内存中的临时表复制到磁盘，很危险 </td> 
  </tr> 
  <tr> 
   <td>locked</td> 
   <td> 加锁 </td> 
  </tr> 
 </tbody> 
</table>

[SouthEast]: /images/20220605/f541aca64daa4422af782f9d1452641e.png