對(duì)于很多的開(kāi)發(fā)小伙伴來(lái)說(shuō)，在MySQL中進(jìn)行in子查詢是一個(gè)非常常見(jiàn)的操作。

雖然也有很多人說(shuō)，盡量少用in子查詢，in的數(shù)量過(guò)多會(huì)影響查詢性能。

但其實(shí)MySQL做了不少的優(yōu)化手段來(lái)保證in子查詢的性能，大家也能在實(shí)際的業(yè)務(wù)中感受到in子查詢的速度也沒(méi)那么慢。

那今天就帶大家了解一下，MySQL到底是怎么來(lái)優(yōu)化in子查詢的。

普通in子查詢

首先，我們看一下MySQL是如何執(zhí)行一個(gè)普通的in子查詢的。

以一個(gè)簡(jiǎn)單的子查詢?yōu)槔?/p>

select  * from table1 where key1 in ('bb','ff','gg');

對(duì)于這個(gè)子查詢畫了一個(gè)簡(jiǎn)單的查詢圖，不同顏色代表不同的數(shù)據(jù)頁(yè)。

在這個(gè)圖里，最上層的是根節(jié)點(diǎn)，中間的是非葉子節(jié)點(diǎn)，最下面的是葉子節(jié)點(diǎn)。

對(duì)于一個(gè)普通的二級(jí)索引來(lái)說(shuō)，葉子節(jié)點(diǎn)存儲(chǔ)的是索引key和主鍵id，這些基礎(chǔ)知識(shí)就不詳細(xì)展開(kāi)說(shuō)了。

需要注意的是，二級(jí)索引在葉子節(jié)點(diǎn)中是按照key的順序從小到大排序的，但是對(duì)應(yīng)的主鍵id可不一定。

可能與大家想象的不同，MySQL在執(zhí)行in子查詢時(shí)，會(huì)把in語(yǔ)句中的條件當(dāng)做一個(gè)個(gè)的區(qū)間，比如：

['bb','bb']，['ff','ff']，['gg','gg']

然后MySQL在二級(jí)索引樹(shù)上，會(huì)先查詢['bb','bb']這個(gè)區(qū)間，比如首先查詢到第一個(gè)數(shù)據(jù)頁(yè)中符合條件的第一條數(shù)據(jù)(bb,2)，獲取到主鍵id=2之后，去聚簇索引回表查詢所需的數(shù)據(jù)（因?yàn)槲覀兪褂玫膕elect *，需要獲取到所有的列值）。

然后查詢第一個(gè)數(shù)據(jù)頁(yè)中符合條件的第二條數(shù)據(jù)(bb,5)，獲取到主鍵id=5之后，去聚簇索引回表查詢所需的數(shù)據(jù)，

然后查詢第二個(gè)數(shù)據(jù)頁(yè)中符合條件的第三條數(shù)據(jù)(ff,6)，

不斷的重復(fù)上面的動(dòng)作。。。。

最后獲取到一個(gè)結(jié)果集，返回到Server，再由Server返回到客戶端。

看到這里大家是否可以感覺(jué)到，這樣查詢數(shù)據(jù)也太麻煩了，特別是當(dāng)in子查詢的條件越來(lái)越多時(shí)，如何保證性能呢？

下面，我們一起來(lái)看一下，MySQL是如何優(yōu)化in子查詢的。

物化表

首先，為了演示我們建兩張表table1和table2，并建立兩個(gè)二級(jí)索引idx_c1和idx_c2。

CREATE TABLE table1  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_c1`(`c1`) USING BTREE
) ENGINE = InnoDB

CREATE TABLE table2  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_c2`(`c2`) USING BTREE
) ENGINE = InnoDB

下面以一個(gè)簡(jiǎn)單的子查詢?yōu)槔涸趖able2表中查詢t2.c2=3的id，并作為table1表c1的查詢條件。

SELECT
  * 
FROM
  table1 t1 
WHERE
  t1.c1 IN ( SELECT id FROM table2 t2 WHERE t2.c2 = 3 );

對(duì)于一個(gè)這樣普通的子查詢來(lái)說(shuō)，MySQL使用了一種叫做物化表的方式來(lái)提升性能。

什么意思呢？

就是將子查詢的結(jié)果集去重后放入到一個(gè)臨時(shí)表中，臨時(shí)表的列就是子查詢的結(jié)果集中的列。

去重的目的是為了讓臨時(shí)表盡可能的精簡(jiǎn)，因?yàn)樵谂R時(shí)表中重復(fù)的列并沒(méi)有什么意義。

當(dāng)結(jié)果集比較小時(shí)，MySQL會(huì)為臨時(shí)表使用memory引擎，并且為臨時(shí)表中的列建立哈希索引。哈希索引的查詢時(shí)間復(fù)雜度是O(1)，查詢速度是非?？斓?。

但是如果結(jié)果集比較大時(shí)，MySQL就會(huì)將臨時(shí)表定義為InnoDB類型表，并且建立B+樹(shù)索引，就像一個(gè)普通的表一樣使用。

話說(shuō)回來(lái)，將子查詢轉(zhuǎn)換為臨時(shí)表以后，其實(shí)查詢就變成了兩張表的連接查詢，也就是兩個(gè)表的內(nèi)連接。

一旦轉(zhuǎn)換為內(nèi)連接就好辦了，經(jīng)典的“小表驅(qū)動(dòng)大表”的優(yōu)化準(zhǔn)則就可以派上用場(chǎng)了。

我們看一下MySQL優(yōu)化器對(duì)上面的SQL優(yōu)化后的結(jié)果：

SELECT
  `t1`.`id` AS `id`,
  `t1`.`c1` AS `c1` 
FROM
  `table2` `t2`
  JOIN `table1` `t1` 
WHERE
  ( ( `t1`.`c1` = `t2`.`id` ) AND ( `t2`.`c2` = 3 ) )

可以看到，MySQL將其轉(zhuǎn)換為了內(nèi)連接，并且以table2為驅(qū)動(dòng)表，table1為被驅(qū)動(dòng)表的方式進(jìn)行了查詢。

由于c1和c2列上都有索引，那么此時(shí)這個(gè)sql的執(zhí)行速度還是相當(dāng)可以的。

半連接

雖然通過(guò)物化表的方式，MySQL將子查詢轉(zhuǎn)換為了連接查詢，但是創(chuàng)建臨時(shí)表的成本也是有的。

那可不可以再優(yōu)化一步，將創(chuàng)建臨時(shí)表的成本也給優(yōu)化掉呢？

在某些情況下，確實(shí)是可以的。

在上文我們提到，MySQL會(huì)將子查詢的結(jié)果集去重后，放入一個(gè)臨時(shí)表中。

那大家是否意識(shí)到，這個(gè)臨時(shí)表中的記錄都是唯一的，換句話說(shuō)，就是一個(gè)唯一索引的列。

那么當(dāng)我們的子查詢語(yǔ)句的結(jié)果集也類似于一個(gè)唯一索引集時(shí)，MySQL就不去創(chuàng)建臨時(shí)表了，而是直接嘗試將sql改寫成內(nèi)連接。

半連接的優(yōu)化還是比較復(fù)雜的，要求條件相對(duì)也苛刻一點(diǎn)，這里就不再詳細(xì)的說(shuō)了，感興趣的朋友可以去深入學(xué)習(xí)一下。

最后

無(wú)論MySQL采用了哪種優(yōu)化方法，只要知道了其實(shí)現(xiàn)的大致原理，對(duì)于使用者來(lái)說(shuō)，就有了對(duì)應(yīng)的優(yōu)化思路。

特別建議大家寫完SQL以后，習(xí)慣性的使用explain分析一下是否命中了索引，掃描的行數(shù)是否過(guò)多。

只有不斷的實(shí)操，優(yōu)化SQL的能力才會(huì)不斷提升。