Variable_name | Value |
---|---|
long_query_time | 1.000000 |
slow_query_log | ON |
slow_query_log_file | D:\MySQL\mysql-5.6.31-winx64\data\zhoupq_sh-slow.log |
概述
数据库查询快慢是影响项目性能的一大因素,对于数据库,我们除了要优化 SQL,更重要的是得先找到需要优化的 SQL。如何找到低效的 SQL 是写这篇文章的主要目的。
MySQL 数据库有一个“慢查询日志”功能,用来记录查询时间超过某个设定值的SQL,这将极大程度帮助我们快速定位到症结所在,以便对症下药。至于查询时间的多少才算慢,每个项目、业务都有不同的要求,传统企业的软件允许查询时间高于某个值,但是把这个标准放在互联网项目或者访问量大的网站上,估计就是一个bug,甚至可能升级为一个功能性缺陷。
为避免误导读者,特申明本文的讨论限制在 Win 64位 + MySQL 5.6 范围内。其他平台或数据库种类及版本,我没有尝试过,不做赘述。
设置日志功能
关于慢查询日志,主要涉及到下面几个参数:
- slow_query_log :是否开启慢查询日志功能(必填)
- long_query_time :超过设定值,将被视作慢查询,并记录至慢查询日志文件中(必填)
- log-slow-queries :慢查询日志文件(不可填),自动在 \data\ 创建一个 [hostname]-slow.log 文件
也就是说,只有满足以上三个条件,“慢查询功能”才可能正确开启或关闭。
命令
以命令行的方式,设置参数,不需要重启 MySQL 服务,注意权限和参数作用域:
# slow_query_log 需要超级权限
mysql> set global slow_query_log = ON;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
# 以管理员身份登录
D:\MySQL\mysql-5.6.31-winx64\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# slow_query_log 是全局变量
mysql> set slow_query_log = ON;
ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.04 sec)
# 设置查询“超时”时间
mysql> set GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
配置文件
以配置文件的方式设置,需要重启 MySQL 服务。在 my.ini 中的 [mysqld] 下:
# 慢日志相关配置
slow_query_log = ON
long_query_time = 1
日志路径
慢查询日志文件不可设置路径,系统会自动创建 :\data\[hostname]-slow.log。我在配置文件中以多种形式定义了日志问价路径,均报错:
# zhoupq_sh.err
[ERROR] MySQL: unknown variable 'log-slow-queries=/data/mysql-slow.log'
[ERROR] MySQL: unknown variable 'log-slow-queries=\data\mysql-slow.log'
[ERROR] MySQL: unknown variable 'log-slow-queries=D:\MySQL\mysql-5.6.31-winx64\data\mysql-slow.log'
也许是我没有找到合适的方法,但是自定义慢查询日志文件这条路对我来说走不通,实际上,我不需要自定义这个文件。
检验
mysql> show variables like '%query%';
+------------------------------+------------------------------------------------------+
| Variable_name | Value |
+------------------------------+------------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | D:\MySQL\mysql-5.6.31-winx64\data\zhoupq_sh-slow.log |
+------------------------------+------------------------------------------------------+
13 rows in set (0.00 sec)
从结果中可以看出,slow_query_log 和 long_query_time 均以更新为设定值,slow_query_log_file 没有指定,却自动生成。
此时的 slow_query_log_file 是没有数据的,除了一些系统信息。接下来就改检测一下“慢查询日志”是否能达到我们的要求。
慢查询被记录
我写了几条 SQL 语句,按照预先的设定,查询时间超过 1s 的查询将被写入日志。
SELECT t.* FROM subscribe t limit 0,1;
SELECT t.product, t.vendor, COUNT(DISTINCT t.id) AS vulcnt FROM temp t WHERE 1 = 1 GROUP BY t.vendor, t.product ORDER BY vulcnt DESC;
第一条 SQL 执行时间 0.001s:
第二条 SQL 执行时间大于 2s:
只有第二条 SQL 被“慢查询日志”记录:
# Time: 170407 14:44:23
# User@Host: root[root] @ localhost [127.0.0.1] Id: 2
# Query_time: 2.133122 Lock_time: 0.106006 Rows_sent: 23878 Rows_examined: 160312
use testDB;
SET timestamp=1491547463;
SELECT
t.product,
t.vendor,
COUNT(DISTINCT t.id) AS vulcnt
FROM
temp t
WHERE
1 = 1
GROUP BY
t.vendor,
t.product
ORDER BY
vulcnt DESC;
小结
记住三个参数:
- slow_query_log
- long_query_time
- log-slow-queries
log-slow-queries 不可自定义
- 生产环境请关闭“慢查询日志”功能,节约空间
- 命令方式 不需要重启 MySQL 服务,而配置文件方式 需要重启