从mysql中的大表中快速选择随机行 [英] quick selection of a random row from a large table in mysql

问题描述

从大型mysql表中选择随机行的快速方法是什么?

What is a fast way to select a random row from a large mysql table?

我正在使用php，但是我对任何解决方案都感兴趣，即使它是另一种语言也是如此.

I'm working in php, but I'm interested in any solution even if it's in another language.

推荐答案

获取所有ID，从中选择一个随机ID，然后检索整行.

Grab all the id's, pick a random one from it, and retrieve the full row.

如果您知道ID是连续无孔的，则只需获取最大值并计算一个随机ID.

If you know the id's are sequential without holes, you can just grab the max and calculate a random id.

如果到处都是空洞，但大多是连续值，并且您不关心随机偏斜，则获取最大值，计算一个id，然后选择ID等于或大于该值的第一行你算了.产生这种偏差的原因是，跟在另一个id后面的那些id后面跟随着这些id的几率更高.

If there are holes here and there but mostly sequential values, and you don't care about a slightly skewed randomness, grab the max value, calculate an id, and select the first row with an id equal to or above the one you calculated. The reason for the skewing is that id's following such holes will have a higher chance of being picked than ones that follow another id.

如果您是随机订购的话，那么您的手将要进行一次可怕的桌子扫描，而 quick 一词不适用于这种解决方案.

If you order by random, you're going to have a terrible table-scan on your hands, and the word quick doesn't apply to such a solution.

不要那样做，也不要按GUID订购，它有同样的问题.

Don't do that, nor should you order by a GUID, it has the same problem.

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