in用不用索引，啥時(shí)候能用啥時(shí)候不能用，一文說清-即時(shí)看
        
                    
        
                        作者：  來源： 博客園 
                        2023-06-18 10:13:59

                    
        
                
        
            
        
            
        
                
        
                    　
        in/or到底能不能用索引應(yīng)該是肯定的，但有時(shí)生效有時(shí)不生效，這個(gè)能不能量化計(jì)算？這是本文想討論和解答的問題。
        in到底用不用索引感覺像一樁懸疑片！古早時(shí)期的面經(jīng)，統(tǒng)一說不走索引，在一些程序員腦海中從此留下不可磨滅的印記。有些從業(yè)時(shí)間較長的程序員腦子里的第一反應(yīng)就是不走索引，上個(gè)月我就曾經(jīng)被同事這樣質(zhì)疑過。

        (相關(guān)資料圖)
        但是那是mysql5.5以前的老黃歷了，現(xiàn)在都到8.0+了,5.5(甚至更早)以后可以肯定的是它會(huì)走索引。但必然走索引嗎？不一定。
        我搜索引擎上搜索關(guān)鍵詞 in/or和 索引，出來一大片文章，一般都會(huì)說，in/or能走索引，但后面跟的條件個(gè)數(shù)多了就不走索引了。但問題就來了，這個(gè)多了到底是多少才算多？對(duì)于一個(gè)動(dòng)態(tài)查詢的SQL,我咋知道到底走不走索引？如何量化計(jì)算呢？
        這時(shí)候就語焉不詳或者直接跳過。
        大名鼎鼎的《阿里巴巴JAVA開發(fā)手冊(cè)》倒是一刀切。最好不超過1000。
        人家這規(guī)范只是推薦，也不是強(qiáng)制，是吧，不能吐槽。
        而且超過1000就算用上了range級(jí)別的查詢，那可能也快不到哪里去啊，對(duì)于要求快速響應(yīng)的互聯(lián)網(wǎng)需求來說這推薦好像沒毛病。
        但這不是重點(diǎn)，今天的重點(diǎn)在于，我一定要搞清楚，在保證explain的type為range而不是ALL全表掃描的前提下，到底select * from table where id in (1,2,3.....x)這個(gè)x能到多少。
        問題
        首先建一張測試表，來一步復(fù)現(xiàn)一下，走與不走索引的情況。
        mysql
        版本:5.7.19引擎:innodb
        創(chuàng)建一個(gè)測試表
        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í)表里無數(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ù)。
        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的條件都快超過表數(shù)據(jù)了，執(zhí)行計(jì)算都不用預(yù)估就知道全表掃描還好一點(diǎn)啦。
        我再往表里插入100萬條數(shù)據(jù)。
        我先按照阿里的開發(fā)規(guī)范推薦的1000這個(gè)值作為臨界值，先使用900個(gè)條件
        再使用1100個(gè)條件
        上圖表明，這兩種情況都使用到了range范圍索引呢。
        再加大劑量，直接上10萬。
        步子邁大了，咔，這下終于全表掃描了。
        但是還是沒找到臨界值。
        官網(wǎng)上尋找答案
        https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
        我在這里尋找到了一個(gè)參數(shù)，描述的倒像是相似的問題。
        這個(gè)方法說的是當(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ù)的估算,簡稱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， 一開始的in后面的條件個(gè)數(shù)就是900，依然是走了range索引的。
        stackoverflow
        于是我找到了stackoverflow，在上面把msyqlincount這些關(guān)鍵詞搜了一下，沒有找到相關(guān)的問題。
        然后我把問題詳細(xì)描述了一下，提了一個(gè)新的問題，沒想到啊，半個(gè)小時(shí)不到，人家就直接給我點(diǎn)踩，并給出了相似的已解答問題。
        尷尬了。我超喜歡stackoverflow，這里的人個(gè)個(gè)都是人才。
        相似的問題在這里。
        https://stackoverflow.com/questions/72361880/mysql-in-operator-on-large-number-of-values
        這位仁兄也在in的使用中也有很多問號(hào)，in的條件卡在14000左右，超過就失去了range索引。
        下面高贊答案提到了一個(gè)參數(shù)，range_optimizer_max_mem_size ，一看就很有搞頭啊。
        轉(zhuǎn)到mysql官網(wǎng)，憑我的渣渣英語也能看明白，我知道，大概我找到答案了。
        https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#equality-range-optimization
        要控制范圍優(yōu)化器可用的內(nèi)存，使用range_optimizer_max_mem_size系統(tǒng)變量:
        值為0表示“沒有限制”。
        當(dāng)值大于0時(shí)，優(yōu)化器將跟蹤在考慮范圍訪問方法時(shí)所消耗的內(nèi)存。如果即將超過指定的限制，則放棄范圍訪問方法，轉(zhuǎn)而考慮其他方法，包括全表掃描。這可能不太理想。如果發(fā)生這種情況，會(huì)出現(xiàn)以下警告(其中N是當(dāng)前的range_optimizer_max_mem_size值)。
        現(xià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索引。
        range_optimizer_max_mem_size=8時(shí)，走ALL全表掃描。
        破案了！
        明明官網(wǎng)上就有答案，我卻三過家門而不入。
        結(jié)論   
        in兩種情況會(huì)走全表掃描。
        in后面條件導(dǎo)致sql大小超過range_optimizer_max_mem_size。in后面條件個(gè)數(shù)接近或者等于表數(shù)量，執(zhí)行引擎認(rèn)為此時(shí)全表掃描更加合適。
        推而廣之，or也是一樣的道理。歸根結(jié)底都是范圍查詢。
        當(dāng)然，總體來說，in后面條件越少越好，假設(shè)一張表有1000萬條數(shù)據(jù)，in后面的條件有10000個(gè)，這時(shí)候就算走了range索引，估計(jì)效率也好不到哪里。
        完　
					
        關(guān)鍵詞
                     
        
                
        
                
        
				 
        
				  				
          
                
        

            
        

            
        
                
        相關(guān)文章