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

2024-11-27 11:36:27

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是一个非常量化的子标，可以根据这些量化指标来比较各项资源的消耗，有利于我们对该语句的整体把控！

MySQL中使用SHOW PROFILE命令分析性能的用法整理

show profile是由Jeremy Cole捐献给MySQL社区版本的.默认的是关闭的,但是会话级别可以开启这个功能.开启它可以让MySQL收集在执行语句的时候所使用的资源.为了统计报表,把profiling设为1 mysql> SET profiling = 1; 之后在运行一个查询 mysql> SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt -> FROM sakila.film_act
MySQL利用profile分析慢sql详解（group left join效率高于子查询）

使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况.分析器可以更好的展示出不良 SQL 的性能问题所在. 最近遇到一个查询比较慢的sql语句,用了子查询,大概需要0.8秒左右,这个消耗时间比较长,严重影响了性能,所以需要进行优化.单独查询单表或者子查询记录都很快,下面来看看详细的介绍. 开启profile mysql> show profiles; -- 查看是否开启 Empty set, 1 warning (0.00
MySQL性能分析工具profile使用教程

分析SQL执行带来的开销是优化SQL的重要手段.在MySQL数据库中,可以通过配置profiling参数来启用SQL剖析.该参数可以在全局和session级别来设置.对于全局级别则作用于整个MySQL实例,而session级别紧影响当前session.该参数开启后,后续执行的SQL语句都将记录其资源开销,诸如IO,上下文切换,CPU,Memory等等.根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整.本文描述了如何使用MySQL profile,不涉及具体的样例分析. 1.有关profil
MySQL使用profile查询性能的操作教程

MYSQL的profiling功能要在Mysql版本5.0.37以上才能使用. 查看profile是否开启 mysql> show variables like '%profil%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | OFF | --开启SQL语句剖析功能 | profiling_history_size |
MySQL 如何分析查询性能

查询优化.索引优化和表设计优化是环环相扣的.如果你有丰富的编写MySQL查询语句的经验,你就会知道如何设计表和索引来支持有效的查询.同样的,知晓表设计同样有助于了解表结构如何对查询语句产生影响.因此,即便表设计和索引都设计得很好,但如果查询语句写得很糟糕,那查询的性能也会很糟糕. 在尝试编写快速的查询语句前,务必记住快速都是基于响应时间进行评估的.查询语句是一组由多个子任务组成的大任务,每一个子任务都会消耗时间.为了优化查询,我们需要尽可能地减少子任务的数量,或者让子任务执行得更快. 注:有些时
Mysql中使用时间查询的详细图文教程

目录一.使用等号查询二.查询某月.某日.某年三.查询时间范围总结一.使用等号查询可以像普通查询使用等号进行查询,但必须查询时间必须和字段对应时间完全相等,比如我要查下面这个值 sql如下: SELECT id, start_time, end_time FROM pay_fee_rule WHERE start_time ='2022-10-9 10:33:39' 查询结果: 但只要改变其中一个值,那么就查不出来了,比如将值改为“2022-10-9 10:33:38”,查询结果如下:
用percona-toolkit为MySQL收集系统和性能信息的教程

系统类工具 1. pt-diskstats 功能介绍: 是一个对GUN/LINUX的交互式监控工具用法介绍: pt-diskstats [OPTION...] [FILES] 为GUN/LINUX打印磁盘io统计信息,和iostat有点像,但是这个工具是交互式并且比iostat更详细.可以分析从远程机器收集的数据. 使用示例: 范例1:查看本机所有的磁盘的状态情况: pt-diskstats 范例2:只查看本机sda2磁盘的状态情况 pt-diskstats --devices
使用mysqldump对MySQL的数据进行备份的操作教程

MySQL 自身的 mysqldump 工具支持单线程工作, 依次一个个导出多个表,没有一个并行的机 ,这就使得它无法迅速的备份数据. mydumper 作为一个实用工具,能够良好支持多线程工作, 可以并行的多线程的从表中读入数据并同时写到不同的文件里 ,这使得它在处理速度方面快于传统的 mysqldump .其特征之一是在处理过程中需要对列表加以锁定,因此如果我们需要在工作时段执行备份工作,那么会引起 DML 阻塞.但一般现在的 MySQL 都有主从,备份也大部分在从上进行,所以锁的问题可以不
Linux下将数据库从MySQL迁移到MariaDB的基础操作教程

自从2012年来,维基百科已经开始从MySQL迁移到MariaDB的过程,是维基媒体数据库架构变革的一个重大里程碑,将英文和德文版的维基百科数据库Wikidata移植到了MariaDB 5.5版本上. 在过去几年中,维基百科一直使用 Facebook 的 MySQL 5.1 衍生版本作为我们的产品数据库,构建版本号是r3753.我们很高兴该产品的性能表现,Facebook 有着全世界最棒的数据库工程师,他们为 MySQL 生态系统带去了很多改进. 现在 MariaDB 的优化器的增强.Perc
PureFTP借助MySQL实现用户身份验证的操作教程

pureftp集成mysql身份验证是将ftp用户信息保存到mysql数据库中,这样可以对大量的ftp服务器做集中管理,对用户帐号的维护只要通过mysql的操作就可以完成. 一.下载pureftp源代码,并确定mysql已经安装好 tar zxvf pure-ftpd-1.0.20.tar.gz cd pure-ftpd-1.0.20 ./configure --prefix=/usr/local/pureftpd \ --with-cookie \ --with-throttling \ --
percona-toolkit对MySQL的复制和监控类操作教程

复制类工具 1. pt-heartbeat 功能介绍: 监控mysql复制延迟用法介绍: pt-heartbeat [OPTION...] [DSN] --update|--monitor|--check|--stop 测量复制落后主mysql或者主PostgreSQL多少时间,你可以使用这个脚本去更新主或者监控复制,具体用法见后面的示例. 原理:pt-heartbeat通过真实的复制数据来确认mysql和postgresql复制延迟,这个避免了对复制机制的依赖,从而能得出准确的落后复制时间
MySQL中修改库名的操作教程

MySQL在5.1引入了一个rename database操作,但在MySQL5.1.23后又不支持这个命令.可以说是一个实验性的功能,没有在生产中支持过(mysql-5.1 release在mysql-5.1.30),那么生产中我们有时为了追求完美需要改一下库名.怎么操作呢? 这里提供一个变通的方法. 1. 创建出新库名: mysql>create database db_v2; mysql>create database db_v2; 2.生成rename语句,从olddb里迁移,我这里o
MySQL查询性能优化索引下推

目录前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围前言前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用主要作用有两个: 减少回表查询的次数减少存

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

相关推荐

随机推荐