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

---

補充一下官丁， in (幾百或幾千個id)，我覺得這個效率不成問題啊，特別是在id為主鍵的時候，MySQL的效率還是很高的。如果你非得要繼續提高，可以做以下事情（假設你是innodb，如果不是的話，先改為innodb）：

（1）如果MySQL版本小於5.5，那麼升級版本到5.5以後，最好是最新版本，5.5對in的操作有了飛躍性的提高。

（2）增加內存，開大innodb_buffer_pool,增加pool可以可以緩存page的空間，讓儘可能多的數據都緩存。

（3）改善磁碟配置，用ssd或者flash卡存儲，提高磁碟掃描速度

（4）官丁說的第四個，不需要改成單條，但可以把一個in很大的列表分成若干分開的列表，至於分多少，可能需要根據機器效率還有你需要的效率取個平衡了，in的列表如果非常大，例如超過10w，100w，效率不高。

---

我總覺得嘛，如果你那幾百幾千個id是從另一個query來的，那你應該把兩個query給join在一起才行。很難想像為什麼要查詢幾百幾千個hardcode好的id的常量。

只要你在幾張表上的id不管是PK還是FK上都有了index，效果拔群

---

剛剛測了哈 5.6 的 INNER JOIN 和 IN 發現 兩個效率差不多(5000條數據, INNER JOIN快0.03秒),

然後用 EXPLAIN EXTENDED + 查詢語句 ， 再執行 SHOW WARNINGS

查看到 用 INNER JOIN 和 IN 的兩個查詢的最終sql（優化器優化後）是一樣的。

看來 IN 在新版本做了優化.

建議題主升級到新版本.

如果不好升級, 建議將 IN 改成 INNER JOIN

---

我來講一下這個問題吧：

題主說的查詢應該是這樣吧：select * from a where id in (select id from b );

對於這條sql語句它的執行計劃其實並不是先查詢出b表的所有id,然後再與a表的id進行比較。

mysql會把in子查詢轉換成exists相關子查詢，所以它實際等同於這條sql語句：select * from a where exists(select * from b where b.id=a.id );

而exists相關子查詢的執行原理是: 循環取出a表的每一條記錄與b表進行比較，比較的條件是a.id=b.id . 看a表的每條記錄的id是否在b表存在，如果存在就行返回a表的這條記錄。

exists查詢有什麼弊端？

由exists執行原理可知，a表(外表)使用不了索引，必須全表掃描，因為是拿a表的數據到b表查。而且必須得使用a表的數據到b表中查（外表到里表中），順序是固定死的。

如何優化？

建索引。但是由上面分析可知，要建索引只能在b表的id欄位建，不能在a表的id上，mysql利用不上。

這樣優化夠了嗎？還差一些。

由於exists查詢它的執行計劃只能拿著a表的數據到b表查（外表到里表中），雖然可以在b表的id欄位建索引來提高查詢效率。

但是並不能反過來拿著b表的數據到a表查，exists子查詢的查詢順序是固定死的。

為什麼要反過來？

因為首先可以肯定的是反過來的結果也是一樣的。這樣就又引出了一個更細緻的疑問：在雙方兩個表的id欄位上都建有索引時，到底是a表查b表的效率高，還是b表查a表的效率高？

該如何進一步優化？

把查詢修改成inner join連接查詢：select * from a inner join b on a.id=b.id; （但是僅此還不夠，接著往下看）

為什麼不用left join 和 right join？

這時候表之間的連接的順序就被固定住了，比如左連接就是必須先查左表全表掃描，然後一條一條的到另外表去查詢，右連接同理。仍然不是最好的選擇。

為什麼使用inner join就可以？

inner join中的兩張表，如： a inner join b，但實際執行的順序是跟寫法的順序沒有半毛錢關係的，最終執行也可能會是b連接a，順序不是固定死的。如果on條件欄位有索引的情況下，同樣可以使用上索引。

那我們又怎麼能知道a和b什麼樣的執行順序效率更高？

答：你不知道，我也不知道。誰知道？mysql自己知道。讓mysql自己去判斷（查詢優化器）。具體表的連接順序和使用索引情況，mysql查詢優化器會對每種情況做出成本評估，最終選擇最優的那個做為執行計劃。

在inner join的連接中,mysql會自己評估使用a表查b表的效率高還是b表查a表高，如果兩個表都建有索引的情況下，mysql同樣會評估使用a表條件欄位上的索引效率高還是b表的。

而我們要做的就是：把兩個表的連接條件的兩個欄位都各自建立上索引，然後explain 一下，查看執行計劃，看mysql到底利用了哪個索引，最後再把沒有使用索引的表的欄位索引給去掉就行了。

---

全部都是按照id查詢，而且整個資料庫才80M嘛，這個是灰常灰常標準的Key-Value DB的拿手好戲了。直接改用Redis搞定。

---

為什麼沒人提inner join

---

針對業務要求：select * from table where id in (幾百或幾千個id)，反問一句：

一次性為啥要顯示幾百 或幾千條記錄？

問這個問題的原因是：除非你是資料庫導出的業務場景，否則這麽多條記錄無法顯示在一屏幕中，要是分頁，也沒有見排序操作

提高效率辦法：

1.資料庫表的大欄位，採用主從表的模式；--這條要權衡，畢竟不同的業務場景和數據場景下，有區別，一般是較好的

2.打開query cache，畢竟商品屬性表修改的可能性較小，而且你只有80M而已～～

3.設置讀數據的參數 read_buffer_size 與 read_rnd_buffer_size 建議適當設置大一點，比如 分別為4M 1M

4.最重要的，修改為單條讀取，或者減少一次性讀取的數量（按需讀取）

---

in 型子查詢，確實在5.5之前，有該優化問題。5.6已經解決了。

5.5之前，mysql會認為是 關聯查詢，將其轉換為exists，（手冊中有說）。

大神們提到的semi-join ,是 in或not in子查詢的優化方式之一，除此還應該有：物化，下推等。這個semi-join應該是上拉。不是所有的in型子查詢，都會使用該優化方案的

---

將ID建一個表，然後使用any或者exist。這樣效率高很多。

---

電商網站難道不要上個search engine？search engine難道不就是干這個的？MySQL之類的東西難道不應該留給下單成交之類的transaction去用？

你隨便搞個Solr或者ES之類的東西幹這種事速度杠杠的，阿里雲那種爛機器都能給你飈到幾百qps。

搞不懂為什麼lz一定要找一個滾得夠快的方形輪子……

---

幾十萬條記錄，80M，索引只有主鍵id，in (幾百或幾千個id)，

這麼多人一本正經的在這兒討論性能問題，你們生活在80年代嗎？

---

哈哈，看來純mysql的優化器確實還是比較捉急的，在oracle里，這種情況一般用in謂詞條件，然後根據in後面的參數個數做執行計劃選擇，如果較短，會走in iterator，也就是每個表記錄進行if else判斷，要是深入想一下，這就是nested join，如果in後面參數較長，就會將in後面參數組看成一個虛擬表，然後進行hash join，大表間關聯一般都是走hash join，這種join主要消耗在構造hash bucket，所以前期準備cost比較大，所以只有大表關聯才可以容忍這種cost

---

沒人提到 以 left join 替代 in 的做法？

先把成百上千的id插入一個 臨時表t

再 select ...

from t as a

left join 原來的表 as b on a.id=b.id

where ...

---

樓上的都說的很好了，我也沒啥多說的。話說這種需求，還真得分下場景。

1. 要一味求快，那就使勁兒燒錢， 升級硬體，設計獨立的cache層。 單機永遠無限提升 SSD 神馬的不可能了。 因為你才80M， 一般千兆網卡 收發包，一秒也才16W個。嫩個破MySQL 就算你用長連接，SQL灰常小，你一秒頂天了也才支撐8W條SQL。（這個是真的，鄙人開發分散式資料庫的時候就測試過，就是路由節點轉發數據包是個瓶頸，在mysql cluster裡面就是那個SQL節點的）

2. 所以搞緩存。 要麼就 借鑒下CDN的思想，把數據放到使用者的旁邊，這樣還能再快。

3. 如果能夠提前知道需求更好了，提前做好，別人還沒說，就先把數據發過去。早上老闆來了就看到心儀的數據報表了。

4. 要考慮未來擴展的話，搞分散式嘛，線性擴展就可以了。

估計這種需求的SQL，一天查不了多少次。

另外 ，我很好奇，樓主，一次邀請我們這麼多人究竟想幹嘛。

這個世界只要有需求，有錢，送人上月球，去火星就不是問題。

---

好問題，關注。

黑名單（名單很大，幾千幾萬條）過濾之類的場景很常見。

同時問一下，如果是ES的話，可以優化嗎？

---

這個需求很像微博中首頁的需求啊，用redis解決吧

---

如果你有許可權的話，可以新建一張表，這張表裡存隨機ID，然後用inner join，join查詢會比in效率高不少。

---

繞過這個問題就好了嘛

---

我覺得還是有這類需求的，例如一個訂單1000個行向，每個行向需要根據商品編號查詢及時的庫存信息，用於訂單拆分或者巡倉。

如果要在一個事務內處理完業務邏輯，這裡的查詢還是批量的最好。

---

卧槽(#?Д?)，原來查詢都不用建立二級索引的。。。。

---

推薦閱讀：