随机记录并发查询与更新(转移、删除)的"无耻"优化方法

背景      

某张表有一批记录，A用户说，这批记录是我要的，但是我只要一条，B用户也说，这批记录是我要的，我也只要一条。

是不是有点像一群男人去逛怡红院，妹子们都是目标，但是今晚只要一位，至于是谁暂时还不确定，虽然不需要抢，但是得锁单。

被动分配式，等着妈妈给你分一个。

主动挑选式，主动到姑娘们群里挑，就涉及到锁单的问题了，一个妹子只能陪一位公子哦。  

上面的例子可能不太适合未成年人，那么看看另一个形象的比喻，某处有一堆砖块，每块砖头都有一个唯一编号，然后一群小伙伴同时来取砖块（每人每次取1块），要求每个小伙伴拿到的砖块ID是随机的，并且要求以最快的方式将砖块取完。

这次真的来搬砖了，来比一比谁的搬砖能力强吧。

我们将问题转化一下，一块砖一个ID，作为一条记录存入数据库，假设我们有1000万块砖。有128个小伙伴同时来搬砖，怎么能以最快的速度，随机的把砖搬完呢？

这个场景实际上有一个来头，某个群红包口令业务，由于该业务没有对接账务系统，没有用户ID也没有用户手机号，所以没法将领红包的资格做判定，为了防止任何人都能猜测口令的方式来领取红包，搞了一个批量生成随机口令的方法，发红包的时候从数据库取走一条(随机口令)。既要考虑随机，又要考虑用户体验，所以选择了8位数值（比较容易猜测），然后又要考虑高并发的发红包场景，所以还要求取值快。

优化方法1

理解了需求后，我们看看如何优化？

考虑随机、并发还不够，因为数据要取走（转移到一个已消耗的表中），因此还需要考虑数据的收缩。

比如PostgreSQL的堆表，末端的空数据块是可以被回收的，那么我们在设计的时候，如果能从末端开始取，是最好的。

1. 插入时就让数据随机，而不是取时随机。

创建测试表, 存放一堆唯一值.

postgres=# create table tbl (id int);
CREATE TABLE

唯一值随机插入, 取数据时按照数据块倒序取出, 这么做的好处是vacuum时可以直接回收这部分空间.

postgres=# select * from generate_series(1,10) order by random();
 generate_series
-----------------
               1
               9
               4
               7
               3
               6
               8
               2
              10
               5
(10 rows)
postgres=# \timing
Timing is on.

随机的插入1000万数据

postgres=# insert into tbl select * from generate_series(1,10000000) order by random();
INSERT 0 10000000
Time: 42204.425 ms

从数据来看 , 已经随机插入了.

postgres=# select * from tbl limit 10;
   id
---------
 9318426
 4366165
 4661718
 8491396
 9413591
 9845650
 8830805
  999712
 7944907
 2487468
(10 rows)

在ctid(行号)上创建索引, 取数据时使用这个索引, 倒序从最后的数据块开始取数据.

postgres=# create index idx_tbl_ctid on tbl(ctid);
CREATE INDEX
Time: 18824.496 ms    

9.x开始不支持对系统列创建索引，所以我们可以增加一个自增主键    

drop table tbl;
create table tbl (pk serial8 primary key, id int);
insert into tbl (id) select * from generate_series(1,10000000) order by random();

例如:

postgres=# select ctid,* from tbl order by pk desc limit 5;
    ctid    |    pk    |   id
------------+----------+---------
 (54054,10) | 10000000 | 2168083
 (54054,9)  |  9999999 | 5812175
 (54054,8)  |  9999998 | 1650372
 (54054,7)  |  9999997 | 2443217
 (54054,6)  |  9999996 | 3002493
(5 rows)

为了防止多个进程重复取数据, 使用这种方法.

postgres=# with t as(select pk from tbl order by pk desc limit 5) delete from tbl where pk in (select pk from t) returning *;
    pk    |   id
----------+---------
  9999997 | 2443217
  9999999 | 5812175
 10000000 | 2168083
  9999996 | 3002493
  9999998 | 1650372
(5 rows)    

DELETE 5

测试并行取数据.

测试方法, 将数据插入另一张表，表示数据从一张表搬运到另一张表。

create table test(like tbl);    

postgres=#  with t as(select pk from tbl order by pk desc limit 5), t1 as (delete from tbl where pk in (select pk from t) returning *) insert into test select * from t1 returning * ;
   pk    |   id
---------+---------
 9999993 | 5893249
 9999995 | 6079644
 9999994 | 1834403
 9999992 | 3511813
 9999991 | 7078819
(5 rows)    

INSERT 0 5
postgres=# select * from test;
   pk    |   id
---------+---------
 9999993 | 5893249
 9999995 | 6079644
 9999994 | 1834403
 9999992 | 3511813
 9999991 | 7078819
(5 rows)

使用pgbench 测试, 16个并行取数据进程, 每次取5条.

postgres@localhost-> vi test.sql
with t as(select pk from tbl order by pk desc limit 5),t1 as (delete from tbl where pk in (select pk from t) returning *) insert into test select * from t1 returning * ;

测试完成后, 查询test表, 看看有没有重复数据就知道这种方法是否靠谱了.

性能见下 :

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 30 s
number of transactions actually processed: 1053020
latency average = 1.819 ms
latency stddev = 1.126 ms
tps = 35083.102896 (including connections establishing)
tps = 35149.046180 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         1.821  with t as(select pk from tbl order by pk desc limit 5),t1 as (delete from tbl where pk in (select pk from t) returning *) insert into test select * from t1 returning * ;

经查没有重复数据, 方法靠谱，搬砖成功

postgres=# select count(*),count(distinct id) from test;
 count  | count
--------+--------
 143400 | 143400
(1 row)

以上方法数据是从堆表的末端开始搬运的，所以表会随着搬运，autovacuum使之变小。

但是实际上，以上QUERY有一个问题，select没有加锁，在delete时，可能已经被其他并发进程搬走了。竞争的问题也被掩盖了。

为了改善这个问题，比如要求每次请求，必须搬走1块砖。那么需要加LIMIT 1 for update skip locked，这样能解决竞争的问题，但是无法解决重复扫描的问题。

我们先看看效果

postgres@localhost-> vi test.sql
with t as(select pk from tbl order by pk desc limit 1 for update skip locked), t1 as (delete from tbl where pk in (select pk from t) returning *) insert into test select * from t1 returning * ;    

$ pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 30
progress: 1.0 s, 4646.7 tps, lat 12.035 ms stddev 32.066
progress: 2.0 s, 4106.0 tps, lat 15.782 ms stddev 40.525
progress: 3.0 s, 4173.0 tps, lat 15.440 ms stddev 37.953
progress: 4.0 s, 4077.0 tps, lat 15.336 ms stddev 38.641
progress: 5.0 s, 4138.0 tps, lat 15.869 ms stddev 41.051
progress: 6.0 s, 4173.0 tps, lat 14.902 ms stddev 41.100
progress: 7.0 s, 4189.9 tps, lat 15.673 ms stddev 41.540

64个搬运工，每秒只能搬运4000条左右。

因为他们中最差的那个询问了64块砖才拿到搬运这块砖头的所有权，只有第一个人，询问了1块砖就拿到了所有权。

那么怎么优化呢? 如何让每个搬运工每次拿到的砖头ID都是随机的，并且没人和他抢。

优化方法2

如何拿到随机的记录是关键，PostgreSQL提供了一个随机访问接口tablesample，通过这个接口，可以随机访问数据（提供一个百分比1-100即可），注意随机访问的数据是在where过滤条件前，所以百分比太小的话，你可能会访问不到任何数据。

目前支持两种随机采样方法，1. system，按块随机(整个数据块的记录被取出)；2. BERNOULLI扫全表，按百分比返回随机记录；因此BERNOULLI比SYSTEM随机度更精准，但是SYSTEM的效率更高。

create or replace function exchange_t(i_limit int8, sample_ratio real) returns setof tbl as $$
declare
  -- 总共搬几块砖
  res_cnt int8 := i_limit;    

  -- 抢到的砖块ID
  pk_arr int8[];    

  -- 这次搬了几块(极少情况, 可能有一些被别抢去了)
  tmp_cnt int8;    

  -- 最多循环次数
  max_cnt int := 16;
begin
  loop
    -- 无耻的搬砖优化，通过PostgreSQL采样接口，随机取砖头
    select array_agg(pk) into pk_arr from (select pk from tbl TABLESAMPLE SYSTEM (sample_ratio) limit res_cnt) t ;
    -- 或者 select array_agg(pk) into pk_arr from (select pk from tbl TABLESAMPLE BERNOULLI (sample_ratio) limit res_cnt) t ;    

    if found then
      -- 搬砖，并返回已搬走的砖头ID
      return query with tmp as (delete from tbl where pk = any (pk_arr) returning *) insert into test select * from tmp returning *;    

      -- 这次搬了几块砖，还需要搬几块
      GET DIAGNOSTICS tmp_cnt = ROW_COUNT;
      -- raise notice 'tmp_cnt: %', tmp_cnt;
      res_cnt := res_cnt - tmp_cnt;
      -- raise notice 'res_cnt: %', res_cnt;
    end if;    

    -- 如果搬完，返回
    if (res_cnt <= 0) then
      return;
    end if;    

    -- 防止无限循环
    max_cnt := max_cnt - 1;
    if (max_cnt <=0 ) then
      return;
    end if;    

  end loop;
end;
$$ language plpgsql strict;    

postgres=# select * from exchange_t(5, 0.1);
NOTICE:  tmp_cnt: 5
NOTICE:  res_cnt: 0
 pk  |   id
-----+---------
  49 | 1035771
  51 | 7966506
  57 | 5967428
  91 | 7405759
 120 | 7764840
(5 rows)

压测

搬砖性能从4000提升到了将近9万。

vi test.sql
select * from exchange_t(1, 0.1);    

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 30    

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 30 s
number of transactions actually processed: 2677383
latency average = 0.714 ms
latency stddev = 2.607 ms
tps = 89200.726564 (including connections establishing)
tps = 89417.041119 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.717  select * from exchange_t(1, 0.1);

场景2

除了这个搬砖场景，还有一些其他场景也能使用类似方法，感谢万能的PostgreSQL。

比如有一个场景初始化了一批账号ID，初始ID=0，每次有用户来注册时，将ID=0的记录修改为此次注册的用户ID，相当于消耗一条ID=0的记录。

使用采样的方法可以优化这个场景，不过别急着套用，因为数据采样是在过滤条件之前发生的，所以当所有数据范围都是我们的目标数据是没问题的，但是如果你把目标数据和非目标数据混到一起，这种采样的方法就可能导致冗余扫描，如果采样比例低，甚至找不到目标数据。因此前面的搬砖场景，我们每次都把数据搬走，剩余的所有数据依旧是目标数据，所以不存在问题。

那么了解了以上原理之后，第二个场景，我们也采样转移法，即申请ID的时候，将数据转移走，而不仅仅是UPDATE ID=NEWID的做法。

例子

初始表
create table tbl1(pk serial8 primary key, id int, info text, crt_time timestamp, mod_time timestamp);    

转移表
create table tbl2(like tbl1);    

初始数据1000万
insert into tbl1 (id, info, crt_time) select 0, 'test', now() from generate_series(1,10000000);

函数

create or replace function exchange_t(i_limit int8, sample_ratio real, i_id int, i_mod_time timestamp) returns setof tbl2 as $$
declare
  -- 总共搬几块砖
  res_cnt int8 := i_limit;    

  -- 抢到的砖块ID
  pk_arr int8[];    

  -- 这次搬了几块(极少情况, 可能有一些被别抢去了)
  tmp_cnt int8;    

  -- 最多循环次数
  max_cnt int := 16;
begin
  loop
    -- 无耻的搬砖优化，通过PostgreSQL采样接口，随机取砖头
    select array_agg(pk) into pk_arr from (select pk from tbl1 TABLESAMPLE SYSTEM (sample_ratio) limit res_cnt) t ;
    -- 或者 select array_agg(pk) into pk_arr from (select pk from tbl1 TABLESAMPLE BERNOULLI (sample_ratio) limit res_cnt) t ;    

    if found then
      -- 搬砖，并返回已搬走的砖头ID
      return query with tmp as (delete from tbl1 where pk = any (pk_arr) returning pk,info,crt_time) insert into tbl2(pk,id,info,crt_time,mod_time) select pk,i_id,info,crt_time,i_mod_time from tmp returning *;    

      -- 这次搬了几块砖，还需要搬几块
      GET DIAGNOSTICS tmp_cnt = ROW_COUNT;
      -- raise notice 'tmp_cnt: %', tmp_cnt;
      res_cnt := res_cnt - tmp_cnt;
      -- raise notice 'res_cnt: %', res_cnt;
    end if;    

    -- 如果搬完，返回
    if (res_cnt <= 0) then
      return;
    end if;    

    -- 防止无限循环
    max_cnt := max_cnt - 1;
    if (max_cnt <=0 ) then
      return;
    end if;    

  end loop;
end;
$$ language plpgsql strict;

测试

postgres=# select exchange_t(1,0.1,10,now());
                                exchange_t
---------------------------------------------------------------------------
 (360129,10,test,"2017-03-03 16:48:58.86919","2017-03-03 16:51:13.969138")
(1 row)    

Time: 0.724 ms
postgres=# select count(*) from tbl1;
  count
---------
 9999997
(1 row)    

Time: 859.980 ms
postgres=# select count(*) from tbl2;
 count
-------
     3
(1 row)    

Time: 0.420 ms

压测

vi test.sql    

\set id random(1,10000000)
select * from exchange_t(1::int8, 0.1::real, :id, now()::timestamp);    

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 30    

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 30 s
number of transactions actually processed: 2970824
latency average = 0.644 ms
latency stddev = 0.348 ms
tps = 98599.587185 (including connections establishing)
tps = 98791.348808 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.001  \set id random(1,10000000)
         0.644  select * from exchange_t(1::int8, 0.1::real, :id, now()::timestamp);

每秒转移9.8万记录，采样法消除冲突后性能惊人。

postgres=# select count(*) from tbl1;
  count
---------
 7029173
(1 row)    

postgres=# select count(*) from tbl2;
  count
---------
 2970827
(1 row)    

postgres=# select * from tbl2 limit 10;
   pk   |   id    | info |         crt_time          |          mod_time
--------+---------+------+---------------------------+----------------------------
 329257 |      10 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:51:01.261172
 107713 |      10 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:51:08.012152
 360129 |      10 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:51:13.969138
  61065 | 7513722 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.669893
  95337 | 4101700 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.672948
 124441 | 7159045 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.673335
  87041 | 1868904 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.671536
 126617 | 4055074 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.673654
  10201 | 3790061 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.673959
 191081 | 6663554 | test | 2017-03-03 16:48:58.86919 | 2017-03-03 16:52:44.674014
(10 rows)

小结

1. 为了解决高并发的数据随机访问、更新、转移等热点与扫描相似悖论的问题，PostgreSQL 采样接口打开一种很"无耻"的优化之门，让小伙伴们可以开足并发，卯足玛丽开搞。

为什么一个蛋糕，大家都要从一处抢呢，围成一圈，每人在各自的方向挖一勺不是更好么？就好像小时候长辈较我们夹菜，要夹靠近自己这一边的一样。

参考

https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html

https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation

时间： 2024-08-03 07:31:51

随机记录并发查询与更新(转移、删除)的"无耻"优化方法的相关文章

Linq To Xml学习 － 3.查询、更新、删除

Linq To Xml学习 - 3.查询.更新.删除 文章最后有该示例的XML文档. 查找具有特定属性的元素 XElement root = XElement.Load ("PurchaseOrder.xml"); IEnumerable address = from el in root.Elements("Address") where (string)el.Attribute("Type") == "Billing" s

返璞归真 asp.net mvc (1) - 添加、查询、更新和删除的 Demo

原文:返璞归真 asp.net mvc (1) - 添加.查询.更新和删除的 Demo[索引页][源码下载] 返璞归真 asp.net mvc (1) - 添加.查询.更新和删除的 Demo 作者:webabcd 介绍 以Northwind为示例数据库,使用asp.net mvc 1.0实现添加操作.查询操作.更新操作和删除操作 示例 1.Model(使用ADO.NET Entity Framework做ORM) CategorySystem.cs(业务逻辑) using System;usin

asp.net在向表中插入记录和dataliast的更新和删除的代码搞不定了

问题描述 asp.net在向表中插入记录和dataliast的更新和删除的代码搞不定了求救

一张表有且只有一条记录(续) - 支持插入，并且查询、更新、删除只作用在最后一条记录上

标签 PostgreSQL , 有且只有一条记录 背景 之前写过一篇文档,介绍如何控制某张表有且只有一条记录. <如何实现一张表有且只有一条记录 implement PostgreSQL table have one and only one row> 接下来这个需求与之类似,一张表好像有且只有一条记录,要求这样: 1.支持插入.更新.删除.查询操作, 2.有一个时间字段用来区分这条记录是什么时候插入.更新的. 3.更新只作用在最后一条记录(时间最大的那条)上, 4.查询只返回时间最大的一条记

LINQ to SQL之面向对象的添加、查询、更新和删除

介绍 以Northwind为示例数据库,DLINQ(LINQ to SQL)之完全面向对象的添加操作.查询操作.更新操作和删除操作 示例 Sample.aspx <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile= "Sample.aspx.cs" Inherits="LINQ_DLINQ_S

mysql 跨表查询、更新、删除示例_Mysql

下面来谈谈跨表插入,更新和删除 首先讨论的是跨表查询: insert into `table_A` select * from `table_B`;注意*代表全部插入. 接着又讨论关于跨表更新 复制代码 代码如下: update `table_A`, `table_B` set `table_A`.`name` = `table_B`.`name` where `table_A`.`id` = `table_B`.`id`;

用PostgreSQL支持含有更新，删除，插入的实时流式计算

大多数的流式计算产品只支持APPEND ONLY的应用场景,也就是只有插入,没有更新和删除操作.如果要实现更新和删除的实时流式计算,在PostgreSQL中可以这样来实现.在此前你可以阅读我以前写的文章来了解PG是如何处理一天一万亿的实时流式计算的:https://yq.aliyun.com/articles/166 要支持更新和删除,思路是这样的,加一张前置表,这个前置表的某个字段用来记录字段的最终状态,即到达这个状态后,记录不会被更新或删除.通过触发器来控制什么记录插入到流中同时从前置表删除

php 随机记录mysql rand()造成CPU 100%的解决办法_php技巧

百度查阅了一些资料,再结合自己的一些经验,采用以下解决办法: 复制代码 代码如下: $idlist=''; for($i=1;$i<=20;$i++){ if($i==1){ $idlist=mt_rand(3,25216); } else{ $idlist=$idlist.','.mt_rand(3,25216); } } $query="select * from table where id in ($idlist) LIMIT 0,10"; 原理其实很简单,就是产生一组随

php 中用mysql rand()取随机记录造成CPU 100%的解决办法

mysql数据库有10几万条数据,使用rand()提取随机10条记录,导致服务器cpu占用居高不下直至死机~ 百度查阅了一些资料,再结合自己的一些经验,采用以下解决办法: $idlist=''; for($i=1;$i<=20;$i++){ if($i==1){ $idlist=mt_rand(3,25216); } else{ $idlist=$idlist.','.mt_rand(3,25216); } } $query="select * from table where id in