我如何优化MySQL的ORDER BY RAND（）函数？

Z时代
2024-01-10
分类：问答

我想优化查询，以便进行调查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

回答：

尝试这个：

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND(20090301) < @lim / @cnt
        ) i

MyISAM（由于COUNT(*)是即时的），此方法特别有效，但即使这样，InnoDB其10效率也比更高ORDER BY RAND()。

这里的主要思想是我们不进行排序，而是保留两个变量并计算running probability要在当前步骤中选择的行的。

有关更多详细信息，请参见我的博客中的这篇文章：

如果您只需要选择一条随机记录，请尝试以下操作：

SELECT  aco.*
FROM    (
        SELECT  minid + FLOOR((maxid - minid) * RAND()) AS randid
        FROM    (
                SELECT  MAX(ac_id) AS maxid, MIN(ac_id) AS minid
                FROM    accomodation
                ) q
        ) q2
JOIN    accomodation aco
ON      aco.ac_id =
        COALESCE
        (
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_id > randid
                AND ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        ),
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        )
        )