How to enable and analyze slow query logs in MySQL?

This article will take you to understand the slow query in MySQL, introduce the method of enabling the slow query log, and analyze the slow query. I hope it will be helpful to everyone!

Mysql has a function that can record slow records of long-term queries. The specific length of time can be configured by yourself, but slow queries are not necessarily bad SQL, and may also be affected by It is also a commonly used performance analysis tool that affects other queries or is caused by system resource limitations. [Related recommendations: mysql video tutorial]

MySQL’s slow query log is controlled by long_query_time and min_examined_row_limit. The former means if the query costs The time exceeds so many seconds, the MySQL server records this to the slow query log file when the slow query log is enabled.

Enable slow query log

To enable slow query log in MySQL, please follow the steps below:

First take a look at the slow query log in MySQL The status of the query.

MariaDB [(none)]> show variables like '%slow%';
+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+                                                                                           
| Variable_name                | Value                                                                                                                                |                                                                                           
+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+                                                                                           
| log_slow_admin_statements    | ON                                                                                                                                   |
| log_slow_disabled_statements | sp                                                                                                                                   |
| log_slow_filter              | admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_rate_limit          | 1                                                                                                                                    |
| log_slow_slave_statements    | ON                                                                                                                                   |
| log_slow_verbosity           |                                                                                                                                      |
| slow_launch_time             | 2                                                                                                                                    |
| slow_query_log               | OFF                                                                                                                                  |
| slow_query_log_file          | hxl-slow.log                                                                                                                         |
+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.001 sec)

First look at the status of slow_query_log, which represents whether the slow query log is enabled. The value can be 0 or OFF to disable, 1 or ON to enable. The destination of the log output is controlled by the log_output system variable. If the value is NONE, even if the slow query log is enabled, it will not be written to the log. log_output The values ​​of the variables are as follows:

• TABLE: (Default) Write general queries to the mysql.general_log table and write general queries to the table mysql.slow_log Writing slow queries.
• FILE: Write general and slow query logs to the file system.
• NONE – Disable logging.

Also general_log_file and slow_query_log_file variables represent the names of the query log and slow query log files. These variables can be set when the server is started or running.

Start the slow query log

The slow query log function is turned off by default in MySQL, so to turn on this function, we need to set slow_query_log to ON as shown below.

SET GLOBAL slow_query_log = 1;

Set the recording duration

long_query_timeThe variable indicates that only the running time exceeding this value will be recorded, as shown below, change the time to 5 Seconds, it defaults to 10 seconds and the minimum value is 0.

SET GLOBAL long_query_time = 5;

Modify the output location

By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log, We can also set another location using the slow_query_log_file variable.

SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

Write the slow query log to the table

The slow query log can also be written to the slow_log table, the method is as mentioned above Modify the log_output system variable to TABLE, the operation is as follows:

SET GLOBAL log_output='TABLE';

Slow query log related variables

• slow_query_log - 启用/禁用慢查询日志
• log_output - 如何写入输出
• slow_query_log_file - 慢查询日志文件的名称
• long_query_time - 定义慢查询的时间（以秒/微秒为单位）
• log_queries_not_using_indexes - 是否记录不使用索引的查询
• log_slow_admin_statements - 是否记录某些管理语句
• log_slow_disabled_statements - 不应记录在慢查询日志中的语句类型
• min_examined_row_limit - 查询必须检查的最小行数才能变慢
• log_slow_rate_limit - 允许记录一部分慢查询
• log_slow_verbosity - 日志中的详细信息量
• log_slow_filter - 限制要记录的查询

分析慢查询

日志记录后，我们还需要分析这些日志，找出影响系统的查询，MySQL提供了一个名为mysqldumpslow的工具，可以简单的显示结果，只需要把日志路径传递给他即可，如下：

# mysqldumpslow -a mysql-slow.log

Reading mysql slow query log from mysql-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=21.0 (21), Rows_examined=21.0 (21), Rows_affected=0.0 (0), root[root]@localhost
  show databases

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=2.0 (2), Rows_examined=2.0 (2), Rows_affected=0.0 (0), root[root]@localhost
  select * from users

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
  select @@version_comment limit 1

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=1.0 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost

他的参数如下

• -s ，按照什么方式起来排序。默认at，也就是按照平均查询时间来排序。都是按照倒序排列。

  al: average lock time 平均锁定时间

  ar: average rows sent 平均返回行数

  at: average query time 平均查询时间

  c: count 总执行次数

  l: lock time 总锁定时间

  r: rows sent 总返回行数

  t: query time 总查询时间

• -t ，show the top n queries，显示前多少名的记录

• -a ，默认不开启这个选项。mysqldumpslow将相似的SQL的值（字符串或者数字）替换为N，开启该选项，则显示真实值。不开启该选项，有点类似于Oracle的绑定变量的记录。\

• -g ，类似于grep命令，过滤出需要的信息。如，只查询A表的慢查询记录。\

• -l ，总时间中包含锁定时间

原文地址：https://juejin.cn/post/6993221989175394311

更多编程相关知识，请访问：编程视频！！

The above is the detailed content of How to enable and analyze slow query logs in MySQL?. For more information, please follow other related articles on the PHP Chinese website!