1, Why open slow query

2, Several common reasons for the slow query of mysql

2.1 query all columns

select * will bring additional I/O, memory and cup consumption to the server, and cause the optimizer unable to complete the optimization such as index coverage scanning.

2.2 finding unnecessary records

For example, from an article website, the server finds out that all the article data is returned to the client, but the client only needs 10 articles to render, which will also cause additional consumption to the server. It is better to use limit to obtain.

2.3 querying recurring records

For example, on the homepage of a website, some data are updated less frequently. We repeatedly query these data. At this point, using caching may improve performance.

2.4 return all columns when multiple tables are associated

There are three tables that need to be associated. Let's not find out all the columns of the three tables. Which columns of which table should be specified? If not, what's the difference with select *?

3, View the configuration and status of mysql slow query

mysql> show variables like 'slow_query%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

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

Parameter Description:

Parameter name	meaning
slow_query_log	On on off off
slow_query_log_file	Slow query log save location
long_query_time	How long does it take to log

4, How to enable slow query

4.1 method 1

Set on state:

mysql> set global slow_query_log='ON'; 

Set log save location:

mysql>  set global slow_query_log_file='/www/server/data/mysql-slow.log';

Set timeout logging time:

set global long_query_time=1;

4.2 method 2

Modify profile my.cnf Under mysqld, add:

slow_query_log = ON
slow_query_log_file = /www/server/data/mysql-slow.log
long_query_time = 1

5, Conduct the test

mysql> select sleep(2),username from fa_cp_users;
+----------+----------+
| sleep(2) | username |
+----------+----------+
|        0 | nice666  |
+----------+----------+
1 row in set (2.00 sec)

The query time has been more than two seconds. Go to the configuration file to check and find the existing mysql-slow.log File:

[root@VM_0_10_centos ~]# cd /www/server/data
[root@VM_0_10_centos data]# ll
total 341828
-rw-rw---- 1 mysql mysql        56 Nov 13  2019 auto.cnf
drwx------ 2 mysql mysql      4096 Dec 11  2019 bishe
drwx------ 2 mysql mysql      4096 Jan 18 18:45 brushorder
drwx------ 2 mysql mysql      4096 Feb 16 13:49 caipiao
drwx------ 2 mysql mysql      4096 Nov 27  2019 daijia
drwx------ 2 mysql mysql      4096 Dec 13  2019 dht
-rw-rw---- 1 mysql mysql  77594624 Jun 14 10:50 ibdata1
-rw-rw---- 1 mysql mysql 134217728 Jun 14 10:50 ib_logfile0
-rw-rw---- 1 mysql mysql 134217728 May 23 04:58 ib_logfile1
drwx------ 2 mysql mysql      4096 Dec 21 11:17 linmaocheng
drwx------ 2 mysql mysql      4096 Dec 17 18:32 ljk
drwx------ 2 mysql mysql      4096 Nov 13  2019 mysql
-rw-rw---- 1 mysql mysql   3124759 Jun 12 11:28 mysql-bin.000036
-rw-rw---- 1 mysql mysql     91172 Jun 14 09:13 mysql-bin.000037
-rw-rw---- 1 mysql mysql        38 Jun 12 11:28 mysql-bin.index
-rw-rw---- 1 mysql mysql       218 Jun 14 11:10 mysql-slow.log
drwx------ 2 mysql mysql      4096 Feb 29 11:58 mytool
drwx------ 2 mysql mysql      4096 Nov 13  2019 performance_schema
drwx------ 2 mysql mysql      4096 Dec 13  2019 RedLetter
drwx------ 2 mysql mysql     12288 Nov 21  2019 RedPacket
drwx------ 2 mysql mysql      4096 May  6 10:43 smallfox
drwx------ 2 mysql mysql      4096 May  8 17:02 studyfast
-rw-rw---- 1 mysql mysql    673013 Jun 14 10:13 VM_0_10_centos.err
-rw-rw---- 1 mysql mysql         6 Jun 12 11:28 VM_0_10_centos.pid
drwx------ 2 mysql mysql     12288 Apr 25 22:32 yuerjia
[root@VM_0_10_centos data]# vim mysql-slow.log

View mysql-slow.log Document content:

# Time: 200614 11:10:53
# User@Host: root[root] @ localhost []  Id: 18230
# Query_time: 2.000251  Lock_time: 0.000082 Rows_sent: 1  Rows_examined: 1
SET timestamp=1592104253;
select sleep(2),username from fa_cp_users;

6, Parameter details

Parameter name	meaning
Query_time	SQL execution time
Lock_time	Wait for table lock time in MySQL server phase (not in storage engine phase)
Rows_sent	Number of rows returned by query
Rows_examined	The longer the number of rows to query and check, the longer it will take

7, Summary

• mysql also has some tools, such as mysql dumpslow. Can help us do better log analysis.
• Analyze tens of millions of sql efficiency schemes, and master one.