1, the introduction of

If slow query logs are enabled, the MySQL database can record statements that are queried after the specified time. By locating performance bottlenecks, the performance of the database can be better optimized.

2. Parameter introduction

Slow_query_log Slow query status

Slow_query_log_file Specifies the directory where slow query logs are stored. (This directory requires write permission of the MySQL run account, and is generally set to the directory where MySQL data is stored.)

Long_query_time Specifies the number of seconds before a query is recorded. The default value is 10 seconds

3. Enable slow query

(1) View slow query parameters

mysql> show variables like 'slow_query%'; +---------------------------+-----------------------------------+ | Variable_name | Value | +---------------------------+-----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /usr/local/var/mysql/slow.log | +---------------------------+-----------------------------------+ mysql> show variables like 'long_query_time'; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | Variable_name Value | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- + | long_query_time | 10.000000 | +-----------------+-----------+Copy the code

(2) Setting method

Method 1: Global variable Settings

Set the slow_query_log global variable to the “ON” state

mysql> set global slow_query_log='ON'; 
Copy the code

Set the location for storing slow query logs

mysql> set global slow_query_log_file='/usr/local/var/mysql/slow.log ';
Copy the code

Set the slow query time. If the query exceeds one second, the system records the query

mysql> set global long_query_time=1;
Copy the code

Method 2: Configure the configuration file

Modify the configuration file my.cnf and add it under [mysqld]

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/var/mysql/slow.log 
long_query_time = 1
Copy the code

(3) Restart the MySQL service

service mysqld restart
Copy the code

(4) Slow query log analysis

  • Intercept a slow query log:
# User@Host: proxy[proxy] @ [192.168.0.16] Id: 6707197 # Query_time: 1.015429 Lock_time: 0.000116 Rows_examined: 1 Rows_examined: 44438 SET TIMESTAMP =1537268781; select id, user_id, device_uuid, bd_client_id, bd_user_id, bd_tag, nodisturb_mode, nodisturb_start_time, nodisturb_end_time, binding_time, device_os_type, app_type, state from app_mobile_device where user_id = '78436' and app_type = 'YGY' order by binding_time desc; # User@Host: proxy[proxy] @ [192.168.0.16] Id: 6707236 # Query_time: 1.021662 Lock_time: 0.000083 Rows_sent: 1 Rows_examined: 44438 SET timestamp=1537268781; select id, user_id, device_uuid, bd_client_id, bd_user_id, bd_tag, nodisturb_mode, nodisturb_start_time, nodisturb_end_time, binding_time, device_os_type, app_type, state from app_mobile_device where user_id = '14433' and app_type = 'YGY' order by binding_time desc;Copy the code

You can see it here:

Query_time (query time for slow statements) exceeds 1s,

Rows_sent (Slow query returned record) Only 1 is returned here

Rows_examined (Slow query for scanned rows) 44438 -> You can probably see that the problem is large

  • Now put the SQL statement into the database for execution and use EXPLAIN analysis to see the execution plan
EXPLAIN                                
select                                 
        id, user_id, device_uuid, bd_client_id, bd_user_id, bd_tag,                        
        nodisturb_mode, nodisturb_start_time,                          
        nodisturb_end_time, binding_time, device_os_type, app_type, state                          
        from app_mobile_device                         
        where user_id = '78436'                            
            and app_type = 'YGY'                       
        order by binding_time desc;
Copy the code

The query result is as follows:

Explain the parameters:

SQL > select * from ‘rows’ where’ rows’ = ‘0’;

Because there are several conditions here, and you’re not using an index, you have to add an index,

I chose to add a normal multi-column index, because the design of this table was wrong in the beginning, resulting in duplicate data, and cannot set a unique index.

ALTER  TABLE  app_mobile_device  ADD  INDEX user_app_type_only (  `user_id` ,`app_type` )
Copy the code

Now that the index is set, let’s look at the SQL execution plan.

You can see that the number of rows checked has gone down significantly.

At this point, the use and optimization of slow queries is basically complete.

Refer to the article: www.cnblogs.com/sunxun/p/96…

www.cnblogs.com/gxj521test/…

Pay attention and don’t get lost

All right, everybody, that’s all for this article. All the people here are talented. As I said before, there are many technical points in PHP, because there are too many, it is really difficult to write, you will not read too much after writing, so I have compiled it into PDF and document, if necessary

Click on the code: PHP+ “platform”

As long as you can guarantee your salary to rise a step (constantly updated)

I hope the above content can help you. Many PHPer will encounter some problems and bottlenecks when they are advanced, and they have no sense of direction when writing too many business codes. I have sorted out some information, including but not limited to: Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, TP6, Laravel, YII2, Redis, Swoole, Swoft, Kafka, Mysql optimization, shell scripting, Docker, microservices, Nginx, etc