Mysql slow query optimization Examples of slow query log analysis Tutorial _mysql

The query is the most problematic database response. Most databases now provide the means to help with performance analysis. Oracle, for example, will help you find the slow statements directly and provide an optimization solution. In MySQL, you have to open slow logging to analyze (records can be saved in a table or file, the default is saved in the file, our system is using the default method).

First look at the MySQL slow query log what the record looks like:

Time         ID Command  Argument
# time:141010 9:33:57
# User@host:root[root] @ localhost [] Id:   1
# Qu ery_time:0.000342 lock_time:0.000142 rows_sent:3 rows_examined:3 use
test;
SET timestamp=1412904837;
select * from T;

This log should be well understood, the first # record timestamp, second # Record the user and address information that executes the command, and the third # records the time the query was executed, the time of the lock, the number of rows returned, and the number of rows scanned. The SQL statement that is actually executed is then recorded. You can also see the meaning of each field in the CVS storage format with the following command.

Show CREATE TABLE Mysql.slow_log;

Next, let's talk about how to get and analyze slow logs.

View MySQL Slow log parameters

Enter the startup good 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 log is stored in the slow log and whether the slow log is open.
So what kind of query needs to be logged? In MySQL, queries with no index and more than the specified number of scanned rows over a specified time need to be recorded in a slow log query.

So how do they look at their parameters?

No query record switch with index

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 open a query with no index on it, and the second

Parameters used to do logging traffic control, a minute can record how many, the default 0 is to express Unlimited.

Query switch over 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 more than a long time for a parameter needs to be logged

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 is not scanned now

Settings to open MySQL slow log parameters

Enter MySQL, enter the following command or in the MySQL boot configuration file to modify or add the boot parameters to MySQL, after entering MySQL changes as follows:

Set global long_query_time=0.1;
Set global log_queries_not_using_indexes=on;
Set global slow_query_log = on;

Here are 2 points to consider, the first is more than what the length of the log is problematic, which is determined by the system requirements. The second is the number of logs to be logged per minute without using indexes, to prevent too much of the log from affecting performance.

In the actual log analysis, usually slow log logs a lot of, and the same query is recorded a lot of the number of bars, here is how to find from the slow log query the most problematic, most need to optimize the log. In this regard, there are many analysis tools, the most basic analysis tool is the MySQL Mysqldumpslow,mysqldumpslow (perl script) Output Example:

[Root@cloudlu bin]#./mysqldumpslow-s t-t 1/usr/local/mysql/data/cloudlu-slow.log 

Reading mysql slow query log from/usr/local/mysql/data/cloudlu-slow.log 
count:1 time=0.00s (0s) lock=0.00s (0s) Rows =3.0 (3), Root[root] @localhost 
 select * FROM t 

It is very clear that its output mainly counts different slow SQL occurrences (Count 1), execution maximum (time 0.00s), cumulative total time-consuming (time 0s), wait for lock (lock 0.00s), total time to wait 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:

• -S sort option: C query number R return record number of rows T query time
• -T n: Show Top N Query

For the general analysis has been almost, but for the percentage and so the data mysqldumpslow is not perfect. So there are a lot of different MySQL slow log analysis tools in the world, and the Better have Mysqlsla (Perl script) and Pt-query-digest (Perl script) that can provide count, SQL execution times and percentage of the total slow log number, Time, execution times, including total time, average time, minimum, maximum time, percentage of total slow SQL time,% of time, removal of the fastest and slowest SQL, coverage accounted for 95% of SQL execution time, lock time, wait for lock, LOC K, 95% Slow SQL wait lock time, rows sent, result row statistics number, including average, minimum, maximum number, rows examined, number of scanned rows, can also generate a report, storage analysis results. Here is not a description.

Through these slow log analysis software to locate the slow query statement has completed the half of SQL optimization. Next, you can see why SQL queries are slow by performing explain or DESC commands in MySQL to view slow query statements.

Mysql> Explain select * from test.t \g 

1. Row *************************** 
      id:1 
 select_type:simple 
    table:t 
     type:all 
possible_keys:null 
     key:null 
   key_len:null 
     ref:null 
     rows:2 
    extra:null 
1 row in Set (0.00 sec) 

Its output format details can focus on the MySQL explain format, in the output of the most attention is:
1. Type:all is the least efficient and most noteworthy

2. Key: Is there any use of key,key length?

3. Extra: It is best not to appear filesort and temporary, the most important thing is to pay attention to the order and GroupBy.

Note:sql optimization is a very complicated process, it is possible to pay for the situation: for example, after adding the index to the database table, the query is fast, but more storage space, insert delete operation time also increased, if in a less-read system, the implementation of this optimization may play a reverse effect. So after optimization, must not be careless, to continuously monitor the system to prevent the introduction of new bottlenecks.