Detailed explanation of MySQL slow log query

Slow log query function

The main function of slow log query is to record the query statements in SQL statements that exceed the set time threshold. For example, for a query SQL statement, we set the threshold to 1s. When the execution time of this query statement exceeds 1s, it will be written to the slow query configuration log.
Slow query is mainly for us to optimize the SQL statement.

Slow log query configuration item description

Open mysql and view the relevant configuration using the following command:

mysql> show variables like '%query%';
+------------------------------+--------------------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/seandeMacBook-Pro-slow.log |
+------------------------------+--------------------------------------------------+
8 rows in set (0.00 sec)

The configuration items that need attention are:

1.slow_query_log

This configuration item determines whether to enable the slow log query function. The configuration value is ON or OFF.

2.slow_query_log_file

This configuration item is a record file for slow log queries and needs to be created manually.

3.long_query_time

This configuration item sets the time threshold for slow log queries. When this threshold is exceeded, the slow log will be recorded. The configuration value is 0 (all SQL statements are recorded) or > 0 (specific threshold). This configuration item is in seconds and can be set to decimals.

4. Log-queries-not-using-indexes

This configuration item is used to record SQL statements that do not use indexes.

How to configure slow log query

There are two ways to configure the slow query function. One is to use the MySQL configuration file, and the other is to use the MySQL command. It is recommended to use the configuration file because in the process of command configuration, it is found that sometimes the configuration item is successful when the set command is used, but the query is still not set.

1. Configuration file configuration

-- Find [mysqld] and add the following code below it.
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/var/localhost-slow.log
long_query_time=0
log-queries-not-using-indexes = 1
-- After configuration, restart the mysql service

2. Use command configuration

-- Here we only need one configuration item, and other configuration items are configured in this waymysql> set slow_query_log=ON;
-- If the execution fails, it may be because the global is not added

mysql> set global slow_query_log=ON;

After configuration, check whether the MySQL slow query log is configured successfully:

mysql> show variables like '%query%';
+------------------------------+--------------------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/seandeMacBook-Pro-slow.log |
+------------------------------+--------------------------------------------------+
8 rows in set (0.01 sec)

How to view slow query records

First, set the time threshold of the slow query log to 0

mysql> set global long_query_time=0;
Query OK, 0 rows affected (0.00 sec)

Restart MySQL to take effect

Before configuring slow queries, the sample data files have been imported. MySQL official data example, then start querying.

mysql> select * from city where Name = 'Salala';
+------+--------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 2818 | Salala | OMN | Zufar | 131813 |
+------+--------+-------------+----------+------------+
1 row in set (0.01 sec)

According to the configured slow query log file /usr/local/mysql/data/seandeMacBook-Pro-slow.log, check the above command operations.

# Time: 2019-08-20T09:17:49.791767Z
# User@Host: root[root] @ localhost [] Id: 150
# Query_time: 0.002549 Lock_time: 0.000144 Rows_sent: 1 Rows_examined: 4079
SET timestamp=1566292669;
select * from city where Name = 'Salala';

Appeal file configuration content description

1.Time

The time when the log is recorded

2.User @Host

MySQL login user and login host address

3. Query_time row

The first time is the query time, the second is the table lock time, the third is the number of rows returned, and the fourth is the number of rows scanned.

4.SET timestamp

This one is the time of MySQL query

5.sql statement

This line is very obvious, it represents the SQL statement we executed

Summarize

Since we configure long_query_time=0, all SQL statements will be recorded. Here we assume, just assume. We set long_query_time=5, but in the third item above, Query_time is greater than 5. If it is not within the normal range in the actual project, it needs to be optimized. Of course, there are many ways to optimize it. Let's use a simple index method to optimize it.

  Optimize SQL statements

1. View the original SQL statement execution structure:

mysql> explain select * from city where Name='Salala'\G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: city
  partitions: NULL
     type: ALL
possible_keys: NULL
     key: NULL
   key_len: NULL
     ref: NULL
     rows: 4188
   filtered: 10.00
    Extra: Using where
1 row in set, 1 warning (0.00 sec)

It can be seen that the SQL statement performs a full disk scan. We can use indexes to optimize it simply.

2. Create an index

mysql> create index idx_cityName on city(`Name`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

3. Use explain to analyze

mysql> explain select * from city where Name='Salala'\G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: city
  partitions: NULL
     type: ref
possible_keys: idx_cityName
     key: idx_cityName
   key_len: 35
     ref: const
     rows: 1
   filtered: 100.00
    Extra: NULL
1 row in set, 1 warning (0.01 sec)

By creating an index, we found that only one row was scanned at this time, and the index scan was used, which greatly improved the efficiency of a MySQL query.

Summary of MySQL slow query usage

In normal development, slow query is very useful as a way to optimize MySQL. It will record some of our sql statements with long query time, and we will analyze them to optimize the sql query statements. However, after the slow log query is turned on, the relevant records for the SQL query will be written to the disk file through disk I/O, which increases the disk I/O read and write. Therefore, we use this function in development and testing environments, not in production environments.

Slow log query tool

The later the slow log query file is, the more content it contains. The more pressure we have to analyze it, the more we need to use some tools to achieve fast analysis. I am not yet fully familiar with these tools. I will write a separate article to introduce this type of tools later. Here I will just list the tool names.

1.mysqldumpslow
2.pt-query-digest
3.mysqltop (Lepus)

The above is a detailed explanation of MySQL slow log query. For more information about MySQL slow log query, please pay attention to other related articles on 123WORDPRESS.COM!