MySQL Statement optimization one column

Pre-Optimization statements:

SELECT

Ifnull (S.mileage,-1) as Mile,

S.fuel_hkm as Val,

T.fhkm_rank as Rank,

Ifnull ((select U.photo from App_user u where u.user_id = t.user_id limit 1), ') as Path,

case if s.car_id = ' *********** ' then 0

Else 1 end as self,

Ifnull ((

SELECT

Case

When Ifnull (U.nickname, ')! = ' then U.nickname

When Ifnull (U.username, ')! = ' then U.username

When Ifnull (U.customer_number, ')! = ' then U.customer_number

else U.mobile END as uname

From

App_user u

WHERE

u.user_id = t.user_id

LIMIT 1

) as Manname,

Ifnull ((Select Case when Ifnull (C.nickname, ")! =" then C.nickname else REPLACE (C.lisence,substr (c.lisence,3,3), ' * * * ' End from App_car c where c.car_id = t.car_id limit 1), ') as Carname

From

App_car_sum_7daily S,

App_rank_ka T

WHERE

t.date = ' 2016-07-12 '

and t.date = S.date

and t.car_id = s.car_id

and s.fuel_hkm > 0

and T.fhkm_rank > 0

ORDER by Rank,self,convert (manname using GBK) limit 10

Post-Optimization statements:

SELECT * FROM (

Select

Ifnull (S.mileage,-1) as Mile,

S.fuel_hkm as Val,

T.fhkm_rank as Rank,

Ifnull (U.photo, ") as Path,

case if s.car_id = ' *********** ' then 0

Else 1 end as self,

Case

When Ifnull (U.nickname, ')! = ' then U.nickname

When Ifnull (U.username, ')! = ' then U.username

When Ifnull (U.customer_number, ')! = ' then U.customer_number

else Ifnull (U.mobile, ') End as Manname,

Case

When Ifnull (C.nickname, ')! = ' then C.nickname

Else Ifnull (REPLACE (C.lisence,substr (c.lisence,3,3), ' * * * '), ') End as Carname

From

App_rank_ka T

Join App_car_sum_7daily s on (s.date = t.date and s.car_id = t.car_id and s.fuel_hkm > 0)

Join App_user u on (u.user_id = t.user_id)

Join App_car C on (c.car_id = t.car_id)

where t.date = ' 2016-07-12 ' and T.fhkm_rank > 0

Order BY rank limit: A order by Rank,self,convert (Manname using GBK) limit 10;

Optimization method:

1, go to sub-query optimization for join query

2, sub-query with limit 1 of the table link keys are the primary key, so no longer need limit 1

3, App_rank_ka (t) table, the rank-alias field and the date word Jianjian to optimize the inner-level ordering Idx_date_rank

4, in the case of the SQL business to judge and change the use of a compromise approach, first according to rank limit 200 or think that the right number of bars is not very much, then the business sort

5, nested link index is very reasonable, the point no optimization space

Optimization principle:

1. The main reason for this slow SQL is the order by multiple fields

2, MySQL order by just use Sort_buffer to do the first field of the sort, the following fields will be in the temporary table, and the calculation is very large, according to Optimizer_ Trace Trace would have been more than 40,000 data were scanned more than 140,000 times after sorting three fields to complete

3, MySQL's Order by optimization algorithm has two, a kind of sort_buffer data for (sort_key1,sort_key2,sort_key3,row_id) sorting completed also need to return table query data, the other is (Sort_key1, Sort_key2,sort_key3,key1 value,key2 Value,key3 value ....) Contains all requirement field data, with the second optimization by default when the data does not exceed the threshold (Max_length_for_sort_data)

3, the above SQL used in the way of using the index to optimize the data from the table when the sorting, limit 200 as the outer layer of the sorting data, but this also established a temporary table, in view of the length of the data is not very large, in the data volume of the impact of a limited amount of efficiency, Sort by three fields in the outer layer it is very efficient to use the sort algorithm to find data.

4, the correlation subquery in the MySQL query optimizer processing is not very good, optimization for join not only avoids the problem of optimizer selection can also reduce the optimizer's consumption time

Optimize before and after execution time comparison:

Before optimization:

| 1 | 8.94656525 |

| 2 | 8.77086475 |

After optimization:

| 3 | 0.00527075 |

| 4 | 0.00513025 |

The SQL has 5 order by collation queries that are optimized in the same way!!!!! , build a date and create a federated index of these 5 fields, and you can delete the existing date column, so because the date field does not do an update operation for the time columns, even if the 5 fields frequently update the data movement between pages, there is no page splitting, and because MySQL provides change_ Buffer provides a memory buffer for the secondary index, so the maintenance cost of a new (5-1) index is fully acceptable

PS: If you can modify the sorting conditions on the business, you can use only subqueries, more efficient, usually try to use only one field to sort, when the volume of data is large, multi-field sorting time is multiplied

This article from "D Tune de silently" blog, declined reprint!

MySQL statement tuning a column