在知乎上看到这样一个问题:
MySQL 查询 select * from table where id in (几百或几千个 id) 如何提高效率?修改
电商网站,一个商品属性表,几十万条记录,80M,索引只有主键id,做这样的查询如何提高效率?
select * from table where id in (几百或几千个id)
这些id没啥规律,分散的。。。。
看了一下答案,感觉有好多不靠谱的,但是口说无凭,所以在我的电脑上写了几个查询测试一下。我用的是Postgresql9.4,但感觉mysql应该也差不多,首先创建一个简单表,只有简单的3列,在这个问题的下面好多人提到了需要看表的大小,其实这个问题和表大小无关,只和index的大小有关,因为是index是建立在int上的,所以只和纪录数目有关。
Table "public.t9" Column | Type | Modifiers --------+----------------+----------- c1 | integer | c2 | character(100) | c3 | character(200) | Indexes: "i1" UNIQUE, btree (c1)insert into t9 values(generate_series(1000,500000,1),repeat('a',90),repeat('b',180));
之后生成一些随机数,Mac上用jot,Linux上用shuf
for ((i=0;i<100000;i++)) do jot -r 1 1000 600000 rand.file done
然后根据rand.file 生成查询语句:
select * from t9 where c1 in ( 494613, 575087, 363588, 527650, 251670, 343456, 426858, 202886, 254037, ... 1 );
分别生成3个sql文件,in内变量的数目分别是100,1000和10000个,执行这3个sql文件,看看时间
try psql study -f test_100.sql -o /dev/null LOG: duration: 2.879 ms try psql study -f test_1000.sql -o /dev/null LOG: duration: 11.974 ms try psql study -f test_10000.sql -o /dev/null LOG: duration: 355.689 ms
可以看到只有在in内数据到了10,000个的时候数据时间会有比较大的变化,但也不过是在300多ms内完成。
那如果按照有些回答那样,先建一个临时表,然后用in subquery,并且希望这时候可以两表join呢?为了简单我直接用两表join了
drop table t_tmp; create table t_tmp(id int); insert into t_tmp (id) values (494613), (575087), (363588), (345980),... (1); select t9.* from t9, t_tmp where t9.c1 = t_tmp.id;
时间如何呢?
try psql study -f test_create_10000.sql -o /dev/null LOG: duration: 2.078 ms LOG: duration: 1.233 ms LOG: duration: 224.112 ms LOG: duration: 322.108 ms
除去drop和create的时间,依然花费了500+的时间,这里的前提还是我用的ssd盘,所以写LOG的时间会快很多。为什么会这么慢呢?用explain看一下,这时候数据量较大,直接走Merge join 了
那1000行数据的效率如何呢?
try psql study -f test_create_1000.sql -o exp.out LOG: duration: 2.476 ms LOG: duration: 0.967 ms LOG: duration: 2.391 ms LOG: duration: 8.780 ms
100行的数据如下:
try psql study -f test_create_100.sql -o /dev/null LOG: duration: 2.020 ms LOG: duration: 1.028 ms LOG: duration: 1.074 ms LOG: duration: 1.912 ms
可以看到在100个值和1000个值的情况下create table的方式不会比直接在in里面写所有的变量好多少,explain看的话是在用NLJ了。但在数据量更大(按照原问题,这里in的数量其实无法预知)的情况下效率只会更低,再加上额外的表维护成本和多余的SQL语句,DBA肯定不喜欢的,还是相信数据库,放心大胆直接用in list来搞定这些问题吧。
以上内容是针对select in 在postgresql的效率问题,希望对大家有所帮助!
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
稳了!魔兽国服回归的3条重磅消息!官宣时间再确认!
昨天有一位朋友在大神群里分享,自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址,com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后,确实是网易的网址,也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情,因为以前都没有出现这样的情况,现在突然提示跳转到国服战网的网址,是不是说明了简体中文客户端已经开始进行更新了呢?
更新日志
- 群星2013-青春缤纷辑压箱宝大公开3CD2[新加坡限量版][WAV整轨]
- 林育群.2013-BalladShow(日本版)【环球】【WAV+CUE】
- 陈加洛.1992-痛到感觉不到【宝丽金】【WAV+CUE】
- 群星.2023-宿命之敌电视剧原声带【韶愔音乐】【FLAC分轨】
- 東京事変-大発見[FLAC+CUE]
- 椎名林檎-三文ゴシップ[FLAC+CUE]
- 2024年08月04日
- 裘德《裘德「最后的水族馆」演唱会LIVE》[320K/MP3][228.89MB]
- 裘德《裘德「最后的水族馆」演唱会LIVE》[24bit 48kHz][FLAC/分轨][2.08G]
- 基因三重奏《如果你什么都不说 音乐会现场录音》[320K/MP3][145.37MB]
- 孟庭苇.1996-月亮说话(2020环球24KGOLD限量版)【上华】【WAV+CUE】
- 群星.1997-新艺宝优质音响系列·国语精选监听版【新艺宝】【WAV+CUE】
- 阿桑.2005-寂寞在唱歌(星外星引进版)【华研国际】【WAV+CUE】
- 基因三重奏《如果你什么都不说 音乐会现场录音》[FLAC/分轨][287.43MB]
- 蔡题谦《我爱你,却依然要看你走》[320K/MP3][88.65MB]