Mysql调优工具------profiling使用

@睡不醒的每天@

已于 2023-07-19 16:33:40 修改

阅读量726

点赞数

分类专栏： mysql sql调优工具文章标签： mysql 数据库

于 2023-07-19 15:15:25 首次发布

本文链接：https://blog.csdn.net/m0_67929156/article/details/131806428

版权

mysql sql调优工具专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章介绍了MySQL的profiling功能，用于查询调优。通过开启profiling，可以查看查询的执行状态和耗时，包括CPU和IO资源的使用情况。通过`SHOWPROFILE`命令获取详细信息，如`SHOWPROFILEFORQUERYquery_id`可以展示特定查询ID的详细性能报告，帮助识别和优化数据库性能瓶颈。

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

提示：MYSQL调优工具

profile 语法：

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
}

一、查看profiling状态

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

0：表示为开启
1：表示开启

二、开启 profiling

Set profiling=1；

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

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

此时profiling状态开启，

三、show profiles 命令

mysql> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00007100 | select profiling                           |
|        2 | 0.00010800 | select @@profiling                         |
|        3 | 0.00004875 | show profiling                             |
|        4 | 0.00031000 | show tables                                |
|        5 | 0.00015800 | select count(1) from emp                   |
|        6 | 0.00017575 | select count(1) from dept                  |
|        7 | 0.00021650 | select * from emp                          |
|        8 | 0.00017050 | select * from emp where location_id='1800' |
+----------+------------+--------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

查看所有语句消耗的具体时间（Duration）和使用的查询（Query ）

四、单query获取详细的profile的信息

语法：
show profile for query query_id

查看具体的query_ID所经历的过程以及消耗的资源详细分析报告

1. 查询 Query_ID=5信息

mysql> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000041 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000011 |
| init                 | 0.000009 |
| System lock          | 0.000005 |
| optimizing           | 0.000003 |
| statistics           | 0.000007 |
| preparing            | 0.000006 |
| executing            | 0.000001 |
| Sending data         | 0.000026 |
| end                  | 0.000002 |
| query end            | 0.000004 |
| closing tables       | 0.000004 |
| freeing items        | 0.000006 |
| logging slow query   | 0.000025 |
| cleaning up          | 0.000007 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

2. 查询 Query_ID=5的cup、io信息

cpu

mysql> show profile cpu for query 5;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000041 | 0.000023 |   0.000014 |
| checking permissions | 0.000004 | 0.000003 |   0.000002 |
| Opening tables       | 0.000011 | 0.000006 |   0.000004 |
| init                 | 0.000009 | 0.000006 |   0.000003 |
| System lock          | 0.000005 | 0.000003 |   0.000002 |
| optimizing           | 0.000003 | 0.000001 |   0.000001 |
| statistics           | 0.000007 | 0.000004 |   0.000002 |
| preparing            | 0.000006 | 0.000004 |   0.000002 |
| executing            | 0.000001 | 0.000001 |   0.000001 |
| Sending data         | 0.000026 | 0.000016 |   0.000009 |
| end                  | 0.000002 | 0.000001 |   0.000001 |
| query end            | 0.000004 | 0.000002 |   0.000001 |
| closing tables       | 0.000004 | 0.000003 |   0.000002 |
| freeing items        | 0.000006 | 0.000003 |   0.000002 |
| logging slow query   | 0.000025 | 0.000016 |   0.000009 |
| cleaning up          | 0.000007 | 0.000004 |   0.000003 |
+----------------------+----------+----------+------------+

CPU_user：当前用户占用的 CPU；
CPU_system：当前系统占用的CPU。

io：

mysql> show profile block io for query 5;
±--------------±---------±-------------±--------------+
| Status | Duration | Block_ops_in | Block_ops_out |
±--------------±---------±-------------±--------------+
| starting | 0.000036 | 0 | 0 |
| freeing items | 0.000009 | 0 | 0 |
| cleaning up | 0.000004 | 0 | 0 |
±--------------±---------±-------------±--------------+
3 rows in set, 1 warning (0.00 sec)