MySQL之慢查询日志和show profile

1. 慢查询日志

1.1 是什么

　　（1） MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

　　（2）具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

　　（3）由他来查看哪些SQL超出了我们的最大 忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的SQL，结合之前explain进行全面分析。

1.2 怎么用

　　默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

　　当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响，慢查询日志支持将日志记录写入文件。

　　（1）开启设置

• SHOW VARIABLES LIKE '%slow_query_log%'; 查看慢查询日志是否开启。默认情况下 slow_query_log 的值为 OFF，表示慢查询日志是禁用的
• set global slow_query_log=1; 开启慢查询日志
• SHOW VARIABLES LIKE 'long_query_time%'; 查看慢查询设定阈值 单位秒
• set long_query_time=1;  设定慢查询阈值 单位秒

　　（2）永久生效，修改配置文件my.cnf中[mysql]下配置

slow_query_log=1

slow_query_log_file=/var/lib/mysql/atguigu-slow.log   //日志写入文件

long_query_time=3   //慢SQL阀值

log_output=FILE   //日志文件格式

　　（3）运行查询时间长的SQL，打开慢查询日志查看。

1.3 日志分析工具 mysqldumpslow

　　（1）查看mysqldumpslow的帮助信息

　　　　命令：[root@cocoon ~]# mysqldumpslow --help  （linux环境）

　　　　

　　（2）查看mysqldumpslow的帮助信息

• 得到返回记录集最多的10个SQL: mysqldumpslow -s r -t 10 c:/mysql/mysql.log
• 得到访问次数最多的 10 个 SQL: mysqldumpslow -s c -t 10 c:/mysql/mysql.log

• 得到按照时间排序的前 10 条里面含有左连接的查询语句：

　　　　mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

• 另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况：

　　　　mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

2. 批量数据脚本

1）. 建表语句

CREATE TABLE `dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, ceo INT NULL , PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `empno` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2）. 设置参数

　　在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。 否则会报错。

　　查询：show variables like 'log_bin_trust_function_creators';

　　设置：set global log_bin_trust_function_creators=1;

　　　　当然，如上设置只存在于当前操作，想要永久生效，需要写入到配置文件中：

　　　　在[mysqld]中加上 log_bin_trust_function_creators=1

3）.创建函数

3.1 随机产生字符串函数

CREATE FUNCTION `rand_string`(n int) RETURNS varchar(255) CHARSET utf8
BEGIN
  DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str varchar(255) DEFAULT '';
  DECLARE i int DEFAULT 0;
  WHILE i < n DO
    SET return_str = CONCAT(return_str, SUBSTRING(chars_str,FLOOR(1 + RAND() * 52), 1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END;

3.2 随机产生部门编号

CREATE  FUNCTION `rand_num`(from_num int, to_num int) RETURNS int(11)
BEGIN
  DECLARE i int DEFAULT 0;
  SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
  RETURN i;
END;

4）. 创建存储函数

4.1 创建往 emp 表中插入数据的存储过程

CREATE PROCEDURE `insert_emp`(in start int(10), in max_num int(10))
BEGIN
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i =i+1;
      INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
        until i = max_num
    end repeat;
    commit;
END;

4.2 创建往 dept 表中插入数据的存储过程

CREATE PROCEDURE `insert_dept`(max_num int)
BEGIN
  DECLARE i int DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT
      INTO `dept`
      (`deptname`, `address`, `ceo`)
    VALUES
      (rand_string(8), rand_string(10), rand_num(1, 500000));
  UNTIL i = max_num END REPEAT;
  COMMIT;
END;

5）.调用存储过程

#执行存储过程，往 dept 表添加 1 万条数据

DELIMITER ;

CALL insert_dept(10000); 

 

#执行存储过程，往 emp 表添加 50 万

DELIMITER ;

CALL insert_emp(100000,500000); 

3. show profile

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

3.1 分析步骤

　　1. 开启Show Profile功能，默认该功能是关闭的，使用前需开启。

 　　

3.2. 批量导入数据

 　　上面已做

3.3. 运行SQL，通过show profiles查看结果

　　

3.4. 使用show profile对sql语句进行诊断。

 通过上面的图，可以看出，第10条SQL查询的时间很慢。我们就来看看这条SQL语句的执行情况

　　

 3.5. show profile的常用查询参数

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

②BLOCK IO：显示块IO开销。

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

④CPU：显示CPU开销信息。

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

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

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

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

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

3.6. 需要注意的参数

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

②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。

③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！

④locked。

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

总结

#1.show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。如果永久开启，修改配置文件。

#2.通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。

#3.注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。

#4.可更多的关注MySQL官方文档，获取更多的知识。