一次顯著的效能提升，從8s到0.7s

前言

最近我在公司優化了一些慢查詢SQL，積累了一些SQL調優的實戰經驗。

我之前寫過一些SQL優化相關的文章《聊聊SQL優化的15個小技巧》和《explain | 索引優化的這把絕世好劍，你真的會用嗎？》，在全網廣受好評。

這篇文章從實戰的角度出發，給大家分享一下如何做SQL調優。

經過兩次優化之後，慢SQL的效能顯著提升了，耗時從8s優化到了0.7s。

現在拿出來給大家分享一下，希望對你會有所幫助。

1 案發現場

前幾天，我收到了一封報警郵件，提示有一條慢查詢SQL。

我開啟郵件檢視了詳情，那條SQL大概是這樣的：

SELECT count(*)
FROM spu s1
WHERE EXISTS (
 SELECT *
 FROM sku s2
  INNER JOIN mall_sku s3 ON s3.sku_id = s2.id
 WHERE s2.spu_id = s1.id
  AND s2.status = 1
  AND NOT EXISTS (
   SELECT *
   FROM supplier_sku s4
   WHERE s4.mall_sku_id = s3.id
    AND s4.supplier_id = 123456789
    AND s4.status = 1
  )
)

這條SQL的含義是統計id=123456789的供應商，未釋出的spu數量是多少。

這條SQL的耗時竟然達標了8s，必須要做優化了。

我首先使用explain關鍵字查詢該SQL的執行計劃，發現spu表走了type型別的索引，而sku、mall_sku、supplier_sku表都走了ref型別的索引。

也就是說，這4張表都走了索引。

不是簡單的增加索引，就能解決的事情。

那麼，接下來該如何優化呢？

2 第一次優化

這條SQL語句，其中兩個exists關鍵字引起了我的注意。

一個exists是為了查詢存在某些滿足條件的商品，另一個not exists是為了查詢出不存在某些商品。

這個SQL是另外一位已離職的同事寫的。

不清楚spu表和sku表為什麼不用join，而用了exists。

我猜測可能是為了只返回spu表的資料，做的一種處理。如果join了sku表，則可能會查出重複的資料，需要做去重處理。

從目前看，這種寫效能有瓶頸。

因此，我做出了第一次優化。

使用join + group by組合，將sql優化如下：

SELECT count(*) FROM
(
  select s2.spu_id from spu s1
  inner join from sku s2
  inner join mall_sku s3 on s3.sku_id=s2.id
  where s2.spu_id=s1.id and s2.status=1
  and not exists 
  (
     select * from supplier_sku s4
     where s4.mall_sku_id=s3.id
     and s4.supplier_id=...
  )
  group by s2.spu_id
) a

文章中有些相同的條件省略了，由於spu_id在sku表中是增加了索引的，因此group by的效能其實是挺快的。

這樣優化之後，sql的執行時間變成了2.5s。

效能提升了3倍多，但是還是不夠快，還需要做進一步優化。

3 第二次優化

還有一個not exists可以優化一下。

如果是小表驅動大表的時候，使用not exists確實可以提升效能。

但如果是大表驅動小表的時候，使用not exists可能有點弄巧成拙。

這裡exists右邊的sql的含義是查詢某供應商的商品資料，而目前我們平臺一個供應商的商品並不多。

於是，我將not exists改成了not in。

sql優化如下：

SELECT count(*) FROM
(
  select s2.spu_id from spu s1
  inner join from sku s2
  inner join mall_sku s3 on s3.sku_id=s2.id
  where s2.spu_id=s1.id and s2.status=1
  and s3.id not IN 
  (
     select s4.mall_sku_id 
     from supplier_sku s4
     where s4.mall_sku_id=s3.id
     and s4.supplier_id=...
  )
  group by s2.spu_id
) a

這樣優化之後，該sql的執行時間下降到了0.7s。

之後，我再用explain關鍵字查詢該SQL的執行計劃。

發現spu表走了全表掃描，sku表走了eq_ref型別的索引，而mall_sku和supplier_sku表走了ref型別的索引。

可以看出，有時候sql語句走了4個索引，效能未必比走了3個索引好。

多張表join的時候，其中一張表走了全表掃描，說不定整個SQL語句的效能會更好，我們一定要多測試。

說實話，SQL調優是一個比較複雜的問題，需要考慮的因素有很多，有可能需要多次優化才能滿足要求。