使用show profiles分析SQL性能

最新推荐文章于 2024-10-08 11:31:42 发布

你刘大爷

最新推荐文章于 2024-10-08 11:31:42 发布

阅读量88

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_42868663/article/details/115484882

版权

本文介绍了如何使用MySQL的showprofiles特性来分析SQL查询的性能。首先展示了如何开启profiling，然后执行SQL查询并查看其消耗的时间。在过程中，注意到showprofiles警告将被PerformanceSchema替代。通过showprofile和showprofileforquery命令，可以详细了解到每个查询阶段的开销，这对于优化SQL性能至关重要。

摘要由CSDN通过智能技术生成

使用show profiles分析SQL性能:

mysql> show variables like "%pro%";

可以看到profiling 默认是OFF的。

开启profile，然后测试

开启profile

mysql> set profiling=1;

执行业务SQL，并用profile分析示例：

--发布SQL查询  
root@localhost[sakila]> select count(*) from customer;  
+----------+  
| count(*) |  
+----------+  
|      599 |  
+----------+  
  
--查看当前session所有已产生的profile  
root@localhost[sakila]> show profiles;  
+----------+------------+--------------------------------+  
| Query_ID | Duration   | Query                          |  
+----------+------------+--------------------------------+  
|        1 | 0.00253600 | show variables like '%profil%' |  
|        2 | 0.00138150 | select count(*) from customer  |  
+----------+------------+--------------------------------+  
2 rows in set, 1 warning (0.01 sec)  
  
--我们看到有2个warning，之前一个，现在一个  
root@localhost[sakila]> show warnings;    --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉  
+---------+------+--------------------------------------------------------------------------------------------------------------+  
| Level   | Code | Message                                                                                                      |  
+---------+------+--------------------------------------------------------------------------------------------------------------+  
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |  
+---------+------+--------------------------------------------------------------------------------------------------------------+

获取SQL语句的开销信息

--可以直接使用show profile来查看上一条SQL语句的开销信息  
--注，show profile之类的语句不会被profiling，即自身不会产生Profiling  
--我们下面的这个show profile查看的是show warnings产生的相应开销  
root@localhost[sakila]> show profile;    
+----------------+----------+  
| Status         | Duration |  
+----------------+----------+  
| starting       | 0.000141 |  
| query end      | 0.000058 |  
| closing tables | 0.000014 |  
| freeing items  | 0.001802 |  
| cleaning up    | 0.000272 |  
+----------------+----------+  
  
--如下面的查询show warnings被添加到profiles  
root@localhost[sakila]> show profiles;  
+----------+------------+--------------------------------+  
| Query_ID | Duration   | Query                          |  
+----------+------------+--------------------------------+  
|        1 | 0.00253600 | show variables like '%profil%' |  
|        2 | 0.00138150 | select count(*) from customer  |  
|        3 | 0.00228600 | show warnings                  |  
+----------+------------+--------------------------------+  
  
--获取指定查询的开销（第二条查询的开销明细） 
root@localhost[sakila]> show profile for query 2;  
+----------------------+----------+  
| Status               | Duration |  
+----------------------+----------+  
| starting             | 0.000148 |  
| checking permissions | 0.000014 |  
| Opening tables       | 0.000047 |  
| init                 | 0.000023 |  
| System lock          | 0.000035 |  
| optimizing           | 0.000012 |  
| statistics           | 0.000019 |  
| preparing            | 0.000014 |  
| executing            | 0.000006 |  
| Sending data         | 0.000990 |  
| end                  | 0.000010 |  
| query end            | 0.000011 |  
| closing tables       | 0.000010 |  
| freeing items        | 0.000016 |  
| cleaning up          | 0.000029 |