如何优化 MySQL 的 ORDER BY RAND

分享于2022年07月17日 mysql performance random 问答
【问题标题】:如何优化 MySQL 的 ORDER BY RAND() 函数?(How can i optimize MySQL's ORDER BY RAND() function?)
【发布时间】:2022-01-23 06:31:35
【问题描述】:

我想优化我的查询,所以我研究了 mysql-slow.log

我的大部分慢查询都包含 ORDER BY RAND() 。我找不到真正的解决方案来解决这个问题。 MySQLPerformanceBlog 有一个可能的解决方案,但我认为这还不够。在优化不佳(或经常更新、用户管理)的表上,它不起作用,或者我需要运行两个或更多查询才能选择我的 PHP 生成的随机行。

这个问题有解决办法吗?

一个虚拟的例子:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
ORDER BY
        RAND()
LIMIT 1


【解决方案1】:

我的建议是添加具有 UUID(版本 4)或其他随机值、唯一索引(或仅主键)的列。

然后您可以在查询时简单地生成一个随机值,并选择大于生成值的行,按随机列排序。

确保如果收到的行数少于预期,则重复查询而不使用大于子句(在结果集的“开始”处选择行)。

uuid = generateUUIDV4()

select * from foo
where uuid > :uuid
order by uuid
limit 42

if count(results) < 42 {
  select * from foo
  order by uuid
  limit :remainingResultsRequired
}