mysql 设置慢查询时间_MySQL开启慢查询并分析原因

第一步.开启 MySQL 慢查询

方式一、修改配置文件

Windows：配置文件为 my.ini，一般在 MySQL 安装目录下或者 c:\Windows 下。

Linux：配置文件为 my.cnf ，一般在 /etc 下。

在 my.ini 增加下面代码:

[mysqld]

long_query_time=2

#5.5以前版本配置如下选项

log-slow-queries="mysql_slow_query.log"

#5.5及以上版本配置如下选项

slow-query-log=On

slow_query_log_file="mysql_slow_query.log"

log-query-not-using-indexes

第一句：定义超过多少秒的查询算是慢查询，这里定义的是2秒

第二句：定义慢查询日志的路径(注意如果是 Linux 或 Mac 系统要考虑权限问题)

第三句：记录下没有使用索引的query

方式二、通过命令行开启慢查询

上面的配置需要重启 mysql server 进程 mysqld 才会生效。但很多时候，尤其是产品生产环境，不希望每次修改都要重新启动mysql服务器，而是希望能在某些特定时间记录。MySQL5.1 之后为我们提供了灵活的运行时控制，使得你不必重新启动 mysql 服务器，也能选择性地记录或不记录某些 slow queries。

MySQL5.1中，提供了全局变量 slow_query_log、slow_query_log_file，可以灵活地控制enable/disable慢查询。同时可以通过 long_query_time 设置慢查询时间

注意:设置了全局变量 slow_query_log ,则变量 log_slow_queries 也会隐性地跟着改变

mysql>set global slow_query_log=ON

不幸的是,在 MySQL5.1 之前并没有提供类似的全局变量来灵活控制，但是我们可以通过将long_query_time 设置得足够大来避免记录某些查询语句。比如

mysql>set global long_query_time = 3600;

mysql>set global log_querise_not_using_indexes = ON;

MySQL5.0 在不关闭服务的情况下，希望不记录日志的办法是让日志文件成为 /dev/null 的符号链接(symbolic link)。注意:你只需要在改变后运行 flush logs 以确定MySQL释放当前的日志文件描述符，重新把日志记录到/dev/null。

和 MySQL5.0 不同, MySQL5.1 后可以在运行时改变日记行为，将日志记录到数据库表中。只要将 MySQL 全局变量 log_output 设置为 table 即可。MySQL 会将日志分别记录到mysql.gengera_log 和 mysql.slow_log 两张表中。但是，推荐的做法是将日志记录在日记文件中。

mysql> show variables like ‘log_output’\G

Variable_name: log_output

Value: FILE

mysql>set global log_output=’table’;

缺陷与审记

如果开启了 log_queries_not_using_indexes 选项，slow query 日志会充满过多的垃圾日志记录，这些快且高效的全表扫描查询(表小)会冲掉真正有用的slow queries记录。比如 select * from category 这样的查询也会被记录下来。

通过microslow-patch补丁可使用更细的时间粒度，和记录所有执行过的sql语句。不过，使用这个补订不得不自己编译MySQL，出于稳定性考滤，我们推荐在开发测试环境，可以打上这个补丁，享受这个补丁带来的便利。在运营环境尽量不要这么做…

第二步.验证慢查询是否开启

执行如下SQL语句来查看 mysql 慢查询的状态，结果会把是否开启慢查询、慢查询的秒数、慢查询日志等信息打印在屏幕上。

/*查看慢查询时间 */

show variables like "long_query_time";默认10s

/*查看慢查询配置情况 */

show status like "%slow_queries%";

/*查看慢查询日志路径 */

show variables like "%slow%";

第三步.执行一次慢查询操作

其实想要执行一次有实际意义的慢查询比较困难，因为在自己测试的时候，就算查询有20万条数据的海量表，也只需要零点几秒。我们可以通过如下语句代替：

SELECT SLEEP(10);

第四步.查看慢查询的数量

通过如下 sql 语句，来查看一共执行过几次慢查询：

show global status like '%slow%';

mysql日志的配置：

注意：这些日文件在mysql重启后才会生效

#记录所有sql语句

log=/usr/local/mysql/data/mysql.log

#记录数据库启动关闭信息，以及运行过程中产生的错误信息

log-error=/usr/local/mysql/data/myerror.log

# 记录除 select 语句之外的所有 sql 语句到日志中，可以用来恢复数据文件

log-bin=/usr/local/mysql/bin

#记录查询慢的sql语句

log-slow-queries=/usr/local/mysql/data/slow.log

#慢查询时间

long_query_time=2

第五步.分析慢查询日志

方式一.通过工具分析

MySQL 自带了 mysqldumpslow 工具用来分析 slow query 日志，除此之外，还有一些好用的开源工具。比如MyProfi、mysql-log-filter，当然还有 mysqlsla。

以下是 mysqldumpslow 常用参数说明，详细内容可用 mysqldumpslow -help 查询。

-s，表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序(从大到小)，ac、at、al、ar表示相应的倒序。

-t，是top n的意思，即为返回前面多少条数据。

-g，后边可以写一个正则匹配模式，大小写不敏感。

接下来就是用MySQL 自带的慢查询工具 mysqldumpslow 分析了(该工具位于 MySQL 的bin 目录下)，我这里的日志文件名字是host-slow.log。

列出记录次数最多的10个sql语句

mysqldumpslow -s c -t 10 host-slow.log

列出返回记录集最多的10个sql语句

mysqldumpslow -s r -t 10 host-slow.log

按照时间返回前10条里面含有左连接的sql语句

mysqldumpslow -s t -t 10 -g "left join" host-slow.log

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化起到非常大的帮助。

方式二.直接分析mysql慢查询日志

日志部分内容如下:

# Time: 170502 17:38:54

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 3.794217 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 4194304

SET timestamp=1350466734;

select * from wei where text='orange';

# Time: 170502 17:46:22

# User@Host: root[root] @ localhost [127.0.0.1]

# Query_time: 3.819219 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 4194304

SET timestamp=1350467182;

select * from wei where text='xishizhaohua';

其实定位到慢查询语句就已经完成了一大步了，执行 explain 或者 desc 命令查看慢查询语句，如下图：

460f2a9f-b343-333a-ab2d-61e7ec3d89ab.png

问题很明显，解决方式也很明显-------建索引。

mysql> create index text_index on wei(text);

Query OK, 4194304 rows affected (1 min 58.07 sec)

Records: 4194304 Duplicates: 0 Warnings: 0

然后再执行查询操作，用时明显少了很多。

mysql> select * from wei where text='orange';

+---------+--------+

| id | text |

+---------+--------+

| 4103519 | orange |

+---------+--------+

1 row in set (0.33 sec)

Slow Query 日志虽然帮你记录了执行过的SQL语句。但它不是万能的，意义可能没有你想象的那么大。它只告诉了你哪些语句慢，但是为什么慢?具体原因，还是需要你自己去分析，不断的调试。也许你只需要换一条更有效的sql语句，也许你只需简单地增加一个索引，但也有可能你需要调整你应用程序的设计方案。比如上面那条语句很明显，它检查了400多万行数据。不幸的是，并不是每条语句都这么明显。也许还有别的原因，比如:

锁表了，导致查询处于等态状态。lock_time显示了查询等待锁被翻译的时间

数据或索引没有被缓存。常见于第一次启动服务器或者服务器没有调优

备份数据库，I/O变慢

同时运行了其它的查询，减少了当前查询

所以,不要过于关注日志文件某条记录，而应该理性地审查，找出真正的原因。如经常出现的 slow query 需要特别注意。如果个别出现，则做一些常规检查即可。推荐大家统计并形成基准报告，进行比较排除，如此肯定比胡乱瞎撞有用。希望大家不要在这部分过于浪费时间与精力。