建站
咨詢
售后
建站咨詢
新聞中心
對于很多的開發(fā)小伙伴來說，在MySQL中進(jìn)行in子查詢是一個(gè)非常常見的操作。
站在用戶的角度思考問題，與客戶深入溝通，找到天等網(wǎng)站設(shè)計(jì)與天等網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類型包括：成都做網(wǎng)站、成都網(wǎng)站建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名與空間、虛擬主機(jī)、企業(yè)郵箱。業(yè)務(wù)覆蓋天等地區(qū)。
雖然也有很多人說，盡量少用in子查詢，in的數(shù)量過多會影響查詢性能。
但其實(shí)MySQL做了不少的優(yōu)化手段來保證in子查詢的性能，大家也能在實(shí)際的業(yè)務(wù)中感受到in子查詢的速度也沒那么慢。
那今天就帶大家了解一下，MySQL到底是怎么來優(yōu)化in子查詢的。
普通in子查詢
首先，我們看一下MySQL是如何執(zhí)行一個(gè)普通的in子查詢的。
以一個(gè)簡單的子查詢?yōu)槔?/p>
select * from table1 where key1 in ('bb','ff','gg');
對于這個(gè)子查詢畫了一個(gè)簡單的查詢圖，不同顏色代表不同的數(shù)據(jù)頁。
在這個(gè)圖里，最上層的是根節(jié)點(diǎn)，中間的是非葉子節(jié)點(diǎn)，最下面的是葉子節(jié)點(diǎn)。
對于一個(gè)普通的二級索引來說，葉子節(jié)點(diǎn)存儲的是索引key和主鍵id，這些基礎(chǔ)知識就不詳細(xì)展開說了。
需要注意的是，二級索引在葉子節(jié)點(diǎn)中是按照key的順序從小到大排序的，但是對應(yīng)的主鍵id可不一定。
可能與大家想象的不同，MySQL在執(zhí)行in子查詢時(shí)，會把in語句中的條件當(dāng)做一個(gè)個(gè)的區(qū)間，比如：
['bb','bb']，['ff','ff']，['gg','gg']
然后MySQL在二級索引樹上，會先查詢['bb','bb']這個(gè)區(qū)間，比如首先查詢到第一個(gè)數(shù)據(jù)頁中符合條件的第一條數(shù)據(jù)(bb,2)，獲取到主鍵id=2之后，去聚簇索引回表查詢所需的數(shù)據(jù)（因?yàn)槲覀兪褂玫膕elect *，需要獲取到所有的列值）。
然后查詢第一個(gè)數(shù)據(jù)頁中符合條件的第二條數(shù)據(jù)(bb,5)，獲取到主鍵id=5之后，去聚簇索引回表查詢所需的數(shù)據(jù)，
然后查詢第二個(gè)數(shù)據(jù)頁中符合條件的第三條數(shù)據(jù)(ff,6)，
不斷的重復(fù)上面的動作。。。。
最后獲取到一個(gè)結(jié)果集，返回到Server，再由Server返回到客戶端。
看到這里大家是否可以感覺到，這樣查詢數(shù)據(jù)也太麻煩了，特別是當(dāng)in子查詢的條件越來越多時(shí)，如何保證性能呢？
下面，我們一起來看一下，MySQL是如何優(yōu)化in子查詢的。
物化表
首先，為了演示我們建兩張表table1和table2，并建立兩個(gè)二級索引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è)簡單的子查詢?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 );
對于一個(gè)這樣普通的子查詢來說，MySQL使用了一種叫做物化表的方式來提升性能。
什么意思呢？
就是將子查詢的結(jié)果集去重后放入到一個(gè)臨時(shí)表中，臨時(shí)表的列就是子查詢的結(jié)果集中的列。
去重的目的是為了讓臨時(shí)表盡可能的精簡，因?yàn)樵谂R時(shí)表中重復(fù)的列并沒有什么意義。
當(dāng)結(jié)果集比較小時(shí)，MySQL會為臨時(shí)表使用memory引擎，并且為臨時(shí)表中的列建立哈希索引。哈希索引的查詢時(shí)間復(fù)雜度是O(1)，查詢速度是非?？斓摹?/p>
但是如果結(jié)果集比較大時(shí)，MySQL就會將臨時(shí)表定義為InnoDB類型表，并且建立B+樹索引，就像一個(gè)普通的表一樣使用。
話說回來，將子查詢轉(zhuǎn)換為臨時(shí)表以后，其實(shí)查詢就變成了兩張表的連接查詢，也就是兩個(gè)表的內(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ū)動表的方式進(jìn)行了查詢。
由于c1和c2列上都有索引，那么此時(shí)這個(gè)sql的執(zhí)行速度還是相當(dāng)可以的。
半連接
雖然通過物化表的方式，MySQL將子查詢轉(zhuǎn)換為了連接查詢，但是創(chuàng)建臨時(shí)表的成本也是有的。
那可不可以再優(yōu)化一步，將創(chuàng)建臨時(shí)表的成本也給優(yōu)化掉呢？
在某些情況下，確實(shí)是可以的。
在上文我們提到，MySQL會將子查詢的結(jié)果集去重后，放入一個(gè)臨時(shí)表中。
那大家是否意識到，這個(gè)臨時(shí)表中的記錄都是唯一的，換句話說，就是一個(gè)唯一索引的列。
那么當(dāng)我們的子查詢語句的結(jié)果集也類似于一個(gè)唯一索引集時(shí)，MySQL就不去創(chuàng)建臨時(shí)表了，而是直接嘗試將sql改寫成內(nèi)連接。
半連接的優(yōu)化還是比較復(fù)雜的，要求條件相對也苛刻一點(diǎn)，這里就不再詳細(xì)的說了，感興趣的朋友可以去深入學(xué)習(xí)一下。
最后
無論MySQL采用了哪種優(yōu)化方法，只要知道了其實(shí)現(xiàn)的大致原理，對于使用者來說，就有了對應(yīng)的優(yōu)化思路。
特別建議大家寫完SQL以后，習(xí)慣性的使用explain分析一下是否命中了索引，掃描的行數(shù)是否過多。
只有不斷的實(shí)操，優(yōu)化SQL的能力才會不斷提升。
網(wǎng)頁名稱：一文帶你了解MySQL是如何優(yōu)化in子查詢的，其實(shí)非常簡單
本文URL：http://fisionsoft.com.cn/article/cohpdes.html
