MySQL optimization slow query (i)

1. Commands that show some parameters for slow queries: show variables like '%slow% '; results

2. The above four parameters mean:

Log_slow_queries off means "slow query" is "off state"

Slow_launch_time 2 means "The query time is more than 2 seconds to log in the slow query logs";

Slow_queries_log off indicates that the slow query log switch is closed

Slow_query_log_file "path" means slow query log holds full path

3. Turn on slow query function

Use command: Set global log_slow_queries=on; This opens the function of the slow query, this parameter is turned on, the Slow_query_log is automatically turned on, closed and then closed.

4. In fact , the Slow_launch_time setting is different from the query threshold setting of the slow query log, which indicates a threshold value of thread create .

If the thread create time exceeds this value, the value of this variable slow_launch_time is added 1.

Set global log_launch_time=1 The time value here must be an integer, otherwise it would be wrong to execute the word

Query threshold for slow query is long_query_time default is 10 seconds

5. Setting the value of Slow_query_log_file is that the string must be enclosed in quotation marks.

To really modify the slow query to modify the MySQL file, the content is added as follows

Long_query_time=0 writes a slow query log as long as it is executed
Log_slow_queries=on Slow Query switch
Slow_query_log =on Slow query log switch
Slow_query_log_file= "D:/phpstudy/1.log" Slow query log file

And you have to restart MySQL before it takes effect.

The log structure of the slow query is as follows:

Mysqla, version:5.5.40-Log(MySQLCommunity Server (GPL)). Started with:TCP Port: 3306, Named Pipe: (NULL) TimeId Command Argument#time:150705 18:31:54#[email protected]: root[root] @ localhost [127.0.0.1]#query_time:0.012001 lock_time:0.001000 rows_sent:51 rows_examined:51 UseWeixin; SET timestamp=1436092314; show databases;#time:150705 18:32:55#[email protected]: root[root] @ localhost [127.0.0.1]#query_time:0.002000 lock_time:0.001000 rows_sent:1 rows_examined:1SET timestamp=1436092375; show variables like"%query_time%";#time:150705 18:33:03#[email protected]: root[root] @ localhost [127.0.0.1]#query_time:0.001000 lock_time:0.000000 rows_sent:0 rows_examined:0 UseChlitina; SET timestamp=1436092383; Use' Chlitina ';#[email protected]: root[root] @ localhost [127.0.0.1]#query_time:0.031001 lock_time:0.001000 rows_sent:869 rows_examined:869SET timestamp=1436092383; Select*From wp_consultant;#time:150705 18:33:40#[email protected]: root[root] @ localhost [127.0.0.1]#query_time:0.052003 lock_time:0.001000 rows_sent:869 rows_examined:1738SET timestamp=1436092420; Select* FROM Wp_consultant ORDER by id DESC;

MySQL optimization slow query (i)