MySQL慢查询日志

1. 慢查询日志

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

long_query_time的默认值是10，也就是运行10秒以上的语句。通过查看哪些SQL超出了我们的最大查询时间值，可以结合explain进行具体的优化。

默认情况下，MySQL数据库没有开启慢查询日志，需手动设置参数开启。一般不是调优需要的话，不建议启动该参数，因为开启慢查询日志会带来一定的性能影响；慢查询日志是支持将日志记录写入文件的。

1.1 查看与启动

• 查看：SHOW VARIABLES LIKE "%slow_query_log%"
• 开启：set global slow_query_log = 1

如果想永久生效，需修改配置文件

1.2 慢查询记录标准

这个是由参数long_query_time控制的，默认情况下该值为10秒。

• 查看：SHOW VARIABLES LIKE "%long_query_time%"

1.3 设置阈值参数

• 设置：SET GLOBAL long_query_time = 3

注意：修改完毕后需重新连接会话

1.4 测试与查看

输入select sleep(4);来模拟一个慢查询，然后打开slow_query_log_file处的慢查询日志，查看慢查询SQL。

• 使用SHOW GLOBAL STATUS LIKE "%slow_queries%"命令查看当前系统中有多少条慢查询记录。

1.5 配置文件

如果要写配置文件，配置如下

slow_query_log = 1
slow_query_log_file=/xxx/mysql/name-slow.log
long_query_time=3
log_output=xxx

1.6 日志分析工具

在生产环境中，如果要手动分析日志，查找、分析SQL比较麻烦，MySQL提供了日志分析工具mysqldumpslow。

2. 批量数据脚本

2.1 建表

CREATE TABLE dept(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT "",
    loc VARCHAR(13) NOT NULL DEFAULT "" /*楼层*/
)ENGINE = INNODB DEFAULT CHARSET = GBK;

CREATE TABLE emp(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
    ename VARCHAR(20) NOT NULL DEFAULT "", 
    job VARCHAR(9) NOT NULL DEFAULT "",
    mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*上级编号*/
    hiredate DATE NOT NULL, /*入职时间*/
    sal DECIMAL(7,2) NOT NULL, /* 薪水*/
    comm DECIMAL(7,2) NOT NULL, /*红利*/
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0/*部门编号*/
)ENGINE = INNODB DEFAULT CHARSET=GBK;

2.2 设置参数

为了避免创建函数时报错This function has none of DETERMINTSTIC....，这里需设置一些参数，具体如下：

• 查看：show variables like "%log_bin_trust_function_creators";
• 设置：SET GLOBAL log_bin_trust_function_creators = 1;

2.3 创建函数

需创建随机生成字符串和随机部门编号函数

• 随机字符串函数

DELIMITER $$ # 设置自定义结束符为$$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnobqrstuvxyzABCDEFGHIJKLMNOBQRSTUVWXYZ';
  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 $$

• 随机部门编号函数

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

2.4 创建存储过程

为插入数据创建两个存储过程

• 员工表插入数据存储过程

delimiter $$
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,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$

• 部门表插数据存储过程

delimiter $$
create procedure insert_dept(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 dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$

2.5 调用存储过程

为了后续学习查询优化，这里模拟100w数据。

• 插入10个部门信息

DELIMITER ;
CALL insert_dept(100,10)

• 插入员工信息，或者50w一次分2次。

DELIMITER ;
call insert_emp(100001,1000000);

3. Show Profile

ShowProfile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况；可以用于SQL的调优测量。

官网链接

3.1 分析步骤

3.1.1 查看与开启

• 查看：SHOW VARIABLES LIKE "profiling";
• 开启：set profiling=on;

3.1.2 运行SQL

• select * from emp group by id%20 order by 5;

3.1.3 查看结果

Show Profile默认存储最近15次的运行结果。

• show profiles;

3.1.4 查看诊断结果

• show profile cpu,block io for query+前面查询SQL的Query_ID

• 关于诊断参数

  • All：显示所有的开销信息
  • Block IO：显示块IO相关开销
  • Context Switches：上下文切换相关开销
  • CPU：显示CPU相关开销信息
  • IPC：显示发送和接收相关开销信息
  • Memory：显示内存相关开销信息
  • Page Faults：显示页面错误相关开销信息
  • Source：显示和Source_function、Source_file、Source_line相关开销信息
  • Swaps：显示交换次数相关开销信息

• 开发中必须注意的几点Status信息：

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

4. 全局查询日志

该选项禁止在生成环境中开启！

4.1 配置启用

直接在配置文件中配置如下：

#开启
general_log=1
#记录日志文件的路径
general_log_file=xxx #本地文件路径
#输出格式
log_output=file

4.2 编码启用

在MySQL命令行中开启

• 查看是否启用：show variables like "%general_log%";
• 开启：set global general_log=1;
• 设置输出格式：set global log_output='TABLE';
• 查看日志：SELECT * FROM mysql.general_log;