MySQL slow Query

MySQL slow Query

Slow query (slow log)It can help us locate specific SQL statements for SQL statement optimization. For example, slow query logs record the SQL statements whose execution time exceeds the given value, so as to locate the problem.

Enable slow Query

View MySQL database instance slow query parameters

mysql> show variables like '%slow%';+---------------------------+-----------------------------------+| Variable_name             | Value                             |+---------------------------+-----------------------------------+| log_slow_admin_statements | OFF                               || log_slow_slave_statements | OFF                               || slow_launch_time          | 2                                 || slow_query_log            | OFF                               || slow_query_log_file       | /var/lib/mysql/localhost-slow.log |+---------------------------+-----------------------------------+5 rows in set (0.00 sec)mysql> show variables like '%long_query_time%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.02 sec)

Where,

Slow_query_logIs to enable slow query parameters

Slow_query_log_fileIs the path of the slow query log file

Log_query_timeIs the maximum query time. If this time is exceeded, log records are queried slowly.

The following example shows how to start a slow query, set the maximum query time to 2 seconds, and run sleep3 seconds to view the slow query log.

mysql> set global slow_query_log=ON;Query OK, 0 rows affected (0.03 sec)mysql> set long_query_time=2;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%long_query_time%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 2.000000 |+-----------------+----------+1 row in set (0.00 sec)mysql> select sleep(3);+----------+| sleep(3) |+----------+|        0 |+----------+1 row in set (3.00 sec)mysql> system cat /var/lib/mysql/localhost-slow.log/usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with:Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sockTime                 Id Command    Argument# Time: 150102 16:56:54# User@Host: root[root] @ localhost []  Id:     1# Query_time: 3.001084  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0use test2;SET timestamp=1420189014;select sleep(3);

Slow query also has a parameter log_queries_not_using_indexes, which indicates the number of times that no index is used to record slow query logs per minute. The default value is 0, indicating no limit, but in the production environment, these statements occupy a large number of slow query logs, which causes difficulties for DBA analysis.

In addition to viewing slow query logs using log files, you can also view logs using tables. In mysql mode, a table named slow_log records the output of slow queries. You only need to change the log output mode log_output. The log_output parameter is global and dynamic, it can be dynamically changed at runtime.

mysql> show variables like '%log_output%';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+1 row in set (0.00 sec)mysql> set global log_output='table';Query OK, 0 rows affected (0.00 sec)mysql> select sleep(3);+----------+| sleep(3) |+----------+|        0 |+----------+1 row in set (3.00 sec)

Note that the engine room csv of the slow_log table may not be very efficient in query, but it is very efficient to append and insert logs.

mysql> show create table slow_log\G*************************** 1. row ***************************       Table: slow_logCreate Table: CREATE TABLE `slow_log` (  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `user_host` mediumtext NOT NULL,  `query_time` time NOT NULL,  `lock_time` time NOT NULL,  `rows_sent` int(11) NOT NULL,  `rows_examined` int(11) NOT NULL,  `db` varchar(512) NOT NULL,  `last_insert_id` int(11) NOT NULL,  `insert_id` int(11) NOT NULL,  `server_id` int(10) unsigned NOT NULL,  `sql_text` mediumtext NOT NULL,  `thread_id` bigint(21) unsigned NOT NULL) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

Another useful parameter for slow queries is long_query_io, which indicates that the logical IO count will exceed the specified number of logical IO reads (logical IO includes physical IO, indicating the sum of the number of reads of physical IO and buffer pool) the SQL statement is recorded in the slow query log.