MySQL使用profile查询性能的操作教程
作者:goldensun 发布时间:2024-01-19 10:22:22
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是一个非常量化的子标,可以根据这些量化指标来比较各项资源的消耗,有利于我们对该语句的整体把控!
猜你喜欢
- A.动态页面第一步:创建转向控制页面,创建网站默认的首页文件(通常为"index.asp"或"default.
- 先写一个批处理文件,给个例子。 代码如下:set rq=%date:~0,10% exp system/system的
- 这十则CSS技巧汇编于网络,作为老手已经司空见惯了,也没有什么新意,但温故而知新,或许阅读一遍也有一定的启发,本文主要面对CSS新手朋友,有
- 调用时输入参数如: www.jb51.net/表示删除www.jb51.net首页的缓存, www.jb51.net/test.
- 目录典型的函数装饰器叠放装饰器参数化装饰器标准库中的装饰器functools.wrapsfunctools.lru_cachefunctoo
- //1、运行到C盘根目录 //2、输入:SET ORACLE_SID = 你的SID名称 3、输入:sqlplus/nolog 4、输入:c
- 我们可用正规表达式来寻找并替换URL和邮件地址为活动的超级链接。用到的主要函数就是InsertHyperlinks(inText),语法为:
- 环境: Python3 + windows。开发工具:Anaconda + Jupyter / VS Code。学习效果:1.认识爬虫 /
- 阅读文本前请参考此文章的数据表结构Django提供了两个非常有用的工具:F对象和Q对象,方便了在一些特殊场景下的查询过程。1.F对象查询F对
- paramiko 执行服务器脚本并拿到实时结果import paramikocmd = '{0}/{1} linux 32'
- pycharm sql语句警告产生原因为没有配置数据库,配置数据库,似乎没什么作用那么,直接去掉他的警告提示找到setting->ed
- 朴素贝叶斯估计朴素贝叶斯是基于贝叶斯定理与特征条件独立分布假设的分类方法。首先根据特征条件独立的假设学习输入/输出的联合概率分布,然后基于此
- 本文实例讲述了Python3.6实现根据电影名称(支持电视剧名称),获取下载链接的方法。分享给大家供大家参考,具体如下:做个笔记(pytho
- newstudent.asp<script LANGUAGE=″vbscript″ RUNAT=″Server″&
- Numpy支持大量的维度数组和矩阵运算,对数组运算提供了大量的数学函数库!Numpy比Python列表更具优势,其中一个优势便是速度。在对大
- Torch.stack()1. 概念在一个新的维度上连接一个张量序列2. 参数tensors (sequence)需要连接的张量序列dim
- 这系列文章将介绍一下Oracle的基础知识,将会使用docker下运行的oracle11g的精简版进行说明。这篇文章介绍一下oracle的版
- 我在传递数据的时候老是出错,请问如何处理?不管是什么数据库,一般来说,是它所包含引号的字符串带来的问题。假设我们使用名为“strDate”的
- Go微服务网关从核心原理理解网关的本质网关具备的基本功能:支持多种协议代理:tcp/http/ websocket/grpc支持多种负载均衡
- 一、简介Supervisor 是一款 Python 开发的进程管理系统,允许用户监视和控制 Linux 上的进程,能将一个普通命令行进程变为