PostgreSQL 随机查询优化
背景
在某些场景可能会需要随机的从表中取出记录。方法比较多，简单的方法可能给数据库带来巨大的开销，下面开始举例说明，看看如何优化一个随机查询。
测试表:
create table tbl_user(id serial8 primary key,firstname varchar(32),lastname varchar(32),corp varchar(32),age smallint);
ID列被作为优化随机查询的选择列.唯一，有索引，非空是比较好的选择.
测试记录:
insert into tbl_user select generate_series(1,999999),'zhou','digoal','sky-mobi',27;
随机查询方法举例:
方法 1. 最简单的随机查询，查询出1条记录。
digoal=> select * from tbl_user order by random() limit 1;
id | firstname | lastname | corp | age
--------+-----------+----------+----------+-----
809085 | zhou | digoal | sky-mobi | 27
(1 row)
Time: 411.856 ms
执行计划:
digoal=> explain select * from tbl_user order by random() limit 1;
QUERY PLAN
------------------------------------------------------------------------------
Limit (cost=24852.98..24852.99 rows=1 width=31)
-> Sort (cost=24852.98..27352.98 rows=999999 width=31)
Sort Key: (random())
-> Seq Scan on tbl_user (cost=0.00..19852.99 rows=999999 width=31)
(4 rows)
简单是简单，缺点也很明显，这种随机查询在大表上跑数据库肯定是吃不消的。
以下列举利用索引列进行优化的方法。
方法 2. 随机取出n条记录,以下取出5条随机记录
digoal=> select * from tbl_user
digoal-> where id in
digoal-> (select floor(random() * (max_id - min_id))::int
digoal(> + min_id
digoal(> from generate_series(1,5),
digoal(> (select max(id) as max_id,
digoal(> min(id) as min_id
digoal(> from tbl_user) s1
digoal(> )
digoal-> limit 5;
id | firstname | lastname | corp | age
--------+-----------+----------+----------+-----
965638 | zhou | digoal | sky-mobi | 27
193491 | zhou | digoal | sky-mobi | 27
294286 | zhou | digoal | sky-mobi | 27
726263 | zhou | digoal | sky-mobi | 27
470713 | zhou | digoal | sky-mobi | 27
(5 rows)
Time: 0.670 ms
执行计划:
digoal=> explain select * from tbl_user
digoal-> where id in
digoal-> (select floor(random() * (max_id - min_id))::int
digoal(> + min_id
digoal(> from generate_series(1,5),
digoal(> (select max(id) as max_id,
digoal(> min(id) as min_id
digoal(> from tbl_user) s1
digoal(> )
digoal-> limit 5;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
--
Limit (cost=50.08..69.63 rows=5 width=31)
-> Nested Loop (cost=50.08..832.26 rows=200 width=31)
-> HashAggregate (cost=50.08..52.08 rows=200 width=8)
-> Nested Loop (cost=0.06..37.58 rows=1000 width=16)
-> Result (cost=0.06..0.07 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.03 rows=1 width=8)
-> Index Scan Backward using tbl_user_pkey on tbl_user (cost=0.00..27844.29 rows=999999 width=8
)
Index Cond: (id IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit (cost=0.00..0.03 rows=1 width=8)
-> Index Scan using tbl_user_pkey on tbl_user (cost=0.00..27844.29 rows=999999 width=8)
Index Cond: (id IS NOT NULL)
-> Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=0)
-> Index Scan using tbl_user_pkey on tbl_user (cost=0.00..3.89 rows=1 width=31)
Index Cond: (digoal.tbl_user.id = (((floor((random() * ((($0) - ($1)))::double precision)))::integer + ($1))))
(16 rows)
方法 3. 取出N条连续的随机记录.(此处用到函数)
digoal=> create or replace function f_get_random (i_range int) returns setof record as $BODY$
digoal$> declare
digoal$> v_result record;
digoal$> v_max_id int;
digoal$> v_min_id int;
digoal$> v_random numeric;
digoal$> begin
digoal$> select random() into v_random;
digoal$> select max(id),min(id) into v_max_id,v_min_id from tbl_user;
digoal$> for v_result in select * from tbl_user where id between (v_min_id+(v_random*(v_max_id-v_min_id))::int) and (v_min_id+(v_random*(v_max_id-v_min_id))::int+i_range)
digoal$> loop
digoal$> return next v_result;
digoal$> end loop;
digoal$> return;
digoal$> end
digoal$> $BODY$ language plpgsql;
CREATE FUNCTION
以下举例取出10条连续的随机记录
digoal=> select * from f_get_random(9) as (id bigint,firstname varchar(32),lastname varchar(32),corp varchar(32),age smallint);
id | firstname | lastname | corp | age
--------+-----------+----------+----------+-----
694686 | zhou | digoal | sky-mobi | 27
694687 | zhou | digoal | sky-mobi | 27
694688 | zhou | digoal | sky-mobi | 27
694689 | zhou | digoal | sky-mobi | 27
694690 | zhou | digoal | sky-mobi | 27
694691 | zhou | digoal | sky-mobi | 27
694692 | zhou | digoal | sky-mobi | 27
694693 | zhou | digoal | sky-mobi | 27
694694 | zhou | digoal | sky-mobi | 27
694695 | zhou | digoal | sky-mobi | 27
(10 rows)
Time: 0.418 ms
执行计划:
digoal=> explain select * from tbl_user where id between 694686 and 694695;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tbl_user_pkey on tbl_user (cost=0.00..4.48 rows=9 width=31)
Index Cond: ((id >= 694686) AND (id <= 694695))
(2 rows)
digoal=> explain select max(id),min(id) from tbl_user;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Result (cost=0.06..0.07 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.03 rows=1 width=8)
-> Index Scan Backward using tbl_user_pkey on tbl_user (cost=0.00..27844.29 rows=999999 width=8)
Index Cond: (id IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit (cost=0.00..0.03 rows=1 width=8)
-> Index Scan using tbl_user_pkey on tbl_user (cost=0.00..27844.29 rows=999999 width=8)
Index Cond: (id IS NOT NULL)
(9 rows)
其他的方法不再一一列举，方法2和方法3可以满足大多数的需求了。开销和运行时间均比方法1下降1000倍以上.
注意事项:
1. 索引列的类型和查询条件的类型必须匹配.
2. random() 取值范围 0.0 到 1.0
3. id between x and y 的写法等同于 id>= x and id<=y .
id BETWEEN SYMMETRIC x and y 的写法等同于 (id >= x and id <= y) or (id >= y and id <= x)
因此两者的执行计划是完全不一样的,如下:
digoal=> explain select * from tbl_user where id BETWEEN SYMMETRIC 3 and 2;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on tbl_user (cost=4.63..6.65 rows=1 width=31)
Recheck Cond: (((id >= 3) AND (id <= 2)) OR ((id >= 2) AND (id <= 3)))
-> BitmapOr (cost=4.63..4.63 rows=1 width=0)
-> Bitmap Index Scan on tbl_user_pkey (cost=0.00..2.31 rows=1 width=0)
Index Cond: ((id >= 3) AND (id <= 2))
-> Bitmap Index Scan on tbl_user_pkey (cost=0.00..2.31 rows=1 width=0)
Index Cond: ((id >= 2) AND (id <= 3))
(7 rows)
digoal=> explain select * from tbl_user where id BETWEEN 2 and 3;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tbl_user_pkey on tbl_user (cost=0.00..4.32 rows=1 width=31)
Index Cond: ((id >= 2) AND (id <= 3))
(2 rows)