AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

MySQL SQL剖析(SQL profile)

2014-10-11 998
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：     分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。该参数可以在全局和session级别来设置。

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

 

1、有关profile的描述

--当前版本
root@localhost[sakila]> show variables like 'version';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| version       | 5.6.17-enterprise-commercial-advanced |
+---------------+---------------------------------------+

--查看profiling系统变量
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |   --只读变量，用于控制是否由系统变量开启或禁用profiling
| profiling              | OFF   |   --开启SQL语句剖析功能
| profiling_history_size | 15    |   --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling
+------------------------+-------+

profiling [539]
If set to 0 or OFF (the default), statement profiling is disabled. If set to 1 or ON, statement prof
is enabled and the SHOW PROFILE and SHOW PROFILES statements provide access to prof
information. See Section 13.7.5.32, “SHOW PROFILES Syntax”.

This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.
profiling_history_size [539]
The number of statements for which to maintain profiling information if profiling [539] is
enabled. The default value is 15. The maximum value is 100. Setting the value to 0 effectively
disables profiling. See Section 13.7.5.32, “SHOW PROFILES Syntax”.
This variable is deprecated in MySQL 5.6.8 and will be removed in a future MySQL release.


--获取profile的帮助
root@localhost[sakila]> help profile;
Name: 'SHOW PROFILE'
Description:
Syntax:
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL                --显示所有的开销信息
  | BLOCK IO           --显示块IO相关开销
  | CONTEXT SWITCHES   --上下文切换相关开销
  | CPU                --显示CPU相关开销信息
  | IPC                --显示发送和接收相关开销信息
  | MEMORY             --显示内存相关开销信息
  | PAGE FAULTS        --显示页面错误相关开销信息
  | SOURCE             --显示和Source_function，Source_file，Source_line相关的开销信息
  | SWAPS              --显示交换次数相关开销的信息 

The SHOW PROFILE and SHOW PROFILES statements display profiling
information that indicates resource usage for statements executed
during the course of the current session.

*Note*: These statements are deprecated as of MySQL 5.6.7 and will be
removed in a future MySQL release. Use the Performance Schema instead;
see http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html.
--上面描述从5.6.7开始该命令将会被移除，用Performance Schema instead代替
--在Oracle数据库中，是通过autotrace来剖析单条SQL并获取真实的执行计划以及其开销信息

2、开启porfiling

--启用session级别的profiling
root@localhost[sakila]> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

--验证修改后的结果
root@localhost[sakila]> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+

--发布SQL查询
root@localhost[sakila]> select count(*) from customer;
+----------+
| count(*) |
+----------+
|      599 |
+----------+

--查看当前session所有已产生的profile
root@localhost[sakila]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00253600 | show variables like '%profil%' |
|        2 | 0.00138150 | select count(*) from customer  |
+----------+------------+--------------------------------+
2 rows in set, 1 warning (0.01 sec)

--我们看到有2个warning，之前一个，现在一个
root@localhost[sakila]> show warnings;    --下面的结果表明SHOW PROFILES将来会被Performance Schema替换掉
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                      |
+---------+------+--------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILES' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+--------------------------------------------------------------------------------------------------------------+

3、获取SQL语句的开销信息

--可以直接使用show profile来查看上一条SQL语句的开销信息
--注，show profile之类的语句不会被profiling，即自身不会产生Profiling
--我们下面的这个show profile查看的是show warnings产生的相应开销
root@localhost[sakila]> show profile;  
+----------------+----------+
| Status         | Duration |
+----------------+----------+
| starting       | 0.000141 |
| query end      | 0.000058 |
| closing tables | 0.000014 |
| freeing items  | 0.001802 |
| cleaning up    | 0.000272 |
+----------------+----------+

--如下面的查询show warnings被添加到profiles
root@localhost[sakila]> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00253600 | show variables like '%profil%' |
|        2 | 0.00138150 | select count(*) from customer  |
|        3 | 0.00228600 | show warnings                  |
+----------+------------+--------------------------------+

--获取指定查询的开销
root@localhost[sakila]> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000047 |
| init                 | 0.000023 |
| System lock          | 0.000035 |
| optimizing           | 0.000012 |
| statistics           | 0.000019 |
| preparing            | 0.000014 |
| executing            | 0.000006 |
| Sending data         | 0.000990 |
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000010 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000029 |
+----------------------+----------+

--查看特定部分的开销，如下为CPU部分的开销
root@localhost[sakila]> show profile cpu for query 2 ;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000148 | 0.000000 |   0.000000 |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |
| Opening tables       | 0.000047 | 0.000000 |   0.000000 |
| init                 | 0.000023 | 0.000000 |   0.000000 |
| System lock          | 0.000035 | 0.000000 |   0.000000 |
| optimizing           | 0.000012 | 0.000000 |   0.000000 |
| statistics           | 0.000019 | 0.000000 |   0.000000 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |
| executing            | 0.000006 | 0.000000 |   0.000000 |
| Sending data         | 0.000990 | 0.001000 |   0.000000 |
| end                  | 0.000010 | 0.000000 |   0.000000 |
| query end            | 0.000011 | 0.000000 |   0.000000 |
| closing tables       | 0.000010 | 0.000000 |   0.000000 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |
| cleaning up          | 0.000029 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

--如下为MEMORY部分的开销
root@localhost[sakila]> show profile memory for query 2 ;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000148 |
| checking permissions | 0.000014 |
| Opening tables       | 0.000047 |
| init                 | 0.000023 |
| System lock          | 0.000035 |
| optimizing           | 0.000012 |
| statistics           | 0.000019 |
| preparing            | 0.000014 |
| executing            | 0.000006 |
| Sending data         | 0.000990 |
| end                  | 0.000010 |
| query end            | 0.000011 |
| closing tables       | 0.000010 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000029 |
+----------------------+----------+

--同时查看不同资源开销
root@localhost[sakila]> show profile block io,cpu for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000148 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000047 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000990 | 0.001000 |   0.000000 |            0 |             0 |
| end                  | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000029 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

--Author: Leshami
--Blog  : http://blog.csdn.net/leshami

--下面的SQL语句用于查询query_id为2的SQL开销，且按最大耗用时间倒序排列
root@localhost[sakila]> set @query_id=2;

root@localhost[sakila]> SELECT STATE, SUM(DURATION) AS Total_R,
    ->   ROUND(
    ->        100 * SUM(DURATION) /
    ->           (SELECT SUM(DURATION)
    ->            FROM INFORMATION_SCHEMA.PROFILING
    ->            WHERE QUERY_ID = @query_id
    ->        ), 2) AS Pct_R,
    ->     COUNT(*) AS Calls,
    ->     SUM(DURATION) / COUNT(*) AS "R/Call"
    ->  FROM INFORMATION_SCHEMA.PROFILING
    ->  WHERE QUERY_ID = @query_id
    ->  GROUP BY STATE
    ->  ORDER BY Total_R DESC;
+----------------------+----------+-------+-------+--------------+
| STATE                | Total_R  | Pct_R | Calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.000990 | 71.53 |     1 | 0.0009900000 |--最大耗用时间部分为发送数据
| starting             | 0.000148 | 10.69 |     1 | 0.0001480000 |
| Opening tables       | 0.000047 |  3.40 |     1 | 0.0000470000 |
| System lock          | 0.000035 |  2.53 |     1 | 0.0000350000 |
| cleaning up          | 0.000029 |  2.10 |     1 | 0.0000290000 |
| init                 | 0.000023 |  1.66 |     1 | 0.0000230000 |
| statistics           | 0.000019 |  1.37 |     1 | 0.0000190000 |
| freeing items        | 0.000016 |  1.16 |     1 | 0.0000160000 |
| preparing            | 0.000014 |  1.01 |     1 | 0.0000140000 |
| checking permissions | 0.000014 |  1.01 |     1 | 0.0000140000 |
| optimizing           | 0.000012 |  0.87 |     1 | 0.0000120000 |
| query end            | 0.000011 |  0.79 |     1 | 0.0000110000 |
| end                  | 0.000010 |  0.72 |     1 | 0.0000100000 |
| closing tables       | 0.000010 |  0.72 |     1 | 0.0000100000 |
| executing            | 0.000006 |  0.43 |     1 | 0.0000060000 |
+----------------------+----------+-------+-------+--------------+

--开启profiling后，我们可以通过show profile等方式查看，其实质是这些开销信息被记录到information_schema.profiling表
--如下面的查询，部分信息省略
profiling
root@localhost[information_schema]> select * from profiling limit 3,3\G;
*************************** 1. row ***************************
           QUERY_ID: 1
                SEQ: 5
              STATE: init
           DURATION: 0.000020
           CPU_USER: 0.000000
         CPU_SYSTEM: 0.000000
  CONTEXT_VOLUNTARY: 0
CONTEXT_INVOLUNTARY: 0
       BLOCK_OPS_IN: 0
      BLOCK_OPS_OUT: 0
      MESSAGES_SENT: 0
  MESSAGES_RECEIVED: 0
  PAGE_FAULTS_MAJOR: 0
  PAGE_FAULTS_MINOR: 0
              SWAPS: 0
    SOURCE_FUNCTION: mysql_prepare_select
        SOURCE_FILE: sql_select.cc
        SOURCE_LINE: 1050

--停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭
root@localhost[sakila]> set profiling=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

鹏城DBA总群

文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
MySQL
语音技术
Oracle
数据库
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
leshami
目录
相关文章
游客vphb4ae2je2zi
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB，多种引擎优惠上线！MySQL倚天版88元/年，SQL Server 2核4G仅299元/年，PostgreSQL 227元/年起。高可用、可弹性伸缩，安全稳定。详情见官网活动页。
游客vphb4ae2je2zi
1837 152
上云就上阿狸云
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎，提供高性价比、稳定安全的云数据库服务，适用于多种行业与业务场景。
上云就上阿狸云
1251 156
上云就上阿狸云
|
关系型数据库 分布式数据库 数据库
阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
阿里云数据库提供多种类型，包括关系型与NoSQL，主流如PolarDB、RDS MySQL/PostgreSQL、Redis等。价格低至21元/月起，支持按需付费与优惠套餐，适用于各类应用场景。
上云就上阿狸云
1975 4
蓝易云
|
SQL 监控 关系型数据库
查寻MySQL或SQL Server的连接数，并配置超时时间和最大连接量
以上步骤提供了直观、实用且易于理解且执行的指导方针来监管和优化数据库服务器配置。务必记得，在做任何重要变更前备份相关配置文件，并确保理解每个参数对系统性能可能产生影响后再做出调节。
蓝易云
1002 11
游客j4mujezz7vm7y
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
512 1
sunrr
|
SQL 存储 监控
SQL Server的并行实施如何优化？
【7月更文挑战第23天】SQL Server的并行实施如何优化？
sunrr
1082 13
小王老师呀
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能，可使用`generate_series`函数生成整数序列，例如：`SELECT value FROM generate_series(1, 10)。此外，`date_bucket`函数能按指定间隔（如周）对日期时间值分组，这些工具结合窗口函数和其他时间日期函数，能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
小王老师呀
804 9
以山向海
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
以山向海
482 6
源码星辰
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统（DBMS）而异，以下将以MySQL、SQL Server和Oracle为例，分别概述其安装部署的基本步骤。请注意，由于软件版本和操作系统的不同，具体步骤可能会有所变化。
源码星辰
1593 3
小王老师呀
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符（1-8000），适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符，适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好，空间固定。 - VARCHAR(max) 对于大文本更合适，但可能影响性能。 - 选择取决于数据长度预期和业务需求。
小王老师呀
1562 1

热门文章

最新文章

  • 1
    MYSQL匿名登录 导致创建不了数据库
  • 2
    PostgreSQL 11 新特性解读 : Initdb/Pg_resetwal支持修改WAL文件大小
  • 3
    linux启动流程图+mysql协议图
  • 4
    MySQL Cluster恢复过程记
  • 5
    100%兼容MySQL！手把手教你基于PolarDB搭建RAG系统
  • 6
    mysql安装问题
  • 7
    使用amoeba实现mysql读写分离
  • 8
    【操作宝典】Navicat+MySQL：极简教程，轻松玩转数据库！
  • 9
    CentOS上如何顺利地升级MySQL到5.7版本？
  • 10
    mysql的三大范式
  • 1
    SQL语言小结
    540
  • 2
    （SQL）SQL语言中的查询语句整理
    568
  • 3
    为什么这些 SQL 语句逻辑相同，性能却差异巨大？
    472
  • 4
    SQL日志优化策略：提升数据库日志记录效率
    526
  • 5
    阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
    1837
  • 6
    阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
    1975
  • 7
    SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
    928
  • 8
    MCP与PolarDB集成技术分析：降低SQL门槛与简化数据可视化流程的机制解析
    978
  • 9
    阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
    1251
  • 10
    生成更智能，调试更轻松，SLS SQL Copilot 焕新登场！
    968

    • 相关课程

    更多
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    一条命令迁移，帮你实现 OpenClaw 与 Hermes Agent 记忆互通！