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

Editor to share with you how to find Mysql query slow SQL statements, I believe that most people do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Add the parameter-log-slow-queries when starting Mysql to record the sql whose execution time exceeds long_query_time seconds:

[@ more@]

Add the parameter-log-slow-queries when starting Mysql to record the sql whose execution time exceeds long_query_time seconds:

/ usr/sbin/mysqld-basedir=/usr-datadir=/var/lib/mysql

-user=mysql-pid-file=/var/run/mysqld/mysqld.pid

-skip-locking-- port=3306-- socket=/var/run/mysqld/mysqld.sock-- log-slow-

Queries=/var/log/mysql/slow.log

Explain to understand the status of SQL execution.

Explain select * from wp_postsG

Use show status like "Handler_read%"; to understand the effect of the index.

A high Handler_read_key value indicates that the index is effective, while a high Handler_read_rnd_ Nextvalue indicates that the index is inefficient.

Use show processlist to view the current running status.

Mysql > show processlist

+-+

| | Id | User | Host | db | Command | Time | State | Info |

+-+

| | 207 | root | 192.168.0.20 Sleep 51718 | mytest | Sleep | 5 | | NULL |

| | 208 | root | 192.168.0.20 Sleep 51719 | mytest | Sleep | 5 | | NULL |

| | 220 | root | 192.168.0.20 Query 51731 | mytest | Query | 84 | Locked |

Select bookname,culture,value,type from book where id=001

First briefly talk about the meaning and purpose of each column, the first column, id, needless to say, a logo, you want to kill a statement is very useful. The user column shows the pre-order user. If it is not root, this command only shows the sql statements within your permissions. The host column, which shows which port of the ip this statement is issued from. Hehe, it can be used to track the user who has the problem statement. The db column that shows which database the process is currently connected to. The command column, which shows the commands executed by the current connection, which are generally sleep, query, connect. Time column, the duration of this state, in seconds. State column, shows the status of the sql statement using the current connection, very important column, there will be a description of all the states later, please note that state is only a certain state in the execution of the statement, a sql statement, which has been queried as an example, may need to be completed through states such as copying to tmp table,Sorting result,Sending data, info column, display this sql statement, because the length is limited, so the long sql statement is not fully displayed But it is an important basis to judge the problem sentence.

The most important thing in this command is the state column. Mysql lists the following states:

Checking table

Checking the datasheet (this is automatic).

Closing tables

The modified data in the table is being flushed to disk and the table that has been used up is being closed. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

Because the temporary result set is larger than tmp_table_size, temporary tables are being converted from memory storage to disk storage to save memory.

Creating tmp table

Creating a temporary table to hold some of the query results.

Deleting from main table

The server is performing the first part of the multi-table deletion, and the first table has just been deleted.

Deleting from reference tables

The server is performing the second part of the multi-table deletion and is deleting records for other tables.

Flushing tables

Executing FLUSH TABLES, waiting for another thread to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.

Locked

Locked by other queries.

Sending data

The record of the select query is being processed and the results are being sent to the client.

Sorting for group

Sorting for GROUP BY.

Sorting for order

Sorting for ORDER BY.

Opening tables

This process should be very fast unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an alter TABLE or LOCK TABLE statement is complete. Trying to open a table.

Removing duplicates

Executing a select DISTINCT

The above is all the contents of the article "how to find out the slow SQL statements in Mysql 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.