MySQL analyzes and finds SQL statements with slow execution efficiency

When MySQL is started, add the -- log-Slow-queries parameter to record the SQL statements whose execution time exceeds long_query_time seconds.

MySQL slow
The log analysis tool mysqldumpslow.
Slow log is MYSQL based on SQL
The execution time of the statement. It is written into a file for analyzing slow statements.

You only need to configure in the my. CNF file:
Log-Slow-queries =
[Slow_query_log_filename]
You can record the SQL statement that exceeds the default 10 s execution time.
Statement.
To modify the default settings, you can add:
Long_query_time = 5
Set to 5S.

Explain to understand the SQL Execution status.
Explain select * From wp_posts \ G;

 

Explain shows how MySQL uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements.

You can add the explain statement before the SELECT statement:

Example: Explain select surname, first_name Form A, B where a. ID = B. ID

Description of the explain column:

• Table: displays the data of this row about which table
• Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and all.
• Possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the where statement for the relevant domain.
• Key: actually used index. If it is null, no index is used. In rare cases, MySQL selects an optimized index. In this case, you can use index (indexname) in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index.
• Key_len: the length of the index used. The shorter the length, the better.
• Ref: indicates which column of the index is used. If possible, it is a constant.
• Rows: the number of rows that MySQL deems necessary to check to return the requested data
• Extra: Extra information about how MySQL parses the query. We will discuss it in table 4.3, but here we can see that the bad examples are using temporary and using filesort, which means MySQL cannot use indexes at all, and the result is that the retrieval will be slow.

Meaning of the description returned by the extra column

• Distinct: Once MySQL finds the row that matches the row, it does not search again.
• Not exists: MySQL optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.
• Range checked for each record (index map: #): no ideal index is found. Therefore, for each row combination in the preceding table, MySQL checks which index is used, use it to return rows from the table. This is one of the slowest connections using indexes.
• Using filesort: When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.
• Using index: the column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are in the same index.
• When using temporary sees this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.
• Where used uses the WHERE clause to limit which rows match the next table or return the rows to the user. If you do not want to return all rows in the table and the connection type is all or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order)
• The system table has only one row: system table. This is a special case of the const connection type.
• Const: the maximum value of a record in the table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because MySQL first reads this value and treats it as a constant.
• Eq_ref: During the connection, MySQL reads a record from the table from the Union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key.
• Ref: This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the fewer the better
• Range: This connection type uses an index to return rows in a range, such as> or <what happens when something is searched
• Index: This connection type performs a full scan of each record in the preceding table (better than all, because the index is generally smaller than the table data)
• ALL: this connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.

Use show status like "handler_read %"; to understand the index effect.
The high value of handler_read_key indicates that the index effect is good, and the high value of handler_read_rnd_next 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: 51718 | mytest | sleep | 5 | null

| 208 | root | 192.168.0.20: 51719 | mytest | sleep | 5 | null

| 220 | root | 192.168.0.20: 51731 | mytest | query | 84 | locked |

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

Let's briefly describe the meaning and purpose of each column,

ID column, an identifier. It is useful when you want to kill a statement. Use the command to kill the query/*/mysqladmin kill process number.

The user column displays the user before the ticket. If it is not the root user, this command only displays the SQL statements within your permission range.

Host column, indicating the port from which the statement is sent. Users used to track problematic statements.

DB column to display the database to which the process is currently connected.

Command column, showing the commands executed by the current connection, which is generally sleep, query, and connect ).

Time column, the duration of this state, in seconds.

The State column displays the status of the SQL statement that uses the current connection. It is an important column and will be described in the future. Note that the State is only a certain State in the statement execution, an SQL statement, taking the query as an example, may need to pass through the copying to TMP table, sorting result, sending data and other States to complete,

The info column shows this SQL statement. Because the length is limited, the long SQL statement is not displayed completely, but an important basis for determining the problem 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.
Other statuses are not listed above, but most of them are only needed to check whether there are errors on the server.

 

The MySQL Manual contains descriptions of all states, links to the following: http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Transfer