PostgreSQL隨機記錄返回-300倍提速實踐(隨機陣列下標代替orderbyrandom())

背景
在業務系統中，有些場景會用到隨機返回的功能，例如論壇，有很多帖子（比如有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》
轉自阿里雲德哥