MySQL Advanced 11 - Slow Query

MySQL records queries that exceed the specified time. We call SQL query queries that exceed the specified time as slow queries.

1. Check the time limit

show variables like '%long%';

and you will see:

+---------------------------------------------------+-----------+
| Variable_name                        | Value     |
+---------------------------------------------------+-----------+
| long_query_time                        | 10.000000 |
| max_long_data_size                     | 1048576   |
| performance_schema_events_waits_history_long_size | 10000     |
+---------------------------------------------------+-----------+

If the query exceeds long_query_time time is called a slow query.

2. Check the startup time of the database

show status like 'uptime%';

and you will see

+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 76333 |
| Uptime_since_flush_status | 76333 |
+---------------------------+-------+

3. Check the query If the number of statements is

show status like 'com_Select';

, you will see

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 1     |
+---------------+-------+

4. Get the number of connections

show status like 'connections';

will see

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 2     |
+---------------+-------+

5. Set the slow query time

set long_query_time = 1;

6. Start in safe mode MySQL service, slow query records will be written to the log.

Enable slow query log

Add the following parameters to the MySQL configuration file

log-show-queries = D:/MySQL/Log/mysqld-slow-query. log

long-query-time = 5

log-queries-not-using-indexes

Explanation:

About the storage location of the slow query log function , the directory file must have write permission. You can use settings. The system will give a default file host_name-show.log

long_query_time

SQL execution time threshold, the default is 10 seconds

Pay attention to three points :

1. After setting the long_query_time threshold, the mysql database will record all SQL statements whose running time exceeds this value, but the situation where the running time is exactly equal to long_query_time will not be recorded. In other words, in the mysql source code, it is judged to be greater than log_query_time, not greater than or equal to.

2. Starting from MySQL 5.1, long_query_time starts to be calculated in microseconds, thus accurately recording the running time of SQL.

3. It is recommended that the time should not be too small or too large, preferably between 5-10 seconds. Of course you can decide according to your own situation.

log-queries-not-using-indexes

If the running SQL statement does not use indexes, the mysql database will also record this SQL statement into the slow query log file.

The above is the content of MySQL Advanced Eleven - Slow Query. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!