Shulou(Shulou.com)06/02 Report--

This article mainly shows you "what is the meaning of php mysql slow query", the content is simple and clear, hoping to help you solve your doubts, the following let the editor lead you to study and learn "what is the meaning of php mysql slow query" this article.

Php mysql slow query refers to recording slow SQL statements in the log, and opening the slow query log allows MySQL to record the query statements that exceed the specified time. By locating and analyzing the performance bottleneck, the performance of the database system can be better optimized.

This article operating environment: windows7 system, PHP7.1 version, DELL G3 computer

What is a php mysql slow query?

MySQL slow query is to record slow SQL statements in the log, which needs to be turned on before it can be used.

1. Brief introduction

By enabling the slow query log, you can let MySQL record the statements that query for more than a specified time. By locating the bottleneck of analyzing performance, you can better optimize the performance of the database system.

2. Parameter introduction

Slow_query_log slow query enabled status

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

How many seconds does the long_query_time query take to record? default is 10 seconds.

3. Open slow query

(1) View the parameters related to slow query

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 the "ON" state

Mysql > set global slow_query_log='ON'

Set the location where slow query logs are stored

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

Set the slow query time, which will be recorded if the query exceeds 1 second.

Mysql > set global long_query_time=1

Method 2: profile settings

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

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

(3) restart MySQL service

Service mysqld restart

(4) slow query log analysis

Intercept a slow query log:

# Time: 180918 19proxy 06virtual User@Host: proxy [proxy] @ [192.168.0.16] Id: 6707197# Query_time: 1.015429 Lock_time: 0.000116 Rows_sent: 1 Rows_examined: 44438SET 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: 670723 Query_time: 1.021662 Lock_time: 0.000083 Rows_sent: 1 Rows_examined: 44438SET 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 slow query statement) exceeds the set 1s.

Rows_sent (slow query returned record) only 1 is returned here.

Rows_examined (number of rows scanned by slow query) 44438-> you can probably see that there is a big problem here.

Now put this SQL statement into the database to execute, 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 results are as follows:

Explain the parameters:

It can be found here: rows is the number of rows queried, more than 4w rows have been queried, and that's for sure.

Because there are several conditions and an index is not used, you can only add an index.

Here, a normal multi-column index is added to the selection, because there is a problem with the design of the table at the beginning, resulting in duplicate data, and the unique index cannot be set.

ALTER TABLE app_mobile_device ADD INDEX user_app_type_only (`user_ id`, `app_ type`)

The index is set, and then take a look at the execution plan of SQL just now.

It can be found that the number of check lines in rows has decreased significantly.

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

The above is all the content of this article "what is the meaning of php mysql slow query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.