一次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)行等值運算，不能進(jìn)行范圍查找。那IN是等值運算，兩種索引方法都適用。即然這樣，把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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL視圖原理與基本操作,簡單描述了mysql視圖的概念、功能并結(jié)合實例形式分析了mysql視圖的創(chuàng)建、查詢、修改、刪除等相關(guān)操作技巧,需要的朋友可以參考下2018-03-03
MySQL數(shù)據(jù)庫基于sysbench實現(xiàn)OLTP基準(zhǔn)測試
這篇文章主要介紹了MySQL數(shù)據(jù)庫基于sysbench實現(xiàn)OLTP基準(zhǔn)測試,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-11-11
這篇文章主要介紹了MySQL中對表連接查詢的簡單優(yōu)化教程,表連接查詢是MySQL最常用到的基本操作之一,因而其的優(yōu)化也非常值得注意,需要的朋友可以參考下2015-12-12
今天小編就為大家分享一篇關(guān)于mysql數(shù)據(jù)庫太大了如何備份與還原，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
mysql 8.0.12 安裝配置方法圖文教程（windows10）
這篇文章主要為大家詳細(xì)介紹了windows10下mysql 8.0.12 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-08-08
本文章將介紹什么是通配符、如何使用通配符以及怎樣使用LIKE操作符進(jìn)行通配搜索，以便對數(shù)據(jù)進(jìn)行復(fù)雜過濾，感興趣的小伙伴跟著小編一起來學(xué)習(xí)吧2023-07-07
MySQL使用SHOW PROCESSLIST的實現(xiàn)
本文主要介紹了MySQL使用SHOW PROCESSLIST的實現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-03-03
連接MySQL時出現(xiàn)1449與1045異常解決辦法
這篇文章主要介紹了連接MySQL時出現(xiàn)1449與1045異常解決辦法的相關(guān)資料,通過IP鏈接MySQL的時候會出現(xiàn)1499與1054錯誤異常的情況，這里提供解決辦法，需要的朋友可以參考下2017-09-09
本文將詳細(xì)介紹怎樣mysql觸發(fā)器和存儲過程，需要了解的朋友可以詳細(xì)參考下2012-11-11
ORDER BY 語句用于根據(jù)指定的列對結(jié)果集進(jìn)行排序,在日常工作中經(jīng)常會用到,這篇文章主要給大家介紹了關(guān)于MySQL子查詢中order by不生效問題的解決方法,需要的朋友可以參考下2021-07-07