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

This article mainly explains "how to find inefficient SQL statements in mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to find inefficient SQL statements by mysql".

MySQL uses slow query logs to locate those inefficient SQL statements. When starting with the-- log-slow-queries [= file_name] option, mysqld will write a log file containing all SQL statements that have been executed for more than long_query_time seconds, and locate the less efficient SQL by viewing this log file.

1. MySQL database has several configuration options that can help us capture inefficient SQL statements in time.

1,slow_query_log

This parameter is set to ON to capture SQL statements that take more than a certain number of times to execute.

2,long_query_time

When the execution time of the SQL statement exceeds this value, it is recorded in the log, which is recommended to be set to 1 or less.

3,slow_query_log_file

The file name of the log.

4,log_queries_not_using_indexes

This parameter is set to ON to capture all SQL statements that are not using an index, although this SQL statement is likely to execute quite quickly.

2. The method of testing the efficiency of sql statements in mysql

1. By querying logs

(1) enable MySQL slow query under Windows

The configuration file of MySQL in Windows system is usually added after my.ini finds [mysqld].

The code is as follows

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

Long_query_time = 2

(2) enable MySQL slow query under Linux

The configuration file of MySQL in Windows system is usually added after my.cnf finds [mysqld].

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 of slow log storage, generally, this directory must have writeable access to the running account of MySQL. Generally, this directory is set to the data storage directory of MySQL.

The 2 in long_query_time=2 means that the query takes more than two seconds to record.

2.show processlist command

WSHOW 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

A flag that is useful when you want to kill a statement, kill the query / * / mysqladmin kill process number with the command.

User column

Displays a single user. If it is not root, this command only displays sql statements within your permissions.

Host column

Shows which ip and which port the statement is issued from. Used to track the user with the problem statement.

Db column

Shows which database this process is currently connected to.

Command column

The commands that show the execution of the current connection are generally sleep, query, and connect.

Time column

The duration of this state, in seconds.

State column

Show the status of the sql statement using the current connection. Important columns will have descriptions of all the states later. Please note that state is only a certain state in the execution of the statement, a sql statement. Take a query as an example, it 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 complete, but it is an important basis for judging the problem statement.

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

An SELECT DISTINCT query 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 acquired, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet.

Repair by sorting

The repair directive is sorting to create an index.

Repair with keycache

The repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting.

Searching rows for update

We are talking about finding qualified records for updating. It must be done before UPDATE modifies the relevant records.

Sleeping

Waiting for the client to send a new request.

System lock

Waiting to acquire an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding 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 informed that the data table structure has been modified and needs to be reopened to get the new structure. Then, in order to reopen the table, you must wait until all other threads close the table. This notification occurs 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 processed all pending insert operations and is waiting for a new request.

Most states correspond to fast operations, and as long as one thread stays in the same state for a few seconds, there may be a problem that needs to be checked.

There are other states not listed above, but most of them are only useful to see if there are any errors on the server.

For example, as shown in the figure:

3. Explain to understand the status of SQL execution

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

To use the method, add explain before the select statement:

For example:

one

Explain select surname,first_name form a,b where a.id=b.id

The result is as shown in the picture

Interpretation of EXPLAIN column

Table

Show which table the data in 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 to this table. If empty, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain

Key

The index actually used. If NULL, the index is not used. In rare cases, MYSQL chooses indexes that are not sufficiently optimized. In this case, you can use USE INDEX (indexname) in the SELECT statement to force the use of an index or IGNORE INDEX (indexname) to force MYSQL to ignore the index

Key_len

The length of the index used. The shorter the length, the better without losing accuracy.

Ref

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

Rows

The number of rows that MYSQL believes must be checked to return the request data

Extra

Additional information about how MYSQL parses queries. It will be discussed in Table 4.3.But the bad examples you can see here are Using temporary and Using filesort, meaning that MYSQL cannot use indexes at all, and the result is that retrieval will be slow

The meaning of the description returned by the extra column

Distinct

Once MYSQL finds a row that matches the row union, it no longer searches

Not exists

MYSQL optimizes LEFT JOIN so that once it finds rows that match the LEFT JOIN standard, it no longer searches

Range checked for each Record (index map:#)

No ideal index was found, so for each combination of rows from the previous table, MYSQL checks which index to use and uses it to return rows from the table. This is one of the slowest connections to use an index

Using filesort

When you see this, the query needs to be optimized. MYSQL needs to take additional steps to discover how to sort the returned rows. It sorts all rows according to the connection type and the row pointer to all rows that store the sort key values and matching criteria.

Using index

Column data is returned from a table that only uses the information in the index and does not read the actual action, which occurs when all request columns to 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 the ORDER BY of different sets of columns, not on the GROUP BY

Where used

The WHERE clause is used to restrict which rows will match the next table or be returned to the user. This can happen if you do not want to return all the rows in the table and the join type is ALL or index, or the query has a problem with the interpretation of different join types (sorted in order of efficiency)

Const

The maximum value of a record in the table can match this query (the index can be a primary key or unique index). Because there is only one line, this value is actually a constant, because MYSQL reads the value first and then treats it as a constant.

Eq_ref

In a join, MYSQL reads a record from the table when querying, the union of each record from the previous table, which is used when the query uses all of the index as the primary key or the unique key

Ref

This join type occurs only if the query uses keys that are not unique or primary keys or parts of these types (for example, using the leftmost prefix). For each row join of the previous table, all records are read out from the table. This type depends heavily on how many records are matched by the index-the fewer the better

Range

This connection type uses an index to return rows in a range, such as using > or show variables like 'log_slow_queries'

+-+ +

| | Variable_name | Value |

+-+ +

| | log_slow_queries | ON |

+-+ +

1 row in set (0.00 sec)

(2) check how many seconds slower SQL execution will be recorded in the log file

Mysql > show variables like 'long_query_time'

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 1 | |

+-+ +

1 row in set (0.00 sec)

Here, value=1 means 1 second.

two。 Configure the my.ini file (my.cnf under inux), find the [mysqld] section, and add the configuration of the log, as shown in the following example:

[mysqld]

Log= "C:/temp/mysql.log"

Log_slow_queries= "C:/temp/mysql_slow.log"

Long_query_time=1

Log indicates the directory where the log files are stored

Log_slow_queries indicates the sql log directory where the record takes a long time to execute

Long_query_time indicates how long it takes to execute, in s.

These configuration items should already exist under Linux, but they have been commented out, and the comments can be removed. But directly adding configuration items is also OK.

After querying inefficient SQL statements, you can use the EXPLAIN or DESC command to obtain information on how MySQL executes SELECT statements, including the order in which tables are joined during the execution of SELECT statements. For example, if we want to calculate the sales of all companies in 2006, we need to associate sales table and company table, and sum the profit field. The corresponding SQL execution plan is as follows:

Mysql > explain select sum (profit) from sales afield company b where a.company_id = b.id and a.year = 2006\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: a

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 12

Extra: Using where

* 2. Row * *

Id: 1

Select_type: SIMPLE

Table: b

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 12

Extra: Using where

2 rows in set (0.00 sec)

Each column is explained as follows:

Select_type: indicates the type of SELECT. Common values include SIMPLE (simple table, that is, no table join or subquery), PRIMARY (main query, that is, outer query), UNION (the second or subsequent query statement in UNION), SUBQUERY (the first SELECT in subquery), and so on.

Table: the table that outputs the result set.

Type: indicates the join type of the table. The join types with good to poor performance are system (there is only one row in the table, that is, the constant table), const (there is at most one matching row in a single table, such as primary key or unique index), eq_ref (for each previous row, only one record is queried in this table, simply speaking, primary key or unique index is used in multi-table joins), ref (similar to eq_ref) The difference is that instead of using primary key or unique index, you use a normal index), ref_or_null (similar to ref, except that the condition includes a query for NULL), index_merge (index merge optimization), unique_subquery (a subquery of a query primary key field followed by in), index_subquery (similar to unique_subquery The difference is that in is followed by a subquery that queries non-unique index fields, range (a range query in a single table), index (data is obtained by a query index for each previous row), and all (data is obtained by a full table scan for each previous row).

Possible_keys: represents the index that may be used when querying.

Key: represents the index actually used.

Key_len: the length of the index field.

Rows: the number of scan lines.

Extra: description and description of the implementation.

In the above example, it has been confirmed that a full table scan of table a caused the inefficiency, so create an index on the year field of table a, as follows:

Mysql > create index idx_sales_year on sales (year)

Query OK, 12 rows affected (0.01sec)

Records: 12 Duplicates: 0 Warnings: 0

After the index is created, the execution plan for this statement is as follows:

Mysql > explain select sum (profit) from sales afield company b where a.company_id = b.id and a.year = 2006\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: a

Type: ref

Possible_keys: idx_sales_year

Key: idx_sales_year

Key_len: 4

Ref: const

Rows: 3

Extra:

* 2. Row * *

Id: 1

Select_type: SIMPLE

Table: b

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 12

Extra: Using where

2 rows in set (0.00 sec)

It can be found that after the establishment of the index, the number of rows that need to be scanned on the a table is significantly reduced (from full table scan to 3 rows). It can be seen that the use of the index can greatly improve the access speed of the database, especially when the table is very large. Using index to optimize sql is a common basic method to optimize sql. In the following chapters, we will specifically introduce how to make the index to optimize sql.

This article mainly introduces the MySQL slow query analysis method. Some time ago, I set up a record in the MySQL database to query SQL statements that are slower than 1 second. Remember that there are several ten-point setting methods, and the names of several parameters can not be recalled, so reorganize them and take notes by yourself.

For troubleshooting problems and finding performance bottlenecks, the easiest problems to find and solve are MySQL slow queries and queries that do not have available indexes.

OK, start finding the SQL statements in MySQL that don't "feel good" to execute.

MySQL slow query analysis method 1:

I am using this method, hehe, I prefer this kind of immediacy.

Versions of MySQL5.0 and above can support the recording of slowly executed SQL statements.

MySQL > show variables like 'long%'

Note: this long_query_time is used to define how many seconds is slower than the "slow query".

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 10.000000 | |

+-+ +

1 row in set (0.00 sec)

MySQL > set long_query_time=1

Note: I set 1, that is, if the execution time is more than 1 second, it is a slow query.

Query OK, 0 rows affected (0.00 sec)

MySQL > show variables like 'slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | slow_launch_time | 2 | |

| | slow_query_log | ON |

Note: whether to turn on logging

| | slow_query_log_file | / tmp/slow.log |

Note: where is the setting?

+-+ +

3 rows in set (0.00 sec)

MySQL > set global slow_query_log='ON'

Note: open logging

Recording starts as soon as the slow_query_log variable is set to ON,MySQL.

The initial value of the above MySQL global variable can be set in / etc/my.cnf.

Long_query_time=1 slow_query_log_file=/tmp/slow.log

MySQL slow query analysis method 2:

MySQLdumpslow command

/ path/MySQLdumpslow-s c-t 10 / tmp/slow-log

This outputs the 10 SQL statements with the most records, of which:

-s indicates how to sort. C, t, l, r are sorted by the number of records, time, query time, and the number of records returned. Ac, at, al, and ar indicate the corresponding flashbacks.

-t, which means top n, that is, how many previous pieces of data are returned.

-g, a regular matching pattern can be written later, which is case-insensitive.

such as

/ path/MySQLdumpslow-s r-t 10 / tmp/slow-log

Get the 10 queries that return the most recordsets.

/ path/MySQLdumpslow-s t-t 10-g "left join" / tmp/slow-log

Get the query statements with left links in the first 10 items sorted by time.

A simpler way:

Open my.ini, find [mysqld] and add long_query_time = 2 log-slow-queries = D:/mysql/logs/slow.log # below it. Write the log there. It can be empty. The system will give a default file # log-slow-queries = / var/youpath/slow.log linux. "host_name-slow.log log-queries-not-using-indexes long_query_time" refers to how long (in seconds) the sql will be recorded, which is set here as 2 seconds.

The following is a description of the common parameters of mysqldumpslow. For more information, you can apply mysqldumpslow-help query. -s indicates how to sort, c, t, l, r are sorted by the number of records, time, query time, and the number of records returned (from large to small), and ac, at, al, and ar represent the corresponding flashbacks. -t, which means top n, means how many previous pieces of data are returned. Www.jb51.net-g, followed by a regular matching pattern, which is case-insensitive. The next step is to use the slow query tool mysqldumpslow that comes with mysql to analyze (under the bin directory of mysql). My log file here is named host-slow.log. List the 10 sql statements with the largest number of records mysqldumpslow-s c-t 10 host-slow.log list the 10 sql statements that return the most recordsets mysqldumpslow-s r-t 10 host-slow.log return the first 10 sql statements with left joins in time mysqldumpslow-st- t 10-g "left join" host-slow.log using the mysqldumpslow command can clearly get all kinds of query statements we need It is of great help to monitor, analyze and optimize MySQL query statements.

In daily development, we often encounter the situation that the opening speed of the page is very slow. Through the exclusion, it is determined that it is the impact of the database. In order to quickly find the specific SQL, we can use the logging method of Mysql.

-- Open sql to perform recording function

Set global log_output='TABLE';-- output to a table

Set global log=ON;-Open all commands to execute the recording function general_log, all statements: successful and unsuccessful.

Set global log_slow_queries=ON;-Open slow query sql record slow_log. Successful execution: slow query statements and statements that do not use indexes

Set global long_query_time=0.1;-slow query time limit (seconds)

Set global log_queries_not_using_indexes=ON;-record sql statements that do not use indexes

-- query sql execution records

Select * from mysql.slow_log order by 1;-- successful execution: slow query statements, and statements that do not use indexes

Select * from mysql.general_log order by 1;-- all statements: successful and unsuccessful.

-- close sql execution record

Set global log=OFF

Set global log_slow_queries=OFF

-- long_query_time parameter description

-- v4.0, 4.1, 5.0, V5.1 to 5.1.20 (inclusive): slow query analysis at millisecond level is not supported (1-10 seconds precision is supported)

-- version 5.1.21 and later: support slow query analysis at millisecond level, such as 0.1

-- 6.0 to 6.0.3: slow query analysis at millisecond level is not supported (1-10 seconds precision is supported)

6.0.4 and later: support slow query analysis at millisecond level

Through the Sql recorded in the log, quickly locate the specific file, optimize the sql to see whether the speed has been improved?

This paper analyzes the problem of slow query of MySQL database server, and puts forward the corresponding solutions. The specific solutions are as follows: developers will often find that developers check statements without indexes or statements without limit n, which will have a big impact on the database.

This paper analyzes the problem of slow query of MySQL database server, and puts forward the corresponding solutions. the specific solutions are as follows:

It is often found that developers check statements without indexes or statements without limit n, which will have a great impact on the database, such as a large table with tens of millions of records to be scanned, or constantly doing filesort, causing io impact on the database and server, and so on. This is the situation above the mirror library.

When it comes to the online library, in addition to the statements that do not have an index and do not use limit, there is one more case, the problem of too many mysql connections. Speaking of which, let's take a look at our monitoring practices in the past.

1. Deploy open source distributed monitoring systems such as zabbix to obtain the io,cpu and connections of the daily database

two。 Deploy weekly performance statistics, including data growth and iostat,vmstat,datasize

3. Mysql slowlog collection, listing top 10

I used to think that it was perfect to do these monitoring, but now after the deployment of mysql node process monitoring, I found a lot of disadvantages.

The disadvantages of the first approach: the zabbix is too large, and it is not monitored within mysql, and a lot of data is not very prepared. Now it is generally used to look up historical data.

The disadvantage of the second method: because it runs only once a week, many cases cannot be detected and reported to the police.

The downside of the third approach: when the node has a lot of slowlog, top10 becomes meaningless, and most of the time it will give you regular task statements that must be run. The reference is of little value.

So how do we solve and inquire about these problems?

For troubleshooting problems and finding performance bottlenecks, the easiest problems to find and solve are slow queries in MYSQL and queries that do not have available indexes.

OK, start finding the SQL statements in mysql that don't "feel good" to execute.

Method 1: I am using this method, hehe, I prefer this kind of immediacy.

Versions of Mysql5.0 and above can support the recording of slowly executed SQL statements.

Mysql > show variables like 'long%'; Note: this long_query_time is used to define the number of seconds before a "slow query"

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 10.000000 | |

+-+ +

1 row in set (0.00 sec)

Mysql > set long_query_time=1; Note: I set 1, that is, any query that takes more than 1 second to execute is a slow query.

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | slow_launch_time | 2 | |

| | slow_query_log | ON | Note: whether to enable logging |

| | slow_query_log_file | / tmp/slow.log | Note: where to set it |

+-+ +

3 rows in set (0.00 sec)

Mysql > set global slow_query_log='ON' Note: turn on logging

Recording starts as soon as the slow_query_log variable is set to ON,mysql.

The initial value of the above MYSQL global variable can be set in / etc/my.cnf.

Long_query_time=1

Slow_query_log_file=/tmp/slow.log

Method 2: mysqldumpslow command

/ path/mysqldumpslow-s c-t 10 / tmp/slow-log

This outputs the 10 SQL statements with the most records, of which:

-s indicates how to sort. C, t, l, r are sorted by the number of records, time, query time, and the number of records returned. Ac, at, al, and ar indicate the corresponding flashbacks.

-t, which means top n, that is, how many previous pieces of data are returned.

-g, a regular matching pattern can be written later, which is case-insensitive.

such as

/ path/mysqldumpslow-s r-t 10 / tmp/slow-log

Get the 10 queries that return the most recordsets.

/ path/mysqldumpslow-s t-t 10-g "left join" / tmp/slow-log

Get the query statements with left links in the first 10 items sorted by time.

Finally, summarize the benefits of node monitoring.

1. Lightweight monitoring, which is real-time and can be customized and modified according to the actual situation.

2. A filter program is set up to filter those statements that must run.

3. It is worthwhile to find those queries that do not use indexes or are illegal in time, although it takes time to deal with those slow statements, but it can avoid database hanging.

4. When there are too many connections in the database, the program will automatically save the processlist,DBA of the current database to find the reason. This is a sharp weapon.

5. When using mysqlbinlog to analyze, you can get a clear period of time when the database state is abnormal

Some people will suggest that we do the mysql configuration file settings.

Found some other parameters when adjusting tmp_table_size

Number of queries that Qcache_queries_in_cache has registered in the cache

Number of queries that Qcache_inserts was added to the cache

Number of Qcache_hits cache samples

The number of queries Qcache_lowmem_prunes deleted from the cache due to lack of memory

The number of queries that Qcache_not_cached has not been cached (cannot be cached, or due to QUERY_CACHE_TYPE)

Total free memory of Qcache_free_memory query cache

The number of free memory blocks in the Qcache_free_blocks query cache

The total number of blocks in the Qcache_total_blocks query cache

Qcache_free_memory can cache some commonly used queries, and if it is a common sql, it will be loaded into memory. That will increase the speed of database access.

At this point, I believe you have a deeper understanding of "how to find inefficient SQL statements in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.