【技能详谈】如何优雅的从数据库中随机捞取数据
从数据库中随机随机捞取一些数据是一个很常见的需求，在应用场景、运维场景、数据分析场景都会有如许的需求，随机的方法有许多，但要能做到：性能好、接近理想随机性，还是一件比力麻烦的事情，这里给出一些常见场景的处理方案和对比以供参考，具体场景还得具体分析。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
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]