What is php mysql slow query?

php mysql slow query refers to recording SQL statements that run relatively slowly in the log. Turning on the slow query log allows MySQL to record statements that query for more than the specified time. By locating and analyzing performance bottlenecks, it can be more accurate. Good optimization of database system performance.

The operating environment of this article: windows7 system, PHP7.1 version, DELL G3 computer

php What is mysql slow query?

MySQL slow query records SQL statements that run slowly in the log. This function needs to be turned on before it can be used.

1. Introduction

Turning on the slow query log allows MySQL to record queries that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

2. Parameter introduction

slow_query_log slow query open status

slow_query_log_file The location where the slow query log is stored (this directory requires the writable permissions of the MySQL running account, and is generally set to MySQL data storage directory)

long_query_time How many seconds does the query take before recording, the default is 10 seconds

3. Enable slow query

(1) View slow query related 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 |
+-----------------+-----------+

(2) Setting method

Method 1: Global variable setting

Set the slow_query_log global variable to "ON" status

mysql> set global slow_query_log='ON';

Set the slow query log Storage location

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

Set the slow query time, and record the query if it exceeds 1 second

mysql> set global long_query_time=1;

Method 2: Configuration file settings

Modify the configuration file my.cnf, in [mysqld ] Add

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

below (3) Restart the MySQL service

service mysqld restart

(4) Slow query log analysis

Intercept a section of the slow query log:

# Time: 180918 19:06:21
# User@Host: proxy[proxy] @  [192.168.0.16]  Id: 6707197
# Query_time: 1.015429  Lock_time: 0.000116 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 = '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;

You can see here:

Query_time (the query time of the slow query statement) exceeds the set 1s,

Rows_sent (the slow query return record) only 1 record is returned here

Rows_examined (number of rows scanned by slow query) 44438 -> From here you can probably see that the problem is huge

Now put this 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;

The query result is:

Explain the parameters:

You can find it here: rows is the number of rows to be queried. More than 40,000 rows have been queried, so it is definitely slow.

Because there are several conditions here, and no index is used, the only way to add an index is to add an ordinary multi-column index to the selection here, because this table was initially designed Something went wrong, resulting in duplicate data and a unique index cannot be set.

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

The index is set, let’s look at the execution plan of the SQL just now.

#It can be found that the number of rows checked has dropped significantly.

At this point, the use and optimization of slow queries are basically completed.

Recommended learning: "

PHP Video Tutorial

"

The above is the detailed content of What is php mysql slow query?. For more information, please follow other related articles on the PHP Chinese website!