《眼睜睜地踩到 MySQL in 子查詢的“坑”》要點(diǎn)：
本文介紹了眼睜睜地踩到 MySQL in 子查詢的“坑”，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

前言

MySQL是項(xiàng)目中常用的數(shù)據(jù)庫(kù),其中in查詢也是很常用.最近項(xiàng)目調(diào)試過(guò)程中,遇到一個(gè)出乎意料的select查詢,竟然用了33秒!

一、表結(jié)構(gòu)

1. userinfo 表

2. article 表

二、問(wèn)題SQL實(shí)例

select*fromuserinfowhereidin(selectauthor_idfromartilcewheretype=1);

大家第一眼看到上面的SQL時(shí),可能都會(huì)覺(jué)得這是一個(gè)很簡(jiǎn)單的子查詢.先把a(bǔ)uthor_id查出來(lái),再用in查詢一下.

如果有相關(guān)索引會(huì)非常快的,拆解來(lái)講就是以下這樣的：

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

但是事實(shí)是這樣的：

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 秒!為什么會(huì)這么慢呢?

三、問(wèn)題原因

官方文檔解釋：in 子句在查詢的時(shí)候有時(shí)會(huì)被轉(zhuǎn)換為 exists 的方式來(lái)執(zhí)行,變成逐條記錄進(jìn)行遍歷(版本 5.5 中存在,5.6 中已做優(yōu)化).

參考：

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

四、解決方式(版本5.5)

1. 使用臨時(shí)表

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;

五、補(bǔ)充

版本 5.6 已針對(duì)子查詢做了優(yōu)化,方式跟【四】中的臨時(shí)表方式一樣,參考官方文檔：

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

文章來(lái)自微信公眾號(hào)：HULK一線技術(shù)雜談

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/4270.html