国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

連續(xù)數(shù)值當(dāng)然between好了 減少解析 并且in的范圍默認(rèn)超過一定數(shù)目就會走全表 9個(gè)還是多少忘了

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

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)

根據(jù)數(shù)據(jù)庫中B樹索引的存儲結(jié)構(gòu)，在葉節(jié)點(diǎn)存儲指向數(shù)據(jù)的物理地址，而這個(gè)物理地址當(dāng)存在聚簇索引時(shí)是有序的。

如果是連續(xù)數(shù)值，between在找到第一個(gè)匹配值后，則直接從該地址往后搜索，直到最后一個(gè)元素為止。這樣就不會對后續(xù)值進(jìn)行索引掃描，因此速度快了。

對于in操作，不大清楚，但是估計(jì)應(yīng)該會對全索引進(jìn)行掃描吧。

EXPLAIN mysql 語句 看下輸出

使用between時(shí)只需要匹配上下界，故而會快一點(diǎn)；in每個(gè)都要看一遍，會造成全表掃描。