程式狂想筆記

一個攻城師奮鬥史

0%

在MySQL5.0查詢大量資料使用IN子查詢好慢(優化方法)

發表於 更新於 分類於 Disqus：

最近使用有一個程式SQL會跑3秒
程式共花了1x秒鐘
想把他cost 時間降下來
但IN方法不知道為什麼那麼慢
就簡單研究有沒有更好的寫法

1
2
3
4
5
6
7
8
9
10
SELECT  ptm.tablename
FROM `secord_big_table` ptm
WHERE ptm.playlistID  IN
    (SELECT stm.playlistID
     FROM `first_big_table` stm 
     WHERE stm.sid IN
         (SELECT sid
          FROM `small_table`
          WHERE as_id = 1
            AND item_no = 1))

執行explain看一下狀態…
first_big_table是掃全表的樣子

暫時用LIMIT和=解決問題

1
2
3
4
5
6
7
8
9
10
11
12
SELECT  ptm.tablename
FROM `secord_big_table` ptm
WHERE ptm.playlistID  =
   (SELECT stm.playlistID
     FROM `first_big_table` stm 
     WHERE stm.sid IN
         (SELECT sid
          FROM `small_table`
          WHERE as_id = 1
            AND item_no = 4)
         LIMIT 0,1
         )

事後發現
這不是我之前寫的非相關子查詢嗎
SQL子查詢筆記(相關子查詢/非相關子查詢) « 進擊的程式新手
照到理沒理由這麼慢阿…
好啦，這時候突然回想起來Backend 台灣 (Backend Tw)裡面的T大常常批評MySQL
在絕望下尋找新方法

exists優化IN語句

第一種格式是使用IN操作符：
… where column in(select * from … where …);
第二種格式是使用EXIST操作符：
… where exists (select ‘X’ from …where …);
我相信絕大多數人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠比第一種格式的效率高。在Oracle中可以幾乎將所有的IN操作符子查詢改寫為使用EXISTS的子查詢。
第二種格式中，子查詢以‘select ‘X’開始。運用EXISTS子句不管子查詢從表中抽取什麼資料它只查看where子句。這樣優化器就不必遍曆整個表而僅根據索引就可完成工作（這裏假定在where語句中使用的列存在索引）。相對於IN子句來說，EXISTS使用相連子查詢，構造起來要比IN子查詢困難一些。
通過使用EXIST，Oracle系統會首先檢查主查詢，然後運行子查詢直到它找到第一個匹配項，這就節省了時間。Oracle系統在執行IN子查詢時，首先執行子查詢，並將獲得的結果列表存放在在一個加了索引的臨時表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在臨時表中以後再執行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因
[效能]SQL語句性能調整原則 @ Programmer Developer Notebook :: 隨意窩 Xuite日誌

實戰MySQL5.0上面

有在IN和EXISTS的差異 @ 芊芊的窩 :: 痞客邦 ::改成exists語句

而假如外部的表格A是相對的儲存大量資料，則採用第一個方法IN的效率將會比較好，假如你使用EXISTS，則除了會對A這個大表格進行全面掃瞄外，還會一筆一筆讀取所有A的資料列，效能自然較差。
簡單的一句話，外大內小=IN，外小內大=EXISTS，這是一個實用的概略評估方法，在大部分的情況下是適用的。

我覺得優化原因是slides/SQL_report_v2.pdf at master · TritonHo/slides的Query Optimizer淺談

不過我修改

1
2
3
4
5
6
7
8
SELECT stm.playlistID
     FROM `first_big_table` stm 
     WHERE exists
         (SELECT 'x'
          FROM `small_table`
          WHERE as_id = 1
            AND item_no = 1
            AND stm.sid IN sid)

還是沒用…

最後還是想到Backend 台灣 (Backend Tw)裡面的T大常常批評MySQL

子查詢語句注意問題

謝晒的PHP網頁設計: [MYSQL] 多層式子查詢 效能注意事項

用程式組IN字串就PK

今天實驗結果，在MYSQL5.0使用子查詢IN改寫exists速度還是很慢
然後發現跟我之前寫的非相關子查詢速度有不一樣
但是改成IN(‘xxxx’,’oooo’,’fffff’)
速度就比較快
所以目前先用程式拆兩段

1
2
3
4
5
6
7
<?
        $array_in = array();
        foreach ($in_sid as  $value) {
            array_push($array_in,$value['sid']);
        }

 $sql = "select xxx from  ooo where oo IN (" .implode( "," , $array_in).")";

柳暗花明又一村

這樣跑子查詢竟然吃到key了…..
但不知道這樣跑好不好

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT b.sid,
  (SELECT ptm.tablename
   FROM `social`.`secord_big_table` ptm
   WHERE ptm.playlistID = b.a)
FROM
  (SELECT g.sid,

     (SELECT stm.playlistID
      FROM `social`.`first_big_table` stm
      WHERE stm.sid = g.sid) AS a
   FROM `pay`.`activity_guess_item` g
   WHERE as_id = 1
     AND item_no = 4) AS b

雖然程式只需要抓到一筆
但上面方法可以抓到多筆
效能能吃到key

算是另類解法

INNER JOIN解決方法

最後網路爬到用INNER JOIN 優化
[慢查优化]慎用MySQL子查询，尤其是看到DEPENDENT SUBQUERY标记时 - 旁观者 - 博客园
MySQL性能优化小记：MySQL子查询很慢的问题 - Bash @ Linux - ITeye博客

`sql
SELECT stm.tablename
FROM
(SELECT ptm.playlistID
FROM secord_big_table ptm
INNER JOIN smaill_table g ON ptm.sid = g.sid
WHERE as_id = 1
AND item_no = 4 ) p
INNER JOIN
first_big_table stm
ON stm.playlistID = p.playlistID
WHERE stm.playlistID = p.playlistID

用這種解法，速度有變比較快
但這種寫法真的不時直覺…

結論

網路上查，好像MySQL到5.7和8.0效能上有很大的改進
希望我有機會能使用到…

參考來源: