备案控制台
探索云世界
开发者社区 数据库 文章 正文

MySQL SQL剖析(SQL profile)

2014-10-11 846
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：     分析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
数据库
关键词：
SQL MySQL
云数据库 RDS MySQL 版profile
SQL profile
云数据库 RDS MySQL 版 sql
云数据库 RDS MySQL 版sql
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
leshami
目录
相关文章
风水道人
|
25天前
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
风水道人
15 0
LightOf
|
11天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化：如何发现SQL中的问题？
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划，关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列，以及区分度高的列。大数据表改结构需谨慎，可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例，如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量，可利用上一批的最大ID进行限制。
LightOf
38 3
蓝易云
|
16天前
|
关系型数据库 MySQL 数据库
MySQL之show profile相关总结
综上所述，`SHOW PROFILE`是MySQL提供的一个用于查询性能分析的工具，可以帮助开发人员定位查询性能问题，并进行优化。通过分析每个阶段的执行时间和资源消耗情况，可以更好地理解查询的执行过程，从而提升数据库性能。
蓝易云
14 0
LightOf
|
18天前
|
SQL 存储 关系型数据库
【MySQL实战笔记】02.一条SQL更新语句是如何执行的-2
【4月更文挑战第5天】两阶段提交是为确保`redo log`和`binlog`逻辑一致，避免数据不一致。若先写`redo log`， crash后数据可能丢失，导致恢复后状态错误；若先写`binlog`，crash则可能导致重复事务，影响数据库一致性。一天一备相较于一周一备，能缩短“最长恢复时间”，但需权衡额外的存储成本。
LightOf
16 1
爱吃糖的范同学
|
25天前
|
SQL 关系型数据库 MySQL
【MySQL】慢SQL分析流程
【4月更文挑战第1天】【MySQL】慢SQL分析流程
爱吃糖的范同学
31 1
洛神灬殇
|
28天前
|
SQL 关系型数据库 MySQL
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
洛神灬殇
42 1
Sunny_yiyi
|
29天前
|
SQL 关系型数据库 MySQL
MySQL SQL语句面试准备
MySQL SQL语句面试准备
Sunny_yiyi
13 0
SarPro
|
15天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库：第二章 关系数据库
【SQL server】玩转SQL server数据库：第二章 关系数据库
SarPro
52 10
w风雨无阻w
|
1月前
|
SQL 数据库 数据安全/隐私保护
Sql Server数据库Sa密码如何修改
Sql Server数据库Sa密码如何修改
w风雨无阻w
23 0
风水道人
|
25天前
|
SQL
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
风水道人
17 0

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 3
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 4
    SQL常见面试题总结2
  • 5
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 6
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 7
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 8
    大模型与数据分析：探索Text-to-SQL（上）
  • 9
    SQL，Group By 真扎心，原来是这样
  • 10
    大模型与数据分析：探索Text-to-SQL（下）
  • 1
    [MySQL]事务原理之redo log,undo log
    45
  • 2
    [MySQL]SQL优化之索引的使用规则
    35
  • 3
    [MySQL] SQL优化之性能分析
    30
  • 4
    【mysql】—— 用户管理
    31
  • 5
    【mysql】—— 视图
    30
  • 6
    【mysql】—— 函数的基本介绍
    25
  • 7
    【mysql】—— 表的增删改查
    40
  • 8
    【mysql】—— 表的约束
    31
  • 9
    【MYSQL】—— MySQL 在 Centos 7环境安装
    52
  • 10
    MySQL模糊查询二三事
    24

    • 相关课程

    更多
  • Java Spring Boot 2.6.0开发实战-1024程序员节创造营公益课
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 5分钟上手Flink MySQL连接器
  • 使用SQL语句管理索引

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）