Explain the slow query analysis of MySql and open the slow query log in detail

I am also studying the performance optimization of MySQL recently, so today is also a study note!

Among the projects developed by small partners, the easiest problems to find and solve for MySQL to troubleshoot problems and find performance bottlenecks are the slow query of MYSQL and the query without index.

Next, teach you how to open slow query logging above MySQL version 5.0;

OK, 1 start to find out the SQL statements that are not "cool" in mysql.

First, we enter the mysql command line through the mysql command:

[root@yunuo_vm ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4977
Server version: 5.6.17 Source distribution
 
Copyright (c) 2000, 2014, 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>

ps: The version of MySQL here is 5.6. 17

OK, entered the console, next, let's see how many seconds is slow query in the default configuration of MySQL

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

As shown in the above table, the default upper limit of slow query time is 10 seconds. Let's change it to 1 second (you can also decide according to your actual situation);

mysql> set long_query_time=1;   Note:   I set up 1,  That is, the execution time exceeds 1 Seconds are slow queries. 
Query OK, 0 rows affected (0.00 sec)

Oh! Finally, let's see if MySQL turns on slow query logging;

mysql> show variables like 'slow%';
+---------------------+---------------+
| Variable_name    | Value     |
+---------------------+---------------+
| slow_launch_time  | 2       | 
| slow_query_log   | OFF      |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+

ps:

slow_query_log//Turn on logging

slow_query_log_file//Log store location

MySQL does not turn on slow query by default. Let's turn on it below:

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

At this point, you are done! ! ! Isn't it simple?