How to query slow SQL statements in MySQL _ MySQL

How to query slow SQL statements in MySQL 1. query logs

(1) enabling MySQL slow query in Windows

In Windows, the configuration file of MySQL is usually my. ini. find [mysqld] and add

The code is as follows:

Log-slow-queries = F:/MySQL/log/mysqlslowquery. Log
Long_query_time = 2

(2) enable MySQL slow query in Linux

The configuration file of MySQL in Windows is usually my. cnf. find [mysqld] and add

The code is as follows:

Log-slow-queries =/data/mysqldata/slowquery. Log
Long_query_time = 2

Description

Log-slow-queries = F:/MySQL/log/mysqlslowquery.

For the location where slow query logs are stored, this directory generally requires the write permission of the MySQL running account. Generally, this directory is set to the MySQL data storage directory;
2 in long_query_time = 2 indicates that the query takes more than two seconds to record;

2. show processlist command

Show processlist shows which threads are running. You can also useMysqladmin processlistStatement.

Meaning and purpose of each column:

ID column

An identifier that is useful when you want to kill a statement. use the command to kill the query/*/mysqladmin kill process number.

User column

If the user is not the root user, this command only displays the SQL statements within your permission range.

Host column

Displays the port from which the statement is sent. Users used to track problematic statements.

Db column

Displays the database to which the process is currently connected.

Command column

Display the commands executed by the current connection, which are usually sleep, query, and connect ).

Time column

The duration of this status, in seconds.

State Column

Displays the status of the currently connected SQL statement, which is an important column and will be described in the future. Note that the state is only a state in the execution of the statement, an SQL statement, take the query as an example. it may take several statuses such as copying to tmp table, Sorting result, and Sending data to complete the query.

Info column

This SQL statement is displayed. due to the limited length, the long SQL statement is not displayed completely, but it is an important basis for determining the problematic statement.

The most important part of this command is the state column. mysql lists the following states:

Checking table
Checking the data table (this is automatic ).

Closing tables
Refreshing the modified data in the table to the disk and closing the used table. This is a very fast operation. if not, check whether the disk space is full or 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, the temporary table is being converted from memory storage to disk storage to save memory.

Creating tmp table
Creating a temporary table to store some query results.

Deleting from main table
The server is executing the first part of multi-table deletion. The first table has just been deleted.

Deleting from reference tables
The server is executing the second part of multi-table deletion and is deleting records of other tables.

Flushing tables
Executing flush tables, waiting for other threads to close the data table.

Killed
If a kill request is sent to a thread, the thread will check the kill flag and discard the next kill request. MySQL checks the kill flag in each primary loop. However, in some cases, the thread may die after a short period of time. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released.

Locked
It is locked by other queries.

Sending data
Processing the record of the SELECT query and sending the result to the client.

Sorting for group
Sorting for group.
Sorting for order
Sorting order.

Opening tables
This process should be fast unless it is disturbed by other factors. For example, a data TABLE cannot be opened by another thread before the alter table or lock table statement is executed. Opening a table.

Removing duplicates
A select distinct query is being executed, but MySQL cannot optimize those duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records and then send the results to the client.

Reopen table
A lock is obtained for a table, but the lock can be obtained only after the table structure is modified. The lock has been released, the data table is closed, and the data table is being re-opened.

Repair by sorting
The repair command is being sorted to create an index.

Repair with keycache
The repair command is using the index cache to create a new index one by one. It is slower than Repair by sorting.

Searching rows for update
We are talking about identifying qualified records for updates. It must be completed before the related record is updated.

Sleeping
Waiting for the client to send a new request.

System lock
Waiting for an external system lock to be obtained. If multiple mysqld servers are not running to request the same table at the same time, you can add the -- skip-external-locking parameter to disable external system locks.

Upgrading lock
Insert delayed is trying to get a lock table to INSERT a new record.

Updating
Searching for matched records and modifying them.

User Lock
Waiting for GET_LOCK ().

Waiting for tables
This thread is notified that the data table structure has been modified. you need to re-open the data table to obtain the new structure. Then, in order to re-open the data table, you must wait until all other threads close the table. This notification is generated in the following situations: flush tables tbl_name, alter table, rename table, repair table, analyze table, or optimize table.

Waiting for handler insert
Insert delayed has completed all the INSERT operations to be processed and is waiting for new requests.
Most states correspond to fast operations. as long as one thread remains in the same state for several seconds, a problem may occur and you need to check it.

Reference: how to find slow SQL statement http://www.studyofnet.com/news/494.html in MySQL