MySQL从60万行中快速选择10条随机行 [英] MySQL select 10 random rows from 600K rows fast

问题描述

如何最好地编写一个查询，该查询从总共60万行中随机选择10行?

How can I best write a query that selects 10 rows randomly from a total of 600k?

推荐答案

一篇出色的文章，介绍了从简单到有缺口，再到有缺口的不均匀情况的几种情况.

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order -by-rand/

在大多数情况下，这是您的操作方式:

For most general case, here is how you do it:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

这假设id的分布是相等的，并且id列表中可以有空白.有关更多高级示例，请参见文章

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

这篇关于MySQL从60万行中快速选择10条随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！