Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to locate slow queries in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Location Analysis of NO.1 slow query Log

MySQL's slow log records are statements in MySQL where the response time exceeds the value set by the parameter long_query_time (in seconds, the default is 10) and the number of scanned records is not less than min_examined_row_limit (the default is 0).

NOTE: by default, management statements are not recorded in the slow query log. If you need to record, please set log_slow_admin_statements = on so that the slow query in the management statement will also be recorded in the slow query log. By default, statements whose query time does not exceed long_query_time but do not use indexes are not recorded. You can configure log_queries_not_using_indexes = on so that all SQL that do not use indexes are recorded in the slow query log (even if the query time does not exceed the value configured by long_query_time).

Steps for using slow query logs:

Using slow query logs is generally divided into four steps:

Open the slow query log.

Set the slow query threshold.

Determine the slow query log path.

Determine the file name of the slow query log.

Enable slow query log (off by default):

Mysql > set global slow_query_log = on; Query OK, 0 rows affected (0.00 sec)

Set the slow query time limit (any query time greater than this value will be recorded in the slow query log (in seconds):

Mysql > set global long_query_time = 1; Query OK, 0 rows affected (0.00 sec)

Determine the slow query log path:

Mysql > show global variables like "datadir"

Determine the slow log file name:

Mysql > show global variables like "slow_query_log_file"

NOTE: tips for setting query time for slow query: it is recommended to set long_query_time to 1 second for online business. If the MySQL of a business requires a higher QPS, slow query can be set to 0.1 second. Find that slow queries are optimized in time or remind developers to rewrite. In general, it is recommended that the threshold set by long_query_time in the test environment is smaller than that in the production environment. For example, if the production environment is 1 second, the test environment is recommended to be set to 0.5 seconds. It is convenient to find some inefficient SQL in time in the test environment. Even some important business test environment long_query_time can be set to 0 to record all statements. And pay attention to the output of the slow query log, after the completion of the functional test before launch, analyze the output of each type of statement in the slow query log, focusing on Rows_examined (the number of rows read from the storage engine during statement execution), and optimize in advance.

Next, after determining the slow query log, you can view it with the command: tail-n5/data/mysql/mysql-slow.log

Here is a detailed description of the above execution result:

Tail-N5: view only the last five lines of the slow query file

Time: time when the slow query occurred

User@Host: client users and IP

Query_time: query time

Lock_time: the time to wait for the table lock

Rows_sent: the number of rows returned by the statement

Rows_examined: the number of rows scanned from the storage engine during statement execution

The above method is viewed with the slow query log that comes with the system. If you find it inconvenient to view the slow query log, friends can use tools such as pt-query-digest or mysqldumpslow to analyze the slow query log. This is not the focus of this section and will not be demonstrated.

Locate slow query through show processlist

Sometimes the slow query is being executed, which has led to a high load on the database, and because the slow query has not been finished, no statements can be seen in the slow query log. At this point, you can use the show processlist command to determine which slow query is being executed. Show processlist shows which threads are running. If you have PROCESS permission, you can see all threads. Otherwise, only the thread of the current session can be seen.

Knowledge extension: if you do not use the FULL keyword, only the first 100 characters of each statement are displayed in the info field. If you want to see the full contents of the statement, you can use full show full processlist.

Here is an explanation of the key parameters of the above results:

Time: indicates the execution time

Info: represents a SQL statement

Here we can determine whether it is a slow SQL by its execution time (Time).

EXLPAIN analysis slow query

Analyzing the execution efficiency of SQL is an important means to optimize SQL. Through the two methods mentioned above, after locating the slow query statement, we will begin to analyze the efficiency of SQL execution. Zi once said: "if you want to do good work, you must first sharpen its tools." we can analyze slow queries through diagnostic tools such as explain, show profile and trace. This section first explains the use of explain, and shares the use of show profile and trace in the next section.

Explain can get the execution plan of the SQL statement in MySQL, such as whether the statement uses associated queries, indexes, the number of rows scanned, and so on. It can help us choose a better index and write a better SQL. How to use it: add explain to the query statement and run it.

Create a test table and insert some data for testing

We created three indexes in the previous chart

PRIMARY KEY (`id`), clustered index KEY `idx_ a` (`a`), nonclustered index KEY `clustered index c` (`b`, `c`) nonclustered index d column did not create an index

Execute three SQL to get the following results

Detailed explanation of Explain field (focus on bold items):

These columns focus on reading:

1. Key interpretation of select_type

2. Type focuses on interpretation: query performance is the best to the worst from top to bottom.

3. Key interpretation of extra

The above is how to locate slow queries in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.