高性能地从数据库随机取一条数据

原创已于 2023-08-12 13:13:12 修改 · 190 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #mysql

于 2023-08-05 14:41:04 首次发布

关系型数据库专栏收录该内容

7 篇文章

订阅专栏

博客介绍了在MySQL中利用主键ID索引特性随机取数据的原理。通过最大ID乘以随机数得到随机ID作为临时表，将原表和临时表用主键ID关联，再按主键ID排序取一条数据，还提及了示例SQL。

原理

利用主键ID索引的特性，通过最大的ID乘以一个随机数得到一个随机的ID；
将随机的ID作为临时表；
将原表和临时表利用主键ID关联；
利用主键ID排序，只取一条数据。

示例SQL

SELECT
  t3.*
FROM
  (
  	SELECT
       t1.*
   	FROM table t1 INNER JOIN
   	(SELECT ROUND(RAND() * (SELECT MAX(id) FROM table)) id) t2 ON t1.id >= t2.id
   	ORDER BY t1.id ASC LIMIT 0,1
  ) t3

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

付聪1210

关注关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
打赏
打赏
打赏举报

举报

专栏目录

SQL Server数据库随机抽取数据性能优化总结篇

ElServer的博客

09-29

496

在SQL Server数据库中，随机抽取数据是一个常见的需求，特别是在需要进行样本分析或数据抽样的场景中。然而，在大型数据库中执行随机抽取操作可能会导致性能下降，因为它需要扫描整个表或索引来获取随机数据。在本文中，我们将讨论一些优化技巧，以提高SQL Server数据库中随机抽取数据的性能。综上所述，SQL Server数据库中随机抽取数据的性能可以通过使用合适的排序方法、表抽样技术和索引优化来提高。如果我们的表有一个适当的索引，我们可以利用索引来辅助随机抽取操作，从而提高性能。

SQL或ORM获取随机一行数据

唯有热爱可抵万难

12-19

668

时，对于一些大型数据库可能会导致性能问题，因为它会在内存中对整个查询集进行随机排序。如果对性能有较高要求，建议使用原生 SQL 结合。或其他更高效的方法。

参与评论您还未登录，请先登录后发表或查看评论

SQL随机提取N条记录

05-24

运用SQL随机抽取N条记录的方法，初学者

sql随机抽取记录

weixin_34344677的博客

11-24

903

我们经常想在一个数据表中随机地选取出数据来，比如随机生成考试试卷等。利用 SQL Server 的 NewID() 方法就可以很轻松地达到这个目的。 NewID() 方法返回一个 GUID，如：EE95A489-B721-4E8A-8171-3CA8CB6AD9E4 在 select 表的时候，再增加一列为 NewID() 就可以了。 SQL 语句：select *, NewID()...

mysql 随机选择一条记录

hbase入门到进阶课程

11-29

612

mysql 随机选择一条记录博客分类： 数据库 MySQLSQLBlog 转自： http://hi.baidu.com/why_question_how/blog/item/d3e20a44e8b032046b63e58c.html MySQL：快速随机选取一条记录的思考 2010年07月14日星期三 10:43 ...

PHP实现在数据库百万条数据中随机获取20条记录的方法

10-20

代码示例中展示了一个简化的查询语句，其中使用了`mt_rand`函数来生成随机偏移量，并通过SQL查询从表中取出一条记录。实际上，为了确保取出的20条记录是随机且不重复的，需要在随机偏移量生成后对之前抽取的记录进行...

如何从数据库中随机取出10条记录的方法

01-02

通常情况下，随机取记录涉及到对数据库中的数据集合进行随机排序，然后从排序后的结果中选择前N条记录。在这个过程中，掌握SQL语言的随机函数和子查询是关键。以MySQL数据库为例，我们可以使用`RAND()`函数结合`...

1亿条记录的MongoDB数据库随机查询性能测试

12-16

在这个特定的性能测试中，针对一个包含1亿条记录的MongoDB数据库进行了随机查询的压力测试。测试环境是基于CentOS 6.4 64位操作系统的，服务器配置包括Intel(R) Xeon(R) CPU E5-2630，64GB内存，以及由6块10K转速...

随机获取oracle数据库中的任意一行数据(rownum)示例介绍

12-16

总结来说，`ROWNUM`是Oracle数据库中一种强大的工具，它可以方便地用于选取特定行或实现分页查询，但在处理并发操作时需要注意并发控制，以防止数据不一致。了解并熟练掌握`ROWNUM`以及相关的分析函数，对于优化...

通过SQL 随机取1条记录

heqiyong的专栏

09-18

296

SELECT TOP(1) * FROM 表名 ORDER BY CHECKSUM(NEWID()); [b]备注[/b]CHECKSUM 对其参数列表计算一个称为校验和的哈希值。此哈希值用于生成哈希索引。如果 CHECKSUM 的参数为列，并且对计算的 CHECKSUM 值生成索引，则结果是一个哈希索引。它可用于对列进行等价搜索。 CHECKSUM 满足哈希函数的下列属性：在使用等...

mysql使用——sql实现随机取一条数据

weixin_33989780的博客

06-28

502

最近在做接口测试的时候，测试数据是从数据库查询的，但是当需要并发多次去调用接口时，如果sql只是单纯的进行了limit取值，那并发的时候肯定会每条数据都一样。因此，研究了下sql随机取一条数据的写法，从而用大量不同数据去进行并发的接口性能测试原文：https://www.cnblogs.com/my_life/articles/7827233.html 三种方法：（1）ORDER B...

SQL语句 - 随机抽取一条记录

IT男也疯狂

11-24

998

select * from [表名] Order By Rand()

mysql随机查询一条数据_mysql使用——sql实现随机取一条数据

weixin_34460480的博客

01-18

596

最近在做接口测试的时候，测试数据是从数据库查询的，但是当需要并发多次去调用接口时，如果sql只是单纯的进行了limit取值，那并发的时候肯定会每条数据都一样。因此，研究了下sql随机取一条数据的写法，从而用大量不同数据去进行并发的接口性能测试三种方法：(1)ORDER BY RAND()写法：SELECT * FROM 表A ORDER BY RAND() LIMIT 1缺点：rand()放在OR...

sql语句如何随机取一条数据

最新发布

02-21

<think>嗯，用户想要用SQLAlchemy从MySQL中随机取10000条数据，而且还要考虑性能。首先，我得想一下，怎么在保证性能的情况下实现随机取数据呢。因为通常来说，随机取数据可能会比较耗资源，尤其是当数据量大的时候。首先，用户提到的随机取数据，常见的方法是用ORDER BY RAND()，但是这个方法在数据量大的时候性能会很差，因为它需要对所有数据进行排序，然后随机选。对于10000条来说，这显然不现实，尤其是当表很大的时候，这样的操作会很慢。那怎么办呢？可能需要找一个更高效的方法。比如，先获取表的总行数，然后生成随机偏移量，再用LIMIT和OFFSET来取数据。不过，这种方法在MySQL中，如果偏移量很大，性能也会有问题，因为OFFSET会扫描前面的所有行，直到偏移位置。如果总行数是100万，随机取10000次的话，可能效率也不高。或者，可以考虑在应用层处理。比如，先获取所有符合条件的ID，然后在应用层随机选取10000个ID，最后用IN查询来获取这些数据。这种方法的好处是避免了数据库的随机排序，但是当ID不连续或者有间隙的时候，可能会有问题。而且，如果表特别大，获取所有ID也可能消耗较多内存和网络资源。另外，有没有其他方法呢？比如，使用WHERE子句加上随机数，比如给一个随机条件，比如id > some_random_value，然后取一定数量的数据。但这种方法可能无法保证正好取到10000条，可能需要多次查询，或者在应用层补足数量。还有一种方法是利用MySQL的TABLESAMPLE，但是MySQL好像不支持这个语法，可能得用其他方式模拟。回到用户的问题，用户需要的是随机取10000条，同时考虑性能。所以，可能需要权衡不同的方法。如果数据量不是特别大，先获取最大ID，然后生成随机ID，可能是个办法。比如，假设ID是连续的，生成随机ID，然后查询。但如果有删除操作，ID可能不连续，这样会有空洞，导致实际取到的数据不足。这种情况下，可能需要先获取所有存在的ID，再随机选择，但这样对于大数据量表来说，可能不太行。或者，可以预先计算一个随机列，比如添加一个专门的列，存储随机数，并建立索引。每次查询时，根据这个列来排序，这样可以利用索引，提高速度。但需要维护这个列，可能会增加写操作的开销。再考虑一下，用户的需求是每次随机取10000条，是否需要完全随机的？如果是近似随机，可能可以接受一些偏差。比如，先计算总行数，生成一个随机偏移量，然后取10000条。这样只需要一次全表扫描，但偏移量大的时候，OFFSET会比较慢。比如，总行数是1亿，随机偏移量可能是0到99990000，然后取OFFSET N LIMIT 10000。这时候，OFFSET N会导致扫描N行，对于N很大时，会很慢。不过，如果使用覆盖索引，可能可以更快，因为索引扫描比全表扫描快。如果表的主键是自增且连续的，没有空洞，那么计算总行数，生成随机偏移量，然后取数据，可能可行。但如果有空洞，这种方法可能无法取到足够的行，或者需要多次查询。例如，假设表的总行数是total_rows，那么可以生成一个随机偏移量offset = random.randint(0, total_rows - 10000)，然后查询OFFSET offset LIMIT 10000。这样只需要一次查询，但总行数需要准确，且主键连续。这可能是一个折中的办法，虽然不一定完全随机，但性能较好。如果用户接受这种近似随机的方案，可能是一个可行的办法。但需要确保主键是连续的，或者处理可能存在的空洞。另外，如果表有索引，可以考虑在索引列上生成随机数，然后利用索引进行排序。例如，如果有一个已索引的列，比如时间戳，或者某个自增列，可以结合随机函数来排序，但这可能不如专门的随机数列有效。或者，使用子查询，先随机选择主键，然后再JOIN回原表。例如： SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RAND() LIMIT 10000); 但子查询中的ORDER BY RAND()仍然需要全表扫描，性能可能不好。不过，如果子查询只选择主键，可能稍微快一点，但依然可能不够。总结一下，可能的解决方案： 1. 避免使用ORDER BY RAND()，而是使用随机偏移量。适用于主键连续的情况，性能较好，但可能不准确。 2. 预存随机数列并建立索引，每次查询时用该列排序。需要维护额外列，但查询快。 3. 分批次随机获取，例如多次查询，每次取部分数据，然后在应用层合并。可能增加查询次数，但每次查询更快。 4. 使用WHERE子句结合随机数，例如利用主键的范围，生成多个随机区间，再UNION结果。但具体实现可能复杂。对于用户的情况，假设表的主键是自增且相对连续，没有太多空洞，那么随机偏移量的方法可能最合适。首先获取总行数，然后生成随机偏移量，然后取LIMIT 10000 OFFSET {offset}。但需要注意，当总行数减去10000小于offset时，需要调整offset。例如，offset的最大值应该是max(0, total_rows - 10000)。具体实现步骤： 1. 使用SQLAlchemy获取表的总行数：session.query(Table).count() 2. 生成随机偏移量：offset = random.randint(0, max(0, total_rows - 10000)) 3. 执行查询：session.query(Table).offset(offset).limit(10000).all() 这种方法在总行数较大的情况下，比如超过10000行时，可以快速获取数据。但如果表的总行数刚好是10000左右，可能offset为0，只能取到全部数据。另外，如果主键不连续，可能会有空洞，导致实际取到的数据不足10000条，这时候可能需要检查结果数量，并进行补充查询，但这样会增加复杂度。另一种方法，如果主键是整数且大致连续，可以生成随机ID范围。例如，找到最小和最大ID，然后在最小ID和最大ID - 10000之间随机选择一个起始点，然后查询WHERE id >= start_id LIMIT 10000。但同样，如果有空洞，可能需要调整。或者，使用应用层随机选择ID。例如，先获取最小和最大ID，然后生成10000个随机ID，然后查询WHERE id IN (...)，但这样生成的ID可能很多不存在，导致查询结果不足，需要多次查询，直到凑够10000条。这种方法可能在ID分布均匀的情况下有效，但效率取决于空洞的数量。如果用户需要严格的随机性，可能必须使用ORDER BY RAND()，但必须优化其性能。比如，先缩小数据范围，例如添加WHERE条件，减少需要排序的数据量。或者，使用覆盖索引，让排序在索引中进行，减少磁盘IO。但无论如何，ORDER BY RAND()在大数据量下性能不佳，所以可能需要其他权衡。回到用户的问题，用户需要的是高性能的随机取10000条数据。所以，可能最合适的办法是使用随机偏移量，前提是数据的主键连续或者近似连续，并且能够接受可能的少量重复或遗漏。在SQLAlchemy中的实现代码可能如下： from sqlalchemy import create_engine, MetaData, Table from sqlalchemy.orm import sessionmaker import random engine = create_engine('mysql://user:pass@host/db') Session = sessionmaker(bind=engine) session = Session() metadata = MetaData() metadata.reflect(bind=engine) table = metadata.tables['your_table'] total_rows = session.query(table).count() if total_rows <= 10000: results = session.query(table).all() else: offset = random.randint(0, total_rows - 10000) results = session.query(table).offset(offset).limit(10000).all() session.close() 这种方法在总行数足够大的情况下，可以快速获取数据，因为offset和limit在MySQL中，如果使用主键范围扫描，可能比较高效。但需要注意，如果表没有主键或者主键不是按顺序存储的，OFFSET的性能可能较差。另外，如果使用InnoDB引擎，主键是聚簇索引，所以按主键顺序扫描会比较快。因此，如果查询中按主键排序，然后使用OFFSET，可能性能更好。例如，在查询中添加order_by(table.c.id)，但默认情况下可能已经按主键排序，但为了确保，可以显式指定。此外，可以考虑使用流式查询或者服务器端游标，避免一次性加载所有数据到内存，但这可能对性能影响不大，因为用户需要的是10000条数据，应该可以一次性处理。另外，如果表中的数据经常变动，总行数和偏移量可能不准，导致取到重复或遗漏的数据。需要根据实际场景考虑这一点。总的来说，推荐的方案是使用随机偏移量，前提是主键连续，并且能够接受可能的少量数据遗漏。如果必须严格随机，可能需要考虑其他方法，但性能会有所牺牲。</think> SQLAlchemy 中从 MySQL 随机高效获取 10000 条数据时，需避免直接使用 `ORDER BY RAND()`（性能极差）。以下是几种优化方案及实现示例： --- ### 1. **随机偏移量法（主键连续时推荐）** **适用场景**：主键近似连续且无大量空洞（如自增 ID 表）。 **原理**：通过总行数计算随机偏移量，用 `LIMIT + OFFSET` 快速分页。 ```python from sqlalchemy import create_engine, Table, MetaData from sqlalchemy.orm import sessionmaker import random # 初始化数据库连接 engine = create_engine('mysql+pymysql://user:pass@host/db') Session = sessionmaker(bind=engine) session = Session() metadata = MetaData(bind=engine) # 反射获取表对象 your_table = Table('your_table', metadata, autoload=True) # 获取总行数 total_rows = session.query(your_table).count() # 计算随机偏移量 if total_rows <= 10000: query = session.query(your_table) else: offset = random.randint(0, total_rows - 10000) query = session.query(your_table).order_by(your_table.c.id).offset(offset).limit(10000) # 获取数据 results = query.all() session.close() ``` -- ### 2. **应用层随机 ID 抽样** **适用场景**：主键存在空洞但可接受多次查询。 **原理**：预查询所有 ID，在应用层随机抽取后分批查询。 ```python import random from sqlalchemy.sql import select # 获取所有主键 id_list = [row[0] for row in session.execute(select(your_table.c.id)).all()] # 随机抽取 10000 个 ID sampled_ids = random.sample(id_list, 10000) if len(id_list) >= 10000 else id_list # 分批查询（避免 IN 语句过长） batch_size = 1000 results = [] for i in range(0, len(sampled_ids), batch_size): batch = sampled_ids[i:i+batch_size] results += session.query(your_table).filter(your_table.c.id.in_(batch)).all() ``` -- ### 3. **预计算随机列（高频率查询推荐）** **适用场景**：需频繁随机查询且允许额外存储。 **原理**：新增一列存储随机数并建立索引，查询时按该列排序。 ```sql -- 数据库新增列并建立索引 ALTER TABLE your_table ADD COLUMN random_val FLOAT DEFAULT RAND(); CREATE INDEX idx_random ON your_table(random_val); ``` ```python # 查询时按随机列排序 results = session.query(your_table).order_by(your_table.c.random_val).limit(10000).all() ``` -- ### 4. **分块随机法（平衡性能与随机性）** **原理**：将数据分块后随机选择块，再从块中随机取数据。 ```python # 分块大小（如总数据量为 1e6，分 100 块） chunk_size = 10000 total_chunks = total_rows // chunk_size # 随机选择多个块 selected_chunks = random.sample(range(total_chunks), k=1) offset = selected_chunks[0] * chunk_size # 查询块内数据 results = session.query(your_table).order_by(your_table.c.id).offset(offset).limit(chunk_size).all() # 若需要更随机，可再从结果中二次抽样 final_samples = random.sample(results, 10000) if len(results) >= 10000 else results ``` --- ### 性能对比总结 | 方法 | 优点 | 缺点 | |--|-------------------------------| | 随机偏移量法 | 单次查询，速度最快 | 依赖主键连续性 | | 应用层 ID 抽样 | 严格随机 | 多次查询，网络开销大 | | 预计算随机列 | 查询极快，支持高并发 | 需维护额外列 | | 分块随机法 | 平衡性能与随机性 | 实现稍复杂 | 根据实际场景选择最合适的方法。**若无严格随机性要求，优先推荐随机偏移量法**。