MySQL查詢select * from table where id in (幾百或幾千個(gè)id) ，如何提高效率？

CKJOKER 2022-06-28 14:43 回答0 收藏1

問題描述：電商網(wǎng)站，一個(gè)商品屬性表，幾十萬條記錄，80M，索引只有主鍵id，做這樣的查詢?nèi)绾翁岣咝剩?select * from table where id in (幾百或幾千個(gè)id) 這些id沒啥規(guī)律，分散的……

收藏問題

10條回答

Jonathan Shiebe

回答于2022-06-28 14:43

看了下面各位的回答，有的說用exist，有的說用join，難道你們不是在把簡(jiǎn)單的事情復(fù)雜化了嗎？竟然還有子表子查詢一說？也有朋友說的很精準(zhǔn)，不要用select *，這個(gè)*是個(gè)坑，實(shí)際開發(fā)過程中，關(guān)于MySQL開發(fā)規(guī)范也會(huì)明確告知大家不要select *。

首先我想問的是：查詢MySQL的一張表怎么查最快？當(dāng)然是根據(jù)主鍵查詢了！

默認(rèn)你的MySQL庫(kù)、表引擎是Innodb引擎，然后會(huì)有一顆主鍵的B+樹，葉子節(jié)點(diǎn)就是這個(gè)主鍵索引對(duì)應(yīng)的數(shù)據(jù)，意味著一次查詢即可，回表都不需要好不好？簡(jiǎn)單直接！

這就是MySQL在Innodb引擎下的聚集索引。

什么是聚集索引？

InnoDB聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄，因此InnoDB必須要有且只有一個(gè)聚集索引。

1.如果表定義了PK（Primary Key，主鍵），那么PK就是聚集索引。

2.如果表沒有定義PK，則第一個(gè)NOT NULL UNIQUE的列就是聚集索引。

3.否則InnoDB會(huì)另外創(chuàng)建一個(gè)隱藏的ROWID作為聚集索引。

這種機(jī)制使得基于PK的查詢速度非常快，因?yàn)橹苯佣ㄎ坏男杏涗洝?/p>

下圖是利用普通索引做查詢時(shí)候的一個(gè)回表操作，如何避免回表操作？使用覆蓋索引！即select xxx,yyy from table where xxx=' and yyy='，只能查詢xxx,yyy就會(huì)避免回表操作！

所以你還搞什么其他各種操作來秀呢？只不過題主說了id不是連續(xù)的，所以做不到范圍查詢，也就無法between查詢了。

不要純粹的依賴數(shù)據(jù)庫(kù)

如果這個(gè)查詢量級(jí)很大，并發(fā)很高，原則上我們是不允許直接查庫(kù)的，中間必須有一層緩存，比如Redis。那至于這個(gè)數(shù)據(jù)怎么存儲(chǔ)到redis就要看具體業(yè)務(wù)具體分析了。

如果內(nèi)存足夠，甚至可以把這幾十萬的數(shù)據(jù)直接放到redis里面去，然后通過redis 的管道查詢一次給批量查詢出來。

如果沒必要存儲(chǔ)這么多，或者不讓存這么多，是不是可以采用redis的淘汰策略來控制緩存里的數(shù)據(jù)都是熱點(diǎn)數(shù)據(jù)？

評(píng)論0 贊同0

加載中...

jayce

回答于2022-06-28 14:43

幾十萬用主鍵查你還閑效率低？又不是上億的表。如果上億了那就拆表吧，也簡(jiǎn)單的。

評(píng)論0 贊同0

加載中...

luoyibu

回答于2022-06-28 14:43

select a.* from tableA a

inner join （select id from tableB）b on a. id=b. id

創(chuàng)建臨時(shí)表tableB并建主鍵索引，

或者構(gòu)建inner join的只包括id字段的虛擬表b。

評(píng)論0 贊同0

加載中...

mysql8.0版本中有with公共表達(dá)式，這個(gè)是最好的，通過兩個(gè)表做關(guān)聯(lián)，8.0以下的取決于你的where條件了，icp可以謂詞下推，in在優(yōu)化器會(huì)自動(dòng)轉(zhuǎn)換成semi-jion，在8.0.4以后exists也可以使用semi join了，在之前的時(shí)候exists就只能使用dependent subquey了，exists 要想快的話是小表驅(qū)動(dòng)大表；所以說這里最重要的是看你的where條件中是否有篩選。如果沒有的話，最好建臨時(shí)表或者用with，另外不要用select *，要不然在后面優(yōu)化器需要block nestloop的時(shí)候會(huì)有壓力，如果都不讓用的話，你目前的敘述，in比exists好

評(píng)論0 贊同0

加載中...

jiekechoo

回答于2022-06-28 14:43

MySQL官網(wǎng)有對(duì)in的說法，如果有索引，其實(shí)都差不多。如果你查詢的不是所有字段，只拿abc三個(gè)字段，可以在abc三個(gè)字段建立組合索引，in再走索引。如果我沒有記錯(cuò)MySQL不會(huì)限制你in多少，但是會(huì)限制整個(gè)SQL總字節(jié)數(shù)

評(píng)論0 贊同0