欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL性能优化之慢查询日志使用讲解

程序员文章站 2022-06-24 19:53:35
在MySQL5.0及之前的版本中,慢查询日志的响应时间单位是秒。显然对于互联网与电子商务如此发达的现在,“秒”级别的查询实在太慢了。在MySQL5.1及更新...

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

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

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

开启慢查询日志

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

mysql> 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秒。

mysql> 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’。

mysql> 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也会被记录到慢查询日志。

mysql> 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语句记录慢查询

log-slow-admin-statements变量会将管理性质的慢SQL记录到慢查询日志中。管理性质的SQL语句包括:ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, 和REPAIR TABLE。

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

查看慢查询记录的条数

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

mysql> 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系统上使用。

[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

使用示例:

# 得到返回记录集最多的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