作者：zhoupq
链接：https://juejin.cn/post/6844903473079648264
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

image.png

概述

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 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 服务，注意权限和参数作用域：

  1. # slow_query_log 需要超级权限
  2. mysql> set global slow_query_log = ON;
  3. ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
  5. # 以管理员身份登录
  6. D:\MySQL\mysql-5.6.31-winx64\bin>mysql -u root -p
  7. Enter password:
  8. Welcome to the MySQL monitor.  Commands end with ; or \g.
  9. Your MySQL connection id is 1
  10. Server version: 5.6.31-log MySQL Community Server (GPL)
  12. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  14. Oracle is a registered trademark of Oracle Corporation and/or its
  15. affiliates. Other names may be trademarks of their respective
  16. owners.
  18. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  20. mysql> 
  22. # slow_query_log 是全局变量
  23. mysql> set slow_query_log = ON;
  24. ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL
  26. mysql> set global slow_query_log = ON;
  27. Query OK, 0 rows affected (0.04 sec)
  29. # 设置查询“超时”时间
  30. mysql> set GLOBAL long_query_time = 1;
  31. Query OK, 0 rows affected (0.00 sec)

配置文件

以配置文件的方式设置，需要重启 MySQL 服务。在 my.ini 中的 [mysqld] 下：

  1. # 慢日志相关配置
  2. slow_query_log = ON
  3. long_query_time = 1

日志路径

慢查询日志文件不可设置路径，系统会自动创建 ：\data[hostname]-slow.log。我在配置文件中以多种形式定义了日志文件路径，均报错：

  1.  # zhoupq_sh.err
  2.  [ERROR] MySQL: unknown variable 'log-slow-queries=/data/mysql-slow.log'
  3.  [ERROR] MySQL: unknown variable 'log-slow-queries=\data\mysql-slow.log'
  4.  [ERROR] MySQL: unknown variable 'log-slow-queries=D:\MySQL\mysql-5.6.31-winx64\data\mysql-slow.log'

也许是我没有找到合适的方法，但是自定义慢查询日志文件这条路对我来说走不通，实际上，我不需要自定义这个文件。

检验

  1. mysql> show variables like '%query%';
  2. +------------------------------+------------------------------------------------------+
  3. | Variable_name                | Value                                                |
  4. +------------------------------+------------------------------------------------------+
  5. | binlog_rows_query_log_events | OFF                                                  |
  6. | ft_query_expansion_limit     | 20                                                   |
  7. | have_query_cache             | YES                                                  |
  8. | long_query_time              | 1.000000                                             |
  9. | query_alloc_block_size       | 8192                                                 |
  10. | query_cache_limit            | 1048576                                              |
  11. | query_cache_min_res_unit     | 4096                                                 |
  12. | query_cache_size             | 1048576                                              |
  13. | query_cache_type             | OFF                                                  |
  14. | query_cache_wlock_invalidate | OFF                                                  |
  15. | query_prealloc_size          | 8192                                                 |
  16. | slow_query_log               | ON                                                   |
  17. | slow_query_log_file          | D:\MySQL\mysql-5.6.31-winx64\data\zhoupq_sh-slow.log |
  18. +------------------------------+------------------------------------------------------+
  19. 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：
image.png
第二条 SQL 执行时间大于 2s：

image.png
只有第二条 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 服务，而配置文件方式 需要重启

优化步骤

  1. 根据慢日志定位慢查询SQL
  2. 用explain分析SQL（type和extra字段分析）
  3. 修改SQL或加索引（如下）
  • 对经常查询的列建立索引，但索引建多了当数据改变时修改索引会增大开销
  • 使用精确列名查询而不是*，特别是当数据量大的时候
  • 减少子查询，使用Join替代
  • 不用NOT IN，因为会使用全表扫描而不是索引；不用IS NULL，NOT IS NULL，因为会使索引、索引统计和值更加复杂，并且需要额外一个字节的存储空间。