MySQL子查詢慢現象的解決
當你在用explain工具查看sql語句的執行計畫時,若select_type 欄位中出現“DEPENDENT SUBQUERY”時,你要注意了,你已經掉入了mysql子查詢慢的“坑"。
相關書籍:高效能MySQL(第3版) 中文PDF帶目錄清晰版
下面我們來看一個具體的例子
有這樣一條查詢語句:
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 表很大的話,那麼效能上將會出現問題。
--------------------------------------分割線 --------------------------------------
Ubuntu 14.04下安裝MySQL
《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF
Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主從伺服器
Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集
Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二進位安裝
--------------------------------------分割線 --------------------------------------
本文永久更新連結地址: