一文帶你掌握in到底走不走索引？啥情況能走啥情況不能走

作者：Shepherd 2024-11-29 10:44:00

總體來(lái)說(shuō)，in后面條件越少越好，假設(shè)一張表有1000萬(wàn)條數(shù)據(jù)，in后面的條件有10000個(gè)，這時(shí)候就算走了range索引，估計(jì)效率也好不到哪里。

in到底用不用索引感覺(jué)像一樁懸疑片！古早時(shí)期的面經(jīng)，統(tǒng)一說(shuō)不走索引，在一些程序員腦海中從此留下不可磨滅的印記。有些從業(yè)時(shí)間較長(zhǎng)的程序員腦子里的第一反應(yīng)就是不走索引，上個(gè)月我就曾經(jīng)被同事這樣質(zhì)疑過(guò)。

但是那是mysql5.5以前的老黃歷了，現(xiàn)在都到8.0+了,5.5(甚至更早)以后可以肯定的是它會(huì)走索引。但必然走索引嗎？不一定。

我搜索引擎上搜索關(guān)鍵詞 in/or 和索引，出來(lái)一大片文章，一般都會(huì)說(shuō)，in/or能走索引，但后面跟的條件個(gè)數(shù)多了就不走索引了。但問(wèn)題就來(lái)了，這個(gè)多了到底是多少才算多？對(duì)于一個(gè)動(dòng)態(tài)查詢的SQL,我咋知道到底走不走索引？如何量化計(jì)算呢？

這時(shí)候就語(yǔ)焉不詳或者直接跳過(guò)。

大名鼎鼎的《阿里巴巴JAVA開(kāi)發(fā)規(guī)范》倒是一刀切。最好不超過(guò)1000。

圖片

人家這規(guī)范只是推薦，也不是強(qiáng)制，是吧，不能吐槽。

而且超過(guò)1000就算用上了range級(jí)別的查詢，那可能也快不到哪里去啊，對(duì)于要求快速響應(yīng)的互聯(lián)網(wǎng)需求來(lái)說(shuō)這推薦好像沒(méi)毛病。

但這不是重點(diǎn)，今天的重點(diǎn)在于，我一定要搞清楚，在保證explain 的type為range而不是ALL全表掃描的前提下，到底select * from table where id in (1,2,3.....x)這個(gè)x能到多少。

問(wèn)題

首先建一張測(cè)試表，來(lái)一步復(fù)現(xiàn)一下，走與不走索引的情況。

mysql

版本:5.7.19 引擎:innodb

創(chuàng)建一個(gè)測(cè)試表

sql

 代碼解讀
復(fù)制代碼CREATE TABLE `t_person` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

使用SQL

EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1)

查看執(zhí)行計(jì)劃

圖片

此時(shí)表里無(wú)數(shù)據(jù)，顯示的是no matching row in const table.

少量數(shù)據(jù)

插入一條數(shù)據(jù)insert t_person (id,name) values(1,'張三')

使用SQL

EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1)

查看執(zhí)行計(jì)劃

圖片

使用了索引，還是效率最高的const(system生產(chǎn)環(huán)境不可能的吧)，此時(shí)id in(1)相當(dāng)于 id = 1。

在in里增加點(diǎn)條件。

sql變成EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2)

查看執(zhí)行計(jì)劃

圖片

使用了索引，但級(jí)別下降到了range,即范圍索引。

繼續(xù)在in里增加條件。

sql變成EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2，3)

查看執(zhí)行計(jì)劃

圖片

索引級(jí)別變成了ALL,即全表掃描，其實(shí)是索引失效了。

再往表里插入兩條數(shù)據(jù)。此時(shí)總共3條數(shù)據(jù)。

scss

 代碼解讀
復(fù)制代碼insert t_person (id,name) values(2,'李四')
insert t_person (id,name) values(3,'王五')

再使用sqlEXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1, 2，3)

查看執(zhí)行計(jì)劃

圖片

可以看到，隨時(shí)表數(shù)據(jù)的增加，同樣的sql執(zhí)行計(jì)劃從ALL變回了range,索引又生效了。

同樣地，再增加一個(gè)in條件，EXPLAIN SELECT id, NAME FROM t_person WHERE id IN (1,2,3,4)的執(zhí)行計(jì)劃又變回了ALL,這里就不放圖了。

多點(diǎn)數(shù)據(jù)

以上只是小打小鬧撒撒水啦，總共幾條數(shù)據(jù)，in的條件都快超過(guò)表數(shù)據(jù)了，執(zhí)行計(jì)算都不用預(yù)估就知道全表掃描還好一點(diǎn)啦。

我再往表里插入100萬(wàn)條數(shù)據(jù)。

圖片

我先按照阿里的開(kāi)發(fā)規(guī)范推薦的1000這個(gè)值作為臨界值，先使用900個(gè)條件

圖片

再使用1100個(gè)條件

圖片

上圖表明，這兩種情況都使用到了range范圍索引呢。

再加大劑量，直接上10萬(wàn)。

圖片

步子邁大了，咔，這下終于全表掃描了。

但是還是沒(méi)找到臨界值。

官網(wǎng)上尋找答案

dev.mysql.com/doc/refman/…

我在這里尋找到了一個(gè)參數(shù)，描述的倒像是相似的問(wèn)題。

圖片

這個(gè)方法說(shuō)的是當(dāng)使用in或or查詢時(shí)，比如where in(1,2,3),執(zhí)行引擎會(huì)先預(yù)估表中的數(shù)量，表中的數(shù)量將決定使用的查詢方式，比如，如果表中只有3條數(shù)據(jù)，那么很明顯，這時(shí)候直接全表掃描。

而這個(gè)預(yù)估的方法有2種,一是dive到index中即利用索引完成元組數(shù)的估算,簡(jiǎn)稱index dive; 二是使用索引的統(tǒng)計(jì)數(shù)值,進(jìn)行估算.

相比這2種方式,在效果上:

index dive: 速度慢,但能得到精確的值（MySQL的實(shí)現(xiàn)是數(shù)索引對(duì)應(yīng)的索引項(xiàng)個(gè)數(shù)，所以精確）
index statistics: 速度快,但得到的值未必精確.

但eq_range_index_dive_limit這個(gè)參數(shù)確實(shí)跟今天的主題相關(guān)系數(shù)不大。很明顯，這個(gè)值在mysql 5.7是200，一開(kāi)始的in后面的條件個(gè)數(shù)就是900，依然是走了range索引的。

stackoverflow

于是我找到了stackoverflow，在上面把msyql in count 這些關(guān)鍵詞搜了一下，沒(méi)有找到相關(guān)的問(wèn)題。

然后我把問(wèn)題詳細(xì)描述了一下，提了一個(gè)新的問(wèn)題，沒(méi)想到啊，半個(gè)小時(shí)不到，人家就直接給我點(diǎn)踩，并給出了相似的已解答問(wèn)題。

尷尬了。我超喜歡stackoverflow，這里的人個(gè)個(gè)都是人才。

相似的問(wèn)題在這里。

stackoverflow.com/questions/7…

這位仁兄也在in的使用中也有很多問(wèn)號(hào)，in的條件卡在14000左右，超過(guò)就失去了range索引。

下面高贊答案提到了一個(gè)參數(shù)，range_optimizer_max_mem_size ，一看就很有搞頭啊。

圖片

轉(zhuǎn)到mysql官網(wǎng)，憑我的渣渣英語(yǔ)也能看明白，我知道，大概我找到答案了。

dev.mysql.com/doc/refman/…

企業(yè)微信截圖

要控制范圍優(yōu)化器可用的內(nèi)存，使用range_optimizer_max_mem_size系統(tǒng)變量:

值為0表示“沒(méi)有限制”。
當(dāng)值大于0時(shí)，優(yōu)化器將跟蹤在考慮范圍訪問(wèn)方法時(shí)所消耗的內(nèi)存。如果即將超過(guò)指定的限制，則放棄范圍訪問(wèn)方法，轉(zhuǎn)而考慮其他方法，包括全表掃描。這可能不太理想。如果發(fā)生這種情況，會(huì)出現(xiàn)以下警告(其中N是當(dāng)前的range_optimizer_max_mem_size值)。

現(xiàn)在事情就很簡(jiǎn)單了。

圖片

range_optimizer_max_mem_size默認(rèn)是8M，使用同樣的SQL，in后面同樣的條件為固定的19900個(gè)，在range_optimizer_max_mem_size=8M,range_optimizer_max_mem_size=8情況下分別執(zhí)行一下看效果。

range_optimizer_max_mem_size=8M時(shí)，走range索引。

圖片