How to find inefficient SQL statements in a MySQL database

In the face of rapid business development, one of the important tasks of DBA is to discover the inefficient SQL statements in the database in time, some can immediately start to solve (such as lack of appropriate indexes), and some need to feedback to the developer to make changes as soon as possible.

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

1,slow_query_log

This parameter is set to on to capture an SQL statement that executes more than a certain number of times.

2,long_query_time

When the SQL statement executes longer than this number, it is recorded in the log and is recommended to be set to 1 or less.

3,slow_query_log_file

The file name of the record log.

4,log_queries_not_using_indexes

This parameter is set to on to capture all SQL statements that are not indexed, although this SQL statement is likely to execute very quickly.

windows open MySQL slow query

MySQL in Windows system configuration file is generally My.ini find [mysqld] below plus

The code is as follows	Copy Code
Log-slow-queries = F:/mysql/log/mysqlslowquery.log Long_query_time = 2

MySQL slow query enabled under Linux

MySQL in Windows system configuration file is generally my.cnf find [mysqld] below plus

The code is as follows	Copy Code
Log-slow-queries=/data/mysqldata/slowquery.log long_query_time=2

Attention

Log-slow-queries = F:/mysql/log/mysqlslowquery.log for slow query log storage location, general this directory to have MySQL running account can write permission, generally this directory is set to MySQL data storage directory;
2 of the long_query_time=2 means that the query is recorded for more than two seconds;

Once we have configured the above parameters, we can monitor the logs and proceed to resolve the following

Analyze the execution plan for inefficient SQL through EXPLAIN:

After querying for inefficient SQL through the steps above, we can obtain information about how MySQL executes the SELECT statement through explain or DESC, including the order in which the SELECT statement performs the process tables and joins.
Explain can know when to add an index to a table to get a faster select that uses an index to find records.

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| primary| 4|1 | Using Index | | simple| a| all| null| null| NULL |12 | Using where | +----------------+----------+-----------+----------------+----------------+--------- -+-----------+----------------+ 2 rows in Set (0.02 sec)

Select_type:select type

Table: Tables for output result sets
Type: Represents the connection type of a table
System is the best connection type when only one row in the table is type.
The value of type is ref when using the index for table joins in a select operation;
When a Select table connection does not use an index, it is often seen that the value of type is all, representing a full table scan of the table, at which point you need to consider creating indexes to increase the efficiency of table joins.
Possible_keys: An indexed column that can be used when querying.
Key: Indicates the index used
Key_len: Index Length
Rows: Scanning range
Extra: Description and description of implementation

Determine the problem and take the appropriate optimization measures:

Through the above steps, the basic can confirm the cause of the problem, can take corresponding measures according to the situation, optimize and improve the efficiency of execution.
For example, we confirm that the full table scan for table A is not efficient, and we created an index on the Year field of Table A, which has a significantly smaller number of rows to scan.

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| primary| 4|1 | Using Index | | simple| a| Ref| year| year| 4|3 | Using where| +----------------+----------+-----------+----------------+----------------+--------- -+- ----------+----------------+ 2 rows in Set (0.02 sec)