Mysql学习-05 查询结果分析

浅浅的花香味﹌ 2024-02-19 13:30 21阅读 0赞

**慢查询日志：**

1.介绍

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。  
具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。long\_query\_time的默认值为10，意思是运行10秒以上的语句。  
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

2.设置

**2.1 说明**

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

**2.2查看是否开启及如何开启**

默认情况下slow\_query\_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow\_query\_log的值来开启  
**SHOW VARIABLES LIKE '%slow\_query\_log%';**

使用**set global slow\_query\_log=1**开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

![20181118154741162.png][] ![20181118154754631.png][]

全局变量设置，对当前连接不影响

![20181118154821639.png][]

对当前连接立刻生效

![2018111815483712.png][]

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，\[mysqld\]下增加或修改参数  
slow\_query\_log 和slow\_query\_log\_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件  
 **slow\_query\_log =1  
slow\_query\_log\_file=/var/lib/mysql/atguigu-slow.log**  
关于慢查询的参数slow\_query\_log\_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host\_name-slow.log（如果没有指定参数slow\_query\_log\_file的话）

**2.3 开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？**

这个是由参数long\_query\_time控制，默认情况下long\_query\_time的值为10秒，命令：**SHOW VARIABLES LIKE 'long\_query\_time%';**

可以使用命令修改，也可以在my.cnf参数里面修改。假如运行时间正好等于long\_query\_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long\_query\_time，而非大于等于。

**2.4 案例**

查看当前多少秒算慢 -->SHOW VARIABLES LIKE 'long\_query\_time%';

使用命令  **set global long\_query\_time=1** 修改为阙值到1秒钟的就是慢sql

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70][]

修改后发现long\_query\_time并没有改变。

**需要重新连接或新开一个会话才能看到修改值。 SHOW VARIABLES LIKE 'long\_query\_time%';或者通过set session long\_query\_time=1来改变当前session变量;**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 1][]

**2.5 记录慢SQL并后续分析**

一条慢sql

![2018111815591756.png][]

跟踪日志信息

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 2][]

**2.6 查询当前系统中有多少条慢查询记录**

show global status like '%Slow\_queries%';

![20181118160049798.png][]

**2.7.配置**

【mysqld】下配置：  
slow\_query\_log=1;  
slow\_query\_log\_file=/var/lib/mysql/xxx-slow.log //慢查询日志路径  
long\_query\_time=3;//时间阈值  
log\_output=FILE //级别

3.日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

**3.1 查看mysqldumpslow的帮助信息 mysqldumpslow--help**

-s: 是表示按照何种方式排序；
    c: 访问次数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间
    -t:即为返回前面多少条的数据；
    -g:后边搭配一个正则匹配模式，大小写不敏感的；

**3.2 常用sql**

得到返回记录集最多的10个SQL
    mysqldumpslow -s r -t 10 /var/lib/mysql/jaxk-slow.log
     
    得到访问次数最多的10个SQL
    mysqldumpslow -s c -t 10 /var/lib/mysql/jaxk-slow.log
     
    得到按照时间排序的前10条里面含有左连接的查询语句
    mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/jaxk-slow.log
     
    另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
    mysqldumpslow -s r -t 10 /var/lib/mysql/jaxk-slow.log | more

**全局查询日志:（尽量不要在生产环境开启这个功能。）**

**1.1 配置启用**  
在mysql的my.cnf中，设置如下：  
\#开启 general\_log=1  
\# 记录日志文件的路径 general\_log\_file=/path/logfile  
\#输出格式 log\_output=FILE

**1.2 编码启用**

命令 set global general\_log=1;  
\#全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中

set global log\_output='TABLE';  
此后 ，你所编写的sql语句，将会记录到mysql库里的general\_log表，可以用下面的命令查看  
select \* from mysql.general\_log;

**批量数据脚本:**

往表里插入50W数据

建表：
     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;

设置参数log\_bin\_trust\_function\_creators

创建函数，假如报错：This function has none of DETERMINISTIC......
    # 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
     
    show variables like 'log_bin_trust_function_creators';
     
    set global log_bin_trust_function_creators=1;
     
    # 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
     
    windows下my.ini[mysqld]加上log_bin_trust_function_creators=1 
     
    linux下    /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

创建函数,保证每条数据都不同

随机产生字符串 
    DELIMITER $$
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    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 $$
     
    #假如要删除
    #drop function rand_string;

随机产生部门编号
     
    #用于随机产生多少到多少的编号
    DELIMITER $$
    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$$ 
     
    #假如要删除
    #drop function rand_num;

创建存储过程

创建往emp表中插入数据的存储过程
     存储过程将方法放置到SQL语句中。然后执行存储过程，就相当于执行
     
    DELIMITER $$
    CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
    BEGIN  
    DECLARE i INT DEFAULT 0;   
    #set autocommit =0 把autocommit设置成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$$ 
     
    #删除
    # DELIMITER ;
    # drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程
     
    #执行存储过程，往dept表添加随机数据
    DELIMITER $$
    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$$
     
    #删除
    # DELIMITER ;
    # drop PROCEDURE insert_dept;

调用存储过程  
dept \#执行存储过程，往dept表添加1万条数据 DELIMITER ;CALL insert\_dept(10000);  
emp \#执行存储过程，往emp表添加50万条数据 DELIMITER ; CALL insert\_emp(100000,500000);

**Show Profile：执行资源消耗**

mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量,默认情况下，参数处于关闭状态，并保存最近15次的运行结果。官网：[https://dev.mysql.com/doc/refman/8.0/en/show-profile.html][https_dev.mysql.com_doc_refman_8.0_en_show-profile.html]

分析步骤：

1.是否支持，看看当前的mysql版本是否支持

Show variables like 'profiling'; 默认是关闭，使用前需要开启

![20181118205847521.png][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 3][]

3.运行SQL  
select \* from emp group by id%10 limit 150000;  
select \* from emp group by id%20 order by 2

4.查看结果，show profiles;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 4][]

5.诊断SQL，show profile cpu,block io for query n (n为上一步前面的问题SQL数字号码);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 5][]

6.日常开发需要注意的结论  
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。  
Creating tmp table 创建临时表 拷贝数据到临时表，用完再删除  
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！  
locked

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 6][]

**SHOW PROCESSLIST：**  
能干什么：查询所有用户正在干什么，如果出现不顺眼的，kill \[id\]

[20181118154741162.png]: https://img-blog.csdnimg.cn/20181118154741162.png
[20181118154754631.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e1ab8871aa5e4bb3aa6b7311465704d7.png
[20181118154821639.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/33473f74a6d746768f6053d231a1c26d.png
[2018111815483712.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/291ec64f8e4a4e0fbca4d71263724332.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/19f9461648d94db58124ddeabc96a626.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/09a5464f975443b9bb5bcbcac4615ae7.png
[2018111815591756.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/552ad3300a8346f8900ed28a96b5f175.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5f8316539b4b482d98dd6be756bc023f.png
[20181118160049798.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f2e331e188d74b91b314fde347f0d3df.png
[https_dev.mysql.com_doc_refman_8.0_en_show-profile.html]: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
[20181118205847521.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2a9eec60332b49b5b8c49a2ede80c1a5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/daf968b012634e9ca21947728eeea644.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b4186c2bf3d9459d899dba6b96d8b697.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f1d21e3e66ff4d3f87843ce144b50fa9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/34c4c0859ad7400a8832b0d55a9df32e.png