Mysql show Profiles详解

2024-01-03 14:12

本文主要是介绍Mysql show Profiles详解,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

1.简介
show profileshow profiles 命令用于展示SQL语句的资源使用情况,包括CPU的使用,CPU上下文切换,IO等待,内存使用等,这个命令对于分析某个SQL的性能瓶颈非常有帮助,借助于show profile的输出信息,能让我们知道一个SQL在哪个阶段耗时最长,消耗资源最多,从而为SQL优化,提高SQL性能提供重要的依据。

版本情况:
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。

2.说明

  • show profiles展示的是简要的耗时信息,如果想了解某个SQL的具体耗时情况,执行show profile 查看。

  • 使用show profile之前,先启用profilingprofilingsession级变量,session关闭,该sessionprofiling信息也会丢失。如果需要默认开启,需要修改 my.ini 配置文件,加上
    profiling = 1;

  • 开启profiling之后,执行几条SQL,然后执行 show profiles 展示最近执行的多个SQL的执行耗时情况,具体能收集多少个SQL,由参数 profiling_history_size 决定,默认值为15,最大值为100。如果设置为0,等同于关闭profiling

3.测试数据准备

create database db1 charset utf8mb4;
use db1;
create table emp(id int primary key auto_increment,name varchar(16) not null,gender enum('male', 'female') not null,phone varchar(11) not null,email varchar(50),age int not null,salary float(10, 2),post varchar(16),join_date date,leader_id int
);
insert into emp
values (1, '刘备', 'male', '13035445001', 'abcdabcdeabc@qq.com', 32, 4000, '总经理', '2035-06-01', null),(2, '关羽', 'male', '13035445002', 'abcdabcdeghefg@qq.com', 20, 8000, '技术总监', '2035-06-05', 1),(3, '张飞', 'male', '13035445003', 'ijklijklijk@qq.com', 25, 12000, '项目经理', '2035-06-10', 2),(4, '赵云', 'male', '13035445004', 'abmnoepmnopmno@qq.com', 19, 6800, '产品经理', '2035-06-10', 2),(5, '马超', 'male', '13035445005', 'abqrstqrstqrs@qq.com', 26, 11000, '后端开发', '2035-07-11', 2),(6, '黄忠', 'female', '13035445006', 'abuvwxuvwxuvw@qq.com', 48, 15000, '后端开发', '2035-07-22', 2),(7, '夏侯惇', 'male', '13035445007', 'yzabyzabyza@qq.com', 36, 34000, '后端开发', '2035-07-29', 2),(8, '典韦', 'male', '13035445008', 'cdefcdefcdef@qq.com', 19, 6800, '后端开发', '2035-08-02', 2),(9, '吕布', 'female', '13035445009', 'ghijghijghij@qq.com', 20, 9000, '前端开发', '2035-08-03', 2),(10, '周瑜', 'female', '13035445010', 'klmnklmnklmn@qq.com', 32, 36000, '前端开发', '2035-08-08', 2),(11, '文丑', 'male', '13035445011', 'opqropqropqr@qq.com', 27, 24000, '测试', '2035-08-12', 2),(12, '诸葛亮', 'male', '13035445012', 'stuevstuvstuv@qq.com', 27, 8000, '市场总监', '2035-06-05', 1),(13, '庞统', 'male', '13035445013', 'wxyezwxyzwxy@qq.com', 37, 4200, '销售', '2035-06-06', 12),(14, '徐庶', 'male', '13035445014', 'xabcdefghijk@qq.com', 36, 4000, '销售', '2035-06-12', 12),(15, '荀彧', 'male', '13035445015', 'lmnopqrstuv@qq.com', 25, 2400, '销售', '2035-06-10', 12),(16, '荀攸', 'male', '13035445016', 'wxyszabcdefg@qq.com', 25, 2400, '销售', '2035-06-12', 12),(17, '鲁肃', 'male', '13035445017', 'hijklmnopqr@qq.com', 43, 4300, '销售', '2035-06-18', 12),(18, '司马懿', 'female', '13035445018', 'stuvwxyzabc@qq.com', 44, 5000, '销售', '2035-06-20', 12),(19, '杨修', 'male', '13035445019', 'defghijklmn@qq.com', 19, 800, '销售', '2035-07-10', 12),(20, '丁仪', 'male', '13035445020', 'opqsrstuvwxy@qq.com', 49, 3500, '销售', '2035-07-11', 12),(21, '宋江', 'male', '13035445021', 'zabcdefghijkl@qq.com', 30, 8000, '人事总监', '2035-06-05', 1),(22, '吴用', 'male', '13035445022', 'mnopqrstuvwxyz@qq.com', 38, 3000, '人事主管', '2035-06-06', 21),(23, '扈三娘', 'female', '13035445023', 'sabcdefghijklm@qq.com', 42, 2500, '招聘专员', '2035-06-11', 21),(24, '顾大嫂', 'female', '13035445024', 'fnopqrstuvwxyz@qq.com', 38, 3300, '招聘专员', '2035-06-25', 21),(25, '孙二娘', 'female', '13035445025', 'wabcdefghijklf@qq.com', 32, 2400, '绩效专员', '2035-07-22', 21),(26, '丁得孙', 'male', '13035445026', 'enopqrstuvwxyz@qq.com', 32, 2800, '培训专员', '2035-08-10', 21),(27, '柴进', 'male', '13035445027', 'tabcdefghijkli@qq.com', 30, 8000, '财务总监', '2035-06-05', 1),(28, '卢俊义', 'male', '13035445028', 'inopqrstuvwxyz@qq.com', 44, 4000, '会计', '2035-08-19', 27),(29, '晁盖', 'male', '13035445029', 'oabcdefghijkle@qq.com', 44, 3500, '出纳', '2035-08-20', 27),(30, '貂蝉', 'female', '13035445030', 'wnopqrstuvwxyz@qq.com', 36, 800, null, '2035-09-01', null);

5.具体使用:

查看是否开启

show variables like "%profiling%";

在这里插入图片描述
版本是否支持也可以使用以下命令:
在这里插入图片描述
如果 profilingOFF,需要手动开启

set profiling = 1;

调整show profile单次查询的条数,默认15条,最大100条,设为0,默认关闭profiling

set profiling_history_size = 20

执行需要分析的sql语句:

示例:

SELECT * FROM `emp`;
SELECT * FROM `emp` where id = 10;
SELECT gender FROM `emp` group by gender;

执行 show profiles

结果如下:
在这里插入图片描述

分析此结果记录中某一条具体的sql的语法:
show profile type 选项:

  • all:显示所有的性能开销信息
  • block io:显示块 IO 相关的开销信息
  • context switches: 上下文切换相关开销
  • cpu:显示 CPU 相关的信息
  • ipc:显示发送和接收相关的开销信息
  • memory:显示内存相关的开销信息
  • page faults:显示页面错误相关开销信息
  • source:显示和 Source_function、Source_file、Source_line 相关的开销信息
  • swaps:显示交换次数的相关信息

语法:

show profile type1,type2.. for query <Query_ID>;

例如:查Query_ID 为2的 cpu,block io

show profile cpu,block io for query 2;

查询结果:
在这里插入图片描述
返回结果列字段含义

  • Status : sql 语句执行的状态
  • Duration: sql 执行过程中每一个步骤的耗时
  • CPU_user: 当前用户占有的 cpu
  • CPU_system: 系统占有的 cpu
  • Block_ops_in : I/O 输入
  • Block_ops_out : I/O 输出

status出现以下情况的建议

System lock

确认是由于哪个锁引起的,通常是因为MySQL或InnoDB内核级的锁引起的。建议:如果耗时较大再关注即可,一般情况下都还好

Sending data

解释:从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见。
备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net。
建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量

Sorting result

正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序。
建议:创建适当的索引

Table lock

表级锁,要么是因为MyISAM引擎表级锁,要么是其他情况显式锁表

create sort index
当前的SELECT中需要用到临时表在进行ORDER BY排序

建议:创建适当的索引

Creating tmp table

创建临时表。先拷贝数据到临时表,用完后再删除临时表。消耗内存,数据来回拷贝删除,消耗时间
建议:优化索引

converting HEAP to MyISAM

查询结果太大,内存不够,数据往磁盘上搬了。
建议:优化索引,可以调整max_heap_table_size

Copying to tmp table on disk

把内存中临时表复制到磁盘上,危险!!!
建议:优化索引,可以调整tmp_table_size参数,增大内存临时表大小

这篇关于Mysql show Profiles详解的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/565955

相关文章

Python装饰器之类装饰器详解

《Python装饰器之类装饰器详解》本文将详细介绍Python中类装饰器的概念、使用方法以及应用场景,并通过一个综合详细的例子展示如何使用类装饰器,希望对大家有所帮助,如有错误或未考虑完全的地方,望不... 目录1. 引言2. 装饰器的基本概念2.1. 函数装饰器复习2.2 类装饰器的定义和使用3. 类装饰

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Python ZIP文件操作技巧详解

《PythonZIP文件操作技巧详解》在数据处理和系统开发中,ZIP文件操作是开发者必须掌握的核心技能,Python标准库提供的zipfile模块以简洁的API和跨平台特性,成为处理ZIP文件的首选... 目录一、ZIP文件操作基础三板斧1.1 创建压缩包1.2 解压操作1.3 文件遍历与信息获取二、进阶技

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

一文详解Java异常处理你都了解哪些知识

《一文详解Java异常处理你都了解哪些知识》:本文主要介绍Java异常处理的相关资料,包括异常的分类、捕获和处理异常的语法、常见的异常类型以及自定义异常的实现,文中通过代码介绍的非常详细,需要的朋... 目录前言一、什么是异常二、异常的分类2.1 受检异常2.2 非受检异常三、异常处理的语法3.1 try-

Java中的@SneakyThrows注解用法详解

《Java中的@SneakyThrows注解用法详解》:本文主要介绍Java中的@SneakyThrows注解用法的相关资料,Lombok的@SneakyThrows注解简化了Java方法中的异常... 目录前言一、@SneakyThrows 简介1.1 什么是 Lombok?二、@SneakyThrows

Java中字符串转时间与时间转字符串的操作详解

《Java中字符串转时间与时间转字符串的操作详解》Java的java.time包提供了强大的日期和时间处理功能,通过DateTimeFormatter可以轻松地在日期时间对象和字符串之间进行转换,下面... 目录一、字符串转时间(一)使用预定义格式(二)自定义格式二、时间转字符串(一)使用预定义格式(二)自

Redis Pipeline(管道) 详解

《RedisPipeline(管道)详解》Pipeline管道是Redis提供的一种批量执行命令的机制,通过将多个命令一次性发送到服务器并统一接收响应,减少网络往返次数(RTT),显著提升执行效率... 目录Redis Pipeline 详解1. Pipeline 的核心概念2. 工作原理与性能提升3. 核

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

Python正则表达式语法及re模块中的常用函数详解

《Python正则表达式语法及re模块中的常用函数详解》这篇文章主要给大家介绍了关于Python正则表达式语法及re模块中常用函数的相关资料,正则表达式是一种强大的字符串处理工具,可以用于匹配、切分、... 目录概念、作用和步骤语法re模块中的常用函数总结 概念、作用和步骤概念: 本身也是一个字符串,其中