我們在寫sql的時候，經常用到in，in後面跟一堆常量列表，如id。有人說in的效率很高，而有人說很低；有人說in能使用索引，還有人說in不能使用索引。。。
到底是一個怎樣的情況呢？我們分析以下幾種情況
在這之前，我們先了解一下explain的幾種type型別（本次分析即參照type型別），按照效能從高到低：

const:表中的一個記錄的最大值能夠匹配這個查詢（索引可以是主鍵或惟一索引）。因為只有一行，這個值實際就是常數，因為MYSQL先讀這個值然後把它當做常數來對待

eq_ref:在連線中，MYSQL在查詢時，從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用

ref:這個連線型別只有在查詢使用了不是惟一或主鍵的鍵或者是這些型別的部分（比如，利用最左邊字首）時發生。對於之前的表的每一個行聯合，全部記錄都將從表中讀出。這個型別嚴重依賴於根據索引匹配的記錄多少—越少越好

range:這個連線型別使用索引返回一個範圍中的行，比如使用>或

index: 這個連線型別對前面的表中的每一個記錄聯合進行完全掃描（比ALL更好，因為索引一般小於表資料）

ALL:這個連線型別對於前面的每一個記錄聯合進行完全掃描，這一般比較糟糕，應該儘量避免

一，in後面只有1個值
1.1 對於主鍵或者唯一索引，那麼type=const，這種效能最高，表示表中只有1個記錄能滿足查詢

1.2 對於普通索引、或者聯合主鍵，type=ref

1.3 對於普通欄位，type=all，這種效能最差

二，in後面多餘1個值，但少於某一個值，這個值具體是多少，之後會揭曉。
這時，不管是主鍵，還是唯一索引，還是普通索引，type=range

但是在這裡需要注意的一個特例是：當你的索引的Cardinality屬性比較低時，type=all，意思就是這個索引的區分度很低，建立的意義不大，
這時他的執行計劃type=all，mysql認為走這個索引還不如全表掃描：

到底Cardinality處於什麼水平時，效能最好？一般認為這個值越接近count(*)，效能最好。而對於像性別這種欄位，就沒必要加索引了。

三，相對於第二種情況，當in後面的值多於某一個值，會導致掃描全表。這個經驗值我目前不能確定，諮詢過相關DBA，他們也不能給出其經驗值
3.1 test1表總共27條資料，當in後面的值少於8個時，type=range，而當超過8個時，type=all，如下：

3.2 這個t_word_cost表，總共有38244條資料，word_id上有索引，我測試了一把，當in後面不超過5千多或者6千多時，type=range，這是什麼意思？
因為令我不解的是，這個值還不確定，它是波動的，我執行了好多次，有時候是5千多，有時候是6千多，或者其他值

透過對test1、t_word_cost表進行測試，我確實沒有找出規律來，我原來妄想，透過大量實踐得出一個經驗值，然後透過經驗值來判斷到底in後面的個數佔count(*)百分比多少的時候，能走索引，看來我徒勞了。

既然不能得出經驗值，那我們只有在實際應用環境下具體選擇解決方案了。
譬如我這次操作t_word_cost表，in後面值的個數都超過count(*)了，如果一次性全部寫進in後面，一次查詢所耗時間是30-40s左右！
根據上面我分析的結果，貌似我應該選擇5000作為臨界值，然後分批、多次查詢，這樣效能應該最高。但實際情況是這樣嗎？
透過我在程式中不斷地人肉測試發現，並不是5000耗時最少，選擇2000或者2500時，總體耗時最少，至於為什麼，可能與記憶體、頻繁的資料庫連線有關吧，
因為我們知道，記憶體、IO都會影響整體效能，所以怎樣平衡這個度需要自己把握。

MySQL中in（常量列表）的執行計劃

相關文章