如何在 MySQL 中使用 Join 比仅使用 Rand() 更快 [英] How is using Join faster than using just Rand() in MySQL

问题描述

怎么样

SELECT t.id 
FROM table t 
JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) 
AS tt 
ON t.id >= tt.maxid 
LIMIT 1

比

SELECT * FROM `table` ORDER BY RAND() LIMIT 1

我实际上在理解第一个方面有困难.也许如果我知道为什么一个比另一个更快，我就会有更好的理解.

I am actually having trouble understanding the first. Maybe if I knew why one is faster than the other I would have a better understanding.

*原帖@难的MySQL自加入请说明>

推荐答案

您可以在查询中使用 EXPLAIN，但基本上:

You can use EXPLAIN on the queries, but basically:

首先，根据(我认为)索引字段的最大值，您将获得一个随机数(不是很慢).这很快，我想说甚至可能是近乎恒定的时间(取决于索引哈希的实现?)

In the first you're getting a random number (which isn't very slow), based on the maximum of a (i presume) indexed field. This is quite quick, i'd say maybe even near-constant time (depends on the implementation of the index hash?)

然后你加入那个数字并只返回那个更大的第一行，而且因为你再次使用索引，所以这是闪电般的快速.

Then you're joining on that number and returning only the first row that's greater then, and because you're using an index again, this is lightning quick.

第二种是通过一些随机函数进行排序.这必须，但你需要查看解释，做一个完整的表扫描，然后返回第一个.这当然非常昂贵.由于那个兰特，您没有使用任何索引.

The second is ordering by some random function. This has to, but you'll need to look at the explain for that, do a FULL TABLE scan, and then return the first. This is ofcourse VERY expensive. You're not using any indexes because of that rand.

(说明看起来像这样，表明您没有使用密钥)

(the explain will look like this, showing that you're not using keys)

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  table    ALL    NULL    NULL    NULL    NULL    14  Using temporary; Using filesort

这篇关于如何在 MySQL 中使用 Join 比仅使用 Rand() 更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！