登录
当前位置： 首页 > 编程笔记 >

MySQL使用profile查询性能的操作教程

钮巴英
2023-03-14
本文向大家介绍MySQL使用profile查询性能的操作教程，包括了MySQL使用profile查询性能的操作教程的使用技巧和注意事项，需要的朋友参考一下

MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用。

查看profile是否开启

mysql> show variables like '%profil%';

+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| profiling       | OFF  |     --开启SQL语句剖析功能                         
| profiling_history_size | 15  |     --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling
+------------------------+-------+
2 rows in set (0.00 sec)


基于会话级别开启

mysql> set profiling = 1;     --关闭则用set profiling = off

Query OK, 0 rows affected (0.00 sec)

 

mysql> select distinct d.account,a.server_id from tab_appserver_user a
  -> inner join tab_department_parent b on a.key_id = b.parent_id
  -> inner join tab_department_member c on b.department_id = c.department_id and c.state=1
  -> and c.isdefault=1 inner join tab_user_info d on c.user_id = d.user_id and d.state=1
  -> where a.type=1 
  -> union          
  -> select distinct b.account,a.server_id from tab_appserver_user a
  -> inner join tab_user_info b on a.key_id = b.user_id and b.state=1
  -> where a.type=0;


查看是否设置生效：

select @@profiling;

默认是0，设置成功是1


运行SQL语句：

mysql> select * FROM hx_line WHERE id = '1455023';

查看profiles

mysql> show profiles;

+----------+------------+---------------------------------------------+
| Query_ID | Duration  | Query                    |
+----------+------------+---------------------------------------------+
|    1 | 0.00036150 | select * FROM hx_line WHERE id = '1455023' |
+----------+------------+---------------------------------------------+

查看具体某条的profile

mysql> show profile FOR QUERY 1;

+--------------------------------+----------+
| Status             | Duration |
+--------------------------------+----------+
| starting            | 0.000013 |
| Waiting for query cache lock  | 0.000014 |
| checking query cache for query | 0.000038 |
| checking permissions      | 0.000006 |
| Opening tables         | 0.000013 |
| System lock          | 0.000009 |
| Waiting for query cache lock  | 0.000024 |
| init              | 0.000060 |
| optimizing           | 0.000014 |
| statistics           | 0.000046 |
| preparing           | 0.000017 |
| executing           | 0.000004 |
| Sending data          | 0.000081 |
| end              | 0.000005 |
| query end           | 0.000004 |
| closing tables         | 0.000008 |
| freeing items         | 0.000009 |
| Waiting for query cache lock  | 0.000003 |
| freeing items         | 0.000013 |
| Waiting for query cache lock  | 0.000003 |
| freeing items         | 0.000003 |
| storing result in query cache | 0.000005 |
| logging slow query       | 0.000003 |
| cleaning up          | 0.000004 |
+--------------------------------+----------+
24 rows

我们看到了一个简单的查询，MYSQL内部做了24次操作。
另外，看到了一堆query cache的操作，试着把query_cache_size=0，把query_cache关闭，再次测试：


mysql> show profile FOR QUERY 1;

+----------------------+----------+
| Status        | Duration |
+----------------------+----------+
| starting       | 0.000040 |
| checking permissions | 0.000007 |
| Opening tables    | 0.000015 |
| System lock     | 0.000010 |
| init         | 0.000061 |
| optimizing      | 0.000013 |
| statistics      | 0.000059 |
| preparing      | 0.000018 |
| executing      | 0.000004 |
| Sending data     | 0.000092 |
| end         | 0.000006 |
| query end      | 0.000004 |
| closing tables    | 0.000008 |
| freeing items    | 0.000020 |
| logging slow query  | 0.000003 |
| cleaning up     | 0.000004 |
+----------------------+----------+
16 rows in set (0.00 sec)

当开启了query_cache的情况下，需要多操作6次，在这个示例里面多化了0.000087s。

查询这条语句对CPU的使用情况：

mysql> show profile cpu FOR QUERY 1;

+----------------------+----------+----------+------------+
| Status        | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting       | 0.000037 | 0.000000 |  0.000000 |
| checking permissions | 0.000009 | 0.000000 |  0.000000 |
| Opening tables    | 0.000014 | 0.000000 |  0.000000 |
| System lock     | 0.000009 | 0.000000 |  0.000000 |
| init         | 0.000059 | 0.000000 |  0.000000 |
| optimizing      | 0.000009 | 0.000000 |  0.000000 |
| statistics      | 0.000044 | 0.000000 |  0.000000 |
| preparing      | 0.000015 | 0.000000 |  0.000000 |
| executing      | 0.000004 | 0.000000 |  0.000000 |
| Sending data     | 0.000081 | 0.000000 |  0.000000 |
| end         | 0.000006 | 0.000000 |  0.000000 |
| query end      | 0.000004 | 0.000000 |  0.000000 |
| closing tables    | 0.000008 | 0.000000 |  0.000000 |
| freeing items    | 0.000021 | 0.000000 |  0.000000 |
| logging slow query  | 0.000004 | 0.000000 |  0.000000 |
| cleaning up     | 0.000004 | 0.000000 |  0.000000 |
+----------------------+----------+----------+------------+




查看io及cpu的消耗

mysql> show profile block io,cpu for query 1;

+--------------------------------+----------+----------+------------+--------------+---------------+
| Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting            | 0.000018 |   NULL |    NULL |     NULL |     NULL |
| checking query cache for query | 0.000099 |   NULL |    NULL |     NULL |     NULL |
| Opening tables         | 0.000963 |   NULL |    NULL |     NULL |     NULL |
| System lock          | 0.000015 |   NULL |    NULL |     NULL |     NULL |
| Table lock           | 0.000169 |   NULL |    NULL |     NULL |     NULL |
| optimizing           | 0.000020 |   NULL |    NULL |     NULL |     NULL |
| statistics           | 0.000027 |   NULL |    NULL |     NULL |     NULL |
| preparing           | 0.000018 |   NULL |    NULL |     NULL |     NULL |
| Creating tmp table       | 0.000055 |   NULL |    NULL |     NULL |     NULL |
| executing           | 0.000003 |   NULL |    NULL |     NULL |     NULL |
| Copying to tmp table      | 0.704845 |   NULL |    NULL |     NULL |     NULL |
| Sending data          | 0.130039 |   NULL |    NULL |     NULL |     NULL |
| optimizing           | 0.000029 |   NULL |    NULL |     NULL |     NULL |
| statistics           | 0.000029 |   NULL |    NULL |     NULL |     NULL |
| preparing           | 0.000020 |   NULL |    NULL |     NULL |     NULL |
| Creating tmp table       | 0.000142 |   NULL |    NULL |     NULL |     NULL |
| executing           | 0.000003 |   NULL |    NULL |     NULL |     NULL |
| Copying to tmp table      | 0.000086 |   NULL |    NULL |     NULL |     NULL |
| Sending data          | 0.000067 |   NULL |    NULL |     NULL |     NULL |
| optimizing           | 0.000004 |   NULL |    NULL |     NULL |     NULL |
| statistics           | 0.000005 |   NULL |    NULL |     NULL |     NULL |
| preparing           | 0.000005 |   NULL |    NULL |     NULL |     NULL |
| executing           | 0.000002 |   NULL |    NULL |     NULL |     NULL |
| Sending data          | 0.023963 |   NULL |    NULL |     NULL |     NULL |
| removing tmp table       | 0.003420 |   NULL |    NULL |     NULL |     NULL |
| Sending data          | 0.000005 |   NULL |    NULL |     NULL |     NULL |
| removing tmp table       | 0.003308 |   NULL |    NULL |     NULL |     NULL |
| Sending data          | 0.000006 |   NULL |    NULL |     NULL |     NULL |
| removing tmp table       | 0.000007 |   NULL |    NULL |     NULL |     NULL |
| Sending data          | 0.000009 |   NULL |    NULL |     NULL |     NULL |
| query end           | 0.000003 |   NULL |    NULL |     NULL |     NULL |
| freeing items         | 0.000144 |   NULL |    NULL |     NULL |     NULL |
| storing result in query cache | 0.000011 |   NULL |    NULL |     NULL |     NULL |
| logging slow query       | 0.000003 |   NULL |    NULL |     NULL |     NULL |
| cleaning up          | 0.000006 |   NULL |    NULL |     NULL |     NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
35 rows in set (0.00 sec)


使用查询语句对消耗进行排序

mysql> SELECT STATE, SUM(DURATION) AS Total_R,ROUND( 100 * SUM(DURATION) / (SE
CT SUM(DURATION)
  -> FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1), 2) AS Pct_R, CO
T(*) AS Calls,SUM(DURATION) / COUNT(*) AS "R/Call"
  -> FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1 GROUP BY STATE O
ER BY Total_R DESC;

+--------------------------------+----------+-------+-------+--------------+
| STATE             | Total_R | Pct_R | Calls | R/Call    |
+--------------------------------+----------+-------+-------+--------------+
| Copying to tmp table      | 0.704931 | 81.26 |   2 | 0.3524655000 |
| Sending data          | 0.154089 | 17.76 |   6 | 0.0256815000 |
| removing tmp table       | 0.006735 | 0.78 |   3 | 0.0022450000 |
| Opening tables         | 0.000963 | 0.11 |   1 | 0.0009630000 |
| Creating tmp table       | 0.000197 | 0.02 |   2 | 0.0000985000 |
| Table lock           | 0.000169 | 0.02 |   1 | 0.0001690000 |
| freeing items         | 0.000144 | 0.02 |   1 | 0.0001440000 |
| checking query cache for query | 0.000099 | 0.01 |   1 | 0.0000990000 |
| statistics           | 0.000061 | 0.01 |   3 | 0.0000203333 |
| optimizing           | 0.000053 | 0.01 |   3 | 0.0000176667 |
| preparing           | 0.000043 | 0.00 |   3 | 0.0000143333 |
| starting            | 0.000018 | 0.00 |   1 | 0.0000180000 |
| System lock          | 0.000015 | 0.00 |   1 | 0.0000150000 |
| storing result in query cache | 0.000011 | 0.00 |   1 | 0.0000110000 |
| executing           | 0.000008 | 0.00 |   3 | 0.0000026667 |
| cleaning up          | 0.000006 | 0.00 |   1 | 0.0000060000 |
| logging slow query       | 0.000003 | 0.00 |   1 | 0.0000030000 |
| query end           | 0.000003 | 0.00 |   1 | 0.0000030000 |
+--------------------------------+----------+-------+-------+--------------+
18 rows in set (0.01 sec)

show profile额外一些命令：
* ALL - displays all information
* BLOCK IO - displays counts for block input and output Operations
* CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
* ipC - displays counts for messages sent and received
* MEMORY - is not currently implemented
* PAGE FAULTS - displays counts for major and minor page faults
* SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
* SWAPS - displays swap counts

最后说明：profile是一个非常量化的子标，可以根据这些量化指标来比较各项资源的消耗，有利于我们对该语句的整体把控！

 类似资料：
  • MongoDB教程之查询操作实例

    本文向大家介绍MongoDB教程之查询操作实例，包括了MongoDB教程之查询操作实例的使用技巧和注意事项，需要的朋友参考一下 1.  基本查询：     构造查询数据。   2.  查询条件：     MongoDB提供了一组比较操作符：$lt/$lte/$gt/$gte/$ne，依次等价于</<=/>/>=/!=。     3.  null数据类型的查询： 4.  正则查询： 5.  数组数据

  • 提高MySQL查询性能-数学重查询

    问题内容： 有人愿意帮助我吗？在具有10000行的MEMORY表上，以下查询大约需要18秒。如果我没有“ where”约束，则只需不到一分钟的时间。我已经打开查询缓存以及将其作为准备好的语句来尝试。有什么我可以做的吗？索引还是什么？ 问题答案： 我认为这将为您提供所需的信息，而不管您关注的滚动日期范围…我已经通过创建带有两个标识列的自己的“发票”表进行了测试。使用@mySQL变量实际上非常简单，可

  • 使用groupby和orderby的MySQL查询中的性能问题

    1）使用的第一个查询...大约用了23秒 目前我修改了查询..这需要大约9秒 我不确定要做的表演是什么？我希望这个查询是快速的..我尝试索引rid和id，但这仍然使查询变得更糟。 下面是表格的详细信息 mza_movie_upload MZA_Movie_Statics

  • MySQL慢查询日志的基本使用教程

    本文向大家介绍MySQL慢查询日志的基本使用教程，包括了MySQL慢查询日志的基本使用教程的使用技巧和注意事项，需要的朋友参考一下 慢查询日志相关参数 MySQL 慢查询的相关参数解释：slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 log-slow-queries ：旧版（5.6以下版本）

  • Redis分析慢查询操作的实例教程

    本文向大家介绍Redis分析慢查询操作的实例教程，包括了Redis分析慢查询操作的实例教程的使用技巧和注意事项，需要的朋友参考一下 什么是慢查询 慢查询的作用：通过慢查询分析，找到有问题的命令进行优化。 和mysql的慢SQL日志分析一样，redis也有类似的功能，来帮助定位一些慢查询操作。 Redis slowlog是Redis用来记录查询执行时间的日志系统。 查询执行时间指的是不包括像客户端响

  • MYSQL视图查询性能问题

    我有5个SQL表 存储 工作人员 部门 SOLD_Items staff_rating 我创建了一个视图，将这四个表连接在一起。最后一个表(staff_rating)，我希望在接近项目被出售的时间(sold_items.date)获得视图行的rating列。我尝试了以下SQL查询，这些查询工作正常，但存在性能问题。 SQL查询1 SQL查询2 SQL查询%2比SQL查询%1快。但这两种方法都存在性

相关阅读

弹性查询的性能MySql查看性能使用LIKE的教义2查询php查询操作实现投票功能MySQL使用集合函数进行查询操作实例详解

相关文章

MySQL子查询MySQL通用查询日志1.5.1 Git使用教程之本地仓库的基本操作MySQL分组查询MySQL空值查询

相关问答

Springboot，使用应用程序-{profile}。属性带模操作的MongoDB morphia查询用CASE语句检查mysql查询中的存在性使用查询生成器执行Laravel upsert操作MongoDB如何使用$date操作符进行查询？

相关工具

Profileprofile-storespotify-profilegithub-profileasync-profile

相关文档

Vim 实操教程Canvas 实操教程MySQL 基础教程KDB+ 高性能列式数据库 中文教程Sanic 使用教程
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.