PostgreSQL 随机记录返回 - 300倍提速实践 (随机数组下标代替order by random())-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

PostgreSQL 随机记录返回 - 300倍提速实践 (随机数组下标代替order by random())

简介: postgresql 数据库 随机排序

背景
在业务系统中,有些场景会用到随机返回的功能,例如论坛,有很多帖子(比如有100万贴),有些是精华帖(比如有5万贴),为了让精华帖可以均衡的被访问,需要将5万贴随机的分页返回给用户。

通常的做法是这样的

select xx from tbl where xx order by random() limit xx;
传统做法,由于需要随机排序,性能会比较差。

传统做法
1、建表

create table tbl_doc (id int primary key, info text, crt_time timestamp, tag int);
2、写入测试数据

tag=1表示精选帖子

insert into tbl_doc select id, md5(random()::text), clock_timestamp(), 0 from generate_series(1,1000000) t(id);
insert into tbl_doc select id, md5(random()::text), clock_timestamp(), 1 from generate_series(1000001,1050000) t(id);
3、测试

vi test1.sql

begin;
declare abc cursor for select * from tbl_doc where tag=1 order by random() ;
fetch 10 from abc;
end;
QPS不过200.

pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 64 -j 64 -T 120
progress: 1.0 s, 190.9 tps, lat 272.908 ms stddev 42.231
progress: 2.0 s, 212.1 tps, lat 284.494 ms stddev 44.371
progress: 3.0 s, 211.9 tps, lat 325.806 ms stddev 58.453
progress: 4.0 s, 172.0 tps, lat 340.460 ms stddev 71.527
优化方法 - 数组随机
周期性将帖子ID放入数组,查询时从数组取随机位置的ID,再从主表反查,避免排序。

1、创建一个存储周期性最新精选帖子的表(数组ID)。

create table tbl_hot_doc (id serial primary key, ids int[], crt_time timestamp);
通过这种方法写入当前最新帖子

insert into tbl_hot_doc (ids,crt_time)

  select array_agg(id), now() from tbl_doc where tag=1     

2、创建一个函数,用于自动的更新最新帖子(根据超时时间),同时返回游标,用户通过游标分页。

create or replace function get_ids (
ts_timeout interval, -- 超时时间
rands int, -- 返回随机多少条
refname name -- 游标名
) returns refcursor as

$$ declare len int; -- 数组长度,有多少精华帖 hotid int; -- tbl_hot_id表的最新状态ID ts timestamp; -- 最新状态的时间 hotids int[]; -- 最新状态的数组 res refcursor := refname; -- 游标名 begin -- 获取最新状态 select array_length(ids,1),id,crt_time,ids into len,hotid,ts,hotids from tbl_hot_doc order by id desc limit 1; -- 如果没有最新状态,或者最新状态已超时 if now()-ts >= ts_timeout or not found then -- 如果不存在则设置HOTID=0 if not found then hotid := 0; end if; -- 使用ad lock抢锁,只有一个会话拿锁,从原始表tbl_doc生成最新数据并写入tbl_hot_doc if pg_try_advisory_xact_lock(hotid) then insert into tbl_hot_doc (ids,crt_time) select array_agg(id), now() from tbl_doc where tag=1 returning array_length(ids,1),id,crt_time,ids into len,hotid,ts,hotids; -- 使用这条SQL,用数组下标随机,代替之前的order by random() open res for select * from tbl_doc where id in (select hotids[(random()*(len-1))::int+1] from generate_series(1,rands)); return res; end if; end if; open res for select * from tbl_doc where id in (select hotids[(random()*(len-1))::int+1] from generate_series(1,rands)); return res; end; $$

language plpgsql strict;
3、优化后的性能测试

vi test.sql
begin;
select * from get_ids(interval '5 min', 1000, 'abc');
fetch 10 from abc;
end;
4、QPS上到6万,提升了300倍。

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120
progress: 1.0 s, 57260.4 tps, lat 1.045 ms stddev 0.211
progress: 2.0 s, 60444.9 tps, lat 1.059 ms stddev 0.050
progress: 3.0 s, 60467.5 tps, lat 1.058 ms stddev 0.045
progress: 4.0 s, 60520.2 tps, lat 1.057 ms stddev 0.039
progress: 5.0 s, 60561.8 tps, lat 1.057 ms stddev 0.045
5、如果需要在修改tbl_doc原始数据后,立即反映(不等查询get_ids输入的超时参数),实时更新tbl_hot_doc的值,可以采用类似如下方法

begin;
update tbl_doc set tag=0 where id=?;
update tbl_hot_doc set ids=array_remove(ids,?) where id=(select max(id) from tbl_hot_doc) and ids<>array_remove(ids,?);
end;
小结
使用本文提供的方法,使得随机数据的查询,性能提升了300倍。

其他方法
还有一些方法,例如将满足条件的数据使用独立分区来存储,然后使用采样的方法来避免排序,但是精确度和性能还是没有办法与先前的方法相比。

postgres=# select * from tbl_doc tablesample BERNOULLI (0.1) where tag=1 limit 10;

id info crt_time tag
1000398 7f232df084bf24a71ccaac9f496639bb 2018-10-09 23:32:45.761598 1
1000806 97095bdfdd0ef1d209515728a078ffc3 2018-10-09 23:32:45.762841 1
1001949 d7733a8b4bc5b5b26bb147fab3cc62d6 2018-10-09 23:32:45.766116 1
1002227 1feb7e972b7206a0ab909cee0a07e41d 2018-10-09 23:32:45.766917 1
1003609 60223d7a74c51cde4a8b017bf9a3e712 2018-10-09 23:32:45.770809 1
1006387 89c9cb8b4dbd46b3e0c0606e7e1e6947 2018-10-09 23:32:45.778873 1
1006533 f57808f0f15140bbc5429929a4b8d4b5 2018-10-09 23:32:45.779258 1
1007279 03fedf609f2e85658a98195daabac2b1 2018-10-09 23:32:45.781323 1
1007880 298bee6992ca255cbc8d0a299f00166a 2018-10-09 23:32:45.783055 1
1008073 bf1b8354692cde77765babc56a9f06a2 2018-10-09 23:32:45.783605 1

(10 rows)

Time: 11.125 ms
分区表时,块级采样无法满足业务需求,因为随机性不够。而非分区时,则引入了IO放大并且可能访问不到足够的满足条件的记录。

《PostgreSQL 任意列组合条件 行数估算 实践 - 采样估算》

《秒级任意维度分析1TB级大表 - 通过采样估值满足高效TOP N等统计分析需求》

《PostgreSQL Oracle 兼容性 之 - 数据采样与脱敏》

《PostgreSQL 巧妙的数据采样方法》

《PostgreSQL 9.5 new feature - table | mview data sample》
转自阿里云德哥

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章