結(jié)合工作中的內(nèi)容和大家分享一次Left Jon優(yōu)化的過程，希望能給同學(xué)們新的思路。

【功能背景】

??? 我們需要按照用戶訂單號(hào)和商戶號(hào)統(tǒng)計(jì)出購買的商品數(shù)量和售后的商品數(shù)量。涉及到的表和關(guān)系見下圖：

很不幸工程師在起初進(jìn)行表結(jié)構(gòu)設(shè)計(jì)的時(shí)候沒有在商戶訂單表中記錄下購買的商品總數(shù)，在商戶訂單的售后單中也沒記錄下售后的商品數(shù)量。

【原始的SQL】

select 
  o.no,s_order.no,sum(s_item.count),sum(after_sale_item.count)
  from 
  buyer_order o 
  left join seller_order s_order on o.id = s_order.buyer_order_id 
  left join seller_order_item s_item on s_order.id = s_item.seller_order_id
  left join seller_order_after_sale after_sale on s_order.id = after_sale.seller_order_id
  left join seller_order_after_sale_item after_sale_item on after_sale.id = after_sale_item.after_sale_id
where o.add_time >='2019-05-01'
group by
  o.id,s_order.id
order by 
  o.id
limit 0,10

以上SQL幾個(gè)關(guān)鍵字段都使用了索引。??

【原始的SQL分析】

這是一條很常規(guī)的SQL，邏輯上也沒什么毛病

這條SQL中有較多的連接查詢，如果隨著售后單的增加，連接的數(shù)據(jù)就會(huì)更多

將符合條件的數(shù)據(jù)都加載到內(nèi)存后按照 order.id,s_order.id 進(jìn)行分組統(tǒng)計(jì)，如果有100W的數(shù)據(jù)會(huì)怎樣？如果你用代碼去實(shí)現(xiàn)這么一段統(tǒng)計(jì)你會(huì)怎么做？

將統(tǒng)計(jì)完的數(shù)據(jù)再按照 order.id 進(jìn)行排序，取出前10條數(shù)據(jù)。

從以上的SQL發(fā)現(xiàn)需要將符合條件的所有的數(shù)據(jù)加載到內(nèi)存后要進(jìn)行分組，統(tǒng)計(jì)，排序，最后再進(jìn)行分頁。我們能不能減少數(shù)據(jù)的加載數(shù)量呢？能不能減少數(shù)據(jù)庫CPU的使用量，能不能先取少量的數(shù)據(jù)再統(tǒng)計(jì)呢？

基于以上的問題，我們進(jìn)行了優(yōu)化

【分析步驟】

作為旁觀者一開始不了解我們功能需要輸出什么樣的數(shù)據(jù)，所以我們一開始要了解每張表存儲(chǔ)的是什么樣的數(shù)據(jù)，彼此之間的關(guān)系是什么。

我們忘記原來的SQL是什么樣的，按照我們需要的數(shù)據(jù)，再次重新的思考，不要再陷入原來的SQL的漩渦中。

針對(duì)上面提出的問題，如何減少數(shù)據(jù)的加載？能不能先分頁數(shù)據(jù)，再對(duì)分頁的數(shù)據(jù)進(jìn)行單獨(dú)的統(tǒng)計(jì)呢？

那么我們是不是需要對(duì)group by進(jìn)行優(yōu)化，我們要想辦法先分頁

大家是否想到了一些方法？

【優(yōu)化后的SQL】

select 
  o.id,o.no,s_order.no,
  (select sum(sot.count) from seller_order so 
    left join seller_order_item sot on so.id = sot.seller_order_id 
        where so.id =s_order.id ),
  (select sum(osat.count) from seller_order_after_sale osa 
    left join seller_order_after_sale_item osat on osa.id = osat.after_sale_id
        where osa.seller_order_id = s_order.id )
  from 
  buyer_order o 
  left join seller_order s_order on o.id = s_order.buyer_order_id 
where o.addTime >='2019-05-01'
order by 
  o.id
limit 0,10

【優(yōu)化的SQL分析】

1. 很直觀的發(fā)現(xiàn)，我們把group by去掉了，因?yàn)榘凑?order.id,s_order.id 分組，實(shí)際只對(duì) buyer_order和seller_order表進(jìn)行連接，邏輯上是一樣的進(jìn)行了分組。
2. group by不使用的話我們就減少了CPU對(duì)數(shù)據(jù)分組的處理，而且我們只連接主要的表數(shù)據(jù)，減少了加載到內(nèi)存中的數(shù)據(jù)。
3. 以上的操作就完成了我們之前說的先對(duì)數(shù)據(jù)分頁。我們?nèi)〕隽?0條數(shù)據(jù)。
4. 接著我們?cè)賹?duì)10條數(shù)據(jù)的銷售出去的商品數(shù)量和售后的數(shù)量進(jìn)行統(tǒng)計(jì)
5. 這時(shí)候大家發(fā)現(xiàn)，我們其實(shí)只對(duì)分頁出來的10條數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，原來是將所有的數(shù)據(jù)分組統(tǒng)計(jì)后取10條?？梢园l(fā)現(xiàn)我們這樣操作大大減少了對(duì)數(shù)據(jù)的統(tǒng)計(jì)處理。我們只需要統(tǒng)計(jì)我們需要的數(shù)據(jù)。

以上優(yōu)化的效果可能遠(yuǎn)遠(yuǎn)超出大家的想象。

實(shí)際工作中連表的數(shù)比我們例子中的要多，未優(yōu)化的SQL在執(zhí)行未分頁的時(shí)候發(fā)現(xiàn)一共有70萬的數(shù)據(jù)，我們分頁取出10條數(shù)據(jù)花了10+秒以上的時(shí)間，數(shù)據(jù)量不大但是大部分的時(shí)間都消耗在了分組和數(shù)據(jù)統(tǒng)計(jì)，大家可以試著寫一段代碼對(duì)這些數(shù)據(jù)進(jìn)行分組和統(tǒng)計(jì)，就能明白其中的復(fù)雜性。

而實(shí)際上無論取出10條和全部取出，時(shí)間基本上一樣的（不考慮IO），因?yàn)橄冗M(jìn)行了統(tǒng)計(jì)。

優(yōu)化后的SQL，加載到內(nèi)存中只有2萬左右的數(shù)據(jù)，而且不進(jìn)行統(tǒng)計(jì)，先取出10條數(shù)據(jù)，然后再對(duì)10條數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，邏輯上比之前的簡單多了。優(yōu)化后的SQL執(zhí)行時(shí)間在20毫秒以內(nèi)。

其實(shí)如果在訂單表和售后表都記錄了對(duì)應(yīng)的數(shù)量，連表數(shù)還要少，還不需要進(jìn)行子查詢。有時(shí)候設(shè)計(jì)表的時(shí)候還是需要考慮一下統(tǒng)計(jì)的需要。

到此這篇關(guān)于MYSQL Left Join優(yōu)化(10秒優(yōu)化到20毫秒內(nèi))的文章就介紹到這了,更多相關(guān)MYSQL Left Join優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論