MySQL开启慢查询

一 开启和查看mysql 慢查询日志

（1）命令方式

 

set global log_queries_not_using_indexes=on ;

set global slow_query_log_file='/var/log/mysql/mysql-slow'.log;

set global long_query_time=0.1;

set global slow_query_log=on ;

 

注意事项1：

    设置slow_query_log_file时，目录必须存在，并且mysql 有权读写该目录，

 

mkdir /var/log/mysql

chown mysql:mysql /var/log/mysql 

 

否则报如下错误：

mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';

ERROR 1231 (42000): Variable 'slow_query_log_file' can't be set to the value of '/var/log/mysql/mysql-slow.log'

 

注意事项2：

     设置long_query_time参数时，需要重新连接才能生效，不需要重启服务。

     否则通过 show variables like ‘long_query_time’ 查看不到变化

 

（2）文件方式

在[mysqld] 部分添加如下信息

 

#打开不使用索引的sql日志记录

log_queries_not_using_indexes=on

 

#设置慢查询日志记录位置

slow_query_log_file=/var/log/mysql/mysql-slow.log

 

#超过多少秒算慢查询，需要记录

long_query_time=0.1

 

#打开慢查询日志

slow_query_log=on

 

 

注：

a）mysql客户端和服务端程序加载配置文件顺序和路径如下

/etc/my.cnf -->  /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf -->  ~/.my.cnf （--defaults-file= 自己指定的参数文件）

 

b）mysql采用集中式的配置文件，配置文件是分块的，以 [NAME] 独立行开始，到下一个[NAME] 独立行结束属于作用于NAME指定的程序的配置。

对于里面的具体参数有如下两点需要记住：

1.在同一块[NAME]中每个参数可以重复出现，对于这种重复的参数默认排在下面的参数覆盖上面的参数。

2.每个[NAME]块中的参数都是默认参数值，只有在NAME命令不带这些参数时才会生效。

比如 [mysql]中定义的port=3306，

当直接用mysql -uuser -ppasswd而不带-P端口参数时，默认连的端口就是3306；

当用mysql -uuser -ppasswd -P3307则不会去连接3306端口，而是连接3307端口。其他模块的参数以此类推。

3.默认情况下，MySQL只是使用恰好够用的资源启动。所以MySQL的这些默认值，一般情况下有些关键参数满足不了生产库的需求。

4.明确一点“没有一个适合所有场景的最佳配置文件”。

 

c）

[client] 是所有mysql客户端程序读取的配置块。针对的是MySQL客户端（不包含mysql命令，指的是具体应用程序，比如java的jdbc，C的mysql驱动等等）

[server]是所有服务端如mysqld会读取的配置块。一般不用设置，完全由[mysqld]替换。

[mysqldump]则是只有 mysqldump命令才会读取的配置文件，

[mysql] 是mysql命令这个客户端程序的配置块，

[mysqld]是mysql服务端程序mysqld 和 mysqld_safe 。在多实例环境下[mysqld3306]和[mysqld3307]可能会覆盖[mysqld]中的参数。

[mysqld_safe]针对mysqld_safe命令生效。

[mysqldump]针对mysqldump命令生效

[mysqladmin]针对mysqladmin命令生效

[mysqld_multi]针对mysqld_multi，管理MySQL单机多实例。

[mysqld3306]mysqld_multi管理的3306实例启动时mysqld读取的参数(也会读[mysqld]的参数，可以被覆盖)

[mysqld3307]mysqld_multi管理的3307实例启动时mysqld读取的参数(也会读[mysqld]的参数，可以被覆盖)

 

 

（3）查看

    slow_query_log

    show variables like 'log_queries_not_using_indexes';

    show variables like 'slow_query_log_file';

    show variables like 'long_query_time';

    show variables like 'slow_query_log';

 

    或

    show variables like '%log%';

 

 二 慢查询日志分析

 

# Time: 2016-07-17T08:37:58.844558Z

# User@Host: root[root] @ localhost []  Id:     3

# Query_time: 0.000211  Lock_time: 0.000093 Rows_sent: 3  Rows_examined: 3

SET timestamp=1468744678;

select * from user;

 

a）查询执行时间 主要是时间戳的形式

b）用户及主机信息

c）SQL的执行信息：查询时间，锁时间，行数

d）SQL的执行时间

e）执行的查询语句

 

3）慢查询是日志分析工具

——用于对海量查询日志进行分析：mysqldumpslow，pt-query-digest

（1）mysqldumpslow 

 

（a）使用时报如下错误：

 

bash: /usr/local/mysql/bin/mysqldumpslow: /usr/bin/perl: bad interpreter: No such file or directory

 

解决方案：按照 perl

 

yum -y install perl perl-devel

 

（b）使用：

 

[root@64669e3cbf05 etc]# mysqldumpslow -s at -t 1 /var/log/mysql/mysql-slow.log

 

Reading mysql slow query log from /var/log/mysql/mysql-slow.log

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

  Time: N-N-17T08:N:N.908977Z

  # User@Host: root[root] @ localhost []  Id:     N

  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N

  SET timestamp=N;

  show tables

 

注：

     -s ：排序字段

     -t  ：查看top多少

 

只是做了初步的整理，比less直接看能找到最长度的

 

主要方法：1）找寻查询次数多，且时间长的sql，占用总时间百分比大的sql。

                   2）IO大的SQL：主要集中在扫描的行数越多，IO开销越大

                   3）索引命中率低的SQL：主要是扫描的记录数远远大于输出的记录数。