色综合图-色综合图片-色综合图片二区150p-色综合图区-玖玖国产精品视频-玖玖香蕉视频

您的位置:首頁技術文章
文章詳情頁

mysql - sql 優化問題,between比in好?

瀏覽:144日期:2022-06-12 14:22:11

問題描述

看到網上的資料說:

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

問題解答

回答1:

連續數值當然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操作,不大清楚,但是估計應該會對全索引進行掃描吧?;卮?:

EXPLAIN mysql 語句 看下輸出

回答4:

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

主站蜘蛛池模板: 国产精品久久久久影视不卡 | 精品国产成人综合久久小说 | 国产精品日韩欧美一区二区三区 | 亚洲成人在线播放 | 久久久久18 | 午夜在线视频一区二区三区 | 亚洲精品久久久久中文 | 亚洲一区网站 | 免费一级毛片在线播放放视频 | 色女生影院 | 欧美综合在线观看 | 欧美成人亚洲高清在线观看 | 欧美精品久久天天躁 | 精品欧美高清不卡在线 | 香港激情黄三级在线视频 | 欧美视频精品在线 | 欧美一级二级毛片视频 | 国产精品夫妇久久 | 国产一区二区三区在线观看视频 | 一级成人黄色片 | 九九热视频在线免费观看 | 精品久久中文字幕有码 | 中文字幕一二区 | 欧美一区二区精品系列在线观看 | 久久亚洲精品中文字幕二区 | 性欧美videos俄罗斯 | 亚洲综合在线视频 | 久久福利资源站免费观看i 久久高清精品 | 热e国产| 刺激一区仑乱 | 久久两性视频 | 久久99热精品免费观看k影院 | 亚洲国产福利精品一区二区 | 日日干日日操日日射 | 色资源二区在线视频 | 久久毛片网站 | 成年美女黄网站小视频 | 国产成人精品福利网站人 | 免费视频网站一级人爱视频 | 456主播喷水在线观看 | 视频二区精品中文字幕 |