一次Mysql使用IN大數(shù)據(jù)量的優(yōu)化記錄

mysql版本號(hào)是5.7.28，表A有390W條記錄，使用InnoDB引擎，其中varchar類型字段mac已建立索引，索引方法為B-tree。B表僅有5000+條記錄。

有一條SQL指令是這樣寫的：

SELECT * FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:b",...此外省略900+條)

通過查詢出來的結(jié)果耗時(shí)294.428s。沒錯(cuò)，將近5分鐘。

使用EXPLAIN分析下：

訪問類型type是range，且已命中索引，rows行也只有587776，可為什么查詢耗時(shí)要這么久？

mac的索引方法使用了B-tree，那對(duì)比下它與HASH的區(qū)別，簡(jiǎn)單地總結(jié)下：B-tree索引可以用于進(jìn)行 =,>,>=,,=和between的計(jì)算，而HASH只能進(jìn)行等值運(yùn)算，不能進(jìn)行范圍查找。那IN是等值運(yùn)算，兩種索引方法都適用。即然這樣，把mac的索引方法修改為HASH，同樣的查詢耗時(shí)為。

既然調(diào)整索引方法并不能明顯地提升語(yǔ)句的查詢性能，那只能從語(yǔ)句本身中進(jìn)行處理。其實(shí)明眼人剛開始一看就知道，SELECT * 是很耗性能的，那我們只查業(yè)務(wù)上需要的字段，語(yǔ)句調(diào)整為：

SELECT id,mileage FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:b",...此外省略900+條)

耗時(shí)并沒有明顯的提升。

竟然IN的方式這么難優(yōu)化，是不是可以放棄使用LEFT JOIN呢？語(yǔ)句調(diào)整為：

SELECT a.id,a.mileage FROM A a LEFT JOIN B b ON b.mac = a.mac WHERE b.create_time >= '2020-01-01'

耗時(shí)超過5分鐘，放棄。

我們知道，在條件量少的情況，EXISTS和IN的效果沒有顯示的差別。但條件多的時(shí)候，IN要比EXISTS的效率也高，來試下EXISTS：

SELECT id,mileage FROM A a WHERE EXISTS(SELECT mac FROM B WHERE create_time >= '2020-01-01' AND mac = a.mac)

耗時(shí)也是超過5分鐘，IN的效率確實(shí)要比EXISTS高，放棄。

所以最后的結(jié)論是，如果IN后接大數(shù)據(jù)量的String，要慎重。

在項(xiàng)目中我把mac作為唯一標(biāo)識(shí)建立與id的對(duì)應(yīng)表，在A表使用mac_id代替mac，查詢的時(shí)候使用IN(1,2,3...)。效率會(huì)提高一些。當(dāng)前使用NoSQL也是一種方式。

總結(jié)

到此這篇關(guān)于一次Mysql使用IN大數(shù)據(jù)量?jī)?yōu)化的文章就介紹到這了,更多相關(guān)Mysql使用IN大數(shù)據(jù)量?jī)?yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！