How to find out inefficient SQL statements in the MySQL database

To find out the inefficient SQL statements in mysql, you can use EXPLAIN to analyze the inefficient SQL statements. However, before using EXPLAIN, you need to enable mysql slow query logs to use EXPLAIN, let's take a look.

In the face of rapid business development, an important task of DBA is to promptly discover inefficient SQL statements in the database, and some can immediately solve the problem (such as the lack of appropriate indexes ), some need to be reported to developers for modification as soon as possible.

MySQL database has several configuration options to help us capture inefficient SQL statements in a timely manner:

1, slow_query_log

This parameter is set to ON to capture SQL statements whose execution time exceeds a certain value.

2, long_query_time

When the SQL statement execution time exceeds this value, it is recorded in the log. We recommend that you set it to 1 or shorter.

3, slow_query_log_file

The log file name.

4, log_queries_not_using_indexes

This parameter is set to ON, which can capture all SQL statements without indexes, although this SQL statement may be executed very quickly.

Enable 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:	Copy code
Log-slow-queries = F:/MySQL/log/mysqlslowquery. log Long_query_time = 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:	Copy code
Log-slow-queries =/data/mysqldata/slowquery. log Long_query_time = 2

Note:

Log-slow-queries = F:/MySQL/log/mysqlslowquery. log is the location where slow query logs are stored. Generally, this directory 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;

After configuring the preceding parameters, we can monitor the logs and solve the problems as follows:

EXPLAIN the execution plan of inefficient SQL statements:

After a low-efficiency SQL statement is queried through the above steps, we can use explain or desc to obtain information about how MySQL executes the SELECT statement, including how the select statement execution table connects to and connects to the sequence.
Explain can know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records.

The Code is as follows:

Copy code

Mysql> explain select sum (moneys) from sales a, companys B where a. company_id = B. id and a. year = 2006; + ---------------- + ---------- + ----------- + ---------------- + --------- -+ ----------- + ---------------- + | Select_type | table | type | possible_keys | key | key_len | rows | Extra | + ---------------- + ---------- + ----------- + ---------------- + --------- -+ ----------- + ---------------- + | SIMPLE | B | index | PRIMARY | 4 | 1 | Using index | | SIMPLE | a | ALL | NULL | 12 | Using where | + ---------------- + ---------- + ----------- + ---------------- + --------- -+ ----------- + ---------------- + 2 rows in set (0.02 sec)

Select_type: select Type

Table: The table of the output result set.
Type: indicates the table connection type.
When only one row in the table is type, the system is the best connection type;
When the select Operation uses indexes for table join, the value of type is ref;
When the select Table connection does not use an index, it is often seen that the value of type is ALL, indicating that the table has been fully scanned, in this case, you need to consider creating indexes to improve the efficiency of table connection.
Possible_keys: indicates the index columns that can be used during query.
Key: indicates the index used.
Key_len: Index Length
Rows: scan range
Extra: Description and description of execution

Determine the problem and take corresponding optimization measures:

After the above steps, you can confirm the cause of the problem and take appropriate measures based on the situation to optimize and improve the execution efficiency.
For example, in the preceding example, we confirm that the full table scan of Table a results in unsatisfactory efficiency. We created an index for the year field of Table, the number of rows to be scanned is obviously small.

The Code is as follows:

Copy code

Mysql> explain select sum (moneys) from sales a, companys B where a. company_id = B. id and a. year = 2006; + ---------------- + ---------- + ----------- + ---------------- + --------- -+ ----------- + ---------------- + | Select_type | table | type | possible_keys | key | key_len | rows | Extra | + ---------------- + ---------- + ----------- + ---------------- + --------- -+ ----------- + ---------------- + | SIMPLE | B | index | PRIMARY | 4 | 1 | Using index | | SIMPLE | a | ref | year | 4 | 3 | Using Where | + ---------------- + ---------- + ----------- + ---------------- + --------- -+ ----------- + ---------------- + 2 rows in set (0.02 sec)