mysql子查詢慢的問題

mysql子查詢慢的問題

當你在用explain工具查看sql語句的執行計劃時，若select_type 字段中出現“DEPENDENT SUBQUERY”時，你要注意了，你已經掉入了mysql子查詢慢的“坑"。。。下面我們來看一個具體的例子
有這樣一條查詢語句：

SELECT gid,COUNT(id) as count FROM shop_goods g1 WHERE status =0 and gid IN (SELECT gid FROM shop_goods g2 WHERE sid IN (1519066,1466114,1466110,1466102,1466071,1453929))GROUP BY gid;

用explain看了一下，出現關鍵字“DEPENDENT SUBQUERY”，意味著子查詢的第一個select依賴外部的查詢;

SUBQUERY：子查詢中的第一個SELECT；DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決於外面的查詢 。

換句話說，就是 子查詢對 g2 的查詢方式依賴於外層 g1 的查詢。它意味著兩步：

第一步，MySQL 根據 select gid,count(id) from shop_goods where status=0 group by gid; 得到一個大結果集 t1，其數據量為rows=850672 了；

第二步，上面的大結果集 t1 中的每一條記錄，都將與子查詢 SQL 組成新的查詢語句：select gid from shop_goods where sid in (15...blabla..29) and gid=%t1.gid%。等於說，子查詢要執行85萬次……即使這兩步查詢都用到了索引，但不慢才怪；

如此一來，子查詢的執行效率居然受制於外層查詢的記錄數，那還不如拆成兩個獨立查詢順序執行呢。

對於此類語句一般的優化策略是拆成兩個查詢語句，你不想拆成兩個獨立查詢的話，也可以與臨時表join查詢，：
你不想拆成兩個獨立查詢的話，也可以與臨時表聯表查詢，如下所示優化後的sql：

SELECT g1.gid,count(1) FROM shop_goods g1,(select gid from shop_goods WHERE sid in (1519066,1466114,1466110,1466102,1466071,1453929)) g2 where g1.status=0 and g1.gid=g2.gid GROUP BY g1.gid;

用explain看了一下，這次又有了一個新的關鍵字"DERIVED"，意思是用於 from 子句裡有子查詢的情況。MySQL 會遞歸執行這些子查詢，把結果放在臨時表裡，然後再做join操作；

DERIVED 的官方含義為：用於 from 子句裡有子查詢的情況。MySQL 會遞歸執行這些子查詢，把結果放在臨時表裡。

《高性能MySQL》的第4.4節“MySQL查詢優化器的限制（Limitations of the MySQL Query Optimizer）”之第4.4.1小節“關聯子查詢（Correlated Subqueries）”也有類似的論述：mysql 在處理子查詢時，會改寫子查詢。通常情況下，我們希望由內到外，先完成子查詢的結果，然後再用子查詢來驅動外查詢的表，完成查詢。

例如：select * from test where tid in(select fk_tid from sub_test where gid=10)；通常我們會感性地認為該 sql 的執行順序是：sub_test 表中根據 gid 取得 fk_tid(2,3,4,5,6)記錄，然後再到 test 中，帶入 tid=2,3,4,5,6，取得查詢數據。

但是實際mysql的處理方式為：

select * from test where exists (select * from sub_test where gid=10 and sub_test.fk_tid=test.tid);

mysql 將會掃描 test 中所有數據，每條數據都將會傳到子查詢中與 sub_test 關聯，子查詢不會先被執行，所以如果 test 表很大的話，那麼性能上將會出現問題。