mysql中between和in的區(qū)別

下面兩條語(yǔ)句有什么區(qū)別，為什么都提倡使用范圍查詢 ?

select * from T where k in(1,2,3,4,5)
select * from T where k between 1 and 5

其實(shí)between和in這兩個(gè)關(guān)鍵字應(yīng)用的場(chǎng)景重合度并不高，但是我們可以通過(guò)思考這么一個(gè)問(wèn)題，來(lái)理清這兩者執(zhí)行的執(zhí)行過(guò)程和邏輯。

要回答這個(gè)問(wèn)題就需要先了解索引的數(shù)據(jù)結(jié)構(gòu)，以及mysql索引利用b+樹(shù)執(zhí)行查詢的執(zhí)行過(guò)程，這里先默認(rèn)大家具備了這個(gè)條件。

先區(qū)分下可能的情況。

第一個(gè)是k字段是主鍵，第二個(gè)是k字段是二級(jí)索引，第三種情況是k是普通字段。

另外，我們需要搞清楚in關(guān)鍵字和between兩種查詢方式在執(zhí)行的時(shí)候的不同之處；

• in關(guān)鍵字其實(shí)是等值查詢的合并，將多個(gè)等值查詢合并成一個(gè)，減少查詢和返回次數(shù)；
• between查詢是典型的范圍查詢，在命中索引的情況下，會(huì)直接在查詢首個(gè)符合條件的數(shù)據(jù)，然后根據(jù)指針一次獲取下一個(gè)葉子節(jié)點(diǎn)數(shù)據(jù)進(jìn)行比較直到查詢到不符合條件的數(shù)據(jù)為止。
• 現(xiàn)在我們?cè)賮?lái)對(duì)三種情況進(jìn)行梳理，

k是主鍵索引和二級(jí)索引的情況是相似的，唯一的區(qū)別就是二級(jí)索引的話可能需要回表，回表不是這次討論的重點(diǎn)，所以我們把這兩種情況合并到一起說(shuō)；

in 條件實(shí)際查詢的時(shí)候是多次搜索索引樹(shù)，而 between 條件只需要執(zhí)行一次搜索樹(shù)的查詢就可以獲得結(jié)果，本題為例,加入索引的底層實(shí)現(xiàn)結(jié)構(gòu)使用的是b+樹(shù)，使用關(guān)鍵字 in 要樹(shù)搜素5次，也就是做5次等值查詢；而 bewteen 基于b+樹(shù)的范圍查詢邏輯，當(dāng)找到第一個(gè)滿足條件的葉子節(jié)點(diǎn)后，根據(jù)當(dāng)前葉子節(jié)點(diǎn)指向的下一個(gè)葉子節(jié)點(diǎn)繼續(xù)查詢，直到查到不滿足條件的葉子節(jié)點(diǎn)，所以只需要樹(shù)搜索一次。

而對(duì)于k不是索引的情況，in條件相當(dāng)于執(zhí)行了多次的全文遍歷，而between其實(shí)也是全文遍歷，都不是一個(gè)好的方式。

mysql條件查詢語(yǔ)句between and和in的效率比較

工作需要，寫(xiě)按sensor_uuid在某范圍查詢的語(yǔ)句：

1.

select * from xz_sensor_data where sensor_time>='2019-03-28 00:00:00.000' and sensor_time<='2019-03-28 08:00:00.000' and sensor_uuid in('20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','201901','201902','201903','201904','201905','201906','201907','201908','201909','201910','201911','201912','201913') ?order by sensor_time asc

2.

select * from xz_sensor_data where sensor_time>='2019-03-28 00:00:00.000' and sensor_time<='2019-03-28 08:00:00.000' and (sensor_uuid ?between '19' and '36' or sensor_uuid between '201900' and '201914' )

發(fā)現(xiàn)第二條執(zhí)行效率高。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論