【第15天】MYSQL进阶-查询优化-慢查询日志（SQL 小虚竹)

2023-11-08 20:50
文章标签 sql 进阶 mysql 日志 15 查询 优化 database 虚竹

本文主要是介绍【第15天】MYSQL进阶-查询优化-慢查询日志（SQL 小虚竹)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

回城传送–》《100天精通MYSQL从入门到就业》

文章目录

  • 零、前言
  • 一、练习题目
  • 二、SQL思路：SQL进阶-查询优化-慢查询日志
    • 初始化数据
    • 解法
      • 慢查询日志是什么
      • 如何开启慢查询日志
        • 第一种方式
          • windows实战
            • slow_query_log 一直为off，没生效
            • 验证
          • linux实战
            • 验证
        • 第二种方式
          • 实操
          • 验证
      • 关闭慢查询日志
        • 第一种方式
          • windows实战
            • 验证
          • linux实战
            • 验证
        • 第二种方式
      • 扩展
        • 慢查询日志失效原因一：在线动态设置long_query_time，对当前已建立的连接不会生效
        • 慢查询日志失效原因二：未使用索引的 SQL 记录不会写入慢查询日志
        • 慢查询日志失效原因三：慢sql里有锁等待
        • 慢查询日志失效原因四：默认不记录管理类命令的慢sql
        • 慢查询日志失效原因五：min_examined_row_limit为非0
        • 慢查询日志失效原因六：slow log文件句柄发生了变化
        • 慢查询日志失效原因七：从库的复制语句默认不记录
  • 三、总结
  • 四、参考

零、前言

今天是学习 SQL 打卡的第 15 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

​ 我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-查询优化-慢查询日志

一、练习题目

题目链接难度
SQL进阶-查询优化-慢查询日志★★★☆☆

二、SQL思路：SQL进阶-查询优化-慢查询日志

在这里插入图片描述
在这里插入图片描述

初始化数据

drop table if exists student;
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`order_num` long NOT NULL  COMMENT '序号',
`student_name` varchar(20) NOT NULL COMMENT '姓名',
`age` int COMMENT '年龄',PRIMARY KEY (`id`))ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

插入100万条数据,这里使用了存储过程，进行批量提交数据，先关闭自动提交，插入一定条数再进行提交。

--创建存储过程drop procedure if exists add_student;CREATE  PROCEDURE `add_student`(in n int,in batchNum int) 
BEGINDECLARE i INT DEFAULT 1;WHILE (i < n+1 ) DOset autocommit = 0;INSERT into student (order_num,student_name,age) VALUES (i,concat('student_name',i),20);set i=i+1;if i mod batchNum = 0 thencommit;end if;END WHILE;commit;
END
-- 调用
CALL add_student(1000000,100000)

解法

要求处理：

  • 从四个选项中选择一个正确答案

分析：

慢查询日志是什么

mysql支持执行超过一定时间long_query_time 的sql语句记录到日志里，方便开发或dba人员去优化sql语句。超过一定时间：这个是可以由参数long_query_time 进行配置。

如何开启慢查询日志

第一种方式

可以在mysql的配置文件里开启慢查询日志。
mysql中“my.ini”文件：

  • 1、linux系统中，默认该文件的位置是“/etc/mysql/my.cnf”；
  • 2、windows系统中，默认该文件的位置是“C:\ProgramData\MySQL\MySQL Server **\Data\my.ini”。

其中C:\ProgramData是隐藏目录，需要显示隐藏目录才能找到。

[mysqld]
slow_query_log = 1
slow_query_log_file = /data/mysql/log/query_log/slow_statement.log
long_query_time = 10
log_output = FILE

配置项说明：

slow_query_log:1代表开启慢查询日志。0：代表关闭慢查询日志。
slow_query_log_file：慢查询日志保存的地方和对应的文件名。
long_query_time：指定SQL语句执行时间超过多少秒时记录慢查询日志。
log_output：File:表示将日志存入文件,默认值是’FILE’；Table:表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log表中。
经验之谈：当记录到数据表中时，则数据表中记录的慢查询时间只能精确到秒；如果是记录到日志文件中，则日志文件中记录的慢查询时间能够精确到微秒。
建议在实际工作中，将慢查询日志记录到文件中。

重点：配置完成后，要重启mysql服务器！配置才会生效！

windows实战

默认该文件的位置是“C:\ProgramData\MySQL\MySQL Server **\Data\my.ini”。
找到文件：
在这里插入图片描述
修改my.ini配置：

# General and Slow logging.
slow_query_log=1
slow_query_log_file="d:/build/mysql/slow_statement.log"
long_query_time=10
log_output=FILE

在这里插入图片描述

保存配置后，重启mysql服务。

使用管理员权限打开cmd,在cmd输入以下命令，然后回车：

 net stop mysql57

在这里插入图片描述
注：不同的mysql版本，服务名可能不一样，可以去服务里找对应的服务名
在这里插入图片描述
重启mysql服务。

net start mysql57

在这里插入图片描述

查看配置是否生效：

show variables like '%slow_query_log%'; 
show variables like '%long_query_time%'; 
show variables like '%log_output%';

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

slow_query_log 一直为off，没生效

咦，就slow_query_log 参数没有生效，检查了参数名，没有写错，那会是什么原因呢？

虚竹哥查了下资料，发现在linux下：slow_query_log_file所在的目录需要MySQL的运行帐号的可写权限，如需变更日志文件目录记得给新目录授予777权限，否则slow_query_log一直为OFF

从这个思路扩展一下，会不会mysql也不会帮忙创建不存在的目录。

slow_query_log_file="d:/build/mysql/slow_statement.log"

在这里插入图片描述
果然没有帮忙创建目录。尝试把目录补上去，再重启下服务试试：
果然生效了
在这里插入图片描述

慢查询日志文件也生成出来了
在这里插入图片描述
画重点：slow_query_log_file指定的文件目录一定要存在的！如果不存在，要手动先创建目录，再重启服务！
画重点：slow_query_log_file指定的文件目录一定要存在的！如果不存在，要手动先创建目录，再重启服务！
画重点：slow_query_log_file指定的文件目录一定要存在的！如果不存在，要手动先创建目录，再重启服务！

验证

执行sql语句，触发慢查询机制：

SELECT BENCHMARK(99999999, MD5('mysql'));

在这里插入图片描述
查看日志内容，如图，已经记录成功：
在这里插入图片描述

linux实战

先创建慢查询日志的存放目录：

mkdir /usr/local/mysql/data/query_log

给目录读写权限

chmod 777 query_log/

在这里插入图片描述

默认该文件的位置是“/etc/mysql/my.cnf”。
虚竹哥自定义my.cnf的位置：/etc/my.cnf
SQL快速入门-安装MYSQL环境（多环境企业级指导）
找到文件：
在这里插入图片描述

修改my.cnf配置：

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/query_log/slow_statement.log
long_query_time = 10
log_output = FILE

在这里插入图片描述
保存配置后，重启mysql服务。
查看下mysql服务状态：

systemctl status mysqld

在这里插入图片描述
服务是开启的,先关闭,再启动 。

systemctl stop mysqldsystemctl start mysqld

再查看下mysql服务状态:
在这里插入图片描述
查看配置是否生效：

show variables like ‘%slow_query_log%’;
show variables like ‘%long_query_time%’;
show variables like ‘%log_output%’;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
慢查询日志文件也生成出来了
在这里插入图片描述
画重点：slow_query_log_file指定的文件目录一定要存在的！如果不存在，要手动先创建目录，再重启服务！
画重点：slow_query_log_file指定的文件目录一定要存在的！如果不存在，要手动先创建目录，再重启服务！
画重点：slow_query_log_file指定的文件目录一定要存在的！如果不存在，要手动先创建目录，再重启服务！

验证

执行sql语句，触发慢查询机制：

SELECT BENCHMARK(99999999, MD5('mysql'));

在这里插入图片描述

查看日志内容，如图，已经记录成功：
在这里插入图片描述

第二种方式

在MySQL命令行中执行命令开启慢查询日志：

SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/data/mysql/log/query_log/slow_statement.log';
SET GLOBAL long_query_time = 10;
SET GLOBAL log_output = 'FILE';

重点：这种方式不需要重启mysql服务。MySQL重启后会失效。

这种方式不用区分什么操作系统

实操

先查看下慢查询日志的相关参数

show variables like '%slow_query_log%'; 
show variables like '%long_query_time%'; 
show variables like '%log_output%';

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
我们看到slow_query_log 参数值为OFF，说明慢查询日志未开启。
在MySQL命令行中执行命令开启慢查询日志，其他参数就不设置了，第一种方式有配置了：

SET GLOBAL slow_query_log = 1;

再看下慢查询日志的相关参数，已经开启

show variables like '%slow_query_log%';

在这里插入图片描述

同时也生成了慢查询日志文件：
在这里插入图片描述

验证

执行sql语句，触发慢查询机制：

SELECT BENCHMARK(99999999, MD5('mysql'));

查看日志内容，如图，已经记录成功：
在这里插入图片描述

关闭慢查询日志

关闭慢查询日志，只需要在my.cnf文件或者my.ini文件中配置slow_query_log=0或者直接删除此选项即可。

第一种方式
windows实战

修改my.ini配置：

# General and Slow logging.
slow_query_log=0

在这里插入图片描述

保存配置后，重启mysql服务。

使用管理员权限打开cmd,在cmd输入以下命令，然后回车：

net stop mysql57

在这里插入图片描述
注：不同的mysql版本，服务名可能不一样，可以去服务里找对应的服务名
在这里插入图片描述
重启mysql服务。

net start mysql57

在这里插入图片描述

手动把之前的慢查询日志先删除：
在这里插入图片描述
查看配置是否生效：

show variables like '%slow_query_log%';

配置生效：
在这里插入图片描述

验证

执行sql语句，看是否还会触发慢查询机制：

SELECT BENCHMARK(99999999, MD5('mysql'));

结果关闭慢查询日志成功：
在这里插入图片描述

linux实战

修改my.cnf配置：

slow_query_log=0

在这里插入图片描述
保存配置后，重启mysql服务。
查看下mysql服务状态：

systemctl status mysqld

在这里插入图片描述
服务是开启的,先关闭,再启动 。

systemctl stop mysqldsystemctl start mysqld

再查看下mysql服务状态:
在这里插入图片描述
手动把之前的慢查询日志先删除：
在这里插入图片描述

查看配置是否生效：

show variables like '%slow_query_log%';

配置生效：
在这里插入图片描述

验证

执行sql语句，看是否还会触发慢查询机制：

SELECT BENCHMARK(99999999, MD5('mysql'));

在这里插入图片描述
结果关闭慢查询日志成功：
在这里插入图片描述

第二种方式

在MySQL命令行中执行命令关闭慢查询日志：

SET GLOBAL slow_query_log = 0;

慢查询阀值是10s,改为1s.

set global long_query_time= 1;

修改之后，建议重新开一个会话查询（旧会话查询出的值可能是原来的10s）
重点：这种方式不需要重启mysql服务。MySQL重启后会失效。

扩展

慢查询日志失效原因一：在线动态设置long_query_time，对当前已建立的连接不会生效

查看慢查询日志是否有开启

show variables like '%slow_query_log%'; 
show variables like '%long_query_time%';

在这里插入图片描述
在这里插入图片描述

是已经开启的。如果没有开启，请按上文介绍的方式操作，这里不再赘述。
慢查询阀值是10s,改为1s.

SET GLOBAL long_query_time = 1;

修改之后，建议重新开一个会话查询（旧会话查询出的值可能是原来的10s）
在这里插入图片描述

慢查询日志失效原因二：未使用索引的 SQL 记录不会写入慢查询日志

查看设置，默认是关闭的。

show variables like 'log_queries_not_using_indexes';

在这里插入图片描述
查看慢查询日志是否有开启

show variables like '%slow_query_log%'; 
show variables like '%long_query_time%';

在这里插入图片描述
在这里插入图片描述

是已经开启的。如果没有开启，请按上文介绍的方式操作，这里不再赘述。

测试sql
检查student 表的索引：

show index from student;

在这里插入图片描述
执行没有使用索引的sql:

select *
from student
where student_name ='student_name446741'

在这里插入图片描述

慢查询日志中，并没有记录到。
在这里插入图片描述
设置没使用索引的 SQL 记录写入慢查询日志：

set global log_queries_not_using_indexes=on;

在这里插入图片描述
再执行慢查询的sql

select *
from student
where student_name ='student_name446741'

在这里插入图片描述
没有索引的数据也添加到慢查询日志了。

注：log_queries_not_using_indexes参数要跟参数log_throttle_queries_not_using_indexes配合使用

log_throttle_queries_not_using_indexes：该参数决定每分钟记录未使用索引的SQL的数量上限，因为未使用索引的SQL可能会非常多，导致慢日志空间增长飞快。

慢查询日志失效原因三：慢sql里有锁等待

慢SQL里有大量锁等待，慢SQL的执行时间不包含锁等待的时间

慢查询日志失效原因四：默认不记录管理类命令的慢sql

log_slow_admin_statements=0，因此alter, create index, analyze table等操作即使超过 long_query_time，也不会记录到慢日志中。
log_slow_admin_statements 该参数决定是否记录管理类的命令，有 ALTER TABLE,ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE,REPAIR TABLE，默认是不记录这一类语句到慢日志。
在这里插入图片描述

慢查询日志失效原因五：min_examined_row_limit为非0

min_examined_row_limit 该参数定义一个SQL所读取的数据行数；
min_examined_row_limit设置为非0值，SQL检查行数未超过该值，也不会记录。
默认值为0
在这里插入图片描述

慢查询日志失效原因六：slow log文件句柄发生了变化

slow log文件钻句柄发生了变化，如运行期间用vim打开log，最后又保存退出，此时文件句柄发生变化，需要执行flush slow logs。

慢查询日志失效原因七：从库的复制语句默认不记录

log_slow_slave_statements：该参数在从库上设置，决定是否记录在复制过程中超过long_query_time的SQL，如果binlog格式是row，则即使开启了该参数，也不会记录相关SQL。
除非binlog格式是statement且开启log_slow_slave_statements。

三、总结

本文介绍了慢查询日志在windows环境和Linux环境如何开启和关闭,开启和关闭分别介绍了两种方式来操作,一种是通过修改配置,重启服务生效;另一种是通过命令快速生效,但MySQL重启后会失效。
可以根据实际的业务场景进行选择。
额外补充了慢查询日志的七个失效原因，都是从实战中摸索出来的宝贵经验。
所以，嗯，这题的答案选。。评论区大声告诉虚竹哥。

四、参考

MySQL进阶技能树–>慢查询日志

我是虚竹哥，我们明天见~

这篇关于【第15天】MYSQL进阶-查询优化-慢查询日志（SQL 小虚竹)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/372431。

相关文章

一文详解MySQL如何设置自动备份任务

一文详解MySQL如何设置自动备份任务

《一文详解MySQL如何设置自动备份任务》设置自动备份任务可以确保你的数据库定期备份,防止数据丢失,下面我们就来详细介绍一下如何使用Bash脚本和Cron任务在Linux系统上设置MySQL数据库的自... 目录1. 编写备份脚本1.1 创建并编辑备份脚本1.2 给予脚本执行权限2. 设置 Cron 任务2
阅读更多...
SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
SpringBoot项目配置logback-spring.xml屏蔽特定路径的日志

SpringBoot项目配置logback-spring.xml屏蔽特定路径的日志

《SpringBoot项目配置logback-spring.xml屏蔽特定路径的日志》在SpringBoot项目中,使用logback-spring.xml配置屏蔽特定路径的日志有两种常用方式,文中的... 目录方案一：基础配置（直接关闭目标路径日志）方案二：结合 Spring Profile 按环境屏蔽关
阅读更多...
canal实现mysql数据同步的详细过程

canal实现mysql数据同步的详细过程

《canal实现mysql数据同步的详细过程》：本文主要介绍canal实现mysql数据同步的详细过程,本文通过实例图文相结合给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的... 目录1、canal下载2、mysql同步用户创建和授权3、canal admin安装和启动4、canal
阅读更多...
SQL中JOIN操作的条件使用总结与实践

SQL中JOIN操作的条件使用总结与实践

《SQL中JOIN操作的条件使用总结与实践》在SQL查询中,JOIN操作是多表关联的核心工具,本文将从原理,场景和最佳实践三个方面总结JOIN条件的使用规则,希望可以帮助开发者精准控制查询逻辑... 目录一、ON与WHERE的本质区别二、场景化条件使用规则三、最佳实践建议1.优先使用ON条件2.WHERE用
阅读更多...
MySQL存储过程之循环遍历查询的结果集详解

MySQL存储过程之循环遍历查询的结果集详解

《MySQL存储过程之循环遍历查询的结果集详解》：本文主要介绍MySQL存储过程之循环遍历查询的结果集,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言1. 表结构2. 存储过程3. 关于存储过程的SQL补充总结前言近来碰到这样一个问题：在生产上导入的数据发现
阅读更多...
MySQL 衍生表(Derived Tables)的使用

MySQL 衍生表(Derived Tables)的使用

《MySQL衍生表(DerivedTables)的使用》本文主要介绍了MySQL衍生表(DerivedTables)的使用,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学... 目录一、衍生表简介1.1 衍生表基本用法1.2 自定义列名1.3 衍生表的局限在SQL的查询语句select
阅读更多...
MySQL 横向衍生表(Lateral Derived Tables)的实现

MySQL 横向衍生表(Lateral Derived Tables)的实现

《MySQL横向衍生表(LateralDerivedTables)的实现》横向衍生表适用于在需要通过子查询获取中间结果集的场景,相对于普通衍生表,横向衍生表可以引用在其之前出现过的表名,本文就来... 目录一、横向衍生表用法示例1.1 用法示例1.2 使用建议前面我们介绍过mysql中的衍生表（From子句
阅读更多...
从基础到进阶详解Pandas时间数据处理指南

从基础到进阶详解Pandas时间数据处理指南

《从基础到进阶详解Pandas时间数据处理指南》Pandas构建了完整的时间数据处理生态,核心由四个基础类构成,Timestamp,DatetimeIndex,Period和Timedelta,下面我... 目录1. 时间数据类型与基础操作1.1 核心时间对象体系1.2 时间数据生成技巧2. 时间索引与数据
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2