《眼睜睜地踩到 MySQL in 子查詢的“坑”》要點：
本文介紹了眼睜睜地踩到 MySQL in 子查詢的“坑”，希望對您有用。如果有疑問，可以聯系我們。

前言

MySQL是項目中常用的數據庫,其中in查詢也是很常用.最近項目調試過程中,遇到一個出乎意料的select查詢,竟然用了33秒!

一、表結構

1. userinfo 表

2. article 表

二、問題SQL實例

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一線技術雜談

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/4270.html