标签
PostgreSQL , 数组 , 随机 , order by random()
背景
在业务系统中，有些场景会用到随机返回的功能，例如论坛，有很多帖子（比如有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 9.5 new feature - table | mview data sample》
