Mysql性能分析利器Query Profiler 快速入门

最新推荐文章于 2025-01-24 08:05:28 发布

静谧之心

最新推荐文章于 2025-01-24 08:05:28 发布

阅读量633

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： mysql sql 数据库 linux java

本文链接：https://blog.csdn.net/qq_33709508/article/details/109195834

Mysql 专栏收录该内容

12 篇文章

订阅专栏

本文介绍MySQL中QueryProfiler工具的使用方法，展示如何通过它来诊断SQL语句的性能瓶颈，包括开启和关闭性能分析、查看执行时间表及获取详细的资源消耗情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。

Query Profiler可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。

首先看一下是否开启此功能

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

开启性能分析:


mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

执行目标sql


mysql> update MPushLog  set PUSHSTATUS=1, RESPCONTENT = '1a'  where id=1500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

检测完记得关闭, 会影响sql执行性能:


mysql> set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看执行时间表


mysql> show  profiles;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                |
+----------+------------+----------------------------------------------------------------------+
|        1 | 0.00310800 | show variables like '%profiling%'                                    |
|        2 | 0.00018100 | update MPushLog  set PUSHSTATUS=1, RESPCONTENT = '1a'  where id=1500 |
|        3 | 0.00033900 | SELECT DATABASE()                                                    |
|        4 | 0.00904500 | show databases                                                       |
|        5 | 0.00628200 | show tables                                                          |
|        6 | 0.00282300 | update MPushLog  set PUSHSTATUS=1, RESPCONTENT = '1a'  where id=1500 |
|        7 | 0.00035400 | update MPushLog  set PUSHSTATUS=1, RESPCONTENT = '1a'  where id=1500 |
+----------+------------+----------------------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

如果你想看得更详细

mysql>  show  profile cpu ,swaps for query 1;
+----------------------+----------+----------+------------+-------+
| Status               | Duration | CPU_user | CPU_system | Swaps |
+----------------------+----------+----------+------------+-------+
| starting             | 0.000289 | 0.000079 |   0.000058 |     0 |
| checking permissions | 0.000019 | 0.000015 |   0.000004 |     0 |
| Opening tables       | 0.000023 | 0.000028 |   0.000014 |     0 |
| init                 | 0.000115 | 0.000080 |   0.000009 |     0 |
| System lock          | 0.000014 | 0.000010 |   0.000003 |     0 |
| optimizing           | 0.000006 | 0.000004 |   0.000003 |     0 |
| optimizing           | 0.000005 | 0.000003 |   0.000002 |     0 |
| statistics           | 0.000017 | 0.000014 |   0.000002 |     0 |
| preparing            | 0.000020 | 0.000018 |   0.000002 |     0 |
| statistics           | 0.000011 | 0.000009 |   0.000003 |     0 |
| preparing            | 0.000009 | 0.000006 |   0.000002 |     0 |
| executing            | 0.000022 | 0.000013 |   0.000009 |     0 |
| Sending data         | 0.000011 | 0.000008 |   0.000003 |     0 |
| executing            | 0.000017 | 0.000005 |   0.000013 |     0 |
| Sending data         | 0.002391 | 0.002004 |   0.000386 |     0 |
| end                  | 0.000010 | 0.000004 |   0.000007 |     0 |
| query end            | 0.000008 | 0.000006 |   0.000001 |     0 |
| closing tables       | 0.000004 | 0.000002 |   0.000002 |     0 |
| removing tmp table   | 0.000049 | 0.000023 |   0.000027 |     0 |
| closing tables       | 0.000013 | 0.000009 |   0.000003 |     0 |
| freeing items        | 0.000028 | 0.000013 |   0.000016 |     0 |
| cleaning up          | 0.000027 | 0.000013 |   0.000014 |     0 |
+----------------------+----------+----------+------------+-------+
22 rows in set, 1 warning (0.00 sec)