数据库原理以及SQL优化（二）：慢查询日志_500 rows retrieved starting from-CSDN博客

1 慢查询日志

慢查询日志是MySQL内置的一项功能，可以记录执行超过指定时间的SQL语句

2 发现慢SQL的工具

Skywalking
VisualVm
JavaMelody

3 慢查询日志相关参数及默认值

参数	作用	默认值
log_output	日志输出到哪儿，默认FILE，表示文件;设置成TABLE，则将日志记录到mysql.slow_log中。也可设置多种格式，比如fIle, TABLE	FILE
longquerytime	执行时间超过这么久才记录到慢查询日志，单位秒，可使用小数表示小于秒的时间	10
logqueriesnotusingindexes	是否要将未使用索引的SQL记录到慢查询日志中，此配置会无视longquerytime的的配置。生产环境建议关闭;开发环境建议开启。	OFF
logthrottlequeriesnotusing_indexes	和logqueriesnot using indexes配合使用，如果log queriesnotusing indexes打开，则该参数将限制每分钟写入的、未使用索引的SQL数量。	0
minexaminedrow_limit	扫描行数至少达到这么多才记录到慢查询日志	0
logslowadmin_statements	是否要记录管理语句，默认关闭。管理语句包括ALTER TABLE, ANALYZE TABLE,CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE。	OFF
slowquerylog_file	指定慢查询日志文件路径	/var路径
logslowslave_statements	该参数在从库上设置，决定是否记录在复制过程中超过longquerytime的SQL。如果binlog格式是row,则该参数无效	OFF
logslowextra	当logoutput=FILE时，是否要记录额外信息(MySQL 8.0.14开始提供)，对logoutput=TABL E的结果无影响。	OFF

4 使用方式

4.1 是否开启慢日志

默认情况下，MySQL并不开启慢查询日志，需要手动开启

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF    |
+----------------+-------+
1 row in set (0.02 sec)
————————————————

4.2 开启慢日志

4.2.1 修改配置文件

#修改配置文件my.cnf, 在[mysqld]段落中加入如上参数即可#

[mysqld]
log_output = 'FILE,TABLE';
slow_query_log = 'ON';
long_query_time =0.001;

#然后重启MySQL#

service mysqld restart

4.2.2 修改全局变量

这种方式无需重启即可生效，但一旦重启，配置又会丢失

# 设置慢查询日志同时记录到文件以及mysql.slow_log表中
set global log_output = 'FILE,TABLE';
set global slow_query_log = 'ON';
set global long_query_time =0.001;
set global log_queries_not_using_indexes = 'ON';

5 慢日志分析

5.1 产生慢日志

# 全表扫描查询不走索引，所以会记录在慢日志中
select * from employees;

employees> select * from employees
[2022-08-11 16:13:31] 500 rows retrieved starting from 1 in 78 ms (execution: 6 ms, fetching: 72 ms)

# 查询下慢日志
select * from mysql.slow_log;

2022-08-11 16:13:31.732006,root[root] @ localhost [127.0.0.1],00:00:00.001273,00:00:00.000271,501,501,employees,0,0,1,/* ApplicationName=IntelliJ IDEA 2022.2 */ select * from employees,36

5.2 日志表查看

当log_output = TABLE时

列名称	列	数据
开始时间	start_time	2022-08-11 16:17:55.341092
用户及IP	user_host	`root[root] @ localhost [127.0.0.1]`
查询花费时间	query_time	00:00:00.001273
查询持有锁的时间	lock_time	00:00:00.000271
返回的数据条数	rows_sent	501
扫描了多少条数据	rows_examined	501
当前执行的SQL	sql_text	/* ApplicationName=IntelliJ IDEA 2022.2 / select from employees
执行这条SQL线程ID	thread_id	36

5.3 日志文件查询

5.3.1 慢日志文件位置

show variables like '%slow_query_log_file%';

在这里插入图片描述

5.3.2 mysqldumpslow分析

当log_output = FILE时，可使用 mysqldumpslow 分析

~ ❯ mysqldumpslow --help                                               18:01:08
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           展示更详细的信息
  -d           debug
  -s ORDER     ORDER 以哪种方式排序，默认at
                al: 平均锁定时间
                ar: 平均返回记录数
                at: 平均查询时间
                 c: 访问计数
                 l: 锁定时间
                 r: 返回记录
                 t: 查询时间  
  -r           将-s的排序倒序
  -t NUM       top n的意思，展示最前面的几条
  -a           不去将数字展示成N，将字符串展示成’ s ’
  -n NUM       名称中至少有n位的抽象数
  -g PATTERN   后边可以写一个正则，只有符合正则的行会展示
  -h HOSTNAME  慢查询日志以主机名-slow. log的格式命名，-h可指定读取指定主机名的慢查询日志，默认情况下是*，读取所有的慢查询日志
  -i NAME      MySQL Server的实例名称(如果使用了mysql . server startup脚本的话)
  -l           不将锁定时间从总时间中减去

示例：