Mysql性能调优命令 show profile

介绍

Show Profile 是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

使用

开启Show Profile功能，默认该功能是关闭的，使用前需开启。命令如下：

使用存储过程向teacher表中添加50w条记录：

-- 创建teacher表
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000020 DEFAULT CHARSET=utf8;

--定义存储过程
dilimiter $
create procedure addTeachers()
BEGIN
DECLARE i int DEFAULT 1;
WHILE i<= 500000 DO
	insert into teacher(name, age) VALUE(i, i%100);
	set i= i+1;
end WHILE;
end $

call addTeachers(); -- 调用存储过程

执行几个相对耗时的sql 语句：

select * from teacher group by id%10 limit 150000;
select * from teacher where age>80 group by age order by age limit 10;
select * from teacher where age>80 group by age%50 order by age limit 30;
、、、
、、、

通过show profiles查看结果，里可以查询最近15次的sql语句，并记录了查询时所消耗的时间：

使用show profile对sql语句进行诊断，执行命令 show profile cpu,block io for query 1; 查询query_id为1的语句cpu 和 block io 的耗时情况。结果如图：

拓展

1、show profile的常用查询参数：

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

2、日常开发需注意的结论（开发中最好不好出现这些）。

• converting HEAP to MyISAM： 查询结果太大，内存不够，数据往磁盘上搬了。
• Creating tmp table： 创建临时表。先拷贝数据到临时表，用完后再删除临时表。
• Copying to tmp table on disk： 把内存中临时表复制到磁盘上，危险！！！
• locked 。

如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。