Online MySQL slow query optimization Example 2 (1)-reprint

Slow Query Optimization of MySQL is a common topic. This article describes the actual problems encountered in the production database and provides examples.

Enable slow query support

First, activate the slow query log, modify the my. CNF configuration file, and add the following options:
Log-Slow-queries = slow. Log
Long_query_time = 1
---- If a patch is applied, you can specify a smaller value.
Log-queries-not-using-Indexes

However
Then, the slow. log is cut by day, for example
Slow. log.20100405

Analyze slow query logs

The most important tool for analyzing slow query statements is mysqldumpslow officially provided by MySQL. For detailed usage, see my previous article

Mysqldumpslow-s t-T 5
Slow. log.20100405
> Analyse.txt

The preceding statement sorts slow query statements by the total time, instead of mysqldumpslow by default by the average time of statement execution. the reason is that the statement with the longest total time is the slow query statement that needs to be optimized, and the optimization of such statements often improves the efficiency.

In the top 5 statements, I found the following SQL statement:

Count:
48 time = 206.79 S (9926 s) Lock = 0.00 S (0 s) rows = 1.0 (48 ),
Work [work] @ [10.81.6.106]
Select max (img_id) as max_img_id from t_mis_pic
Where n and img_id % N ='s'
(It is executed 48 times a day, with an average of 206 s each time. It is definitely slow and needs to be optimized)

Based on the preceding SQL statement model, find one of the actual SQL statements in slow. log:

Select max (img_id) as max_img_id from t_mis_pic where 1 and
Img_id % 2 = '0 ';

Now let's see if we can optimize it.

Locate and optimize Problems

The tool for analyzing slow query statements is explained first. If it cannot be solved, use profile.
Come
Show the table structure:

Show create table t_mis_pic/g
Table: t_mis_pic
Create
Table: Create Table 't_ mis_pic '(
'Img _ id' bigint (20) unsigned not
Null,
'Add _ time' int (10) unsigned not null,
'Is _ del 'tinyint (3) unsigned
Not null default '0 ',
Primary Key ('img _ id '),
Key 'add _ time'
('Add _ time', 'is _ del ')
) Engine = InnoDB default charset = GBK
1 row in Set
(0.00 Sec)

We can see that the img_id column has a primary key.

Explain again:
Explain select
Max (img_id) as max_img_id from t_mis_pic where 1 and img_id % 2 = '0'
/G
* *************************** 1. row ***************************
ID:
1
Select_type: simple
Table: t_mis_pic
Type: index ---- scan the entire index

Possible_keys: NULL
Key: add_time
---- The index is used, but not the primary key index.
The add_time index itself contains the primary key column, which is smaller than the table data, so MySQL selects it

Key_len: 5
Ref:
Null ---- filter and quick search cannot be achieved based on conditions

Rows:
89964741 ----- about 0.1 billion of data, full index scan, resulting in slow Query

Extra:
Using where; Using index --- use the index and where Condition Statement

The above analysis is clear, MySQL cannot effectively use the primary key index, and because the data volume is too large, it causes slow query statements. Then let's look at the table status:
Show
Table status like 't_ mis_pic '/g
...
Rows: 85532594
Avg_row_length:
55
Data_length: 4771020800
Max_data_length: 0
Index_length:
2581594112
...
From the above statistics, we can see that the index volume accounts for more than half of the data, which is huge. Therefore, MySQL
It takes a long time to scan the index. Now that we know the problem, how should we optimize it?

From select max (img_id)
Max_img_id from t_mis_pic where 1 and
Img_id % 2 = '0 ';
In this case, we want to find the largest and even img_id. then we can use the primary key index on img_id to directly find the maximum value of img_id, and then find the value smaller than or equal to the maximum value and an even number.
The img_id value is the result we want.

First explain:

Explain select max (img_id)
Max_img_id from t_mis_pic/G:
...
Rows: NULL
Extra: Select tables
Optimized away
...
The result shows that MySQL can obtain the maximum img_id value without querying the table.
Select
Max (img_id) as max_img_id from t_mis_pic where 1 and img_id % 2 = '0'
This statement:
Select img_id as max_img_id
From t_mis_pic
Where
Img_id <= (select max (img_id) from t_mis_pic) ----- The wehre condition has become the where img_id <= constant

And img_id % 2 = 0 ---- limit img_id to an even number

Order by img_id DESC ---- use the index on img_id to search for img_id with an even number starting from the maximum img_id.

Limit 1
--- Return the maximum img_id that meets the condition

Based on the above analysis, Let's explain again to see if
No meets our expectations:

Explain select img_id as max_img_id from t_mis_pic where
Img_id <= (select max (img_id) from t_mis_pic) and img_id % 2 = 0 order by img_id
Desc limit 1/g

* *************************** 1. Row
***************************
ID: 1
Select_type: Primary
Table:
T_mis_pic
Type: range ---- MySQL still performs index scanning, but here is
Range scan. You do not need to scan the entire index.

Possible_keys: Primary
Key:
Primary
Key_len: 8
Ref: NULL
Rows: 23548910 ----- the SQL statement itself only needs to return a row and does not need to check 23548910
So many rows, the numbers here can be ignored. In fact, MySQL only needs to scan up to two rows.

Extra: Using where; Using Index
----- MySQL scans indexes instead of tables.

* *************************** 2.
Row ***************************
...
Rows: NULL
Extra: Select tables
Optimized away ---- optimized cannot be optimized anymore

Execute this statement on the slave machine, and the slow query statement can be completely eliminated.

Let's look back at the daily statistics of slow. log,
Select max (img_id) as max_img_id from t_mis_pic where N and img_id % N = 'S'
The statement is executed 48 times a day, and the average execution time is about S. After optimization, the execution time is negligible, and the effect is very obvious.

Other methods

Since all queries use the img_id % 2 condition, we can add a column to the table. The stored value is the result after img_id % 2. then create an index for this column. however, the speed is not as high as the SQL statement after rewriting. The reason is that img_id % 2 is not an odd or even number, and the discrimination is too small, the effect of indexing such columns is not obvious. in fact, in other commercial databases, such as Oracle, its function index is based on this principle. for this example, the bitmap index of Oracle is faster.