Mysql优化之慢查询

什么是慢查询

慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过 long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

慢查询配置

配置文件修改

通过修改Mysql配置文件开启慢查询日志，这里以Window 为例，找到Mysql的安装目录下的配置文件my.ini

[mysqld]

# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory

# shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use
# socket=MYSQL

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Path to installation directory. All paths are usually resolved relative to this.
# basedir="C:/Program Files/MySQL/MySQL Server 5.6/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.6/Data

# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server=

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=FILE
general-log=0
general_log_file="DESKTOP-MOP0GDT.log"
slow-query-log=1
slow_query_log_file="DESKTOP-MOP0GDT-slow.log"
long_query_time=0

slow_query_log 是否开启慢查询日志，1表示开启，0表示关闭。
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间伐值（单位：秒，默认10秒），为了测试方便我们修改为0
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

修改配置文件后，要使配置生效，需求重启Mysql服务，Win+R 输入 services.msc 回车打开服务

在服务列表中，找到mysql 右键重启服务

通过SQL命令开启慢查询

1
2
3

set global slow_query_log = 1; --开启慢查询日志
set global long_query_time=0;
set global log_output='FILE,TABLE'

通过命令设置开启慢查询不需要重启Mysql服务可以使设置立即生效，如果重启Mysql服务设置的参数将被还原，查看设置后的参数

show VARIABLES like '%slow_query_log%'

show VARIABLES like '%slow_query_log_file%'

show VARIABLES like '%long_query_time%'

show VARIABLES like '%log_queries_not_using_indexes%'

show VARIABLES like 'log_output'

慢查询解读

当我们开启了慢查询，默认情况下，慢查询日志文件保存在与数据库数据文件同级目录中，这里我们找到生成的慢查询日志文件

从慢查询日志里面摘选一条慢查询日志，数据组成如下

把方便解读放，慢查询格式显示

第一行：用户名、用户的IP信息、线程ID号
第二行：执行花费的时间【单位：毫秒】
第三行：执行获得锁的时间
第四行：获得的结果行数
第五行：扫描的数据行数
第六行：这SQL执行的具体时间
第七行：具体的SQL语句

慢查询日志分析工具

我们开启慢查询的目的就是通过慢查询日志来找出有问题的SQL语句，对其进行优化。随着mysql数据库服务器运行时间的增加，数据库表记录的增多，可能会有越来越多的SQL语句被记录到了慢查询日志文件中，慢查询的日志记录非常多，要从里面找寻一条查询慢的日志并不是很容易的事情，一般来说都需要一些工具辅助才能快速定位到需要优化的SQL语句。下面介绍两个慢查询辅助工具

ActivePerl一个perl脚本解释器，工具下载地址https://www.activestate.com/products/perl/downloads/ 安装完成后，我们就可以在命令行中使用perl命令了

进入Mysql 安装目录bin路径下，使用mysql自带的mysqldumpslow.pl 执行perl命令如下

语法：

perl mysqldumpslow.pl -s r -t 5 d:\DESKTOP-MOP0GDT-slow.log

-s order (c,t,l,r,at,al,ar) 
         c:总次数
         t:总时间
         l:锁的时间
         r:总数据行
         at,al,ar  :t,l,r平均数  【例如：at = 总时间/总次数】

  -t  top   指定取前面作为结果输出

通过如上命令，我们能够方便的查找出top前5条需要待优化的记录如下。