最近我在公司優(yōu)化了一些慢查詢SQL，積累了一些SQL調(diào)優(yōu)的實(shí)戰(zhàn)經(jīng)驗(yàn)。這篇文章從實(shí)戰(zhàn)的角度出發(fā)，給大家分享一下如何做SQL調(diào)優(yōu)。

經(jīng)過兩次優(yōu)化之后，慢SQL的性能顯著提升了，耗時(shí)從8s優(yōu)化到了0.7s。

現(xiàn)在拿出來給大家分享一下，希望對(duì)你會(huì)有所幫助。

1 案發(fā)現(xiàn)場(chǎng)

前幾天，我收到了一封報(bào)警郵件，提示有一條慢查詢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的含義是統(tǒng)計(jì)id=123456789的供應(yīng)商，未發(fā)布的spu數(shù)量是多少。

這條SQL的耗時(shí)竟然達(dá)標(biāo)了8s，必須要做優(yōu)化了。

我首先使用explain關(guān)鍵字查詢?cè)揝QL的執(zhí)行計(jì)劃，發(fā)現(xiàn)spu表走了type類型的索引，而sku、mall_sku、supplier_sku表都走了ref類型的索引。

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

不是簡(jiǎn)單的增加索引，就能解決的事情。

那么，接下來該如何優(yōu)化呢？

2 第一次優(yōu)化

這條SQL語句，其中兩個(gè)exists關(guān)鍵字引起了我的注意。

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

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

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

我猜測(cè)可能是為了只返回spu表的數(shù)據(jù)，做的一種處理。如果join了sku表，則可能會(huì)查出重復(fù)的數(shù)據(jù)，需要做去重處理。

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

因此，我做出了第一次優(yōu)化。

使用join + group by組合，將sql優(yōu)化如下：

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的性能其實(shí)是挺快的。

這樣優(yōu)化之后，sql的執(zhí)行時(shí)間變成了2.5s。

性能提升了3倍多，但是還是不夠快，還需要做進(jìn)一步優(yōu)化。

3 第二次優(yōu)化

還有一個(gè)not exists可以優(yōu)化一下。

如果是小表驅(qū)動(dòng)大表的時(shí)候，使用not exists確實(shí)可以提升性能。

但如果是大表驅(qū)動(dòng)小表的時(shí)候，使用not exists可能有點(diǎn)弄巧成拙。

這里exists右邊的sql的含義是查詢某供應(yīng)商的商品數(shù)據(jù)，而目前我們平臺(tái)一個(gè)供應(yīng)商的商品并不多。

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

sql優(yōu)化如下：

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

這樣優(yōu)化之后，該sql的執(zhí)行時(shí)間下降到了0.7s。

之后，我再用explain關(guān)鍵字查詢?cè)揝QL的執(zhí)行計(jì)劃。

發(fā)現(xiàn)spu表走了全表掃描，sku表走了eq_ref類型的索引，而mall_sku和supplier_sku表走了ref類型的索引。

可以看出，有時(shí)候sql語句走了4個(gè)索引，性能未必比走了3個(gè)索引好。

多張表join的時(shí)候，其中一張表走了全表掃描，說不定整個(gè)SQL語句的性能會(huì)更好，我們一定要多測(cè)試。

exists和not exists常用示例說明

1.查詢a表在b表中存在數(shù)據(jù)

相當(dāng)于sql中in操作。

select * from a where exists (select 1 from b where a_id=a.id )

以上sql等價(jià)于下面的sql

select * from a where id in (select a_id from b)

2.查詢a表在b表中不存在數(shù)據(jù)

相當(dāng)于sql中not in操作。

select * from a where not exists (select 1 from b where a_id=a.id )

以上sql等價(jià)于下面的sql

select * from a where id not in (select a_id from b)

3.查詢時(shí)間最新記錄

以下sql查詢同一id內(nèi)的c_date最近的記錄。

SELECT * FROM c t1
   WHERE NOT EXISTS(select * from c where id = t1.id and c_date>t1.c_date)

分析：子查詢中，先看id = 1 的情形，只有當(dāng)t1.c_date 取最大值時(shí)，沒有返回結(jié)果，因?yàn)槭荖OT EXISTS關(guān)鍵字，所以Where條件成立，返回符合條件的查詢結(jié)果

4.exists替代distinct剔除重復(fù)數(shù)據(jù)

例如下面sql

SELECT distinct a.id,a.name from a, b WHERE a.id=b.a_id;

使用exists提出重復(fù)，等價(jià)于上面的sql

select id,name from a where exists (select 1 from b where a_id=a.id );

分析：RDBMS 核心模塊將在子查詢的條件一旦滿足后，立即返回結(jié)果，所以自帶去重

總結(jié)

說實(shí)話，SQL調(diào)優(yōu)是一個(gè)比較復(fù)雜的問題，需要考慮的因素有很多，有可能需要多次優(yōu)化才能滿足要求。

到此這篇關(guān)于explain慢查詢SQL調(diào)優(yōu)exists的實(shí)戰(zhàn)的文章就介紹到這了,更多相關(guān)慢查詢SQL調(diào)優(yōu)exists內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論