PostgreSQL快速随机取出记录

1、传统的，也是最慢的方式：

SELECT myid FROM mytable ORDER BY RANDOM() LIMIT 1;

缺点：近似于全表扫描，没有好的索引可以走；

2、稍微快一点的方式，用offset来实现：

SELECT myid FROM mytable OFFSET floor(random()*N) LIMIT 1;

3、德哥的实现方式，用函数实现：

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);

4、借助另一列的索引实现：

create table randtest (id serial primary key, data int not null);
insert into randtest (data) select (random()*1000000)::int from generate_series(1,1000000);
create index randtest_md5_id_idx on randtest (md5(id::text));
explain analyze
select * from randtest where md5(id::text)>md5(random()::text) order by md5(id::text) limit 1;

5、9.5版用 TABLESAMPLE:

SELECT * FROM my_table TABLESAMPLE SYSTEM(0.000001) LIMIT 1;