mysql - sql 最佳化問題，between比in好？
看到網路上的資料說：
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看到網路上的資料說：
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
阿神2017-05-18 10:58:50
連續數值當然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)阿神2017-05-18 10:58:50
根據資料庫中B樹索引的儲存結構，在葉節點儲存指向資料的實體位址，而這個物理位址當存在聚集索引時是有序的。
如果是连续数值，between在找到第一个匹配值后，则直接从该地址往后搜索，直到最后一个元素为止。这样就不会对后续值进行索引扫描，因此速度快了。
对于in操作，不大清楚，但是估计应该会对全索引进行扫描吧。
