【技术详谈】如何优雅的从数据库中随机捞取数据

从数据库中随机随机捞取一些数据是一个很常见的需求，在应用场景、运维场景、数据分析场景都会有这样的需求，随机的方法有很多，但要能做到：性能好、接近理想随机性，还是一件比较麻烦的事情，这里给出一些常见场景的处理方案和对比以供参考，具体场景还得具体分析。

---

---

1.概览-问题主要挑战点

在数据库中随机捞取数据是一项看似简单但实际复杂的任务，尤其是在大规模数据集上执行随机查询时，需要在性能与随机性之间取得平衡。这一平衡对于优化数据库查询的效率和确保数据的真实随机性至关重要。此外，数据库结构的设计也对随机查询的性能产生重大影响。

两个核心问题：性能和随机性。

1.1 性能问题

通常，在SQL中实现随机获取数据的最常见方式是使用ORDER BY RAND()。尽管这种方法简单直接，但在大规模数据集上却非常低效。ORDER BY RAND()的实现机制是对所有记录生成一个随机数，然后基于这些随机数对记录进行排序并返回结果。在小规模数据集上，这种方法还行，但在包含数百万甚至数十亿条记录的大型表中，这种方法会导致严重的性能瓶颈，因为它需要为每一条记录生成随机数并进行全表排序，这对CPU和内存的消耗极大。

1.2 随机性问题

理想的随机性意味着每条记录都有相同的概率被选中（均匀随机）。然而，某些优化方法可能会偏离这一理想状态。例如，为了提高性能，可能会选择通过索引扫描来获取随机数据。此时，如果索引是基于某个特定的顺序（如自增ID或时间戳），那么随机性可能会受到影响，因为这些索引倾向于集中在特定的数据范围内，而非全表分布均匀。

1.3 数据库结构的影响

数据库结构对随机数据获取的性能有着直接的影响。合理利用数据库结构，如索引、分区和视图，可以显著优化随机查询的效率。

索引：以B树索引为例，B树索引能够快速定位记录，适用于范围查询。然而，如果用于随机查询（例如随机选择一个ID范围），索引可能会倾向于选择相邻的记录，而非全表随机分布。因此，为了提高随机性，可能需要结合多个索引或使用哈希索引。

分区：通过对数据进行水平或垂直分区，可以将查询限制在较小的数据集上，从而提高效率。在随机查询中，可以先随机选择一个分区，再在分区内进行数据选择。这种方法不仅提高了查询速度，还可以利用分区键来增强随机性。例如，如果分区键是基于日期的，可以通过随机选择不同日期的分区来实现更加均匀的随机分布。

视图：尤其是物化视图，可以通过预计算和存储部分查询结果，减少复杂查询的实时计算开销。对于频繁的随机查询，可以考虑创建一个包含预处理随机样本的物化视图，定期刷新以保证样本的更新和随机性。这种方法能够在提高查询效率的同时，保证一定的随机性。

1.4 如何平衡（常见：分段随机）

在实际应用中，完全平衡性能与随机性是困难的，通常需要在这两者之间做出权衡。例如，在一些数据分析场景中，可以接受一定程度的随机性偏差，以换取显著的性能提升；而在其他需要高度随机性的场景（如公平抽奖），则必须优先考虑随机性。

一种常见的策略是分段随机，即先随机选择一个数据段（例如按ID范围或时间范围划分），然后在选定的数据段内进行精细的随机选择。这种方法通过减少全表扫描的开销，显著提高了查询效率，同时在大多数情况下能够保持较高的随机性。

2.常见的随机数据获取方法

2.1 SQL中的ORDER BY RAND()方法

ORDER BY RAND() 是最常见的实现随机数据获取的方法。如 1.1 所述，它的基本原理是为每一条记录生成一个随机数，然后对这些随机数进行排序，从而达到随机选择记录的目的。虽然这种方法简单直接，但在大数据集上的性能问题非常显著。

示例：

SELECT * FROM my_table
ORDER BY RAND()
LIMIT 10;

这种方法虽然慢，但是如果最后过滤到只剩下几百上千条了，倒也是个便捷的方法。

2.2 使用TABLESAMPLE或SAMPLE语句

在某些支持TABLESAMPLE或SAMPLE语法的数据库中，如PostgreSQL或SQL Server，可以直接通过这些语句高效地获取随机数据。这些方法通常通过对存储数据页进行采样，而不是对每条记录进行采样，从而在不牺牲太多随机性的前提下大幅提高查询效率。

示例（PostgreSQL）：

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

内部实现机制：

TABLESAMPLE的实现通常依赖于存储引擎的物理存储结构。例如，PostgreSQL的SYSTEM方法通过直接从数据块中抽取样本，从而减少了对全表扫描和排序的需求。尽管这种方法不是完全随机的（因为它的随机性依赖于数据的物理存储方式），但它在大多数情况下提供了足够的随机性，并且性能优异。

限制：

• 随机性不完全：由于TABLESAMPLE依赖于物理存储结构，采样可能偏向于某些数据块，这意味着它的随机性不如ORDER BY RAND()理想。
• 支持有限：并非所有数据库都支持TABLESAMPLE或SAMPLE语法，这限制了其适用范围。

2.3 基于索引的随机采样

基于索引的随机采样是一种通过索引直接定位记录的方法。在具有自增主键或其他连续索引的表中，可以通过生成一个随机索引值，然后基于该值进行查询来实现随机采样。
示例：

SELECT * FROM my_table
WHERE id >= FLOOR(RAND() * (SELECT MAX(id) FROM ifamily_photo))
ORDER BY id
LIMIT 10;

优点：

• 性能优越：通过索引直接定位记录，无需全表扫描和排序，性能显著提升。
• 随机性较好：对于自增主键或其他均匀分布的索引，随机性较好。

应用场景：

在大型分布式数据库中，基于索引的随机采样尤其适用。由于分布式数据库通常具有分片和多副本机制，通过索引采样可以有效减少跨节点的查询开销。

限制：

• 索引依赖性强：此方法依赖于索引的存在和索引值的分布情况。如果索引不均匀分布，可能导致随机性降低。

2.4 分页法

分页法是一种通过获取总记录数，再通过随机选择页面来获取随机数据的方法。它首先计算数据表中的总行数，然后随机选择一个页面进行查询。这种方法在大多数数据库中都有广泛应用。
示例：

SELECT * FROM my_table
LIMIT 10 OFFSET FLOOR(RAND() * (SELECT COUNT(*) FROM ifamily_photo));

优点：

• 实现简单：无需依赖特殊的语法或索引，适用于几乎所有数据库。
• 随机性好：在理论上，分页法可以提供接近理想的随机性。

缺点：

• 性能问题：随着数据集增大，OFFSET操作的开销也会增大，因为它需要扫描并跳过大量记录。
• 适用场景有限：在数据量较小或中等规模的数据集上效果较好，但在大规模数据集上性能会显著下降。

2.5 基于主键范围的随机采样

这种方法特别适用于有自增主键的表。通过先获取主键的范围，再在该范围内随机生成主键值来查询记录，可以有效避免全表扫描。
示例：

-- 获取表中主键的最小值和最大值
SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM my_table;

-- 生成一个随机主键，并获取相应的数据
SELECT * FROM my_table WHERE id = FLOOR(RAND() * (max_id - min_id + 1)) + min_id;

优势：

• 高效性：只需一次全表扫描来获取主键范围，后续查询基于索引，性能较高。
• 可控性：可以指定随机数的生成范围和数量。

场景：

• 适合场景：用于有自增主键或顺序主键的表，且数据分布均匀。
• 不适合场景：如果主键不连续（如删除了大量记录），会导致数据分布不均。

2.6 利用物理分区随机获取数据

在分区表中，每个分区存储不同的数据块。我们可以随机选择一个分区，再从该分区中随机获取记录。
示例：

-- 随机选择一个分区
SELECT * FROM my_table PARTITION (p1) ORDER BY RAND() LIMIT 1;

优势：

• 减少扫描范围：只需扫描单个分区，而非全表，提升查询效率。
• 适应大数据：分区表常用于大数据场景，分区内随机获取数据更具可操作性。

场景：

• 适合场景：大规模分区表，特别是按时间或地理位置分区的表。
• 不适合场景：当分区数据量差异较大时，可能导致随机性不均匀。

2.7 使用CTE与ROW_NUMBER()结合

在没有自增主键或特定索引时，使用Common Table Expressions（CTE）与ROW_NUMBER()结合，通过分配行号来实现随机获取数据。
示例：

WITH NumberedRows AS (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num, *
    FROM my_table
)
SELECT * FROM NumberedRows
WHERE row_num = FLOOR(RAND() * (SELECT COUNT(*) FROM my_table)) + 1;

优势：

• 灵活性：无需依赖表的结构，适用于各种复杂的查询。
• 可扩展性：可以在CTE中嵌套复杂的逻辑或过滤条件。
  场景：
• 适合场景：数据表没有明显的索引或主键，或者需要在复杂查询结果上进行随机取样。
• 不适合场景：大表查询性能较低，行号计算代价较大。

2.8 利用近似聚合函数进行随机选择

在需要快速近似随机结果时，可以使用近似聚合函数，如APPROX_COUNT_DISTINCT（部分数据库支持）或其他近似算法来进行随机选择。
示例：

SELECT *
FROM my_table
TABLESAMPLE(10 PERCENT) -- PostgreSQL 示例

优势：

• 速度快：比精确计算要快得多，适用于实时性要求较高的场景。
• 适用于大数据集：特别是在处理海量数据时，近似算法能有效减少计算开销。

场景：

• 适合场景：大数据场景，实时性要求高，但对精确随机性要求不高。
• 不适合场景：需要精确控制随机性或结果数量的场景。

2.9 合理利用业务字段进行随机选择

在某些业务场景中，部分字段本身就具备天然的随机性，例如文件的哈希值。在这些情况下，我们可以利用这些字段进行排序，直接选取哈希值最高或最低的几条记录，因为哈希值的高低并不具有特定的意义，而是均匀分布的。
示例：

SELECT *
FROM my_table
ORDER BY hash_value ASC
limit 10

优势：

• 速度极快：在这些业务字段上建立索引后，这种方法的查询效率远超其他随机选取方案。
• 随机性好：哈希值具有均匀分布的特性，能够保证选取数据的随机性。

场景：

• 适合场景：业务表中已有具备随机性的字段，例如哈希值、加密字符串等。
• 不适合场景：业务表中没有具备随机性的字段。如果为了随机性而添加一个哈希列，虽然能提高查询性能，但会引入一定的数据冗余和维护复杂性。

拓展：如果为每条记录添加一个随机生成的数值字段，可以通过这种字段实现高效的随机数据选择。然而，这种做法可能会对业务逻辑造成一定的侵入性，需要在设计阶段慎重考虑。

3.特殊场景的随机数据获取

在实际应用中，随机数据的获取往往需要针对特定场景进行优化和调整。以下将探讨如何在时间序列数据、大规模数据分析以及实时应用中实现高效的随机数据获取。

3.1 时间序列数据

挑战：时间序列数据具有时间上的连续性，因此在进行随机查询时，容易出现“随机”数据聚集在某一时间段的问题，这与真正的随机性存在冲突。同时，时间序列数据往往需要考虑数据的趋势和周期性，因此单纯的随机获取可能会忽略数据的上下文。

解决方案：
1.基于时间窗口的随机采样：通过将时间序列数据划分为多个时间窗口，然后在每个窗口内随机抽取一定数量的数据。这样既可以保证数据的随机性，又不会丢失时间上的连续性。

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('hour', timestamp) ORDER BY RANDOM()) as rn
    FROM time_series_data
    WHERE timestamp BETWEEN '2024-08-01' AND '2024-08-31'
) t
WHERE t.rn <= 10;

这个查询将数据按小时分段，并在每个小时内随机抽取10条记录。

2.分层采样：如果时间序列数据有明显的周期性或分层结构，可以先按层级（如年份、季度、月份）进行分层，再在每个层级内进行随机采样。这样可以保证不同层级的数据都有机会被选中。

SELECT *
FROM time_series_data
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY month ORDER BY RANDOM()) as rn
        FROM time_series_data
    ) t
    WHERE rn <= 5
);

场景应用：在股票市场数据分析、传感器数据处理、气象数据分析等需要考虑时间上下文的场景下非常适用。

3.2 大规模数据分析

挑战：在大数据平台中进行大规模数据分析时，数据量通常达到TB级别，直接从中随机抽样不仅耗时巨大，还可能因为数据的分布不均而导致抽样结果失真。

解决方案：
1.分区随机抽样：将数据分区后再进行随机抽样。通过在每个分区内独立进行抽样，可以减少数据的偏倚性，并提高抽样的执行效率。

SELECT * 
FROM big_data_table
TABLESAMPLE SYSTEM(1);  -- PostgreSQL示例，获取1%的随机样本

2.分布式计算框架：利用Spark、Flink等分布式计算框架进行随机采样，通过MapReduce等并行计算方法，能够在保证随机性的同时，大幅度提升采样速度。

val sampleData = bigDataRDD.sample(withReplacement = false, fraction = 0.01, seed = 42)

3.预计算抽样集：对于需要频繁随机抽样的大数据集，可以预先计算好一组抽样集并存储下来，后续只需要从预计算好的数据集中进行随机抽取，这样可以大幅度降低计算开销。

CREATE TABLE precomputed_sample AS 
SELECT * 
FROM big_data_table
WHERE RAND() < 0.01;

场景应用：适用于大规模用户行为分析、市场调研、机器学习模型训练等需要对大量数据进行抽样分析的场景。

3.3 实时应用

挑战：在高并发、低延迟的实时应用中，传统的随机数据获取方法可能无法满足实时性和高效性的要求。例如，在推荐系统中，需要从大量数据中快速抽取出适合当前用户的推荐内容。

解决方案：
1.缓存随机数据：为了应对高并发需求，可以预先生成一批随机数据并存入缓存，当需要随机获取时直接从缓存中提取。这种方法适合对实时性要求极高的场景。

@Cacheable(value = "randomDataCache", sync = true)
public List<Data> getRandomData() {
    return dataRepository.getRandomDataFromDatabase();
}

2.基于分片的并行随机获取：通过将数据分片并行处理，每个分片独立随机获取一定数量的数据，然后再汇总，既能保证随机性，又能提高数据获取速度。

SELECT * FROM (
    SELECT * FROM data_table WHERE shard_id = 1 ORDER BY RAND() LIMIT 10
    UNION ALL
    SELECT * FROM data_table WHERE shard_id = 2 ORDER BY RAND() LIMIT 10
    -- 可继续扩展
) as combined_results;

3.流式计算与近似随机：在某些实时分析场景中，可以采用流式计算的方法，通过对数据流进行近似随机采样，以满足实时性需求。

SELECT STREAM * 
FROM real_time_data 
SAMPLE PERIOD 1 SECOND;

场景应用：适用于在线广告推荐、实时监控系统、实时数据分析等需要高并发、高性能的数据处理场景。

4.总结

在数据库里随机捞取数据是一个看似简单实际剧透挑战性的任务，尤其在需要兼顾性能和随机性的情况下。本文探讨了几种常见的随机数据获取方法以及一些特殊场景的解决方案，希望能给广大开发者一点帮助。

常见方法:

• ORDER BY RAND() 是最直接的方式，但在大数据集上性能较差。
• 使用 TABLESAMPLE 或 SAMPLE 可以在支持这些语法的数据库中高效抽样。
• 基于索引的随机采样和分页法在特定场景下表现良好，适合大规模数据集。

优化与扩展:

• 索引优化: 合理设计B树或哈希索引能提高查询效率，减少全表扫描。
• 缓存策略: 通过缓存随机查询结果，减少对数据库的直接访问，提升性能。
• 数据分片: 在分布式数据库中，通过数据分片避免全局扫描，优化查询速度。

这些方法只是提供了一个参考，实际场景可能还要复杂的多，需要具体问题具体分析。

---

ATFWUS 2024-08-21