對于很多的開發(fā)小伙伴來說，在MySQL中進行in子查詢是一個非常常見的操作。

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

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

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

普通in子查詢

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

以一個簡單的子查詢?yōu)槔?/p>

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

對于這個子查詢畫了一個簡單的查詢圖，不同顏色代表不同的數(shù)據(jù)頁。

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

對于一個普通的二級索引來說，葉子節(jié)點存儲的是索引key和主鍵id，這些基礎(chǔ)知識就不詳細(xì)展開說了。

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

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

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

然后MySQL在二級索引樹上，會先查詢['bb','bb']這個區(qū)間，比如首先查詢到第一個數(shù)據(jù)頁中符合條件的第一條數(shù)據(jù)(bb,2)，獲取到主鍵id=2之后，去聚簇索引回表查詢所需的數(shù)據(jù)（因為我們使用的select *，需要獲取到所有的列值）。

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

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

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

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

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

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

物化表

首先，為了演示我們建兩張表table1和table2，并建立兩個二級索引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

下面以一個簡單的子查詢?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 );

對于一個這樣普通的子查詢來說，MySQL使用了一種叫做物化表的方式來提升性能。

什么意思呢？

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

去重的目的是為了讓臨時表盡可能的精簡，因為在臨時表中重復(fù)的列并沒有什么意義。

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

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

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

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

我們看一下MySQL優(yōu)化器對上面的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ū)動表，table1為被驅(qū)動表的方式進行了查詢。

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

半連接

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

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

在某些情況下，確實是可以的。

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

那大家是否意識到，這個臨時表中的記錄都是唯一的，換句話說，就是一個唯一索引的列。

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

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

最后

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

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

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