为什么不使用mysql ORDER BY RAND()? [英] Why don't use mysql ORDER BY RAND()?

问题描述

我看到许多网站说不使用ORDER BY RAND()，例如 http://forge. mysql.com/wiki/Top10SQLPerformanceTips 因此，我进行了测试，测试了20k条记录表的速度和性能，其中10k条记录具有username ="username":

I saw many websites saying don't use ORDER BY RAND(), eg http://forge.mysql.com/wiki/Top10SQLPerformanceTips So I run a test, I have tested the speed and performance on 20k records table, 10k records among them have the username="username" :

SELECT username FROM testingspeed WHERE username='username' ORDER BY RAND();

结果:

Showing rows 0 - 29 (10,000 total, Query took 0.0119 sec).
id = 1 
select_type = SIMPLE
table = testingspeed
type = ref
posible_keys = username
key = username
key_len = 32
ref = const
rows = 3225
Extra = Using where; Using index; Using temporary; Using filesort

由于执行查询只花了0.0119秒，所以它应该是非常好的速度，为什么人们仍然说不要使用ORDER BY RAND()?为什么仅3225行受到影响?为什么不影响10,000行?

since it took 0.0119 seconds only to execute the query, it should be very good speed, why people still say DON'T use ORDER BY RAND()? Why 3225 rows are affected only? Why not 10,000 rows are affected?

推荐答案

ORDER BY RAND()的问题是，正如您所解释的那样，它告诉您使用临时文件"和使用文件排序".对于每个请求，都会创建一个临时表并进行排序.那是相当繁重的操作.当您的数据库不处于高负载状态时，这可能无关紧要，但是会花费很多性能.

The problem of ORDER BY RAND() is that as your explain tells you the "Using temporary" and the "Using filesort". For each request a temporary table is created and sorted. Thats a pretty heavy operation. It will probably not matter when your database is not under heavy load but it will cost a lot of performance.

这篇关于为什么不使用mysql ORDER BY RAND()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！