MySQL——性能分析工具ShowProfile

发表于： 2019-10-23 分类于：服务端技术

字数： 4566 阅读：≈ 10分钟浏览：评论：

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句

SQL批量插入

1、建库建表

 1CREATE TABLE dept(
 2	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 3	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
 4	dname VARCHAR(20) NOT NULL DEFAULT "",
 5	loc VARCHAR(13) NOT NULL DEFAULT ""
 6)ENGINE=INNODB DEFAULT CHARSET=GBK;
 7
 8CREATE TABLE emp(
 9	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10	empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
11	ename VARCHAR(20) NOT NULL DEFAULT "",/*名字*/
12	job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
13	mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
14	hiredate DATE NOT NULL,/*入职时间*/
15	sal DECIMAL(7,2) NOT NULL,/* 薪水*/
16	comm DECIMAL(7,2) NOT NULL,/*红利*/
17	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
18)ENGINE=INNODB DEFAULT CHARSET=GBK;

2、设置参数

创建函数，假如报错: This function has none of DETERMINIST…，由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。

但是这样设置会导致的问题是：如果MySQL重启，上述参数又会丢失，所以到达到永久配置的效果，需要修改配置文件，在/etc/my.cnf[mysqld]下加上 global log_bin_trust_function_creators=1;

3、创建函数，保证数据的随机性

随机产生字符串

 1DELIMITER $$
 2CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
 3BEGIN
 4 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 5 DECLARE return_str VARCHAR(255) DEFAULT '';
 6 DECLARE i INT DEFAULT 0;
 7 WHILE i< n DO
 8 SET return_str = CONCAT(return_str, SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 9 SET i = i + 1;
10 END WHILE;
11 RETURN return_str;
12END $$

随机产生数字编号

1DELIMITER $$
2CREATE FUNCTION rand_num( ) RETURNS INT(5)
3BEGIN
4 DECLARE i INT DEFAULT 0;
5 SET i = FLOOR(100 + RAND()*10);
6RETURN i;
7END $$

4、创建存储过程

向emp表存储数据的存储过程

 1/* 建立存储过程（插入数据emp）*/
 2DELIMITER $$
 3CREATE PROCEDURE insert_emp(IN START INT(10) ,IN max_num INT(10) )
 4BEGIN
 5DECLARE i INT DEFAULT 0;
 6#set autocommit =0把autocommit设置成0
 7SET autocommit = 0;
 8 REPEAT
 9 SET i = i + 1;
10 INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START+i), rand_string(6) , 'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());
11 UNTIL i = max_num
12 END REPEAT;
13 COMMIT;
14END $$

向dept表存储数据的存储过程

 1/* 向dept表存储数据的存储过程 */
 2DELIMITER $$
 3CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
 4BEGIN
 5DECLARE i INT DEFAULT 0;
 6SET autocommit = 0; 
 7REPEAT
 8SET i = i+ 1;
 9INSERT INTO dept(deptno, dname, loc) VALUES ((START+i) ,rand_string(10), rand_string(8));
10UNTIL i = max_num
11END REPEAT;
12COMMIT; 
13END $$

5、调用存储过程

由于定义函数的时候是以$$ 这个符号作为结束符，现在要更换为普通语句：

1DELIMITER;

下面开始调用：

1CALL insert_dept(100, 10);

试试向emp表添加50万条数据：

1CALL insert_emp(100001, 500000);

哈哈，虚拟机还可以，OK 妥妥的50万条数据！

Show profiles

1、Show profiles是什么

Show profiles是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量

这就好比去超市买东西，买什么东西花了多少钱都是有明确的记录的，Show profiles也是一样，记录SQL执行步骤耗时，每一步都做了记录。默认情况下，参数处于关闭状态，并保存最近15次的运行结果

2、Show profiles分析步骤

① 查看当前版本是否支持

② 开启功能，默认是关闭，使用前需要开启

1show variables like 'profiling%'

把之前的测试数据放过来

 1create table tbl_dept(
 2    id int(11) not NULL auto_increment,
 3    deptName varchar(30) default NULL,
 4    locAdd varchar(40) default NULL,
 5    primary key (id)
 6)engine=INNODB auto_increment=1 default charset=utf8;
 7
 8create table tbl_emp(
 9    id int(11) not null auto_increment,
10    name varchar(20) default null,
11    deptId int(11) default null,
12    primary key (id),
13    key fk_dept_id(deptId)
14    #constraint fk_dept_id foregin key('deptId') references tbl_dept(id)
15)engine=innodb auto_increment=1 default charset=utf8;
16
17insert into tbl_dept(deptName, locAdd) values ('RD', 11);
18insert into tbl_dept(deptName, locAdd) values ('HR', 12);
19insert into tbl_dept(deptName, locAdd) values ('MK', 13);
20insert into tbl_dept(deptName, locAdd) values ('MIS', 14);
21insert into tbl_dept(deptName, locAdd) values ('FD', 15);
22
23insert into tbl_emp(name, deptId) VALUES ('z3', 1);
24insert into tbl_emp(name, deptId) VALUES ('z4', 1);
25insert into tbl_emp(name, deptId) VALUES ('z5', 1);
26insert into tbl_emp(name, deptId) VALUES ('z3', 1);
27
28insert into tbl_emp(name, deptId) VALUES ('w5', 2);
29insert into tbl_emp(name, deptId) VALUES ('w6', 2);
30insert into tbl_emp(name, deptId) values ('s7', 3);
31insert into tbl_emp(name, deptId) values ('s8', 4);
32insert into tbl_emp(name, deptId) values ('s9', 51);

然后开启Show profile，进行了几条查询再show profile

下面开始选取一条开始分析。

3、分析执行过程

现在假设分析的是语句3 ，也就是select * from tbl_dept:

从上面可以看出，通过show profile 的分析，完整的呈现了一条SQL执行的全流程，配合着MySQL架构模型，其实很容易看出，先进行权限检查，打开表，初始化，优化器优化等等一系列的执行流程…

只能查看CPU和IO吗？当然不是，下面给出了常用的查询字段：

①ALL：显示所有的开销信息。

②BLOCK IO：显示块IO开销。

③CONTEXT SWITCHES：上下文切换开销。

④CPU：显示CPU开销信息。

⑤IPC：显示发送和接收开销信息。

⑥MEMORY：显示内存开销信息。

⑦PAGE FAULTS：显示页面错误开销信息。

⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

⑨SWAPS：显示交换次数开销信息

4、日常开发需要注意的结论

Status里面出现的字段：

① converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

② creating tmp table 创建临时表：说说创建临时表为什么这么费事呢？首先需要新建临时表，然后需要拷贝数据到临时表，数据推送后需要删除数据，这也就是为什么创建临时表非常损耗性能的原因

③ copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！这说明临时表都存不下了，只能往磁盘丢

④ locked 锁定了

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

下面看看临时表的处理过程：

可以看出，拷贝数据到临时表是非常消耗时间的！