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

mysql版本號是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é)果耗時294.428s。沒錯，將近5分鐘。

使用EXPLAIN分析下：

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

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

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

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

耗時并沒有明顯的提升。

竟然IN的方式這么難優(yōu)化，是不是可以放棄使用LEFT JOIN呢？語句調(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'

耗時超過5分鐘，放棄。

我們知道，在條件量少的情況，EXISTS和IN的效果沒有顯示的差別。但條件多的時候，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)

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

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

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

總結(jié)

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