MYSQL Left Join優化(10秒優化到20毫秒內)

Posted on by

結合工作中的內容和大傢分享一次Left Jon優化的過程，希望能給同學們新的思路。

【功能背景】

    我們需要按照用戶訂單號和商戶號統計出購買的商品數量和售後的商品數量。涉及到的表和關系見下圖：

很不幸工程師在起初進行表結構設計的時候沒有在商戶訂單表中記錄下購買的商品總數，在商戶訂單的售後單中也沒記錄下售後的商品數量。

【原始的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幾個關鍵字段都使用瞭索引。  

【原始的SQL分析】

這是一條很常規的SQL，邏輯上也沒什麼毛病

這條SQL中有較多的連接查詢，如果隨著售後單的增加，連接的數據就會更多

將符合條件的數據都加載到內存後按照 order.id,s_order.id 進行分組統計，如果有100W的數據會怎樣？如果你用代碼去實現這麼一段統計你會怎麼做？

將統計完的數據再按照 order.id 進行排序，取出前10條數據。

從以上的SQL發現需要將符合條件的所有的數據加載到內存後要進行分組，統計，排序，最後再進行分頁。我們能不能減少數據的加載數量呢？能不能減少數據庫CPU的使用量，能不能先取少量的數據再統計呢？

基於以上的問題，我們進行瞭優化

【分析步驟】

作為旁觀者一開始不瞭解我們功能需要輸出什麼樣的數據，所以我們一開始要瞭解每張表存儲的是什麼樣的數據，彼此之間的關系是什麼。

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

針對上面提出的問題，如何減少數據的加載？能不能先分頁數據，再對分頁的數據進行單獨的統計呢？

那麼我們是不是需要對group by進行優化，我們要想辦法先分頁

大傢是否想到瞭一些方法？

【優化後的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

【優化的SQL分析】

  1. 很直觀的發現，我們把group by去掉瞭，因為按照 order.id,s_order.id 分組，實際隻對 buyer_order和seller_order表進行連接，邏輯上是一樣的進行瞭分組。
  2. group by不使用的話我們就減少瞭CPU對數據分組的處理，而且我們隻連接主要的表數據，減少瞭加載到內存中的數據。
  3. 以上的操作就完成瞭我們之前說的先對數據分頁。我們取出瞭10條數據。
  4. 接著我們再對10條數據的銷售出去的商品數量和售後的數量進行統計
  5. 這時候大傢發現，我們其實隻對分頁出來的10條數據進行統計，原來是將所有的數據分組統計後取10條。可以發現我們這樣操作大大減少瞭對數據的統計處理。我們隻需要統計我們需要的數據。

以上優化的效果可能遠遠超出大傢的想象。

實際工作中連表的數比我們例子中的要多，未優化的SQL在執行未分頁的時候發現一共有70萬的數據，我們分頁取出10條數據花瞭10+秒以上的時間，數據量不大但是大部分的時間都消耗在瞭分組和數據統計，大傢可以試著寫一段代碼對這些數據進行分組和統計，就能明白其中的復雜性。

而實際上無論取出10條和全部取出，時間基本上一樣的（不考慮IO），因為先進行瞭統計。

優化後的SQL，加載到內存中隻有2萬左右的數據，而且不進行統計，先取出10條數據，然後再對10條數據進行統計，邏輯上比之前的簡單多瞭。優化後的SQL執行時間在20毫秒以內。

其實如果在訂單表和售後表都記錄瞭對應的數量，連表數還要少，還不需要進行子查詢。有時候設計表的時候還是需要考慮一下統計的需要。

到此這篇關於MYSQL Left Join優化(10秒優化到20毫秒內)的文章就介紹到這瞭,更多相關MYSQL Left Join優化內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：