探讨select in 在postgresql的效率问题
作者:db2zos 发布时间:2024-01-20 01:41:41
在知乎上看到这样一个问题:
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的效率问题,希望对大家有所帮助!
猜你喜欢
- 问题你想在一个消息传输层如 sockets 、multiprocessing connections 或 ZeroMQ 的基础之上实现一个简
- 编写思路:把本地文件在客户端通过base64编码以后发送目的地.测试过程中,上传文件过大,导致超时不成功.后来经过改善.把编码分段发送.测试
- 用phpMyAdmin时在导入和导出MySQL5数据时,有一个SQL compatibility mode选项,其可选值为NONE、ANSI
- 一、时间对象timetime模块使用的是C语言函数库中的函数。只能处理1970/1/1到2038/12/31之间的数据。1.测量运行时间方法
- 1.算法概述决策树算法是在已知各种情况发生概率的基础上,通过构成决策树来求取净现值的期望值大于等于零的概率,评价项目风险,判断其可行性的决策
- 本文实例讲述了Python实现的将文件每一列写入列表功能。分享给大家供大家参考,具体如下:# -*- coding: utf-8 -*-#!
- np.percentilenumpy.percentile(a, q, axis=None, out=None, overwrite_inp
- photoshop快捷键大全: 工具箱(多种工具共用一个快捷键的可同时按【Shift】加此快捷键选取)相关文章:网页设计软件FrontPag
- Python 操作文件时,我们一般要先判断指定的文件或目录是否存在,不然容易产生异常。1.文件# 是否存在import osos.path.
- 1、Matplotlib 简介Matplotlib 简介:Matplotlib 是一个python的 2D绘图库,它以各种硬拷贝格式和跨平台
- 首先来分析下需求,web程序后台需要认证,后台页面包含多个页面,最普通的方法就是为每个url添加认证,但是这样就需要每个每个绑定url的后台
- 本文实例讲述了Python栈的实现方法。分享给大家供大家参考,具体如下:Python实现栈栈的数组实现:利用python列表方法代码如下:#
- 一、装饰器decorator decorator设计模式允许动态地对现有的对象或函数包装以至于修改现有的职责和行为,简单地讲用来动态地扩展现
- 动态生成的IFRAME,设置SRC时的,不同位置带来的影响。以下所说的是在IE7下运行的。IE6下也是同样。在这个blog中,直接点击运行代
- JavaScript中没有Trim函数,VBScript语言中才有这个函数,就是去掉字符串头和尾的空格。您可以访问这篇文章:《增加 java
- 一、问题分析runtimeError: package fails to pass a sanity check解决方法如下:解决一:使用p
- 利用SocketServer模块来实现网络客户端与服务器并发连接非阻塞通信。首先,先了解下SocketServer模块中可供使用的类:Bas
- 经纬度坐标转换最常见办法就是调用第三方 API,例如百度、高德地图等服务平台,提供了相应的功能接口,它们的这类技术已经非常成熟啦,准确稳定,
- 将SQL Server中所有表的列信息显示出来: SELECT SysObjects.Name as tb_name, SysColumns
- 由于众所周知的原因,ACCESS在大型站点应用中都靠不上边,主要问题就是数据量大了以后几乎无法索引。当ACCESS里数据过万后,明显可以感觉