MySQL使用profile分析SQL执行状态

最新推荐文章于 2021-12-20 23:35:20 发布
最新推荐文章于 2021-12-20 23:35:20 发布
阅读量4.7k 收藏
点赞数
分类专栏： MySQL 文章标签： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/staricqxyz/article/details/18943001
版权

打开profile

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)

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

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

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

使用profile分析SQL，可以看到执行两次后,Send data和sending cached result to clien执行效率的变化 

mysql> select count(*) from sakila.payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.03 sec)

mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.00020400 | select @@profiling                  |
|        2 | 0.00008900 | select count(*) from payment        |
|        3 | 0.00006800 | show databaes                       |
|        4 | 0.02102800 | show databases                      |
|        5 | 0.02847600 | select count(*) from sakila.payment |
+----------+------------+-------------------------------------+
5 rows in set (0.00 sec)
mysql> show profile for query 5;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000030 |
| Waiting for query cache lock   | 0.000005 |
| checking query cache for query | 0.000043 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000027 |
| System lock                    | 0.000010 |
| Waiting for query cache lock   | 0.000010 |
| init                           | 0.000000 |
| optimizing                     | 0.023255 |
| statistics                     | 0.000118 |
| preparing                      | 0.000041 |
| executing                      | 0.000033 |
| Sending data                   | 0.003833 |
| end                            | 0.000054 |
| query end                      | 0.000045 |
| closing tables                 | 0.000045 |
| freeing items                  | 0.000072 |
| Waiting for query cache lock   | 0.000033 |
| freeing items                  | 0.000785 |
| Waiting for query cache lock   | 0.000016 |
| freeing items                  | 0.000002 |
| storing result in query cache  | 0.000005 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
24 rows in set (0.00 sec)

mysql> select count(*) from sakila.payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.00020400 | select @@profiling                  |
|        2 | 0.00008900 | select count(*) from payment        |
|        3 | 0.00006800 | show databaes                       |
|        4 | 0.02102800 | show databases                      |
|        5 | 0.02847600 | select count(*) from sakila.payment |
|        6 | 0.00006900 | select count(*) from sakila.payment |
+----------+------------+-------------------------------------+
6 rows in set (0.00 sec)

mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000029 |
| Waiting for query cache lock   | 0.000004 |
| checking query cache for query | 0.000007 |
| checking privileges on cached  | 0.000004 |
| checking permissions           | 0.000008 |
| sending cached result to clien | 0.000012 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
8 rows in set (0.00 sec)

sending data比较耗费cpu 

mysql> select min(seq) seq,state,count(*) numb_ops,
    ->     round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,
    ->     round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu
    ->     from information_schema.profiling
    ->     where query_id = 5
    ->     group by state
    ->     order by seq;
+------+--------------------------------+----------+---------+---------+---------+---------+
| seq  | state                          | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |
+------+--------------------------------+----------+---------+---------+---------+---------+
|    2 | starting                       |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
|    3 | Waiting for query cache lock   |        4 | 0.00006 | 0.00002 | 0.00100 | 0.00025 |
|    4 | checking query cache for query |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
|    5 | checking permissions           |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|    6 | Opening tables                 |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
|    7 | System lock                    |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|    9 | init                           |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
|   10 | optimizing                     |        1 | 0.02326 | 0.02326 | 0.00000 | 0.00000 |
|   11 | statistics                     |        1 | 0.00012 | 0.00012 | 0.00000 | 0.00000 |
|   12 | preparing                      |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
|   13 | executing                      |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
|   14 | Sending data                   |        1 | 0.00383 | 0.00383 | 0.00300 | 0.00300 |
|   15 | end                            |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |
|   16 | query end                      |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |
|   17 | closing tables                 |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |
|   18 | freeing items                  |        3 | 0.00086 | 0.00029 | 0.00000 | 0.00000 |
|   23 | storing result in query cache  |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
|   24 | logging slow query             |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
|   25 | cleaning up                    |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
+------+--------------------------------+----------+---------+---------+---------+---------+
19 rows in set (0.00 sec)

mysql> show profile cpu for query 5;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000030 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000043 | 0.000000 |   0.000000 |
| checking permissions           | 0.000007 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000027 | 0.000000 |   0.000000 |
| System lock                    | 0.000010 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000010 | 0.001000 |   0.000000 |
| init                           | 0.000000 | 0.000000 |   0.000000 |
| optimizing                     | 0.023255 | 0.000000 |   0.000000 |
| statistics                     | 0.000118 | 0.000000 |   0.000000 |
| preparing                      | 0.000041 | 0.000000 |   0.000000 |
| executing                      | 0.000033 | 0.000000 |   0.000000 |
| Sending data                   | 0.003833 | 0.002999 |   0.001000 |
| end                            | 0.000054 | 0.000000 |   0.000000 |
| query end                      | 0.000045 | 0.000000 |   0.000000 |
| closing tables                 | 0.000045 | 0.000000 |   0.000000 |
| freeing items                  | 0.000072 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000033 | 0.000000 |   0.000000 |
| freeing items                  | 0.000785 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000016 | 0.000000 |   0.000000 |
| freeing items                  | 0.000002 | 0.000000 |   0.000000 |
| storing result in query cache  | 0.000005 | 0.000000 |   0.000000 |
| logging slow query             | 0.000003 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000004 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
24 rows in set (0.00 sec)

mysql> show profile block io for query 5;
+--------------------------------+----------+--------------+---------------+
| Status                         | Duration | Block_ops_in | Block_ops_out |
+--------------------------------+----------+--------------+---------------+
| starting                       | 0.000030 |            0 |             0 |
| Waiting for query cache lock   | 0.000005 |            0 |             0 |
| checking query cache for query | 0.000043 |            0 |             0 |
| checking permissions           | 0.000007 |            0 |             0 |
| Opening tables                 | 0.000027 |            0 |             0 |
| System lock                    | 0.000010 |            0 |             0 |
| Waiting for query cache lock   | 0.000010 |            0 |             0 |
| init                           | 0.000000 |            0 |             0 |
| optimizing                     | 0.023255 |            0 |             0 |
| statistics                     | 0.000118 |            0 |             0 |
| preparing                      | 0.000041 |            0 |             0 |
| executing                      | 0.000033 |            0 |             0 |
| Sending data                   | 0.003833 |            0 |             0 |
| end                            | 0.000054 |            0 |             0 |
| query end                      | 0.000045 |            0 |             0 |
| closing tables                 | 0.000045 |            0 |             0 |
| freeing items                  | 0.000072 |            0 |             0 |
| Waiting for query cache lock   | 0.000033 |            0 |             0 |
| freeing items                  | 0.000785 |            0 |             0 |
| Waiting for query cache lock   | 0.000016 |            0 |             0 |
| freeing items                  | 0.000002 |            0 |             0 |
| storing result in query cache  | 0.000005 |            0 |             0 |
| logging slow query             | 0.000003 |            0 |             0 |
| cleaning up                    | 0.000004 |            0 |             0 |
+--------------------------------+----------+--------------+---------------+
24 rows in set (0.00 sec)

再看同结构数据的MyISAM表 

mysql> create table payment_myisam like payment;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table payment_myisam engine=myisam;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table payment_myisam\G
*************************** 1. row ***************************
       Table: payment_myisam
Create Table: CREATE TABLE `payment_myisam` (
  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint(5) unsigned NOT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `rental_id` int(11) DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into payment_myisam select * from payment;
Query OK, 16049 rows affected (0.10 sec)
Records: 16049  Duplicates: 0  Warnings: 0
mysql> select count(*) from payment_myisam;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.01 sec)

mysql> show profiles;
                                                                                                                                                                        
|       23 | 0.09449600 | insert into payment_myisam select * from payment                                                                                                                                                                                                                                        |
|       24 | 0.00023500 | select count(*) from payment_myisam                                                                                                                                                                                                                                                     |

15 rows in set (0.00 sec)

mysql> show profile for query 24;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000026 |
| Waiting for query cache lock   | 0.000003 |
| checking query cache for query | 0.000037 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000015 |
| System lock                    | 0.000009 |
| Waiting for query cache lock   | 0.000019 |
| init                           | 0.000011 |
| optimizing                     | 0.000006 |
| executing                      | 0.000006 |
| end                            | 0.000000 |
| query end                      | 0.000001 |
| closing tables                 | 0.000008 |
| freeing items                  | 0.000019 |
| Waiting for query cache lock   | 0.000008 |
| freeing items                  | 0.000013 |
| Waiting for query cache lock   | 0.000008 |
| freeing items                  | 0.000008 |
| storing result in query cache  | 0.000015 |
| logging slow query             | 0.000008 |
| cleaning up                    | 0.000009 |
+--------------------------------+----------+
可以看到在count操作MyISAM表时没有sending data这个状态，和Innodb不同，MyISAM不需要访问表数据，所以count操作比Innodb快。



ZhongWeicheng
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL性能分析--SQL执行分析
weixin_52777458的博客
07-08 479
MySQL性能分析--SQL执行分析
mysql profile 调试sql_SQL Server profile使用技巧
weixin_30368405的博客
01-27 228
介绍经常会有人问profile工具该怎么使用？有没有方法获取性能差的sql的问题。自从转mysql我自己也差不多2年没有使用profile，忽然profile变得有点生疏不得不重新熟悉一下。这篇文章主要对profile工具做一个详细的介绍；包括工具的用途和使用方法等。profile是SQLServer自带的一个性能分析监控工具，它也可以生成数据库引擎优化顾问分析需要的负载数据，比如开发对功能进行调...
Windows下Neor Profile SQL 配置监控centos（linux）mysql详细步骤
11-16
Windows下Neor Profile SQL 配置监控centos（linux）mysql详细步骤
MySQL SQL剖析(SQL profile)
乐沙弥的世界
10-11 5222
分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。根据这些开销进一步分析当前SQL瓶颈从而进行优
MySQL--基础知识点--53--profile分析SQL语句性能消耗
Chasing__Dreams的博客
10-28 176
1 前言 2 流程 简易流程大概如下： set profiling=1; //打开分析 run your sql1; run your sql2; show profiles; //查看sql1,sql2的语句分析 show profile for query 1; //查看sql1的具体分析 show profile ALL for query 1; //查看sql1相关的所有分析【主要看i/
超详细用profile工具分析sql语句执行及status说明
Receptive2WE的博客
12-20 1292
超详细的profile使用说明，附有status说明，和一个小案例 通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。
mysql sqlprofile_MySQLSQL剖析(SQLprofile)
weixin_31235395的博客
01-19 386
分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和ses分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。对于全局级别则作用于整个MySQL实例，而session级别紧影响当前session。该...
MySQL利用profile分析慢sql详解（group left join效率高于子查询）
09-09
首先，`PROFILE`是MySQL提供的一种性能分析工具，用于追踪SQL语句的执行过程，它详细列出了每一步操作的时间消耗，包括解析、预处理、执行等阶段，帮助我们找到性能瓶颈。例如，在开启`profiling`后，我们可以通过`...
Neor Profile SQL 汉化
03-17
Neor Profile SQL 汉化文件，兼容版本4.1 安装原版后，将文件放入languages文件夹下，从setting中选择Chinese即可
mysql中profile的使用方法教程
09-09
主要给大家介绍了关于mysql中profile的使用方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
mysql的profile_MySQL中如何使用profile分析SQL执行状态
weixin_42459078的博客
01-18 101
打开profilemysql> select @@have_profiling;+------------------+| @@have_profiling |+------------------+| YES |+------------------+1 row in set (0.00 sec)mysql> select @@profiling;+----...
Mysql高级 （3） -- 使用profile来分析sql
weixin_34194551的博客
03-07 272
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql驱动profilesql_spring boot 系列 --> spring boot&mybatis&druid连接mysql
weixin_31572175的博客
01-28 121
spring boot 系列 --> spring boot&mybatis&druid连接mysql1、首先新建一个maven项目2、添加依赖 mysql驱动&druid&mybatis3、druid数据源配置3.1、这里直接使用class类@Configuration@MapperScan(value = "cn.soft1010.**.dao.**", ...
mysql驱动profilesql_java连接mysql的驱动配置问题
weixin_42299396的博客
01-21 154
想用java访问mysql数据库，下载了一个驱动，然后根据网上说的，配置了classpath，结果失败，原因是ClassNotFoundException，然后继续往上搜索，最后将该驱动放置到jdk\lib\ext目录下，...想用java访问mysql数据库，下载了一个驱动，然后根据网上说的，配置了classpath，结果失败，原因是ClassNotFoundException，然后继续往上搜索...
『Mysql』Mysql中自带的性能分析工具Profile
老陈聊架构
10-31 1547
📣读完这篇文章里你能收获到， profile的操作步骤， profile的命令详细说明
mysql- profile使用
Frankltf的博客
05-03 432

 Mysql分析-profile详解 简介 Profiling是从 mysql5.0.3版本以后才开放的。 启动profile之后，所有查询包括错误的语句都会记录在内。 此工具可用来查询SQL执行状态，System lock和Table lock 花多少时间等等， 对定位一条语句的I/O消耗和CPU消耗 非常重要。(SQL 语句执行所消耗的最大两部分资源就是IO和...
mysql profile及其对应表使用
mojolang
02-21 324
--mysql的profile可用于查看一个sql的具体消耗 show profile all for query 1\G; --profiling has a default value of 0 (OFF) mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 ...
MySQL优化指南：SQL执行效率提升策略
"MySQL优化教程，包括通过showstatus监控SQL执行效率，利用慢查询日志定位低效SQL，以及使用EXPLAIN和showprofile进行分析优化" 在MySQL数据库管理中，性能优化是至关重要的，尤其对于处理大量数据的应用来说。本...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值