MySQL Slow query detailed

The method of analyzing the performance of MySQL statement query in addition to using the EXPLAIN output execution plan, you can also let the MySQL record query over a specified time of the statement, we will exceed the specified time of the SQL statement query is called "Slow query."

 View/Set time definition for "slow query" mysql> Show variables like "long%";+-----------------+----------+| variable_name | Value |+-----------------+----------+| long_query_time | 0.000100 |+-----------------+----------+1 row in Set (0.00 sec)as the above statement outputs, the time for "slow query" is defined as 0.0001 seconds (easy to test, typically set to 1-10 seconds). Use the following statement to define a "slow query" time mysql> set long_query_time=0.0001;Query OK, 0 rows Affected (0.00 sec)turn on "slow query" recording function mysql> Show variables like "slow%";+---------------------+------------------------------------+| variable_name | Value |+---------------------+------------------------------------+| slow_launch_time | 2 || slow_query_log | OFF || slow_query_log_file |/opt/mysql/data/localhost-slow.log |+---------------------+------------------------------------+3 rows in Set (0.00 sec)The above statement looks at the configuration information for "Slow query", you can customize the log file storage, but you must set the Slow_query_log global variable to "on" state, execute the following statement: mysql> set global slow_query_log=on;Query OK, 0 rows affected (0.01 sec)Results:mysql> Show variables like "slow%";+---------------------+------------------------------------+| variable_name | Value |+---------------------+------------------------------------+| slow_launch_time | 2 || slow_query_log | On || slow_query_log_file |/opt/mysql/data/localhost-slow.log |+---------------------+------------------------------------+3 rows in Set (0.00 sec)  view the number of records for a slow querymysql> show global status like '%slow% ';  View log_queries_not_using_indexes Statusmysql> Show variables like ' log_queries_not_using_indexes '; go to the Mysql/bin directory, enter Mysqldumpslow. Help or--help to see the parameters of this toolmysqldumpslow-s c-t Host-slow.logmysqldumpslow-s r-t Host-slow.logThe above command shows the 20 most visited SQL statements and the 20 SQL that returns the maximum number of recordsetsmysqldumpslow-t 10-s t-g "left join" Host-slow.logThis is the SQL statement that contains the left link in the first 10 lines. mysqldumpslow-s c-t 10/database/mysql/slow-logThis outputs 10 SQL statements with the highest number of records, where:- s order, which is the way of ordering, the order value is: C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding reverse;- t num, which is the data that returns the number of preceding bars;- G Pattern,pattern can write a regular matching pattern, case insensitive;using the Mysqldumpslow command can be very clear to get a variety of query statements we need, the MySQL query statement monitoring, analysis, optimization is the first step of MySQL optimization, is also a very important step.

MySQL Slow query detailed