MySQL slow Query Optimization example of slow query log analysis tutorial

The number one problem with slow database response is queries. Most databases now provide performance analysis assistance. Oracle, for example, will help you find slow sentences directly and provide optimization solutions. In MySQL, you have to turn on slow logging for analysis (records can be stored in tables or files, by default they are stored in files, which is what our system USES by default).

First take a look at what the records in the MySQL slow query log look like:

Time         Id Command  Argument
# Time: 141010 9:33:57
# User@Host: root[root] @ localhost [] Id:   1
# Query_time: 0.000342 Lock_time: 0.000142 Rows_sent: 3 Rows_examined: 3
use test;
SET timestamp=1412904837;
select * from t;

This log should make sense, # 1 to record the timestamp, # 2 to record the user and address information for executing the command, # 3 to record the time the query was executed, the time the lock was locked, the number of rows returned, and the number of rows scanned. This is followed by the actual execution of the SQL statement. You can also see what each field in the cvs storage format means by using the following command.

SHOW CREATE TABLE mysql.slow_log;

Let's talk about how to get and analyze slow logs.

View the MySQL slow log parameters

Enter the boot MySQL and execute the following command

mysql> show variables like '%slow_query%';

+---------------------------+----------------------------------------+
| Variable_name       | Value                 |
+---------------------------+----------------------------------------+
| slow_query_log      | OFF                  |
| slow_query_log_file    | /usr/local/mysql/data/cloudlu-slow.log |
+---------------------------+----------------------------------------+

This tells us where the slow log is stored and whether the slow log is on or off.
So what queries need to be logged? In MySQL, no index queries and queries that exceed the specified time and the specified number of scanned rows need to be recorded in the slow log query.

So how do you look at their parameters?

There is no index query record switch

mysql> show global variables like '%indexes%';

+----------------------------------------+-------+
| Variable_name             | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes     | OFF  |
| log_throttle_queries_not_using_indexes | 0   |
+----------------------------------------+-------+

The first parameter indicates whether to turn on a query without index, and the second

Parameter is used to control the flow of logging. How many entries can be logged in one minute? The default 0 is no limit.

Query switches that exceed the specified length of time

mysql> show global variables like '%long_query%';

+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

A query that specifies how much longer than one parameter needs to be logged

A scan query switch that exceeds the specified number of rows

mysql> show variables like '%min_examined_row_limit%';

+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| min_examined_row_limit | 0   |
+------------------------+-------+
1 row in set (0.00 sec)

The default is 0, which means the number of rows will not be scanned now

Set the MySQL slow log on parameter

Enter MySQL and enter the following command or modify it in the startup profile of MySQL or add startup parameters to MySQL. The modification after entering MySQL is as follows:

SHOW CREATE TABLE mysql.slow_log;

0

There are two things to consider here. The first is that the log beyond what length is problematic, depending on the system requirements. The second is the number of logs logged per minute without using indexes, to prevent the impact of too many logs on performance.

In the actual log analysis, the number of SLOW log log is usually quite large, and the number of the same query is also very large. Here, how to find the most problematic and optimized log from the slow log query is needed. In this respect, there are many analysis tools, the most basic analysis tools is MySQL comes with mysqldumpslow, mysqldumpslow(Perl script) output examples:

SHOW CREATE TABLE mysql.slow_log;

1

SHOW CREATE TABLE mysql.slow_log;

2

It is very clear that the output mainly counts the occurrence times of different slow sql (Count 1), the longest execution time (Time 0.00s), the total cumulative time spent (Time 0s), the waiting time for lock (Lock 0.00s), the total waiting time for lock (Lock 0s), the total number of rows sent to the client (Rows 3.0), the total number of rows scanned (Rows 3), The user (root) and the sql statement itself. Its most commonly used parameters include:

-ES93en sort option: c query times r returns the number of rows recorded t query times -ES97en n: Displays top n queries

That's good enough for a 1-like analysis, but not for percentages and so on. Therefore, there are more various MySQL slow log analysis tools in the world, among which mysqlsla(Perl script) and ES1010en-ES107en-ES108en (Perl script) can provide the execution times of Count and sql, as well as the percentage of the total amount of slow log, Time, including the total time, the average time, the minimum time, the maximum time, and the percentage of the time in the total slow sql time. 95% of Time, removing the fastest and slowest sql, covering 95% of the execution time of sql, Lock Time, waiting time for lock, 95% of Lock, 95% of the slow sql sent, resulting in the number of rows, including the average, minimum and maximum number, Rows examined, the number of rows scanned, can also generate the table report, Store the analysis results. I'm not going to introduce 11 here.

Using these slow log analysis software to locate the slow query statement has completed more than 1 percent of SQL optimization. You can then see why SQL queries are slow by executing explain or desc commands in MySQL and looking at the slow query statements.

mysql> explain select * from test.t \G 

SHOW CREATE TABLE mysql.slow_log;

4

The output format details can be looked at MySQL explain format, and the most important ones in the output are:
type: ALL is the least efficient and most important

2. key: Is Key used? How about the length of key

3. Extra: It is best to avoid filesort and temporary. The most important thing is to focus on orderby and groupby.

Note: SQL optimization is a very complex process, the possibility of robbing Peter to pay Paul: have added after the index to a database table, for example, does the query is fast, but add more storage space, insert the delete operation has also increased the time-consuming, if in a write read less system, perform the optimization may have the opposite effect. So do not be careless after the optimization, to continue to monitor the system to prevent the introduction of new bottlenecks.