PgSQL·應用案例·驚天效能！單RDSPostgreSQL例項支撐2000億

db匠發表於2018-01-24

SQL

背景

20億使用者，每個使用者1000個標籤，基於任意標籤組合圈選、透視（業務上的需求是一次最多計算100個標籤的組合）。

相當於要處理2000億記錄。

1、實時求標籤組合的記錄數。（即滿足標籤組合的使用者有多少）

2、使用者ID。（級滿足標籤組合的使用者ID。）

要求實時響應。

通常你肯定會想，這個至少需要上百臺機器來支撐。

但是我要給你一個驚喜，這個資料量，一臺RDS PG例項即可。怎麼做呢？聽我道來，用最少的資源解決業務問題，用到RDS PG黑科技。

RDS PG 解決方案

方案如下：

《阿里雲RDS PostgreSQL varbitx實踐 – 流式標籤 (閱後即焚流式批量計算) – 萬億級，任意標籤圈人，毫秒響應》

優化方案，提高響應速度

1、bitmap切段

2、計算滿足條件的USER COUNT值時，平行計算(使用dblink非同步呼叫)

3、求使用者ID時，使用遊標，流式返回。

DEMO

1、需要用到的外掛

create extension dblink;  
create extension varbitx;

2、建立標籤表，切段，例如20億個使用者，切成400段，每一段5000萬個使用者BIT。

postgres=# create table t_bitmap (  
  tagid int,   -- 標籤ID  
  ofid int,    -- 偏移值, 乘以5000萬  
  v varbit     -- userid 位元  
);  
CREATE TABLE

3、建立索引(約束)

create unique index idx_t_bitmap_1 on t_bitmap (tagid, ofid);

4、建立1000個標籤的BITMAP資料，每一個標籤400條，每條的BIT長度為5000萬位。

postgres=# do language plpgsql $$  
declare v varbit := repeat(`1`,5000000)::varbit;  
begin  
  for i in 1..100 loop  
    for x in 0..399 loop  
      insert into t_bitmap values (i, x, v);                            
    end loop;  
  end loop;  
end;  
$$;  
  
  
DO  
Time: 150468.359 ms (02:30.468)

5、建立生成dblink連線的函式，重複建立不報錯。

create or replace function conn(  
  name,   -- dblink名字  
  text    -- 連線串,URL  
) returns void as $$    
declare    
begin    
  perform dblink_connect($1, $2);   
  return;    
exception when others then    
  return;    
end;    
$$ language plpgsql strict;

6、AND標籤組合的平行計算函式(dblink 非同步並行)，返回USERID透視數。

create or replace function get_bitcount_and(  
  and_tagids int[],   -- 輸入標籤ID陣列  
  v_bit int,          -- 求1或0的位元個數  
  conn text,          -- 連線串
  OUT cnt int8        -- 返回值, 多少個1或0  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   -- 生成400個連結，因為每行5000萬，20億個BIT，剛好400條。並LOOP  
  perform conn(`link`||i,  conn);   -- 連線  
  perform dblink_get_result(`link`||i);                        -- 消耗掉上一次非同步連線的結果，否則會報錯。  
    
  -- 傳送非同步DBLINK呼叫  
  -- 每次操作一個bit分段，返回BIT為0或1的位數  
  perform dblink_send_query(`link`||i, format(`select bit_count(bit_and(v), %s) from t_bitmap where tagid = any (%L) and ofid=%s`, v_bit, and_tagids, i));    
end loop;  
  
for i in 0..399 loop  
  -- 返回非同步呼叫結果，包括所有分段  
  return query SELECT * FROM dblink_get_result(`link`||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;

7、OR標籤組合的平行計算函式(dblink 非同步並行)，返回USERID透視數。

create or replace function get_bitcount_or(  
  or_tagids int[],   
  v_bit int,   
  conn text,          -- 連線串
  OUT cnt int8  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   
  perform conn(`link`||i,  conn);   
  perform dblink_get_result(`link`||i);  
  perform dblink_send_query(`link`||i, format(`select bit_count(bit_or(v), %s) from t_bitmap where tagid = any (%L) and ofid=%s`, v_bit, or_tagids, i));  
end loop;  
  
for i in 0..399 loop  
  return query SELECT * FROM dblink_get_result(`link`||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;

8、AND，OR 標籤組合的平行計算函式(dblink 非同步並行)，返回USERID透視數。

create or replace function get_bitcount_and_or(  
  and_tagids int[],   
  or_tagids int[],   
  v_bit int,   
  conn text,          -- 連線串
  OUT cnt int8  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   
  perform conn(`link`||i,  conn);   
  perform dblink_get_result(`link`||i);  
  perform dblink_send_query(`link`||i, format(`  
    with t1 as (select bit_and(v) b from t_bitmap where tagid = any (%L) and ofid=%s),   
         t2 as (select bit_or(v) b from t_bitmap where tagid = any (%L) and ofid=%s)   
    select bit_count(bitor(t1.b, t2.b), %s) from t1,t2`,   
    and_tagids, i, or_tagids, i, v_bit));  
end loop;  
  
for i in 0..399 loop  
  return query SELECT * FROM dblink_get_result(`link`||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;

-- 更復雜的QUERY，可以自行修改函式。實際業務中這種需求較少。  
-- (a and b andc or d) or (a and c) or (d and not b)..........

9、計數透視的效能如下，50個標籤組合，僅1.5秒，100個標籤組合，僅2.6秒：

我們統計2000億個user_tags組合（每個使用者一條記錄，每條記錄1000個標籤時的換算），僅僅需要2.6秒。

一個標籤：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,`dbname=postgres user=postgres`) cnt from generate_series(1,1) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 791.392 ms  
  
10個標籤組合：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,`dbname=postgres user=postgres`) cnt from generate_series(1,10) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 847.427 ms  
  
50個標籤組合：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,`dbname=postgres user=postgres`) cnt from generate_series(1,50) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 1478.847 ms (00:01.479)  
  
100個標籤組合：  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,`dbname=postgres user=postgres`) cnt from generate_series(1,100) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 2574.761 ms (00:02.575)

10、AND 、 OR組合效能如下，效能一樣：

postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,`dbname=postgres user=postgres`) cnt from generate_series(1,1) t(id)) t;  
 sum   
-----  
      
(1 row)  
  
Time: 854.934 ms  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,`dbname=postgres user=postgres`) cnt from generate_series(1,10) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 889.472 ms  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,`dbname=postgres user=postgres`) cnt from generate_series(1,50) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 1519.031 ms (00:01.519)  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,`dbname=postgres user=postgres`) cnt from generate_series(1,100) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 2597.701 ms (00:02.598)

11、求USERID，AND 函式如下，我們為了達到高速響應，使用遊標返回。

create or replace function get_pos_and(  
  and_tagids int[],    -- 標籤組合  
  v_bit int            -- 求1或0的BIT位，返回遊標，遊標包含ofid與位置下標（當然了，這個翻譯動作也可以交給程式，那麼返回BIT和ofid即可）  
) returns setof refcursor as $$  
declare  
  ref refcursor[];    -- 返回遊標陣列  
  res refcursor;      -- 返回遊標  
  sql text;           -- 遊標對應的SQL，即取USERID位置的SQL  
begin  
  for x in 1..400 loop   -- 生成400個遊標名  
    ref[x] := `cur`||x;  
  end loop;  
  
  for i in 0..399 loop   
    -- 使用0到399的偏移值， 乘以5000萬係數。  
      
    -- 賦予遊標名  
    res := ref[i+1];   
    -- 生成遊標對應的動態SQL(ofid, bit位置)，注意bit位置可以不翻譯，交給程式翻譯也沒問題。程式翻譯的話，翻譯好之後，再使用in查詢字典  
    -- select uid from uid_mapping where pos in (pos_array);  
    -- 1億，in 100萬, 380毫秒  
    -- [《HTAP資料庫 PostgreSQL 場景與效能測試之 25 - (OLTP) IN , EXISTS 查詢》](201711/20171107_26.md)    
    sql := format(`select %s, bit_posite(bit_and(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s`, i, v_bit, and_tagids, i);   
    -- 開啟遊標  
    open res for execute sql ;  
    -- 返回遊標  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;

12、求USERID，OR 函式如下，我們為了達到高速響應，使用遊標返回。

create or replace function get_pos_or(  
  or_tagids int[],   
  v_bit int   
) returns setof refcursor as $$  
declare  
  ref refcursor[];    
  res refcursor;   
  sql text;  
begin  
  for x in 1..400 loop  
    ref[x] := `cur`||x;  
  end loop;  
  
  for i in 0..399 loop   
    res := ref[i+1];   
    sql := format(`select %s, bit_posite(bit_or(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s`, i, v_bit, or_tagids, i);  
    open res for execute sql ;  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;

13、求USERID，AND OR 函式如下，我們為了達到高速響應，使用遊標返回。

create or replace function get_pos_and_or(  
  and_tagids int[],   
  or_tagids int[],   
  v_bit int   
) returns setof refcursor as $$  
declare  
  ref refcursor[];    
  res refcursor;   
  sql text;  
begin  
  for x in 1..400 loop  
    ref[x] := `cur`||x;  
  end loop;  
  
  for i in 0..399 loop   
    res := ref[i+1];   
    sql := format(`with t1 as  
                   (select bit_and(v) v from t_bitmap where tagid = any (%L) and ofid=%s),  
                   t2 as  
                   (select bit_or(v) v from t_bitmap where tagid = any (%L) and ofid=%s)  
                   select %s, bit_posite(bitor(t1.v, t2.v), %s, true) from t1,t2`,   
                  and_tagids, i, or_tagids, i, i, v_bit);  
    open res for execute sql ;  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;

14、求USERID例子，88毫秒響應，極端速度。

postgres=# begin;  
BEGIN  
Time: 0.031 ms  
postgres=# select * from get_pos_and_or(array[1,2,3], array[4,5,6], 1);  
 get_pos_and_or   
----------------  
 cur1  
 cur2  
 cur3  
 cur4  
 cur5  
 cur6  
 cur7  
 ....  
 cur399  
 cur400  
(400 rows)  
  
Time: 88.069 ms

獲取遊標值，5000萬ID，僅692毫秒：

fetch 1 from cur1;  
Time: 692.408 ms

15、如果我們把位置翻譯放到客戶端做，那麼只需要獲取結果BITMAP，那就更快了，224毫秒就可以獲取5000萬BIT走。這塊也能做成併發，每個客戶端獲取不同的ofid。

CREATE OR REPLACE FUNCTION public.get_pos_and(and_tagids integer[])
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
 STRICT
AS $function$
declare
  ref refcursor[];  
  res refcursor; 
  sql text;
begin
  for x in 1..400 loop
    ref[x] := `cur`||x;
  end loop;

  for i in 0..399 loop 
    res := ref[i+1]; 
    -- sql := format(`select %s, bit_posite(bit_and(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s`, i, v_bit, and_tagids, i);
    sql := format(`select %s, bit_and(v) from t_bitmap where tagid = any (%L) and ofid=%s`, i, and_tagids, i);
    open res for execute sql ;
    return next res;
  end loop;
end;
$function$;


postgres=# 	iming
Timing is on.
postgres=# begin;
BEGIN
Time: 0.045 ms
postgres=# select get_pos_and(array_agg(id)) from generate_series(1,100) t(id);
 get_pos_and 
-------------
 cur1
 cur2
 cur3
...
 cur397
 cur398
 cur399
 cur400
(400 rows)


fetch 1 from cur1;
Time: 224.776 ms

16、如果要求包含某標籤，但是不包含某標籤的使用者，同樣使用BIT操作即可。

例子:

包含b1，同時不包含b2的使用者

postgres=# select b1 & bitxor(b1,b2) from (values (bit`11001100`, bit`11000001`)) as t(b1,b2);
 ?column? 
----------
 00001100
(1 row)
  
使用這個方法，新增一個UDF即可。