mysql 語句最佳化一列

標籤：mysql 最佳化   sql 最佳化   

最佳化前語句：

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 when 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

最佳化後語句：

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 when 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 200) a order by rank,self,convert(manname using gbk) limit 10;

最佳化方法：

1、去子查詢最佳化為join查詢

2、子查詢帶limit 1的錶鏈接鍵都為主鍵，所以不再需要limit 1

3、app_rank_ka(t)表，對別名為rank的欄位和date欄位建聯合索引最佳化內層排序idx_date_rank

4、在不對sql業務進行判斷及變更的情況下採用折中的辦法，先根據rank limit 200條或者覺得合適的條數只要不是非常多，再進行業務排序

5、嵌套連結索引都很合理，該點無最佳化空間

最佳化原理：

1、該sql慢的主要原因在於order by 多個欄位

2、mysql的order by只是利用sort_buffer做第一個欄位的排序，後面的欄位會在暫存資料表中進行，並且計算量很大，根據optimizer_trace跟蹤本來只有4萬多條資料在排序三個欄位之後掃描了14萬次以上才完成

3、mysql的order by最佳化演算法有兩個，一種sort_buffer存的資料為(sort_key1,sort_key2,sort_key3,row_id)排序完成還需回表查詢資料,另一種為(sort_key1,sort_key2,sort_key3,key1 value,key2 value,key3 value....)包含所有需求欄位資料，在資料不超過閾值(max_length_for_sort_data)時預設都會採用第二種最佳化方式

3、上面sql採用的方式就利用了索引先最佳化從表裡面取資料時的排序，限制了200條作為外層的排序資料，不過這也建立了一個暫存資料表，鑒於資料長度不是很大，在資料量不多時效率影響有限，在外層利用三個欄位進行排序不管是使用那一種sort演算法尋找資料都是很高效的

4、關聯子查詢在mysql查詢最佳化工具中處理都不是很如意，最佳化為join不僅避免最佳化器選擇的問題也可以降低最佳化器的消耗時間

最佳化前後執行時間對比：

最佳化前：

|        1 | 8.94656525 |

|        2 | 8.77086475 |

最佳化後：

|        3 | 0.00527075 |

|        4 | 0.00513025 |

該sql有5種order by定序的查詢，最佳化方式相同！！！！！，分別建date和這5個欄位建立聯合索引，可以刪除現有的date單列所以，因為date欄位為時間列不會做更新操作，即使該5個欄位經常更新頁之間資料移動也不會發生頁分裂的情況，又因為mysql提供change_buffer為輔助索引提供記憶體緩衝，所以建立（5-1）個索引的維護成本完全可以接受

PS:如果業務上能修改排序條件，可以只使用子查詢就行，效率更高，通常盡量只使用一個欄位排序，在資料量大時多欄位的排序耗時成倍增長

本文出自 “D調de默默” 部落格，謝絕轉載！

mysql 語句最佳化一列