MySQL slow query, MySQL

MySQL slow query, MySQL

Brief description:

To analyze the query performance of MySQL statements, in addition to using the EXPLAIN command to output the execution plan, MySQL can also record the statements whose query exceeds the specified time, the SQL statement query that exceeds the specified time is called "Slow query ".

It records all SQL statements that have been executed longer than long_query_time, and helps you find slow SQL statements to optimize these SQL statements.

When optimizing MySQL, you usually need to analyze the database. Common analysis methods include slow query logs, EXPLAIN analysis and query, profiling analysis, and show command query system status and system variables, by locating the analysis performance bottleneck, we can better optimize the performance of the database system.

Ideas:

--> Analyze slow query logs --> View table structure and table status --> View table indexes --> analyze SQL statements --> explain --> modify SQL statements --> verify modification results

Slow query log Configuration:

1. Add the following options in the my. ini configuration file:

Log-slow-queries = master-0-slow.log slow query log location

Log-queries-not-using-indexes = on queries without indexes are also counted in slow Query logs.

Long_query_time = 1 when the Query statement is greater than 1 second, it is recorded in the slow Query log.

--> How to enable slow query in mysql in linux:

The configuration file of mysql in linux is usually my. cnf, and my path is/etc/my. cnf. You can find vi/etc/my. cnf Based on the compiled and installed path.

The same is added under [mysqld:

Log-slow-queries =/var/lib/mysql/slowquery. log

Long_query_time = 2

Log-queries-not-using-indexes

I will not explain it here. After mysql is restarted, you can go to/var/lib/mysql to check whether slowquery. log has generated. cd/var/lib/mysql/

2. query the slow query configuration information in the Command window

① View the Query statement execution time greater than the number of seconds counted into the slow Query log

 

Dynamic configuration through the command line: MySQL> set long_query_time = 1;

② Check whether slow query logs are opened and stored in the path

 

Dynamic configuration through the command line: MySQL> set global slow_query_log = 'on'

Slow query log format

# Time: 120331 10:05:48

# User @ Host: root [root] @ 91SK-B49337164E [10.10.10.99]

# Query_time: 14.031250 Lock_time: 0.218750 Rows_sent: 0 Rows_examined: 90785 SET timestamp = 1333159548; delete from orderinfo;

This is one of the slow query logs. It takes 14.031250 seconds, locks 0.218750 seconds, returns 0 rows, and queries 90785 rows in total.

Run the mysqldumpslow command to view slow query logs:

1. because mysql's built-in command mysqldumpslow. pl uses a perl script, you need to install the perl environment. Download and install the activeperl.exe installation package.

2. Execute the mysqldumpslow command under the doscommand line.

 

Use the mysqldumpslow command to find the top 10 SQL statements that have the most records in the f: \ master-0-slow.log slow query log. And put it in the f: \ master-slow-0.txt file.

Mysqldumpslow Command Parsing:

-S indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of returned records, ac, at, al, and ar indicate reverse descriptions;

-T indicates the top n, that is, the number of previous data records returned;

-G, followed by a regular expression matching mode, which is case insensitive;

For example, 10 queries with a maximum record set are returned.

The first 10 query statements sorted by time contain left join statements.