問題描述

看到網上的資料說：

in 和 not in 也要慎用，否則會導致全表掃描，如：select id from t where num in(1,2,3)對于連續的數值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

問題解答

連續數值當然between好了 減少解析 并且in的范圍默認超過一定數目就會走全表 9個還是多少忘了

針對樓下評論再補充一下答案：in走全表是分情況的，上面的解答只是憑記憶大概說了一下，樓下小伙伴比較認真，這里就再解釋一下，這個不是個數而應該是個比例，大概25%-35%左右，你要再問到底多少不好意思水平有限不讀源碼沒辦法確認。然后這個30左右的比例也并不是說一定走全表掃描，因為mysql還有一個索引掃描，就是說如果select的內容在你的索引里面就能找到的話當然不會去掃全表了，比如下面的例子中select id from ttt where id in (..);和select * from ttt where id in (...);前面這個肯定是走主鍵掃描，即使你in了所有id值他也是走主鍵，而后面的情況就是這種百分比的情況了，具體看下面示例，歡迎指正^_^額，然后還想說一下between的情況，為什么好是因為除了索引段上連續存取減少解析以外，還有一個情況就是在磁盤尋址檢索數據的時候，會默認讀取第一次取值附近的部分數據（有這么一個概率算法說的是當一個數據被檢索到的時候，他附近的數據也很大概率會被用到）所以就有了這么一個一次性取出冗余數據避免多次尋址的情況，這時候使用between的連續取值就正適用了

mysql> select * from ttt;+----+-------------------+| id | name |+----+-------------------+| 1 | I17021234001 || 2 | IC17031234002 || 3 | C17041234003 || 4 | IAsEw1234001 || 5 | I17021234001A2 || 6 | IC17031234002A2 || 7 | C17041234003A2 || 8 | IAsEw1234001A2 || 9 | I17021234001A2 || 10 | IC17031234002A2 || 11 | C17041234003A2 || 12 | IAsEw1234001A2 || 13 | I17021234001A2A2 || 14 | IC17031234002A2A2 || 15 | C17041234003A2A2 || 16 | IAsEw1234001A2A2 || 17 | I17021234001A2 || 18 | IC17031234002A2 || 19 | C17041234003A2 || 20 | IAsEw1234001A2 |+----+-------------------+20 rows in set (0.00 sec)mysql> show create table ttt;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ttt | CREATE TABLE `ttt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(32) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> explain select * from ttt where id in (1,2,3,4,5,6);+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | ttt | ALL | PRIMARY | NULL | NULL | NULL | 20 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from ttt where id in (1,2,3,4,5);+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | ttt | range | PRIMARY | PRIMARY | 4 | NULL | 5 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from ttt where id in (1,2,3);+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | ttt | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from ttt where id in (1,2,3,4,5,6,7);+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | ttt | ALL | PRIMARY | NULL | NULL | NULL | 20 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select id from ttt where id in (1,2,3,4,5,6,7);+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+| 1 | SIMPLE | ttt | index | PRIMARY | PRIMARY | 4 | NULL | 20 | Using where; Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+1 row in set (0.00 sec)mysql> explain select name from ttt where id in (1,2,3,4,5,6,7);+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | ttt | ALL | PRIMARY | NULL | NULL | NULL | 20 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)回答2：

根據數據庫中B樹索引的存儲結構，在葉節點存儲指向數據的物理地址，而這個物理地址當存在聚簇索引時是有序的。

如果是連續數值，between在找到第一個匹配值后，則直接從該地址往后搜索，直到最后一個元素為止。這樣就不會對后續值進行索引掃描，因此速度快了。對于in操作，不大清楚，但是估計應該會對全索引進行掃描吧。回答3：

EXPLAIN mysql 語句 看下輸出

使用between時只需要匹配上下界，故而會快一點；in每個都要看一遍，會造成全表掃描。