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

MySQL SQL剖析(SQL profile)

2014-10-11 878
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介：     分析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
数据库
关键词：
云数据库 RDS MySQL 版sql
SQL MySQL
SQL profile
云数据库 RDS MySQL 版sql profile
云数据库 RDS MySQL 版profile
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
leshami
目录
相关文章
青云交（Java大数据AI云计算Python）
|
5天前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计：MySQL 数据库 SQL 语句调优方法详解（2-1）
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划，如使用 EXPLAIN 命令及理解关键指标；优化查询语句结构，包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识，如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章，强调 SQL 语句调优重要性。为提升数据库性能提供实用方法，适合数据库管理员和开发人员。
青云交（Java大数据AI云计算Python）
46 11
青云交（Java大数据AI云计算Python）
|
5天前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计：MySQL 数据库 SQL 语句调优的进阶策略与实际案例（2-2）
本文延续前篇，深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用，介绍多种索引类型及避免索引失效等；调整数据库参数，如缓冲池、连接数和日志参数；还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章，强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导，确保数据库高效运行。
青云交（Java大数据AI云计算Python）
39 5
游客kufrkwrbkmpsa
|
1月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】如何将mysql含有group by的SQL转换成崖山支持的SQL
本文探讨了在YashanDB（崖山数据库）中执行某些SQL语句时出现的报错问题，对比了MySQL的成功执行结果。问题源于SQL-92标准对非聚合列的严格限制，要求这些列必须出现在GROUP BY子句中，而SQL:1999及更高版本允许非聚合列直接出现在选择列中。YashanDB和Oracle遵循SQL-92标准，因此会报错。文章提供了两种解决方法：使用聚合函数处理非聚合列，或将GROUP BY与ORDER BY拆分为两层查询。最后总结指出，SQL-92标准更为严谨合理，建议开发者遵循此规范以避免潜在问题。
游客kufrkwrbkmpsa
58 7
erqfvnamwumvo
|
2月前
|
SQL 存储 缓存
MySQL的架构与SQL语句执行过程
MySQL架构分为Server层和存储引擎层，具有高度灵活性和可扩展性。Server层包括连接器、查询缓存（MySQL 8.0已移除）、分析器、优化器和执行器，负责处理SQL语句；存储引擎层负责数据的存储和读取，常见引擎有InnoDB、MyISAM和Memory。SQL执行过程涉及连接、解析、优化、执行和结果返回等步骤，本文详细讲解了一条SQL语句的完整执行过程。
erqfvnamwumvo
107 3
游客kufrkwrbkmpsa
|
1月前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
游客kufrkwrbkmpsa
46 0
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
蓝易云
|
1月前
|
关系型数据库 MySQL 数据库连接
docker拉取MySQL后数据库连接失败解决方案
通过以上方法，可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限，以及检查主机防火墙设置等。通过逐步排查，可以快速定位并解决连接问题，确保MySQL服务的正常使用。
蓝易云
290 82
青云交（Java大数据AI云计算Python）
|
23小时前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计：MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象，介绍多种负载均衡算法及故障排除步骤，包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法，如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。
青云交（Java大数据AI云计算Python）
28 12
游客r2tgrg3ez7yzc
|
3月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示：“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后，连接时提示不允许连接问题解决
游客r2tgrg3ez7yzc
263 69
技术自由圈/原疯狂创客圈
|
1月前
|
消息中间件 缓存 NoSQL
缓存与数据库的一致性方案，Redis与Mysql一致性方案，大厂P8的终极方案（图解+秒懂+史上最全）
缓存与数据库的一致性方案，Redis与Mysql一致性方案，大厂P8的终极方案（图解+秒懂+史上最全）
技术自由圈/原疯狂创客圈
143 2
蓝易云
|
2月前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤，希望对您有所帮助。
蓝易云
262 42

热门文章

最新文章

  • 1
    OmniSQL：开源文本到SQL神器！自然语言秒转查询到复杂多表连接等SQL需求
  • 2
    如何优化SQL查询以提高数据库性能？
  • 3
    Text2SQL圣经：从0到1精通Text2Sql（Chat2Sql）的原理，以及Text2Sql开源项目的使用
  • 4
    MyBatis动态SQL字符串空值判断，这个细节99%的程序员都踩过坑！
  • 5
    解决大小写、保留字与特殊字符问题！Oracle双引号在SQL中的特殊应用
  • 6
    SQL查询优化：where子句的高效使用方式
  • 7
    SQL注入之万能密码：原理、实践与防御全解析
  • 8
    SQL查询优化：where子句的高效使用方式。
  • 9
    数据库编程：在PHP环境下使用SQL Server的方法。
  • 10
    数据库数据恢复——sql server数据库被加密的数据恢复案例
  • 1
    MySQL 中的回表是什么？
    45
  • 2
    MySQL选错索引了怎么办？
    25
  • 3
    查看Linux、Apache、MySQL、PHP版本的技巧
    51
  • 4
    利用Cron表达式实现MySQL数据库的定时备份
    33
  • 5
    大数据新视界--大数据大厂之MySQL 数据库课程设计：MySQL 数据库 SQL 语句调优的进阶策略与实际案例（2-2）
    39
  • 6
    大数据新视界--大数据大厂之MySQL数据库课程设计：MySQL 数据库 SQL 语句调优方法详解（2-1）
    46
  • 7
    大数据新视界--大数据大厂之MySQL 数据库课程设计：数据安全深度剖析与未来展望
    50
  • 8
    Spring Boot整合MySQL主从集群同步延迟解决方案
    40
  • 9
    【告别容量焦虑】RDS MySQL高性能本地盘全规格最大存储空间提升，最高可达16TB！
    62
  • 10
    大数据新视界--大数据大厂之MySQL 数据库课程设计：开启数据宇宙的传奇之旅
    96

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

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

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云oss简介和如何对接使用