隨機記錄併發查詢與更新(轉移、刪除)的”無恥”優化方法
背景
某張表有一批記錄，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=# iming
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
相關文章
- mysql 隨機查詢記錄MySql隨機
- Oracle查詢重複資料與刪除重複記錄方法Oracle
- 轉載：Oracle中查詢和刪除重複記錄方法簡介Oracle
- 查詢刪除表中重複記錄
- Oracle查詢重複資料與刪除重複記錄Oracle
- 查詢、刪除多個欄位相同的記錄
- 利用logminer查詢被刪除記錄的資訊
- MongoDB隨機查詢返回一條或N條記錄的方法MongoDB隨機
- 處理表重複記錄(查詢和刪除)
- win10更新失敗記錄怎麼刪除_win10更新失敗記錄刪除操作方法Win10
- mysql 跨表查詢、更新、刪除示例MySql
- 【探索】兩種查詢和刪除重複記錄的方法及其效能比較
- windows10更新記錄刪除_怎樣刪除win10更新歷史記錄WindowsWin10
- Oracle 查詢並刪除重複記錄的SQL語句OracleSQL
- 查詢優化與併發控制[姊妹篇.第六彈]優化
- GIS資料的查詢，插入，刪除，更新（ArcEngine）
- MySQL 隨機查詢資料與隨機更新資料實現程式碼MySql隨機
- 如何實現MySQL隨機查詢資料與MySQL隨機更新資料？MySql隨機
- SQL查詢優化的方法SQL優化
- SQL的資料庫操作：新增、更新、刪除、查詢SQL資料庫
- 隨機更新：補題記錄x隨機
- Mysql查詢調優記錄MySql
- 簡單查詢、插入、更新、刪除SQL語句SQL
- Oracle隨機抽取記錄的方法Oracle隨機
- mysql刪除查詢MySql
- oracle 快速刪除大批量資料方法（全部刪除，條件刪除，刪除大量重複記錄） 轉Oracle
- 【轉】oracle 快速刪除大批量資料方法（全部刪除，條件刪除，刪除大量重複記錄）Oracle
- 不要隨意刪除或者移動系統檔案目錄
- 回閃查詢查詢刪除的資料
- Yii 1.0資料庫操作 查詢、增加、更新、刪除資料庫
- 工作隨筆——mysql子查詢刪除原表資料MySql
- 為什麼刪除記錄表檔案不會減小？(記錄的插入與刪除在磁碟上的變化)
- mysql表刪除重複記錄方法MySql
- exists與in子查詢優化優化
- MySQL索引與查詢優化MySql索引優化
- 二叉查詢樹的插入刪除查詢
- 刪除檔案或目錄提示"檔案或目錄無法刪除"的解決方法！
- mysql 關聯更新刪除不走索引優化MySql索引優化