MySQL optimized SQL statement consumption

1. Turn on slow log query for SQL

configuration file, complete the following configuration and restart MySQL

Log_queries_not_using_indexes

#这个参数设置为ON, you can capture all SQL statements that do not use an index, although this SQL statement is likely to execute very quickly.

Log-bin=mysql-bin slow_query_log=on long_query_time=2 Log_queries_not_using_indexes=on slow-query-log-file=/var/li B/mysql/slowquery.log

 show variables like  "%slow_query%"; +--------------- ------+------------------------------+| variable_name       |  value                         |+---------------------+------------------------------+| slow_ query_log      | on                            | |  slow_query_log_file | /var/lib/mysql/slowquery.log |+---------------------+-------------- ----------------+2 rows in set  (0.00 sec) 

Show variables like "%long_query_time%" +-----------------+----------+| variable_name | Value |+-----------------+----------+| Long_query_time | 2.000000 |+-----------------+----------+1 row in Set (0.00 sec)

Note that turning on slow query log can have a certain effect on performance.

Parsing slow logs

Mysqldumpslow-s c-t 20/var/lib/mysql/slowquery.log

which

-S, is the way to indicate the sort, C, T, L, R are in accordance with the number of records, time, query time, the number of records returned to sort, AC, at, AL, AR, indicating the corresponding flashbacks;

-T, which is the meaning of top N, which is to return the data of the previous number of bars;

-G, you can write a regular matching pattern, the case is not sensitive;

For example:

Mysqldumpslow-s r-t 10/var/lib/mysql/slowquery.log

Get up to 10 queries that return recordsets.

Mysqldumpslow-s t-t 10-g "left join"/var/lib/mysql/slowquery.log

Get the query that contains the left connection in the first 10 lines sorted by time.

Using the Mysqldumpslow command can be very clear to get a variety of query statements we need, the MySQL query statement monitoring, analysis, optimization is a very important step in MySQL optimization. When the slow query log is turned on, due to the logging operation, CPU resources will be used to affect the performance of MySQL, but it can be opened periodically to locate the performance bottleneck.

2. Short analysis of slow queries (this method is time consuming)

show variables like  "%pro%", +---------------------------+-------+| variable_name              | value |+------------------ ---------+-------+| have_profiling             | yes   | |  profiling                  | off   | |  profiling_history_size    | 15    | |  protocol_version          | 10     | |  proxy_user                 |       | |  slave_compressed_protocol | off   | |  stored_program_cache      |  256   |+---------------------------+-------+7 rows in set  (0.00 sec) 

A. Enabling analysis

Mysql> set profiling=1; #--Open Analysis

After opening the INFORMATION_SCHEMA database will establish a profiling table record.

B. Running the SQL statement to parse

C. Parsing the SQL statement just executed

Show Profiles; #--shows the analysis of all recent statements, the default maximum number is 15, can be set to a maximum number of 100show profiles for query 1; #--View the nth statement just executed, the specific time consumed by show profile cpu,memory for query 1;show profile cpu,block io,memory,swaps,context switches, source for Query 1;

D. Closing the analysis

Set profiling=0; #--Shutdown Analysis

4.show processlist Command

 show processlist;+-----+----------+----------------------+--------------------+---------+-------+-- -----+------------------+| id  | user     | host                  | db                  | command  | Time  | State | Info              |+-----+----------+----------------------+--------------------+---------+------- +-------+------------------+| 861 | warmdata | ip:51724 | collect             | sleep   | 15920  |       | NULL              | | 880 | root     | localhost             | information_schema | Query   |      0 | null  | show processlist | |  881 | warmdata | ip:18730 | information_schema | Sleep    |  1542 |       | NULL              | |  882 | warmdata | ip:18732 | NULL                | Sleep   | 12312 |        | NULL              |+-----+----------+----------------------+--------------------+---------+-------+-------+------------------+ 

Show Processlist shows which threads are running. You can also use the Mysqladmin processlist statement to get this information.

The meaning and purpose of each column:

ID column

An identity that is useful when you want to kill a statement, killing this query with a command/*/mysqladmin the kill process number.

User column

Displays the previous user, if not root, this command displays only the SQL statements that are within the scope of your permission.

Host Column

Shows which IP port this statement was issued from. The user that is used to track the problem statement.

DB column

Shows which database the process is currently connected to.

Command column

Displays commands for the execution of the current connection, typically sleep (sleep), query, connection (connect).

Time column

The duration of this state, in seconds.

State column

Displays the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is only one of the states in the statement execution, an SQL statement, as an example, may need to go through copying to TMP table,sorting Result,sending data and other status can be completed

Info column

Display this SQL statement, because the length is limited, so the long SQL statement is not complete, but a judgment question statement important basis.

The most critical of this command is the State column, which is listed in the following categories:

Checking table

Checking the data table (this is automatic).

Closing tables

The modified data in the table is being flushed to disk, and the tables that have been exhausted are being closed. This is a quick operation, and if not, you should confirm that the disk space is full or that the disk is under heavy load.

Connect out

Replication from the server is connecting to the primary 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 temporary tables to hold partial query results.

deleting from Main Table

The server is performing the first part of a multi-table delete and has just deleted the first table.

deleting from reference tables

The server is performing the second part of a multi-table delete and is deleting records from other tables.

Flushing tables

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

Killed

Sends a KILL request to a thread, the thread checks the kill flag bit and discards the next kill request. MySQL checks the kill flag bit in each of the main loops, but in some cases the thread may die in a short period of time. If the line regulation regulation is locked by another thread, the kill request will take effect as soon as the lock is released.

Locked

Locked by another query.

Sending data

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

Sorting for group

Sorting is being done for group by.

Sorting for order

The order by is being sorted.

Opening tables

The process should be quick, unless other factors interfere with it. For example, a data table cannot be opened by another thread until the row of the ALTER TABLE or LOCK TABLE statement is complete. Attempting to open a table.

removing duplicates

A query that is executing a SELECT distinct method is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again, and then send the results to the client.

Reopen table

A lock on a table is obtained, but it must be changed after the table structure has been modified. The lock has been released, the data table is closed, and the data table is being tried again.

Repair by sorting

Repair instructions are being sorted to create an index.

Repair with Keycache

The repair instructions are using the index cache to create a new index one by one. It will be slower than repair by sorting.

Searching rows for update

The qualifying records are being told to find out to prepare for the update. It must be completed before the update is about to modify the related records.

Sleeping

Waiting for the client to send a new request.

System Lock

Is waiting to get an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can suppress the external system lock by increasing the--skip-external-locking parameter.

Upgrading lock

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

Updating

Searching for matching records, and modifying them.

User Lock

Waiting for Get_lock ().

Waiting for tables

The thread is notified that the data table structure has been modified and the data table needs to be reopened to obtain a new structure. Then, to be able to reopen the data table, you must wait until all other threads close the table. This notification is generated in the following cases: FLUSH TABLES tbl_name, ALTER table, RENAME table, REPAIR table, ANALYZE table, or optimize table.

Waiting for handler insert

Insert delayed has processed all pending insertions and is waiting for a new request.

Most of the state corresponds to a fast operation, so long as one thread remains in the same state for several seconds, a problem may occur and need to be checked.

There are other states that are not listed above, but most of them are only useful to see if there is an error in the server.

5. Using explain

explain select * from c_task_detail_assist;+----+-------------+----------------------+---- --+---------------+------+---------+------+------+-------+| id | select_type | table                 | type  | possible_keys | key  | key_len | ref  | rows  | extra |+----+-------------+----------------------+------+---------------+------+---------+-- ----+------+-------+|  1 | simple      | c_task_detail_ assist | all  | null          |  NULL | NULL    | NULL | 3220 |        |+----+-------------+----------------------+------+---------------+------+---------+------+- -----+-------+1 row in set  (0.00 SEC) 

Explanation of the Explain column

Table

Shows which table the data for this row is about

Type

This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all

Possible_keys

Displays the indexes that may be applied in this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain

Key

The index that is actually used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used 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 to use. The shorter the length the better, without loss of accuracy

Ref

Shows which column of the index is used and, if possible, a constant

Rows

The number of rows that MySQL considers must be checked to return the requested data

Extra

Additional information about how MySQL parses the query. It will be discussed in Table 4.3, but the bad examples you can see here are the using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be slow

The meaning of the description returned by the extra column

Distinct

Once MySQL finds a row that matches the row, it no longer searches for

NOT EXISTS

MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches

Range checked for each Record (index map:#)

The ideal index was not found, so for each combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

Using Filesort

When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

Using Index

Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of the table are part of the same index

Using Temporary

When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by

Where used

A WHERE clause is used to restrict which rows will match the next table or are returned to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem different connection types of interpretation (in order of efficiency)

Const

The maximum value of a record in a table matches the 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 reads the value first and treats it as a constant.

Eq_ref

In the connection, when MySQL queries, from the previous table, the union of each record reads a record from the table, which is used when the query uses the index as the primary key or the unique key.

Ref

This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better

Range

This connection type uses the index to return rows in a range, such as what happens when you use > or < to find something

Index

This connection type makes a full scan of each record in the previous table (better than all because the index is generally less than the table data)

All

This connection type has a full scan of each previous record, which is generally bad and should be avoided as much as possible.

Reference Link: http://blog.sina.com.cn/s/blog_ad0672d60102vag4.html

Http://zhidao.baidu.com/link?url=209y3CUJ84_ Kzpk5plelnu5rxbuszmptd81gi8z-pa7hq61auy4pr-ht3etoatp5kix0vg8ivbihcfd1cuyh-8jo9boj3flcomqvefqurs7

http://blog.csdn.net/lxpbs8851/article/details/7834836 (recommended)

MySQL optimized SQL statement consumption