PostgreSQL从数据表选取单条随机行的最佳实现方案

你目前的写法采用random() < 0.2做随机抽样，本质是给每一行符合nickname LIKE 'test%'条件的测试用户设置了20%的行保留概率。当符合筛选条件的总行数较少时（比如总共只有3-5条匹配记录），很容易出现单次查询中所有行都被概率规则过滤掉的情况，后续配置的LIMIT 1没有可选取的数据，最终就会返回null。

选取单条随机行没有通用的银弹，需要根据表内匹配条件的数据规模选择对应方案，从根本上避免空值返回的问题：

• 小表场景（匹配条件的总行数<1万行，也就是你当前遇到的场景）：完全不需要靠概率过滤降低扫描量，直接通过随机偏移取值即可，只要有匹配数据就一定能返回结果，性能开销可忽略。
  对应改写后的函数实现：

  CREATE OR REPLACE FUNCTION any_test_user() RETURNS JSONB AS $$
    WITH matched AS (
      SELECT id FROM "User"
      WHERE "nickname" LIKE 'test%'
    ), total_stat AS (
      SELECT count(*) AS matched_cnt FROM matched
    )
    SELECT to_jsonb(u.*)
    FROM "User" u
    CROSS JOIN total_stat
    WHERE u."nickname" LIKE 'test%'
    OFFSET floor(random() * total_stat.matched_cnt)::int
    LIMIT 1;
  $$ LANGUAGE 'sql' STABLE;
  

  这个实现的逻辑是先统计所有符合筛选条件的用户总数，再生成一个0到总数-1之间的合法随机偏移量，从匹配结果集中取出对应位置的行，不会出现概率过滤把所有行筛空的问题。
• 大表场景（匹配条件的总行数超过10万，全表扫描统计的成本不可接受）：放弃概率抽样的逻辑，改用基于主键范围的随机定位方案，既不会漏取数据，也能借助索引实现毫秒级查询。
  对应函数实现（仅当id为自增主键时适用）：

  CREATE OR REPLACE FUNCTION any_test_user() RETURNS JSONB AS $$
    WITH value_range AS (
      SELECT min(id) AS min_id, max(id) AS max_id
      FROM "User"
      WHERE "nickname" LIKE 'test%'
    )
    SELECT to_jsonb(u.*)
    FROM "User" u
    CROSS JOIN value_range
    WHERE u.id >= (min_id + floor(random() * (max_id - min_id)))::bigint
      AND u."nickname" LIKE 'test%'
    ORDER BY u.id
    LIMIT 1;
  $$ LANGUAGE 'sql' STABLE;
  

  这个实现通过主键索引定位随机起始位置，向后查找第一个符合筛选条件的行，不需要全表扫描，哪怕匹配的数据量达到百万级也能快速返回，只要存在符合条件的记录就不会返回null。

注意事项：任何场景下都不建议用random() < N的逻辑选取单条随机行，这种不等概率抽样的方式不仅容易在小数据集下出现空结果，在大数据集上也无法保证每一行被抽到的概率均等，随机性表现不稳定。

内容的提问来源于stack exchange，提问作者zcaudate