記踩到 MySQL in 子查詢的“坑”

前言

MySQL是專案中常用的資料庫，其中in查詢也是很常用。最近專案調試過程中，遇到一個出乎意料的select查詢，竟然花了33秒！

一、表格結構

#1. userinfo 表格

#2. article 表

#

select*fromuserinfowhereidin(selectauthor_idfromartilcewheretype=1);

大家第一眼看到上面的SQL時，可能都會覺得這是一個很簡單的子查詢。先把author_id查出來，再用in查詢一下。

如果有相關索引會非常快的，拆解來講就是以下這樣的：

1.selectauthor_idfromartilcewheretype=1;　　2.select*fromuserinfowhereidin(1,2,3);

但是事實是這樣的：

mysql> select count(*) from userinfo;

#

mysql> select count(*) from article;

#

mysql> select id,username from userinfo where id in (select author_id from article where type = 1);

#33 秒！ 為什麼會這麼慢呢？

三、問題原因

#官方文件解釋：in 子句在查詢的時候有時會被轉換為 exists 的方式來執行，變成逐條記錄進行遍歷（版本 5.5 中存在，5.6 中已做優化）。

#參考：

https://dev.mysql.com/doc/refman/5.5/en/subquery-optimization.html

四、解決方式（版本5.5）

1. 使用暫存表

#

select id,username from userinfo

where id in (select author_id from

(select author_id from article where type = 1) as tb);

#2. 使用 join

select a.id,a.username from userinfo a, article b

where a.id = b.author_id and b.type = 1;

#五、補充

版本 5.6 已針對子查詢做了最佳化，方式跟【四】中的臨時表方式一樣，參考官方文件：

If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery.

For example, the following IN subquery is noncorrelated  ( where_condition involves only columns from t2 and not t1 ):

select * from t1

where t1.a in (select t2.b from t2 where where_condition);

The optimizer might rewrite this as an EXISTS correlated subquery:

select * from t1

where exists (select t2.b from t2 where where_condition and t1.a=t2.b);

Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query##.

https://dev.mysql.com/doc/refman/5.6/en/subquery-materialization.html

文章來自微信公眾號：HULK一線技術雜談

#

以上是記踩到 MySQL in 子查詢的“坑”的詳細內容。更多資訊請關注PHP中文網其他相關文章！