mysql 开启慢查询会影响性能吗_MySQL性能优化[准备篇]-慢查询日志

在MySQL5.0及之前的版本中，慢查询日志的响应时间单位是秒。显然对于互联网与电子商务如此发达的现在，“秒”级别的查询实在太慢了。在MySQL5.1及更新的版本中，慢查询日志的功能得到了增强，甚至可以通过设置long_query_time为0来捕获所有的查询。

在MySQL的当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具。我们完全不用担心开启慢查询日志会带来额外的I/O开销，这方面已经经过了测试，慢查询日志带来的开销可以忽略不计。更需要担心的是日志可能消耗大量的磁盘空间。如果长期开启慢查询日志，注意部署日志轮转(log rotation)工具，或者不要长期启用慢查询气质，只需在收集负载样本期间开启即可。

MySQL还有另外一种查询日志，被称为“通用日志”，但极少用于分析服务器性能。通用日志在查询请求达到服务器时记录，所以不包括响应时间和执行计划等重要的性能信息。MySQL5.1之后支持将日志记录到数据库的表中，但大多数情况没必要记录到表中。这对性能有较大的影响，MySQL5.1在将慢查询记录到文件中时已经支持微妙级的信息，然而将慢查询记录到表中会导致时间退化到秒级别。秒级别的慢查询日志没有太大意义。

开启慢查询日志

默认情况下，慢查询日志被禁用；默认慢查询日志放在数据目录，默认文件名为host_name-slow.log。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25mysql> show variables like 'slow_query%';

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

| Variable_name | Value |

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

| slow_query_log | OFF |

| slow_query_log_file | E:\mysql-5.7.15-winx64\data\DESKTOP-B76J065-slow.log |

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

2 rows in set, 1 warning (0.00 sec)

-- 开启慢查询日志

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.01 sec)

-- 设置慢查询日志路径

mysql> set global slow_query_log_file='E:\\slow_query.log';

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'slow_query%';

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

| Variable_name | Value |

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

| slow_query_log | ON |

| slow_query_log_file | E:\slow_query.log |

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

2 rows in set, 1 warning (0.00 sec)

注意：使用set global slow_query_log=1开启慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf(windows下为my.ini)，或者在使用命令行启动mysql的时候，在--slow-query-log参数中指定，其它系统变量也是如此。

设置慢查询时间阈值

开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time控制，默认long_query_time=10，也就是10秒。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27mysql> show variables like 'long_query_time';

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

| Variable_name | Value |

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

| long_query_time | 10.000000 |

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

1 row in set, 1 warning (0.01 sec)

mysql> set global long_query_time=1;

Query OK, 0 rows affected (0.00 sec)

-- 注意show variables查看的是当前会话的变量值

mysql> show variables like 'long_query_time';

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

| Variable_name | Value |

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

| long_query_time | 10.000000 |

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

1 row in set, 1 warning (0.00 sec)

mysql> show global variables like 'long_query_time';

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

| Variable_name | Value |

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

| long_query_time | 1.000000 |

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

1 row in set, 1 warning (0.00 sec)

注意：使用show variables like 'long_query_time'查看是当前连接会话的变量值。因为long_query_time变量在GLOBAL和SESSION中都有，如果不加访问域则使用默认的SESSION访问域，所以要想看到全局的long_query_time变量需要用如下语句show global variables like 'long_query_time'。

show variables完整语法为：show [global|session] variables [like ‘pattern‘ | where expr]

将慢查询日志记录到数据表中

尽管将慢查询日志记录到数据表中会影响性能，但谁也无法保证不会出现这样的需求。

log_output变量用于指定日志的存储方式，这个变量有两个取值：FILE,TABLE，默认取值为FILE。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18mysql> show variables like 'log_output';

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

| Variable_name | Value |

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

| log_output | FILE |

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

1 row in set, 1 warning (0.00 sec)

mysql> set global log_output='TABLE';

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_output';

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

| Variable_name | Value |

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

| log_output | TABLE |

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

1 row in set, 1 warning (0.00 sec)

将未使用到索引的查询当做慢查询记录到日志中

系统变量log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan(索引全扫描)的sql也会被记录到慢查询日志。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18mysql> show variables like 'log_queries_not_using_indexes';

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

| Variable_name | Value |

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

| log_queries_not_using_indexes | OFF |

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

1 row in set, 1 warning (0.00 sec)

mysql> set global log_queries_not_using_indexes=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';

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

| Variable_name | Value |

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

| log_queries_not_using_indexes | ON |

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

1 row in set, 1 warning (0.00 sec)

将管理性质的SQL语句记录慢查询1

2

3

4

5

6

7mysql> show variables like 'log_slow_admin_statements';

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

| Variable_name | Value |

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

| log_slow_admin_statements | OFF |

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

1 row in set (0.00 sec)

查看慢查询记录的条数

如果你想查询有多少条慢查询记录，可以使用这个系统状态变量——Slow_queries。

1

2

3

4

5

6

7mysql> show global status like 'slow_queries';

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

| Variable_name | Value |

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

| Slow_queries | 0 |

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

1 row in set (0.00 sec)

日志分析工具

在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow

注意：mysqldumpslow是一个perl语言写的脚本，最好在*nix系统上使用。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33[root@localhost~]#mysqldumpslow --help

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 verbose

-d debug

#s 是表示按照何种方式排序

-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default

al: average lock time 平均锁定时间

ar: average rows sent 平均返回记录数

at: average query time 平均查询时间

c: count 访问计数

l: lock time 锁定时间

r: rows sent 返回记录

t: query time 查询时间

-r reverse the sort order (largest last instead of first)

#t top n的意思，即为返回前面多少条的数据；

-t NUM just show the top n queries

-a don't abstract all numbers to N and strings to 'S'

-n NUM abstract numbers with at least n digits within names

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

-g PATTERN grep: only consider stmts that include this string

-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),

default is '*', i.e. match all

-i NAME name of server instance (if using mysql.server startup script)

-l don't subtract lock time from total time

mysqldumpslow --help

使用示例：

1

2

3

4

5

6

7

8

9

10

11#得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

#得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

参考：

《高性能MySQL》